How to run an automated sports pool in a spreadsheet
Spreadsheets let you do a lot with very little technical ability. This example happens to be a fairly simple Masters pool, but most of these tips/tricks can be used for other sports — NHL & NBA playoffs start soon! — or for doing your own own data analysis, running your own league standings, etc.
The Basics
Setting the Rules & Making Picks
There are some different ways you could run the selection and scoring process (search for “golf pool” or “Masters pool”) but a golf pool doesn’t really need to be complicated: pick a bunch of players and whoever’s players get best scores wins.
The format we settled on for this pool was to each draft 7 players, from which we’d only count the top 4 scores. There were only two of us, and neither of us is a PGA expert, so we had some fun and mixed in a lot of sentimental, half-serious picks. (We also did this very last-minute—Thursday night, actually—so we had the benefit of picking some people who were already playing well.)
We listed our picks in a very basic Google spreadsheet. I manually entered each of the players’ scores, and used SUM formulas to add them up. Easy enough so far…
Adding More Functionality
Importing Live Scores
As simple as the initial spreadsheet was, I didn’t feel like manually updating scores all weekend. Luckily Google Sheets has a bunch of IMPORT functions that make it fairly easy to pull data in from other sources. Once the live data is in the spreadsheet you can do all kinds of creative stuff.
The IMPORTHTML function is what I used here. It might require a little trial-and-error to find a good source, but once you find one it’s an easy way to pull data from another website. In this case the URL I used was ESPN.com’s Masters leaderboard. You also need to know whether you want to pull data from a table or list on the page. In this case it’s a table. Lastly, you need a numeric reference to the table or list you want to pull from (don’t overthink this one; it’s just a number, which you can guess).
Just type the IMPORTHTML formula with those three pieces of information — “http://www.espn.com/golf/leaderboard”, “table”, 1—into a single cell and a whole table or list should magically appear.
Looking Up Individual Player Scores
The main thing I wanted to do was use the live data to update the scores for each of our picks. The most common way to do this is the VLOOKUP function. VLOOKUP is one of those things that involves a little more thinking but comes in handy once you get the hang of it. (More advanced users running lookups in large tables can combine INDEX and MATCH for more flexibility and faster processing.)
If you’re a spreadsheet novice I encourage you to take full advantage of the tooltips and help available in Google Sheets, and don’t forget to copy or autofill it to the other cells.
The VLOOKUP formula above is basically saying:
- I want to find the value of cell B3 (“Lee WestwoodL. Westwood”) from within a range of cells…
- The range I want to look in includes the “Player” and “To Par” columns of the data we imported from ESPN.com (in this example that is Data!D2:E189)—to which we add dollar signs (Data!$D$2:$E$189) to keep it from changing when we copy or autofill it to the other cells.
- I want to return the value from 2nd column of that range (which in the example above is Lee Westwood’s “To Par” score of -1). FALSE means that we’re looking for an exact match (don’t overthink this).
You probably noticed that I had to change the players’ names to the weird format that appears in ESPN’s live data import. That’s easy to change, along with some other things we need to clean up to make the data more accurate and usable.
Cleaning Up the Data
The biggest issue so far is that the scores aren’t adding up. This is because a lot of ESPN’s data is actually text, so math formulas skip right over it. Specifically, we want to get rid of the “+” sign for over-par scores and change even scores from “E” to the number zero.
It’s often a good idea to clean up data before doing anything else, but in this case I just focused on the data I was using. The SUBSTITUTE function got rid of the “+” signs and changed “E” to a zero. It took a little trial-and-error to figure out how to make multiple substitutions using a nested formula. Even then, the numbers were still coming out as text, so I added a VALUE function to convert the scores to numeric values.
For the player names I simply added new columns and typed them the way I wanted them to appear in our final results, which we’ll get to shortly…
Sorting and Adding the Top Scores
Remember we only wanted to count the top 4 scores from each team. If we were updating the spreadsheet manually we could select the data we want to sort, click Filter in the Data menu and sort each table. But we’d have to keep doing that every time the scores changed. Fortunately, setting it up to sort automatically is just as easy using (you guessed it) the SORT function.
The SORT function creates a copy of the designated range/table (e.g. B3:E9 for Team A) and sorts it by whichever column you indicate (e.g. 4 for the numeric scores in column E). TRUE tells it to sort in ascending order, from lowest to highest (which in golf is best to worst).
Once the scores are in order you can use a couple of good ol’ SUM formulas to add up the top 4 scores for each team. I did this by adding new columns and merging cells (select the cells you want to merge and select Merge from the Format menu).
Making it User-Friendly
What we have so far technically does everything we need but it doesn’t look great. It only takes a few minutes of extra work to create a scoreboard that’s actually somewhat ok to look at—and adds a layer of protection over the underlying calculations.
It’s generally good practice to separate your scoreboard or “Results” view from the underlying operations and data tables. It gives you a lot more flexibility to play with the the visual design while keeping the Calculations and Data logical and usable in a utilitarian way.
Notice we’re only showing the normal player names in our Results tab, and the “+” signs and “E”s have reappeared. That’s because the calculations have already been made in another tab, so we’re free to show scores in text format here.
For spreadsheet beginners, if you want cell C6 in the Results tab to display whatever is in cell C13 in the Calculations tab, click on C6 and type “=Calculations!13” — or just type “=” and click on cell C13 in Calculations.
A lot more could be said about formatting and visual design, but this example should be pretty straightforward. Visual spreadsheet design might be a topic for a future post.
Finally, don’t forget to lock the important sheets! Right-click on the tab you want to lock down and choose Protect Sheet. Google Sheets let’s you prevent or warn certain people (including yourself) from making any changes—just like it lets you decide who gets access to view it.
Check out the live spreadsheet here: https://docs.google.com/spreadsheets/d/1Mg_3MYJUdFFWTGDp-loZyU2HszQlKyyaXdQmR_D-o-k/edit?usp=sharing
[Update: I’ve had some requests for access to the spreadsheet. If you have a Google account you should (I think…) be able to make your own copy in the File menu. If not, request access and I’m happy to make a copy for you.]
