Tag: Draft

How to Automatically Shade Drafted Players In Excel

I know, I know. It’s mid-March, college basketball is taking over your time, and you’re in the final push of draft preparation for your baseball leagues. The last thing you want to do right now is start tinkering with your draft spreadsheet and screw something up.

Then here I come with another monster Excel post…

NOT THIS TIME! I swear. This is an easy one and you might find it helpful.

In this post I’ll show you how to use Excel’s conditional formatting to gray out players that have been chosen during your draft.

Excel’s Conditional Formatting

Conditional Formatting is a way to adjust the format of individual (or many) cells based upon the information within that cell. If you were using this in a business spreadsheet you might want really high sales months to show up in bolded font or below average sales people to show up shaded in red.

For fantasy baseball you might want all players projected to hit 30 HR to have their HR projection in bold red font. Who knows.

The point is you can tell Excel “what to format” and “how to format it”.

Here’s an example of what I’ll show you in this specific post:EXAMPLE_DRAFTED_PLAYERS

Assumptions

I’m using Excel 2013 in the screenshots below. I think this will work on Excel 2007 or 2010 without any issues. I’m also assuming you’re starting with a spreadsheet you created following either this standings gain points series or this points league series.

Step-by-Step Instructions

Continue reading “How to Automatically Shade Drafted Players In Excel”

How to Add Average Draft Position Info to Your Spreadsheet

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

Continue reading “How to Add Average Draft Position Info to Your Spreadsheet”

Are Hitter SGPs Directly Comparable to Pitcher SGPs?

I recently got an e-mail from a reader of the site (thanks Brad!), asking an interesting question. He was working through creating his own standings gain points rankings and tailoring them for his custom league.

How do I bring Losses into the SGP calculation? If I do make losses a negative, how would I make the pitcher and hitter SGP numbers apples-to-apples?

You might realize that he actually has the correct answer, but it didn’t make sense at first because he wanted to keep hitter and pitcher SGP comparable.

Losses Are an Inverse Category

Losses, like ERA and WHIP, is a category where the goal is to have the lowest total. So the answer is to make the SGP factor a negative number.  If you think about how we calculate SGP factors, we’re really trying to find the slope, or line of best fit, to our historical standings data.

Stolen_Bases_SGP
Example SGP slope calculation.

Traditional categories like Ws and Ks have a line with a positive slope (slanting up and to the right on your high school math class x- and y-axis).  The higher the number of Ws on the x-axis, the more points you get in the standings on the y-axis.

The slope of a graph plotting out Losses would be downward sloping.  The more Ls on the x-axis, the lower the standings points reflected on the y-axis.

So Brad had the right answer, but he was afraid that adding in another inverse pitching category was going to destroy his ability to compare hitter and pitcher SGPs.

The Real Reason We’re Here

I realize you didn’t come here for me to take you back to basic math class.  The real question is, can you take a pitcher who is projected for 4 SGP and say he’s a better draft selection than a hitter projected for 3.8 SGP? Continue reading “Are Hitter SGPs Directly Comparable to Pitcher SGPs?”

Is Multiple Position Eligibility Worth Something?

Think of players like Ben Zobrist, Martin Prado, and Matt Carpenter.

In recent seasons, players like these have been eligible to play multiple positions. The big question is, “All else equal, should you pay more for a multi-eligible player than a player only eligible at one position?”.

I think there’s a clear answer to this question. And it might surprise you.

The Argument That They Are Not Worth More

Would you pay $2 more for Player A because he’s also second base eligible?

Player A (2B / SS): .272, 10 HR, 83 R, 52 RBI, 10 SB

Player B (SS):  .275, 11 HR, 78 R, 55 RBI, 8 SB

Some very intelligent fantasy baseball minds will tell you that a player with multi-position eligibility is not worth more. For instance:

However, since playing more than one position doesn’t mean the player will produce any extra stats, he doesn’t have any greater an actual dollar value than someone who plays only one position.

~ Larry Schechter, Winning Fantasy Baseball

If you haven’t read Schechter’s book, you need to.  It is loaded with very clear and well-reasoned thoughts and strategies like this one.

When you read the book, it becomes clear that Larry has a very bottom line approach.  If a possible decision ends up adding dollar value to your team, you choose that option.  If there is no measurable benefit in dollar value, you don’t.

I love the approach.  That’s why I think everyone should create their own rankings. Attaching dollar values to players makes all decisions systematically easier to make. You no longer have to agonize over decisions like, “Should I drop player X and add player Y from the waiver wire?”.  It’s a lot easier to simply look at each player’s projected dollar value for the rest of the season and decide.

With that said, I think it’s a little short-sighted to only look at the dollar value of a player at a given point in time.

Let’s Play Cards

My wife says my analogies are horrible, but I’m going to try a few on you anyways. Before we get into specific scenarios involving fantasy baseball, let’s first consider a couple of card game examples.

Blackjack

We can probably agree that the ace is the most powerful card in blackjack. A big reason for this is that it’s a gateway to reaching the total of 21.  Without an ace you need a combination of two cards to get to 11 or 21 (2 & 9, 3 & 8, etc.). But the ace gets you there in one card.

Another huge power of the ace is its ability to act as two different cards, a 1 or an 11. It’s an insurance policy. You can behave as if it’s an 11 and quickly change it to a 1 if you bust. It lets you take risks you wouldn’t otherwise take. Keep this in mind.

I’ll end the blackjack example with a question. Assume an 8 in blackjack could be used as an 8 or a 3. You get to decide. Would that make the card more valuable to you than a card that could only be used as an 8?

Poker

I’m not a poker guru but I played my share of Texas Hold’em games in the early 2000’s just like everyone else.

In my mind, a huge decision-making factor in poker is the number of helpful cards (or “outs”) left in the deck.

For example, if you have a pair of 9s, we know there are only two helpful cards remaining (the other two 9s) to help you make three of a kind.

Or maybe you have an open-ended straight like a 4, 5, 6, and 7.  In this case there are four 3s and four 8s that would finish of your straight, for a total of eight “outs”.

Your Hand Your Goal Outs
One Pair Three of a Kind 2
Two Pair Full House 4
Inside Straight Straight 4
Open-ended Straight Straight 8
Four Flush Cards Flush 9
Straight / Flush Draw Straight / Flush 15

Continue reading “Is Multiple Position Eligibility Worth Something?”

How To Calculate Projected Standings For Your Draft

So you’ve got a great set of projections, rankings, and dollar values.

This is great for draft preparation. But how do you take this information and use it during the draft?  Is there a way to monitor the progress of the draft and see if you’re lacking in power compared to the others in the league?  Do you need speed?  Are you lacking in strikeouts?

You don’t need to buy draft software or a special draft spreadsheet.  You can easily add a few more things to your Excel files and have a very powerful draft tool that can help you make these assessments.

I wrote about how to track drafted players in a spreadsheet last January.  If you haven’t read that yet, please do.  The instructions that follow pick up where that post leaves off.

Here’s how to calculate projected standings during your draft.

Prerequisites

I’ll be using Excel 2013 to create the projected standings.  I think you’ll be able to follow a very similar process in Excel 2010 and Excel 2007.

Teams_DraftedAs I mentioned, this post assumes you have already added the named range and data validation drop down listing to select the team that has drafted a player.

This is how you will be tracking the draft selections during your draft.  As each player is taken, you’ll locate them on your rankings/projections lists and select the team that drafted them.

I realize it’s a little early to be thinking about draft spreadsheets, but it’s top of mind for me now because I am participating in a mock draft that was coordinated by Bryan Curley of BaseballProf.com.  I wanted to see how the draft is going and how teams stack up after the first few rounds.  Not to mention you can set this up in your spreadsheet now and have it read to roll when we finally get closer to the season.

You can see the list of those participating in the mock draft in image above.  As selections are being made in the draft, I’m marking them off in drop down I created in the “TAKEN” column.TAKEN

 

You may want to make several “fake” selections in your Excel file, just to have some data to work with.  You will want to test that your projected standings are working prior to the draft…  That’s the last thing you need to be monkeying on draft night.

Excel Functions and Features We’ll Be Using

There are a variety of ways to do this, but I’ll show you how to use a pivot table in this example.  If you’ve never created a pivot table before, don’t worry.  They’re a lot easier to work with than most think.

The pivot table will help us to quickly accumulate every team’s offensive stats (R, RBI, HR, SB, BA).  Once we have the statistics accumulated in one nice table, we’ll then use the RANK formula to calculate the standings.

Pivot Tables

Pivot tables are very useful for taking a lot of lines of data (like we have on our “Hitter Ranks” tab) and combining or summarizing that data into more easily or digestible parts.

For example, your listing of all hitters and their projections is probably hundreds of rows of information.  If you were to select 14 hitters for each of the 12-teams in your league, that would be 168 hitters.  Sorting all of those players into teams and then calculating the totals for five different categories for each team might seem like a daunting task.

The good news is that creating a pivot table can be done in only a few clicks of the mouse and within minutes you can have a table that looks just like this:

Pivot_Table_Projected_Standings

Rank (or RANK.EQ) Formula

These two formulas are essentially the same; however, RANK was discontinued in Excel 2010 and was replaced by RANK.EQ.

If you’re using Excel 2010 or later, either one should work. But if you are using Excel 2007 or earlier, you must use RANK (RANK.EQ didn’t exist then).

These formulas will interpret a list of numbers and return the ranking of a specified item in the list.  We can use this to analyze the entire list of player SGPs and give us a ranking for each player (e.g. Mike Trout is #1).

The formulas require three inputs:

RANK(Number, Ref, Order)

RANK.EQ(Number, Ref, Order)

  1. Number – This is the specific number you want ranked. If your goal is to figure out where your team ranks in total home runs, you would select the individual cell containing your team’s projected home runs.
  2. Ref – This is the range of data, or the list of data, to calculate the ranking from. Continuing with the total home runs example, you would select the entire listing (or range) of projected home runs for all teams in the league.
  3. Order – Technically this is not a required part of the formula, but using it can make our lives a little easier. If you leave this part out of the formula, Excel would return a “1” for the team with the most home runs and a “12” for the team with the least home runs. You can see we don’t really want to know our “Rank”. We’re looking for “Rotisserie Points”. We would like the top team in home runs to show a “12”. The “Order” parameter in the RANK function is looking for a zero or a one. If you leave it blank or put in a zero, the RANK function gives a “descending” ranking. This is the typical ranking where “#1” is the best or highest possible ranking. We are looking for an “ascending” ranking, where a “12” is the best result. To do this, just put a one in for this piece of the function.

Step-by-Step Instructions

Continue reading “How To Calculate Projected Standings For Your Draft”

How Does a Player’s Age Affect Draft Return?

A few weeks back I took a closer look and analyzed the last five years of preseason Steamer projections (what I’m using as my best approximation of the “draft value” of each player heading into the season) and compared them to the actual end of season dollar values earned by those same players.

One of the glaring omissions in that article was some kind of analysis by age.  Are there certain age groups that might be undervalued?  Better yet, are there certain age groups of hitters we can take advantage of and a separate age group of pitchers we can jump on?

If we are trying to decide between a $20 pitcher who’s 23 years old or a $20 pitcher who’s 33 years old, who should we choose?

Quick Reminders

I’d highly recommend reading the first article that started me down this road.  There’s a greater explanation of the approach used.  But for a quick reminder… the dollar values are based on a standard 12-team league using traditional rosters (2 catchers, 14 hitters, 9 pitchers) and the standings gain points approach.

I also calculate return “including losses” and “without losses”.  The best way to think about this is with a pitcher suffering a terrible injury in the first month of the season.  Being injured that early, regardless of how good the pitcher is, will result in negative earnings.  But the “benefit” of an injured pitcher is that you can immediately drop them and not suffer any of those negative earnings.

The flip side of that coin is with a struggling pitcher.  You may decide to stick with a struggling pitcher for weeks or months, hoping for them to turn it around.  In this scenario you are saddled with many of the negative earnings for that player.  So the actual “return” on players lies somewhere between the “including losses” and “without losses” results.

Draft Results By Player Age

Take a look at the “Including Losses” and “Without Losses” charts below.  Does anything jump out at you?

RETURN_BY_AGE_WITH_LOSSES Continue reading “How Does a Player’s Age Affect Draft Return?”

Taking Your Draft Spreadsheet To The Next Level

In this post I’ll show you how you can take a boring black and white spreadsheet and convert it into a visually meaningful piece of art in less than 10 minutes.

An important factor in being a skilled drafter is the ability to remain cool and calm in pressure situations. There may also be opportunities to intimidate some of your opponents during the draft, to give them the impression that you are highly prepared and ultimately familiar with the player pool and your own drafting strategy.

Nothing can accomplish these things better than a fancy color-coded spreadsheet.

There may be an element of hyperbole in that statement. But we have all been there. It’s late in the draft, you’ve already wasted 60 of your 90 second allotted time and you’ve just made the decision that you need to draft a second basemen with some power but that won’t kill you in batting average. Or you need a pitcher with good strikeout totals that won’t kill you in WHIP. Or some obscure shortstop is thrown out for bid in the auction and you can’t remember if he will help you with reaching your target goal in stolen bases.

Enter Conditional Formatting in Excel

Conditional Formatting in Excel allows you to give visual meaning to a mass of boring looking numbers that can be hard to interpret all at once.  Let’s face it, while looking at the spreadsheet example in the image below it is not easy to quickly make sense of the numbers.

Conditional_Formatting_Example

But if I take this same area and apply a few of the different conditional formatting rules to it, you can much more quickly make sense of what you are seeing:

Conditional_Formatting_Example2

In the “RSGP” column I have applied a rule in which you select two colors.  One color represents the highest value in the data selected and the other color represents the lowest value in the data selected.  I chose a standard green color for the highest value and white for the lowest.  Excel then automatically provides shades between white and green to each player’s “RSGP” based upon its value relative to all the other players in the list.  Whether you agree with me or not, I’m down on Billy Hamilton’s ability to stick at lead off for the entire year, and his run projection is the lowest of anyone captured in the screenshot.  So he’s the closest to white.  While Votto has the highest “RSGP” in this small group of players, so he’s shaded closest to green.

The “HRSGP” column has a rule using three colors.  Green is still used for the values that are greatest in this column.  Red is used for the lowest values,  And Yellow is used for values near the 50th percentile of the group.  Again, Excel automatically shades ever player’s value according to this set of instructions.  You simply select the three colors and Excel does the rest.  Here you can see Billy Hamilton is projected to be very low in “HRSGP”, while Jay Bruce and Chris Davis are much higher.  These two illustrate an important concept too.  I have Bruce projected to hit fewer home runs than Chris Davis, but Bruce comes out with a higher “HRSGP” based upon the positional replacement level adjustment under the SGP method.

The “RBISGP” is using data bars to depict the value of each player’s RBI compared to the group.  Not to keep piling on Billy Hamilton’s lack of hitting prowess, but you see he has a red bar pointing to the left, in his “RBISGP” column.  This indicates the magnitude to which his RBI projections are below 0.00 SGP.  Jay Bruce, right above him, has a very strong RBI projection and a blue shaded bar depicts this.

Conditional_Formatting_Example3

This Seems Way Too Complicated

It’s really not.  You can add this to your draft spreadsheet in under 10 minutes.

Things To Consider Before We Start

In the step-by-step instructions below, I apply conditional formatting to a player’s standings gain point (SGP) figure for each individual category (e.g. HR, BA, R, etc).  I choose to do this because after following the “Create Your Own Fantasy Baseball Rankings” approach, the final SGP figure for any player includes an adjustment for replacement level at the position.  This means that 15 HR from a 2B are valued more than 15 HR from a 1B.

The other benefit of applying the formatting to the SGP columns is that rate states like BA, ERA, and WHIP will be weighted appropriately.  An OF that might hit .300 but only get 200 PAs isn’t really helpful to our team BA.

There is an argument to be made that you would want to apply conditional formatting to a player’s raw statistics instead of the SGP figure.  After all, if you’re trying to draft players in order to reach a targeted goal (like 250 HR), you might be more interested in a 1B projected to hit 18 than the C projected for 17, even though those 17 from the C are more “valuable”.

Anyways, that’s beyond the scope of this post.  I’m just here to add fancy colors to your spreadsheet right now.  Just know that you can do this using a player’s raw projections even if you haven’t worked through the “Create Your Own Fantasy Baseball Rankings” series.

Step-By-Step Instructions

Continue reading “Taking Your Draft Spreadsheet To The Next Level”

How To Track Drafted Players In A Spreadsheet

I’ll paraphrase a reader question I recently received:

Hey, you with your big fancy spreadsheets.  I need a way to easily hide the players that have already been drafted so I don’t waste time digging around in a bunch of players that are no longer available.  Have any tips?

– Bill

Great question.  Let me show you how I do this.

But First…

We have three key Excel concepts or features you should understand.  If you’re already familiar with these, just skip down to the step-by-step instructions below.

  1. Named Ranges
  2. Data Validation
  3. Filtering

Named Ranges

Excel allows you to create names for groups or blocks of cells.  Once established you can use this name in formulas instead of having to fully spell out the entire range of data again.  I find it a lot easier to deal with a name like “TeamNames” than it is to explicitly specify the area of a range like “Settings!$A$1:$A$10”.  Named ranges are a lot easier to remember and a lot less likely to result in errors.

Data Validation

Data validation ensures that information entered in a cell or calculated by the Excel fits specified criteria.    You could validate that information entered in the cell is a date or is larger than 0, for example.

In the example below we will create a drop down menu that lists each team in your fantasy league and validates that the selected value is spelled correctly and corresponds to a team in your league.

Filtering

Filtering is a function in Excel that allows you to hide entire rows of data that don’t meet certain requirements.  In this example we will filter the list of players to hide players that have been given a team (they have been drafted).

Step-By-Step Instructions

The following instructions will take you through the process of adding a column to track which players have been drafted and which team in your league selected each player.  We will create a drop down menu that lists all teams in your league and use this to document who was drafted and by what team.

These instructions assume you have some kind of a starting spreadsheet containing projections and rankings already.  If you don’t have such a starting point, skip to the end of this post for information on how to get here. Continue reading “How To Track Drafted Players In A Spreadsheet”