You are here: Browse: Home / 2015 / Page 3

Archives by date

You are browsing the site archives for 2015.

How to Add Average Draft Position Info to Your Spreadsheet

How to Add Average Draft Position Info to Your Spreadsheet

Published by on | 7 Responses

I think you’re gonna like this one.

In this post I’ll show you how to link live average draft position information from the web into your draft spreadsheet. Every time you open your rankings file it will pull down updated ADP information. Bam!

How We’re Going To Do This

We will use a powerful feature of Excel called web querying to pull in the ADP information aggregated by FantasyPros.com (please note, I’m an affiliate of Fantasy Pros). The web query will suck up that table of ADP information and bring it directly into Excel for us to then VLOOKUP into our existing “Hitter Ranks” and “Pitcher Ranks” tabs.

Fantasy Pros has the best ADP information I've found on the web. They have the most sources in one place and it appears to be updated regularly.
Fantasy Pros has the best ADP information I’ve found on the web. They have the most sources in one place and it appears to be updated regularly.

Assumptions

I am assuming that you’ve followed my Standings Gain Points or Points-League Ranking series and are already starting with a spreadsheet that is based off one of those (you don’t have to have those exact spreadsheets, but something similar).

Excel Functions and Concepts Used in this Post

Web Query

As you can probably imagine, the power of the web query is that it automatically updates the data in your Excel file without you having to do ANYTHING after the initial setup.

Web queries are created from the “Data” tab on the Excel Ribbon, under the “Get External Data” icon grouping. There are several ways to get data from outside sources into Excel, we will be using the “From Web” button.

GET_EXTERNAL_DATA

One “weakness” I have found in web queries is that they cannot work with “tables” in Excel. You cannot pull a web query in as the data of a table. If you’re a big follower of this site you know that’s a bit of an issue for me because I use tables all the time. Thankfully this doesn’t prevent us from doing things, I just point it out because you might wonder why I set things up the way I do in the instructions below.

Find

The FIND function searches within a specified cell for a string of text that you provide. If the FIND function locates the string, it will return the character position where the string starts at.

I know, you’re thinking “What the heck does that even mean?”. Here’s an example. Let’s say we have the text “Mike Trout (LAA)” in a cell and every player in our whole spreadsheet follows that format. If we want to pull out each player’s team we will need to start by figuring out where the team name starts in that cell. And we can’t just say it will always start at the 13th character each time when we have players like this hanging around MLB.

Instead we can use the FIND formula to intelligently determine where that opening parenthesis is for each player (it starts at 12 for Trout and 23 for Salty).

This formula requires two inputs:

FIND(Find_text, Within_text)

FIND

  1. Find_text – This is the string of text you are searching for and keep in mind it is case-sensitive. You would wrap the string you are searching for in quotation marks. So in our example above, to look for the opening parenthesis you would enter “(” here. Or if you’re trying to be slightly more precise, you could enter ” (“, a space before the parenthesis.

  2. Within_text – This is the text you want to search WITHIN. This can be a cell number.

Left

The LEFT function gives you the leftmost number of characters in a text string. You also get to specify the number of characters to specify.

For example, if you have a text string of “Mike Trout (LAA)” and you ask for the 10 leftmost characters in that string, you would get “Mike Trout” back.

This formula requires two inputs:

LEFT(Text, Num_chars)

LEFT_FUNCTION

  1. Text – This is the text string you want the leftmost characters from.

  2. Num_chars – This is the number of characters you want from the string. This can be a hard entered number (e.g. 10) or it can be a formula itself that results in a number.

Combining Functions Together

We can do something pretty powerful by combining the FIND and LEFT functions together. I’ve been hinting at it with this “Mike Trout (LAA)” example. Recall from above that the LEFT function wants to know our text string (“Mike Trout (LAA)”) and the number of characters on the left to pull from that string.

Assume that cell B2 has a value of “Mike Trout (LAA)”. Instead of using this formula:

LEFT(B2, 10)

We can use this:

LEFT(B2, FIND(" (",B2)-1)

The FIND(" (",B2)-1 part of the formula returns a 10 (if you don’t subtract the one it returns an 11), and “Mike Trout” has 10 characters in it (including the space). By using this combination of functions we don’t have to type in a “10” for Mike Trout and a “21” for Saltalamacchia.

Important Prerequisite

Before you’re able to proceed with the instructions below you must make sure the PLAYERIDMAP in the file you’re working with was updated after February 21st, 2015. I added a column to the Player ID Map called “FANTPROSNAME” that is necessary for the steps below to work.

Instructions for updating your PLAYERIDMAP can be found here. Completing the update should only take five minutes or so.

Step-by-Step Instructions

(more…)

Posted in , | Tagged , , , , , ,
How to Update the Player ID Map in your Spreadsheets

How to Update the Player ID Map in your Spreadsheets

Published by on | 6 Responses

Warning – The instructions below are likely only relevant if you are following some of my much older work. The Player ID Map has since been updated to allow much easier updating. If you’re looking for guidance relating to a spreadsheet you’ve built or purchased since 2015, you likely want to be looking here for guidance relating to the Player ID Map.

You’ve been following the site for a while. You’ve even created a spreadsheet to develop your own points league or SGP rankings. You’ve spent all this time building this spreadsheet but it’s getting to be a bit out of date. Players have been traded, rookies have been called up from the minors…

How do you update things? Do you have to rebuild your spreadsheets from scratch each season?

No way, give me some credit! I’m smarter than that. I design things to be reusable.

In this post I’ll show you how to quickly and easily update the Player ID Map in your spreadsheet so you can get updated MLB teams and have new players available to tie in to your projections.

Warning!

All we’re really doing here is downloading the new version of the Player ID Map and pasting it on top of our existing Player ID Map already in your ranking file. The key is that you have to be very particular about how you paste the new version in. If you’re not careful you will break all the existing formulas in your spreadsheet that reference the PLAYERIDMAP named table.

Read carefully!

Step-by-Step Instructions

Step Description
1. Open your existing rankings spreadsheet, the one in which you want the new Player ID Map information. Save a backup copy of the file, just in case something were to go wrong during this process.

Go to the PLAYERIDMAP tab.PLAYER_ID_MAP
2. We will soon be pasting information onto this sheet so it is important to make sure all the data is currently showing.

Click on Excel’s “Data” tab and then click the “Clear” button of the “Sort & Filter” icon grouping. CLEAR_FILTERS
3. Click this link to download an updated copy of the Player ID Map.

Once the download completes, open the file. If Excel is displaying any kind of warning message, enable your ability to edit the file (provided you trust this site).ENABLE_EDITING

Now refresh the content to pull in any recently added players. Do this by right-clicking on a cell within the table (somewhere within the blue and white rows of data). Then choose the option to “Refresh.”
4. Place your mouse in cell A1 of the newly downloaded Player ID Map. Then hit the CTRL + SHIFT + End keys all at once. After you’ve done this release the keys. Then hit SHIFT + the up arrow key.LAST_PLAYER
This set of key strokes should select the entire Player ID Map table and then deselect the “Last Player”.

Now hit CTRL + C to copy the selected data.
5. Return to your customized rankings spreadsheet. Select cell A1 with your mouse and then paste the data you just copied over cell A1.

The reasoning behind this specific set of copying and pasting instruction is so that the existing table named “PLAYERIDMAP” in your rankings spreadsheet will not be renamed during this process. If you don’t deselect the “Last Player” before copying, the entire Player ID Map table will be renamed and it will break all existing VLOOKUP formulas you have looking for this information.
6. That’s it!

Well, kind of. Any new players added to the PLAYERIDMAP will not yet be listed on your “Hitter Ranks” or “Pitcher Ranks” worksheets.

This is where you have a decision to make.

If you have taken notes next to players, entered keeper dollar values, or otherwise “hard entered” information that relates to a specific player, then you manually add the player IDs of “new” players to your “Hitter Ranks” or “Pitcher Ranks” tabs.

For example, simply go to the “Hitter Ranks” tab and type the player’s ID at the very bottom of the first column. When you hit enter the Excel table should grow to add your new player and all the other formulas should automatically copy down (another benefit of using Excel tables!). FRANCISCO_LINDOR

If you’re not sure what players were added to the PLAYERIDMAP, you can look on the “CHANGE LOG” tab on the newly downloaded Player ID file to see a brief note of all the players added or updated recently. CHANGE_LOG

I try to put brief descriptions of the players that have been added so you can manually add to your “Hitter Ranks” or “Pitcher Ranks” sheets, if necessary.CHANGE_LOG_INFO

7. If you have not edited dollar values or added player notes, you can copy and paste the hitter IDs onto the “Hitter Ranks” sheet and the pitcher IDs on to the “Pitcher Ranks” sheet.

To do this, go to the PLAYERIDMAP tab in your spreadsheet and apply a filter to only show hitters. On the “POS” column filter, uncheck the “N/A” (if there are any) and “P” check boxes. This will only display the hitters.POSITION_FILTER

Then select cell the first cell below the header in column A and hit the SHIFT + CTRL + Down Arrow Key. SHIFT_CTRL_DOWNCopy this information and go to your “Hitter Ranks” tab and paste it into the first cell below the header in column A there.PASTE_HITTER_LIST

After you do this all the other information on the tab should update immediately.

No go back to the PLAYERIDMAP tab and adjust the filter to only show pitchers and repeat the process by pasting those players onto the “Pitcher Ranks” tab.

Now you’re done!

Have Any Questions?

Please leave a comment on this post.

I have to do this quite frequently to keep all the spreadsheets I maintain for the site up-to-date, but this is probably something you’ll only need to do a few times a year. Maybe after the season ends, to get all the new players I’ve added during the season, late February, to get all the players that have changed teams, and once during the season, if you’re doing in-season rankings.

Want More Tips Like This

Make sure to follow me on Twitter, that’s the best place to hear about new posts and updates at the site.

Posted in | Tagged , , , , ,

Easily Combine Multiple Projection Systems

Published by on

COMPARE_HITTERS

After over a year of working on this and getting feedback from a very helpful group of SFBB readers, the “Projection Aggregator” Excel file is finally ready!

The Projection Aggregator is an easy to use Excel spreadsheet that can combine (or average) up to three different projection sets to give you the best possible set of projections to use for the upcoming season. You can use just about any well known projection source you have at your disposal. Download your favorite projections, fill out some settings, and you’re done.

No complicated formulas. No VLOOKUPs. Just download your projections, bring them in to the Aggregator, and you’ll have better projections in minutes. Click here to find out more.

Posted in , | Tagged , , ,
New Excel Tool - SGP Slope Calculator

New Excel Tool – SGP Slope Calculator

Published by on | 21 Responses

I can’t believe it took me so long to think of this! In this post I’ll share a new Excel template I created to help calculate your SGP factors (or SGP denominators).

One of the very first posts I wrote on this site was about “Calculating What it Takes to Win Your League”. I’ve then gone on to write many articles about standings gain points and even about an improvement in the process I found by reading Art McGee’s book “How to Value Players for Rotisserie Baseball” (It’s quite expensive at Amazon but Baseball HQ has had it on “Inventory Closeout” for quite a long time).

So after a few years of writing about SGP it finally dawned on me that, “I should create an Excel file that wraps all this together.” So that’s what I’ve tried to do. The “SGP Calculator” asks a few simple setup questions about your league, the amount of history you want to use, and then uses the SLOPE formula to calculate the standings gain points for each of your league’s scoring categories.

Here’s How the “SGP Calculator” Works

The first tab is aptly named “ANSWER THESE QUESTIONS FIRST”. Several of these are very important in determining how the slope calculations are made so don’t skip over this.SGP_SLOPE_SETUP_QUESTIONS

The Excel file can accommodate up to eight different hitting and pitching categories, up to 10 years of standings information, and up to 15 teams. Use the drop down menus on this tab to select your league’s setting.

The next tab in the spreadsheet is “SGP CALCULATIONS”. Let’s skip over that for now and circle back to it.

SGP_CALCULATIONS

The yellow colored tabs are where you will enter your league history of the overall standings and the totals in each individual category. There are tabs for “Historical Final Standings” (where you’ll enter the total ending roto points for each team), “Hitter Stats”, and “Pitcher Stats”.

There are 17 yellow tabs. One for overall standings and then eight each for hitting and pitching categories.
There are 17 yellow tabs. One for overall standings and then eight each for hitting and pitching categories.

Each yellow tab displays the “Category Name” in the top left (see “Batting Average” example below). You can enter the history for that category in the table below. Because the spreadsheet has been set up to accommodate 15 teams and 10 years of history you may see several rows and columns with labels “DO NOT ENTER DATA”. Just ignore those rows and columns and only enter data for the years and number of teams you have selected.

The image is large but I had to shrink it to fit here. Click the image to see the full version.
The image is large but I had to shrink it to fit here. Click the image to see the full version.

Now we can return to the “SGP CALCULATIONS” tab, the end result. It displays the average statistics for each scoring category and uses Excel’s SLOPE formula to calculate the SGP factor (denominator) for each category.

Click the image to see the full-sized version.
Click the image to see the full-sized version.

These are the same SGP factors you would then plug into your customized rankings.

Download the File

(more…)

Posted in | Tagged , , , ,
How To Calculate Custom Rankings for a Points League: Part 6 - Replacement Level and Position Scarcity

How To Calculate Custom Rankings for a Points League: Part 6 – Replacement Level and Position Scarcity

Published by on | 19 Responses

Welcome to the first part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league.  These instructions can be used for a season-long points league or a weekly head-to-head points league.

If you’re looking for info on how to rank players for a roto league, look here.

I recommend going through all the parts of the series in order. If you missed an earlier part of this series, you can find it here:

smartfantasybb_3d2 - 500x635

Please note that this series has been adapted into a nine-part book that also shows you how to convert points over replacement into dollar values and how to calculate in-draft inflation. Click here if you’re interested in reading more about the conversion to dollar values.

ABOUT THESE INSTRUCTIONS

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

IN PART 6

In this part of the series we will discuss the concept of replacement level, prove that it can lead to better decision making, demonstrate how it is an objective measure for making positional scarcity adjustments, and then incorporate replacement level adjustments for each position into our projected point values.

Accounting For Replacement Level

Heading in to the 2015 season, Ryan Braun is projected by Steamer to produce 82 R, 25 HR, 82 RBI, and 13 SB (or 752 points in my example league).   Buster Posey is projected for 69 R, 19 HR, 75 RBI, and 1 SB (681 points).

Braun’s raw production is clearly superior to that of Posey.  But is that all we need to look at to conclude which player is more valuable?  Don’t we need to include some measure of “replacement level” in this calculation?  Isn’t that what WAR is all about?  Wins Above Replacement?

How do I account for the fact that the day after our fantasy draft I can go out to the free agent listing and pick up an OF that would produce 61 R, 10 HR, 47 RBI, and 15 SB (478 points), or a Catcher that would produce 38 R, 9 HR, 45 RBI, and 7 SB (319 points)?

Clearly the replacement catcher is much less productive than the replacement level OF.

Using Points League Settings

You’ve been following me through the creation of a rankings file for an example league. We just finished converting projected statistics into point values for this league, so let’s take a look at comparing Braun to Alejandro De Aza (a hypothetical replacement level OF) and Posey to Christian Bethancourt (a hypothetical replacement level catcher).

Player Projected Points
Ryan Braun 752
Alejandro De Aza 478
Buster Posey 681
Christian Bethancourt 319

Braun is projected for 274 points over the replacement level outfielder and Posey is projected for 362 points more than the replacement level catcher!

That means Posey is roughly 88 points more valuable than Braun, despite having lower overall projected points.

If you’re having a hard time digesting that, think of it this way.  Let’s assume Braun and Posey represent second round draft picks (just go with it, don’t argue) and De Aza and Bethancourt will be last round draft picks (replacement level).

The team that takes Braun in the second round and Bethancourt in the last round would be projected for 1,071 points.  The team that takes Posey in the second round and De Aza in the last round would be projected for 1,159 points.  Again, that’s 88 more points than the Braun/Bethancourt combination!

This is why considering replacement level matters.

Positional Scarcity Adjustments

You have probably come across suggestions or you might have even thought to yourself that you should “bump” a player up your rankings because he plays a weak position.  But is this really appropriate?  How much do you bump him up?

Another great benefit of incorporating replacement level into your rankings is that it makes your positional scarcity adjustments for you!

You just saw how we proved Posey’s 681 points as a catcher are more valuable than Braun’s 752 from the outfield.  Rather than arbitrarily “bumping” Posey in the rankings, we can figure out exactly where he should be ranked by calculating his “Points Above Replacement”.

Let’s look at the top 15 projected hitters in my example points league.PROJECTED_TOP_15

Not a catcher to be found.  But if we presume this league has 24 starting catchers (you need to read this if you play in a two-catcher league), things change significantly when we calculate points above replacement.TOP_15_OVER_REPLACEMENT

Three catchers rocket into the top 10 while OF and 1B are devalued some.  This movement that takes place after you calculate Points Over Replacement Level IS THE POSITIONAL SCARCITY ADJUSTMENT.  Players move exactly the proper amount.  No guesswork.

EXCEL FUNCTIONS AND FORMULAS IN THIS POST

Nothing really new here.  We’ll just be using things we’ve already used in earlier parts of the series.  We will use another VLOOKUP formula, create a table, and use structured references to build some formulas.

STEP-BY-STEP INSTRUCTIONS

(more…)

Posted in , | Tagged , , , , , , , ,
How To Calculate Custom Rankings for a Points League: Part 5 - Calculating Projected Points

How To Calculate Custom Rankings for a Points League: Part 5 – Calculating Projected Points

Published by on | 7 Responses

Welcome to the first part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league.  These instructions can be used for a season-long points league or a weekly head-to-head points league.

If you’re looking for info on how to rank players for a roto league, look here.

I recommend going through all the parts of the series in order. If you missed an earlier part of this series, you can find it here:

ABOUT THESE INSTRUCTIONS

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

IN PART 5

In this part of the series we will use the named cells created in Part 2 along with our projection information on the “Hitter Ranks” and “Pitcher Ranks” sheets to calculate total projected points for each hitter and pitcher.

Please note that this series has been adapted into a nine-part book that also shows you how to convert points over replacement into dollar values and how to calculate in-draft inflation. Click here if you’re interested in reading more about the conversion to dollar values.

EXCEL FUNCTIONS AND FORMULAS IN THIS POST

We’ll just be doing some basic addition and multiplication.  We won’t be adding in any new features, but we will be doing this basic math using the named cells for your league’s scoring settings that we created in earlier parts of the series.  All_Point_ValuesTo refresh your memory and to see the complete list of named cells, access the “Formulas” tab of the Ribbon and then click the “Name Manager” button.Name_Manager

The list will display all named cells/ranges and named tables.  To view only named cells, click on the “Filter” drop down menu and choose “Defined Names”.Defined_Names

STEP-BY-STEP INSTRUCTIONS

(more…)

Posted in , | Tagged , , , , , , ,
How To Calculate Custom Rankings for a Points League: Part 4 - Pitcher Rankings

How To Calculate Custom Rankings for a Points League: Part 4 – Pitcher Rankings

Published by on | 17 Responses

Welcome to the first part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league.  These instructions can be used for a season-long points league or a weekly head-to-head points league.

If you’re looking for info on how to rank players for a roto league, look here.

I recommend going through all the parts of the series in order. If you missed the beginning of this series, you can the earlier parts here:

ABOUT THESE INSTRUCTIONS

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

IN PART 4

In this part of the series we will again use Excel’s VLOOKUP and IFERROR formulas as well as Table and Structured Reference features, but this time to pull pitcher information and projections to create our pitcher rankings tab.

EXCEL FUNCTIONS AND FORMULAS IN THIS POST

This is where I normally give detailed explanations of each Excel feature and formula used in the instructions below; however, we’re not introducing anything new in Part 4. If you would like more background on the features and formulas used below, please refer to Part 3 or ask questions in the comments area below.

STEP-BY-STEP INSTRUCTIONS

(more…)

Posted in , | Tagged , , , ,
How To Calculate Custom Rankings for a Points League: Part 3 - VLOOKUP, Excel Tables, and Structured References

How To Calculate Custom Rankings for a Points League: Part 3 – VLOOKUP, Excel Tables, and Structured References

Published by on | 20 Responses

Welcome to the third part of the “How To Calculate Rankings For a Points League” series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

If you’re looking for info on how to rank players for a roto league, look here.

I recommend going through all the parts of the series in order. If you missed an earlier part of this series, you can find it here:

ABOUT THESE INSTRUCTIONS

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

IN PART 3

In this part of the series we’ll use Excel’s VLOOKUP and IFERROR formulas as well as Table and Structured Reference features to pull hitter information and projections from other areas of the spreadsheet in order to create our hitter rankings tab.

EXCEL FUNCTIONS AND FORMULAS IN THIS POST

Below are the Excel functions and formulas used in this part of the series. If you’re already familiar with what these are, you can skip ahead to the step-by-step instructions.

VLOOKUP

One of the most powerful Excel formulas, in my opinion. And it’s easier to use than you might think.

This formula searches the first column of a table for a desired value (a player ID) and then returns a value that is in the same row but in a separate column. For example, we might tell Excel to go into a table of projection data, locate the player ID for Billy Hamilton (10199), and give us back the number in the fourteenth column (column N, which holds the number of SBs).

BILLY_HAMILTON_PROJ_SB

This formula requires four inputs:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)VLOOKUP

  1. Lookup_value – This is the value we want to search for in the table of data (e.g. Billy Hamilton’s player ID 10199). In the rankings spreadsheet, we’re mostly going to use player IDs for this. “Hey Excel, go look for this player ID”.
  2. Table_array – This has to be two or more columns of data. Excel will look for the Look_up value in the first column in the set of data. You do not necessarily need to include the first column on a spreadsheet tab (you don’t have to use Column A of a sheet, the first column of your table could be Column G). But Excel is going to look through the first column you provide. “Hey Excel, here are fifteen columns of data for you, look through everything in the first column for the Lookup_value.”
  3. Col_index_num – This is the column number that of the Table_array that contains your desired information. This has to be a number and it has to be within the Table_array you provided. For example, if your table_array only has five columns, but you put a six for Col_index_num, you’ll have a problem. “Hey Excel, the fourteenth column has projected stolen bases. After you find Billy Hamilton’s player ID, tell me how many stolen bases are in that fourteenth column.”
  4. Range_lookup – This input can be either “TRUE” or “FALSE”. If you use “TRUE”, Excel will look for an approximate match of the lookup_value (PLAYERID). If you enter “FALSE”, Excel will only look for an exact match. This is an optional input, but I feel very strongly that it must be used and that “FALSE” is the option selected. You may otherwise get the wrong projections showing up for players.

TABLES (NAMED RANGES, Structured References)

Similar to how we named individual cells in the last part of our series, Excel has functionality that allows you to convert a block of data (player projections) into a named table. There are quite a few benefits to using tables:

  1. Tables have names.  This is great for the Table_array input in the VLOOKUP formula. We can give the projection sheet the name “STEAMER_H” (for Steamer Hitters projections) and use that instead of traditional way of selecting data in Excel (something like’Steamer Hitters’!A1:W500).  Not only is this a huge time saver (using your mouse to scroll and select 20 columns and 500 rows takes a long time), but it gives your formulas meaning.  When you look back at your VLOOKUP formula and see “STEAMER_H”, you’ll easily be able to remember that you’re looking up projected Steamer hitter information.
  2. Columns have names. I have a hard time remembering what column projected HRs are in. But I don’t need to if I know that the column name is “HR”. If you don’t use a table, you’re stuck trying to remember things like, “were HRs in column G, H, or I?”. And then you have to figure out if column I is column number 8, 9, or 10?  When referring to a column, use the following convention – TABLENAME[COLUMNNAME]. The column name is surrounded in brackets.
  3. Formula consistency. In a table, all formulas within a column are identical. When you change the formula in one cell of a column, the rest of the column automatically updates too. No more editing a formula in one cell and having to copy it to hundreds of other cells in the same column.
  4. Easy sorting and filtering. As easy as clicking a drop down arrow.EXCEL_TABLE_SORT

COlumn

This function returns the column number of a cell or range of data. The function only requires one input; the cell or range to be evaluated:

COLUMN(TableName[ColumnName])

Let’s use a real example to illustrate:

COLUMN(STEAMER_H[SB])

This formula will look for the stolen bases column in the Steamer Hitter Projections and will return the column number.  If SB are in column N, this formula calculates to 14.

IFERROR

The IFFERROR function allows us to control what happens when another formula being used is calculating out to an error.

The image below is a great example of this. In this spreadsheet we have a series of VLOOKUP formulas that instruct Excel to go find Kendrys Morales’ player ID (moralke01) in the “Steamer Projections” tab.

You may recall that during the 2014 season Morales remained an unsigned free agent until well into the season, so he was not included in the Steamer projections. Because he was not included, the VLOOKUP formula could not find his player ID and could only calculate to this “#N/A” error message.

IFERROR-Excel-Formula

The IFERROR 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”.

Using IFERROR we could instead make Kendrys Morales line look like this:

KENDRYS_MORALES_IFERROR

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”, happens to be 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 , , , , ,
How To Calculate Custom Rankings for a Points League: Part 2 - Set Up League Scoring Settings

How To Calculate Custom Rankings for a Points League: Part 2 – Set Up League Scoring Settings

Published by on | 3 Responses

Welcome to the second part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league.  These instructions can be used for a season-long points league or a weekly head-to-head points league.

If you’re looking for info on how to rank players for a roto league, look here.

I recommend going through all the parts of the series in order. If you missed Part 1 of this series, where we downloaded hitter and pitcher projections and started to set up the rankings Excel file, you can find it here.

About These Instructions

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

In Part 2

In this second part of the series, we’ll create a new tab in our Excel file to document our league’s scoring settings.  We’ll use Excel’s “Name” feature so we can calculate projected points for all players and easily make changes to the scoring system in the future.

Excel Functions and Formulas In This Post

Below are the Excel functions and formulas used in this part of the series.  If you’re already familiar with what these are, you can skip ahead.

Named Cells (or Named Ranges)

Excel has a feature called “Names”, where you can give a cell, a group of cells, or an entire table a name.  After giving a cell a name, you can then use the name in calculations.

In this part of the series we’ll give names to the point values of each scoring category for our league. Let’s assume each HR for a hitter is worth 9 points.All_Point_Values

We can name this point value as “H_PTS_HR”.

If we’re later trying to determine how many points Javier Baez will earn from home runs, we can multiply Baez’s projected home runs by “H_PTS_HR” instead of multiplying by 9 or by cell B7 (in the image above).
Javier_Baez_HR

If you’re familiar with the basic concepts of computer programming, setting the point value of home runs equal to a name is like using a variable.

The benefit of this is that we can later change the 9 to a different value. This is especially useful if we play in multiple leagues or if the scoring format changes.

It becomes very easy to change the scoring system without having to search within complex formulas. And we also don’t need to flip back and forth and remember that the HR point value was in B7 (as opposed to B6 or B8).

Using a name also gives the formula “meaning”.  It’s a lot easier to understand this:

= B2 * H_PTS_HR

than it is to understand this:

= B2 * B7

Another helpful benefit is that Excel makes all of your Names available through a type-ahead feature.  So if you know you have a series of Names that start with “H_PTS_”, you can see all of them by typing out part of the name. This works no matter what tab of the Excel file you are on, meaning you don’t have to flip back and forth to figure out the exact cell holding the point value.

List_Of_Names_H

Using Cell Shading To Indicate Meaning

As we continue to build a spreadsheet that will eventually be quite complex, it can be helpful to indicate which cells are “input” cells and which ones are “calculated” (formula) cells. An easy way to denote this is by using cell shading (or coloring).

An “input” cell would be facts or assumptions that will later be used in formulas for calculating projected points and player rankings.  For example, the number of points for each home run hit or the number of teams in the league.

A “calculated” cell will obviously be some kind of formula we enter in Excel.

The color coding will help remind you what cells you can change in the future (inputs) and what cells you should leave alone (formulas, calculations).

You can use your own color scheme, but for the rest of the series I will shade cells blue if they are “input” cells and a light red if they are “calculated” cells.

Not to mention that color coding your spreadsheets also offers a nice intimidation factor to your opponents at the draft table.  Who knows, someone might just think twice about getting in a bidding war with you if you’ve got a kick ass spreadsheet on-screen.

Adjust For Your League’s Scoring Settings

The example league I’m using to create these rankings for is loosely based on a scoring system suggested for Fangraphs Ottoneu leagues.  It is likely very different than the scoring system in your league.  Accordingly, you will need to use judgment and tailor the steps below to fit your league’s specific scoring system.

STEP-BY-STEP INSTRUCTIONS

(more…)

Posted in , | Tagged , , , ,
How To Calculate Custom Rankings for a Points League: Part 1 - Download Projection Data and Player ID Map

How To Calculate Custom Rankings for a Points League: Part 1 – Download Projection Data and Player ID Map

Published by on | 5 Responses

Welcome to the first part of a series in which we’ll go step-by-step through the process of using Microsoft Excel to calculate your own rankings for a fantasy baseball points league (as opposed to rotisserie or head-to-head rotisserie).

Whether you’re in a standard points league at a major site like ESPN or a more advanced Ottoneu league at Fangraphs, this process will help you develop customized rankings for your league.  These instructions can be used for a season-long points league or a weekly head-to-head points league.

If you’re looking for info on how to rank players for a roto league, look here.

In this first part of the series we’ll start a new Excel file and download projection information.

Why I Created This Series

Points leagues seem to be like fingerprints and snow flakes. Each one is a little different than the others. I’m a big believer that in order to be a strong fantasy player, you need to create your own rankings and dollar value calculations that are tailored specifically to the league(s) you play in.

I’ve also felt that nobody takes the time to explain exactly how to create your own rankings. If you look hard enough you might find an article that gives a quick bullet point list.  Maybe something like:

  • Download projections
  • Multiply projections by your point system
  • Adjust for replacement level
  • You’re done!

I’m going to be a little more thorough than that.

In Part 1

In this first part of the series we’ll download hitter and pitcher projections, take a look at and download player ID information, and bring all of this information into one Excel file.

ABOUT THESE INSTRUCTIONS

  • The projections used in this series are the Steamer 2015 preseason projections from Fangraphs.  If you see projections that you disagree with or that appear unusual, it’s likely because I began writing this series in December 2014, still early in the off-season.
  • For optimal results, you will want to be on Excel 2007 or higher.  Some of the features used were not in existence in older versions.
  • I use Excel 2013 for the screenshots included in the instructions.  There may be some subtle differences between Excel 2007, 2010, and 2013.
  • I can’t guarantee that all of formulas used in this series will work in Excel for Mac computers.  I apologize for this.  I don’t understand why Excel operates differently and has different features on different platforms.

Where To Get Projections

There are many solid projection systems available for download.  Some very fine projections are available at membership sites like Baseball Prospectus or Baseball HQ.

But if you’re like me, when I started out on the adventure of calculating my own rankings, I wasn’t looking to pay for something I wasn’t sure I’d be able to translate into fantasy success.  It’s great to have accurate projections, but how do you use them if you can’t take the next step to rank and value those projections?

For that reason, I’m partial to the Steamer projections.

They’re available in easy-to-use Excel downloads (specifically CSV) at Fangraphs. There are also daily rest-of-season updates, meaning on any day you can see the projections for the remainder of the season for any player.  Steamer does a good job of projecting playing time (if a player gets hurt, they try to estimate the effect on playing time).

And they’re free.

Use Whatever Projection System You Prefer

I’ll be using Steamer in this example.  But you can use the system of your choice.  Just try to pick a system that uses some form of player ID system.

What is a Player ID?

Just like you and I are identified by Social Security Numbers or employee IDs (at work), most of your major fantasy or MLB websites use some form of an ID number that is unique to each player.

Using an ID is a more reliable way of identifying a player than a simple name.  Two players can have the exact same name (think Chris Young and Alex Gonzalez) which could cause big problems when ranking players.

And players can go by different name variations (Mike and Michael, Jon and Jonathan, JP and J.P., AJ and A.J.) or even change their name (Mike to Giancarlo Stanton, Fausto Carmona to Roberto Hernandez).

Here’s a quick look at some player IDs for various systems:

Player ID Source Mike Trout Clayton Kershaw Giancarlo Stanton
Baseball Reference troutmi01 kershcl01 stantmi03
Fangraphs 10155 2036 4949
MLB 545361 477132 519317
CBS 1739608 1221725 1630093
ESPN 30836 28963 30583

If you’re wondering how to determine someone’s player ID, visit their player page on a particular website.  You can usually find the player ID in that web page’s URL.  For example, here’s Mike Trout’s MLB.com player page:

MIKE_TROUT_PLAYERID

In looking at that table above, you can see here that there is not one universal numbering system.

To alleviate this problem, I maintain a “Player ID Map” (click here to download in Excel).  The Player ID Map lists out all “fantasy-relevant” players and their ID for each of the major systems (Fangraphs, Baseball Reference, Baseball Prospectus, Yahoo, ESPN, etc.).

PlayerIDImage

I stumbled across the concept of the player map from Tim Blaker at Crunchtimebaseball.com and tailored it to meet my needs.  This provided me with an excellent starting point.  Tim maintains his own version and updates his more frequently than I do.  I only maintain my own because I’ve wanted to add some new columns.

On this site I will typically work with the Baseball Reference ID.  I like those IDs more than the others because I can look at an ID and usually determine who the player is (troutmi01 is Mike Trout).  Most other sites use a straight ID number that has no inherent meaning (10155 or 545361).

If you’re familiar with Excel and using VLOOKUPs, Player IDs are the item we’ll be matching upon to start pulling information around our Excel rankings file.  If you have no idea what I just said, don’t worry.  We’ll get there soon.

STEP-BY-STEP INSTRUCTIONS

(more…)

Posted in , | Tagged , , , , , ,