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.
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:
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|
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).
- 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:
- 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
- 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.
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.
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.