You are here: Browse: Home / 2014 / Page 2

Archives by date

You are browsing the site archives for 2014.

An Even Better Positional Ranking Formula

An Even Better Positional Ranking Formula

Published by on | 2 Responses

In my last post about how to calculate positional rankings in your player spreadsheet (link), I realized a weakness in the approach I outlined.  I’m here to fix that with a slightly more complex formula. Final_Excel_Formula_Output

The Weakness In The First Approach

The problem with the first approach is that it is entirely dependent upon how the file is sorted.  If the file is sorted by dollar value or standings gain points in descending order, then the rankings work.

But if you sort the file by home runs, stolen bases, or player name, then the rankings fall apart.  The player with the most home runs (or the most stolen bases or the first name alphabetically) becomes the top ranked player.

The COUNTIFS Excel Function

Instead of the COUNTIF function, we’ll use the COUNTIFS function this time.

You might remember from the last post that the COUNTIF function will count the number of cells in a specific range that meet a specified condition (e.g. “Hey Excel, count all the players in this column that have a ‘POS’ of ‘OF’.”).

Well the COUNTIFS function counts the number of cells in a specific range that meet all of the conditions you provide (you can give multiple conditions).

In plain English, our goal is to have Excel count all the players in our list of hitters that have a “POS” of “OF” AND that have a higher projected SGP than our player being evaluated.

We’ll get into the specifics of our formula in a bit, but here’s a screenshot of the Excel formula wizard for our COUNTIFS function.COUNTIFS_FUNCTION

This formula allows for an open-ended number of arguments, but you do need to pieces of information for each set of criteria you want to specify.  Each criteria requires:

  1. Range – This is the block, column, or area of cells we want to count from.  “Excel, look in Column E and count the cells that meet this condition I’m about to tell you about in bullet #2.”
  2. Criteria – This is what we are evaluating the cells for.  “Count the cells in Column E that show ’1B’ as the position.”

If we want to specify three conditions that must be met, then we would need six arguments (three ranges and three criteria: range1, criteria1, range2, criteria2, range3, criteria3).

Step By Step Instructions

(more…)

Posted in | Tagged , , , , ,
How To Add Positional Ranking To Your Spreadsheet

How To Add Positional Ranking To Your Spreadsheet

Published by on | 4 Responses

I just came across an interesting use of an Excel formula that will let you easily determine what a player’s ranking is at their given position (1B-1, 1B-2, 1B-3, etc.).

Here’s an explanation that might show what I’m talking about.  You’re scrolling through your huge list of ranked hitters (see image below).  You have them sorted by Total Standings Gain Points (column V) in descending order.

You see Edwin Encarnacion’s name pop up in row #38.  You know he’s a first basemen, and you can pretty easily determine that he must be the #37th ranked player (by showing up on row 38).  But now you want to know where he ranks amongst only other first basemen.

Without_POS_Rank

In this post I’ll show you a formula we can use to get our spreadsheet to look like this (look at column W):With_POS_Rank_Edwin_Encarnacion

I’m a Moron

I got nearly to the end of this post when I started to think it was weird that Encarnacion was ranked #37, Starlin Castro #32, and Paul Goldschmidt #21. Turns out I used a rankings file from the 2013 preseason for all the screenshots…

I decided against starting over because it’s not the player names that are important, we’re mostly looking at a new formula. And I found it pretty interesting and thought provoking to look at these old lists and see names like Nori Aoki and B.J. Upton so high.

Excel Formulas Used In This Post

Using the “&” to Build Text

As you can see from the image above, we’re trying to take each player’s position (e.g. “1B”) and then add a dash and then the player’s positional ranking (e.g. “1B-8” for Encarnacion).

You can use the ampersand (would you know what that was called without “Wheel of Fortune”?), in an Excel formula to add text from different columns.

A real practical application of this is to build a player’s full name (e.g Billy Hamilton) from their first name and last names being in separate columns.  Here’s an example:

Billy_Hamilton_Jose_Altuve_Craig_Kimbrel_Madison_Bumgarner (more…)

Posted in | Tagged , , , , , ,
Designing Reusable Fantasy Baseball Spreadsheets

Designing Reusable Fantasy Baseball Spreadsheets

Published by on | 3 Responses

Why do your spreadsheets have so many tabs?

When you’re building a rankings spreadsheet, why don’t you just build the calculations right on the projection tab?

Why do you even use this ridiculous Player ID map?  There’s so much useless information on that thing.

Have You Ever Wondered These Things?

I have to say that I haven’t had anyone ask these questions, but I feel the need to address the questions nonetheless.  I don’t mean for them to be overly complicated, but there’s a good chance that the spreadsheets I design can be overwhelming.  So let’s take a closer look at the reasoning behind things.

There Is More Than One Way To Skin a Cat

I don’t think it’s much of a stretch to say that spreadsheet design is a form or art.  There are many different ways to get to the same end goal.  I’m always looking for new ideas and methods to add to my Excel work, and I’m certain there are more efficient and better ways to design things I have done.

With that said, I believe the principles I’m about to talk about are universal.  Whether you like the way I’ve designed things or if you prefer to do things a different way, using these concepts should help you out in the long run.

Why Do Your Spreadsheets Have So Many Tabs?

There are two main reasons for this:

  1. I want all of my spreadsheets to be reusable.
  2. I try to set things up so you only have to enter a piece of information one time.

Reusable Spreadsheets

We’ll take a close look at reusable design in a moment, but think about the different tabs/worksheets I use.  Raw projections, player information, replacement level information, and calculations all on separate worksheets.

By setting these up on distinct areas of a spreadsheet it allows you to easily update one of those components without screwing up the whole model.  Have new projection info?  Just replace the data on the projection tab.  Have a new Player ID Map?  Just copy it over top of the existing one.

This setup makes it much easier for your work to be updated during the season and into future seasons.

Entering Information Only Once

Dollar_Value_InputsIf you’ve read my book on calculating dollar values and in-draft inflation, you’ll recall that we added a “Settings” tab to the rankings spreadsheet.  On that tab we color-coded blue cells to indicate input cells.

These are just basic settings about the league, but by separating them out onto the “Settings” tab you only have to type the information one time.  If we didn’t do this, we would have to embed this information into our calculations for both the hitting and pitching rankings.

Everything is also formula-driven, so if a 13th team is added next year, or if team salary caps increase, or if you want to try a 65% hitting allocation, you can easily change those inputs and all calculations for hitters and pitchers update automatically.

If we didn’t have things set up this way you would have to search deep inside complex formulas on the hitter tab and edit these inputs.  And then you’d have to go do the same thing on the pitcher rankings.

And if you’re in more than one league you can set your rankings up for one league, just change the few settings different between the leagues, and you’ll easily have rankings tailored to each league you play in.

Why Don’t You Just Build Your Calculations Right on the Projections Tab?

Whether you’ve downloaded projections from another website or you’ve created your own, it might seem like I’m over-complicating things by having the projections on one tab and then using formulas to pull the projections to a whole other worksheet where I then calculate rankings, SGP, and dollar values.

If I were to use this spreadsheet one time, I would agree with this line of thinking.  But take a look at this image.

Rankings_Spreadsheet_DesignThis is a very primitive flow-charting model of a rankings spreadsheet.  You have a few sets of raw data as inputs (the blue rhombuses, or is it rhombi?) – the projections, the Player ID Map, and replacement level data. (more…)

Posted in | Tagged , , ,
How To Pull a Player ID From a Hyperlink in Excel

How To Pull a Player ID From a Hyperlink in Excel

Published by on | 8 Responses

Maybe you’re trying to build a the next great daily fantasy baseball spreadsheet.  Or maybe you’re attempting to figure out who to start next week.

You’ve got plans to use a powerful projection tool like Razzball’s Hitter-Tron…  but then you see it.  There’s not a player ID to be found!  How can you take data from a site like Razzball and drop it into your existing rankings, dollar value, or trade evaluator spreadsheets?

Giancarlo_Stanton_Ben_Zobrist_Daily_Projection

You Know Me.  I’m Obsessed With PLayer IDs.

They’re the best way to build a reusable spreadsheet that you can just drop projection data in to.  You can have all sorts of valuation formulas, trade evaluators, or daily roster lineup tools ready to go.  You just drop in some projections with player IDs and everything seamlessly links up and calculates.

Look Closely At the Links

Even if you don’t see a player ID at first glance, on many sites you can still get what you need if you look carefully.

Hover over a player’s hyperlink.  Depending on your browser you should somewhere see the web address for the hyperlink (in Google Chrome the destination of the link shows in the bottom left on the status bar).

Evan_Longoria_Razzball

There’s a player ID in there!  9368 for Evan Longoria.  A quick look at the PlayerID Map tells us the 9368 happens to be Evan Longoria’s Fangraphs ID.

Unfortunately, if you paste the Hitter-Tron information into Excel, you don’t get text about the web address.  You’re left with just the hyperlink itself and no reference to player ID #9368.

Zobrist_Longoria_Cruz_BeltreThere’s not even an existing Excel formula we can use to just pull out the web address.  We have to get a little advanced and create our own.

Step-By-Step Instructions

(more…)

Posted in | Tagged , , , ,
The Difficulty In Aggregating Projections

The Difficulty In Aggregating Projections

Published by on

Now that we’ve established that we can benefit from combining multiple projection models into one, let’s take a look at the challenges this presents.

I’ll also give brief explanations of how you can work around these challenges in Excel.  At the end I’ll discuss an Excel template I’m working on that will do these calculations for you automatically and how you can get your hands on it.

I love Your Feedback

If you’re a SFBB Insider you might recall that after you sign up, the very first e-mail I send you asks you to reply with any fantasy baseball topics you’d like to know more about or difficulties you’re having (if you’re not, you can register here.  I like to think it’s worth your while).

Insider

I Read All Of Those Responses

I’ve been fortunate enough to have nearly 500 people register, and I read every single response that comes in from that question.  One of the most frequent areas of interest is how to average, or aggregate, multiple sets of projections into one usable set of information.

More Difficult Than I Originally Thought

These requests started to roll in during the off-season, and I even replied to several people saying that I thought this was going to be easy and that I’d have guidance coming out soon on how to do this.

… And here I sit months later having never written on the topic yet.

In theory, averaging a set of three numbers in Excel is easy.  If one system says 25 HR, one says 30 HR, and another says 35 HR, Excel’s AVERAGE formula can easily respond with the average of 30.

But I quickly ran into some big problems that greatly complicated things.

Problem 1 – Lining Projections Up To Do The Averages

In order to aggregate multiple projection systems into one, we need a method of “lining up” the projections from one system with those of another system.  Perhaps Giancarlo Stanton is projected to hit 20 HR the rest of the season by Steamer and 22 HR by PECOTA.

Giancarlo_Stanton_ROS
I made this information up just to illustrate the concept of “lining up” different projections.

We can use formulas in Excel (e.g. VLOOKUP) to pull Stanton’s Steamer projection and place it next to his PECOTA projection.  But you can run into some complications in doing this.  What if one projection system lists him as “Stanton, Giancarlo” and the other as “Giancarlo Stanton”.

Using names to pull data also opens you up to inconsistencies in the name being used.  Is it Jonathan Singleton or Jon Singleton?  AJ Burnett or A.J. Burnett?

If you have taken on the challenge of creating your own rankings, you know that we’ve dealt with this problem before, but on a smaller scale.  In my rankings spreadsheets I use a consistent playerID to pull information between the different tabs.  I prefer to use the Baseball-Reference playerIDs because you can tell who a player is (Stanton is “stantmi03” because there were two other Mike Stanton’s before him).

But seemingly every major baseball site has their own player ID system.  Fangraphs says Stanton is “4949”, Baseball Prospectus uses “57556”, ESPN says “30583”, etc.

This is why I maintain the SFBB player ID map Excel file.  The map allows for this translation or “lining up” to happen.  It’s the bridge that can easily help you take Stanton’s projection from one system and place it next to his projection from another.  Giancarlo_Stanton_PlayerID

Problem 2 – Players Not Projected In All Systems

(more…)

Posted in , , | Tagged , , , ,

Should You Combine Multiple Projection Systems Into One?

Published by on | 9 Responses

Should I use this projection system or that one?  Why mess around with the second best system if you can easily determine the best, right?

If you search the web, you can locate previous studies that review the accuracy of baseball’s many projection models.

I Don’t Have Time To Read All That.  Just Tell Me what They Say.

Understood.  Here’s my summary:

  • There area lot of different approaches to projecting stats (Marcel, Steamer, Zips, Oliver,PECOTA, etc.)
    • Basic three year weighted average with regression to league average
    • More than three year weighted averages incorporating more advanced component metrics
    • Crowd sourcing
    • Aging curves
    • Similar player modelling
  • No single projection system is consistently better than the others in all the stat categories we care about for fantasy baseball
  • The most accurate projection model changes from year-to-year
  • But there are some that consistently perform well
  • Some systems do well in projecting offensive statistics
  • Some are better at pitching

What Is Also True

A lot of research has been done on the effectiveness of combining or “aggregating” different projections or forecasts into one.  This research was not done with only fantasy baseball in mind, but we can take advantage of it.  Here’s one very interesting article on the topic (it’s from a website named “forecastingprinciples.com” and is a PDF of a study from the Wharton School of Business at Penn, it has to be legit, right?).

The thinking behind aggregating projections is that the wisdom of many intelligent people looking over a lot of information can lead to better results than just one isolated model for projecting future results.  When you combine all of this together you’ll naturally be removing the outliers from the individual models, but hopefully you’re also improving the accuracy as a whole.

The Actual Results

It may not be appropriate to boil a 15 page research paper into a couple of sentences.  But I’m going to do it anyway!  Here’s what the PDF linked above concludes on the evidence on the value of combining forecasts: (more…)

Posted in , | Tagged ,
How Much Do Current Season Stats Matter?

How Much Do Current Season Stats Matter?

Published by on | 2 Responses

Every major fantasy league hosting site (Yahoo, CBS, ESPN) allows you to look at recent history (e.g. the last 7 days or the last 14 days).  It’s also very easy to see the year-to-date stats any player has accumulated to this point in the season.

Yahoo_Stats
The Yahoo! free agent list allows you to look at the last 7, 14, and 30 days.

And now that we’re nearly half way through the current season, how much do those current year stats mean?  If you’re trying to add a free agent, should you be looking at the last 7 days?  Is the last month OK to use?  How much can we expect production from the first half of the season to continue into the second half?

CBS_Stats
CBS allows you to look at 7, 14, 21, and 28 days, as well as 3 year averages.

Let’s Take A Quiz

Before we get to the answers to those question, let’s put you to the test with some very specific questions.  I’ll lay out a series of “story problem” (remember middle school math?) questions for you .  Place yourself in each situation and make what you think is the best fantasy baseball decision.

Question #1

Your team recently suffered an injury and you must go out to the free agent list and find a replacement.  Which of these measures is the best method of identifying the player who will perform the best for the rest of the season?

  1. Looking at the statistics for free agents in the last 7 days
  2. Looking at the statistics for free agents in the last 14 days
  3. Looking at the statistics for free agents in the last 28 days
  4. Looking at the statistics the free agents have accumulated to this point in the season (season-to-date stats)
  5. Looking at the projected statistics for free agents for the remainder of the season  (like Steamer or Zips rest-of season projections)

Question #2

Which model(s) above do you actually use to make decisions?

Question #3

Which player would you rather have the remainder of the season given these levels of production so far?

Current production (as of 6/22/2014):

Player PA R HR RBI AVG
Nelson Cruz 306 45 23 60 .299
Chris Davis 252 32 12 37 .220

Question #4

Which player would you rather have the remainder of the season given these levels of production and the Steamer RoS projections below?

Current production (as of 6/22/2014):

Player PA R HR RBI AVG
Nelson Cruz 306 45 23 60 .299
Chris Davis 252 32 12 37 .220

Steamer RoS Projections (as of 6/22/14):

Player PA R HR RBI AVG
Nelson Cruz 320 41 17 47 .261
Chris Davis 341 46 20 52 .261

Question #5

Similar scenario to question four above…  But now imagine that we’re five full months into the season instead of at roughly the half way point.  Who would you rather have in the final month of the season?

  • The player who was incredibly hot for the first five months but that projections say will cool off towards his career averages or
  • The player that has struggled for the first five months but is projected to improve and perform closer to his higher level of career averages over the final month of the season?

Question #6

Which player would you rather have the remainder of the season given these levels of production and the Steamer RoS projections below?

Player IP K/9 ERA WHIP
Andrew Cashner 76.1 6.96 2.36 1.19
Homer Bailey 90.0 8.07 4.68 1.45

Steamer RoS Projections (as of 6/22/14):

Player IP K/9 ERA WHIP
Andrew Cashner 103.0 7.29 3.85 1.27
Homer Bailey 95.0 7.99 3.80 1.22

The Research

The information that follows (more…)

Posted in , | Tagged , , ,
Tool Review - FantasyBaseballAlerts.com

Tool Review – FantasyBaseballAlerts.com

Published by on | 4 Responses

I’m in four leagues this season.  Only one of which has weekly lineups. So that leaves me with three teams with daily rosters to manage.

I’m also trying to dip my toes into the strategy of platooning hitters to gain a slight edge on the competition, by employing players like Adam Lind and Seth Smith.

The problem with this strategy is that Lind and Smith are out of the lineup a handful of days each week because their Major League teams are also platooning them.

I usually start my day by setting my lineup, putting anyone without a game on the bench and moving starting pitchers into the lineup.  But I don’t really take the time to check if the Blue Jays or Padres are facing a lefty. This leaves me with a problem.  The advantage I could get by running a platoon is more than offset if I don’t even have a guy in the lineup on a given night.

One Solution

I’ve written previously about the very helpful “My Lineup” tool at BaseballPress.com. As far as I know, they’ve still got the earliest and most accurate lineups available.  They also provide weather information, recent relief pitcher usage information (look at Betances below), and batting lineup info (Allen Craig is batting 4th today). BaseballPressMyLineup That information is second to none.  I love this tool.  But I often leave work at 5:30 PM and don’t get home until 6:30 PM, where I’m greeted by the chaos of my beautiful three and four-year old daughters.  So pulling up Baseball Press before the 7:05 starts is not top of mind.

Another Solution

Right around the time the 2014 season started, I received an e-mail from one of the guys that runs FantasyBaseballAlerts.com.  They wanted to know if I’d be interested in trying out their service (more about that in a minute) for free.

I get a number of requests from sites wanting to run ads here or that want me to put links to their site, and I don’t usually act.  I could probably make a few bucks by running ads, but one of the principles I use in guiding this site is that I’ll only promote or link to services I use, books that I’ve read, or ideas I believe in.

With this in mind, the idea behind FantasyBaseballAlerts.com really seemed beneficial to me.  So I signed up to test it out.

What The Service Does

It’s very similar to BaseballPress, but the information comes to you.  You don’t have to seek it out.

Simply put, you get an e-mail or text message sent to you when a player on your fantasy team is not in the starting lineup for their Major League team that day.

You can also choose to receive either e-mail or text message alerts containing player news, DL information, and more.

Here’s a full list of the types of alerts you can have sent to you:FantasyBaseballAlerts2

Example E-mails

Here’s an example notice about Adam Lind being benched.  Notice that the alert e-mail was sent at 11:52 AM for a 1:07 PM start time.  A pretty solid amount of time to notice the e-mail and make the roster change. FantasyBaseballAlertsAdamLind (more…)

Posted in
Five Lessons About Fantasy Baseball Decision Making

Five Lessons About Fantasy Baseball Decision Making

Published by on | 4 Responses

I probably spend four to six hours commuting to work each week, so I’ve become a huge fan of podcasts as a medium to keep up-to-date on my MLB and fantasy news. Then I recently came across the OverDrive Media Console app which allows me to check out audio books from my local library for free.

After browsing the selection of available books, I was unable to find anything sabermetrically inclined. Nothing by Bill James. Nothing even baseball-related.

But I did come across the book, Thinking, Fast and Slow, by Daniel Kahneman (that’s an affiliate link).It sounded like something I could potentially apply to fantasy baseball, so I gave it a shot.

What It’s About

Turns out there were a lot of connections I could draw to this silly game we play. Our brains have two main thought patterns; fast and slow. Or an instinctual center (System #1) that makes fast assessments and decisions, and a more powerful and deliberate thought center (System #2) capable of in-depth analysis. This video does a great job of explaining the differences between the two systems and even illustrates, if we’re not aware, how the fast thinking system can make incorrect decisions and assessments.

Lesson #1 – We Often Make Very Important Decisions Using Only System #1

One of the anecdotes Kahneman offers is that of a CEO of a large investment company that explained his investment in Ford Motor Company because he, “liked the cars”. Nothing about company earnings, the cost of the stock, quality of leadership, or competitive advantage.

That doesn’t sound like a thorough decision-making process if you were just buying a car for yourself, let alone investing millions of dollars.

We do this in fantasy baseball. We might pick up a player based only on surface stats, without looking at underlying stats like strike out rates, HR/FB, or BABIP.

Or we might drop a valuable player for just the opposite reasons. Making only snap judgements using system #1 and not thoroughly analyzing factors that could make a player’s current stats deceiving or without looking at what a future projection looks like in spite of past performance.

Lesson #2 – System #1 Cannot Be Shut Off.  But You Can Control It With Awareness.

OpticalIllusionThink of the popular optical illusion shown here. (more…)

Posted in , | Tagged ,
How To Evaluate a Trade Using Standings Gain Points

How To Evaluate a Trade Using Standings Gain Points

Published by on | 2 Responses

In this video I’ll show you how to add a Trade Evaluator into your existing rankings spreadsheet.

Here’s an animated image demonstrating the finished product.  This spreadsheet will pull in all the Rest of Season projections for a player, their total SGP to be earned the rest of the season, and the player’s dollar value (provided you’ve added dollar value calculations to your sheets).

TradeEvaluator

I’ll also show you a practical example and explain a few important things to think about when considering trade offers.

The video is roughly 30 minutes long, but keep in mind that just about everything you create by following the guides on this site are long-term in nature.  With a little bit of maintenance, all of these tools can be used all season long AND into future seasons.

The Step-By-Step Process

I start with a spreadsheet that has already been updated with RoS Projections.  I then show you how to add a tab just to evaluate trades and other roster decisions.  We’ll add drop down menus that pull each player’s statistics, dollar value, and SGPs.

This information will enable you to add clarity to all your roster moves.  No more using your gut to analyze a 2-for-3 player trade involving hitters and pitchers.  You’ll be able to see exactly which side of the offer is better.

If you’re new to the site, I would suggest getting familiar with How To Create Your Rankings Using Standings Gain Points.

A Quick Suggestion

WatchVideoDoubleSpeedIf you’re looking for a way speed things up by watching them 1.5 or 2 (double)  speed, cutting down the time it takes to watch significantly.  Just adjust the settings at the bottom of the video player.  Click the cog and change the “Speed to 1.5 or 2.

I also recommend watching the videos in HD.  A lot of the detail in Excel can only be seen well in 720p or higher.

And a Disclaimer

I created this video using Prince Fielder, Jose Abreu, Billy Hamilton, and Ian Kinsler in an example trade.  News that Fielder is facing season-ending surgery came out the next day!  I apologize for this glaring problem with the example, but hope you can still see the power of using a tool like this to evaluate trades and free agency acquisitions.

Here’s The Video

Thanks for Watching

Stay smart.

Questions?  Comments?  Future Video Ideas?

Let me know in the comments below.

Posted in | Tagged , , , , , , , , , ,