I’m a little biased, but I think the Player ID Map is an invaluable tool.
But if I’m being honest… it has a really big weakness. When I make changes to it, there’s not a great way for me to get that updated information to you.
Until now!
The Easy-to-Update Player ID Map
After discovering that you can create a live connection from a Google Sheet to an Excel file, I have moved the Player ID Map into a Google Sheet.
The advantage of doing this is that you can link to this Google Sheet in your own spreadsheets. And if you download the Excel version, it will already have a pre-established link to the Google Sheet version.
How to Update the Player ID Map
Once you’ve downloaded the new version, you can simply right-click anywhere in the player listing and choose the option to “Refresh” the connection. Any changes will automatically pull into your file.
The “Change Log” tab of the Player ID Map will work the same way. Right-click and refresh the connection on that page to get an updated listing of the changes that have been made.
In the past you would have to come back to the site, download a new copy of the Excel file, and then paste it into your existing spreadsheets. Now you’ll just need to right click (or keep reading to see how you can have it update automatically) and update it!
The Links
The Player ID Map and Change Log are available in a variety of formats, depending on the goal you’re trying to accomplish.
| Description | Information |
|---|---|
| Updating Player ID Map Excel File | Link: https://www.smartfantasybaseball.com/wp-content/uploads/2015/10/SFBB-Player-ID-Map.xlsx
This is a link to download the Player ID Map now containing a connection to an online source, so that when I add players to the list, they can easily be refreshed in your files. |
| Player ID Map Web Page | Link: https://www.smartfantasybaseball.com/PLAYERIDMAPWEB
This is a web page version of the Player ID Map. You can web query it into your Excel files or simply look at the list if you’re searching for a piece of information. |
| Player ID Map CSV File | Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCSV
This link can be used to create a connection to an online CSV version of the Player ID Map that you can set up within Excel. We’ll take a closer look at how to do this in a set of instructions below. |
| Change Log Web Page | Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCHANGELOG
This is a web page version of the Player ID Map Change Log. You can web query it into your Excel files or simply look at the list of changes to see what updates have recently been applied. |
| Change Log CSV File | Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCHANGELOGCSV
Similar to the CSV of the actual Player ID Map, this link can be used to create a connection to the change log within Excel. We’ll take a closer look at how to do this in a set of instructions below. |
What If I Currently Have the Old Player ID Map in my File?
It’s great that the newly downloaded Player ID Map comes with the connection. But what about those who have the old version? Here’s a short set of instructions of how to establish this connection.
| Step | Description |
|---|---|
| 1. | Open the existing Excel file you have that contains the unlinked Player ID Map*. Save a backup of this file just in case something goes wrong in this process.
* Please note this will not work on the Projecting X Excel Template. If you’re interested in updating that file, please contact me for information. |
| 2. | Download and open the new Player ID Map (click here to download). After the file downloads, you will likely have to “Enable Editing” by clicking the warning button.
Then click the button to enable the external data connections in the file (this is so the information can connect to the Google Sheet and download any updates I make). |
| 3. | Next, we will copy the entire Excel table from the new Player ID Map and paste it right over the top of the old Player ID Map table in your existing file.
To do this, click in cell A1 of the new Player ID Map. Then hit the SHIFT+CTRL+END keys on your keyboard. This should select the entire body of the Player ID Map table. Then copy the selected data (right-click Copy or CTRL + C). |
| 4. | Now open your existing Excel file and go to the “PLAYERIDMAP” tab. Select cell A1 and paste the new Player ID Map right over the top of this old Player ID Map.
Depending on the security settings you have set in Excel, you may see a warning that pasting this will create a connection within your Excel file. If you trust the information I provide, click “OK”. This is your goal. To create a connection to the Player ID Map. |
| 5. | After you have pasted in the connection, you can adjust the settings of how it updates by clicking on the “Data>Connections” button on the ribbon.
Then locate and click once on the “PLAYERIDMAP CSV Link” connection and click the “Properties…” button. I would suggest checking the “Refresh data when opening the file” box. The alternative is leaving it unchecked but then having to remember to periodically refresh it manually to see if updates have been made. Click “OK” to close the Properties window and then “Close” to close the Connections window. |
You Can Also Create Your Own Connection
If you’re starting a file from scratch or are interested in adding the Player ID Map to an existing Excel file, you can easily create a linked connection in that file.
In the instructions below, I’ll show you how to create a connection to the “Change Log” in the Player ID Map, but the same steps can be used to add a link to the “Player ID Map” information or any other published CSV file. You might want to link in the Change Log so you can see what updates have recently been made.
| Step | Description |
|---|---|
| 1. | Open the fantasy baseball spreadsheet you’re trying to add the connection too.
In your Excel file, click the plus sign button toward the bottom to create a new sheet. Then right-click on the new sheet and rename it something descriptive. For my example, I’ll name it “Player ID Change Log”. Now copy this bolded web address: That’s the address of the CSV version of the change log. |
| 2. | Click on the “Data” tab of the Excel ribbon and then click the “From Text” button on the “Get External Data” grouping of icons.
You will then be presented with a browsing window where you are expected to look for some kind of text file on your computer. Instead, just paste the web address you just copied earlier into the “File name:” field and then hit “Open”. |
| 3. | Excel’s “Text Import Wizard” will appear. Make sure the “Delimited” option selected (a CSV file is a data file in which the columns of data are separated by commas, this makes it “delimited”). Also make sure you have checked the “My data has headers” check box.
Hit the “Next” button to proceed to the next importing step. |
| 4. | This screen of the import wizard is asking what character type the delimiter is.
Because we are importing a CSV, it’s a comma (CSV stands for “comma separated values”). Check that option in the list of delimiters and look at the “Data Preview” to make sure the columns are breaking in the proper places (you should see lines drawn between each column). Click “Next” to proceed to the next step. The ensuing screen allows you to classify each column as a certain data type. The change log is simply a list of text information, so we don’t need to worry about changing data types here. Just click the “Finish” button. |
| 5. | You should then be asked where and how to import the data. This is an important screen here. When given the option, I think you should choose to import data as an Excel table, but you see that option is currently grayed out. 
Check the “Add this data to the Data Model” check box. This should activate the “Table” radio button. Before hitting “OK”, click the “Properties” button. This should bring up the “Connection Properties” menu. If you wish to do so, you can rename the connection here. And then adjust the property check boxes to those shown below. Make sure to uncheck the “Prompt for file name on refresh” box. And although it sounds like a good idea to check the “Refresh data when opening the file” box, DON’T! Checking that now seems to cause error messages when opening my spreadsheets. But we can come back later and check it with no issue. Click “OK” to close the Connection Properties. Then click “OK” to accept the “Import Data” settings and to perform the actual import. |
| 6. | Now we can return to update the refresh file on opening setting. Click on the “Data” tab of the ribbon. Then click the “Connections” button. Locate the connection we just created. Select it from the list. Then click the “Properties…” button. 
Now check the “Refresh data when opening the file” box and click the “OK” button. |
| 7. | You now have the Change Log listing in your spreadsheet and it will update automatically any time I make adjustments to the Player ID Map in the future (if I add a new players or fill in missing information, it will be listed on the log).
Note, sometimes information comes into my tables sorted in an odd manner. You can sort the Change Log listing so the most recent changes appear on the top by clicking the filter drop down menu above the “DATE” column and choosing the “Sort Newest to Oldest” option. |
Need a Player Added?
Now that I can push out updates to your file much more easily, let me know if you need a player added to the Player ID Map. Let me know on Twitter if you find a missing player.
Thanks for reading! Stay smart.
Tanner, when I copy & paste the new downloaded ID map something goes wrong and my hitters/pitchers ranks appear all in N/A’s. Please help.
Hey Jun,
Yes. The act of pasting this will possibly destroy the links that are looking to the existing table. I realize it’s draft season, so you may want to just try this older method of updating: https://www.smartfantasybaseball.com/2015/02/how-to-update-player-id-map/
It’s less likely to cause issues.
If you want the new “refreshable” Player ID Map, the best bet may to simply be start over from scratch with it. I was trying to help with this new version, but it has caused some issues. You can read more about them here: https://www.smartfantasybaseball.com/2015/12/an-important-lesson-and-how-to-resolve-vlookup-errors/
Thanks,
Tanner
Hi Tanner. All this stuff is great. Thanks… I’m running into an issue that I can’t seem to figure out. I’m trying to automatically (and efficiently) pull in Yahoo position eligibility data for each player into a spreadsheet. (For example, Manny Machado is eligible at SS and 3B on Yahoo and I want my spreadsheet to reflect that). I’ve tried your power query method, but Yahoo’s tables only go out to 25 players, so that method will require me to do way too many queries. Your player map only has one position for each player, so that doesn’t work either. I’ve even tried creating a connection to crunchtimebaseball’s player map, but the Yahoo positions don’t line up with the eligibility on the fantasy wesbite. Do you have any suggestions? Thanks. Rick
Hi Rick,
I think you’ll have to use Power Query, web query, or use Google Sheets IMPORTHTML to pull in game appearances for each player from a site like this: http://www.baseball-reference.com/leagues/MLB/2016-appearances-fielding.shtml
Then you’ll have to use formulas to determine which position a player qualifies at, based upon your league settings. That page is for 2016 appearances, you can get to 2015 by just changing the number in the web address.
For example, if your games player limit to qualify at a position was 20 games in 2015, you could use an IF formula to determine which columns have an amount of at least 20 games in them.
Hope that helps some. Let me know if you get into this and have more specific questions.
Thanks,
Tanner
Tanner, finally got around to attempting what you suggested. I ran into a couple of issues…
I used power query to import appearances from the link you copied here. Unfortunately that only gives info on games played rather than games played AND games started at each position, which are both factors in determining position eligibility in my league.
I tried to use the Baseball Ref appearances link by position, which has the information I need, but it caused problems when I refreshed the data (i.e. the tables I selected for the data seemed to jump around day to day, requiring me to start the whole power query process over again to find the table that contained the relevant information).
MLB.com has good data on GP and GS by position, but they list each player in ‘last name, first initial’ format which doesn’t agree at all with the player ID map.
The best site I found for this was FanGraphs. I was able to change the size of the table to include all players. (I changed the end of the link to ‘&page=1_5000’ — which for some reason you can’t do for projection data, only stats). This seems to work a lot better than anything I’ve come across, but I’m sure there is an easier way.
Thanks for the help.
Rick
Thanks for sharing, Rick. Yes, the Fangraphs “&page” trick works nicely on the leaderboards. I wish it would work the same on the projections. I have found that the Fangraphs pages changed once this year, but they are pretty stable and don’t change frequently.
Tanner, trying to create this link inside my SGP ranking sheet (from your excellent book!). Using Excel 2010, I can create the links but there is no option to import the ID sheet as a table. In fact I can’t even make it a table afterwards without deleting the links. Do you know of a solution for this?
Hi William,
Unfortunately it seems the table functionality wasn’t implemented into Excel until 2013. You won’t be able to create this as a table in the 2010 version, but you can still import the Player IDs so they’ll be “updateable”.
This does mean that downstream from the player ID map that your VLOOKUP or INDEX/MATCH formulas would have to change.
Instead of a table format like this: =VLOOKUP(@PLAYERID,PLAYERIDMAP,COLUMN(PLAYERIDMAP[POS]),FALSE)
You’d have to use something like this: =VLOOKUP([@PLAYERID],PLAYERIDMAP!$A$2:$AJ$1670,COLUMN(PLAYERIDMAP!$H$2:$H$1670),FALSE)
Your table references would revert to “old school” Excel address formats.
Tanner-
I’m having issues with the links in the PlayerID Map actually working. I don’t see the option to right click and Refresh on the spreadsheet. Any idea how to fix?
[…] Setting the Player ID Map Up to Easily Update […]