Monday, August 31, 2020

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:

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

20 comments:

Shane said...

I tried updating the scores this morning and it says "There didn't seem to be any final games"

Excel_Geek said...

@Unknown -- I'll take a look at it this morning quick.

Excel_Geek said...

@Unknown -- I've now fixed the glitch. Embarrassing...

MUCHO said...

HOW DO YOU UDATE SCORES? IS THERE AN UPDATE BUTTON?

Excel_Geek said...

@MUCHO, simply click the little Update Scores Button. Be sure that you enabled macros when you opened the file, though, or that button won't do anything.

Unknown said...

Hello-- looks like an awesome doc, thanks for sharing. When I try to update scores though, it says macros are corrupted and to open a back-up file. Can you advise? Thanks in advance.

Excel_Geek said...

@Unknown RE: corrupted macros...

I've never seen that before. You could always re-download and try fresh. Feel free to reach out to me via email. My address is my first initial and last name at Google's popular email service. Thanks, Eric Hunzeker.

Daniel X said...

due to Covid some games have changed, Week 6. Is there a way that we can update to reflect the updated changes?

MUCHO said...

DO YOU HAVE AN UPDATE FOR THE GAMES THAT GET MOVED B/C OF COVID

Renae said...

Any chance there's an update to the weekly game schedule?

Renae said...

Never mind. I figured it out. :)

Excel_Geek said...

@Daniel X, @MUCHO, and @Renae -- Looks like Renae has figured it out, but you can make adjustments to the schedule yourselves. Simply go to that tab, unprotect, and make the changes. I haven't done so yet myself, but if I get around to it, I'll try to get an update posted. No need to wait on me, though.

MUCHO said...

Yeh u can cut n paste from other weeks. But it messed with the codes sometimes when u update score. If u type the score manually for those games along with the winner n then press update every will work out

Excel_Geek said...

@MUCHO RE: Cut n paste... Yeah, cutting and pasting would be a disaster. Copy --> Paste Special... Values would be the way to go to not mess up any formulas.

Anonymous said...

Good Morning. Any updates on the new updated excel sheet due to Covid some games have changed. I tried moving some teams from a different week to the current week and its getting really messy. Is there a way that we can update to reflect the updated changes?

Nole Lou said...

Will you be doing a college bowl spreadsheet this year?

Excel_Geek said...

@Nole... yep! I plan to get that posted sometime tomorrow.

Nole Lou said...

Awesome, thanks!

Shane said...

Are you doing a 2021-2022 football spreadsheet?

Excel_Geek said...

@Unknown RE: doing a 2021-2022 football spreadsheet...

Yeah. I'm planning to. Just haven't found the time yet. I'll see what I can do this weekend.