Here’s a not so hypothetical scenario…
You have downloaded a CSV file of player salaries from DraftKings or FanDuel. You pull that information into Excel. Your goal is to take the “Opponent” information and use it to determine who each player’s opposing starting pitcher will be.
You have also followed this very brief set of instructions on how to get a list of starting pitchers into Excel that refreshes automatically each day (OK, not so brief).
The challenge is that the list of starters does not use the same team name system as the DFS salary information. This is but one example of this. If you ever try to combine information about MLB teams that comes from different web sites, you’ll likely find a number of other inconsistencies. Even the sites that use abbreviations (like the DFS info above), don’t use them consistently. Sometimes the Giants are “SF” and sometimes they’re “SFG”. The Nationals might be “WAS”, “WSN”, or “WSH”!
The Solution – a Team ID Map
To solve this problem, I have created an “MLB Team ID Map”. It’s similar in concept to the Player ID Map.
The map lays out the abbreviations (or team name, in Fangraphs’ case) from the following sites:
- Fangraphs
- Baseball Reference
- FanDuel
- DraftKings
- Yahoo!
- ESPN
- FantasyPros
- BaseballPress
- Baseball Prospectus
- Rotowire
Two Formats to Use the Team ID Map
The information is available in both a web page format (so you can web query it) and in an online CSV file (see instructions on how to use the CSV option later in this post).
Link to the Web Page Format:
https://www.smartfantasybaseball.com/TEAMMAPWEBPAGE
Link to the CSV File:
https://www.smartfantasybaseball.com/TEAMMAPLINK
Importing the CSV Team ID Map Into Excel
| Step | Description |
|---|---|
| 1. | Open the fantasy baseball spreadsheet you’re trying to add the Team ID Map too.
In your Excel file, click the button toward the bottom to insert a new sheet. Then right-click on the new sheet and choose the menu option to rename it. Name it something like “MLB Team ID Map”. Now copy this bolded web address: That’s the address of the CSV version of the Team ID Map. |
| 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 Team ID Map 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 to import the data. This is an important screen here. I want to import this as an Excel table, but 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. You might want to rename the connection to be something like “Team ID Map”. 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 Team ID Map in your spreadsheet and it will update automatically any time I make adjustments in the future (if I add a new site’s team abbreviations, they’ll show up in your file). 
|
Give Me An Example of How to Use This Thing
You’ll need to use Excel’s INDEX and MATCH functions to make the best use of the Team ID Map (here’s an example of INDEX and MATCH).
Sticking with the same example from earlier in this post, you can use INDEX and MATCH to take the “Opponent” column in the image above, go into the Team ID Map, and bring back the Fangraphs standard team name.
Then in the “OpposingSP” column in the image above, take the value in your “FangraphsOpp” column, go into the probable starting pitchers list, and bring back the name of the starter for that team.
Download a completed Excel file example of this Google Sheet CSV Link w Team ID Map.
Need Another Site Added?
Let me know in the comments below if you’d like team abbreviations added from another site.
Thanks for reading. Stay smart.
Great article, Tanner. I had figured out how to download the next day’s probables by changing url one day ahead. Two things. … the data I use is available only when I select custom data table on the bottom, not on the probables landing page, and two, fangraphs is not perfect in listing the probables. Oftentimes it will list a position player in place. But great article and I definitely will play with it.
Hi Jesse, thanks!
I agree, the stats shown on the probables page is not everything you’d want to see. I would probably suggest getting a more comprehensive stats list for all hitters and pitchers and putting them in your file somehow. Then don’t rely upon the stats just from the probables page.
And I can also throw in one more problem about the data you didn’t mention… I believe when a player is traded during the season, Fangraphs no longer shows a team for the player (because they have played for more than one).
But there are a few things that might help you work around this.
I need to play with this more to see if you can use this on other leader boards!
OMG. It works!
http://www.fangraphs.com/leaders.aspx?pos=all&stats=bat&lg=all&qual=y&type=8&season=2015&month=0&season1=2015&ind=0&team=0&rost=0&age=0&filter=&players=0&page=1_300
LOL…you tricked it. Phenomenal job. As to the gap where it doesn’t show the pitcher’s team if he’s been traded, on my excel spreadsheet I run a macro that searches against a master list for the player and returns their present team. So kind of a cheat there, lol…
What I do is I have all the batters and their stats (which I update from Fangraphs (FD) every month or so) on my spreadsheet, then go to MLB schedules.com and copy and paste the day’s slate (or the next day’s slate) to my spreadsheet. Then I go to FG and download the day’s pitching probables using the custom data button on the bottom, as I don’t use the data that they supply initially. I export, then copy and paste to my program, then run my program and it THEORETICALLY spits out the day’s best batters. I’ve gotten to the point where my exporting and copying and pasting really only take about 20 minutes, but I am always looking for ways to streamline.
I will sit down at home, with no distractions, and see how I cant take your fantastic info and fold it into my program. I am nowhere as proficient in excel or VBA as you are, as I kind of taught myself excel, and look forward to learning more from you.
Thanks again. I’ll let you know how I make out.
Jesse
Tanner, do you have any success with heat maps? Or, more specifically, batter and pitcher profiles? My next step in analyzing daily batters would be to match up the pitcher’s stats vs the batter’s stats via the hitting zones. I have been manually (ugh!) inputting the zone profiles posted on BroksBaseball.net to my excel spreadsheet, with each hitter’s and pitcher’s quadrant filling a cell.
I tried copying and pasting, but that only puts a picture of the heat map on my spreadsheet, rather than putting each number into individual cells. I know this is a longshot, but I think that would give us tremendous advantages (I would be happy to share my program with you) in analyzing the day’s plays. Do you think this can be done?
Fabulous resources you have, and I cannot wait for a quiet night where I can sit and analyze all you have.
Jesse
Sorry, it’s BrooksBaseball.net. My bad.
Ooops, sorry for not replying sooner Jesse. I’m going to e-mail you because you have a really specific question that we might need to have some back and forth on.
Really enjoying going through this spreadsheet building series. I have a question though…when I import the MLB TEAM MAP as a table….excel doesn’t recognize it as a table….can’t get formulas to work. I’m sure it’s something silly that I missed.
Hi Michael,
Glad you’re enjoying it! Looking closely at step #5 above, do you have that “Add this data to the Data Model” box checked? And then that “Table” radio button?
Depending on the version of Excel you’re using, those options may not exist. I think the Data Model options were added in Excel 2013?
If you don’t have them imported as tables your VLOOKUPs or INDEX/MATCH are still possible, but the format of the formulas changes quite a bit to the traditional Excel cell/range references.
Tanner
It was something silly as I expected….the name of the table was different for some reason…renamed it in excel and everything started working….Now if I could figure out how to use pitcher handiness to map hitter advanced stats splits…:)
Great, glad it’s working. I do have pitcher handedness in the Player ID Map. And I think I would download splits files from Fangraphs for vs LHP and a separate for vs RHP. Then set up a lookup to look in the appropriate place. You can download multiple year histories of split data from Fangraphs so you have a larger sample size you can feel confident in.
Thanks for this awesome tool! Retrosheets uses a different team naming system. It seems like it is exactly the same as BPTEAM except Saint Louis is SLN instead of STL. If you could add a new column for retrosheets that would be awesome!
I found their list here: http://www.retrosheet.org/CurrentNames.csv (it was on this page: http://www.retrosheet.org/Nickname.htm)
This should be updated now. Let me know if I screwed anything up!