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:
$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
