Month: August 2014

How To Add Positional Ranking To Your Spreadsheet

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 Continue reading “How To Add Positional Ranking To Your Spreadsheet”

Designing Reusable Fantasy Baseball Spreadsheets

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. Continue reading “Designing Reusable Fantasy Baseball Spreadsheets”

How To Pull a Player ID From a Hyperlink in Excel

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

Continue reading “How To Pull a Player ID From a Hyperlink in Excel”