NFL Projection Tool User Guide

The NFL Projection Tool allows you to use a variety of stats to create custom projections and generate weekly fantasy lineups. You can purchase the tool here if you haven’t already.

System Requirements

1. Microsoft Excel 2007 or later (or Excel for Mac 2008 or later)

2. Solver plugin for Excel (free). Instructions on how to enable it are found here. If you don’t enable this plugin you won’t be able to use the tool to generate an optimal lineup. There’s an extra set of steps that are required for Mac users.

Mac Users Only
1. Click on Tools > Hover over Macros and choose VBA Editor
2. Then click on Tools again and choose References
3. When the reference box opens up you want to make sure the box next to “Missing Solver.XLAM” is unchecked and “Solver” is checked.

How to Use the Tool to Create Projections and Generate an Optimal Lineup

Projections

The first step is to ensure you have the scoring settings set for the site you’d like to create a lineup for. In the top left of the “Projections” tab is a black box labeled fantasy site. Change that value to either Fanduel or DraftKings depending on the site you’re creating a lineup for.

fantasy-site

The NFL Projection Tool allows you to use historical data and matchup specific stats to create custom projections for each player. There’s a lot of data at your disposal, and this guide will show you how to go through the workflow to ultimately produce the best possible lineups for daily sites like Fanduel and DraftKings. There are 4 main components of the NFL Projection Tool: Adjustments, Player History, Rank, and Depth Chart. Each of the 4 components generates a projection for each player and then you can choose how you’d like to weight the 4 component projections to get to your final composite projections which you’ll use to choose your optimal lineups. Here’s how each of the components works:

Adjustments

The Adjustments projection starts out with a historical average of fantasy points scored by a range of the weekly rankings at each position. For QB’s and TE’s the average of all starters is used, RB’s and Wr’s are broken up into ranges of 1-15, 16-30, 31-80. So if a RB is ranked 45th in the current week his base projection would be the historical average of RB’s ranked 31-80 in past weeks. Then adjustments from all of the other factors will increase or decrease that base projection. In this method historical stats from each player are not used at all. All of the adjustment values have been determined by analyzing historical data in the same situations over the past 4 years (2010-2013). The situational factors you can choose to use in your custom projections are:

  • Vegas Line
  • Vegas Projected Points (derived from the over/under and the line)
  • DST Against Rank
  • Location
  • Stadium Type
  • Temperature
  • Wind Speed
  • Humidity

adjustments

For example, in Week 1 of 2014 Peyton Manning starts with a base projection of 15.9 (based on Fanduel scoring) which is the average for all starting QB’s. Then he gets a 10% increase from the Vegas line since the Broncos are a 7 point favorite against the Colts and historically QB’s in that situation scored 10% higher than average. The Vegas Projected Points for the Broncos is 31 based on the over/under of 56 and the 7 point cushion for Denver. Manning gets a 40% increase from that value because QB’s that have played with Vegas Projected Points of 31 have scored 40% higher than the average QB. The same types of adjustments are made for the rest of the factors if you choose to use them by selecting “Yes” or “No” in the Use? column.

Player History

There are 4 sub-components that you can use to create your own custom player history projection:

  • Preseason Projections – aggregate projections provided by FantasyPros.com broken down to a per-game set of values
  • Season averages – once the season gets started, weekly averages will be computed for each stat

One variation used on season averages is the option to use a player’s “floor” or “ceiling” instead of the average. This could be particularly useful in different daily tournament types (i.e. it might be preferable to use floor stats for head-to-heads to create a safer lineup, but use ceiling stats when building a lineup for a GPP. Floor is defined as the 25th percentile of each player’s fantasy points and the ceiling is the 75th percentile. For the first 3 weeks the prior season floor and ceiling values are used, but in-season stats will be calculated starting week 4. You can choose Average, Floor, or Ceiling on the bottom-left of the controls highlighted in brown

  • Last 3 game averages – similar to the season stats, but limited to the last 3 games
  • Defense-Adjusted averages – assumes that each player will receive his season average number of touches, but uses the opposing defenses’ yards per carry, yards per attempt/target, and TD rate per play to compute values for the offensive player if they performed exactly at the average level of the defense they’re facing. Regressed 2013 defensive numbers are currently loaded for the first 3 weeks of the season, but in-season averages will take over in week 4.

You can choose how you’d like to weight each of those components to create your own composite player history projection. For example, during the first 2-3 weeks of the year it doesn’t make much sense to use in-season statistics because the sample size will be too small. In that case it might make sense to split 50/50 between preseason projections and defense-adjusted. Later in the season you might want to use all four elements. Just remember the total weight assigned to each of the four components must equal 1.

Each sub-component contains all relevant fantasy stats including rushing attempts, receiving targets and of course all the stats that produce fantasy points.

player-history

Rank

The rank method is very simple. Over the last 4 years I’ve computed the average fantasy points scored by each week according to the projected weekly ranking value of each position. The best fit regression line was found to smooth over the anomalies. The starting point (based on Fanduel scoring) is 20.94 for QB’s which decreases by 0.36 points for each rank of QB. RB’s start at 16.47, WR’s at 14.46, and TE’s at 11.8. For example, a QB ranked 10th in the weekly rankings would get a projection of 20.94 – (o.36*10) which is equal to 17.34.

Depth Chart (starting week 4)

Once the season gets into full swing the Depth Chart component will become active. This will look at the average fantasy points scored by the player at each given position on the depth chart from week to week. As an example, say Montee Ball gets injured in week 6 and is unable to play in week 7. Ball has been the #1 running back on the Broncos and he’s scored 17 fantasy points per game. With Ball out the next week someone else (possibly Ronnie Hillman or C.J. Anderson) would take over the #1 running back spot. The tool would set a value of 17 fantasy points to the player that assumes a starting role.

Final Projections

Now that you’ve gotten projections using four different methodologies you can choose to weight all 4 of them equally or create your composite projection any way you’d like. The values must add up to 1, but you could choose to give all other methods a weight of 0 if you’d only like to use Player History.

final-projections

Manual Adjustments

Once you’ve finished tweaking the settings you can browse through the projections for each of the players. The “Customized Projection” all the way to the right of your sheet is the final value that you’ll be using to create your optimal lineup. If any of those values jump out to you as being wrong you can make a manual adjustment for any player. Simply navigate to the Manual Adjustments tab, enter the players name in the “Manual Adjustments” column (make sure you spell it correctly or it won’t show you a valid projection) and then enter the value you want to add or subtract from the current value. You’ll see the projection change as soon as you enter in a value.

You can also exclude particular players from your lineup if you want to avoid them completely by entering their name into the “Exclude Players” column.

man-adj

 

Loading your own custom projections

Alternatively, if you have you own projections that you’d like to use and you’d prefer not to use the projection engine in the file you can load your own set of projections in the “Custom Projections” column of the Manual Adjustments tab. The format needs 2 columns: Player Name and Projected Points. Please be sure that player names match exactly with the Fanduel and DraftKings naming conventions. Loading data in these columns will completely overwrite the projections that the sheet derives so you will no longer be able to make adjustments via the controls.

Optimal Lineups

Once you have your projections set you’re ready to move to the Fanduel Optimal Lineup tab or the DraftKings Optimal Lineup tab. You can only create lineups for one site at a time, but all it takes is the change of the Fantasy Site value in the top left to switch to the other site.

At this stage, all you need to do is hit the “Run Model” button and it should take about 15-30 seconds to generate your optimal lineup. You will get an error message if you have not installed the Solver plugin in your instance of Excel so please be sure to follow the instructions to enable Solver.

If there are players in your lineup that you’re unhappy with just go back to the Manual Adjustments tab and add them to the exclude list. This also works well for creating multiple, diversified lineups. If you get a few guys on your first lineup that you want to ensure are not in your subsequent lineups then simply save your initial lineup and then add those players to the “Exclude Players” list.

optimal-lineup

Now you’re ready to enter your lineups onto the daily fantasy sports page and hopefully dominate your league.

1 Comment

First Look – NFL Tool

Over the last few months I’ve been doing a lot of statistical research on the NFL and week to week scoring. Next Tuesday (September 2nd) the Spreadsheet Sports NFL Projection Tool will be available to purchase, and I think you’ll be hard pressed to find a better resource to help you build your weekly fantasy lineups. The tool will support Fanduel and DraftKings out of the gate and fresh data will be loaded each week of the season. I’ll save the details for when it’s launched, but here’s a quick look at the adjustments and features you’ll have at your disposal to create custom projections based on what matters most to you.

nfl tool preview

Spreadsheet-Sports Fantasy Football League

I’d also like to extend an invitation to anyone that would like to join a season-long league with other readers of Spreadsheet-Sports. I’d like to start a 12 team league run on ESPN with a $25 entry fee. Scoring will be PPR and mostly default for everything else. The entry fee will be $25 and with the playoff champion taking home $250 and the runner up getting $50.

If you’re interested in playing – please add your name and email address here.

20 Comments

MLB Custom Projection Tool Now Supports DraftKings and DraftStreet

Up to this point the MLB Custom Projection Tool has only supported projections and optimal lineups for Fanduel. As of tonight’s update DraftKings and DraftStreet are now supported with all of the same features that you’ve been getting with Fanduel. To choose what site you’d like to generate a lineup for change the Fantasy Site setting on the Adjustment Selection tab of the Projection Tool.

fantasy-site-control

Everything else should function just as it did before, but you will need to run the “To Solver” button for each site you want to find a lineup for. If you want to get a Fanduel lineup – get your adjustment settings in place and then hit “To Solver” as you normally would. Now you can use the Solver tab on the Solver file. Then, if you want a DraftKings lineup – get back into the Projection Tool, switch the Fantasy Site setting to DraftKings and hit “To Solver” again. Now in the Solver file you can use the DraftKings Solver tab and click “Run Model” there. In the Solver file – you’ll see 2 additional tabs. Along with the familiar “Solver” tab (used for Fanduel) you’ll see a DraftKings Solver and DraftStreet Solver tabs. Use those tabs for the respective site that you’d like to generate a lineup for.

Be sure to click “To Solver’ in the Projection Tool and Run Model in the Solver file for each lineup you’d like to generate.

To load the player pools for each site it’s important that you load the All-Day Contest data that’s delivered in the Google Spreadsheet. If you only care about Fanduel you won’t need to take any extra step. However, if you want a DK or DS lineup then you’ll need to export the player pool CSV from the contest page. Here’s what it should look like on each site.

ds-export_001

 

draftstreet_export

 

In the Projection Tool you’ll see two new tabs for “DS Data” and “DK Data.” Copy the entire sheet from the downloaded CSV and paste it over the data in the tab for the site you want.

One thing to note on DraftStreet. Technically, you can plug in a Relief Pitcher in the 3rd pitcher slot. At this time, the tool will only select 3 starting pitchers (all will need to be projected starters for the day).

If you run into any issues please leave a comment or send me an email.

Leave a comment