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

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:

• 2020-21_Bowl_Pool_Entry_Form.xlsx
• 2020-21_Bowl_Pool_Summary.xlsm

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!

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:

• 2020-21_NFL_Weekly_Football_Pool.xlsm
• 2020-21_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. 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