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).
In this post I’ll show you how to add batter and pitcher handedness to your spreadsheets. To do this, we’ll have to learn two new Excel formulas we have not tackled yet.
I’ve avoided doing this for a long time… But there’s just no way around it now. It’s time to say goodbye to relying exclusively on VLOOKUP. Let’s put on our big boy pants and tackle VLOOKUP’s more flexible and powerful counterpart… INDEX and MATCH.
Sometimes VLOOKUP Can’t Get the Job Done
Take this scenario. You’ve started to build a DFS spreadsheet and you’ve imported FanDuel player salaries from a CSV file. Now you want to add player handedness (Lefty/Righty) as a column to your spreadsheet.FanDuel Salary Information
You are also aware of the Player ID Map and know that it’s an easy way to get handedness information on players.The Player ID Map contains information for batter and pitcher handedness, date of birth, team, position, and many player ID and naming systems.
You look at this data above and you think, “No problem!”. FanDuel ID is in both sets of data. How hard could this be? A simple VLOOKUP and we’re done.
But you quickly realize things are not that easy. You see, the VLOOKUP has a very restrictive assumption. If you are doing a VLOOKUP from the salary information into the Player ID Map, the Excel function assumes that “FanDuelID” will be the FIRST column in the Player ID Map.
And that’s NOT the case.
Let’s look at an example VLOOKUP formula:
=VLOOKUP([@FanDuelID],PLAYERIDMAP,10,FALSE)
In this formula we’ve told Excel to go look for the “FanDuelID” column in the “PLAYERIDMAP” table and give us back the value in the 10th column.
But “FanDuelID” is not the first column of the PLAYERIDMAP. It’s the 33rd (wow… the Player ID Map is getting to be quite large). So VLOOKUP will not work.
Other Weaknesses of VLOOKUP
Not only is assuming the data you want to match is in the first column awfully restrictive, if you think about it, VLOOKUP also ties you to a left-to-right lookup. For example, if you’re trying to use Excel to VLOOKUP which team Michael Brantley plays for, his player ID must be in the first column of your data set and you are then forced in to looking only to the right.
We want a formula that will allow our lookup to be in any column and then to look to the left! For example, go find Nelson Cruz’s FanDuelID and then look to the left a few columns and give me the side of the plate he bats from.
=VLOOKUP([@FanDuelID],PLAYERIDMAP,10,FALSE)
Going back to this example formula right above, the hard coding of a “10” in the formula to return the information in the 10th column is a flimsy approach, but that’s how many people are taught to write VLOOKUP formulas.
The flimsiness comes in if you decide to insert a column somewhere in the PLAYERIDMAP. If column 10 becomes column 11, Excel will not adjust its formula accordingly. Because you are likely building a spreadsheet that you’ll use all throughout the season, it seems highly likely you’ll want to add a new piece of information to your analysis. That inevitably means adding columns to bring that new information in. You don’t want to have to hunt through your formulas to figure out what the new column number in your VLOOKUP needs to be.
When I’m using a function I’m unfamiliar with, I will add it through the “Insert Function” button. I like doing this because Excel will then give you a search menu to find a formula. And after locating your function you’ll get a helpful wizard that breaks down all the inputs it needs.
If you follow that approach to add the INDEX function, you’ll soon realize there are two versions of it.
I always use the first version, which allows us to locate a cell anywhere within a block of data and return the value from that cell. This function uses the following inputs:
INDEX(Array, Row_num, Column_num)
Array – The range of cells you are searching for a value in. This could be a table or a block of cells.
Row_num – The row within that array that the value is in. This should be a number representing the row.
Column_num – The column within the array that the value is in. Again, this should be a number representing the column (not the letter representation of the column).
It may help to see a visual representation of the function. Assume we’re trying to find Nelson Cruz’s batting handedness. If we tell the index function to look in the PLAYERIDMAP (the “array”), in row 309 (“row_num”), and column 26 (column_num), it would return “R”.
We know the array to look in. And we can easily determine the column we want to look in. The challenge we now face is how to determine the row to look in… How do we easily determine that Nelson Cruz is listed on row 309. That’s where the “MATCH” function can help us.
MATCH
The MATCH function will look for a specific value in a range of cells. The function will return a number representing where the matched item falls in the list.
Translating that into English, a realistic use for the function is to look in an entire column for a match. The function will start at the top of the column and proceed down until it locates the desired value. The function then returns where the item falls in the list, which happens to be the row the item is in.
The function uses these inputs:
MATCH(lookup_value, lookup_array, [match_type])
Lookup_value – This is the value you are hoping to match in the array (or column). For us, this will usually be a Player ID of some sort.
Lookup_array – This is the area you are searching for the match within. When using the MATCH function with the INDEX function, this will usually be a column of data.
Match_type – This is an optional input telling Excel some more details about the kind of match you are looking for. You can enter a 1, 0, or -1. Entering a 1 or -1 are forms of approximate matches and are useful if you are looking up numeric values. But we are typically looking to match strings (I consider a Player ID made up of all numbers to still be a string) of text.
This means we want exact matches only. Accordingly, I always use a 0 for this argument (even though it’s optional, leaving it blank tells Excel an approximate match is acceptable).
Combining INDEX and MATCH
As I alluded to before, the power of these two formulas comes when you combine (or nest) them together. Recall that the INDEX function looks like this:
INDEX(Array, Row_num, Column_num)
If we drop the MATCH function in place of the “Row_num” argument:
We now have a formula that is more flexible and powerful than a VLOOKUP! The combination of INDEX and MATCH can look for a value anywhere in a table of data and we are no longer tied to the first column and a right-only lookup.
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!
I’ve been exclusively focusing on web querying Fan Duel to this point. But what about Draft Kings? Is there a way to web query their salary information? Here’s an e-mail I recently got from a reader of the site…
Hey Tanner, I just got back from vacation and saw some of your recent posts. That looks great… but I play on Draft Kings. When I try to do those steps on that site, it doesn’t work. No player names. No salaries. Any ideas?
There is no <Table> element on the Draft Kings salary info.
I followed the same web querying steps we went through in this post, “How to Use Excel to Web Query DFS and Other Fantasy Baseball Data”, using FanDuel’s site this time. And sure enough, no matter what web querying option you use… No dice. If you do an investigation using the “Inspect element” option for your browser, I believe the technical reason the web query is unsuccessful is because the salary information is not coded as an HTML table.
So things aren’t going to be as easy as the web query. But due to a nice feature on Draft Kings, we can get very close. It’ll take just a few extra mouse clicks to get to the same place.
You see, even running a FanDuel web query isn’t seamless. You still have to log into your FanDuel account in your browser, locate a contest ID (URL), and type it into your Excel file to kick off the web query.
One Extra Step for Draft Kings
While Draft Kings salary info can’t be web queried, they do offer the ability to export all player information from a given contest into a nicely packaged CSV file. The link for a contest appears just below the player list you sort through to select players for your lineup.
“CSV” stands for “comma separated values”. Many computer generated data export files have some kind of delimiter, or character, that breaks the data into columns. The CSV format specifically uses a comma to separate the values. Here’s what a CSV file looks like when opened outside of Excel:
That Looks Awful, How Do I Work With That File?
The CSV format does not look user-friendly when viewed outside of Excel. But fortunately, once you install Excel, it becomes the default program to open CSV files… And this format is so standardized, Excel is trained to clean up the data and put it in our friendly Excel format:
This Doesn’t Look as Efficient as a Web Query
Right about now you’re probably thinking something like this…
You mean to tell me each time I want to update my DFS baseball spreadsheet, I need to…
go Draft Kings,
find a contest to enter,
export the salary information,
open that CSV file,
copy the information,
and then paste it into my spreadsheet?
Not quite. Some of those things we have to do regardless. We can’t really automate the selection of the contest you want to enter. But we can have Excel automate steps four, five, and six (the process of getting the CSV data into a preexisting DFS spreadsheet.
Excel Data From External Sources
You might remember in the previous web querying article that we used the “From Web” option to “Get External Data” into our Excel files.
Fortunately, Excel also has an external data option for “From Text” (and a CSV file is a text file) that works on the exact same principles of a web query.
Just like how the web query is set up to pull information from a very specific web URL, this text file link can be set up to pull information from a very specific file path.
So as long as we save and name our Draft Kings CSV file in the same place each time, the data can automatically pull in to our DFS Excel files each time we open it.
Step-By-Step Instructions
Step
Description
1.
Log into your Draft Kings account (unlike for a web query, you can use any browser you wish for this).
Once you’ve logged in, locate a contest you’d like to participate in and click the “ENTER>>” button.
2.
After the contest loads, click the “EXPORT TO CSV” button below the player salaries (located on the left half of the contest screen).
NOTE: It does not matter if you check or uncheck the “ONLY PROBABLE PITCHERS” box. The export always contains all pitchers.
3.
Depending on the browser you’re using and its settings, one of three things will likely happen at this point:
The CSV file will automatically download to your pre-specified download location
You’ll be prompted to name the CSV file and choose where to save the file
Or Excel may open and display the CSV file immediately
Regardless of what scenario you find yourself in, the main goal here is to save the CSV in a location where you want it to stay going forward. Maybe that’s your desktop or maybe you have a “Fantasy Baseball” or “DFS” folder on your computer. Save the CSV file in that long-term location.
So if it automatically downloaded to your “Downloads” folder, move it. If you’re being asked where to save the file, point it to the desired location. If Excel launched, perform a “Save As” to the desired folder. If you have the CSV file open, close it now.
4.
Open your DFS spreadsheet (you may want to make a backup before proceeding) or start a blank Excel file if you’re just beginning (don’t start in the CSV file!).
5.
Go to the “Data” tab and click the “From Text” button.
6.
At this point Excel will prompt you to browse for the text file you want to import. Browse to and locate your CSV file.
Once you’ve located the file, select it and click the “Import” button.
7.
Excel’s “Text Import Wizard” will launch and ask which type of text file you’re importing. Remember that a CSV file is a special type of delimited text file that uses a comma to separate the values (a fixed width file doesn’t have a character (like a comma or semi-colon) separating the data, it would just have clearly visible spaces separating the data into columns). So choose the “Delimited” option.
Also check the “My data has headers” box and click “Next”.
On the next screen, choose the “Comma” delimiter option and uncheck any other options. You’ll get a preview of how the columns will be identified. Click “Next”.
On the final screen of the Text Import Wizard you can likely just hit “Finish”. For future imports, if you have a column containing something you only want treated as text or a date, you can choose those options. But “General” is fine for our purposes.
8.
Once you click “Finish”, you’ll be prompted about where you want to import the CSV data to in the Excel file. If you are working in your pre-existing DFS spreadsheet, click on the tab you want the text to import to and use the “Existing Worksheet” option, or just choose the “New worksheet” radio button.
Don’t click OK yet! Click the “Properties” button.
9.
There are many available properties to adjust, but I’ll call out a few I feel the most strongly about changing:
Prompt for file name on refresh – If you plan to save the CSV file in the same location and with the same name each time, you should uncheck this option. If left checked, each time you refresh the connection to the CSV file you will need to browse to and select the file. Conversely, if you don’t want the restriction of having to name the file consistently and in the same spot, you may want this checked. But you’ll then need to browse and point to the new file each time you want to refresh the link.
Refresh data when opening the file – As you can probably guess, if you check this box, each time you open your DFS spreadsheet it will link to and import the text file information.
Fill down formulas in columns adjacent to data – We may not need this, but if we end up needing formulas next to the data being imported, I would recommend checking this box.
Click “Ok” to accept the settings changes.
Then click the next “OK” button on the “Import Data” window.
10.
That’s it! The next day, when you go to prepare for a new contest, simply repeat steps 1 through 3.
In doing so, be careful to name or save the CSV with the exact same name and in the exact same location as your Excel file is looking to.
For instance, if you saved your first Draft Kings export as C:\Users\tanner.bell\Downloads\DKSalaries.csv, then make sure you save it there again the next day. If you have an older CSV already sitting in that location, the export will likely save as “DKSalaries (1).csv”. Not a problem. Just delete the older one and rename the new download to be just “DKSalaries.csv” (right-click on the file and choose “Rename”).
Web Query vs. CSV Import
I’m not sure which is superior or if there is even a clear winner. There is something elegant about the web query. And I really like how you can create a dynamic web query (I wish there were a way to do a dynamic text file link, but I can’t see a way to do that). But the web query is slow.
The text file import does require a couple of extra steps, in terms of naming the CSV file, but once that part is complete it’s essentially the same. The actual import of the data into Excel is much faster for the text file option (at times my web queries can take 30 seconds or more to complete).
For both options, Excel will seamlessly import the data and the rest of your Excel file can be built around this to automatically update based on the salary list.
Second Look at FanDuel
Having noticed this for Draft Kings, I took another look at FanDuel… Lo and behold, FanDuel has the same export option.
One key difference in the FanDuel CSV is that it has a unique file name associated with the contest ID.
So if you decide to go the CSV route for FanDuel, instead of using this unique name, rename/save the file with a generic name like “FDSalaries.csv”. If you also save the file in the same location each time, you would be able to link an Excel file to it repeatedly. At that point, just follow the setup instructions above and there is no difference in the setup of a FanDuel export and a Draft Kings export.
But Wait… What is that on the FanDuel Salaries List???
Oh my… This might be enough to convert me off the web query and to an exported CSV approach even for FanDuel (damn you Chris Youngs and Jose Ramirezes!).
If you happen to be using Excel on a Mac, the menu location to start the import process is slightly different. You can find it on the “Data” tab.
Or you can also find it under the Data menu.
Once you find these menu options, the settings are the same as above (for the most part). Make sure to click on the “Properties” button to get options about when and how to refresh the import upon opening the Excel file.
How Is Your DFS Spreadsheet Coming Along
Do you have any neat features you’ve added? Struggling to get some kind of information added? Feel free to post a message below to share what you’ve done or let me know your struggles and maybe I can help.
“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.
You might remember in the previous web querying article that we used the “From Web” option to “Get External Data” into our Excel files.
