Welcome to the fifth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.
If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.
In this fifth part of the series we discuss updating the Player ID Map to pull new players into the rankings information.
Player ID Map
The SFBB Player ID Map contains the Fangraphs, MLB, Baseball-Reference, Retrosheet, CBS, NFBC, ESPN, Baseball Prospectus, Davenport, and Yahoo player IDs for over 1,200 players. It’s not a comprehensive list of past players by any means, but I make a concerted effort to have all current fantasy-relevant MLB players and those minor league players likely to make a fantasy impact this season.
To give credit where credit is due, I originally downloaded the player map from Crunchtimebaseball.com and tailored it to meet my needs. CrunchTimeBaseball is run by Tim Blaker. He continues to maintain his own map of player IDs and generally keeps his more up-to-date than I do. You can obtain his version here.
Why Update the Player ID Map
April inevitably brings us players that were never intended to be fantasy relevant; minor leaguers that were not anticipated to make the jump, role players thrust into starting jobs, and more. As the summer rolls on, impact rookies begin to get called up that may not have been in the preseason Player ID Map. When September arrives there will be more of the same.
While it is possible to add individual players to the player ID map, it can be inefficient to add more than a handful. Updating the entire list is probably easier.
|1.||It is important to save your Excel file at this point. If something goes wrong in the steps that follow, close the Excel file and don’t save it. This way you can return to the file exactly as it is right now.|
Download and open the most recent SFBB Player ID Map.
NOTE: When opening the downloaded file, you will likely be prompted to “Enable Editing”. If you trust that the Player ID Map is from a reliable resource (hopefully you do!), click “Enable Editing”.
|3.||Ensure that both the newly downloaded Player ID Map and the Player ID Map tab in your RoS rankings file do not have any filters applied before proceeding. To do this, click on the “Data” Tab, then the “Clear” button under the “Sort & Filter” section of the Office Ribbon.|
|4.||Return to the newly downloaded Player ID Map.
Click once on cell A1, then hit SHIFT + CTRL + END on your keyboard to select the entire Player ID Map. After everything is selected, release those keysThen hit the SHIFT key and the up arrow key on your keyboard at the same time.The purpose of this exercise is to select all but the last row of the Player ID Map. If you select an entire table in Excel and attempt to copy it into an existing table, the original table is blown away and is replaced by the name of the new table. This would destroy all the formulas currently in place looking to our original table.
But if we trick Excel by not selecting the entire table, our original “PLAYERIDMAP” named table will remain. I’ve added a fake player as the last alphabetical item in the table too, so not copying this row will not cause any problems.
|5.||Hit CTRL + C to copy the selected information.
Now return to the Player ID Map in your RoS ranking file.
|6.||Click on cell A1 of the PlayerIDMap in your rankings file and then hit CTRL + V to paste the updated information.
I will occasionally add new columns to the Player ID Map. If new columns exist, the width of your existing PLAYERIDMAP table must be adjusted to include them.
To do this, hit CTRL + END to be taken to the bottom right hand corner of the information on the PLAYERIDMAP. You might see something like in the image below. You can see that column Q is the end of the PLAYERIDMAP in Excel (where the blue and white shading ends), but you can also see that now the data goes into columns beyond that.To resize the table, place your mouse over the small symbol in the corner where the existing table ends. You should see your mouse cursor change to the symbol below, which indicates you can resize this.Once this resize symbol appears, click and drag with your mouse to right in order to select the additional columns of information.
You should now see that the formatting has spread to include all the data.
|7.||To double check that you performed these steps properly, go to the “Formulas” tab and click on the “Name Manager” button.In the ensuing list, you should see an entry for “PLAYERIDMAP”. If you see something like “PLAYERIDMAP1”, you might have a problem. Try starting this process over. Close the RoS rankings file WITHOUT SAVING (remember, we saved at the beginning of this part, so you will only have to reperform the steps in this section), and attempt to bring in the Player ID Map once more.|
This is an important step but the applicability of it depends on when you started following the site. Information was added to Player ID Map over time that can cause the minor issue displayed below. This step may be inapplicable to most people. If it does not apply to you, please move on to step 9.
To determine if this step is applicable to you, open the “Hitter Ranks” tab in your RoS rankings spreadsheet. Look at the columns to ensure the proper information is pulling into the proper column. For example, you might see something like this:You may recall that columns B-F are pulling information from the Player ID Map. Notice that the LNAME column is actually pulling in the first name, FNAME is pulling in a weird number (it’s actually a numerical representation of the player’s birthdate), and TEAM, POS, and IDFANGRAPHS are all incorrect.
Some small edits to the VLOOKUP formulas in these columns will fix the problem. Note the edits below in bold red font:
LNAME formula should be:
FNAME formula should be:
TEAM formula should be:
POS formula should be:
IDFANGRAPHS formula should be:
Repeat this step for pitchers. The formulas above will be the same for the “Pitcher Ranks” tab of your spreadsheet.
Helpful Tip: As you are editing these column names within the formula, you may see Excel provide you with a list of column names to choose from. Simply double click on the desired column and hit Enter.
|9.||You now have an updated the Player ID Map which should contain the players that have become fantasy relevant since the preseason. However, these players are not yet added to the “Hitter Ranks” or “Pitcher Ranks” tabs.To do this for hitters, return to the Player ID Map tab in our RoS rankings file.
Click on the drop down arrow on the “POS” column. You’ll be presented with a list of all the positions. Uncheck any “P” values. Click OK to apply.
TIP: If you play in an NL or AL only league, this becomes a very important step. In addition to unchecking any “P” in the “POS” column, you will also want to uncheck the league you don’t use in the “LG” column. For example, if you’re in an NL-only league, you would uncheck the “AL” option.
|10.||The Player ID Map will now list only hitters. Starting at the first player and ending with the last, click and drag within the “IDPLAYER” column (first column) and select all player (or select the first player and then use the keyboard shortcut SHIFT + CTRL + Down Arrow). Only select from this first column.|
|11.||Copy this selected data. Return to the “Hitter Ranks” sheet. Click once into cell A2 then paste the data (there is no need to delete anything before doing this, just paste right over the existing player list).This will put players back into alphabetical order. Sort them again by TTLSGP or $VALUE using the column header drop down menu.|
|12.||We must now add the updated pitchers from the Player ID Map to the “Pitcher Ranks” tabs.To do this for pitchers, return to the Player ID Map tab in our RoS rankings file.
Click on the drop down arrow on the “POS” column. You’ll be presented with a list of all the positions. Toggle the “Select All” button until all values become unchecked. Then check the “P” value. Click OK to apply.
|13.||The Player ID Map will now list only pitchers. Starting at the first pitcher and ending with the last, click and drag within the “IDPLAYER” column (first column) and select all players (or select the first player and then use the keyboard shortcut SHIFT + CTRL + Down Arrow). Only select from this first column.|
|14.||Copy this selected data. Return to the “Pitcher Ranks” sheet. Click once into cell A2 then paste the data (there is no need to delete anything before doing this, just paste right over the existing player list).This will put players back into alphabetical order. Sort them again by TTLSGP or $VALUE using the column header drop down menu.|
|15.||You now have an Excel file with an updated Player ID map, updated Hitter & Pitcher ranks listings, and updated RoS projections. The last step in the process is to reconsider replacement level. This is a very important step in creating updated dollar values. Failure to determine replacement level accurately can lead to misleading results.|
LINK TO DOWNLOAD EXAMPLE EXCEL FILE – RoS-Rankings-and-Dollar-Values-Part-5.xlsx
Do you have questions about Part 5? Or want to see what others have asked? Comment below.