Building a DFS Spreadsheet
When using the term ‘spreadsheet’ a lot of people think of using the Microsoft product ‘Excel.’ For the ease of access, especially for those who are always on the go, we will create our spreadsheet using google docs. Google docs performs a lot of the same functions as excel but is mobile and, most importantly, free!
-Click start a blank sheet!
-Title your spreadsheet
-Import your data set from whichever website you are extracting projections from.
This can be done from numerous different sites with varying reliability. My advice is to do your research on the sites you pull from. Some will make you pay for the data, some will be free, no matter how you get the projections, make sure they can easily be converted into a spreadsheet. My personal website of choice is Daily Roto (https://dailyroto.com/). They offer a reasonable deal on their projections and provide lots of metrics to create your spreadsheets as sensitively as youd like them. For other projections we will show you how to ‘scrape’ data from other websites to create a much more powerful and accurate spreadsheet.
-By clicking this top left square you can highlight the entire spreadsheet of projections you intend to copy over to your spreadsheet. Once we create this spread sheet, this will be the only thing you need to copy and paste each week.
Similarly, by clicking this little square in your blank spreadsheet is will highlight the entire spreadsheet and you can paste in the data.
-Now we can create additional sheets in our spreadsheet that will create the view we want. We can do this by clicking the ‘+’ sign in the bottom left hand corner.
I will rename my ‘sheet 4’ to ‘Summary’ as this will be the tab I look to first to get a quick idea of the stats I want to see.
-The next thing I will do is create a column for all the players in the spread sheet.
I will start a new column called ‘Player’ in my new ‘Summary’ sheet.
-Now things will start to get a little tricky so please go slowly and reread certain parts if you need to. In the A2 box we will start using equations to automate the way our spreadsheet works. So in A2 just put an equals sign in it and click the first player in our daily roto tab.
-First put an ‘=’ sign in the A2 box
-Then click the first player in the daily roto sheet
When you’re finished, and you did it correctly, you should have the first player name in first box of your ‘Summary’ spreadsheet as follows:
An equation will be in the ‘fx’ box. Now everytime you reupload the new data every week into your ‘Daily Roto’ sheet, it will automatically refresh into your ‘Summary’ Tab. Lets go ahead and get the rest of the players to autopopulate by clicking the little square in A2 and dragging it all the way down the page.
When you’re done it should look something like this:
Now lets generate a desired view that we can look at to get important information. We will repeat the previous steps to populate the following data:
So for it to auto-populate Le-Veon Bells position we would put an ‘=’ in B2 and then navigate to his position in the Daily Roto sheet and click it.
The daily roto sheet has the position listed in C2
So now our ‘Summary’ sheet will look like this:
When you click on cell ‘B2’ is should have that [ =’Daily Roto’!C2] in the function box. You can drag the little box all the way down and it will repeat the same process for the rest of the players.
(NOTE: I deleted the ‘opponents’ section for this example and readd it when we go into our advanced spreadsheet metrics discussion)
This is what our spreadsheets should look like now:
For this next step we will need to change our ‘DK Salary’ to ‘Plain text’ so highlight the column (mine in this example is the D column) and click the numbers right above it as follows:
With the D column still highlighted convert the whole column to ‘plain text’
Daily Roto provides a ‘Value’ metric. We will add one in ours as well. For Draft kings we are looking for players that have a value of about 3. Ill explain what that means in a minute. But for now, you should know that the higher the value, the stronger the play. In order to create a ‘value’ column you will need to divide your DK salary by 1000 and then divide your projected fantasy points by that. It will look something like this (projected points)/(DK Salary/1000). In our spreadsheet it will look like this:
The last step for this portion of the tutorial we will be doing is heat-mapping so you can quickly see your higher value players.
Click the F (Value) Column so that it highlights the whole column and then go to ‘Format’ and select ‘Conditional Formatting’ then click ‘Color Scale’ and use the following settings (or colors that you prefer more. Basically red means WORSE and green means BETTER)
Last tip for this lesson:
Highlight the entire ‘1’ row
Then click ‘View’ and ‘Freeze’ —> 1 Row
This will lock the Top row so you have a cleaner view and can scroll without losing track of which value goes where. I also changed the colors of my top row to make it easier to distinguish it from the rest of the sheet.
It may seem like this took a long time, but I promise this will save you many hours during the season if you set it up now and will lead to much more educated DFS plays and a higher chance of winning more! Please continue on to lesson 2 to start getting more advanced metrics into your spreadsheet by taking advantage of more functions within google sheets.