Tools to help make you a better and smarter fantasy baseball player. These may be created by Smart Fantasy Baseball or links to smart tools created by others.
I’ve recently made some notable edits to the Player ID Map that should be helpful to many.
Added a field for “OTTONEUID”
Added a field for “HQID” (for Baseball HQ player IDs)
Updated “POS” column to reflect games played during the 2015 season. I determine which positions a player qualifies at using a 20 game minimum and then assign the player to ONLY his most valuable position. I assume position value goes C, SS, 2B, 3B, OF, and then 1B.
Added several new players
The Ottoneu ID column is almost completely full of IDs, there were very few players I had on my list that are not in the Ottoneu universe. In fact, I think it’s just the foreign players coming to MLB for the first time this season (Maeda, Park, etc.). The Fangraphs IDs for those players recently became available and they ARE now included in the map.
The Baseball HQ ID column is not nearly as populated. It only includes about 450 or so players that I think are included on one of HQ’s current year rankings files.
I’ve also added players like Zach Davies, A.J. Reed, Max Kepler, Tim Anderson, Orlando Arcia, Trevor Story, and Adam Conley.
Player Name
Fangraphs ID
Kenta Maeda
18498
Byung-ho Park
18717
Hyun-soo Kim
18718
Orlando Arcia
sa596917
Tim Anderson
sa737508
A.J. Reed
sa599279
Max Kepler
12144
Zach Davies
13183
Trevor Story
sa597765
Adam Conley
14457
As usual, if I’m missing a player or if you find an error on the log, please e-mail me or send a Tweet to @smartfantasybb with the information. I do try to keep the log current with “fantasy relevant” players. But that definition gets real cloudy when I start to consider DFS.
I’ll try to add these new ID systems to the Projection Aggregator soon, but I think HQ reports typically include “MLBAM ID”, so you can always use that instead.
If you have Excel 2013 and you’ve downloaded the “new” version of the Player ID Map, you can right click anywhere in that tab of the spreadsheet and choose to “Refresh” the connection. Excel will seamless download the new updates to your file.
Let me come clean. I screwed up. And it likely will cause you to see errors in your spreadsheets. That’s the whole reason for this post.
Having trouble with VLOOKUP error messages? This post should help.
What Happened?
While this post is going to address a very important topic (resolving VLOOKUP errors), there wasn’t much of a need for this until I came up with a new format for the Player ID Map. The intent was to make the Player ID Map easily updatable. I hate having to lookup the IDs, birth dates, and handedness of all the new players.
And it’s always bothered me that there was no easy way for you to get updated Player ID information.
Let’s be honest. It’s a pain in the ass. Especially this time of year when players are switching teams every day and minor league players we haven’t had to deal with in the past are now projected to reach the big leagues this season. It’s tedious to keep teams up-to-date and to add these new players.
I needed to find a way to improve this process and to make everyone’s lives a little easier.
The Solution
The solution was to make the Player ID Map available in an online CSV file. One you connect that online file to your Excel spreadsheet, you simply have to right-click on the Player ID Map and hit “Refresh”. You will instantly get any update I’ve made.
Sounds amazing, right?
Major leaguers have a purely numeric ID while minor leaguers have text in their ID.
The Problem
The fly in the ointment happens to be the way Fangraphs structures their player IDs. Major leaguers, like Jose Abreu, have a purely numeric ID. Whereas minor leaguers that have not reach the big leagues, like Yoan Moncada, have the text “sa” in front of a string of numbers.
The unintended consequence of importing the Player ID Map file is that because some IDs contain text, Excel will treat the ENTIRE imported column as text.
The problem is that reports you download from Fangraphs and then open in Excel treat the player ID column as numeric values.
Warning… It’s About to Get Technical
If you’re fine with the old Player ID Map and the fact that it doesn’t get updated very often, you don’t have to use the new one. The old one can be downloaded here and will still be updated periodically. You can stop reading this post and save yourself some sanity.
But if a little complication doesn’t scare you off and you see the value in being able to refresh the Player ID Map and get regular updates… Keep reading.
Text and Numbers Are Treated Differently
Excel and most other computer applications treat text and numbers differently. And this is a common problem with VLOOKUPS. So the number “15676” is not the same as a text string of “15676”. So in our VLOOKUPS, we need to make sure we are comparing numbers to numbers and text to text.
Consider the Error Message
The first step in resolving a VLOOKUP problem is to understand the error message you’re seeing.
The “#N/A” error is the most common VLOOKUP error. And it essentially means that a match was not found during the lookup.
There are two main reasons a match would not be found:
The item (player ID) doesn’t exist where you told Excel to look for it
Or you told Excel to look for the wrong data type (look for a text value in a list of numbers, or vice versa)
Abreu’s ID is the there. It’s in the first column. Why isn’t the VLOOKUP finding this???
You can easily test the first error by manually performing the search yourself. Let’s walk through a hypothetical example with Jose Abreu. He’s a well known player. He’ll surely be in the Steamer projections I’ve downloaded.
I see from the data that Abreu’s Fangraphs ID is 15676. If I trace that through into the Steamer Hitter projections, I am able to locate Abreu. So why isn’t the VLOOKUP finding the same match?
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.
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.
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.
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.
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.
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.
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.
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.A list of DFS player salaries and an abbreviation for the opposing team.
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).
A list of the day’s probable starting pitchers and their team name. How can we get this list of probable starters listed against the player salary list from above?
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.
Click the image to see the live web page of the Team 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).
Short and sweet update here… Columns for “FanDuelName”, “FanDuelID”, and “DraftKingsName” have been added to the Player ID Map (click to download the Excel file).
FANDUELNAME, FANDUELID, and DRAFTKINGSNAME columns have been added to the Player ID Map.
New to the site? Here are some past articles about how to use the Player ID Map in developing your spreadsheets (they focus on the season-long game, but the principles of using Player IDs or the map to account for differences in player names across sites still apply).
Please keep in mind that the Player ID Map is not intended to be all encompassing. I aim to keep all “fantasy relevant” players on the list. On choice days when a bench player or swing-man starting pitcher get the call, one might argue they become “fantasy relevant” to the daily game, but such players may not be included in the listing. I have to draw the line somewhere!
Now that the season is underway and you no longer need to exert so much energy on draft preparation, it’s a great time to read a book, check out in-season analysis tools, or try listening to new MLB or fantasy baseball podcasts.
I’ve broken the resources page down into segments for books, free tools, premium tools, and podcasts. Know of a great tool that I’m missing? Please let me know on Twitter.
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.
Fantasy Pros has the best ADP information I’ve found on the web. They have the most sources in one place and it appears to be updated regularly.
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.
After over a year of working on this and getting feedback from a very helpful group of SFBB readers, the “Projection Aggregator” Excel file is finally ready!
The Projection Aggregator is an easy to use Excel spreadsheet that can combine (or average) up to three different projection sets to give you the best possible set of projections to use for the upcoming season. You can use just about any well known projection source you have at your disposal. Download your favorite projections, fill out some settings, and you’re done.
No complicated formulas. No VLOOKUPs. Just download your projections, bring them in to the Aggregator, and you’ll have better projections in minutes. Click here to find out more.
I can’t believe it took me so long to think of this! In this post I’ll share a new Excel template I created to help calculate your SGP factors (or SGP denominators).
One of the very first posts I wrote on this site was about “Calculating What it Takes to Win Your League”. I’ve then gone on to write many articles about standings gain points and even about an improvement in the process I found by reading Art McGee’s book “How to Value Players for Rotisserie Baseball” (It’s quite expensive at Amazon but Baseball HQ has had it on “Inventory Closeout” for quite a long time).
So after a few years of writing about SGP it finally dawned on me that, “I should create an Excel file that wraps all this together.” So that’s what I’ve tried to do. The “SGP Calculator” asks a few simple setup questions about your league, the amount of history you want to use, and then uses the SLOPE formula to calculate the standings gain points for each of your league’s scoring categories.
Here’s How the “SGP Calculator” Works
The first tab is aptly named “ANSWER THESE QUESTIONS FIRST”. Several of these are very important in determining how the slope calculations are made so don’t skip over this.
The Excel file can accommodate up to eight different hitting and pitching categories, up to 10 years of standings information, and up to 15 teams. Use the drop down menus on this tab to select your league’s setting.
The next tab in the spreadsheet is “SGP CALCULATIONS”. Let’s skip over that for now and circle back to it.
The yellow colored tabs are where you will enter your league history of the overall standings and the totals in each individual category. There are tabs for “Historical Final Standings” (where you’ll enter the total ending roto points for each team), “Hitter Stats”, and “Pitcher Stats”.
There are 17 yellow tabs. One for overall standings and then eight each for hitting and pitching categories.
Each yellow tab displays the “Category Name” in the top left (see “Batting Average” example below). You can enter the history for that category in the table below. Because the spreadsheet has been set up to accommodate 15 teams and 10 years of history you may see several rows and columns with labels “DO NOT ENTER DATA”. Just ignore those rows and columns and only enter data for the years and number of teams you have selected.
The image is large but I had to shrink it to fit here. Click the image to see the full version.
Now we can return to the “SGP CALCULATIONS” tab, the end result. It displays the average statistics for each scoring category and uses Excel’s SLOPE formula to calculate the SGP factor (denominator) for each category.
Click the image to see the full-sized version.
These are the same SGP factors you would then plug into your customized rankings.
“The Process”, My Latest Book, with Jeff Zimmerman
The 2024 edition of The Process, by Jeff Zimmerman and Tanner Bell, is now available! Click here to read what folks like John Pausma, Phil Dussault, Eno Sarris, Clay Link, Rob Silver, Rudy Gamble, and others have to say about the book.
The Process is your one-stop resource for better drafting, in-season management, and developing strategies to become a better manager. The book is loaded with unique studies, tips, and strategies you won't find anywhere else. Click here for more details.
If you have Excel 2013 and you’ve downloaded the “new” version of the Player ID Map, you can right click anywhere in that tab of the spreadsheet and choose to “Refresh” the connection. Excel will seamless download the new updates to your file.
