I think you’re gonna like this one.
In this post I’ll show you how to link live average draft position information from the web into your draft spreadsheet. Every time you open your rankings file it will pull down updated ADP information. Bam!
How We’re Going To Do This
We will use a powerful feature of Excel called web querying to pull in the ADP information aggregated by FantasyPros.com (please note, I’m an affiliate of Fantasy Pros). The web query will suck up that table of ADP information and bring it directly into Excel for us to then VLOOKUP into our existing “Hitter Ranks” and “Pitcher Ranks” tabs.
Assumptions
I am assuming that you’ve followed my Standings Gain Points or Points-League Ranking series and are already starting with a spreadsheet that is based off one of those (you don’t have to have those exact spreadsheets, but something similar).
Excel Functions and Concepts Used in this Post
Web Query
As you can probably imagine, the power of the web query is that it automatically updates the data in your Excel file without you having to do ANYTHING after the initial setup.
Web queries are created from the “Data” tab on the Excel Ribbon, under the “Get External Data” icon grouping. There are several ways to get data from outside sources into Excel, we will be using the “From Web” button.
One “weakness” I have found in web queries is that they cannot work with “tables” in Excel. You cannot pull a web query in as the data of a table. If you’re a big follower of this site you know that’s a bit of an issue for me because I use tables all the time. Thankfully this doesn’t prevent us from doing things, I just point it out because you might wonder why I set things up the way I do in the instructions below.
Find
The FIND function searches within a specified cell for a string of text that you provide. If the FIND function locates the string, it will return the character position where the string starts at.
I know, you’re thinking “What the heck does that even mean?”. Here’s an example. Let’s say we have the text “Mike Trout (LAA)” in a cell and every player in our whole spreadsheet follows that format. If we want to pull out each player’s team we will need to start by figuring out where the team name starts in that cell. And we can’t just say it will always start at the 13th character each time when we have players like this hanging around MLB.
Instead we can use the FIND formula to intelligently determine where that opening parenthesis is for each player (it starts at 12 for Trout and 23 for Salty).
This formula requires two inputs:
FIND(Find_text, Within_text)
-
Find_text – This is the string of text you are searching for and keep in mind it is case-sensitive. You would wrap the string you are searching for in quotation marks. So in our example above, to look for the opening parenthesis you would enter “(” here. Or if you’re trying to be slightly more precise, you could enter ” (“, a space before the parenthesis.
-
Within_text – This is the text you want to search WITHIN. This can be a cell number.
Left
The LEFT function gives you the leftmost number of characters in a text string. You also get to specify the number of characters to specify.
For example, if you have a text string of “Mike Trout (LAA)” and you ask for the 10 leftmost characters in that string, you would get “Mike Trout” back.
This formula requires two inputs:
LEFT(Text, Num_chars)
- Text – This is the text string you want the leftmost characters from.
-
Num_chars – This is the number of characters you want from the string. This can be a hard entered number (e.g. 10) or it can be a formula itself that results in a number.
Combining Functions Together
We can do something pretty powerful by combining the FIND and LEFT functions together. I’ve been hinting at it with this “Mike Trout (LAA)” example. Recall from above that the LEFT function wants to know our text string (“Mike Trout (LAA)”) and the number of characters on the left to pull from that string.
Assume that cell B2 has a value of “Mike Trout (LAA)”. Instead of using this formula:
LEFT(B2, 10)
We can use this:
LEFT(B2, FIND(" (",B2)-1)
The FIND(" (",B2)-1 part of the formula returns a 10 (if you don’t subtract the one it returns an 11), and “Mike Trout” has 10 characters in it (including the space). By using this combination of functions we don’t have to type in a “10” for Mike Trout and a “21” for Saltalamacchia.
Important Prerequisite
Before you’re able to proceed with the instructions below you must make sure the PLAYERIDMAP in the file you’re working with was updated after February 21st, 2015. I added a column to the Player ID Map called “FANTPROSNAME” that is necessary for the steps below to work.
Instructions for updating your PLAYERIDMAP can be found here. Completing the update should only take five minutes or so.
Step-by-Step Instructions
| Step | Description |
|---|---|
| 1. | Make sure you’ve completed the update of your PLAYERIDMAP tab mentioned above. Once that’s completed, insert a new sheet into your existing rankings spreadsheet. |
| 2. | Place the sheet next to your “Hitter Ranks” and “Pitcher Ranks” tabs. Name this new sheet “ADP Info”. |
| 3. | Our goal is to pull the ADP information from FantasyPros.com into this new tab. The web address of the page containing the ADP information is http://www.fantasypros.com/mlb/adp/overall.php. Use your mouse to select and then copy that address. |
| 4. | Place your mouse cursor on cell A1 of the new blank worksheet and click to select that cell. Then go to the “Data” tab of Excel and click on the “Data From Web” icon.
In the “New Web Query” window that pops up, paste the ADP web address into the “Address:” field and click the “Go” button. |
| 5. | I will frequently get error messages popping up at this point asking me, “Do you want to continue running scripts on this page?”. This is not unique to this example, I get those on just about every web query I do.
Click No. You may have to answer the question several times.
You should see one of these yellow arrows in the top left of the ADP table. The tables at FantasyPros are web query friendly. You can try performing web queries of other sites and you might find that the yellow arrows do not appear on each individual table. In these cases you’ll need to import the entire web page and then use other Excel formulas to isolate in on the information you want. This can be difficult to do… pray for the yellow arrows! Click the yellow arrow on the ADP information and you should see the table get selected (it shades light blue). |
| 6. | After the data is selected, click the “Import” button.
You will then be asked “Where do you want to put the data?”. You should see that Excel will add the data to cell A1 on the existing worksheet, this is what we want. But before we do the import, click the “Properties” button. Type “ADP_INFO” into the “Name:” field in case you ever add future web queries to your sheet (so you can tell by the name what the queries are doing). Then check the “Refresh data when opening the file” box. This will retrieve new ADP info each time you open your draft spreadsheet. |
| 7. | Click “OK” to accept your changes to the “External Data Range Properties” dialog. Then click “OK” to start the data import. Excel will think for awhile and bring in the ADP info! |
| 8. | Our next challenge will be to pull the ADP information from this tab to our “Hitter Ranks” and “Pitcher Ranks” tabs. And it’s going to take a bit of work to do this.
If you’re a frequent follower of the site you should be cringing a little when you look at this data… There are no player IDs! GASP! We are going to have to match and pull data using just player names. And to make matters worse, the FantasyPros data isn’t just the player name. You can see from the image below it also contains the player’s team and position inside parentheses. We need to isolate just each player’s name and strip away the parenthetical information. We can do this using the FIND and LEFT formulas discussed above. We’ll start with the FIND formula and use it to locate the position of the opening parenthesis. Use your mouse to select cell J2 (this should be a couple columns to the right of the player with the highest ADP, Mike Trout as I write this). Enter the following formula in cell J2 (I chose column J to give a little bit of cushion in case FantasyPros adds more columns to their ADP report):
Note there is a space before the opening parenthesis. This is because after each player’s name is a space and then the opening parenthesis and I want to be as specific as possible to help Excel find this. Hit Enter to accept your formula. Excel is telling us that the |
| 9. | If the " (" starts at character 11, then that means we want to use the LEFT formula to pull out the first 10 characters of Trout’s name cell. To do this we will enter the LEFT formula around the FIND formula we just added. The FIND formula will be an input into our LEFT formula. Adjust your existing FIND formula to be (the new parts of the formula are in red):
Recall that the LEFT formula is looking for the cell to evaluate (B2, or |
| 10. |  Use your mouse to select the new “Mike Trout” name we just created. Then hover your mouse over the little black square on the lower right hand corner. You should see the cursor turn to a cross or plus sign. Click your mouse and drag this down for as far as the ADP information goes on the spreadsheet.
This new column of player names corresponds to the “FANTPROSNAME” column in the PLAYERIDMAP and we can now use a VLOOKUP to get at this information. |
| 11. | I mentioned at the beginning of this article that we cannot turn the data that comes from the web query into an Excel table, but we can turn these player names off to the side of the web query into a table.
To do this, give the list of players a column name (e.g. “FANTPROSNAME”). |
| 12. | After you format the table you should be placed into the “Table Tools” menu (if you’re not, click once on an item within the table and you should see this menu appear on the ribbon). Give your table a more meaningful name, like “ADPINFO”. |
| 13. | Type “ADP” in next to the “FANTPROSNAME” column. After you hit Enter Excel should expand the table to include this new column.
Now choose what you think is the best ADP source of information for your purposes. If you’re preparing your spreadsheet for an NFBC draft or a Yahoo Public League, you may want to choose just those columns as your sole source. For this example I will choose the “AVG” column provided by Fantasy Pros. I now want to pull the “AVG” column from the web query into the “ADP” column in my new table. To do this I’ll enter the formula Hit Enter and the formula should copy down and adjust accordingly to all rows in the ADPINFO table. |
| 14. | Now that the web query and ADPINFO table are set up, we can add the VLOOKUP formulas to the “Hitter Ranks” and “Pitcher Ranks” sheets.
Go to the “Hitter Ranks” sheet and add two new columns to the far right of your spreadsheet. Label one “FANTPROSNAME” and one “ADP”. |
| 15. | In the FANTPROSNAME column we will use the PLAYERID (in column A) to go into the PLAYERIDMAP tab, locate that PLAYERID, and then return the player’s name from the FANTPROSNAME column in the PLAYERIDMAP (FantasyPros does not use an ID system, so we must match things using player names as they appear on that site).
Fortunately we already have a column on the “Hitter Ranks” tab, IDFANGRAPHS, that is doing this same thing and we can copy the formula and make a minor edit. Click on the Fangraphs ID (in the IDFANGRAPHS column) for the first player in your spreadsheet. Copy this cell. Then go to the FANTPROSNAME column and paste the formula. This will bring over the Fangraphs ID information. The formula we just pasted is:
We can make one minor edit to get the Fantasy Pros Name (the edit is in red):
Note, your formula may have the “IFERROR” function wrapped around the VLOOKUP formula. This is great. If the formula you just pasted DOES NOT use “IFERROR” before the VLOOKUP, add that now. So now your formula would be:
|
| 16. | We can now use the FANTPROSNAME info to perform a VLOOKUP into the ADPINFO table we just created. Enter the following formula into the ADP column:
This formula attempts to take the player’s name in the FANTPROSNAME column, find it in the ADPINFO table, and give us back what is in the second column (hence the 2 in the formula) of the ADPINFO table (which is the ADP info). Hit Enter when you’ve finished typing/copying the formula. You may then have to select the entire ADP column and use the number formatting buttons in Excel to format the information correctly. |
| 17. | Repeat steps 14, 15, and 16 on the “Pitcher Ranks” tab in order to add pitcher ADP information there. |
| 18. | After you’ve added the ADP information to the “Hitter Ranks” and “Pitcher Ranks” tabs you can then hide the FANTPROSNAME columns from those. Just don’t delete them, the VLOOKUP formulas to get the ADP need that column to work.  |
| 19. | That’s it! Each time you open your rankings file it will be updated with the newest ADP information. |
Once you’re through those error messages you should see the Average Draft Position info. This Excel dialog gives us a preview of the web address we pasted in and we now must choose to import the entire web page into Excel or we also have the option to isolate a specific table for importing. Whenever possible, I recommend isolating in on the the specific table you are after. To choose just a specific table, look for the small yellow arrow in the top left corner of that table.
Then click the “Format as Table” button
Have Questions?
Please ask me any questions you have about this in the comments area at the end of this post.
Want to Know How to Do Something in Excel?
If you have a suggestion or question on how to do something fantasy baseball related in Excel, shoot me an e-mail at smartfantasybaseball at gmail dot com.
Want More Info Like This?
The best way to stay in touch with the site and to get more information like this is to follow me on Twitter or to register as an SFBB Insider.
Stay smart!
I get all the way to step 16, but the final VLOOKUP formula results in a bunch of #N/A entries. Not sure why, everything else in the step by step worked perfectly.
FYI, I e-mailed “rocky” privately and was able to find a minor issue in his FIND and LEFT formulas. There is a tricky little “minus one” that is important in the formula:
=LEFT(B2,FIND(" (",B2)-1)Yep! Thanks for be willing to take a look and for responding so quickly!
Can web queries also be used to automatically bring in projections as well? If so, how would one do that? Thanks!
Hi Mike. Yes, you can use a web query to get just about any kind of data that’s in a table format on a web page (like the FantasyPros ADP). Projections would be another great candidate for this. You just need to find projections that are all listed on one page. That’s one of the really nice things about the FantasyPros site, they list everyone on one page and don’t divide it onto separate web pages (which would be difficult to web query).
I should warn you that some sites do not allow web querying of their content so you should check the terms of use for the sites you want to pull from. I think they dislike people taking their content but not sticking around and using the site, viewing their ads, etc. .
Just follow steps 1-8 above using the site you want to grab projections from.
after i pasted the revised data into the PLAYERIDMAP, the three new columns were not inlcuded as part of the PLAYERIDMAP table, and therefore the vlookup is not finding the FANTPROSNAME link. for the life of me i can’t figure out how to add the three columns to the table! any help?
Hi zdm. Let’s see if I can explain this without any screenshots. Go to the bottom right hand corner of the Player ID Map. Find the lower right hand corner of the “table”, where blue and white shading ends. Hover your mouse right over that bottom right hand corner and you will get the option to resize the table to include additional columns.. Click the mouse button down and drag the resize icon to the right in order to get more risks, etc.