Whether it’s creating your own projections, averaging them with other popular projection systems, creating your own rankings and dollar values, or calculating points-league player values, now is the time to start preparing for the 2016 season.
Whether it’s creating your own projections, averaging them with other popular projection systems, creating your own rankings and dollar values, or calculating points-league player values, now is the time to start preparing for the 2016 season.
About a week ago I got an e-mail from a reader of the site asking me for help using “Power Query” to pull some Fangraphs data into Excel. Power Query is an add-in for Microsoft Excel that offers more advanced data importing options and ability to combine data from different resources.
I knew Power Query existed. But as I was reading the e-mail, my palms began to sweat and an overwhelming sense of guilt washed over me.
“I don’t know anything about Power Query!!!”
Coincidentally, my wife was out of town for the weekend and with the girls in bed early, I had a handful of hours on Saturday night to give myself a crash course in how to use Power Query (ah, the exciting and glamorous nightlife of a baseball nerd!).
And now I might be hooked.
I recently wrote a post (about importing a CSV file into Excel) that included a list of benefit to using Excel tables.
But I missed a really important one… If you import your data into Excel as a table, you create a connection to the data that is linked and can be updated automatically.
Let that sink in for a minute.
I’ve shown you how to make a lot of the Excel file’s that are isolated, dead, and not directly linked to any outside information.
I might have you download some data. Then copy and paste it into Excel. And then convert it into a table. But this is not ideal. The only way to update that information is to manually download it, open the file you downloaded, copy the data, paste it into your file, and cross your fingers that none of your formulas break when you paste over the top of everything.
If we can start importing data directly into Excel as tables, rather than copying and pasting data manually, we can maintain the link to the original data and then very easily update it in the future. And Power Query is capable of helping us do that. It gives more options to create live links to data sources and better options to manage those connections.
Imagine not having to rebuild a new rankings and dollar value file from scratch EVERY season. If you set the file up intelligently, you can use the same file to quickly get in-season values or to update the file for the next season in only a couple of minutes.
In my limited use of Power Query so far, the thing that has me most excited is that it gives you the ability to import more data sources as tables. We have previously looked at how to use web queries to get information into Excel, but if you use a basic web query, the information does not come in as a table.
Granted, a web query does still leave a live link to the original data. But I want the best of both worlds. I want a live link to the original data AND to import it as a table!
When using a standard web query (outside of Power Query), you do have the option to import the entire web page. This is messy and loaded with complications, but it’s helpful to have the option.
There is no such option in Power Query. You can only use Power Query to web query actual HTML tables from a web site. My educated guess is that to set something up as a table in Excel requires a neat and structured block of data, which querying an entire web page is not.
This is unfortunate, because some really great sites like Baseball Press don’t use tables to present their data. Instead, they use the division (< DIV >) HTML tag.
Despite Power Query not being the silver bullet we need to resolve all our data needs, it’s definitely a tool worth having in the arsenal. And it’s free!
You can download the add in from this page. There are some restrictions you should know about. The program requires at least Windows 7 (sorry again Mac users… you should really look into partitioning your Mac to run Windows).
You also need to be running Excel 2013 (any version) or Excel 2010 “Professional Plus”. After you’ve downloaded the installation, close out of Excel and proceed through the installation. The new toolbar on the ribbon should appear the next time you open Excel. If you’re not seeing a “Power Query” tab, you may need to activate the add-in. Check out the instructions here on how to turn on the add-in (look for the section labelled “My Power Query Tab Disappeared”).
If you’ve read any of my previous pieces on web queries, this really isn’t that much different. The improvement is with the data being in a table and some additional capabilities to fine tune the data that is imported. But let’s take a closer look at how the basic functionality changes.
|1.||The first task is to identify a web page you want to query AND to determine that it does contain HTML tables. My excitement over Power Query is tempered some by that fact that it is difficult to locate useful resources that put their data into tables. Many valuable sites and specific pages don’t!
Remember, to determine if data is in a table, right click somewhere on the web data you would like to capture and choose the menu option to “Inspect Element”.
This will load the HTML “code” used to create the web page. If you see references to
A few examples of potentially helpful tables that I’ve found:
After you have located a table to import, copy the web page address. For my example, I’ll use the Fantasy Pros rest of season projections (link is http://www.fantasypros.com/mlb/projections/ros-hitters.php). I realize this is not useful for DFS, but I just want to demonstrate the basics of Power Query now.
|2.||Open a blank Excel file. Click the newly added “Power Query” tab. Then click the “From Web” icon on the left of the ribbon.
Then paste the copied URL into the dialog box and click “OK”.
|3.||The “Navigator” dialog will appear. It may take a minute or two to load as Excel processes each of the tables on the page.
Once the loading process completes, you will see a list of all the tables available for import. Click your mouse to locate the data you want. If you wish to import more than one table, check the “Select multiple items” box.
As you click on the various tables, watch the preview pane on the right in order to locate the exact table you want.
|4.||Stop! This step is informational only. Don’t do anything!
At this point, you could click the “Load” or “Load To…” button.
The “Load” button will import the data exactly as you see it into a newly created worksheet tab.
The “Load To…” option gives you a few more control over how the data loads. In the ensuing menu you have the option to import the table (recommended) or only add the connection to your file (not sure why you would want to do this unless you were unsure of where to place the table now). You can also choose to create a new worksheet or to place the table in an existing spot. Working with data models is something I may explore in the future. If you want to look ahead, you can start here.
Loading from here bypasses some of the real value that Power Query offers. These features are available when you click the “Edit” button.
|5.||Start! You can start following along again.
Click the “Edit” button and the “Query Editor” will load. In this screen we not only see the preview of the data that will be imported, we can also clean things up.
For example, the first column is labelled “VBR”. This looks like some kind of a ranking, but I don’t want to import this. Additionally, the second column has a lot of information in it. Instead of seeing “Mike Trout(LAA – CF)” all in one column, I want to try breaking that into separate columns.
|6.||Now let’s move on to splitting apart the player name, team, and position.
Click once on the “Player” column. Then click the “Split Columns” button.
And then click the “By Delimiter” option.
A delimiter is a unique character that represents a change in the field or information. Looking at the data we have, the opening parenthesis is a delimiter between player name and team. There is no option to choose that from the drop down menu, so instead select the “–Custom–” option.
Then type in the open parenthesis, “(“, and select the option for “At the left-most delimiter”.
You should now see that the columns automatically get split!
|7.||Let’s keep going and try to separate out each player’s position. Click the new “Player.2” column and then click the “Split Columns>By Delimiter” menu button again. This time use the settings in the image below to split the column at the hyphen.
Power Query is really looking useful.
|8.||The last thing bothering me is the closing parenthesis after each player’s position. To get rid of this, click to select the “Player.2.2” column and then click the “Replace Values” icon.
Once the “Replace Values” dialog loads, enter the closing parenthesis in the “Value To Find” field AND LEAVE THE “REPLACE WITH” FIELD BLANK! Then click “OK”.
Check this out…
|10.||Now that the data is cleaned up, click the “Close & Load To…” button on the “Home” tab of the ribbon.
This will load the same “Load To” box discussed earlier. Adjust the settings as you see fit and click the “Load” button when you’re done.
The data loads exactly as you cleaned it up!
Because I’m in the middle of a series of DFS-related blog posts, I wish I had a more concrete example that specifically tied in DFS information. But I did want to demonstrate the power of cleaning and tweaking data with Power Query. Hopefully you can recognize there is a great deal of value in knowing these tools exist so you can use them to solve issues as you build your own DFS spreadsheet with the information you like using.
I’ve included a couple more links below that may help you down the Power Query path.
As always… stay smart.
Is anyone using Power Query already? What kinds of things are you using it for? What sites are you loading the data from?
I’d love to hear it if you are. Please e-mail me or leave a comment below.
I’ve only given a brief overview of the full capabilities. If you’re intrigued and looking for more examples, check out these additional resources below.
|Download Page||Free download page. Power Query is free, but it does require you to have Excel 2013 or Excel 2010 “Professional Plus” (I don’t know exactly what that means). It also requires you be using at least Windows 7.
If you’ve been on the fence about upgrading to the newest version of Excel, I list out a few of the purchasing options here.
|Interactive Online Tutorial||If you choose only one of these items to click on. Choose this one. The demo is only a few minutes long, but it does a great job of demonstrating how you can really fine tune and clean up the data you import through Power Query.|
|Interesting Examples that Might Apply to Baseball Data||This is a fairly lengthy post, but look specifically for the sections labelled “Append (Combine) Tables with Power Query”, (I could see this being a way to import a player’s last three seasons of data, or to import multiple projection systems) and “Merge Tables – A VLOOKUP Alternative” (a way to combine DFS salaries with info from other sites).|
|Introduction to Power Query||This is simply a written explanation of Power Query and its features and benefits. More detailed than I’ve explained above.|
|Advanced Example, Dynamic and Multi-Layered Queries||I started this post off by referring to a question I received from a reader of the site. He wanted to provide Excel with a list of player IDs and then have it systematically go out to the player pages for each of those IDs and pull back data.
I wasn’t sure this was possible in Excel, but turns out that it is! This resource demonstrates how to make advanced edits to your query to make it dynamic (to ask for a player ID) and to make it multi-layered. Said another way, you can have one query go and fetch a list of player IDs and you can have a subsequent query run off each of those IDs. “Hey Excel, go get this list of players. Then go through each player on that list and go get me the standard data from their Fangraphs page.”
It’s slow… But it works.
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:
Traditional Excel Formula
Structured Reference Excel Formula
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…
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!
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.
|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).
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”.
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.
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!
Thanks for following the site and stay smart.
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.
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:
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:
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.
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.
|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:
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”).
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.
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.
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.
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.
ADP Aggregate Projections Ben Zobrist Buster Posey Case Study Clayton Kershaw Create Your Own Rankings CSV Derek Carty DFS Dollar Values Draft Draft Kings Excel FanDuel Fangraphs Google Sheets Hanley Ramirez Hitters In Season Analysis Jose Abreu Martin Prado Mike Trout Mock Draft Ottoneu Pitchers PITCH f/x Player ID Points Leagues Power Query Prince Fielder Projecting X Projections Razzball Replacement Level Ryan Braun Spreadsheet Standings Gain Points Steamer Strategy Todd Zola Trading Troy Tulowitzki Video Web Query