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.
Step-By-Step Instructions
| Step | Description |
|---|---|
| 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
=GetWebAddress(B1)
|
| 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:
=LEFT(MID(U1,FIND(“player/”,U1,1)+7,99), |
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.
All of these player ID systems correspond to the IDs you’ll find in the Player ID map (click here to learn more about player IDs or click here to download the Excel file).
Take a look at links to Jose Abreu on the sites below:
Baseball-Reference
ESPN
Yahoo
CBS
Conclusion
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!
Stay smart.
I started adding the Razzball IDs to the SFBB Player ID Map….it appears that Razzball uses the Fangraphs IDs for their IDs.
That’s a good point, Kevin. I agree that they’re using Fangraphs IDs. You can probably avoid adding a whole new column and just use what’s in the Fangraphs ID column.
I’m sorry i dont see how all this is necessary or how its different from just inserting a hyperlink into excel and labeling it whatever you want… which you outlined in one of your other articles. Love the site and all the great information. Also, When i ran SGPs for my 14 team mixed I was a little alarmed to see Bill Hamilton in the top 5 most valuable using steamer projects… if you have experienced similar results please let me know. Have a great day/night! BC
Hi PSU. This article might be better labelled “how to ‘extract’ a player ID” from a website if they don’t provide it for you. A number of sites, like CBS and ESPN, don’t provide player IDs when you download their projections. This can make it very difficult to join/VLOOKUP/match their projections up with other models or to develop your own rankings from. It’s the reverse of building a hyperlink into Excel. Instead I’m using Excel to pull the text out of the hyperlink so I can get at the player ID.
In regards to your Hamilton question, yes. Players like Hamilton and Altuve, projected for a lot of SBs, do come out as very valuable in my SGP rankings. I think it’s important to keep in mind that the rankings are calculated using a logical and systematic approach. In my mind the rankings are telling us those are very valuable players.
On the other hand, this doesn’t mean you need to draft Hamilton in the first round. Rankings are going to tell you how players should be valued. And then we can start to play the game of judging the market and figuring out how long we can delay before taking such a player. I’m not fully up-to-date on Hamilton’s early mock draft results, but let’s assume he’s coming out as a fourth round pick. If your rankings say he’s the 5th most valuable player, maybe you snag him in the third round (or wait as long as you can before you think he’ll be taken by someone else).
Or if you’re in an auction and Hamilton is valued at $25 but some average auction results are telling you he’s going for $18 on average, then you can feel comfortable going to $19, $20, or higher.
If you really don’t like the fact that he’s coming out so high, then you could adjust your SGP denominator or factor. Instead of having it set at every 9.4 SB is equal to 1 SGP, increase it to 10, 11, 12, etc. Increase it until you feel comfortable with the rankings.
I don’t recommend doing this because then I think you’re getting away from the true calculation of value that you’ve spent time doing. But it certainly is an option.
When I copy and paste the second line from step five, the text is red rather than green and I get the following error message
Compile error:
Expected: end of statement
Any idea what might be causing this to not work correctly for me?
Hi hankp,
I have a guess. Does the apostrophe that comes over look odd? Try deleting that apostrophe and retyping your own using the one on your keyboard. The apostrophe there tells Excel that is not really a line of code, it’s just a comment we are leaving to ourselves. So if it’s not turning green, I think something went wrong there. I think it’s an issue of how the blog converts the apostrophe into a weird looking character that’s not truly an apostrophe.
Let me know if that doesn’t work.
Tanner
Or it may just work now if you try recopying and pasting. I changed the format of the text so I don’t think the apostrophe will get screwed up any more.
Worked like a charm. I think you are right about it likely being the apostrophe, because I had to retype the quotation marks in your formula from step 9 to get it to work. Thanks so much for the help!