Kickstarter Tracking & Historic currency conversion in Google Spreadsheets

Because I’m going to need this again at some point.

I’m currently tracking progress of my Kickstarted projects with a Google Drive Spreadsheet. This is partly because it tells me exactly how much money I’ve spent on KS projects over the last year, and helps me manage my new One In One Out system of dealing with them. But one of the issues with this is that most Kickstarters charge in dollars, and the CSV export reflects this, as does my record of how much I’ve spent, so I wanted to convert the numbers to sterling for stats. I could use the current rate, but while it fluctuates slowly, it does fluctuate, and I’d like this to be more long-term useful. Since I already have a completion date for each project – which is within a couple of days of when the money was taken – I can work it out using the GoogleFinance methods. This is how I’m doing that:

Given this:

Column E is worked out like this:

(Because it keeps breaking: That’s This GIST).

So eventually you can get graphs like this:

Which tells me that by straight count, 34.2% of the projects I’ve backed have delivered, a small number have failed, and most are in progress (“Delivered” means I have all the physical/digital stuff I paid for out of it. This is a personal rather than objective measure, so I’m counting the OOTS as having arrived because my main purpose was to get the books, the digital stuff Rich is still working on is very much an added bonus. So from my perspective it’s “done”. “Failed” means that the project was funded, but didn’t work out. Kickstart failures don’t get on this chart)

(If you want to copy the spreadsheet for your own purposes, you can clone it to your own Google Drive account from this blank version)

Current issues: If the final date wasn’t a trading day, the historical version falls back to today’s result, rather than the previous trading day. Also, I’d like to automatically pull in data from Kickstarter, but so far no API to do so 🙂