Here are the Excel tools and books I have available for the 2018 season. You won’t find draft lists or player profiles here. But if you’re looking to build skills and to develop your own methods for ranking and valuing players, these are for you! All of the spreadsheet tools listed below have been updated for the 2018 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.
In order to prepare for the upcoming season, we need some important information from last year. In this post, I’ll share with you the average standings for 12- and 15-team NFBC leagues, and the average ERA, WHIP, and batting average in those leagues. With this information, you should have everything you need to get started on your SGP rankings for the 2018 season.
The last several years, I’ve participated in the NFBC’s Draft Champions competition. By participating in such a league, a user gets access to see the standings to all the various NFBC competitions.
As far as I can tell, it seems like you need to be a registered NFBC user to see the standings data. If you happen to be one (and you’re logged into the NFBC site), you can see standings data for the various contests at these links:
I take this data and manipulate it in Excel to calculate average standings across all the leagues using the process I describe here.
If you are not an NFBC user, you can see some of the historic analysis I’ve compiled from 2012 through 2016 here.
With that in mind, let’s take a look at the 2017 results.
12-team League Average Standings
Across the 149 Online Championship leagues hosted by NFBC, the average standings for first through twelfth are shown below. Note, the 1,156 RBI is the average of all teams that finished in first place in RBI. It is NOT the average of what league winners averaged in the RBI category. The league winner in RBI could have finished in 7th place overall, but is included in the 1,156 average figure.
RK
PTS
AVG
R
HR
RBI
SB
ERA
WHIP
W
K
SV
1
12
.2788
1,178
365
1,156
174
3.480
1.171
107
1,522
105
2
11
.2750
1,144
349
1,121
159
3.638
1.204
102
1,469
97
3
10
.2727
1,125
340
1,098
149
3.740
1.222
98
1,435
90
4
9
.2706
1,108
332
1,080
142
3.827
1.238
96
1,403
85
5
8
.2688
1,091
324
1,061
135
3.891
1.250
93
1,372
81
6
7
.2674
1,078
317
1,045
129
3.960
1.263
90
1,341
76
7
6
.2660
1,064
310
1,027
123
4.027
1.276
88
1,313
70
8
5
.2643
1,048
303
1,011
117
4.100
1.287
85
1,282
65
9
4
.2626
1,029
296
992
111
4.171
1.300
82
1,245
59
10
3
.2609
1,010
288
972
105
4.263
1.316
78
1,209
51
11
2
.2588
983
277
948
96
4.375
1.336
73
1,150
40
12
1
.2543
937
258
896
80
4.557
1.366
66
1,057
26
12-team League SGP Factors
Using the information from the league average standings, the raw and relative SGP factors are as follows:
Here are the Online Championship hitting categories:
Year
Type
BA
R
HR
RBI
SB
2012
Raw
0.00220
19.197
8.016
20.675
8.270
2013
Raw
0.00193
19.265
7.537
20.685
8.603
2014
Raw
0.00197
18.843
7.481
19.639
7.900
2015
Raw
0.00177
19.920
8.429
19.549
7.591
2016
Raw
0.00182
19.721
8.797
21.527
8.508
2017
Raw
0.00193
19.060
8.526
20.635
7.405
2012
Relative
0.00011
0.92848
0.38769
1.000
0.40001
2013
Relative
0.00009
0.93136
0.36435
1.000
0.41589
2014
Relative
0.00010
0.95950
0.38094
1.000
0.40224
2015
Relative
0.00009
1.01898
0.43115
1.000
0.38828
2016
Relative
0.00008
0.91607
0.40863
1.000
0.39520
2017
Relative
0.00009
0.92366
0.41320
1.000
0.35885
Here are the Online Championship pitching categories:
Year
Type
ERA
WHIP
W
K
SV
2012
Raw
(0.07840)
(0.01320)
3.253
30.968
7.184
2013
Raw
(0.07623)
(0.01472)
2.899
32.811
7.038
2014
Raw
(0.06880)
(0.01280)
2.999
31.181
6.964
2015
Raw
(0.07876)
(0.01464)
2.926
35.163
7.210
2016
Raw
(0.08042)
(0.01529)
3.184
34.212
6.842
2017
Raw
(0.08587)
(0.01548)
3.288
37.244
6.461
2012
Relative
(0.00253)
(0.00043)
0.10503
1.000
0.23197
2013
Relative
(0.00232)
(0.00045)
0.08837
1.000
0.21452
2014
Relative
(0.00214)
(0.00040)
0.09320
1.000
0.21640
2015
Relative
(0.00224)
(0.00042)
0.08321
1.000
0.20505
2016
Relative
(0.00235)
(0.00045)
0.09307
1.000
0.19998
2017
Relative
(0.00231)
(0.00042)
0.08827
1.000
0.17348
15-team League Average Standings
The NFBC offers two different types of 15-team leagues. The “Main Event” is a closer approximation to your typical home league, in that it allows for in season player pickups from the waiver wire. The Draft Championship does NOT allow in-season moves, but you do draft a 50-player team in order to build a deeper roster that might get you through the season without the ability to add anyone.
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).
We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.
