Excel_Geek...I'll Do That in Excel for $50

2024-25 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Sun, 04 Aug 2024 23:12:00 +0000

Folks, this may be the earliest I've managed to get this season's NFL Pick 'Em pool files posted! Like last year, it seems that all I had to do to make this year's version was to update to the 2024-2025 schedule. I did test the automatic score pulling feature, and it seems to be good, but I had to use the scores from 2023 to do that testing since no games have been played in 2024 yet. If you run into issues, just let me know.

Like years past, there's also 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.

Without further ado, here are the files:

If you have any trouble downloading the files, try right-clicking on the links and opening them in an Incognito window (using Chrome.) That always does the trick for me.

Remember, this NFL pick 'em sheet I do for free. That said, if you just _have to_ do something for me for my efforts, 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

2023-24 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Thu, 07 Sep 2023 12:12:00 +0000

Folks, just in the nick of time I've completed and released this season's NFL Pick 'Em pool. Apologies for making so many of you pester me up to the last minute to get it done! Like last year, it seems that all I had to do to make this year's version was to update to the 2023-2024 schedule. I did test the automatic score pulling feature, and it seems to be good, but I had to use the scores from 2022 to do that testing since no games have been played in 2023 yet. If you run into issues, just let me know.

Like years past, there's also 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.

Without further ado, here are the files:

If you have any trouble downloading the files, try right-clicking the links and opening in an Incognito window (using Chrome). That always does the trick for me.

Remember, this NFL pick 'em sheet, like the college bowl pool sheet I do, I do for free. That said, if you just _have to_ do something for me for my efforts, 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

2022-23 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Sun, 14 Aug 2022 14:45:00 +0000

Folks, I've completed and released this season's NFL Pick 'Em pool. I actually got this done before too many of you had to pester me! All I had to do to make this year's version from last year's was to update to the 2022-2023 schedule. Seems too easy. I did test the automatic score pulling feature, and it seems to be good, but, as always, just let me know if things don't seem to be working.

Like years past, there's also 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.

Without further ado, here are the files:

Remember, this NFL pick 'em sheet, like the college bowl pool sheet I do, I do for free. That said, if you just _have to_ do something for me for my efforts, 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

2021-22 College Bowl Pool Summary File is Ready!

noreply@blogger.com (Excel_Geek) — Mon, 20 Dec 2021 18:33:00 +0000

Ok, everybody! The summary file is ready.

Without further ado...the files:

If you have any trouble getting the download links to work, try a couple of things: 1) try opening the link in an icognito tab, 2) try using a different browswer, 3) try right-clicking and opening in a new tab or "saving as...". Honestly, I don't fully understand the underlying issue with Chrome and these download links.
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 -- once I've got that posted -- 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.

We'll see if we can make it through the entire bowl season without changes, cancellations, etc.

Finally, 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, and I'll put these donations towards website hosting fees:

$5.00 $10.00 $25.00 $50.00

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

Enjoy, everyone!

2021-22 College Bowl Pool Entry Form is Ready! (Summary file to come)

noreply@blogger.com (Excel_Geek) — Thu, 16 Dec 2021 20:28:00 +0000

Ok, everybody! I've got this season's college football bowl pool Entry Form file finally ready! I'm still working on the modifications necessary to bump up the old version to include this year's 43 total bowl games. (I didn't include the FCS Bowl, which was already played back on the 12th.) I'll try to wrap that up this evening, but since I know many of you pool administrators out there need to get picks in from your participants, here's at least that file. Special shout-out to super fan Kendall for helping get some of the info pulled together for me this year. Thanks, man!

Without further ado...the file:

2021-22_Bowl_Pool_Entry_Form.xlsx

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 -- once I've got that posted -- 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.

We'll see if we can make it through the entire bowl season without changes, cancellations, etc.

Finally, 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, and I'll put these donations towards website hosting fees:

$5.00 $10.00 $25.00 $50.00

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

Enjoy, everyone!

2021-22 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Wed, 25 Aug 2021 12:48:00 +0000

Folks, I've completed and released this season's NFL Pick 'Em pool. Like past years, many of you have been diligently pestering me for a few weeks now. The tweaks I had to make to this year's version from last year's were to 1) update to the 2021-2022 schedule, which adds a Week 18 for the first time ever, and 2) make one small change to the macro that automatically pulls scores from FootballDB.com, as they must have just slightly altered their HTML code on the scores pages. I did test that change using prior years and all appears to be working, but, as always, just let me know if things don't seem to be working.

Like years past, there's also 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.

Without further ado, here are the files:

As also 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. I remind them that this, like the college bowl pool sheet I do, I do for free. That said, if you just _have to_ do something for me for my efforts, 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

2020-21 College Bowl Pool Files Are Ready! Finally!

noreply@blogger.com (Excel_Geek) — Tue, 22 Dec 2020 13:43:00 +0000

Ok, everybody! This season's college football bowl pool files are finally ready! I'm sorry I didn't get these out late Selection Sunday, per usual, or even yesterday before the first game, as I'd planned, but it was a challenge to make all the modifications I needed to, and the last minute changes kept rolling in.

Many, many noteworthy items this COVID-19-impacted year:

There would have been 3 new bowl games added this year, had it not been for the pandemic. There is the Myrtle Beach Bowl, which was actually played yesterday. There would have been the Fenway Bowl, which was to be played at Fenway park a la the Pinstripe Bowl played at Yankee Stadium, but it was canceled due to COVID, and was replaced with a one-year-only Montgomery Bowl, which will be played tomorrow. Finally, there was to be a LA Bowl played in southern California, but it was canceled this year due to COVID.
16 bowl games canceled, some stating that it was "due to the pandemic" (or similar), and a few outright saying they canceled because there weren't enough teams to play, given the large number of otherwise eligible teams choosing to opt out of the bowl season. We've lost the Bahamas Bowl (COVID, travel restrictions), the Frisco Bowl (SMU couldn't play due to COVID cases), the Celebration Bowl (both the MEAC and SWAC canceled all of their fall sports), the Las Vegas Bowl (pandemic reasons), the Hawai'i Bowl (COVID, travel restrictions), the Independence Bowl (couldn't find team to play Army), the Quick Lane Bowl (pandemic reasons), the Military Bowl (had selected Army when the Independence Bowl canceled, but couldn't find another team), the Pinstripe Bowl (pandemic reasons), the Holiday Bowl (pandemic reasons), the Cheez-It Bowl (this is the old Copper Bowl, not last year's Camping World Bowl, which this year is the Cheez-It bowl -- couldn't find enough teams to play), the Redbox Bowl (was the first to cancel due to COVID back in August), the Sun Bowl (pandemic reasons), the Birmingham Bowl (couldn't find enough teams), and the previously would-be new Fenway and LA Bowls (pandemic reasons).
All in we're left with 29 games, down from last year's 41. (41+3-16+1=29)
We did have two title sponsor changes. Last year's Camping World Bowl becomes this year's Cheez-It Bowl, which isn't to be confused with last year's Cheez-It Bowl, which was really just an incarnation of the old Copper Bowl, which is no more. The Belk Bowl from last year is this year's Duke's Mayo Bowl, which is probably one of my favorite title sponsor-named bowls of all time.
I've updated the sheets with opening betting lines for your convenience, but you should probably update these as we get closer to the games, as the lines are constantly being updated until game time. I use the "VI Consensus" lines from Vegas Insider.

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.

I'll personally be shocked if we make it through the entire bowl season without more changes, cancellations, etc., to bear with me and I'll try to keep up.

Finally, 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, and I'll put these donations towards website hosting fees:

$5.00 $10.00 $25.00 $50.00

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

Enjoy, everyone!

2020-21 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Mon, 31 Aug 2020 13:56:00 +0000

UPDATE Sept 11, 2020:

I'm super embarrassed! A couple users of the spreadsheet pointed out that it wasn't pulling scores for last night's game. Well, embarrassingly, just like I did last year, this was due to me leaving in one row of code in the macro that I was using to test pulling scores using preseason games. I'll update the master spreadsheet, so you can re-download, but if you've got yours all set up and everyone's picks added and don't want to have to do that again, you can make a simple VBA code change yourself!

The first thing you'll need to do is enable the "Developer" menu in the Ribbon (if you haven't already). Head to File - Home - Options - Customize Ribbon, and then find Developer in the left list of available menus and commands, and Add it over to the right side of selected menus and commands. Click OK.

Now you'll have a new menu for Developer. Click on that and choose Visual Basic. That will launch what is known as the Visual Basic Editor (VBE). You'll see a sort of project-organized view of things. In the top left part of this view, look for the Modules section and specifically Module2. Double-click on that. The main part of the view now should show the code that enables pulling scores from FootballDB.com. I want you to look about 9 lines down into that macro code (including the blank lines) for where it says, "'THIS_SEGMENT = "reg" followed by "THIS_SEGMENT = "pre"." You'll notice that the "reg" line has a single quote in front of it and the font is green. Adding the single quote in front of it "comments it out" so it doesn't work. I had commented out the "reg" (regular season) line and added a "pre" (preseason) line for testing. You simply need to un-comment out the "reg" line by deleting that leading single quote and comment out or delete the "pre" line. Done! You're a coder now!

All should be right with the world. At least that I know right now.

Sorry for the hassle. Feel free to download a fixed version of the master file if you'd rather not hack around in the code yourself.

Folks, I've completed and released this season's NFL Pick 'Em pool. Like past years, many of you have been diligently pestering me for a few weeks now. The only tweaks I had to make to this year's version from last year's was to 1) change "OAK" to "LV" throughout, since the Raiders have officially made their way to Las Vegas, and 2) update to the 2020-2021 schedule. It also appears that FootballDB.com didn't make any changes to how they display completed games, so the code for automatically pulling scores _should_ still work. The problem is that since the NFL cancelled all of the preseason games, I didn't get the chance to test it. If things end up not working after Week 1, I'll push out an update.

Like years past, there's also 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.

Without further ado, here are the files:

2019-20 College Bowl Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Tue, 10 Dec 2019 01:22:00 +0000

Ok, everybody! This season's college football bowl pool files are now ready! I'm sorry I didn't get it out last night per usual, but I was feeling a touch under the weather.

Some noteworthy items:

Navy has made a bowl game again, and I'm again not waiting until after the (now traditional, "final week") Army-Navy game, so I don't know Army's final record, so I've simply listed them as "[23] Army (10-2 or 9-3)". I've found this to be simpler than leaving that cell unprotected, allowing you all to edit it, and then dealing with the possibility that you do that AFTER picks are entered and thereby cause the picks for those game to no longer match.
The bowl line-up is essentially the same as last year -- no new bowls and no eliminated bowls.
We do have one or two sponsor/name changes: The Dollar General Bowl is now the LendingTree Bowl, and the bowl I had listed as the Taxslayer Bowl, which I guess was actually officially called the Taxslayer Gator Bowl starting last year, I've now just listed as the Gator Bowl, matching how I shorten most other sponsor-name-included bowl game names -- at least those bowl games whose names don't ONLY include a sponsor name.
Like I started doing last year, in addition to including the teams' records, I've included their rankings (for those in the top 25).
I've updated the sheets with opening betting lines for your convenience, but you should probably update these as we get closer to the games, as the lines are constantly being updated until game time. I use the "VI Consensus" lines from Vegas Insider.
Finally, 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.

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.

Finally, 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, and I'll put these donations towards website hosting fees:

$5.00 $10.00 $25.00 $50.00

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

Enjoy, everyone!

2019-20 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Tue, 13 Aug 2019 03:07:00 +0000

UPDATE Sept 9, 2019:

A couple users of the spreadsheet pointed out that it wasn't pulling scores for yesterday's games. Somehow it was only pulling scores seemingly randomly for Denver (who plays tonight) and Atlanta. Well, embarrassingly, this was due to me leaving in one row of code in the macro that I was using to test pulling scores using preseason games. I'll update the master spreadsheet, so you can re-download, but if you've got yours all set up and everyone's picks added and don't want to have to do that again, you can make a simple VBA code change yourself!

The first thing you'll need to do is enable the "Developer" menu in the Ribbon (if you haven't already). Head to File - Home - Options - Customize Ribbon, and then find Developer in the left list of available menus and commands, and Add it over to the right side of selected menus and commands. Click OK.

Now you'll have a new menu for Developer. Click on that and choose Visual Basic. That will launch what is known as the Visual Basic Editor (VBE). You'll see a sort of project-organized view of things. In the top left part of this view, look for the Modules section and specifically Module2. Double-click on that. The main part of the view now should show the code that enables pulling scores from FootballDB.com. I want you to look about 9 lines down into that macro code (including the blank lines) for where it says, "'THIS_SEGMENT = "reg" followed by "THIS_SEGMENT = "pre"." You'll notice that the "reg" line has a single quote in front of it and the font is green. Adding the single quote in front of it "comments it out" so it doesn't work. I had commented out the "reg" (regular season) line and added a "pre" (preseason) line for testing. You simply need to un-comment out the "reg" line by deleting that leading single quote and comment out or delete the "pre" line. Done! You're a coder now!

There was one other issue I found once I fixed this, though. Somehow it wasn't pulling scores from the Colts-Chargers or Lions-Cardinals games. I noticed right away that these were the only overtime games yesterday. FootballDB.com must have changed out they display overtime games since last season, and the regular expression code that matches "final" games wasn't working. I found it right away. In the past the little box score for final games all displayed a "FINAL" in the top right. Now for overtime games, they display "F/OT". So a simple little tweak to that regular expression was needed. If you head to line 40 in that same macro you'll see a comment line that says, "' This identifies only the Final games." The bottom line in that cluster is what we'll need to tweak. Where it says "FINAL" in that long string of gobbleygook, it needs to say "(FINAL|F/OT)". This will make it match either "FINAL" or "F/OT".

All should be right with the world. At least that I know right now. This does highlight one other issue, though. You'll notice that we did have one game end in a tie -- Lions-Cardinals. The way the sheet is designed, no points will be awarded on this game since there was no winner. If that's not how you do it in real life, make a comment and let me know.

Sorry for the hassle. Feel free to download a fixed version of the master file if you'd rather not hack around in the code yourself.

Folks, I've completed and released this season's NFL Pick 'Em pool. Like past years, many of you have been diligently pestering me for a few weeks now. The good news was that we don't (still) have any new team names/cities to deal with this year. Maybe next year? The other good news (for me) was that FootballDB.com didn't decide to change how they display completed games, so the code changes I made last year seem to be working for this year, also. As always, though, if we get into the season, and something doesn't seem to be working right, just let me know. Like years past, I have created 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.

Without further ado, here are the files:

Back Again! 2019 March Madness Spreadsheet System

noreply@blogger.com (Excel_Geek) — Mon, 18 Mar 2019 02:50:00 +0000

Guess what?!? After a one year hiatus, I've decided to push out my much anticipated two-file ExcelGeek.com March Madness pool manager spreadsheet system.

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.

You saw that right, folks! Both the individual picks spreadsheet AND the more complex "master" spreadsheet are FREE this year. For those of you new to my March Madness spreadsheets, I used to charge a nominal amount -- $2.00 -- for the "master" file. I first made these free two years ago and asked you all to help out a good friend who needed to raise money for medical expenses. This year, I've just decided to make it free. If you just have to pay me _something_ for my time, etc., feel free. I'll give you instructions for doing so. The files are still both locked down and protected, and just as before, the master spreadsheet requires a "key code" to unlock its Excel_Geeky magic.

Like I began doing in 2015, I don't make you email me the "lock code" the file presents to you when you first open it and then email you back the corresponding "key code" to unlock the file. I've automated the process via a webpage. You'll no longer have to submit a PayPal payment to see the "key code".

What you need to do is the following:

Download the "master" file by clicking on the link above.
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 when prompted. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
Copy the "lock code" the file presents you.
Paste that code into the "Lock Code" field found on this linked page. Be sure to delete any leading or trailing spaces/carriage returns added when you paste it. For some reason it normally adds an extra trailing space or carriage return. A valid "lock code" will contain no spaces -- leading, trailing, or otherwise.
Click the "Submit" button.
If you've done everything right, you'll be presented with the "key code" you'll need to activate the file.
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 or if you do it with leading or trailing spaces, you won't receive a "Key Code" or you'll receive an invalid one, and you won't be able to unlock your file. Do not forget this part! One nice thing about me not charging for the file any more is that you can always try again without having to pay again.

These files are protected with a password, so you won't be able to get into all of the inner workings. Sorry, there's some stuff in here, particularly the lock code - key code stuff, that I want to keep locked down for now. If you have some ideas for cool hacks/additions, pass them along, and I'll see what I can do for next year.

So for you folks who must pay me for my efforts, 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/5.

Thank you and bless you for helping if you can, and best of luck during March Madness, everyone!

Excel_Geek

2018-19 College Bowl Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Mon, 03 Dec 2018 14:58:00 +0000

Ok, everybody! This season's college football bowl pool files are now ready!

Some noteworthy items:

I'm again not waiting until after the Army-Navy game, so I don't know Army's final record, so I've simply listed them as "Army (10-2 or 9-3)". I've found this to be simpler than leaving that cell unprotected, allowing you to edit it, and then dealing with the possibility that you do that AFTER picks are entered and thereby cause the picks for those game to no longer match.
The bowl line-up is essentially the same as last year -- no new bowls and no eliminated bowls.
We do have three sponsor/name changes: The Zaxby's Heart of Dallas Bowl is now the Servepro First Responder Bowl. The Motel 6 Cactus Bowl is now the Cheez-It Bowl. And the Foster Farms Bowl has become the Redbox Bowl. You can just feel the tradition building with those...
This year, in addition to including the teams' records, I've included their rankings (for those in the top 25).
I've updated the sheets with opening betting lines for your convenience, but you should probably update these as we get closer to the games. I use the "VI Consensus" lines from Vegas Insider.
Finally, 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. It ends up being an exciting game, at least.

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.

Finally, 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/5.

Enjoy, everyone!

2018-19 NFL Pick 'Em Pool Files Are Ready

noreply@blogger.com (Excel_Geek) — Mon, 27 Aug 2018 12:36:00 +0000

Folks, I've completed and released this season's NFL Pick 'Em pool. Like past years, many of you have been diligently pestering me for a few weeks now. The good news was that we don't (yet) have any new team names/cities to deal with this year. Maybe next year? The bad news (for me) was that FootballDB.com decided to significantly change how they display completed games, so I had to rewrite much of my automatic score updating code. Oh well, I believe it's ready to go now. As always, if we get into the season, and something doesn't seem to be working right, just let me know. Like years past, I have created 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.

Without further ado, here are the files:

2017-18 College Bowl Pool Sheets

noreply@blogger.com (Excel_Geek) — Wed, 06 Dec 2017 03:44:00 +0000

Ok, everybody! You've all mostly been patient. I apologize that it took me a couple of extra days this year. I've been quite busy with the _real_ job. This season's college football bowl pool files are now ready!

Some noteworthy items:

Since we again aren't waiting until after the Army-Navy game, and we don't know Army and Navy's final records, I've simply listed what each of those two teams may have for final records. That's simpler than leaving those cells unprotected, allowing you to edit them, and then dealing with the possibility that you do that AFTER picks are entered and thereby cause the picks for those game to no longer match.
The bowl line-up is different then last year. We're down one bowl! No more Poinsettia Bowl. :: Sniff ::
Also, three other bowls got a rename: The Miami Beach Bowl is now the Frisco Bowl. The St. Petersburg Bowl is now the Bad Boy Mowers Gasparilla Bowl. O_o And the Russell Athletic Bowl is now the Camping World Bowl.
There aren't currently betting lines set on the two games involving Army and Navy, 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 those two 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.
Finally, 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. It ends up being an exciting game, at least.

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 and the new NCAA football 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/5.

Enjoy, everyone!

NEW THIS YEAR! College Football Pick 'Em Pool!

noreply@blogger.com (Excel_Geek) — Fri, 11 Aug 2017 12:26:00 +0000

Hey everyone! I recently had a person approach me to convert my NFL Pick 'Em Pool into a college football pick 'em pool. What a great idea! It was no easy feat, however, since for the college football pool, I had to allow the pool administrator to pick the games each week that he or she wanted to include in the pool, whereas for the NFL pool, the schedule is the schedule. Also, for this particular person, I needed to support the way they handled the bowl season. The result is pretty cool, though.

Here's how the pool works: at the beginning of the season the pool administrator determines the games that will be involved each week -- up to a maximum of 16 games each week -- and sends out the picks sheets for the entire season. All participants make their picks for the entire season at the beginning. They also can optionally have people answer some tie-breaker questions, though no scoring for these is accounted for in the master spreadsheet. Then the participants send their picks sheets in.

I wrote a macro that enables the pool admin to automatically pull in the picks from the participants' sheets, so that they won't have to manually key them in. They simply need to save the unprocessed picks sheets into a user-definable directory and click a button. Once they're processed, the pool administrator can move those picks files to a "processed" directory. What a time saver if you've got a couple hundred participants, right? I'll have ot figure out if I can do something similar for the NFL Pick 'Em...

I also adapted the automatic score updating from FootballDB.com, so as long as you've picked games tracked by that website, you can use that feature.

Because the system is based off of the old NFL Pick 'Em pool file, you'll still have the choice to go straight picks vs. picking against the spread, and the choice of how to allocate points for correct picks.

Here are the files:

I can already anticipate, since my readers truly are the nicest, most appreciative people on the planet, that some of you will be offering to pay me or donate to me for the sheet. This, like the college bowl pool and NFL pick 'em sheets I do, I do for free -- because I enjoy them. That said, if you just _have to_ do something for me for my efforts, feel free to PayPal me whatever you like. It does help to cover my hosting costs, etc.:

$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 inaugural NCAA Football Pick 'Em pool. We're bound to run into a bug or snag or learning curve or two along the way, so when you do, just let me know, and I'll do my best to help resolve any issues.

Later,

Excel_Geek

2017-18 NFL Pick 'Em Pool Files Are Ready!

noreply@blogger.com (Excel_Geek) — Thu, 10 Aug 2017 12:47:00 +0000

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. I had to wait a bit into the preseason, however, so that I could see how FootballDB.com was going to treat our now TWO teams from Los Angeles, so that my automatic score updater will work. Again this year, I have created 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.

Without further ado, here are the files:

2017-18_NFL_Weekly_Football_Pool.xlsm
Updated with Tampa at Miami moved to Week 11: 2017-18_NFL_Weekly_Football_Pool_schedulechange.xlsm
2017-18_NFL_Weekly_Football_Pool_Picks.xlsx

As also 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 just _have to_ do something for me for my efforts, 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

2017 March Madness Files Are Ready!

noreply@blogger.com (Excel_Geek) — Mon, 13 Mar 2017 01:16:00 +0000

Here we go, everyone! This year's much anticipated two-file ExcelGeek.com March Madness pool manager spreadsheet system 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.

You saw that right, folks! Both the individual picks spreadsheet AND the more complex "master" spreadsheet are FREE this year. For those of you new to my March Madness spreadsheets, I typically charge a nominal amount -- $2.00 -- for the "master" file. I alluded to the notion of making both free in my last post, and I've got an ulterior motive for doing so that I'll explain in a bit. The files are still both locked down and protected, and just as before, the master spreadsheet requires a "key code" to unlock its Excel_Geeky magic.

Like I began doing in 2015, I don't make you email me the "lock code" the file presents to you when you first open it and then email you back the corresponding "key code" to unlock the file. I've automated the process via a webpage. This year, however, you won't have to submit a PayPal payment to see the "key code".

What you need to do is the following:

Download the "master" file by clicking on the link above.
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 when prompted. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
Copy the "lock code" the file presents you.
Paste that code into the "Lock Code" field found on this linked page. Be sure to delete any leading or trailing spaces/carriage returns added when you paste it. For some reason it normally adds an extra trailing space or carriage return. A valid "lock code" will contain no spaces -- leading, trailing, or otherwise.
Click the "Submit" button.
If you've done everything right, you'll be presented with the "key code" you'll need to activate the file.
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 or if you do it with leading or trailing spaces, you won't receive a "Key Code" or you'll receive an invalid one, and you won't be able to unlock your file. Do not forget this part! One nice thing about me not charging for the file this year, I guess, is that you can always try again without having to pay again.

So...why have I all of a sudden decided to make the "master" file free this year? Simple. I'm hoping that by showing generosity, I can encourage some of you to do the same. You see, two of my close friends are facing a major hardship. Their names are Tracy and Rodney "Hot Rod" Nelson. Hot Rod was diagnosed several years ago with Parkinson's disease. He walks with a limp, drags one arm a bit, and suffers off and on from debilitating tremors. Sucks. right? Well, he hasn't let that get him down. Up until lately he's gone to work everyday, enjoys having beers with his friends and his wonderful wife, Tracy, and continues to pour his soul into his custom '71 Chevy pickup, participating in car show after car show. That's his passion.

Last November he came down with what was thought to be a common cold, but that ended up being pneumonia. On top of that doctors determined had a virus that was affecting his liver, heart and spleen. While in the hospital they further discovered that he had a mitral heart valve not closing and, in fact, he likely had two separate heart attacks in the couple months before. After further testing, they determined that he needed to have triple bypass surgery and a mitral valve replacement. Following the bypass and valve replacement surgery, Hot Rod had additional complications necessitating more surgery, this time on his small intestine. Following that surgery things were not going well. He had to be moved to the ICU, put on a ventilator, and induced into a coma. Things were bleak. Through the grace of God, Hot Rod pulled through. He made it out of the ICU, but still has a feeding tube for quite awhile. Rod's at home now and just passed his swallowing test, so he's on the mend. Again, this is all in addition to dealing with his Parkinson's.

Rodney has been off of work since December 5th and has actually been in the hospital since January 12th. Tracy carries their high-deductible health insurance through her job, though since she works for a government contractor, her job can be inconsistent and is often in limbo. Hot Rod and Tracy are dealing with many out of pocket costs, and their bills are quickly mounting. They've got a great group of friends and family to provide support, but with more and more medical expenses mounting and not knowing when or if he'll be able to return to work, it's going to take more than this support system to help them financially recover.

Hot Rod and Tracy are salt of the earth people who've never hesitated to lend a helping hand to anyone in need, and now they are need of many helping hands. Since you're not having to pay anything for this year's spreadsheet, if you'd be willing to give even a small amount to the GoFundMe page that was started on their behalf, we can start to make a dent in the financial mountain they'll have to climb. If that's not a possibility, if you could help share their story and that GoFundMe page via Facebook or Twitter or any other way you can, that would also be a great way to help. If enough Good Samaritan's learn of their situation, maybe, just maybe, a miracle can happen.

If you're unsure about contributing directly to the GoFundMe page, feel free to simply PayPal me whatever you think is a good contribution, and I'll gather up those donations and make one big contribution at the end.

Thank you and bless you for helping if you can, and best of luck during March Madness, everyone!

Excel_Geek

Welcome Puneet and ExcelChamps.com!

noreply@blogger.com (Excel_Geek) — Wed, 01 Feb 2017 13:48:00 +0000

Hey Everyone!

I'd like to take a moment to post about a new affiliate of ExcelGeek.com. Last summer the bright, young Puneet Gogia of ExcelChamps.com reached out to me to see if I'd be willing to add his URL to my growing list of "Other Excel+VBA Sites". Sure! I'm all about touting other Excel-related sites -- particularly those, like Puneet's, so full of basic Excel information and tutorials.

Well, Puneet's come back to me, this time with a new packaged Excel product -- an inventory management system. It's a pretty powerful tool that I'm confident some of my readers will find useful. You'll see that I added this banner ad to my right side bar.

To be clear, this is a paid endorsement, since I'm signed up under Puneet's affiliate marketing service and will get a cut of any sales generated on my site. That's not really why I'm doing this, though, and don't expect to get rich doing so. Sure, any little bit helps cover the costs of my hosting, etc., but what I'm trying to continue doing is helping out fellow Excel enthusiasts turned entrepreneurs while also sharing some incredible Excel solutions. If you're interested, check it out. If not, apologies for what may feel like a spam post.

In other news...who's getting geeked up (pun intended) for this year's NCAA March Madness? Selection Sunday is just 5 1/2 weeks away! I'm looking forward to getting those spreadsheets out, and I am toying around with the idea of making them completely free this year. Interesting, eh?

Anywho...later!

Excel_Geek

2016-17 College Bowl Pool Sheets

noreply@blogger.com (Excel_Geek) — Mon, 05 Dec 2016 18:54:00 +0000

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!

2016-17 NFL Pick 'Em Pool

noreply@blogger.com (Excel_Geek) — Wed, 10 Aug 2016 17:45:00 +0000

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:

Now with auto-score updater: 2016-17_NFL_Weekly_Football_Pool_v2.xlsm
2016-17_NFL_Weekly_Football_Pool_Picks.xlsm

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

2016 March Madness Files Are Ready!

noreply@blogger.com (Excel_Geek) — Mon, 14 Mar 2016 04:34:00 +0000

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:

Download the "master" file by clicking on the link above.
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.
Copy the "lock code" the file presents you.
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.
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.
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.
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.

Best of luck, everyone!

Excel_Geek

2015-16 Bowl Pool Spreadsheet with Scenario Analysis

noreply@blogger.com (Excel_Geek) — Mon, 28 Dec 2015 15:30:00 +0000

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!

2015-16 NCAA Football Bowl Pool Files Are Ready

noreply@blogger.com (Excel_Geek) — Sat, 12 Dec 2015 22:14:00 +0000

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!

2015-2016 NFL Pick 'Em Pool

noreply@blogger.com (Excel_Geek) — Thu, 10 Sep 2015 02:20:00 +0000

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

2015 March Madness Brackets are Ready!

noreply@blogger.com (Excel_Geek) — Tue, 17 Mar 2015 02:53:00 +0000

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.

Download the "master" file by clicking on the link above.
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.
Copy the "lock code" the file presents you.
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.
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.
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.
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!