It’s been a bit of a family tradition to do a football pool with every World Cup and EuroCup. This year my brother didn’t have the time to take it unto himself to organize the entire pool, so I offered to do it instead. Last Friday I got everybody’s predictions and I realized what a pain it was going to be to update everything by hand…

This weekend was HackCamp though, so I figured I could just as well turn my family’s football pool into my hack for the two days. I started with the idea to make an actual site where every family member could log in and check their scores. After messing around with GWT and Google App Engine for a couple of hours, I felt a bit overwhelmed and tried to look for a simpler approach.

I turned to Google docs’ spreadsheets. The tricky bit was to calculate all the scores correctly. See how our family plays the pool is to make predictions for all the matches in the first round; you get 3 points if you get the result right (draw, win, loose), but an extra 7 points for the exact score. Then predictions for who goes through in the second round, but extra points if you predict the right spot (for instance A1 and A2). Then, points for correctly predicting who goes through to the quarterfinal, semifinal and final and finally the winner. Then I also wanted a graph to show some more info per game:

I won’t go through every single step, but you can check the sheet how I did it all. Yeah, it is ‘just’ a spreadsheet, but turns out you can do a couple of interesting things with it, and it was a perfect solution for my family’s pool. For starters, I can generate charts based on the data and embed them elsewhere, like here on my blog:

This chart shows how many points every person in the pool has made, with each game in it’s own colour (for instance, gray: both Mum and Matt predicted correctly that it would be draw between England and USA). If you look at the chart within Google docs (Chart 1), you can even click on each bar and see which game it is.

Here you can see per game who scored what (and which games no one predicted correctly). The one thing I’m still trying to get working is having the scores automatically updated, without me having to touch anything at all (at the moment the one thing I have to do is enter the score per played game). I got it almost working! Surprisingly I couldn’t find any simple RSS feed or XML with only the score data in it. I found a Twitter account that updates the scores during the game, put it through a Yahoo Pipe to strip out only the data I wanted. Problem right now is getting that Pipe to show the data correctly (it seems like every change I make in the pipe doesn’t show properly until a new item shows up). Once that works though, it shouldn’t be too difficult to import the data into the spreadsheet.

So I entered my ‘hack’ for the HackDay yesterday and I won a prize! I got a cool Duracell USB battery with which I can charge all my gadgets on the go. Even though I’ve been to a lot of hackdays, this is the first time I’ve actually made something. I know it’s just a spreadsheet and not the most impressive thing I could have done, but it works for what it was meant for.

Not sure if anybody might be up for it, but I’ve created another version of the spreadsheet, so that my blog/Twitter followers can play along too! Just fill in your predictions for Round 1, Round 2 and the Quarter/Semi/Finals and the rest will be taken care of.  (Or if you want to play with your own friends, I think you can make a copy from within Google docs).

Tags: Events