Maybe you’re trying to build a the next great daily fantasy baseball spreadsheet. Or maybe you’re attempting to figure out who to start next week.
You’ve got plans to use a powerful projection tool like Razzball’s Hitter-Tron… but then you see it. There’s not a player ID to be found! How can you take data from a site like Razzball and drop it into your existing rankings, dollar value, or trade evaluator spreadsheets?
You Know Me. I’m Obsessed With PLayer IDs.
They’re the best way to build a reusable spreadsheet that you can just drop projection data in to. You can have all sorts of valuation formulas, trade evaluators, or daily roster lineup tools ready to go. You just drop in some projections with player IDs and everything seamlessly links up and calculates.
Look Closely At the Links
Even if you don’t see a player ID at first glance, on many sites you can still get what you need if you look carefully.
Hover over a player’s hyperlink. Depending on your browser you should somewhere see the web address for the hyperlink (in Google Chrome the destination of the link shows in the bottom left on the status bar).
There’s a player ID in there! 9368 for Evan Longoria. A quick look at the PlayerID Map tells us the 9368 happens to be Evan Longoria’s Fangraphs ID.
Unfortunately, if you paste the Hitter-Tron information into Excel, you don’t get text about the web address. You’re left with just the hyperlink itself and no reference to player ID #9368.
There’s not even an existing Excel formula we can use to just pull out the web address. We have to get a little advanced and create our own.
|1.||Start with an Excel file containing hyperlinks within a player’s name, like mentioned above. If you’re using Razzball as a source, you can use your mouse to select a table of information and then paste it into Excel. The links should transfer into the Excel file.|
|2.||To create our own function we must first save the Excel file in a format that allows for macros.If you’re using a version of Excel earlier than 2007, you don’t need to worry about this. If you’re using Excel 2007 or later, perform a “Save As”.Choose to save the file as an “xlsm” file type (Excel Macro-Enabled Workbook).|
|3.||After saving, hit ALT + F11. This will load Microsoft Visual Basic, a program that will allow us to create and define our new function.|
|4.||To begin, click once to select the “Microsoft Excel Objects” folder in the upper left hand corner of the screen. Then right-click and choose the option to Insert > Module.The new module is where we can “code” the new function. You should notice that a blank white area now appears on the rightportion of the screen. This is where we will place the code.|
|5.||In this blank area, copy and paste the following text:
You should notice some changing of colors in the text after you paste it.
I won’t get too much into programming methodology, but we just created a function called “GetWebAddress” that we can now use in Excel. That function requires just one input – the “cell_with_link” or the cell containing the hyperlink we want to extract.
The output of GetWebAddress is the address property of the hyperlink.
|6.||Click the Save button in Microsoft Visual Basic. And then exit the program (but leave Excel open).|
|7.||Let’s test out the new function. I’ll use the BenZobrist hyperlink in my example to illustrate how to use it. The formula we created requires oneinput. Thatinput is the cell containing the hyperlink.I added the formula to cell U1 in order to pull the hyperlink from Ben Zobrist’s name in cell B1. That formula is
|8.||After the formula is complete, hit Enter.You can then copy the formula down to the other rows of data.|
|9.||Once you’ve extracted the web address you can use Excel’s LEFT, MID, RIGHT, and FIND functions to pull out just the player ID string.TIP: I’m sure there’s an easier way to do it, but the formula I used to get the player ID out of the Razzball addresses is:
That’s Great. What Can I Use This For?
I get a lot of requests for advice on how to create a spreadsheet for determining the best daily fantasy lineup. This would be a great place to start.
Razzball puts out daily hitter and pitcher projections that are powered by Steamer’s Rest of Season projections and then adjusted for opposing pitchers, park factors, batting order, and more.
They also have 7 day projections for those of us playing in weekly lineup leagues. This information is perfect for deciding who to start and who to sit.
You can access these various projections under the Tools>Hitter-Tron menu at Razzball.
Where Else Can I Use This Strategy of Pulling Data From Hyperlinks?
Many other sites use this same approach of linking to player pages. This same formula can be used to extract player IDs from a variety of sites.
Take a look at links to Jose Abreu on the sites below:
I try to avoid doing VLOOKUPs and similar Excel functions using player names. You can see above that Jose Abreu might be presented as “Abreu, Jose”, “Jose Abreu”, or “José Abreu”. Using player IDs takes the guess work out of this. Creating your own user defined function in Excel will help you get at the underlying player ID on many sites.
And let’s give a special thank you to Fangraphs for just including the player ID in all their downloadable reports!