Baseball Projection Tool Guide
- Excel 2007, 2010, 2013, or 2016 for Windows or Excel for Mac 2011 or 2016
- Solver add-in for Excel – this is a free add-on that can be enabled directly in Excel. For Windows users follow these instructions to enable Solver. For Mac 2011 Solver should already be enabled, but if you don’t see it as an option you can follow these instructions. For Mac users there’s a second set of steps that you need to follow:
1. Click on Tools > Hover over Macros and choose VBA Editor2. Then click on Tools again and choose References3. When the reference box opens up you want to make sure the box next to “Missing Solver.XLAM” is unchecked and “Solver” is checked.
4. For any issues getting the tool up and running in Excel please see the Excel Troubleshooting Guide.
Getting Started and Downloading Excel Files
- Now that you have Excel configured with everything you need you’re ready to open the main file. Download the file titled “MLB Projection Tool”. This file will be updated each week so you’ll need to come back to the site to get the most current data. If you have the FanDuel or DraftKings only package the file will be named “MLB FanDuel Projection Tool” or “MLB DraftKings Projection Tool.”
- The next file you’ll need is the MLB-Multi-Lineup-Optimizer. When you download this file you can’t just open it right away because the Projection Tool won’t recognize it. Instead, save the file to a location on your computer (it can be anywhere…Desktop, Downloads, separate folder, etc.) Then go back to Excel where you already have the Projection Tool open, click File > Open and go find your MLB-Solver file. You’ll need to make sure when that file gets saved it’s named exactly “MLB-Multi-Lineup-Optimizer.xlsm” If you download that file multiple times you might get a (2) or (4) added on to the end of the file name.
Using the Projection Tool
1. Refresh the spreadsheet to the current day’s data on the Daily Lineups tab
Simply click the “Refresh Data to Today” button on the Daily Lineups tab. This will refresh all of the data sources in the tool to the current date. You can analyze a Custom Date by entering a different date in the “Custom Date” field in the top left of the Daily Lineups tab, but starting lineups will only be set for the current date. In that situation, default starting lineups will be used. Also, be aware that official starting lineups are released at different times throughout the day. The sheet references Baseball Press so what you see there will be included in the tool. Anything not yet posted will revert to a default starting lineup.
You can manually adjust lineups on this tab if they haven’t been updated yet and you know from another source who is likely to play.
2. Hunches & Exclusions
There are 6 main functions you can perform from this tab:
- Select the list of games for the slate you’re playing by using a 1 for any game you want included and a 0 for any game you want to remove (on the far right of the tab).
- Exclude players that you want to make sure are not included in your starting lineup. Assuming you updated the lineups in the previous step you should only be getting projections for players that are expected to start. However, there may be players that you want to make sure are avoided. Check the Adjustment Selection tab for official player names, but simply enter the players name into that column and that will effectively set his projection to 0.
- Exclude teams that you want to make sure are not represented at all in your optimal lineup. You should enter team abbreviations as they appear on the Adjustment Selection tab. This can be useful for games that you expect to be rained out or for late/early games that don’t apply to your exact contest.
- Add points to a given player’s projection because you are confident that player will have a big game and you want to make sure that player is in your lineup. Simply add the player’s name and you’ll see his current projection appear. Then you can enter a new value that will get added to or subtracted from that player’s projection.
- Guarantee players to appear in your Optimal Lineup by listing the player in the Lineup Guarantee column. Make sure you spell the player’s name as it appears on FanDuel or DraftKings.
- Load your own custom projections to be used with the tool. If you choose this option – all of the different adjustment options or player exclusions are no longer applied. You will just get an optimal lineup based on your list of projections.
On this tab you’ll determine the settings you’d like to use to generate your custom set of projections. Make sure you change the Fantasy Site setting in the top left to FanDuel or DraftKings based on the site you’d like to create a lineup for.
On the right you’ll see the Time Period used for baseline stats. The primary metric being used across the tool is Fantasy Points per Plate Appearance. That is calculated as fantasy points scored (based on the site specific settings) per each plate appearance historically. Runs and Stolen Bases (+ Caught Stealing) are computed at the per game level since they are not necessarily dependent on plate appearances.
There are several different factors that you can choose to use for determining each player’s projection. Most the adjustments are Yes/No options. Either you use that adjustment for all batters or you don’t use it at all.
Adjustments for Batters
Betting Situation – currently set to add 5% onto the projections for any batter playing in a game in which their team is favored and subtract 5% for any batter that’s playing on an underdog.
Betting Total – This takes the Vegas Over/Under for the game, divides by 2 to get projected runs for each team and multiples by 1.1 if a team is favored and by 0.9 if a team is the underdog. Then the betting total adjustment adjusts up or down based on how many projected runs a team is expected to score divided by a historical average of 4 runs per game. So if a team is projected to score 4.3 runs the calculation would be 4.3/4 for a value of 1.075.
Pitcher Strength – compares the average fantasy points allowed by the opposing starting pitcher to a historical average for all pitchers. Adjusts the batter’s projection up or down accordingly compared to how far off the pitcher is from the historical average. So if a pitcher like Clayton Kershaw allows 0.75 points per game to batters relative to an average pitcher then a batter facing Kershaw would have his projection multiplied by 0.75.
Pitcher Throwing Hand – adjusts for career batting performance depending on the throwing hand of the pitcher for each batter. If a batter scores 0.5 fantasy points per plate appearance against left-handed pitchers but scores 0.6 fantasy points per plate appearance overall (against all pitchers during his career) then the adjustment value would be 0.83 (0.5/0.6). The batter’s final projection would be multiplied by 0.83.
Pitching Ground/Fly – adjusts for career batting performance for each batter depending on the tendency of the pitcher to give up groundballs or flyballs more often
Pitcher Power/Finesse – adjusts for career batting performance for each batter depending on whether the opposing pitcher could be characterized as a Power Pitcher or a Finesse Pitcher
Ballpark – Based on how much offensive production a ballpark has historically produced relative to other ballparks. Data from FanGraphs Park Factors
Batter vs. Pitcher – Finds the difference between fantasy points per plate appearance for each player against the specific pitcher they’ll be facing for the day and that player’s overall career average. Many of these matchups have very few plate appearances to draw from. There is a BvP At-Bat Minimum threshold in the top left that you can set to ignore any BvP matchups with fewer plate appearances than you’ve chosen. By default it’s set to 40 so it will ignore any BvP matchups with less then 40 historical plate appearances, but you can change that you whatever you’d like.
Time Period for Baseline Stats
To get the baseline stats for each player you can choose the time period that you’d like to apply to all players. The options you have for batters are:
- Preseason Projection (utilizing the Steamer projection system via FanGraphs)
- Last 30 days
- Last 14 days
- Last 7 days
The % Used should add up to 1 for each of these time periods. For example, if you want the baseline stats to be generated using 50% of the batters’ preseason projection numbers and 50% of the current season numbers then you would put 0.5 on preseason projection and season. If you’d like to generate the baseline stats using a combination of all 5 time periods you can set each of them to 0.2. In that scenario the tool will use this calculation:
FPG (fantasy points/game) = Fantasy Points per Plate Appearance * Projected Plate Appearances + Runs and Stolen Bases per Game
(Preseason Projection FPG * 0.2) + (Season FPG * 0.2) + (Last 30 days FPG * 0.2) + ( Last 14 days FPG * 0.2) + (Last 7 days FPG * 0.2)
For pitchers the same calculation applies, but only time periods for career and season are available.
There’s one additional adjustment that you can choose to use for pitchers:
Points Against – adds up all of the projected points for the opposing lineup and compares it to all other pitcher matchups for the current day. If you use the adjustment it will improve the projection for pitchers against a weaker lineup and lower the projection for pitchers facing a difficult lineup.
At this point you should have all of your projections set. Now you’re ready to find the Optimal Lineup based on all of your custom settings. First, make sure you have the “Fantasy Site” setting on the correct site before you move to the FanDuel Optimal Lineup or DraftKings Optimal Lineup tab. If those are out of sync, you won’t get the correct salaries and projections for the site you’re trying to build a lineup for. Then head over to the Optimal Lineup tab for the site you want a lineup for.
The Stack Settings box provides the ability to ensure that you’ll get the best “stack” of players from a team you specific. For example, if you think the Rangers have an outstanding matchup against a poor pitcher with a high over/under you might want to stack up to 4 Rangers on your roster hoping they all have big games. In the Stack Settings box you can enter a Team Abbreviation and then a number of players from which you’d like to stack. Be sure you don’t make that number greater than 4 because you can only include a maximum of 4 players from a single team in your lineup.
Finally, hit the Find Optimal Lineup button for Excel Solver to run and generate an optimal lineup. You’ll see a progress % in the bottom left corner of Excel, but it typically will take 20-40 seconds to run and create your lineup. You’ll see a pop-up appear asking if you want to Keep Solver Solution. Click OK and your mathematically optimal lineup according to your projections will appear.
If you want to get multiple lineups based on your custom settings you’ll need to make sure you have the MLB-Solver file open (with Editing Enabled) and then hit the “Send to Solver” button. At this point you should save and close the Projection Tool. If you leave it open while the MLB-Solver file runs it will take considerably longer to run.
MLB Multi-Lineup Optimizer
After you close the Projection Tool – you’re ready to run the solver file and get as many lineups as you want from your custom settings. You can change the number of lineups in the box to the right. The tool is setup to find 10 lineups by default. If you change that value you may not see the full formatting applied to every lineup, but you’ll still get as many lineups as you request. You can Exclude or Lock any player in the list by using a capital X or L in the first column. Expect FanDuel lineups to take about 5-10 seconds per lineup so 10 lineups would be generated in about 1 minute and DraftKings lineups will take longer at 20-30 seconds per lineup. This is due to DraftKings’ added complexity with players eligible for multiple positions.
You’ll then be taken to your lineups with additional stats listed for each player selected. You can always go back and re-run it and exclude or lock players based on your preference.
The Lineup Generator first requires you to send refreshed data from the Projection Tool by clicking on the “Send to Lineup Generator” button on the Optimal Lineup tab of the Projection Tool. Make sure your file names are the same as when you downloaded them from the site. Once the data is transferred you’ll want to save and close the Projection Tool.
Next, you’ll want to click the “Enable Fast-Editing” button on the right so that you can quickly add new players to your list of preferred players on the first tab. You’ll need to manually add players to each position in the blue boxes. Once you have all the players listed that you’d like to consider – you can hit the Generate Lineups button and all of the combinations from the players you’ve listed will be ranked on the Ranked Lineups tab. Ignore the error (just hit End) if you don’t want a CSV created for upload directly to FanDuel or DraftKings
If you want a CSV of your lineups generated that you can upload directly to FanDuel or DraftKings – make sure you open the “FanDuel Upload Template” (or DraftKings Upload Template) in the same folder as the Lineup Generator file. This will automatically create your CSV file through the lineup generation process.
Differences with Excel for Mac 2011
Tips and Troubleshooting
I will add to this section based on feedback that I get…
- Submit error messages or other bugs to the Bug Report form
- Resolve Excel errors through the Excel Troubleshooting Guide
- If a cell has yellow or blue shading on it then you can edit it with your own value. If there’s no shading then you probably should not be writing over a formula and risk breaking the sheet. If that happens you can always download a fresh copy and start clean.
- If you are going to make your own adjustments to the Manual Lineups tab and insert your own players (rather than waiting on the web refresh to get official lineups) I would recommend downloading a fresh sheet each day. If not, you should be able to save the sheet on your own computer for the week.