My dad, brother-in-law's, and I are in a college football bowl game pick'em with over 180 other money-hungry football fanatics. The odds are low and the stakes are big. It's been a lot of fun to play the past 5 years, except there's one major downfall of running a remote pick'em contest: Excel.
The spreadsheet itself is a masterpiece, updating your points and ranking in real-time. Tracking the results, however, is a different story.
See all of those empty yellow boxes? Yeah, those are for me to fill in. Tedious is an understatement. 40 college football bowl games, over two weeks, interspersed with the holiday season and most chaotic time of the year. It's a lot to track manually, but when there's over a thousand dollars at stake, you do what you've got to do.
This year, however, I decided to do it differently.
Bowl season was quickly underway. Hours before kickoff, we were all emailed the master spreadsheet of everyones bowl game winner picks, along with the formulas to track real-time rank and points earned. I loaded the file into Google Sheets. Step 1, check. Next, I needed to know if there was a way to get college football game results in real-time. Thanks to some really basic search engine queries, I found CollegeFootballData.com (woo!), and began one of my first solo ventures of configuring an API Import into Parabola. No sweat.
The API returned 40 rows with 20 columns of data. For this project, I only needed 4 columns, but more on the others later. By putting the API through a column filter, I was left with just Home Team, Home Team Points, Away Team, and Away Team Points. I used a super simple If/Else step to designate a Winner column (if Home Team Points > Away Team Points, Winner is Home Team, else Winner is Away Team). Very simple logic.
Then I ran into my first problem. The API Import did not include the Bowl Game's actual name, which I planned to use as my matching criteria for filling in the winner. I found an easy fix on the web: a list of bowl game names and their participating college teams. Now, I was able to join the API Import of home and away teams to the list of bowl games. A column filter removed some extraneous data points, leaving me with a simple two column result: Bowl Game and Winner.
I setup the flow to export back to my Google Sheet, where all of the bowl game picks lived. Using a common formula, I matched the bowl game names and populated the winning school in those little yellow boxes I pointed out earlier.
Setting my Parabola flow to run every 10 minutes meant that I had a near-perfect real-time running app updating my spreadsheet for me. Take that ESPN! It felt like magic.
A few side notes:
First, I did pretty poorly in the end, with no hope of bringing home any winnings.
Secondly, CollegeFootballData.com provided a win probability percentage for each matchup. I ignorantly overlooked it, and I wish I didn't. A quick Parabola flow tells me that had I gone with their projected winner in each matchup, I would've been 84.6% accurate, instead of my abysmal 66.67% accuracy. Oh, and I would have also been rooting for an LSU victory on Monday night in the National Championship, with a mere $1,400 on the line.
Third, if you're a fan of either LSU or Clemson, know that I now have no rooting interest. If LSU wins, my brother-in-law's dad finishes in 2nd place. If Clemson wins, my brother-in-law finishes in 3rd. Both could get some cash out of it. I won't finish higher than in a massive tie for 40th place. No cash. Not even close.
Lastly, CollegeFootballData also brings in real-time betting information, with lines and over/unders from Bovada, Caesars, and more. I'm not a sports bettor, but I imagine this would be very useful. With all of this power (you can try out my flow by clicking here), what could you build in Parabola?