Monday, August 12, 2019

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

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:

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

25 comments:

Anonymous said...

Are you doing a college football season pool like you did a couple years ago?

Excel_Geek said...

@Anonymous... I've updated the one that I designed around the specific needs of the customer who requested that a couple years ago, but if you'd like to check it out, feel free to email me. I use gmail and my email is my first initial and last name. My name's Eric Hunzeker, btw.

Bill said...

Hi - I can't figure out how to have the scores update automatically using the Update Scores button. I click it, and it says "Run-time error '429' ActiveX component can't create object. I'm very unfamiliar with VBA, so I'm hoping you can help? Thanks.

Excel_Geek said...

@Bill, what kind of computer are you using? Feel free to email me. E hun z ker (at) g mail dot com. Obviously remove those extra spaces.

Unknown said...

Trying to update scores after 1:00 pm games today - only scores that update are ATL 10 and DEN 14 - just those 2 teams. Using PC.

Excel_Geek said...

@Unknown... Yep, there's a problem. I accidentally left in one line of code i was using to test pulling scores where i was using the preseason weeks. I'll get an update posted soon. I think even with that fixed it's having trouble pulling scores from games that went into overtime. I need to research and get a fix out.

Anonymous said...

Our tie breaker number was for both Monday night games combined, is there a quick fix that I can do on my spreadsheet to accommodate this?

Excel_Geek said...

@Anonymous RE: week 1 tie-breaker involving both Monday night games...

Sure! Just unprotect that sheet (it's not password protected) and go to Cell AK10, which is the TIE-BREAK PTS MISS column's top cell. Change the formula from this:

=IF(ISBLANK(AH10),"",IF(AJ10=1,ABS(AH10-SUM($AG$4:$AG$5)),""))

to this:

=IF(ISBLANK(AH10),"",IF(AJ10=1,ABS(AH10-SUM($AG$4:$AG$5)-SUM($AE$4:$AE$5)),""))

Then copy-paste that formula down the entire column. Re-protect the sheet to help prevent accidental goofs, and you're all set! Enjoy!

Anonymous said...

This excel file is incredible! I do have a question though. On the .xlsx file, which does not have the tie-break points miss column, the tie-break points are not populating to show the monday night points pick winner. Can you please advise. Thanks!

Excel_Geek said...

@Anonymous RE: .xlsx file...

All of the calculations dealing with the tie-breaker points happen in the .xlsm file. The .xlsx file is simply where your participants make their pick and set their tie-breaker points (which is the combined score of the Monday night game) and send them off to your pool administrator to add into the .xlsm file. Let me know if you've got questions.

TreDog said...

Are you doing a CFB bowl pool again this year? Thanks in advance.

Shane said...

Can you make a confidence pool sheet for all 40 of the College bowl games?

Excel_Geek said...

@Unknown RE: confidence pool for college bowl games...

Look for my annual college bowl pool sheet, which I'll be posting later today.

Excel_Geek said...

@TreDog RE: college bowl pool...

Look for my annual college bowl pool sheet, which I'll be posting later today.

redsahx64 said...

I greatly appreciate your sheets and all the work you do to create them. In your NFL sheets there is enough room to add over 200 participants. Is it possible to do the same with the College Bowl pick tracker because I see where it only tracks 40? I have a group of over 100 that I need to track.

Thanks again!

Excel_Geek said...

@redsahx64 RE: more participants for the college bowl pool...

Unfortunately, given the column limitation in Excel and given that I chose, way back in the day, to lay this one out with participants as columns instead of as rows, it'd be a major overhaul to make that happen. My only suggestions is to have multiple copies of the file, which I realize isn't ideal. sorry I couldn't have a better answer.

redsahx64 said...

Thanks for the quick response. I was thinking I'd have to go about doing it the same way you suggested with using multiple copies of the file. Again great job on all the files that I have had a chance to view. Is there an area where I can view all files? Can these be used for multiple years with little editing? Example the NFL weekly sheet can that be reused by editing the header with teams or is that all coded so to pull in the final weekly scores? Can't wait to try out the March Madness.

Excel_Geek said...

@redsahx64,

If you want to find all of the football pool-related posts, which is where you'd find the download links, just use the Labels feature on my blog. Scroll down a bit and it's on the right side of the page. Click on "football pool" to filter down to the 12 posts containing my NFL pick 'em, college football bowl game pick 'em, and college football pick 'em files. Click March Madness to find those posts. I release new versions each year, so you don't have to update them yourself. Sometimes it's a lot more complicated than you might expect. Other than the March Madness files, which are password protected and locked down, the others are not password protected, and you can edit them however you see fit. If you make something cool, it'd be great if you tell me about it.

Bill said...

Hey - I'm a little behind in updating my league's picks, and it's been about a month since I opened up the file. I'm trying to pull scores for Weeks 11-15 now, and I'm getting an error: Runtime error '35012'. Any thoughts?

Excel_Geek said...

@Bill RE: Error when trying to pull scores...

Not sure. Feel free to send me the file and I'll see if I can replicate and let you know.

redsahx64 said...

Any chance you can put together a XFL Weekly Football Pool Tracker? I'm thinking of starting a tester league of Pick'ems for XFL but it won't let me adjust team names due to protected sheets.

Thanks!

Excel_Geek said...

@redsahx64 RE: XFL...

I hadn't even heard about the XFL until your comment. Feel free to unprotect the workbook and worksheets and modify to your heart's content.

Peter said...

Hi Eric - the NFL sheet is incredible! I'm trying to change "OAK" to "LV" but cannot find that table. Can you assist, please? Thanks! - Peter

Excel_Geek said...

@Peter RE: changing Oakland to Las Vegas... That's a little more involved than you might guess. I plan to release a version for this year, as well, so maybe just hold tight for a few days until I get the new version out.

Excel_Geek said...

@Peter (and everyone, I guess), this year's version is now out.