There were some big waves in the nerdy baseball world (I’m a proud card-carrying member) last week, when Baseball-Reference.com released a redesigned website. While the improvements are very nice, especially on a mobile device, the unfortunately broke the link to the Projecting X 2.0 spreadsheet.
Unfortunately, the newly designed site doesn’t seem to allow for the web querying function that was used to extract several of the pieces of information necessary to project pitcher stats. Because of this, we’ll have to make a few small edits to your spreadsheet that will allow you to link directly to the player you are projecting so that you’re taken right to the table containing the desired information (if we can’t pull it into the file, we’ll create a link that takes you right to where the information is located).
What follows are instructions that will help create these helpful hyperlinks and add them to your spreadsheet.
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.
This is what the finished spreadsheet calculating the NFBC average standings will look like.
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.
Projecting X 2.0 and the Excel Template are now available!
Yes, that’s right. Mike Podhorzer has just released Projecting X 2.0. And I’m excited to announce an updated Projecting X Excel template has been upgraded to be more helpful than ever and has been updated to be consistent with all the new projection methodologies used in Projecting X 2.0.
NOTE: The Projecting X 2.0 Bundle has been updated for the upcoming 2017 MLB season.
What’s New in Projecting X 2.0?
While I would not consider version 2.0 to be a complete re-write of the original Projecting X, it’s certainly an improvement of the process, methods, and formulas used in the original book.
Don’t get me wrong, I love the Projecting X approach. But I did feel there were a couple of methods in the original version that I thought had room for improvement. For example, I’ve come to learn that using K% is superior to using K/9. And I thought the approach to projecting runs and RBI was too subjective.
Well, Podhorzer has addressed all of those issues, improved upon several of his methods, and even introduced new ones.
My favorite changes to the process are:
A much improved and more scientific methodology for projecting Runs and RBI
Switching from K/9 and BB/9 to K% and BB%
A method for projecting quality starts (I get asked about QS projections all the time!!!)
Addition of metrics like strike percentage (STR%), looking strikes (L/STR), and swinging strikes (S/STR) to pitcher projections, and
Revisions to the projection of stolen base frequency
What’s New in the Excel Template
The Excel template has been updated to be 100% consistent with all the new methodologies and formulas used in Projecting X 2.0. Take a look.
If you’re a user of the Projecting X 1.0 Excel template, the biggest improvements in the file are:
Addition of career stats
Addition of a customizable three-year weighted average
New team hitting and pitching totals that sum as you project
More league average information
New links to Baseball Savant, Brooks Baseball, and RosterResource.com
It’s now easier to add a new player to the spreadsheet
The Player ID Map is now easily refresh-able so that when I add new players or change player teams, this information updates in your spreadsheet too
Download the Updated Bundle Today
The updated book and spreadsheet are available for the bundled price of $17.99 (they separately sell for $9.99 each). Click the Add to Cart button below to begin the checkout process.
Let me come clean. I screwed up. And it likely will cause you to see errors in your spreadsheets. That’s the whole reason for this post.
Having trouble with VLOOKUP error messages? This post should help.
What Happened?
While this post is going to address a very important topic (resolving VLOOKUP errors), there wasn’t much of a need for this until I came up with a new format for the Player ID Map. The intent was to make the Player ID Map easily updatable. I hate having to lookup the IDs, birth dates, and handedness of all the new players.
And it’s always bothered me that there was no easy way for you to get updated Player ID information.
Let’s be honest. It’s a pain in the ass. Especially this time of year when players are switching teams every day and minor league players we haven’t had to deal with in the past are now projected to reach the big leagues this season. It’s tedious to keep teams up-to-date and to add these new players.
I needed to find a way to improve this process and to make everyone’s lives a little easier.
The Solution
The solution was to make the Player ID Map available in an online CSV file. One you connect that online file to your Excel spreadsheet, you simply have to right-click on the Player ID Map and hit “Refresh”. You will instantly get any update I’ve made.
Sounds amazing, right?
Major leaguers have a purely numeric ID while minor leaguers have text in their ID.
The Problem
The fly in the ointment happens to be the way Fangraphs structures their player IDs. Major leaguers, like Jose Abreu, have a purely numeric ID. Whereas minor leaguers that have not reach the big leagues, like Yoan Moncada, have the text “sa” in front of a string of numbers.
The unintended consequence of importing the Player ID Map file is that because some IDs contain text, Excel will treat the ENTIRE imported column as text.
The problem is that reports you download from Fangraphs and then open in Excel treat the player ID column as numeric values.
Warning… It’s About to Get Technical
If you’re fine with the old Player ID Map and the fact that it doesn’t get updated very often, you don’t have to use the new one. The old one can be downloaded here and will still be updated periodically. You can stop reading this post and save yourself some sanity.
But if a little complication doesn’t scare you off and you see the value in being able to refresh the Player ID Map and get regular updates… Keep reading.
Text and Numbers Are Treated Differently
Excel and most other computer applications treat text and numbers differently. And this is a common problem with VLOOKUPS. So the number “15676” is not the same as a text string of “15676”. So in our VLOOKUPS, we need to make sure we are comparing numbers to numbers and text to text.
Consider the Error Message
The first step in resolving a VLOOKUP problem is to understand the error message you’re seeing.
The “#N/A” error is the most common VLOOKUP error. And it essentially means that a match was not found during the lookup.
There are two main reasons a match would not be found:
The item (player ID) doesn’t exist where you told Excel to look for it
Or you told Excel to look for the wrong data type (look for a text value in a list of numbers, or vice versa)
Abreu’s ID is the there. It’s in the first column. Why isn’t the VLOOKUP finding this???
You can easily test the first error by manually performing the search yourself. Let’s walk through a hypothetical example with Jose Abreu. He’s a well known player. He’ll surely be in the Steamer projections I’ve downloaded.
I see from the data that Abreu’s Fangraphs ID is 15676. If I trace that through into the Steamer Hitter projections, I am able to locate Abreu. So why isn’t the VLOOKUP finding the same match?
I’m a little biased, but I think the Player ID Map is an invaluable tool.
But if I’m being honest… it has a really big weakness. When I make changes to it, there’s not a great way for me to get that updated information to you.
The advantage of doing this is that you can link to this Google Sheet in your own spreadsheets. And if you download the Excel version, it will already have a pre-established link to the Google Sheet version.
How to Update the Player ID Map
Once you’ve downloaded the new version, you can simply right-click anywhere in the player listing and choose the option to “Refresh” the connection. Any changes will automatically pull into your file.
The “Change Log” tab of the Player ID Map will work the same way. Right-click and refresh the connection on that page to get an updated listing of the changes that have been made.
In the past you would have to come back to the site, download a new copy of the Excel file, and then paste it into your existing spreadsheets. Now you’ll just need to right click (or keep reading to see how you can have it update automatically) and update it!
The Links
The Player ID Map and Change Log are available in a variety of formats, depending on the goal you’re trying to accomplish.
This is a link to download the Player ID Map now containing a connection to an online source, so that when I add players to the list, they can easily be refreshed in your files.
This is a web page version of the Player ID Map. You can web query it into your Excel files or simply look at the list if you’re searching for a piece of information.
This link can be used to create a connection to an online CSV version of the Player ID Map that you can set up within Excel. We’ll take a closer look at how to do this in a set of instructions below.
This is a web page version of the Player ID Map Change Log. You can web query it into your Excel files or simply look at the list of changes to see what updates have recently been applied.
Similar to the CSV of the actual Player ID Map, this link can be used to create a connection to the change log within Excel. We’ll take a closer look at how to do this in a set of instructions below.
What If I Currently Have the Old Player ID Map in my File?
It’s great that the newly downloaded Player ID Map comes with the connection. But what about those who have the old version? Here’s a short set of instructions of how to establish this connection.
You have downloaded a CSV file of player salaries from DraftKings or FanDuel. You pull that information into Excel. Your goal is to take the “Opponent” information and use it to determine who each player’s opposing starting pitcher will be.A list of DFS player salaries and an abbreviation for the opposing team.
You have also followed this very brief set of instructions on how to get a list of starting pitchers into Excel that refreshes automatically each day (OK, not so brief).
A list of the day’s probable starting pitchers and their team name. How can we get this list of probable starters listed against the player salary list from above?
The challenge is that the list of starters does not use the same team name system as the DFS salary information. This is but one example of this. If you ever try to combine information about MLB teams that comes from different web sites, you’ll likely find a number of other inconsistencies. Even the sites that use abbreviations (like the DFS info above), don’t use them consistently. Sometimes the Giants are “SF” and sometimes they’re “SFG”. The Nationals might be “WAS”, “WSN”, or “WSH”!
The Solution – a Team ID Map
To solve this problem, I have created an “MLB Team ID Map”. It’s similar in concept to the Player ID Map.
Click the image to see the live web page of the Team ID Map.
The map lays out the abbreviations (or team name, in Fangraphs’ case) from the following sites:
Fangraphs
Baseball Reference
FanDuel
DraftKings
Yahoo!
ESPN
FantasyPros
BaseballPress
Baseball Prospectus
Rotowire
Two Formats to Use the Team ID Map
The information is available in both a web page format (so you can web query it) and in an online CSV file (see instructions on how to use the CSV option later in this post).
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?
To view the actual HTML code of a site, right click on a web page and choose the option to “Inspect Element”. If you don’t see “<TABLE>” references, you may not be able to import the page into Excel.
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:
The URL for the probable pitchers list is dynamic and changes based upon the day you’re accessing it.
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!
You can edit the URL to see tomorrow’s probable pitchers.
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!
Why Don’t You Use Power Query?
Power Query was able to import the table, even when a traditional web query could not… But I can’t yet figure out how to create a dynamic query in Power Query.
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.
Google Sheets makes the process of querying data from a web page very easy. You can then “publish” you Google Sheet as a CSV file on the web and then use Excel to import the CSV.
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!).Exclude the date piece of the Fangraphs URL.
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
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.
Power Query is an add-in that will appear on the Excel ribbon.
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!).
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.
Click the link to see a larger version of this image.
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.
Power Query and the Power of Tables
When using Excel’s standard web query to import data, you don’t have the option to import the information as a table.
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!
There is a Catch
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.
To view the actual HTML code of a site, right click on a web page and choose the option to “Inspect Element”. If you don’t see “<TABLE>” references, you may not be able to import the page with Power Query.
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.
Downloading Power Query
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 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”).
Making Your First Web Query With Power Query
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.
Step
Description
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 table, tr (means table row), or td (means table cell), this is a table and a web query should be successful.
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.
Rather than bring it in and have it clog up my screen, we can tell Power Query not to import this column. To do this, click on the “VBR” column to select it. Then click the “Remove Columns” button.
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!
This Is Not the Best Example
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.
Are You Using Power Query? Other Add-Ins?
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.
Other Resources
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.
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.
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.
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).
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.”
“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.
. 
