Fantasy Basketball – Solver for Draft Optimization

In the last post, I shared my rankings and how they were derived. Great, but the question remains, what is the best team money can buy in an auction draft?

Excel and Open Office come with a Solver tool, it’s capable of linear optimization, or maximizing a value Y with a budget constraint X.

Below is a list of each player, their average cost, their estimated value, and how many of each position I want/need.

nba solver 2

Below is how I set up solver

nba solver pic

This gave a team that is much better than a lot of teams drafted. If you draft against a completely average set of opponents, consider yourself lucky. Enjoy.

Posted in fantasy basketball | 1 Comment

Fantasy Basketball Rankings

Fantasy Basketball drafts can be tough to do without some sort of plan/ranking system ahead of time.For example, your turn comes up. Which player do you choose to build your team around?:

Player A: 19 pts, 0 threes, 10 reb, 3 ast, .5 stls, 1.5 blks, 529 FG%, 823% FT


Player B: 25 pts, 6.5 reb, 4.5 ast, 1.5 stl, 1 blk, 500 FG%, 758 FT%

Arguments could be made either way. So I tried preparing an objective ranking system for Rotisserie leagues. It can be applied/altered for Head-to-Head leagues as well.

1. Projections for each player

To make player projections, one could make a weighted average of each player’s past few seasons. Basketball-monster’s already done that:

(See for a simplified breakdown of doing this manually)

It’s a lot to go through their projections, but if you copy+paste, and delete the useless players’ projections, you have a leg up over basing a player on just their past season.

The only thing is that these are projections for how well they’ll do if they all played 36 minutes. That’s not true for most players. So I estimated each player’s minutes played, based on last year’s totals and changes to their team’s depth chart.

Finally, to get the players’ projections, multiply their per-36 projections by the number of minutes you expect them to play.

2. Weighing FG% and FT%

These stats should have each player’s FGA, FGM, FTA and FTM. You could calculate the FG% and FT% that way, but more importantly is to take volume into account.

For example, Durant making 90% FT at 9 FTA/game is much more valuable than Nash’s 90% FT at 3 FTA/game.

To adjust for volume, multiply FGA * (their FG% – league FG%). Let’s call this xFG.


Brandon Jennings’ xFG = 15.1FGA * (.397 – .473) = -1.15

That is probably the worst xFG, while the best is about +1.5 from Dwight.

3. Finding z-values (standard deviations above the mean)

The basketball equivalent of step 2 in my baseball post (

With your sample of players, find the average of each of the 9 categories.

Find the standard deviation of each category as well. Excel does so with =STDEV(H3:H150)

To find a player’s contribution in one category…

(Their Pts – league average Pts)/Standard deviation of Points

Durant is 3 standard deviations above average, meaning he’s the top 1% of scorers available.

If you do that for all the categories, you can add up their contributions to get their value.

4. Positional adjustments

The value above will give a general ranking that may undervalue starting PGs. The reason being that each team only has 1 player that can rack up ASTs, while 3s, stls, etc can be obtained by any number of players.

To adjust for PG or C scarcity, find the replacement level of each player. Basketball has a lot of players who qualify for more than one position, so instead of forcing a SG/SF/PF into one position, I made 3 simplified positions.

G – Players with more than 4 asts.

C – Players with more than 8 rebounds

F – all other players

This gave a somewhat balanced number of players for each category. I found that the 30th ranked player in each category were Rashard Lewis, Daren Collison, Marcus Camby. Droppable players, but also addable.

All players were then added points so that these 3 players had values of 0.

Gs had a adjustment of 1.77, Fs of .85 (intuitive sense, as mediocre wing players to shoot 3s can be found more easily than mediocre PGs), and Cs had a 1.29 adjustment.

These may be more accurate if I had used a sample of more than 133 of the top players in recent years. I don’t think the attached sample overrates bigs, but the more players used, the better.

Finally, to account for the 2 UTIL positions, I subtracted the 104th best player’s value from the 96th best player’s value. Demar Derozen and Mehmet Okur are replacement level players after this change.

5. Calculating $ Values

If you’re doing an auction draft, ranking players from top to bottom isn’t enough.

To convert z-score value to $ value, take all the positive z-scores and divide it by the number of $ in the draft.

I prefer using to see how many positive z-score values there were. As a collection of data from the past, it’s more accurate than my sample that may have left out players that have contributed value in the past.

280 z-scores were contributed, and a 12 league team with $200 budgets gives an economy of $2400.

Each z-score is worth $2400/280 z-scores =  $8.59/z-score

Multiply that by each players value to get their final projected $ values.

Below is the file for reference. The first 3 sheets are the only used. Enjoy.

f bball

Posted in fantasy basketball | Leave a comment

Fantasy Football Rankings

I realize I never made a post on ranking Fantasy Football players. This post shows how to use a player’s past seasons to project their points in the upcoming season.

Step 1: Weighing past seasons

I took the top draft-worthy players’ stats from the past 2 seasons. I then created a very simple formula to project this season.

Projected points = .66*last year points + .33*points from 2 years ago

Step 1A: per-game adjustment

Some of the players may have missed a number of games, and I like to adjust their projected points to assume that they’ll play 16 games. Injury history should be considered, but I want to rank the players by their talent, not the amount of games they played in.

For example, if Frank Gore only played in 13 games last year, I would multiple his season total in points by (16/13) to get an estimate of how he WOULD have done without the injury. These rankings may be biased toward injury-prone players that can hurt a team, so depth shouldn’t be ignored.

Step 2: Define the replacement level player

In a 10-team league, each team has 3 starting WRs and probably 2 benched WRs. Assuming the top 50 WRs are owned, that means that the 51st best WR is going to be available on Free Agency, and doesn’t need to be drafted. I took an average how many points the 51st best WR scored in each of the past 3 years to get a general idea of the quality of player that would be available as a replacement at about anytime in the season. Doing this for each position gives you an idea of how many points you can get from a non-drafted player.

Step 3: Subtract each players projected points by their positional replacement level

In my rankings, Ryan Fitzpatrick was projected to be worth 242 points, and the replacement level QB was worth 235 points. Fitzpatrick was then worth 7 points Value over Replacement Player, or he had a VORP of 7.

Step 4 (for auction leagues): Creating a $ value from VORP

To give a player a $ value, you have to find his value as a factor of VORP

In my league, there are 10 teams, each with $200. 10 x $200 = $2000 in the fantasy economy.

So you just need to figure out the conversion rate from 1 VORP to $1.

In all the draftable players I researched, their VORP summed to a combined 4500.

VORP * x = $

2000/4500 = .44 $/VORP

So a player with a VORP of 1 is worth about 44cents.


Step 5 (auction leagues): Find undervalued players

Yahoo posts the average cost of each player.

Value – Average Cost = Surplus Value.

This is how I identified and targeted players that were going for cheaper than they should. Ex. Antonio Gates

Step 6: Run Solver

In the spreadsheet, I set up Excel Solver as I did in a past baseball post. The steps to this are a even more dry and technical that previous steps, so they can be elaborated on if anyone cares to hear it.

Basically Excel Solver can maximize value with a constraint of $185 to spend on 1 QB, 2 RB, 3 WR, 1 TE (I left $15 over to pay for a bench)

It spit out a team such as

Vick, A Foster, BenJarvis, Andre Johnson, Desean Jackson, Stevie Johnson, Antonio Gates.

I like that team.

These values can be valuable tools for drafting and trading. The biggest mistake you could make however would be to follow the projected points without considering the context of the numbers. For example, teams that have fallen a great ways from the previous years, like the Colts, will have players overvalued by a system that doesn’t take into account Peyton’s season ending injury. Go with the numbers AND your gut, never just one.

I’ve attached a spreadsheet showing some of the projected values I calculated using this method. Enjoy!

final fantasy football



Posted in fantasy basketball | 3 Comments

Building Player Valuations II: positional adjustments, $ values and bargains

Step 4: Adjusting for position

calculating $

The spreadsheet is a bit messy. How did we get those values? I’ll walk alongside it.

The values up to step 3 in my last post should show that C, SS are underperformers compared to power spots like OF and 1B. But because good C and SS are rare, you have to give them a bump.

To create a positional adjustment, you take the replacement level player at each position, and make him equal to 0.

For example, say Aubrey Huff is expected to produce the 13th best raw value at the 1B position. His raw value is -.04, so .04 becomes the replacement-level raw value, and you add all 1B’ raw values by .04. Huff’s new adjusted value is 0, and all valuable 1B above him have some adjusted value. For OF, the 37th best raw value player is chosen as the baseline to be equal to 0.

You do this for each position, and should see that C and SS gives the greatest positional adjustments.

However, don’t some of those players seen as below replacement level still look rosterable? You need utility players after all. So you sort the raw values by the top 120 hitters. Each of them should start on a team in a league with 10 positions and 12 teams. You then find the player with the lowest position-adjusted value.

For pitching, RP should be given a greater adjustment. I left out stellar set-up pitchers in my sample, so out of 35 pitchers that may see some saves, you have to be careful not to set the replacement level too low or high. This is subjective, I set it at 2, a little below Brad Lidge. You can do otherwise if you’d like.

For SP, I decided not to use a baseline. The first SP that would create a negative impact should not be rostered (unless you expect him to exceed the projections of course). This leaves veterans like Carl Pavano in free agency as can often be the case.

Step 6:  calculating $ value (my favorite step)

Great, so I came up with a sheet with all the values. Yahoo leagues and hardcore leagues such as Tout Wars consistantly have about 69% of budget allocated to hitters and 31% to pitchers. This is because pitchers are much more volatile. You can sometimes draft poor pitchers and pick up risky pitchers (Webb, Bedard, Peavy, Strasburg, Dan Hudson) that come out of nowhere. You can’t expect to do the same for hitters. 30% of the end-of-year value is provided by pitchers that went undrafted. So what I do is set a rule of a roughly 70/30 split between hitters and pitchers.

Once you have values to compare all the players to one another, you can create a dollar value. Not only for standard position-adjusted values (from step 4) but for Per AB values as well (previous post).

The league I’m playing in has 12 teams, 23 players per team, and $230 per team to use for the auction draft. So 12 * 260, there are $3120 in the draft economy.

I want to maintain that 70/30 hitter/pitcher split, so there are $2184 in the hitter economy, and $936 in the pitcher economy.

Sum the total values of all the players that have a total value greater than 0. Let’s say there are about 475 raw value units for all hitter with a raw value above 0. In that case, 2184/475 = 4.6. So to find any player’s raw $ value, multiple his raw value * 4.6.

$ multiplier = total $ / total value units

Step 8: Finding the undervalued assets

Great so you should have a sheet telling you CC is worth about $25, Ethier is worth about $20. Now what? You still don’t want to blow your wad on the earliest dudes in the draft to fill your positional needs. You want to maximize your value.

So I found the Average Auction Price for each player above a $1 rating.

By subtracting a players ($ value – aap $ value) you see which players give the greatest value return on the price. Know it going into the draft. Things like the following I’ve observed from my sheet: there are cheap values at 2B and OF, you should get a top C, there are only a few 1B worth the money, a number of quality SP can by had for only $13 or so,  there is only 1 true value at 3B and I will bid like a madman to get him, and so on and so forth.

Posted in fantasy baseball | Leave a comment

Building Fantasy Player Valuations Part I: Raw values

Hi. Have you played fantasy baseball? How many hours have you poured into it only to come up short? Wanna put in a few more hours to create a system instead of relying on Yahoo rankings?

This is a thought I had, and these are my ideas on possible steps to gain an advantage in a fantasy baseball league. To be more specific, I’ll discuss how to create $ values to use for an auction league fantasy draft. Credit where it’s due: fangraphstom tango’s site.

Step 1: using a projection system (protip: use the pros’)

First off, we need projections for how all the players in your draft are going to do next season. Sure you could do them by gut, or with some sort of weighting… but people much smarter than myself have already done this. They’ve used massive amounts of data and math and they share their results with the public. There’s CHONE, Cairo, ZiPS, Marcel, Bill James, Fantistics, PECOTA, RotoChamp and a few others. Which one to choose though? They’re all respected, and have varying levels of success as projections. Why not choose all of them?

So instead of taking 1 projection, let’s take an average of 9 different ones. This lessens the bias some projections may have against some types of players. For example, CHONE often minimizes error pretty well but has it setbacks. Since it draws only upon major-league results, it may undervalue young players in terms of both performance and playing time.

Unfortunately I can’t find the google document of this data,  it’s been taken down. Dang. But here is what I ended up with after weeding out the players unlikely to be drafted in my league. Not only does it have projections but it also shows how I calculate the values in this post.

Aggregate projections

Step 2: calculating z-scores

The goal is to find out a players contribution in each statistic by seeing how many standard deviations they are from the average. We’ll discuss the hitters, then the pitchers.

With a spreadsheet (like the above) it’s not hard to calculate the average and standard deviation of each stat for all hitters. First you need to know the average in each of the 5 categories. Then the standard deviation.

Excel will calculate the standard deviation and average of SB in the i4-i152 range if we type



Let’s stick that in a safe place, the header.

Moments In History, Oct. 30 2007: Jacoby Ellsbury earned everyone in the country a free taco

Say the projection has Jacoby Ellsbury stealing 55 bases, and the average player steals 12. Out of all players, some steal very little and some steal a lot, so there’s a wide distribution of SB. In other words this data set has a large variance in SB. But all we need is the standard deviation of SB, the variance square rooted.

So the standard deviation is about 12. With a large enough sample size, only 1% of any SB totals should be 3 or more standard deviations from the mean. Ellsbury is 3.5 SDs above average in SB, showing how he can win you that category. This contribution of 3.5 is called a z-score.

We then do the same technique for 7 of the 10 stats.


Pujols RBI z-score = (Pujols RBI-avg RBI)/stdev( HR)

= (114-76)/(18)  = 2.4


If you play around with the spreadsheet and are comfortable with Excel you should see how to quickly calculate each of the 150 hitter’s 5 z-scores. You might be wondering what xH is, that’s covered in the next step.

Step 3: calculating rate-based z-scores

We still have to adjust rate stats such as WHIP, ERA and AVG. This way Roy Halladay’s 2.8 ERA over 230 innings has a greater impact than a relief pitcher’s 2.8 ERA. To find his true value, you need to know how many ER he gives up, and how many ER the league average pitcher would give up if they pitcher the same number of innings as Roy. So to find Roy’s expected ER over average

= (Roy’s IP) (avg ERA – Roy’s ERA)/9

= 230*((3.68-2.78)/9) = 23 xER (ER better than the average pitcher)

It’s a similar calculations for the other statistics

xH = Pujols AB * ( Pujols avg – all hitters’ avg)

xWHIP = Roy’s IP * (Roy’s WHIP – avg WHIP)

Mentioning Roy Halladay gave me an excuse to relive this

After that you should have 5 z-scores for each hitter, and 5 z-score for each pitcher. Simply add a hitters 5 z-scores together to get his value, the same for goes for pitchers. I will refer to each stat as the player’s raw value. You can rank players by their position, or predict which players will have the highest o-rank next year. Woo!

Now, it’s important to note that the weights for each z-score don’t have to be equal. Some would prefer putting less of an emphasis on, say, AVG. For example, it’s a lot easier to predict a player’s HR range than their AVG range. Some players like Adam Dunn may alternate between hitting .230 and .270. If you’re playing in a head-to-head league, this is especially true, as AVG is much less predictable over 1 week. Even a dominant AVG team can lose in AVG in a short series, that’s baseball. AVG, wins and ERA are the most vulnerable to bad luck. For this reason, I might recommend multiplying those players’ z-scores in ERA, W, AVG by .8 or so. This lessens the weight that those stats have towards player’s overall value. Pitchers are able to control WHIP, whereas ERA/W/SV can vary depending on good/bad luck. It would be interesting to know the optimal weights to place, but that’s a question for another time.

Next time: turning value into $, finding undervalued players

Posted in fantasy baseball | 1 Comment

Fantasy Baseball Projections – Dollar Values

In the past few posts I discussed how to calculate dollar values for players. It’s a great resource for your draft. Readers may not want to go through all that.  So below I attached a spreadsheet listing:

A. name and position

B. upside value (the value as if all hitters got 600 AB, all SP got 200 IP, all RP got 65 IP)

C. The average price for each player in Yahoo auction drafts

D. Value above market price in both those projections

Below is the goods, it passed the laugh test for me so I’m gonna use it. Questions and comments appreciated:

baseball values


Posted in fantasy baseball | Leave a comment

Adjusting for upside: per-AB expectations

In my first post I used an aggregate of 9 projection systems to value fantasy baseball players for 2011. I noticed that some of the players with upside (breakout or bounceback candidates) were being rated lower than more predictable, consistent players. A good deal of this is because breakout candidates are forecasted to have 400 or so at-bats vs some with an expected 600 at-bats. So these candidates could be projected to be doing better per at-bat, in which case I would think they would be more valuable come head-to-head playoff time. They could also exceed their utility or injury expectations. All else equal, drafting a player that has a decent chance at a huge season (Manny) is personally more worthwhile to me than a more predictable low-upside player (Howie Kendrick).

So I took (600/player AB) for each draftable hitter. This gave me a multiplier that I could multiply their stats by, and I left their AVG unchanged.

After that I repeated the process from my first post:

-finding each players (stat-avg stat)/stdev of stat)

-doing that for all 5 stats

-summing those 5 values to get a raw value

-choosing a replacement level for each position

-subtracting the raw value – positional replacement level

-subtracting that adj. value – UTIL replacement level (25th best player below the replacement level players)

upside calculation

Manny, Chris Carter, Dominic Brown, Carlos Beltran, the usual bounceback and breakout candidates were the biggest benefactors of this new upside-adjusted value. I like to use this projection as a proxy for upside.

Posted in fantasy basketball | Leave a comment