It’s time! Are you getting the itch to start thinking about fantasy baseball again? Are ready to take on a new challenge this year and calculate your own rankings or create your own projections? All spreadsheet templates have been updated for the upcoming 2017 season. Take a look at the available books and tools below.
The Projecting X 2.0 Bundle comes with Mike Podhorzer’s instructional guide to creating your own baseball projections, as well as an accompanying Excel template to help save you hours and hours of time as you work through the projection process.
(NOTE: the Excel template requires you to enter certain formulas from the book, Projecting X 2.0. If you purchased the bundle prior to the 2016 season, this is being offered to save you the time of having to manually update the player names, teams, and positions in the spreadsheet in order to start projecting the 2017 season.)
An easy-to-use Excel spreadsheet that can combine (or average) up to three different projection sets. The aggregator can use just about any well known projection set you can find on the web (if you find one that doesn’t work, let me know!). Simply download your favorite projection sets, fill out some settings, and you’re done. No complicated formulas or VLOOKUPS for you to add.
Ever wanted to create your own rotisserie rankings? This is my instructional guide written specifically to show you how to create customized rotisserie player rankings, dollar values, and inflation dollar values, in Microsoft Excel, tailored to your own league. No more downloading rankings from the web, hoping they apply to your unique league. 10, 12, or 15-team league? $260 or $300 budget? AL-only or mixed league? 10 hitters or 14? It doesn’t matter. This book will guide you through the process of developing rankings for just about any kind of rotisserie league.
My step-by-step guide to building custom rankings, dollar values, and inflation dollar values, in Microsoft Excel, for your points league. This book will guide you through the process of developing rankings for just about any point-based scoring format.
It’s been awhile. But, yes, I’m still alive! And if you’re like me and itching to start thinking about and preparing for next season, you’ll be excited to know the SFBB Player ID Map has been updated.
The update includes additions of many players that entered the major leagues during the 2016 season as well as players projected to be impactful for the upcoming 2017 season.
In addition, players’ teams listed in the spreadsheet are updated for all transactions that occurred through December 6th. Finally, player positions have been updated to reflect games played during 2016. The position listed reflects the “most valuable position” played (if a player qualified at catcher and first base, he’s listed as a catcher).
I’m an SGP guy. Standings gain points are what I first learned. The approach has been good to me. And it seems I’ve been fairly successful using the approach. But SGP has a weakness. It’s a big weakness that prevents a lot of fantasy baseball players from using the approach.
Where Can I Get Reliable SGP Data?
Where can I find historical SGP data??? This is one of the most common questions I get about the use of standings gain points. If you’re starting a new league, don’t have access to league history, or switched website providers, you’re screwed. You can’t really start using SGP. And let’s not even mention those of you that play in AL or NL-only leagues (I still don’t have an answer for that, sorry).
In this post I’ll share with you where and how you can get great quantities of actual league standings in competitive mixed leagues (again, sorry mono-leaguers, I would love to help you one day but I haven’t found out how yet).
I got the idea to do this by reading Jeff Zimmerman’s fantasy draft prep series in 2014 and 2015.
Where Can You Find Standings Information for Competitive Leagues?
I haven’t proven the theory yet, but I’m pretty certain you could write some kind of web scraping program to pull down the standings information for public Yahoo! and ESPN leagues. But who knows what the level of competition is in those? You would have to find a way to weed out the non-competitive leagues and teams to prevent those that draft and then never change their lineup the entire season from distorting the standings information.
Enter the National Fantasy Baseball Championship (NFBC)
The National Fantasy Baseball Championship (NFBC) is the industry leader in premium fantasy baseball leagues. Meaning leagues that people pay an entry fee to join in an effort to win prize money.
The fact that people are paying money to enter these leagues and that prize money is at stake is the best mechanism we could hope for to ensure competitiveness. The standings information will not be tainted by schleps that draft a team and abandon in after the first week of the season.
Not only that, but the NFBC also publishes final league standings by category and makes them available to anyone! This is an SGP jackpot.
Different Types of Leagues
The NFBC has several different competitions. The two most likely to be of value to us are the “Online” and “Draft Champions” leagues. These leagues have the most entrants, so we can reduce concerns over small sample sizes. Here’s a summary of the two league types and links to the standings information for them:
Standard 23 player lineup (14 hitters, 9 pitchers, 27 bench spots)
Online drafts, November through April
Draft and hold, no free agency
No trading
So the big differences to note are that the “Online” leagues have 12 teams and a 30 round draft. The “Draft Champions” leagues have 15 teams and have 50-round drafts because they don’t have free agency during the season. We’ll a look at this in future posts to see if it seems to affect things.
Now That We Have This Information, What Do We Do Next?
There were 125 leagues and 1,500 teams in the 2015 Online NFBC leagues and 192 leagues and 2,880 teams in the 2015 Draft Champions leagues.
That’s a lot of data. Is there a practical way to take all of that data and use it to calculate SGP factors? Of course!
You’re Boring Me and I Don’t Want to Do This Myself
NOTE: I’m about to go through instructions how to calculate the NFBC SGP numbers yourself, but if you just want my completed analysis, you can download them here:
I may not update this information every year into the future… So remember, the instructions below will remain so you can do this yourself!
Excel Functions Used in this Post
We’ll be using the SLOPE, IF, and AVERAGEIFS formulas to calculate SGP for the NFBC leagues.
SLOPE
You can read more about the SLOPE formula in a three part series I wrote about here, here, and here.
The short description is that the SLOPE function finds the line of best fit through a given set of data points. With our rotisserie standings data, the SLOPE formula essentially calculates the actual SGP factor or denominator. I’d highly suggest reading the three part series. Or at least Part I!
IF
The IF function checks to see if a condition is met. If the condition is met, the function returns one response. If the condition is not met, the function returns another response. One important fact to realize is that the responses you specify in the IF formula can be formulas. So if the condition you specify is met, you can have the cell use formula A. And if the condition you specify is not met, you can have the cell use formula B.
The function requires three inputs:
Logical_Test – This is typically a formula to be evaluated. An example might be “is cell C2 greater than cell D2”.
Value_If_True – This is the value to be shown or the formula to be evaluated if the Logical_Test is passed,.
Value_If_False – This is the value to be shown or the formula to be evaluated if the Logical_Test is failed.
AVERAGEIFS
The AVERAGEIFS formula will calculate the mean of groups of cells that meet a set of conditions. You can specify multiple groups of cells and multiple conditions that must be met. The function requires three inputs (but can use more…):
Average_Range – These are the cells to be included in the calculation of the average
Criteria_Range1 – This is the first set of cells you want to be evaluated for the condition
Criteria1 – This is the condition that must be met for the item in the Average_Range to be included in the calculation of the average
If you have more conditions to be evaluated, you can continue to add pairs of Criteria_Range2 and Criteria2, Criteria_Rang3 and Criteria3, etc.
This is a little vague until I tell you more about how we will design this spreadsheet to work.
Our goal will be to design a spreadsheet containing a separate tab for each rotisserie scoring category.
And one tab that will analyze each scoring category and calculate the average needed to finish in each place for that category. For example, this table will show what the average batting average was for each of the 15 places in an NFBC Draft Champions league.
Each cell under the roto categories will contain an AVERAGEIFS formula. For example, the table tells us that first place in the Batting Average category had an average of 0.277. The formula in this cell is set up to look on the “BA” tab for the batting average of each team (the Average_Range), then look in the “Place in League” column (the Criteria_Range1) for any rows with a “1” in them (the Criteria).
That 0.277 calculation is the average of all (and only) first place teams.
Step-By-Step Instructions to Calculate SGP for NFBC Leagues
In the instructions that follow I’ll be calculating the SGP factors from the 2015 NFBC Draft Championship standings data.
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.
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?
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)
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?
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).
In this post I’m going to address two common questions I get about creating daily fantasy baseball spreadsheets:
Where and how can I download today’s AND tomorrow’s projected starting pitchers?
Why I don’t see the yellow arrow when trying to web query a site in Excel?
And in addressing those two questions, we’ll also take a look at a powerful tactic of using Google Sheets and Excel together to get baseball data off the web. We’ll be focusing closely on obtaining a list of projected starters, but the concepts behind using Google Sheets and tying that back into Excel is one that can be applied in many other areas (like creating spreadsheets for your season long leagues).
Where Can I Find a Reliable and User-Friendly List of Probable Starting Pitchers?
We all know DFS is exploding and there are countless sites out there providing lineup information, alerts, weather data, and more. But unless I’m looking in the wrong spot, most of that information is intended for that day’s games. And as a father of two with a day job, I can’t practically create a lineup the day of a contest. I need to prepare a day in advance for the next day’s games.
The other challenge in finding this information is that it will be a lot easier to deal with in Excel if we can find the data in a table format (see image to the right, I won’t bore everyone with technical details, but just because data looks to be in columns and rows on a site, doesn’t mean it’s in the format Excel can handle easily).
I have struggled and struggled to find a good resource for tomorrow’s projected pitchers. AND IT HAS BEEN RIGHT IN FRONT OF MY FACE ALL SEASON! Take a look at the Fangraphs home page:
If you visit the “Probables Leaderboard” (here’s an example link), it looks perfect. A table of all the projected starters, and even some friendly advanced metrics we could use in evaluating each player.
Now take a look at the URL for the page:
I started to write this post on September 4th. And when I clicked the “Probables Leaderboard” link, it took me to the “p2015-09-04” web address. You can see that last part simply reflects the current date.
Anytime you see a URL like that, with all the different arguments and parameters (like “pos”, “stats”, “lg”, “season”, etc.), you should get excited. It likely means you can manually type in values for those parameters and create your own “query” of the site. Here’s an example I wrote awhile back using Brooks Baseball to illustrate these concepts.
So instead of just using the “p2015-09-04” address, I tried “p2015-9-5”. This was to test two different things. First, to see if I could get tomorrow’s probables in the same table format. Second, to see if the zeros before the month and day numbers were important… And it worked!
So not only do we have a reliable list of probable starters, we can also get the projected starters for days in advance!
We Need a Dynamic Web Query
While it’s great that we now know where to get tomorrow’s probable starters, the fact that the URL changes each day is a challenge. We’ll need to create a dynamic web query that can determine tomorrow’s date and download the data from the appropriate web address.
With this in mind, I brushed up my memory on how to create a dynamic web query (look for the section titled “Step-by-Step Instructions, Dynamic and Updating Web Query”) and started the process of building it in Excel.
Why Don’t I See the Yellow Arrow in My Excel Web Query Window?
Everything was going so well until I hit a common stumbling block that occurs when web querying in Excel. No yellow arrow displays on the table of data I want to capture in my web query.
Why does this happen? One definite cause is if the information isn’t really in HTML table format (remember that image above?). But the Fangraphs table is in fact a table. I checked. I don’t have a great explanation as to why you don’t always see the yellow arrow, but I imagine it has something to do with how the table is coded or just Excel’s ability to properly process it.
But if you do in fact see that the data is stored in an HTML table, Google Sheets offers a very simple method of doing a web query. One that works even when the yellow arrow box is missing!
I would like to. But the dynamic web address tripped me up. I spent three days trying to figure out how to get it to work and was unsuccessful.
I ultimately realized that I knew a much easier way to do this with Google Sheets, and this is something I’ve been meaning to demonstrate for a long time. So rather than continue to waste time trying to get Power Query to do the job, why not go with something I already know?
The ultimate irony of the situation is that Power Query didn’t have a problem importing the probables! If I could only have gotten a dynamic query to work…
Enter Google Sheets
If you’re not familiar with Google Sheets, it is a very strong spreadsheet alternative to Microsoft Excel. And it’s free.
So why don’t I write more about using Sheets? Quite frankly, Excel is the better product. It is much more powerful and responsive, largely because it’s an application that runs on my own computer. Google Sheets is web-based and suffers from performance limitations and access issues because of it (if you have a slow internet connection or a lot of calculations in your spreadsheets, you’ll drive yourself crazy using Google Sheets).
With that said, there are some really interesting benefits to Google Sheets. Being free is hard to beat. It’s very easy to share a workbook and work on the spreadsheet at the same time as others. And as I mentioned, importing HTML table data is a snap!
Another really neat feature is that you can publish (or share) the results of a spreadsheet online in CSV format.
And a file in CSV format is easily importable into Excel!
So we can create a Google Sheet to web query troublesome table data. Publish that data as a CSV. And then use Excel (and even Power Query) to import the data into our master spreadsheet.
Let’s get started!
Prerequisites
To use Google Sheets, you need to have a Google account (if you use Gmail, Google Drive, or any other Google service beyond searching the web, you already have one). If you don’t have a Google account you can create one from the Google Sheets sign up page here.
Google Sheets Functions Used in This Post
IMPORTHMTL
In Excel, we set up a special connection to pull information from a website. Things are much simpler in Google Sheets. You enter a very simple formula and the data gets pulled into the document.
The specific function we’ll use is “IMPORTHTML”. The function has three inputs:
URL – Enter the web address of the page to be queried in quotation marks. In our example, it will be the address of the Fangraphs Probables page.
Query Type – This is the data type you wish to pull from the web page. You can enter either “table” or “list”. Similar to what we look for when doing an Excel web query, we most likely will be using the “table” option.
Index – This is the instance number of the table (or list) on the web page. Google’s documentation says the index begins at 1, meaning if you want to query the first table on a page you would simply type a 1. If you want the fourth table on a page, you’d enter a 4. But for some reason using a 0 is what works for the Fangraphs page we’ll be using.
MONTH, DAY, and YEAR
These are three separate functions. Each is looking for one input, a date.
The MONTH function will return the numeric representation of the month in the date. DAY returns the numbers from the date string corresponding to the days. And YEAR returns the numbers of the year in the date.
Going back to our example date string from earlier, a formula of =MONTH("09/04/2015") will return “9”.
TODAY
The TODAY function requires no inputs. And when used it simply returns today’s date.
For example, if you enter the formula =TODAY() and look at your spreadsheet on September 5th, 2015, your spreadsheet will display “9/5/2015”.
The formula updates when your spreadsheet recalculates. So if you opened the spreadsheet the next day, the formula would display “9/6/2015”.
You can perform addition with the TODAY function. So if you wanted to display tomorrow’s date, the formula would be =TODAY()+1. Or a week from now would be =TODAY()+7. Knowing that we can add one to the TODAY function will be important to finding tomorrow’s probable starters.
CONCATENATING or BUILDING TEXT STRINGS
By now you probably realize that we’re going to take the beginning of that long Fangraphs URL and then attach the date, as calculated by the TODAY function, to that. Every day these formulas will update and automatically create the new URL to determine tomorrow’s pitchers.
To attach two strings of text together in Google Sheets (or in Excel), you can use the ampersand (“&”). For example, we could put tomorrow’s date in cell A1 of a spreadsheet and then use this formula to build the Fangraphs web address:
Step-by-Step Instructions – Create a Google Sheet and Use the IMPORTHTML Function
Step
Description
1.
Go to the Google Sheets home page and click the button to start a new blank spreadsheet.
Click on the “Untitled spreadsheet” title and give the file a better name. Maybe something like “Tomorrow’s Probables”.
2.
Next, we’ll use the date formulas previously discussed to build the date string to attach to the Fangraphs probable starters URL. Enter the following formula in cell A1:
=YEAR(TODAY())
This should result in just the year of today’s date. As I write this post in September of 2015, the formula returns “2015”.
Now we’ll continue to build on this formula. Add the following to the existing formula in cell A1:
=YEAR(TODAY())&"-"&MONTH(TODAY())
Hit ENTER to accept your changes. See how the ampersand is used to add the hyphen and then another ampersand is used to add the month? As I write this post, that last formula results in “2015-9”. We’ll continue to use the ampersand to add new pieces of text to this string.
This last piece puts in one more hyphen and then the current day of the month. In my example file it’s showing “2015-9-5”, which is the exact format we need for the Fangraphs page.
But remember, we want to show tomorrow’s date. Not today’s. So make these last final adjustments:
The reason we have to add one to all three pieces of the date is to account for when you reach the last day of a month. If you don’t add one to the month component, your day would reset to “1” but your month would still be lagging one behind (e.g. If it’s August 31st and I don’t add one to all of the today formulas, my formula would results as “2015-8-1”, not “2015-9-1”).
3.
We’ve completed the last date piece of the Fangraphs web address, so let’s create the full address to the page so that it will update dynamically. Visit the Fangraphs probables page (here’s a link you can use that will lead to an old date).
Use your mouse to select all but the end of the URL that contains the date (get the “p” though!).
Copy that URL. Then return to your Google Sheet. In cell A2 type an equal sign then a quotation mark:
="
Then paste the Fangraphs URL and close it with another quotation mark:
Hit ENTER to complete the formula and you should see a fully usable hyperlink that will take you to tomorrow’s probable starters.
To test the hyperlink, hover your mouse over it and then click on the popup that appears.
4.
There are just two more inputs needed for the IMPORTHTML function. Type TABLE into cell A3 and a zero into cell A4.
Now click the downward pointing arrow on the sheet name at the bottom of the screen and then choose the menu option to “Rename…”.
Give this tab or the spreadsheet a meaningful name, like “IMPORTHTML Inputs”.
5.
Now click the “+” sign, to the left of this newly renamed tab, in order to start a new sheet.
Click the downward pointing triangle on this new sheet and rename it to something meaningful, like “Probable Starters”.
6.
Click your mouse into cell A1 and enter the following formula:
If you named your first tab exactly the same as I did, you can copy and paste the formula above into cell A1. Or instead of typing out the formula, you can click to your “IMPORTHTML Inputs” tab and select the applicable cells.
Hit enter to accept the formula. After several seconds (depending on the speed of your internet connection), you should see the probable pitchers load!
You can see that it’s very easy to pull data from the web into Google Sheets. Much easier and with fewer steps than in Excel.
7.
Before we go on, think for a moment about how an Excel spreadsheet runs its calculations. Similar to Google Sheets, Excel has a TODAY calculation. But if the Excel file containing the TODAY formula was closed for an entire week, we wouldn’t expect that the TODAY formula was updating each day in that closed spreadsheet.
We face a similar problem with this Google Sheet. We don’t want to have to open this list of probable starters each day just so it can update the list. It would be great if there were a property we could turn on so that the spreadsheet would refresh itself every so often… And fortunately Google offers this feature!
Within the Google spreadsheet, go to the “File>Spreadsheet Settings…” menu.
In the ensuing menu, adjust the “Recalculation” setting to the “On change and every hour” setting. This means the spreadsheet will reevaluate the TODAY formula each hour and update the list of probable starters accordingly.
Click the “Save settings” button to accept this change.
8.
The last task we need to complete in the Google Sheet file is to publish the list of starters as an online CSV file.
To start this process, click on the “File>Publish to the web…” menu.
Click the drop down that currently says “Entire Document”.
Then choose to only publish the “Probable Starters” tab.
Now click the drop down that says “Web page” and change it to the “Comma-separated values (.csv)” option.
Click the “Publish” button to complete your changes.
9.
After you click the publish button, the menu will change to display a link to the published CSV file. Copy this link for now. We’ll need it in the next section. In fact, you may want to copy and paste it into a Word file or some other place for easy access. We will use it again a couple of times.
You can always return to review or change these settings under the “Publish to the web…” menu. Just click the “Stop publishing” button, reconfigure the settings to your liking, then republish the document.
Google Sheets Wrap Up
Now you see how much more simple the “web query” is in Google Sheets. Especially the creation of a dynamic query that can grab the results of a different page each day with no need for us to update or even open the file! When a new day rolls around, the probable starters list will automatically update in the Google Sheet and in the published CSV file.
The ease of importing data is a huge benefit of Google Sheets, but on the whole I don’t find it to be up to par with Excel. So now let’s take a look at how to get this CSV file into our daily fantasy baseball spreadsheets.
Step-by-Step Instructions – Import a Published CSV File Into Excel
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.
You are also aware of the Player ID Map and know that it’s an easy way to get handedness information on players.
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.
In this post I’m going to put a slight twist on the Draft Kings CSV salary file import we discussed recently and show you how to import the data into an “Excel table” (or sometimes referred to as an Excel structured reference).
If you’ve followed any of my previous “how to” sets, you know I’m a big proponent of using these Excel tables. They give a number of benefits:
Efficiency – When you add a formula to a cell, Excel automatically copies that same formula to all other rows in the table. No more copying and pasting and scrolling around to copy your formulas.
Consistency – Reduces the likelihood of an error in your spreadsheet by making sure formulas in a column are identical.
Easier to Build Formulas – Your table becomes part of Excel’s reference system giving you useful type ahead features
More Reliable Formulas – Because of the naming and reference system, formulas can better adjust when rows/columns are added or deleted. Ever had a VLOOKUP formula fall apart after you added a column to your spreadsheet? Or how do you think your spreadsheet will respond when you import a large list of players for a big slate of games and the next day you play a small slate of afternoon games? Setting the range of salaries as an Excel table allow formulas to adjust automatically.
Meaningful Formulas– Structured reference formulas inherently have more meaning to them than standard formulas not using structured references. Look at these two examples…
While the first formula is shorter in length, it’s also shorter on meaning. You can’t look at the formula and easily determine its purpose. You can easily look at the second formula to see it’s trying to locate the PLAYERID in the PLAYERIDMAP table and return the FIRSTNAME of the player.
I think this is a HUGE benefit…
Is This Really Necessary?
Necessary? Probably not. An improvement? I think so. Since discovering Excel’s structured reference system, I’ve been using it on all my spreadsheets. I was excited to find a way to do this while importing data from an outside source. I didn’t know it was possible… I just wish we could get it to work with a web query too!
Step-By-Step Instructions
Here are instructions to bring in a Draft Kings or FanDuel CSV salary listing into an Excel file as a table. I use FanDuel in the example, but this can just as easily be performed with a Draft Kings CSV export.
Step
Description
1.
Log into your FanDuel account and download the CSV relating to the contest you wish to enter.
To do this, choose the contest you wish to enter. After identifying the contest, click the “Enter” button.
Once the contest loads, scroll to the bottom of the player salary list and click the link to “Download player list”.
The file you download will have a unique name. Recall from our previous post on using CSV files that we will be better off to change this to a more generic name, like “FanDuel.csv”. We can then use that generic name going forward and instruct Excel to import “FanDuel.csv” each time it opens.
By saving the file in the same spot and using the same name each time, Excel can seamlessly open and update the salary information with us not having to perform the CSV import each time.
So, after you have renamed it, save your “FanDuel.csv” file somewhere you don’t mind it residing in the future.
2.
Start a new Excel file or open the file you wish to add the FanDuel salary list to.
On Excel’s Data tab, click the “Get External Data From Text” button.
Then browse to and select your “FanDuel.csv” file. Then hit “Import”.
3.
At this point, Excel’s “Text Import Wizard” will open, asking you which type of file you’re importing. Choose the “Delimited” option and be sure to check the “My data has headers” box.
At step 2 of the import wizard, check the “Comma” delimiter box and uncheck any others. Click “Next”.
No changes are necessary at step 3. Just click “Finish”.
4.
This is where we diverge from the previous instructions. To format the imported data as an Excel table, check the “Add this data to the Data Model” box (other options on this screen will be grayed out until you check that option). Then ensure the “Table” radio button is selected under the “Select how you want to view this data in your workbook” area.
Click the “Properties…” button.
5.
In the past, I’ve suggested you check the “Refresh data when opening the file box” at this point. But don’t do that yet!
When I check that box at this point, my Excel file gets an error each time I open it. I get the sense the connection is still working, but the error bothers me. In researching the error, it seems others have experienced the same thing. The simple workaround to the error is to not check that box now, and just come back and check it later in the process.
I would recommend unchecking the “Prompt for file name on refresh box”.
Click “OK” to close the “Connection Properties” settings. Then click “OK” to close the “Import Data” settings.
6.
Your import should be complete! You’ll know that Excel formatted your data as a table if it’s somehow shaded with alternating colors.
You can change the colors under the “Table Tools Design” tab that should appear on the Excel ribbon when you click within the table.
7.
After the import is done and you’re satisfied with the color, go to Excel’s “Data” tab and click on the “Connections” button.
In the list of “Workbook Connections” that loads, choose your FanDuel connection and hit the “Properties…” button.
Now check the “Refresh data when opening the file” option that we purposely held off on earlier.
8.
To test the connection, close your Excel file. Then browse to and open the “FanDuel.csv” file.
Make an obvious edit to a player’s salary (for example, change Clayton Kerhsaw’s salary to “99999”), save your changes, and close the CSV file.
Note, your CSV file may open in Excel. You can still edit a player’s salary. When you go to save the file, you’ll get a series of annoying messages about “Do you want to keep using the CSV format?”. Just be sure you’ve saved the file and say “Yes” to those questions. You’ll get prompted with the same set of questions when you close the CSV file.
9.
Now open your Excel file and verify that the salary change you made flows through. You might first get a security warning from Excel that data connections have been disabled. Click the “Enable Content” button and watch the data below update.
My data files sometime import in a strange order. You may have to use the “Salary” drop down menu to sort by salary in descending order (to see Kershaw at the top).
Changing the Name of a Table
Now that you’ve created an Excel table, you an edit its name and other properties on the “Table Tools” tab. This tab is not always visible but should appear when you select a cell within the table.
Once you’ve selected the “Table Tools” tab of the ribbon, you can change the name of the table under the “Properties” icon set all the way to the left of the tab. Remember, a great deal of the value from using structured references is the ability to use type ahead formula building and to have meaning in your formulas. So avoid names like “Table1” and go for things like “FanDuel_Salaries” or “Table_FanDuel”.
Using Type Ahead in Formulas
Once you’ve set up a table, the type ahead features immediately activate with no effort needed on your part. To see the type ahead in action, just start by typing the name of your table in a formula.
You can see in the image below that just typing “Ta” (for “Table_FanDuel”) pulls up the table. You can then use your mouse to double-click on one of the items in the list or use your arrow keys to select one and then hit the “Tab” key to select the highlighted item.
Once you have completed adding the table name, an open bracket (“[“) will then present you with a list of field names, all of which you can cycle through with the up and down arrrow keys and then hit Tab to select a column/field.
Wrapping Up
A little bit of a personal story here. I occasionally give Excel trainings at work to people that know their way around a spreadsheet pretty well. Most could even rattle off a VLOOKUP formula without much thought. I show them Excel tables and structured references and everyone’s eyes glaze over and they look like a bunch of deer in headlights.
I get it. The structured reference thing is new. If you already know how to do a VLOOKUP it’s hard to see the value in changing. Why take the time to do this?
Keep in mind that I try to design spreadsheets that will last for a long time and be reusable. At least for one season and possibly into future seasons. You could easily just get caught in a cycle of whipping up an inferior spreadsheet each time you want to create a lineup. But I would rather invest the time to build a long-term tool on a strong foundation. This way I can save time each night by having a prebuilt tool and because it’s built on a strong foundation I can continue to add new data, projections, and features over time.
And don’t you just love the pretty alternating row colors????
The SFBB way is all about doing things yourself, building things the right way, and continually improving and learning new things. So take the time to play around with structured references and learn the language.
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).
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?
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.