Monday, December 05, 2016

2016-17 College Bowl Pool Sheets

UPDATE 12-06-2016: A few of you have reported that there's a bug in the way you set picks for the National Championship game. I hadn't moved the data validation from last year's semi-final games to this year's. That issue is now fixed in both files. I apologize for the sloppiness.

UPDATE 12-28-2016: I've fixed a couple more issues in the Summary file. The first dealt with the way in which the was with the Bowl Rankings and Average Wagers are calculated in the GAME CHARTS sheet. I had missed one of the games, and it cause the rest of them to be referencing the wrong data. The second was on the CRITICAL GAMES tab. I had the National Championship game referencing the wrong possible teams, since the playoff games change each year. This same issue was impacting how the ODDS OF WINNING scenarios were being ran in the hidden scenario data tab. Basically, it was only picking from Nebraska, Tennessee, South Alabama, and Air Force as the possible teams in the championship game instead of from Washington or Alabama in the Peach Bowl or Ohio State or Clemson in the Fiesta Bowl. That is now fixed, as well. Thank you, Matt, for helping identify some of these issues.


Ok, everybody! This season's college football bowl pool files are now ready! Many of you have been patiently (and not so patiently) nudging me to get these ready. I'm like, "Hey, can't we at least let the Army-Navy game happen first, so we know what their regular season records are, at least?" and you all are saying, "Nope." Fine. Here you go.

Some noteworthy items:

  • Since we don't know Army and Navy's final records, I've left those two cells in both the Summary and Entry sheets unlocked, so that they can be easily edited to specify their respective final records. Note that if you make picks, copy-paste picks in from Entry Forms, etc., before making that edit, your picks will no longer match with the team names/records and scoring will not work for those games.
  • The bowl line-up is exactly the same as last year, with the exception that the GoDaddy Bowl has become the Dollar General Bowl.
  • There aren't currently betting lines set on all of the games, so I just added the spreads for those already available -- check Vegas Insider or similar sites. I did, though, use the first team as the "favorite" for all other games, so that I could do some testing with the "Odds of Winning" functionality, which doesn't work unless favorites are set. This is interesting to know, so that if you find a game has no favorite, at least pick one of the teams and either leave the Spread blank or set it to zero.
  • My wife, son, and I haven't had a chance to make our picks yet, so for those of you who like to use our picks as benchmarks of sorts, you'll have to wait until I update that later.
  • I did choose to keep the FCS-level Celebration Bowl as part of the line-up. Feel free to have your participants make picks for that game or not, as you like. I think that's likely to be a great game, btw.
Without further ado...the files:
For those unfamiliar with the two-file system, the idea is that pool managers can distribute the simple, single person Entry Form version to those in their pool to make their picks and assign confidence points. The participants them email back that file, and the manager can simply copy, paste special... values those picks and points into the Summary file, which is where they manage everything.

As always, the file is protected, but not with a password, so feel free to unprotect the workbook and then unhide some of the sheets running this stuff behind the scenes to see how it works. And if you add some cool hacks/additions, pass them along, so we can all enjoy them.

Final, as also always happens each year, several folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheets. This, like the NFL pick 'em sheet I do, I do for free as a labor of love. That said, if you are so compelled, feel free to PayPal me whatever you like:

$5.00     $10.00     $25.00     $50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Enjoy, everyone!

Wednesday, August 10, 2016

2016-17 NFL Pick 'Em Pool

Folks, I've completed and release this season's NFL Pick 'Em pool. Like past years, many of you have diligently been pestering me for weeks now. Keep doing that! It's paying off. I'm actually getting this out well before the season, unlike last year. Now, I didn't have the ambitious list of new features this year that I did last year, but I did have to contend with the Rams moving back to LA, which actually ended up being more involved to change than I'd like to admit. Also, at the request of several pool admins, I did create a companion "Picks" sheet to distribute to pool participants, so they can simply email back the file for the admins to copy-paste into the master sheet. Just remember to always copy, paste special... values when moving their picks into the master sheet, so as to not jack up formatting or other stuff.

UPDATE: I guess since I was able to get this year's version out so early compared to years past, I've been compelled to throw in one last feature enhancement. Many of you through the years have been asking for an automatic game results/score updater, using data from the web. I've got that feature now implemented, pulling scores from my score information website of choice -- footballdb.com. Please let me know if you encounter any issues using this new feature. That is all.

Without further ado, here are the files:

Also as happens each year, several folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheet. This, like the college bowl pool sheet I do, I do for free. That said, if you are so compelled, feel free to PayPal me whatever you like:

$5.00     $10.00     $25.00     $50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Anyways...please enjoy this year's NFL Pick 'Em. If you encounter a bug or need help with using the sheet, let me know.

Later,

Excel_Geek

Sunday, March 13, 2016

2016 March Madness Files Are Ready!

UPDATE: I've updated the Participant file ("Individual Picks") after several of you reported a bug that prevented you from using the Pool Setup & Results Summary functionality due to a "Run Time Error 1004" issue dealing with a cell being protected. I've addressed that issue, which was a problem in the Participant sheet. That's the good news. It's fixed. The not great news is that if you're a pool admin, you'll need to get all of your pool participants' picks into this updated sheet for that function to work. I'm truly sorry about that.

Here we go, everyone! The much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet) is out!

The system works the same as in previous years. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to automatically pull their picks into the master.


Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Like I first did in 2012, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it -- I've automated the process.

What you will do is the following:
  1. Download the "master" file by clicking on the link above.
  2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
  3. Copy the "lock code" the file presents you.
  4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
  5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
  6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little avatar on it. It will display (among other things) the "key code" you'll need to activate the file.
  7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!
Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.


Lock Code


Best of luck, everyone!

Excel_Geek

Monday, December 28, 2015

2015-16 Bowl Pool Spreadsheet with Scenario Analysis

I promised this in my previous post about this year's college football bowl pool spreadsheets, and now I'm delivering, even if a touch later than planned.

I've now got a version of the summary file out there with the aforementioned scenario analysis built in. Here's how it works:

  • I'm using the betting lines (point spreads) for each game to determine the probabilities of the favorite teams winning. Essentially, I used the College Football table found on this website to convert the point spreads to the probabilities.
  • I then run all not-yet-played games (meaning, the winner hasn't been set on the Picks tab) through 1,000 random scenarios, where the outcomes are weighted accordingly. That is, if I run 1,000 scenarios of a game that is a 7 point spread, the favorite in that game should come close to winning 70.3% of the time, but it's still random, so it make be more or less to some degree.
  • For games that have already been played, I always use the actual winner in the scenarios.
  • Then I apply the winning teams from each scenario to each participant's picks and points, and see which participant ends up with the most points and thereby wins each scenario. Actually I rank all participants' score for each scenario.
  • Then I calculate each participant's overall odds of finishing at each possible rank, including their chances of winning the pool, based on that sample of 1,000 scenarios.
  • Finally, I do some analysis for each participant to highlight which games are of particular importance to each person. For example, it might be that for a given participant, 100% of the scenarios for which they win the pool involve California beating Air Force in the Armed Forces bowl. It'd be handy for everyone to know that.
  • This functionality is all found in two new tabs -- ODDS OF WINNING and CRITICAL GAMES -- as the rest of the sheet remains unchanged.

It's worth noting that running these 1,000 scenarios on my PC -- which is a decent Dell Latitude E6520 (2.40 GHz Intel i7 CPU with 8 GB of RAM and a Samsung 850 EVO 500 GB SSD) -- takes a touch over a minute. I do think that only running 1,000 scenarios is likely way under-sampling things, particularly when there are 20+ games remaining, which means there are over a million and up to 4.4 trillion possible outcomes! Given that, I could build out an option to run 10,000, 20,000, or even more scenarios. It's just that run time for such an option will go up in a linear fashion as you add more scenarios, meaning for 20,000 scenarios it could be half an hour or longer probably, and I don't know that folks really want to wait that long for what still amounts to a sampling of potential outcomes. Also, I don't know that the overall precision of the estimated odds to win for each participant will become that much better.

Without further ado, here are this year's college football bowl pool management files:

If you plan to migrate your data from the prior Summary file version to this one, you should be able to simply copy-paste all of the participants' picks and points from one to the other. I'd do each participant separately, and ALWAYS use the Paste Special... Values option.

As always, the file is protected, but not with a password, so feel free to unprotect the workbook and then unhide some of the sheets running this stuff behind the scenes to see how it works. And if you add some cool hacks/additions, pass them along, so we can all enjoy them.

Enjoy, everyone!

Saturday, December 12, 2015

2015-16 NCAA Football Bowl Pool Files Are Ready

UPDATE as of 12/14/2015: Initially, I left off one of this year's now 42 bowl games -- actually the very first one: the Air Force Reserve Celebration Bowl, featuring Alcorn State and North Carolina A&T. Thank you, readers "Anonymous" and Tron Guy, for setting me straight. For some reason this bowl doesn't appear on the NCAA's website, nor on Wikipedia. And none of the betting line service websites I looked at to get odds shows it. Odd. This has been corrected in both files now.

UPDATE as of 12/28/2015: I've now got a version of the summary file out there with the aforementioned scenario analysis built in. I'm using the betting lines (point spreads) to determine the probabilities of the favorites winning, and then running 1,000 random scenarios, where the outcomes are weighted accordingly. Then I apply the winning teams from each scenario to each participant's picks and points, and see who wins each scenario. Then I calculate each participant's overall odds of winning the pool, based on that sample of 1,000 scenarios. Finally, I do some analysis for each participant to highlight which games are of particular importance to each person. For example, it might be that for a given participant, 100% of the scenarios for which they win the pool involve California beating Air Force in the Armed Forces bowl. It'd be handy for everyone to know that. It's worth noting that running these 1,000 scenarios on my PC -- which is a decent Dell Latitude E6520 (2.40 GHz Intel i7 CPU with 8 GB of RAM) -- takes a touch over a minute. I do think that only running 1,000 scenarios is likely way under-sampling things, particularly when there are 20+ games remaining, which means there are over a million and up to 4.4 trillion possible outcomes! Given that, I could build out an option to run 10,000, 20,000, or even more scenarios. It's just that run time for such an option will go up in a linear fashion as you add more scenarios, meaning for 20,000 scenarios it could be half an hour or longer probably, and I don't know that folks really want to wait that long for what still amounts to a sampling of potential outcomes. Also, I don't know that the overall precision of the estimated odds to win for each participant will become that much better.

At long last, here are this year's college football bowl pool management files:

It took me a bit longer than normal to get the files out this season, and I've gotten many comments, emails, Facebook messages, etc., asking when they'd be ready. Well...they're ready.

For those unfamiliar with the two-file system, the idea is that pool managers can distribute the simple, single person Entry Form version to those in their pool to make their picks and assign confidence points. The participants them email back that file, and the manager can simply copy, paste special... values those picks and points into the Summary file, which is where they manage everything.

Note: Here's how I dealt with the playoff semi-finals and championship: I've used formulas to automatically fill in the two winners of the semifinals for the left part of the summary file. For each person's picks, they'll have the two teams they selected to win those semi-finals to pick from in the championship. Let me know if that doesn't work well or if you're confused by it. It does make the Individual Chart for the championship game a bit odd if a participant picked a team to win that game that didn't even play in it, but the points system still works fine.

Another note: I don't have the final record for the Navy team, since they're currently playing in the annual Army-Navy game this weekend. I've also left that cell unprotected so you can easily update the record for the Midshipmen if you want to.

Also, I don't yet have my personal picks completed, but will add them, as well as those for my wife (MRS. EXCEL_GEEK) and son (EXCEL_GEEK, JR.) and update the files once we're ready. Some of you have voiced a preference to have those picks included, to give you a benchmark for your own picks (and to see me lose each year to my wife.) UPDATE: Our picks are now included.

Finally, I'm planning on working in a new feature that I'll release in a few days that will enable the pool manager to run a series of simulated scenarios to see which Participants, given their picks and points vs. games that have already been played vs. point spreads, have the greatest chances of winning the pool. It should be a neat feature.

Enjoy, everyone!

Wednesday, September 09, 2015

2015-2016 NFL Pick 'Em Pool

At long last, I've completed this season's NFL Pick 'Em pool. Some of you have diligently been pestering me for weeks now. I am truly sorry to make you wait up to the night before opening game. Truly. That said, I had an ambitious list of new features -- several that have been requested for a few years now -- to integrate into the old spreadsheet.

For example, many, many of you wanted the ability to not just pick winners and losers, but to pick against the spread. Done. This is now an option on the all new Settings tab. Selecting this option will un-grey-out a new row where you input each game's favorite and point spread, which is then taken in to account when determining correct picks.

Similarly, lots of you have requested support for a Confidence Points system -- so not all games counted for the same amount of points if picked correctly. Not only this, but some of you wanted a traditional "1-X" system where you assigned 1 point to a game, 2 points to another, 3 to another, all the way up to 16 or however many games there are to pick that week. Others wanted a "Pool of Points" system wherein you start with a defined pool of points that you must spread across all of that weeks games as you see fit. I've added support for both. Enabling Confidence points of either type will un-grey-out new columns next to each participants picks in which to input their confidence points, which are then taken into account when calculating overall weekly points.

Also, lots of you wanted more capacity for participants. 100 wasn't enough. I've upped it to 300. I could keep adding more capacity, but it comes at a file size trade-off. I'm already up to over 3 MB now!

I did a few other things, too, like cleaning up and exposing the Schedule tab. Many of you have expressed interest in a college football pick 'em. Well, just un-protect (it's not password protected) and change that Schedule tab to whichever games you like. I also fixed the often reported bug with the sorting buttons. Those should work now. Finally, one this I didn't do this year is create the companion "picks" version of the sheet. No one ever really said it was that helpful, and it was just something else to maintain. If you need a way to have your participants easily send you their picks, they can just use the same file -- sorry again, about that 3 MB file size.

Without further ado, here's the file: 2015-16_NFL_Weekly_Football_Pool.xlsm

More than a handful of folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheet. This, like the college bowl pool sheet I do, I do for free. That said, if you are so compelled to contribute to my son's college saving plan, feel free to PayPal me whatever you like. PayPal just recently launched this great new feature called PayPal.me that makes it really easy to request any amount:

$5.00

$10.00

$25.00

$50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Anyways...please enjoy this year's NFL Pick 'Em. If you encounter a bug or need help with using the sheet, let me know.

Later,

Excel_Geek

Monday, March 16, 2015

2015 March Madness Brackets are Ready!

Here we go, everyone! After a year off (sorry about that), the much anticipated ExcelGeek.com March Madness pool manager spreadsheet (and associated individual picks spreadsheet) is out!

The system works the same as in previous years. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to automatically pull their picks into the master.


Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Like I first did in 2012, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it -- I've automated the process.

What you will do is the following:
  1. Download the "master" file by clicking on the link above.
  2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
  3. Copy the "lock code" the file presents you.
  4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
  5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
  6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little avatar on it. It will display (among other things) the "key code" you'll need to activate the file.
  7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!
Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.


Lock Code


Best of luck, everyone!

Excel_Geek