Category: How To

Everything You Need to Know About the Player ID Map

Contents

This post is intended to be an all-encompassing discussion of the Player ID Map tool. Click the links below to jump directly to a specific section below. Use the Back browser or mouse button to jump back to this list.

What Is the Player ID Map?

The MLB and fantasy baseball landscapes are wide reaching. We fantasy players gather information, projections, and opinions from many locations. Any time you have a situation like this, where data is coming from many disparate places, some form of “mapping” table can help connect the dots and data points from these different sources.

If you’ve ever tried to line projections from Fangraphs up with projections from Baseball Prospectus, Mastersball, or Baseball HQ, you’ll understand this challenge. You may have tried a VLOOKUP in Excel or Google Sheets to line the data up side-by-side. But player names are not a great mechanism to do this. Names can change (see Nick Castellanos and Nicholas Castellanos or B.J. Upton and Melvin Upton). Name conventions can differ between sites (see A.J. Pollock and AJ Pollock or Ronald Acuna and Ronald Acuna Jr.). Even worse, there are occasionally duplicate names (Chris Young being a recent example).

The Player ID Map solves these discrepancies. It enables an owner to line up an A.J. Pollock in their spreadsheet to an AJ Pollock from projection system. The Player ID Map is the bridge that has enabled me to build tools like the Projection Aggregator and the Automated SGP Ranking Tool. It enables building spreadsheet tools and other solutions that can work with or link to major sports websites (ESPN), projection systems (Steamer, Razzball, Mastersball, ATC), and fantasy baseball providers (NFBC, Fantrax, Yahoo!, Draftkings).

What Versions of This Tool are Available?

All the different “versions” below are pointing to the same source information. They are just different formats of using or viewing the data.

  1. Excel Version (LINK) – A downloadable Excel file that can be incorporated into your own fantasy baseball spreadsheets. The data in this file contains connections to the main version I maintain in an online Google Sheet. This version will likely be out of date when it is downloaded, but instructions on how to refresh the data through the connection to the Google sheet are easy-to-follow and are included later in this post. The Excel version contains two tabs:
    • PLAYERIDMAP – All available IDs and naming system information that I track
    • Change Log – Explanations of changes made, including additions, corrections, and dates these changes were made
  2. Web Version of PLAYERIDMAP (LINK) – A live look at the html/web-based version of the PLAYERIDMAP tab in the live Google Sheet. You might use this if you just want to see the Player ID Map or look for specific pieces of information
  3. CSV Version of PLAYERIDMAP (LINK) – A one-time CSV download of the Player ID Map tab of the live Google Sheet. I wouldn’t really recommend using this because there is no connection back to the live data I maintain, like in the Excel version. But it could be used if you only need an easy-to-use one-time dump of the data.
  4. Web Version of Change Log (LINK) – A live look at the html/web-based version of the Change Log tab in the live Google Sheet. This could be used to review recent changes to the central Sheet and determine if a refresh is needed.
  5. CSV Version of the Change Log (LINK) – I have no idea why I make this available. Seems like it wouldn’t be valuable at all! But it’s here if you need it. It’s a one-time CSV download of just the Change Log tab.

How Do I Update or Refresh the Player ID Map?

Note, you will be prompted about the potential danger of downloading Excel files from the internet the first time you download and open the Player ID Map. There are no dangerous macros or harmful code embedded in the file. It does maintain a connection back to my Google source file to enable you to download updated information. Click “Enable Editing” to accept this reminder and be able to interact with the Excel file.

You may at times also see an Excel warning that external data connections can be harmful. These are common warning messages and good reminders that you do have to be careful what you download on the web. Click “Enable Content” to allow the data connection to pull in refreshed player ID data.

If you trust me and do download the Excel file, here’s how to refresh it.

Step Description
1. Select a cell inside of the player ID data. It does not matter which player or piece of data. It just has to be something inside the blue and white table.
2. Right-click on the selected cell and choose the menu option to “Refresh”.
3. You will begin to see various status messages as Excel begins to refresh the connection. You may see information being relayed in the bottom right of Excel.

A popup may appear. And status information may even appear in the bottom left of Excel (I couldn’t grab a screenshot quick enough).

The entire refresh may take 60 seconds or so. And it may depend on how much time has transpired since your last update. You don’t really get a “This is Done!!!” message. You’ll just know you’re ready to proceed when all the statuses stop changing.

Origin

I created the Player ID Map in 2013. I started my ID map largely from information from Tim Blaker’s map. Tim continues to provide updates in his mapping file, but our maps have different purposes. I needed the flexibility to add new names and systems and not be reliant upon someone else. But I continue to use Tim’s map as an input to mine when I’m performing major updates for new players that enter the baseball world.

The Player ID Map has grown since 2013. I continue to add new systems, new name formats, and even new information about each player that will help me provide spreadsheet tools the the SFBB audience.

Do People Really Use This Thing?

I’m surprised at how often it’s used. At the time I write this article, the file has been downloaded or refreshed over 280,000 times (Who knows. 200,000+ of them could be me tinkering in spreadsheets). If I could only figure out a way to charge ten cents each time it’s used…

It’s an integral part to building long-lasting and flexible fantasy baseball spreadsheets that can take advantage of many different sources of baseball data.

Does the Player ID Map Include All MLB Players?

No. The tool is intended to be used for fantasy baseball purposes. Accordingly, the goal is to include only “fantasy relevant” players. That’s a purposely vague threshold. In the preseason, I generally keep the top 750ish players accordingly to NFBC ADP included in the Player ID Map. This should be enough players to cover most normal leagues. It’s possible the Player ID Map will not be deep enough for your 20-team NL-only league, your AL Central-only league, or your middle-reliever contest. Keep reading for advice on how to get more players added to the Player ID Map.

Continue reading “Everything You Need to Know About the Player ID Map”

“The Process – 2020 Edition” Now Available

This year’s edition of The Process is now available!

About the Book

A very thorough and detailed write-up of what’s included in the book is available here. At a high level, this book is everything Jeff Zimmerman and I know about how to play rotisserie baseball (and even points leagues). Regardless your level of experience, I guarantee it includes pages and pages of unique ideas, research, and data you have never seen before. We continue to pour our new ideas, new research, and recent realizations into it. The e-book is 265 pages and includes 58 additional pages of appendices full of projections, statistics, and helpful information.

The paperback edition of the book can be purchased from Amazon.com by clicking here.

Want more detail about the topics covered in the book? You can see the table of contents here.

Want a testimonial that the book is this thorough and contains the value we say? You can read a Clay Link’s (RotoWire, 2018 TGFBI Overall Champion) introduction to the 2020 edition here. Or you can see what many other respected fantasy analysts (Rob Silver, Rudy Gamble, Eno Sarris, Mike Podhorzer, Mike Gianella, Tim Heaney) had to say about the 2019 edition here.

My Favorite New Part

Keep reading for details on all that was added to this 2020 edition, but my favorite addition is a detailed study that performed on the 2018 NFBC Main Event, including all the player adds, player drops, and final standings of all 34 leagues and 510 teams. The study sought to confirm if the advice and strategies in the book can be observed and corroborated in the actions of the game’s best managers. The NFBC Main Event, with its $1,700 buy in, prestigious name, and overall competition aspect, offers the best laboratory to study this.

That new study delves into many topics:

  • How much does draft position affect the chances of winning a league?
  • Do better owners just accrue more playing time? Or are their players also accumulating more stats per AB and per IP? How much more?
  • How many free agent transactions do the best teams make?
  • How do these better owners spread their transactions throughout the season?
  • How do these better owners allocate their FAAB spending?
  • What bidding patterns can be observed from winning teams?
  • How do these owners allocate transactions between hitters and pitchers?
  • How often do these owners acquire two-start pitchers? Closers?
  • How much season-long value do these owners acquire and drop during the season?
  • How much weekly value do these owners acquire and drop during the season?

For example, here’s a table of data in the section analyzing how owners finish in the standings and the amount of moves spent on closer speculation.

Closer acquisitions in the 2018 NFBC Main Event.

 

A Personal Note

I’m really proud of this book. Or shall I say, this annual publication that we’ve started. But the intent to have annual editions creates a significant challenge. The book’s main strength is its long-term nature. Much of what we discuss are fundamental concepts to success at rotisserie baseball. Those ideas are not going to change much from year-to-year. It’s certainly going to be a test to keep the book relevant and worthy of your time and money. We understand that.

I am confident we can do this. Afterall, I’ve been writing on this site for several years now, exclusively with a long-term slant to my analysis. I don’t have much time to write. So I choose to focus my efforts doing research, writing instructions, and building tools that will have long-term benefits. I simply don’t have time to devote to writing short-term pieces that will be irrelevant within weeks or even days. Despite blogging for several years and having written a handful of books, I still feel as though we’re finding new ways to play and think about rotisserie baseball. I see no shortage of strategy-related questions to research.

You can see this in the topics we expanded in this year’s book. Among the higher-level strategy questions approached in this year’s book are:

  • Are rookies more volatile than established hitters? Does a rookie’s upside offset the possible downside? Do rookie projections differ from the projections of MLB regulars?
  • How much does a player’s previous levels of fantasy-production affect their future performance? Do these players offer a higher return on investment than those that have never before attained a given production level?
  • What are the key differences owners should know about the SGP and z-score player valuation models? Where are these systems similar?
  • How does the cost of closers during the draft compare to their cost in free agency (FAAB)? How does the cost of starting pitchers differ?
  • How much value should multi-position eligibility add to a player’s valuation? (Note: This topic was included last year, but we expanded our thoughts)
  • Having studied how weekly values appear in 12-team leagues during the 2017 season, how much did things change in 2018? How does the appearance of weekly values change in a 15-team league?
  • What do average weekly statistics lines look like for players? For example, what does a $30-35 weekly hitter line look like? What do valuable weekly pitcher statistics look like?
  • What strategies and behaviors can we observe from the NFBC’s amazing data (standings, adds, drops, etc.)? What behaviors lead to success? What beliefs about how to best play rotisserie baseball can we confirm by studying this data?

On top of all this, Jeff and I continue to evolve the way we play the game and we share those changes and decisions with you. Here are the new discussions of strategies, tips, and tools included this year:

  • We co-owned a team together this season. We share what we learned from this experience, both during the draft and in-season. Our general recommendation is that partnering is very helpful, especially for leagues where there is a grind of weekly transactions.
  • To that end, we outline the weekly FAAB process we went through together on that shared league. Having a consistent weekly routine is the key to uncovering valuable players and to avoiding under- or over-bidding. We share the process that works for us.
  • We outline the specific tools we use to identify FAAB targets and two-start pitchers. We also share the process we go through for setting lineups and finding important last-minute MLB news.
  • We explored different approaches to dealing with catchers this past season. We share the results of those strategies.
  • We share a FAAB binning strategy that helps owners stay disciplined about overbidding and maintaining a healthy weekly budget.

Do you have to read through the entire book hunting for what’s new? No! We kept track for you. You can see a full list of changes and jump right to the updated content. Here’s what that list looks like.

Please Click the ‘Buy Now’ Button Below to Purchase the e-Book for $17.99

After clicking the “Buy Now” button, you’ll be taken through an online checkout process using PayPal. There is also an option to pay with a debit or credit card. After completing the purchase, a link to download the PDF book will immediately be e-mailed to you. You can read the PDF on any mobile device, PC, or tablet.

 

Or Click Below to Buy the Paperback Edition at Amazon for $22.99

Click here to buy The Process – 2020 Edition: Integrating Valuations and Biases into a Winning Fantasy Baseball Formula at Amazon.com.

New Excel Tool – Automated SGP Rankings and Dollar Values

I’m a firm believer that using customized projections and running those through a valuation system, like standings gain points, is the single biggest leap a fantasy owner can take in improving at fantasy baseball. The problem with taking that step is it’s a significant hurdle to get over.

It’s complicated. It takes learning advanced Excel skills. It’s time consuming. It’s not for everyone.

I’ve been hard at work to help solve these problems. It’s taken several years, but I’m finally able to announce the new Automated SGP Rankings Excel Tool. With this Excel tool, you’ll be able to calculate rankings and dollar values from your favorite projection set within minutes. You can use Steamer, any other Fangraphs projection set, Rotowire, Mastersball, and even PECOTA. Whatever projection set you have access to should work.

You can have tailored rankings and dollar values within minutes using the Automated SGP Rankings Tool.

Interested in learning more about this tool? Watch the following video or click here to read all about its features.

The Automated SGP Rankings Excel Tool is now available for the 2019 season! This tool will save you huge amounts of time. You won’t be stuck troubleshooting Excel formulas. You can focus on player research and forming your own opinions about players. You’ll have custom dollar values to make decisions from. Those values will be tailored to your league’s specific settings. It’s a great step toward winning your league this upcoming season.

The Excel tool currently works with the following rotisserie categories:

Hitting Categories Pitching Categories
Runs (R) Wins (W)
Home Runs (HR) Saves (SV)
Runs Batted In (RBI) Strikeouts (K)
Stolen Bases (SB) Earned Run Average (ERA)
Batting Average (BA) Walks + Hits per Inning Pitched (WHIP)
On Base Percentage (OBP) Quality Starts (QS)
Slugging Percentage (SLG) Holds (HD)
On Base Plus Slugging (OPS) Saves + Holds (SV+HD)

Click here to read more about its features and to purchase the tool.

“The Process – 2019 Edition” Now Available

The Process is now available!
Twitter 1500x500

I partnered Jeff Zimmerman (Fangraphs, Rotowire, Fantrax, multiple-time Tout Wars Champ) to write this comprehensive e-book guide (PDF) that outlines the start-to-finish process we go through during a fantasy baseball season. Please click here to buy The Process e-book.

 

How to Win Your League

The book is a chronological guide through the fantasy baseball season, with the main goal being to help you win your league. The topics covered are:

  • Use of Projections
  • How to Adjust Projections
  • How to Convert Projections to Values/Rankings
  • How to Adjust Values and Rankings
  • Draft & Auction Preparation
  • Draft & Auction Strategy
  • In-season Management & Strategy
  • End-of-season Management & Strategy
  • Wrapping Up the Season

Player valuation and how it is determined are big themes in the book.
Player valuation and how it is determined are big themes in the book.

As you read through that list, you may be thinking, “I already know that topic,” or, “What more could be said about that?” But that is what I’m most proud of. I think we managed to provide unique perspective, insights, and studies that have not been seen before.

If you’re not an experienced owner and you’re looking for a place to start, The Process can help you too. It is a comprehensive guide, but we also present shortcuts and alternate ways of doing things. You can pick and choose the topics or areas in which you want to expand your game. Adding one or two new strategies or tactics to your own process each season is a great way to improve over time.

I’m also very proud of the way we were able to weave in a lot of theory, so owners are not just presented with a way of doing things, but can also understand the “why”, so it can be applied to similar situations in the future.

Cognitive biases and other decision-making concepts are also sprinkled throughout the book. We believe this combination of process, theory, and decision-making tactics makes The Process a unique tool for fantasy owners.

Tell Me More About What’s Included

One of the more interesting studies included in the book is around the concept of weekly player values. Much of the research and decision-making fantasy owners do centers around annual valuations for players. Yet outside of draft and hold leagues, we don’t make decisions on an annual time horizon. Most owners must make decisions on a weekly or even daily basis. A study of weekly player valuations sheds light on how well we capture value in the preseason, what kinds of players create weekly value, and when new value appears during the season.

This table is one of many in a study of how well fantasy owners do at capturing weekly hitter and pitcher value.
This table is one of many in a study of how well fantasy owners do at capturing weekly hitter and pitcher value.

The book also includes average standings data and standings gain points calculations for many popular league variations. Save hours of time having to calculate these yourself!

Example AL-Only OBP Average League Standings Data
Example AL-Only OBP Average League Standings Data

This data is provided for the following league types:

  • 15-team, Standard League (14 hitters, 9 pitchers)
  • 15-team, 1-Catcher League (2 utility hitters)
  • 15-team, OBP League (instead of batting average>
  • 15-team, Draft and Hold League (no in-season pickups)
  • 12-team, Standard League
  • 12-team, OBP League
  • 12-team, AL-only Standard League
  • 12-team, NL-only Standard League
  • 12-team, AL-only OBP League
  • 12-team, NL-only OBP League

What Do Others Have to Say?

You don’t just have to take my word for it. Some of the minds I most respect in the fantasy baseball community have taken the time to read the book and offer their feedback (Rob Silver, Rudy Gamble, Eno Sarris, Mike Podhorzer, Mike Gianella). You can see what they had to say here.

Please Click the ‘Buy Now’ Button Below to Purchase the e-Book for $17.99

After clicking the “Buy Now” button, you’ll be taken through an online checkout process using PayPal. There is also an option to pay with a debit or credit card. After completing the purchase, a link to download the PDF book will immediately be e-mailed to you. You can read the PDF on any mobile device, PC, or tablet.

 

More (Than You Wanted to Know) About Ratio Stats and Standings Gain Points

Most of the concepts used in the standings gain points process of valuing players are straightforward. If there’s one facet of the process that causes the most confusion, it’s the handling of ratio statistics like batting average and WHIP. In the piece that follows, I’ll revisit the basics of what makes ratios statistics different and then I’ll get into two very specific and more complicated questions I often see.

  1. How do you determine the baseline (or league average) ratio to compare the player pool to?
  2. What if you don’t use an accurate measure for that baseline?

Counting Stats are Simple

A player cannot hit a “negative home run” or “lose strikeouts”. Each counting stat helps you move closer to the next team in the standings. So when we are calculating SGP for a player, the counting stats all evaluate out to a positive number. For example, if we determine based on 2017 standings that it takes 8.526 home runs to move up one spot in the standings, Dee Gordon’s two home runs still calculate out to 0.2346 standings gain points. They’re still beneficial in an absolute sense.

Ratio Stats are Messy

When it comes to rotisserie scoring of ratio stats, we are not judged in that same manner. A player with a poor batting average or a bad earned run average can lower your score within those categories. You can have negative ratios that cause you to lose points or fall in the standings. A player can have a negative SGP for a ratio category.

Negative-Standings-Gain-Points-SGP

How Do We Calculate SGP for Ratio Stats?

NOTE: This part is a refresher. If you already have a general understanding of how this works, skip down to the next bolded heading.

Let’s revisit how we calculate things for ratio stats. Directly from my guide “Using Standings Gain Points to Rank and Value Fantasy Baseball Players” (with specific text emphasized):

If we assume a league of 12 teams and 14 hitters on each team (adjust for your roster size), that is 168 players (12 * 14). In the Steamer projections, the top 168 major leaguers are projected for an average of about 509 at bats per player.

This means the average team in this fantasy league will have approximately 7,126 at bats (14 players * 509 at bats). According to Razzball, the average rotisserie batting average in 12-team leagues was .267. This means the average team had approximately 1,902 hits (7,126 * .267). And the average player had 136 hits (1,902 team hits / 14 players).

To find the impact of Trout we need to remove one “average” player from the team and then add in Trout’s projections. We can do the same for Tulowitzki.

13 “average” players * 509 at bats = 6,617

13 “average” players * 136 hits = 1,768

Before I start getting into the intricacies of that process, it’s important to understand that the approach we use to calculate SGP for ratio stats is to find an individual player’s effect on that ratio stat for an AVERAGE team in the standings.

Here’s a closer look at how you would do this for batting average:

Step Description
1. You first need to know what your fantasy league’s average batting average was. In this example, let’s say it’s .267.
2. Determine what the SGP factor is for the ratio stat. In this example, let’s use 0.0019 (the raw factor for the 12-team Online Championships calculated here).
3. You then need an approximation of how many at bats it took to generate that average. In this example, I determined that 168 players (12 teams * 14 players per team) would average 509 at bats. (more on how this was determined later!)
4. Using that information, derive the number of hits the average player had. Knowing the league average and the number of at bats, we can easily figure out that the average player would have had 136 hits (.267 * 509 = 135.9).
5. Next, you extrapolate the 136 hits and 509 at bats per player to team totals. But not a full team of players. ONE LESS PLAYER THAN A FULL TEAM (PER YOUR LEAGUE’S SETTINGS). We’re trying to figure out what an average team looks like without the one player we’re trying to rank.

In this example of a 14-player roster, we’ll use 13 players (one less than a full team). This means the average team (minus one player) would have 1,768 hits (13 * 136) and 6,617 at bats (13 * 509).

It’s CRITICAL that these two numbers hold true to your league average stat. Notice that 1,768 divided by 6,617 is still .267.
6. Then, you add in the projection for the player being evaluated and DETERMINE THE TEAM’S RATIO STAT WITH THAT PLAYER. Assume a player is projected for a .300 average on 200 hits and 667 at bats. The calculation would be:

= (1,768 hits + 200 hits) / (6,617 at bats + 667 at bats)

= 1,968 hits / 7,284 at bats

= .27018 AVG
7. Finally, determine the effect of the individual player by subtracting the average team’s ratio (from step 1 above) from the recalculated team ratio with the player, then divide by the SGP factor.

In our example, this calculation would be:

= (.27018 – .267) / 0.0019

= 1.6737 SGP

Instead of using a player with a .300 average, assume we used a player that dragged the team average down to .264. For this player, the SGP calculation would be:

= (.264 – .267) / 0.0019

= -1.5789 SGP

Negative standings gain points! I said this cannot happen with counting stats… But it CAN AND WILL HAPPEN with ratio stats.

How Do You Determine the Baseline (or League Average) Ratio to Compare the Player Pool to?

When you read through that description above, things generally make sense. But when you actually try to reperform that process, you’ll quickly realize I skipped some steps and take some liberties…

Continue reading “More (Than You Wanted to Know) About Ratio Stats and Standings Gain Points”

How to Get Reliable SGP Data

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.

NFBC_LOGO

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:

NFBC League Type Standings Links Description of League
Online Overall Standings
BA, R, HR, RBI, SB
ERA, WHIP, W, K, SV
  • Standard 5×5 roto categories
  • 12 teams
  • Online 30-round draft
  • Standard 23 player lineup (14 hitters, 9 pitchers, 7 bench spots)
  • Online drafts, February through April
  • Free agency FAAB bidding proccess
  • No trading
Draft Champions Overall Standings
BA, R, HR, RBI, SB
ERA, WHIP, W, K, SV
  • Standard 5×5 roto categories
  • 15 teams
  • Online 50-round slow draft
  • 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.

ROTO_CATEGORIES

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.
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.

Continue reading “How to Get Reliable SGP Data”

Now Available – The Projecting X 2.0 Bundle!

Projecting X 2.0 and the updated Excel template are now available!
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.

PDF (recommended) Buy Now
AZW3 (Kindle) Buy Now
EPUB (Nook, Apple iPad/iBooks, Sony Reader, Kobo) Buy Now
View Cart

Continue reading “Now Available – The Projecting X 2.0 Bundle!”

An Important Lesson and How to Resolve VLOOKUP Errors

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.
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 Fangraphs ID while minor leaguers have text in their ID.
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:

  1. The item (player ID) doesn’t exist where you told Excel to look for it
  2. Or you told Excel to look for the wrong data type (look for a text value in a list of numbers, or vice versa)

These are the downloaded Steamer Projections. Abreu's ID is the there. It's in the first column. Why isn't the VLOOKUP finding this???
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?

Continue reading “An Important Lesson and How to Resolve VLOOKUP Errors”

Important Player ID Map Update

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.

Until now!

The Easy-to-Update Player ID Map

After discovering that you can create a live connection from a Google Sheet to an Excel file, I have moved the Player ID Map into a Google Sheet.

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.

REFRESH_CONNECTION

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.

CHANGE_LOG_PLAYER_ID

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.

Description Information
Updating Player ID Map Excel File Link: https://www.smartfantasybaseball.com/wp-content/uploads/2015/10/SFBB-Player-ID-Map.xlsx

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.
Player ID Map Web Page Link: https://www.smartfantasybaseball.com/PLAYERIDMAPWEB

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.
Player ID Map CSV File Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCSV

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.

Change Log Web Page Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCHANGELOG

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.
Change Log CSV File Link: https://www.smartfantasybaseball.com/PLAYERIDMAPCHANGELOGCSV

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.

Continue reading “Important Player ID Map Update”

New Tool – MLB Team ID Map

Here’s a not so hypothetical scenario…

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.

FANDUEL_SALARIES
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).

FANGRAPHS_PROBABLES
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.
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).

Link to the Web Page Format:
https://www.smartfantasybaseball.com/TEAMMAPWEBPAGE

Link to the CSV File:
https://www.smartfantasybaseball.com/TEAMMAPLINK

Importing the CSV Team ID Map Into Excel

Continue reading “New Tool – MLB Team ID Map”