Tuesday, December 8, 2009

Fantasy Basketball Spreadsheet for ESPN

I've just gotten into fantasy basketball again. Last I played was back in the late 90's, but I've found that it is much more interesting and challenging than Fantasy Baseball. I've been using ESPN for this, and am playing the Rotisserie style. This is where they score each of the 10 teams based on how they are doing in 8 different categories. As part of these, and to keep things competitive they provide a limit on how many games can be played by the players on your team. (820 total games.) Anyways, one of the things that bothered me was that I couldn't tell how I was doing relative to the other players. So, as you can see in the screenshot to the left, the guy in first has played more games than everyone else, and is way over the "pace" for the league. This means that once he hits his 820 game limit, he no longer accumulates points. So my question was, do I need to take drastic steps to change my team, or will I eventually catch him once he hits the 820 game limit.

Although I could estimate it, I wanted to quantify how I was doing, in terms of averages per game vs. the leader and all others in the league. To accomplish this, I created a spreadsheet that pulls in all the relevant information from my league, and then thru a series of calculations I can see my averages and projections vs. the others in my league. This is a little rough right now, but it is available on my web site completely free at:
As of now, this just supports 10 team rotisserie leagues registered with ESPN. Depending on interest, this could be enhanced, to support a h2h league, or leagues from other providers (CBS, or Yahoo for example.)
The following will tell you how to use it, and what you can do with it.
Use Cell C8 to specify your league number. You will be able to see this on the address line. For example, one of the leagues I'm in is number 130475.
Use Cell C8 to specify the team number. By selecting a team, the spreadsheet will highlight the relevant rows of the team in green. The team you select is also used in the heat map area that show you the categories that you need to improve on to move up in the standings.
Cells B12:L23 are used to illustrate the current standings of your league. So, who has how many points for each of the 8 categories in the league.
Cells M14:N23 are used to illustrate the average PR (player rank over the last 15 games) for the players on your team. This is used to tell you who is hot, and who is not. You can also see the average % ownership value for your team. This tells you what % of teams in all the ESPN leagues own a specific player. This gives you an indication of the talent level on each of the teams.
Cells P8:W9 are used to define and control the heat maps that are displayed below them. Use cells P8:W8 to define the ranges that are used for the "Season Statistics" heat map. For example, specify the value of 20 in R8 if you want to display the teams that are ahead of you by less than 20 3 pointers. The per game values in row 9 are used to control the display of the Per Game Statistics heat map. Again, if you want to see the teams who are averaging less than .2 rebounds per game more than you, put .2 in cell s9. (Details on the heat maps below.)
Cells P12:W35 are used to display heat maps for each team and category. The season statistics heat map shows you your current standings relative to everyone else. The colors are as follows:
Red - Teams that are behind you (the team specified in cell c9) in the specific category. Red is intended to indicate "beware".
Green - Teams that are ahead of you by less than the values specified in rows 8 or 9 as defined above. Green is intended to indicate short term moves that you could make to improve your standings.
Yellow - Teams that are ahead of you by more than the values specified. Yellow is intended to indicate where you are behind by more, which in turn means it will probably take a long time to move up in the standings.
The "Per Game Statistics" heat map shows your current standings but with a per game average. This is probably the more important of the two heat maps. In the leagues I am in, the two leaders are doing well in the standings, but they are primarily doing well because they have played more games than everyone else. So, I use this area to know if my per game averages are better than his, which in turn means that I will eventually catch him when I make up the game differential. For example, I may be down 10 blocked shots in the standings but he's played 20 more games than me. But if I average .6 blocks per game, and he is only average .5 blocks per game, I know I will catch him without making any major moves.
Cells B24:L35 are used to illustrate what the standings would be if they used per game averages. Again, this is a more telling result than the overall standings. In one of my leagues someone is up by over 15 total points, but the per game average standings illustrate that the guy in 4th will eventually take him once he makes up the games.
Cells B36:X335 are used to illustrate weekly standings. These standings can be either standard or per game averages depending on how you check the box in cell d36. The value here is to see your trend over time. So, you may have made some major moves in week 2, so it will be important to see if those moves are paying off, and if you are improving over time. You can also see how others are doing, as well. So, someone may have started off well, but are cooling off due to some key injuries. To simplify the creation of this sheet, I included an area for each week, of the year. Recommend you hide the row if the week has not been played yet, or you can hide the rows of weeks that you don't care about any more.
Cells B336:X361 are used to drill down into a specific category. So, Blocks, or Steals depending on what you select in the dropdown list in cell d336. This will display either the per game averages, as selected previously, or the overall totals by team and by week for the item that you selected. These weekly values are charted, and will allow you to see a trend for your or the other teams in a specific category. So, are your blocks per game improving, and if so, what is the impact on your FT%.
Again, the workbook is a little rough, and just the part of what I want to do overall. If you have enhancements requests or changes or you find bugs with the workbook, please comment on this blog, and I'll do what I can to address the issues.

1 comment:

  1. This is amazing if I could get it to work. I load it up and I can't see any data and Excel just runs a debugger saying it can't find the project/library. Any ideas?