xlsSports

2012 Bracket Odds

2012-03-11T16:19:00.002-07:00

Been quiet for a while, though I felt inspired tonight and updated the Bracket odds for the 2012 tournament. Some shortcuts were taken, such as only using Ken Pomeroy's statistics this year and advancing the better of the two teams for each play-in situation.

For users in Excel 2007 or later, you must ensure macros are enabled for the custom functions driving the simulation.

Bracket Odds 2012

Original Author- Erich

Original Release Date- 20120311

World Cup Simulator

2010-06-03T16:38:00.000-07:00

While I cannot recommend a particular approach to estimating probabilities in soccer, I can provide a simulation framework for the coming World Cup tournament. The schedules were compiled from the FIFA and Wikipedia sites, and the structure is there to input team ratings, churn through a hastily modified log5 approach, and generate simulations in a Monte Carlo method.

Email or comment with advice on improvements, including team ratings and a formulaic approach to assess win/lose/draw probabilities given those ratings.

World Cup Simulator
Original Author- Erich
Original Release Date- 20100603

Best of 7 Playoff Simulator

2010-04-14T16:24:00.000-07:00

Although I released an NBA Playoff simulator last year, I wanted to improve upon it by allowing for re-seeding and thus cover the NHL and NFL playoffs too. I apparently did the work on the file, but never posted it.
With the NHL playoffs starting tonight, my memory was jogged. I cleaned up the file and now share it with you.

Like the NBA Playoff simulator, this runs off conditional probabilities. Selecting the reseeding option will force the highest seeded team to play the lowest seeded survivor on each side of the bracket. This also can work for the NFL playoffs with the included instructions. Please comment or email me with any bug reports.

Best of 7 Playoffs
Original Author- Erich
Original Release Date- 20100414

Bracket Odds

2010-03-08T19:48:00.000-08:00

While this is not the most refined workbook released, this is sure to be one of the most interesting. This workbook generates advancement probabilities for each team in a 64-team tournament and is designed with basketball in mind.

In the past, my bracket picks generated by such a model were graciously hosted on David Berri's Wages of Wins blog (article links available here).

Additional credit for this workbook's development goes to its statistical forefathers, Ken Pomeroy and Jeff Sagarin, and to Adam M., a former blogger and now proud resident of the Contrarianville community for his input on conditional probabilities.

Note that macros must be enabled for the custom functions to operate properly. This workbook has calculations set to manual due to the volume & complexity. To trigger a calculation, press F9. To turn on automatic calculations, use Tools\Options and find the Calculation tab.

Bracket Odds

Original author- Erich

Revision date- 20100308

Edit: File updated with 2010 seeding and metrics, corrections on results validations
Revision date- 20100320

NBA Season Simulator

2009-08-21T12:06:00.000-07:00

While I'm at it, the NBA's schedule just came out and I've updated the season simulator for the NBA. Note there are new fields for an over/under threshold and also an update to the schedule page that will allow users to easily filter to a specific team.

Do note that while the basic version employs a pythagorean approach, I encourage users to modify the formula for any other number of approaches, such as incorporating basketball's four factors.

NBA Season Simulator
Original author- Erich
Revision date- 20090821

NFL Season Simulator

2009-08-02T08:47:00.000-07:00

Back by popular demand, this Monte Carlo season simulator looks at the 2009 NFL season.

Note that this is an early release and has a clearly visible bug. The Wild Card calculations are not perfect yet, but everything else seems to be fine. I'll update this post with the wild card fix in a few days. The baseball simulator was posted here.

NFL Season Simulator
Original author- Erich
Revision date- 20090801

Update: The spreadsheet now correctly handles wild cards and probabilities in scoring a bye week.
Revision date- 20090806

NBA Draft Lottery Odds update

2009-05-13T19:59:00.000-07:00

Given this year's ping pong ball distribution, I have updated the NBA Draft Lottery Odds worksheet (originally introduced here).

NBA Draft Lottery Odds Generator
Original Author- Erich
Original Release Date- 20080514
Revised Release - 20090513

NBA Playoffs Simulator

2009-04-15T21:25:00.000-07:00

I have taken the basis used to create the MLB playoff simulator and constructed a version for a best of seven series. This simulator calculates the conditional probabilities for each team given a 16 team, best-of-seven tournament.
Currently, this is best suited for tracking the NBA playoffs and John Hollinger runs a similar simulation here.

The NFL and NHL playoffs reseed their brackets so look for a future revision to tidy up the simulator for use in those sports.

NBA Playoffs Simulator
Original author- Erich
Revision date- 20090415

Fantasy Baseball Draft Board

2009-03-11T22:11:00.000-07:00

I'm rushing this one to the presses to make sure this is available to the standard drafting fantasy baseball players. This shares the same functionality as the football and basketball draft boards. Novices can listen to me droll on about some of the basic features here.

Much like the auction version, this is loaded with LastPlayerPicked.com's pricing for 5x5 MLB 12 team leagues.

Comment with any bugs or feature requests!

Fantasy Baseball Draft Board
Original Author- Erich
Revision date- 20090312

LastPlayerPicked.com

2009-03-11T21:57:00.000-07:00

Ever try to calculate auction dollar values? I have, and that is why I now leave it to the experts. Mays Copeland is precisely the expert to consult. His site, LastPlayerPicked.com, allows users to customize dollar projections to their league settings in an easy-to-use interface. Mays offers a variety of high quality sabermetric projection sets as a basis and combines them all in a composite projection.
LastPlayerPicked.com has been kind enough to authorize me to use their projections in xlssports fantasy baseball boards and I have revised the auction board to include their composite projections and Zips projections for a 12 team MLB 5x5 league, which seems as vanilla as it gets. Users in these types of leagues can pickup this spreadsheet and head straight for their auction.

For other leagues & settings, the spreadsheet will still work given appropriate modifications. I am still working on making these modifications as easy as possible and will work toward that direction in future releases.

Baseball Auction Board
Original Author- Erich
Revision Date- 20090312

(a hat tip to Sean Smith for introducing me to the site)

Baseball Auction Draft

2009-03-07T13:21:00.000-08:00

I have rebuilt my baseball auction draft spreadsheet from the ground up. This is still a work in progress and currently best suited for those in standard leagues or ambitious beta testers.

In the immediate term, I hope to provide revisions for ease of use and create a standard draft version. Please leave comments or send emails to report bugs or list feature requests.

Baseball Auction Board
Original author- Erich
Revision date- 20090308

Bracket Pool

2009-02-28T21:36:00.000-08:00

The NCAA tournament just doesn't come around often enough, so I have often used the attached to start an office pool on a variety of tournaments. The worksheet provides a generic 16 team bracket and allows users to set up the rules as desired. Distribution is as easy as a print out and entry tracking allows the administrator to easily grade all outstanding submissions.

The most direct uses for this bracket include the NBA Playoffs and UEFA Champions League knockout rounds.

With minor modifications and possibly creative rules, this same template can also provide brackets for NCAA Conference Tournaments, the NHL playoffs, the NFL playoffs, the MLB playoffs, and various tennis tournaments.

Bracket Pool
Original author- Erich
Revision date- 20090301

MLB Season Simulator

2009-02-23T18:21:00.000-08:00

The baseball season is right around the corner, so given the MLB schedule, a log5 framework, and user-defined team strengths, the linked tool provides a Monte Carlo analysis, giving odds on division championships, wild card winners, and season win totals.

Consider this to be the play-at-home version of Baseball Prospectus's Postseason Odds or James Holzhauer's Playoff Odds.

MLB Season Simulation
Original author- Erich
Revision date- 20090222

On the docket...

2009-02-23T17:52:00.000-08:00

With baseball season coming and a load of spreadsheets still buried on my hard drive, I'm planning to hasten up the schedule a little bit. Going forward, expect more quantity and a little less polish.

Coming soon, readers will see posts on MLB baseball, fantasy baseball, NCAA basketball, the NBA playoffs, golf, and possibly personal finance. To keep up with more frequent posts, I will sacrifice some of the face lifts given prior to posting. Expect less documentation & formatting and more reliance on comments & emails to address user questions.

Also new to this site is a blogroll for those that use xlsSports material. If you'd like to be included, send me a link and info on how you are using this material. Early adopters include sports handicappers such as vegaswatch.net and Rexford Buzzsaw.

Superbowl Squares

2009-01-08T16:02:00.000-08:00

Want to run a Superbowl Squares pool in Excel? The linked workbooks will allow you to do just that, and more. Based on data posted by Doug Drinen of pro-football-reference.com, these files will tell you the statistical worth of each square on the board, allowing odds assessments and presenting trading opportunities to the more ambitious users.

The first file is based on Doug's original guest post on Sabernomics.com, where the odds are based off only final score results. The file is macro-driven and will even generate the random numbers when the pool has been filled.

NFL Squares Pool
Original Author- Erich
Revision Date- 20090103

The second offering is a significant revision of the above, with no macros and a pivot table based report. This file is loaded with Doug's latest odds and includes quarter-by-quarter expected values, allowing for the accurate calculation of square value on a variety of payout schemes.

Superbowl Squares Pool
Original Author- Erich
Revision Date- 20090103

NBA Fantasy Basketball Draft Board

2008-10-23T21:22:00.001-07:00

I have updated my football draft board for the coming basketball season. There are live roto scoring trackers that will update throughout the draft. For help, refer to the NFL draft board screencast. Enjoy, and please keep me posted with any feature requests or problems.

NBA Fantasy Basketball Draft Board
Original Author- Erich
Revision date- 20081020

NFL Confidence Pool Strategy

2008-10-23T20:17:00.000-07:00

One of my colleagues asked me for advice on filling out his NFL confidence pool, and I offered 2 strategies:
1. Assign confidence points by the Vegas money lines, the heaviest favorites getting the top points and on down. This offers the best chance of maximizing total season points.
2. Do the above, but switch one of the top 3 picks to take the underdog. This offers the best chance of winning a weekly top score payout.

Attached is a rough proof, a Monte Carlo simulation with the following assumptions:
Player 1 employs strategy #1.
Player 2 employs strategy #2.
Player 3 employs strategy #1 but reduces top game pick to 1 point, increasing all the rest of the values (this is clearly dominated by strategy #2 and even comes out under average when strategy #2 is not included in the simulation)
Player 4 picks totally randomly (Strategy #4)
Players 5-50 use strategy #5 to pick roughly in line with the Vegas odds, though they pick upsets given Rand^2>Vegas money line. This is a generalized model of the median confidence pool picker.

Notable results:
The #1 strategy gains 3 points on average over the strategy employed by players 5-50.
The #2 strategy is over 2x as likely to win the weekly payout over the strategy employed by players 5-50.
The #1 strategy is 25% as likely to win weekly payouts compared to strategy #5 players, given there are 45 players emulating the median confidence pool model.
The #2 strategy will average 10 less points than strategy #1.
Going completely random (strategy #3) has a better chance to win the weekly total than all but strategy #2.

The money lines in the example were taken from this week's slate (14 games which happen to be without a prohibitive favorite). Even though this is full of assumptions and strategies should vary depending on competition, those that want to skip on the game theory should consider employing strategy #1 early in the season and switching to strategy #2 if they fall behind in the overall standings.

I know there are plenty of areas for improvement here. This went from question through model to post in 2 hours at an airport. Feel free to use the attached to model your own simulations and keep me posted via email or the comments with your strategies and results.

Confidence Pool Sim
Original Author- Erich
Revision Date- 20081023

Baseball Playoffs Simulator

2008-09-28T08:51:00.000-07:00

As a demonstration of the Monte Carlo method, the attached file gives users a framework to simulate the baseball playoffs either via straight statistical probability or Monte Carlo analysis. Users can apply their preferred Win percentage to each contender, whether it be regular season winning percentage, pythagorean win percentage, or otherwise. Users can also shade these win percentages to express their own opinions on adjustments from regular season performance to playoff strength.

Note that this is an early version rushed out for the coming start of the playoffs. Revisions will be made this week regarding the odds calculations given playoff results are present.
** Update ** Some revisions have been completed, but significant changes are needed to accurately calculate statistical odds given results in progress. Those changes will likely wait until the NBA playoffs and next year's MLB playoffs. For the time being, users should rely on Monte Carlo simulations for creating probabilities once the games have started.

Baseball Playoffs
Original author- Erich
Revision date- 20080928
**Macros must be enabled for full functionality. If you prefer to review the macro code before enabling, open with macros as disabled, open the Visual Basic Editor (Alt+F11) and review the workbook's worksheet and modules.**

Monte Carlo Analysis in Excel

2008-09-20T12:55:00.000-07:00

Attached is a very simple tool to help you run an analysis via the Monte Carlo method. Given a model, this tool generates random values and can build a probability distribution of results.
The attached tool brings this capability to the masses, as no coding is required. Simply follow the instructions to identify the proper Copy & Paste sections that would record & aggregate your results, then run the macro to start the analysis.
While I will primarily be posting sports-based examples in this blog, the uses of this approach can be employed in numerous fields. Please drop me an email and let me know how you have employed this tool.

Warning - Widespread use of this kind of analysis should be tempered by knowing what you cannot know.

Monte Carlo Analysis Tool
Original author- Erich
Revision date- 20080920
**Macros must be enabled for full functionality. If you prefer to review the macro code before enabling, open with macros as disabled, open the Visual Basic Editor (Alt+F11) and review the workbook's worksheet and modules.**

Open for Open Source

2008-08-09T17:17:00.000-07:00

I created xlssports to share the worksheets I've created over the past years and hopefully the success they brought me. These worksheets provide strong substitutes to the proprietary draft tools of paid fantasy sites, and they are free!
With your feedback and ideas, I'd love to see these tools become more powerful as an open source, user-improvable project. Please let me know your improvement suggestions in the comments or email, or even better, implement them and send back the resulting spreadsheet. There is plenty of Excel knowledge out there and I greatly appreciate the innovative and savvy solutions sure to be in place on your own spreadsheets.

Send worksheet submissions to xlssports+excel@gmail.com for review and discussion. Full credit will be given for any improvements to existing tools or stand alone spreadsheet solutions.

Regardless of the quantity of other author submissions, readers should still expect interesting content from me, as I've got plenty of other spreadsheets to share and improvement ideas to implement.

Video Introduction to Draft Board Use

2008-08-09T17:02:00.000-07:00

xlssports- Fantasy Draft Board 101
The linked screencast provides an overview of the fantasy draft board and is targeted for either novice excel users or users new to the operation of these worksheets. In the future, more screencasts will be provided regarding advanced functionality, though they will be better given user feedback suggesting the features to cover.

Football Auction Draft Board

2008-08-08T17:40:00.000-07:00

While more popular in fantasy baseball, auction drafts offer a more engaging way to allocate fantasy football players. The attached tool is a trimmed down version of my baseball auction board and should serve as a flexible base for your football auction needs.

The worksheet layout assumes users present one set of $ values, though those columns can be copied if users have multiple valuation sources.

Football Auction Draft Board
Original author- Erich
Revision date - 20080808

Football Over/Under Pool

2008-07-03T21:50:00.000-07:00

Want to see if you or your friends can beat Vegas? This Over/Under pool tracker is for you. The attached worksheet allows for any group to create and track an Over/Under pool for total wins. Functionality is provided to help convert an email or comment submission into a compatible format.

Suggested rules are included and can be modified to desired level of detail and worksheet can also be used to track professional and media predictions.

Football Over/Under Pool
Original author- Erich
Revision Date- 20080703

Pardon the Interruption

2008-07-03T21:47:00.000-07:00

I have been on hiatus in creating NBA Draft content for DraftExpress.com. Expect more frequent posts regarding the coming NFL football season, followed by early fall releases of basketball content.

Fantasy Draft Board Update - 20080527

2008-05-26T22:25:00.000-07:00

I have spent some time and updated the fantasy football draft board with many features, including live updates of scoring, updated formatting, and rotisserie scoring features. Why on earth would I track rotisserie scoring for football? Primarily, this feature will cater to other fantasy sports like basketball and baseball. The rotisserie scoring can be found on the Settings tab by unhiding the last set of columns (push the plus sign above column AX).

The other prominent feature added was autodraft capability. This allows for automatic drafting off a pre-ordered & sorted list. Now leagues can conduct live drafts even if all the members can't make it. The autodraft settings allow for distinct control over what positions should be selected and when, making it a more flexible tool than offered by some free fantasy providers.

Of course, the documentation on this worksheet is lagging, so expect further updates and instructions in the near future.

20080704 Edit Autodraft instructions are now included.

Fantasy Football Draft Board
Original Author- Erich
Revision Release Date- 20080527