You are here: Browse: Home / Tanner Bell / Page 8

Tanner Bell

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 , , , , , , , , , ,
How To Use SGP To Rank and Value Players During the Season - Part 6 - Adjust Replacement Level

How To Use SGP To Rank and Value Players During the Season – Part 6 – Adjust Replacement Level

Published by on | 2 Responses

Welcome to the final part of the series in which we go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides.  I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In this sixth part of the series we will revisit the concept of replacement level and adjust replacement level for our updated RoS projections.

Reminders About Replacement Level

If you’re new to the concept of replacement level read the introduction here (don’t go into the “Step-by-Step Instructions”.  When you consider the injuries that occur in Major League Baseball, rookies being called up, players underperforming projections, and others exceeding projections, the player pool is constantly changing.

In order to make the best possible decisions and to calculate representative dollar values, it is very important that we update the estimate of replacement level.

Caution:  No Further Adjustments Necessary

When listening or reading fantasy advice, you might come across a piece of advice that goes something like this, “You really need to draft a SS early to account for the lack of depth at the position.  Go ahead and reach for that shortstop.”

Don’t listen to that advice when you’re using the approach we’re now going through.  The replacement level adjustment that follows is already calculating the effect of depth at each position.  And it does it mathematically.  There is no guesswork going on here.

DON’T MAKE ANOTHER ADJUSTMENT.

You do not need to make an arbitrary adjustment to shortstops or catchers, or any other position.  When you have added this adjustment to your rankings, each player will be ranked according the their value over the worst players at the position.  If the position is weak, that’s accounted for.  If the position is deep, it’s accounted for.

If you then decide to make arbitrary adjustments to your rankings after adding in the replacement level calculation, YOU ARE DOUBLE COUNTING.

You will be reaching for players and you will be hurting your team.  Don’t “bump” players up a list because of their position.

Step-By-Step Instructions

(more…)

Posted in | Tagged , , , , ,
How To Use SGP To Rank and Value Players During the Season - Part 5 - Update the Player ID Map

How To Use SGP To Rank and Value Players During the Season – Part 5 – Update the Player ID Map

Published by on

Welcome to the fifth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In this fifth part of the series we discuss updating the Player ID Map to pull new players into the rankings information.

Player ID Map

The SFBB Player ID Map contains the Fangraphs, MLB, Baseball-Reference, Retrosheet, CBS, NFBC, ESPN, Baseball Prospectus, Davenport, and Yahoo player IDs for over 1,200 players.  It’s not a comprehensive list of past players by any means, but I make a concerted effort to have all current fantasy-relevant MLB players and those minor league players likely to make a fantasy impact this season.

PlayerIDMap

To give credit where credit is due, I originally downloaded the player map from Crunchtimebaseball.com and tailored it to meet my needs.  CrunchTimeBaseball is run by Tim Blaker.  He continues to maintain his own map of player IDs and generally keeps his more up-to-date than I do.  You can obtain his version here.

Why Update the Player ID Map

April inevitably brings us players that were never intended to be fantasy relevant; minor leaguers that were not anticipated to make the jump, role players thrust into starting jobs, and more.  As the summer rolls on, impact rookies begin to get called up that may not have been in the preseason Player ID Map.  When September arrives there will be more of the same.

While it is possible to add individual players to the player ID map, it can be inefficient to add more than a handful.  Updating the entire list is probably easier.

Step-By-Step Instructions

(more…)

Posted in | Tagged , , , , ,
How To Use SGP To Rank and Value Players During the Season - Part 4 - Add the IFERROR Formula

How To Use SGP To Rank and Value Players During the Season – Part 4 – Add the IFERROR Formula

Published by on | 1 Response

Welcome to the fourth part in a series of posts in which I’ll go through the process of plugging Steamer’s Rest of Season (RoS) projections into your existing ranking/dollar value spreadsheet so you can make informed and objective roster decisions during the season.

If you register as an SFBB Insider (it’s free), you can receive the entire series in an easy-to-use e-book (also free) along with two other helpful guides. I’ve also written a comprehensive guide on ranking players and calculating player dollar values that’s available at Amazon.

Introduction

In the fourth part of the series we’ll introduce a new Excel formula to help remove lookup errors, like those shown above, from our spreadsheets.  These occur when we have instructed Excel to do a VLOOKUP to find a player’s RoS projections and Excel is unable to find the player ID within the projection data.

Often times a player will stop appearing in the RoS projections.  This might be because they’ve suffered a season-ending injury, they’ve retired, or they’re an unsigned free agent. If that player remains in the list of hitters or pitchers rankings, no projection can be found for that player.  I’ve realized that the rankings and dollar value formulas I previously used did not handle these situations very well, so it’s necessary to adjust these formulas slightly.

Adding this formula to your spreadsheet is a one-time fix.  You won’t need to go through this part when you download updated RoS projections in the future.

Excel Functions in Part 4

IFERROR

The IFFERROR function allows us to control what happens when another function being used is calculating an error.  The image below is a great example of this.  On our “Hitter Ranks” tab we have a series of VLOOKUP formulas that instruct excel to go find Kendrys Morales’ player ID (moralke01) in the “Steamer Projections” tab.  During the 2014 season Morales is likely not included in the RoS projections because he remains unsigned by any Major League team.

IFERROR-Excel-Formula

The IFFERROR function will allow us to replace the error message with any value of our choice.  It essentially works by telling Excel, “If this other formula I’m using comes back with an error, use this instead”.

The formula requires two inputs:

IFERROR(value,value_if_error)

  1. Value – This represents the formula or calculation we want Excel to perform.  In our example above it will be the same VLOOKUP formula we already have entered.
  2. Value_if_error – This represents the value or message we want Excel to return if the first argument, “Value”, returns an error.  In our example above we don’t want the default “#N/A” error message that turns up if Excel cannot locate Kendrys Morales in the RoS projections.  Instead, we could just ask for Excel to return zeroes for his projected stats.

Step-By-Step Instructions

(more…)

Posted in | Tagged , , , ,