Wednesday, September 09, 2015

2015-2016 NFL Pick 'Em Pool

At long last, I've completed this season's NFL Pick 'Em pool. Some of you have diligently been pestering me for weeks now. I am truly sorry to make you wait up to the night before opening game. Truly. That said, I had an ambitious list of new features -- several that have been requested for a few years now -- to integrate into the old spreadsheet.

For example, many, many of you wanted the ability to not just pick winners and losers, but to pick against the spread. Done. This is now an option on the all new Settings tab. Selecting this option will un-grey-out a new row where you input each game's favorite and point spread, which is then taken in to account when determining correct picks.

Similarly, lots of you have requested support for a Confidence Points system -- so not all games counted for the same amount of points if picked correctly. Not only this, but some of you wanted a traditional "1-X" system where you assigned 1 point to a game, 2 points to another, 3 to another, all the way up to 16 or however many games there are to pick that week. Others wanted a "Pool of Points" system wherein you start with a defined pool of points that you must spread across all of that weeks games as you see fit. I've added support for both. Enabling Confidence points of either type will un-grey-out new columns next to each participants picks in which to input their confidence points, which are then taken into account when calculating overall weekly points.

Also, lots of you wanted more capacity for participants. 100 wasn't enough. I've upped it to 300. I could keep adding more capacity, but it comes at a file size trade-off. I'm already up to over 3 MB now!

I did a few other things, too, like cleaning up and exposing the Schedule tab. Many of you have expressed interest in a college football pick 'em. Well, just un-protect (it's not password protected) and change that Schedule tab to whichever games you like. I also fixed the often reported bug with the sorting buttons. Those should work now. Finally, one this I didn't do this year is create the companion "picks" version of the sheet. No one ever really said it was that helpful, and it was just something else to maintain. If you need a way to have your participants easily send you their picks, they can just use the same file -- sorry again, about that 3 MB file size.

Without further ado, here's the file: 2015-16_NFL_Weekly_Football_Pool.xlsm

More than a handful of folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheet. This, like the college bowl pool sheet I do, I do for free. That said, if you are so compelled to contribute to my son's college saving plan, feel free to PayPal me whatever you like. PayPal just recently launched this great new feature called that makes it really easy to request any amount:





Really you just have to change that dollar amount in the URL --

Anyways...please enjoy this year's NFL Pick 'Em. If you encounter a bug or need help with using the sheet, let me know.



Monday, March 16, 2015

2015 March Madness Brackets are Ready!

Here we go, everyone! After a year off (sorry about that), the much anticipated March Madness pool manager spreadsheet (and associated individual picks spreadsheet) is out!

The system works the same as in previous years. There are two files: The first is the "master" spreadsheet, used by the pool organizer to track the results of everyone's picks vs. who actually wins, etc. This is where you determine how many points a correct pick in each of the rounds is worth, as well. Also, the pool manager can make his or her picks in this file, too. The second file is the simplified "individual picks" spreadsheet used by the participants in the pool to make their picks and send the file to the pool organizer to automatically pull their picks into the master.

Once again, the individual picks spreadsheet is free, but the more complex "master" spreadsheet is not. It is ONLY $2.00, though. The files are both locked down and protected. In the case of the master spreadsheet, it requires a "key code" to unlock the magic.

Like I first did in 2012, I won't make you email me the "lock code" the file presents to you when you first open it and then email you back a "key code" to unlock it -- I've automated the process.

What you will do is the following:
  1. Download the "master" file by clicking on the link above.
  2. Open the file. Make sure you've got macros enabled. If you didn't have macros enabled when you first opened the file, enable them, then close and reopen the file, making sure to enable macros. If the file doesn't display a "Lock Code" when you open it, something didn't work right. Try again.
  3. Copy the "lock code" the file presents you.
  4. Paste that code (be sure to delete any trailing spaces added when you paste it -- it normally adds an extra trailing space for some reason) into the "Lock Code" field below.
  5. Click the "Buy Now" button below to pay the $2.00 for the file via PayPal. You do not need to have a PayPal account to pay this way. Any major credit card will work just fine.
  6. At the end of the transaction, you'll be redirected to a confirmation page with my pretty little avatar on it. It will display (among other things) the "key code" you'll need to activate the file.
  7. Copy and paste that code into the spreadsheet into the proper place, click submit, accept my terms and conditions, and you're off and running!
Again, if you don't paste in your Lock Code and complete your transaction, you won't receive a "Key Code" that will actually work to unlock your file. Do not forget this part! You may have to pay another $2.00 if you mess it up.

Lock Code

Best of luck, everyone!


Monday, December 08, 2014

2014-15 College Football Bowl Pool

You've all been waiting very patiently for me to update my annual college football bowl pool spreadsheets, and here they are!

There were all sorts of changes this year. Five all new bowl games, one discontinued bowl game, and a few renames. Plus, we're in our first year of the College Football Playoff era! That's right, we've got one game in the list for which we won't even know the teams playing until later on.

Here's the new bowl games:

  • The Camellia Bowl. This one will be played in Montgomery, Alabama between a Sun Belt Conference and Mid-American Conference teams. The bowl is owned by ESPN Regional Television.
  • The Miami Beach Bowl. This one will be played in Miami in Marlins Park. We always need more Florida bowl games, right?
  • The Boca Raton Bowl. This one will be played at Florida Atlantic University stadium in Boca Raton. It's also owned by ESPN Events, and features Mid-American conference teams until 2019.
  • The Popeyes Bowl. Officially the Popeye's Bahamas Bowl for its sponsor Popeye's Louisiana Kitchen, this one will be played in Nassau Bahamas and features Mid-American conference teams through 2016. This will be the first game played outside the U.S. since 2010 International Bowl in Toronto.
  • The Quick Lane Bowl. This bowl will be replacing the Little Caesars Pizza Bowl. This isn't merely a renaming, though. It's a whole new bowl organization. The Little Caesars Bowl is no more (for now).
Here's the renamess:
  • The former Kraft Fight Hunger Bowl is now called the Foster Farms Bowl.
  • The former Buffalo Wild Wings Bowl is now called the Cactus Bowl.
  • The former BBVA Compass Bowl is now called the Birmingham Bowl.
  • The BCS National Championship is now called the College Football Playoff (CFP) National Championship, as we enter the CFP era. Bye bye, BCS.
Here are the files:

For those unfamiliar with the two-file system, the idea is that pool managers can distribute the 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.

Note: Since I wasn't sure how you folks would be dealing with the fact that we don't currently know which teams will be playing in the CFP Championship, I've made it so that you can easily edit those teams later. Some of you may prefer to have everyone pick a winner for that game upfront, filling in the teams playing with their two picks for the initial two playoff games. Some of you may opt to have folks send in their picks for that game later, once the teams are known. It's up to you, I guess.

Another note: I don't have the final record for the Navy team, since they play in the annual Army-Navy game this weekend. I've also left that cell unprotected so you can easily update the record for the Midshipmen.

Finally, I don't yet have my personal picks completed, but will add them, as well as those for my wife (MRS. EXCEL_GEEK) and son (EXCEL_GEEK, JR.) and update the files once we're ready. Some of you have voiced a preference to have those picks included, to give you a benchmark for your own picks (and to see me lose each year to my wife.)

Enjoy, everyone!

Tuesday, August 19, 2014

2014-15 NFL Pick 'Em Pool

For those of you who've been eagerly anticipating this year's rendition of my NFL Pick 'Em Pool, it's ready to roll!

I've baked in a few notable enhancements:

  • I've rearranged some things to make it easier to copy-paste from the picks area of the sheet or to select large ranges and delete to clear out picks. Before I had some hidden and protected columns in between each of the game columns that made this not possible.
  • I added in conditional formatting that automatically formats the top Home and Visitor team cells to match the team colors of the teams. Snazzy! I got this idea from a past user of my spreadsheet who'd implemented the color schemes himself, though not with the conditional formatting.
  • I've also included a companion worksheet to the one that the pool administrators will use -- one that participants can simply pick the week, and then make their picks, and then send them to the administrator, who can now copy and paste them easily into the "master" sheet.
Here are links to the two files:

Pool Administrator "Master" sheet

Pool Participant "Weekly Picks" sheet

Enjoy everyone!


Monday, July 07, 2014

Updated Again: Parse Google Search Results for First URL

This is pretty much a regurgitation of my post from back in February of 2011. Google slightly changed how they build their search results pages again sometime between then and now, and I had a new Insiders subscriber sign-up looking for that very solution. When I checked to see if the file still worked, it did not, so I made a few tweaks to get it running again.

Here's a copy-paste of the last post on this topic:

Those of you who've been following me for some time may know that I've done quite a few projects related to parsing Google search results for various data -- URLs, number of results, etc. Those of you using the code likely know that some time ago Google apparently made some changes to how they put together their site causing my stuff to stop working. Well, recently one of the requesters of one of these projects - one that returns the first non-paid URL returned - came back and had me update it to work with how Google's site currently displays search results.

This time I made it into a user-defined function. You simply enter the function...

=getFirstGoogleResultURL(A2), where the value of cell A2 is the search term.

It works pretty slick, but know that Google will not allow this sort of thing to be used on large volumes of searches. I'm pretty sure they'd much rather have you using their APIs for this sort of usage.

Excel_Geek Insiders subscribers, your copy of this file is on its way.



Tuesday, March 11, 2014

2014 March Madness Bracket -- can you wait a week?

Beloved readers,

You've loyally come back to year after year to get what's become probably my most widely circulated spreadsheets -- the March Madness brackets.

I have bad news, folks. Well, bad news for you, mostly. You see, I'll be -- shall we say -- unable to connect to the Internet due to being unplugged in a beautiful place over Selection Sunday this year. I won't be staying up late that Sunday, adding in this year's teams and accounting for the NCAA's maddening random placement of the so-called play-in games to deliver you a fresh, clean, all-ready-to-go set of spreadsheets.

I'm sorry.

I'll be back, though, and I do plan to deliver my files, albeit only after some games will have been played, however. So....if you'd like to have your pool participants deliver their picks to you in some other format, and then have them resubmit later, once you've got my handy files, I will have them posted when I'm back.

Again, many apologies for my untimely absence from the Internet.



Monday, December 09, 2013

The 2013-14 College Football Bowl Pool Spreadsheet is here!

Sorry it took me a bit longer than normal to get this out. I usually stay up on Bowl Selection Sunday and get this delivered.

This year, thankfully, there were no material changes to the bowl game line-up. We still have 35 games. Three of the games did get new sponsors, though:

  • The former MAACO Bowl Las Vegas becomes the Royal Purple Las Vegas Bowl.
  • The former Bridgepoint Education Holiday Bowl becomes the National University Holiday Bowl.
  • The former Discover BCS National Champtionship becomes the VIZIO BCS National Championship.
As usual there are two files. The first is the "Summary" -- this is the one that pool organizers use to track how everyone's picks are doing vs. actual results. the second is the "Entry Form" -- this is the file that pool organizers send to their participants for them to make their picks and send it back. Pool organizers, remember to use Copy - Paste Special... Values when copy-pasting people's picks in to the Summary.

Here are your files:


UPDATED: I received feedback from a number of you today that my NOT initially including the traditional picks and points for me (Excel_Geek) and my wife (Mrs. Excel_Geek) and the recently added Excel_Geek Jr. was UNACCEPTABLE. It turns out many of you like to gauge how you're doing by comparing your picks to ours. Also, some of you apparently find it funny that I rarely do better than my wife (or even last year my son.)

The Summary form now has all three of our picks and points included. Do please enjoy!