“How do I calculate an age in Excel?”Marion Urban, a French journalist and student on the MA in Online Journalism in Birmingham, was preparing data for the forthcoming UK General Election.
In order to do this Marion had downloaded details on the candidates who had stood successfully in the previous election.
“It was a very young intake. But it wasn’t easy to calculate their ages.”
Indeed. You would think that calculating ages in Excel would be easy. But there is no off-the-shelf function to help you do so. Or at least, no easy-to-find function.
Instead there are a range of different approaches: some of them particularly, and unnecessarily complicated.
In this extract from Finding Stories in Spreadsheets I will outline one approach to calculating ages, which also illustrates a useful technique in using spreadsheets in stories: the ability to break down a problem into different parts. Continue reading →
My latest ebook – Finding Stories in Spreadsheets – is now live on Leanpub.
As with Scraping for Journalists, I’m publishing the book week-by-week so the book can be updated based on reader feedback, user suggestions and topical developments.
Each week you can download a new chapter covering a different technique for finding stories, from calculating proportions and changes, to combining data, cleaning it up, testing it, and extracting specific details.
There’s also a downloadable spreadsheet at the end of each chapter with a series of exercises to practise that chapter’s technique and find particular stories.
Along the way I tackle some other considerations in telling the story, such as context and background, and the importance of being specific in the language that you use.
The book has been written in response to requests from journalists who need a book on Excel aimed at storytellers, not accountants.
Finding Stories In Spreadsheets will outline a range of techniques, including ways to find the ‘needle in the haystack’ in text data, number calculations to make stories clearer, and methods of cleaning and combining data to tell new stories, including getting data ready for maps and charts.
Sometimes the most impressive tools solve a problem you never knew you had. In the case of QueryTree, a new data analysis tool, that problem is something most people never question: spreadsheets.
For all the shiny-shiny copy-and-paste-click-and-drag-ness in new journalism tools, most data digging comes back to at least some simple spreadsheet work, and that represents a significant hurdle for many journalists used to working with simpler tools.
While interface design has undergone generations of improvement on the web, spreadsheet software interfaces have remained largely unchanged for decades.
Here’s an example of how APIs can be useful to journalists when they need to combine two sets of data.
I recently spoke to Lincoln investigative journalism student Sean McGrath who had obtained some information via FOI that he needed to combine with other data to answer a question (sorry to be so cryptic).
He had spent 3 days cleaning up the data and manually adding postcodes to it. This seemed a good example where using an API might cut down your work considerably, and so in this post I explain how you make a start on the same problem in less than an hour using Excel, Google Refine and the Google Maps API.
Step 1: Get the data in the right format to work with an API
APIs can do all sorts of things, but one of the things they do which is particularly useful for journalists is answer questions. Continue reading →