The Movie Evangelist

I Heart Movie Spreadsheets

Posted on November 26, 2010

Note to readers: if reading this blog in your head, or indeed out loud, you will get the most benefit from this post if your brain affects one of those nasal, John Major-style nerdy accents that signify the ramblings of a true geek. For we are about to get very geeky indeed.

At some point in the last three years, my movie going transcended from casual addiction to full-blown OCD. As with any good tradesmen, the movie obsessive-compulsive needs his tools, and in this age of t’internets and apps it’s easy to use technology to support that habit. I’ve used a few tools to effectively supplement my rampage through the finest picture houses of East Anglia and the surrounding countryside, but something that’s been an invaluable assistant for me for a few years now has been the humble spreadsheet.

I, like most other people, fall back on the unfortunately ubiquitous Microsoft Excel (other spreadsheet packages are available), and have used it in a number of ways over the years to support my habit. I’ll share a couple of those with you now, and hopefully they will inspire you, my faithful readership, to your own ways of advancing your own movie attendance to as yet unprecedented levels. (Or you can

I do use a number of online tools, such as the list functionality of My Movies at IMDb and also the Flixster element of Facebook, to keep track of what I’ve seen. But spreadsheets allow me to take that a stage further. For example, I downloaded my IMDb list for this year, and off the back of it ran up a quick pivot table of all of the movies I’d seen this year, up to the end of October, a total of 110. This allowed me to produce the following analysis on my ratings:

Firstly, this graph showing how many movies I’ve given each rating to – so no 1/10 movies (yet) this year, and a roughly normal distribution. Also plotted against this is the IMDb average score for those movies, so you can see that my views are, on average, roughly a reflection of the average moviegoer, although I do appear to have a soft spot for some 4/10 showings that others weren’t so sympathetic to. Thrilling, I’m sure you’ll agree. Even less more interesting is this:

Here I’ve used the pivot table functionality to compare the location of my cinema attendance against the language that I saw movies in while there. Oddly, while you can see I’ve gotten a fair bit of travel in to feed my habit this year, I’ve only seen foreign language movies in Cambridge, with the singular exception of Journey to Mecca at the IMAX which mixed up Ben Kingsley and some Arabic. Fascinating.

Still awake? Good. I’m sure in the fullness of time I will come up with many other ways to carve up that data, such as the number of amimated movies I’ve seen while wearing a scarf or the total distance I’ve travelled to see movies with cats in, all of which just continue to feed my obsession. But spreadsheets not only allow me to dissect my habit to the finest level of detail – I have also regularly used them to make sure I maximise my viewing opportunities as well.

For you don’t see 118 movies in a little under eleven months by just popping in to the odd one or two. I’ve seen as many as seven in a day in previous years, although my record this year is just a humble four, but I have done that three times. But sometimes, navigating the long listings of the local cinemas, especially given that there are 31 screens in the five cinemas I most often frequent, proves a challenge in getting the most films in during the time I have available. So any time I want to have a cinema session, I break out the spreadsheet:

This is a sample planner that I whipped up a couple of weeks ago to help plan a day’s cinema visits. I had from the start of the day until I met  my wife at around 9:30 p.m. to get as many in as possible, and so picked out the five movies I was most interested in. You’ll see that there are columns to enter the name, start time and duration of the film, which I tapped in manually, then also the likely duration of the trailers and ads. Two reasons for this: it does vary from cinema to cinema, and if you’re engaged in a marathon, you don’t want to sit through the same ads over and over.

The next two columns are formulas, to then work out when the actual movie starts and finishes, and then the final column at the beginning is a drop-down selector to allow me to toggle on and off whether I’m seeing the movie or not. Then, reading across, you’ll see I’ve made five minute slots across the day. There are formulas in each cell that then graphically plot out the trailers (indicated by a T) and the movie itself (indicated by an M) of the movies themselves. This way, I can use the yes / no toggle on the left to show each movie graphically and then work out how to keep the gaps to a minimum. Using this, I got four in that day, and the four which were my first choices (Saw 3D being a back-up to fill time if I couldn’t make a decent list of the others in the time available).

I’d love to find the time to evolve this spreadsheet a bit. There’s a few things, such as travel time, it would be nice to add in to make sure I’m mapping the experience correctly, as I have to navigate from cinema to cinema as well as from screen to screen. I’d like to scrape the data directly from the cinema’s websites, rather than go through the tedious process of typing in start times and film names. And I’d also like to automate the selection process, getting Excel to work out suggested itineraries for me, rather than me using this to make manual guesses. All possible with my own Excel and VBA skills, it’s just a case of having the time.

Just in case you’d find any of this useful, and if you are as much of a geek as me, then you have my sympathies. You are also welcome to have my spreadsheet, to do with what you will.

Movie Planner 06-Nov-2010

If you are as much of a geek as me, I’d love to know what you get up to with your spreadsheets. May the geeks rise up and take over the cinema!