Building a powerful DFS spreadsheet: Lesson 2

In our last DFS spreadsheet article we left off with a spreadsheet that auto-populates and should be heat mapped for quick reference and SHOULD look like this, or some preferred variant of this:

If you have not reached this point yet, please refer to the first lesson which can be found here:

Building a powerful DFS Spreadsheet: Lesson 1

For this lesson, we will how to pull information off the internet from specific sites so that each week, when a website updates the information, it will automatically update in your google sheet! This data can then be used in advanced DFS formulas to create statistical views that will automatically recalculate every week.

Often times there are valuable tables on websites that contain great information on rankings for teams that can be helpful. One of my favorites is football outsiders. They recently revamped their website and it looks great! It can be found here: https://www.footballoutsiders.com/

One particularly useful statistic that they provide is free statistics and rankings of team efficiency, offense, and defense statistics. If you click on the link below you will see how this is rated

https://www.footballoutsiders.com/stats/teamdef

Here, we will show you how to look up this info and add it to your metrics to automate the sheet to update whenever the website updates its rankings. At the bottom of the sheet we already have created, add a new page by clicking the plus sign

You can name the new page whatever you prefer. For simplicity we will name ours FO-WRDEF

In the first cell in the top left we will enter the following formula:

=IMPORTHTML(“url”, “TABLE”,”WHICH TABLE YOU WANT”)

So, if you go to FO website for DEF, their defensive rank against WR’s is the second table and your formula for google sheets will look as follows.

=IMPORTHTML(“http://www.footballoutsiders.com/stats/teamdef”, “table”, 2)

Having this URL properly placed will lead to a sheet that looks like this:

This will automatically update from the website every time you reload the google sheet! It is a live view of what the website rankings are. You can import information (also known as ‘scraping’ data) for any other website in a similar fashion.

Now we will take this information and add it to our default view, but what is the easiest way to digest this information? For ease of use, we will take the ranking and add it to our default view. We will do this using a formula known as ‘VLOOKUP’.

In order to make VLookup effective for this, we will need to create an automatic way for the opponent to update every week. Lets knock out two-birds with one stone. We will use the defensive projections from Daily Roto in one of our sheets to 1)import projections for def for the next week and 2) create a new sheet to reference for opponents. Create a new sheet in your workbook and label it ‘DEF’ and paste the information from daily roto DEF projections in there.

This will create a new page and will have this view:

Now, navigate to the ‘Summary’ sheet and create a new column called opponent and our ‘Summary’ will look like this:

Now create a VLOOKUP formula to automatically populate the opponent every week. The VLOOKUP equation should look something like this:

=VLOOKUP(C2,DEF!A:B, 2, false)

When using VLookup, the first variable after the parenthesis references the item you are trying to find first. In this scenario we are looking up the opponent that they are playing that week. C2 will reference the team our player is playing for – ‘DEF!’ references the sheet we are searching, and ‘A:B’ references the columns we are matching up. What this does is search ‘A’ and ‘B’ columns for an exact copy of whatever is typed in ‘C2’ (in this case C2 says ‘PIT’). The number ‘2’ tells google sheets what to spit out into the blank cell once it finds ‘PIT’, in this case we have selected two columns (A-B) so itll spit out whatever it finds in the column immediately 2nd to A which is the opponent this week (NE in this format). The sheet should now look like this:

You can now drag that small square in the bottom right hand corner down the column to get it to auto-populate the rest of the teams, which will lead to this:

More info on VLOOKUP function can be found here:

https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1

In the top variable row, next to the ‘Value’ cell, create a cell called ‘FO-WR1’. We will add the rankings in for each teams opposing defense AGAINST the WR1 for a quick view of how a WR1 will be matched up any given week. In the first cell under the ‘FO-WR1’ column, the VLOOKUP formula will look like this:

=VLOOKUP(C2,’FO-WRDEF’!B:D,3,false)

This should result in this:

This shows us that NE has the 17th ranked WR DEF against WR1. Again, we drag it down all the way to the bottom and itll autofill the rest of the column.

Some times, like in the photo above, there will be an “N/A” that appears. This happens when there is a discrepancy for VLOOKUP being unable to find ‘SD’ in the search parameters. This just means that we need the C column spelling to match wherever we are searching for – in DEF SD is listed as LACh, so we will need to change the origin to that for Vlookup to work –

If we change LaCh to SD as shown below

To SD

It fixes the opp

You may need to do this initially in your spread sheet to make it work. There are also options within VLOOKUP where you can add SD, LAC and LACH to mean the same term.

Lets repeat this VLOOKUP for the WR2 and WR3 poistions.

WR2 will look like this:

VLOOKUP(D2,’FO-WRDEF’!B:H, 7, false)

And WR3 should look like this:

=vlookup(D2,’FO-WRDEF’!B:L, 11, false)

For a final view of this:

Finally, add some heat-map action (using conditional formatting we learned from lesson 1) and we get this:

Congratulations! We have created an auto renewing, user friendly, quick-view sheet that updates weekly based on whatever projections we choose to use. This is a basic tutorial for using =importHTML and =VLookup. These are powerful formulas for automating your DFS weekly spreadsheets. In the next lesson we will analyze the best variables to use get more sensitive results and create consistent line ups. We will continue to use self-updating spreadsheets to create these views. This will effectively reduce the amount of time we spend searching for numbers and information that is vital to creating strong lineups and making the cash.

Written by:

Chad Toujague

#spreadsheetwithmeandmakesomemoney