Here’s a quick tip that can save you a lot of time if you maintain a spreadsheet of player projections, a list of player rankings, or if you’re simply looking for a more efficient way to do player research.
Hypothetical
Let’s say you have a list of ten free agent pitchers you want to look up at BrooksBaseball.net. You have a preference for pitchers that limit fly balls (thus limiting home runs), have a variety of pitches at their disposal, and you also want to see if they have an effective strikeout pitch(es). You also want to view the mix of pitches used by the pitcher over time. Finally, you wish to limit your research to the last two years of major league data (2012 and 2013 seasons, at the time of this article).
You visit BrooksBaseball.net and this example page below (for Clayton Kershaw) displays fly ball percentage, ground ball percentage, the number of pitches and times each has been thrown, and the whiff percentage for each pitch type over the last two years.
This example page shows you mix of pitches used, by month, over the last two years.
Between these two pages, we can do all the necessary research to make a decision about the free agent pitchers.
Looks At the URLs For Those Sites
The key to making this player research a more efficient process is to take advantage of the web address (URL) for these pages. You can locate the web address for a web page by visiting the site and looking at the path shown at the top of your browser.
The image above is the URL for Kershaw’s “Tabular Data>Sabermetric Outcomes” page at BrooksBaseball.net. The tail end of that URL has very important information embedded in it that we can use.
www.brooksbaseball.net/tabs.php?player=477132 &gFilt=&time=month&minmax=ci&var=so&s_type=2& endDate=08/04/2013&startDate=03/30/2012
The “player=477132” component of the URL specifies that this search/web page is for player ID 477132, or Clayton Kershaw.
The “endDate=08/04/2013&startDate=03/30/2012” component restricts the search to 2012 and 2013 season data (up to August 4, 2013, the time of writing).
Here’s the typed URL for the “Usage and Outcomes” page at BrooksBaseball.net:
www.brooksbaseball.net/outcome.php?player=477132 &gFilt=&time=month&startDate=03/30/2012&endDate=08/04/2013&s_type=2
The same concepts for player ID and dates apply. We can now focus in on the bold red text which differentiates the actual type of page being visited.
A Quick Discussion On Player IDs
If you’re not familiar, there are a number of Player ID systems used to track the statistics of major league baseball players. MLB.com, Fangraphs, and Baseball Reference all have their own player ID system.
Clayton Kershaw’s player IDs for these three systems are as follows:
| ID System | Player ID |
|---|---|
| MLB.com | 477132 |
| Fangraphs | 2036 |
| Baseball Reference | kershcl01 |
Notice that the MLB.com ID for Kershaw is the same as the ID used at BrooksBaseball.net. So we know that Brooks Baseball uses MLB.com Player IDs.
If you want to know more about Player IDs, look back to part two of the “Create Your Own Fantasy Baseball Rankings” series where we looked at Player IDs, what they are, and how to use them to your advantage when working with large sets of baseball data.
Let’s Do This
- I’ll start by listing the ten free agents and setting up the framework for the spreadsheet to also display Player ID, a link to the Sabermetric Outcomes page, and a link to the Usage and Outcomes page.
- For this small defined group of players, finding the values for the Player ID column is a manual process (if you’re doing this for a ranking or projection list, use a lookup function to more efficiently lookup the IDs). Use the SFBB Player ID Map Excel file (link to download Excel file) to look up player IDs. The Player ID map contains IDs for MLB, Fangraphs, Baseball Reference, and CBS, among others.
- Before beginning the next step, copy the URL/web address for the “Sabermetric Outcomes” page at BrooksBaseball (you can copy from the quote below).
http://www.brooksbaseball.net/tabs.php?player=477132&gFilt=&time=month&minmax=ci&var=so&s_type=2&endDate=08/04/2013&startDate=03/30/2012
- We’ll now use Excel’s “HYPERLINK” function to build a link that will take us directly to each player’s specific page. The function has two arguments, the first being the URL/web address and the second being the text to display in Excel.To begin, start entering the formula “=hyperlink(“.
- Paste the BrooksBaseball URL copied above in to the formula. Note, you must surround the URL in quotation marks. Then enter a comma, to indicate the first portion of the formula is complete. And finally, type “Link” and a closing parentheses.
- This formula has a hard coded player ID in it (477132). To make the formula more powerful we need to have the player ID look to the values in the “Player ID” column and not be hard coded. To do this, replace the current player ID with this – “&A2&” (include the quotes). The quotation mark closes the first part of the URL, then says “and whatever is in cell A2 and resume the rest of the URL/web address. That probably makes no sense… The formula should look like this:
=HYPERLINK(“http://www.brooksbaseball.net/tabs.php?player=“&A2&”&gFilt=&time=month&minmax=ci&var=so&s_type=2&endDate=08/04/2013&startDate=03/30/2012″,”Link”)
- The formula is now dynamic, meaning it can be copied down to the remaining nine players in the list and automatically grab their player IDs. To quickly copy the formula down to the rest of the players double click on the black square in the lower right hand corner of the cell.
- Repeat steps 3-7 with the URL for the “Usage and Outcomes” page. This formula below
http://www.brooksbaseball.net/outcome.php?player=477132&gFilt=&time=month&startDate=03/30/2012&endDate=10/31/2013&s_type=2
becomes
http://www.brooksbaseball.net/outcome.php?player=“&A2&”&gFilt=&time=month&startDate=03/30/2012&endDate=10/31/2013&s_type=2
- The final product will be a series of usable links for each of the players, making the research project much more efficient. You can also see how adding links like this to your projection or rankings sheet for each player can make a great added feature.
Other Uses
There are many other uses beyond links to BrooksBaseball pages. Razzball.com uses Fangraph IDs for their player pages (e.g. http://razzball.com/player/2036 for Kershaw). You could use this to link to Razzball’s player value calculations. You could also link to players’ Fangraphs pages to see hitter batted ball data, hitter split data (useful for daily fantasy play), or plate discipline information.
Like This Tip? Want to Be Aware of Similar Excel Tips In The Future?
Make sure to like SmartFantasyBaseball.com on Twitter.
Conclusion
Pay close attention to the URLs for your favorite research sites. Look for Player IDs embedded in the web address that you can take advantage of to build powerful links.
What sites do you use that have player IDs embedded in the URL? Let me know in the comments below.
Stay smart.
thank you very much for the information and clarification on player_ids. I know that I can use the mapping spreadsheet (thanks again!), but can you please tell me if I have a mlb player_id, is there a single live xml file (or another live online source) that I can look up the player name (programmaticly )? Thanks!
Hi Dru, thanks for visiting the site. I don’t know of any live resource like this. I originally got the idea from a gentleman named Tim Blaker that maintains his own list at http://www.crunchtimebaseball.com/baseball_map.html. Because I needed to be able to update the list on my own schedule and because he tracks some extra IDs I don’t need, I decided to start my own list. I’m not much of a programmer, but I believe Tim has found a way to do this more “automatically” than me. You can probably contact him on his site or via Twitter. He may be able to help you. https://twitter.com/crunchtimebb
If you are interested, I can post my (working) solution explanation and code.
Dru, I’m definitely interested. Can you either e-mail it to me or post it in the comments, if the format will translate here? The process of maintaining this log is a bit time consuming and labor intensive. Always looking for easier ways to do things.
thank you very much for the prompt reply! I figured out a way to get what I want and will be happy to post if anyone is interested. I will check in with Tim Blaker to see if he has a better solution. Happy New Year!
BATS and THROWS columns?
Does anyone have the solution to the following problem:
I’m compiling a database (using Google Sheets) of all MLB 2013 Player Stats. I’m having a problem finding a source that contains a B-T column or separate BATS and THROWS columns. This seems to be not important on every table I’ve seen and sorted online.
I’ve tried Baseball-Reference, Fangraphs, Baseball Almanac, among many others but just can’t seem to find this data available in columns. I’ve created a MASTER Player Sheet in my workbook that I use to reference stats in other sheets through the ‘Query” function, but I’m stuck having to manually enter each and every player’s BATS and THROWS data. But I’m sure that there has to be a source…
Any ideas?
Hi Jody, thanks for reading the site. I just e-mailed you the “Master.csv” file from the Lahman database, which is available for free at seanlahman.com. It has batter and pitcher handedness information. I think this is what you’re looking for. Let me know if it’s not.
Great! Thanks so much for your help. I’m going to check it or now. And after that, going straight to your website. That’s a must.