Friday, August 11, 2017

NEW THIS YEAR! College Football Pick 'Em Pool!

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. 


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

8 comments:

keylimesoda said...

I'm kind of a rookie with Excel, can you help me understand the workflow here?

1. Generate a copy of the entry form for each player
2. Place each form in my skydrive folder
3. Send a "sharing" link to each player for their input form
4. "Process" the inputs on the pool form

Q: How do I link the schedule in my pool sheet with what shows up on the input forms? Should the input forms be auto populated/updated when I update the schedule on the pool sheet?

Excel_Geek said...

@Keylimemsoda... The way the system was designed (per direction from the customer who requested and paid for it) was that the entire schedule would be determined at the begin ning of the season. So it's not really designed to determine the game schedule each week. That's something I may be trying to figure out for next year. Also, I've done no testing of the macros when the files are stored in a Skydrive environment, so I'm not sure how well or if that will work.

chris moseley said...

are you going to do the college bowls spreadsheet / workbook again? That thing was awesome to use last year!!

Excel_Geek said...

@Chris, of course!

chris moseley said...

Sweet! Can't wait.

Chris said...

Are you doing the college football pick em this year? I have been checking daily to see if you post it. Cant wait if you do!!

Daniel said...

I really appreciate the work you've done with the college football picks. I used the season picks this last regular season. I'm not sure if you're planning to do any more with it at this point, but wanted to suggest a possible addon/extension to allow more games.

I ended up pushing it to 25 instead of the regular 16 you have and while it mostly worked there were some crashes and long delays trying to parse the entry forms in particular. I had to edit some of the backend code to get it to work at all so there's a good chance I introduced an issue there. The results population seemed to be fine other than the occasional team that's not in the default list that had to be added.

I look forward to any future versions you put out. Really great stuff.

Excel_Geek said...

@Daniel, sounds like a cool extension. Sadly, I didn't even put out a college pick 'em this year. that project was driven by one particular (paid) request, but it didn't seem to have as much appeal as the NFL one or the college bowl pick 'em, so I didn't do a new one. Feel free to share what you've created. I'd love to check it out. You can get me at e hun zeker [ a t ] g m ai l dotcom.