Getting Started With Local Council Spending Data

With more and more councils doing as they were told and opening up their spending data in the name of transparency, it’s maybe worth a quick review of how the data is currently being made available.

To start with, I’m going to consider the Isle of Wight Council’s data, which was opened up earlier this week. The first data release can be found (though not easily?!) as a pair of Excel spreadsheets, both of which are just over 1 MB large, at http://www.iwight.com/council/transparency/ (This URL reminds me that it might be time to review my post on “Top Level” URL Conventions in Local Council Open Data Websites!)

The data has also been released via Spikes Cavell at Spotlight on Spend: Isle of Wight.

The Spotlight on Spend site offers a hierarchical table based view of the data; value add comes from the ability to compare spend with national averages and that of other councils. Links are also provided to monthly datasets available as a CSV download.

Uploading these datasets to Google Fusion tables shows the following columns are included in the CSV files available from Spotlight on Spend (click through the image to see the data):

Note that the Expense Area column appears to be empty, and “clumped” transaction dates use? Also note that each row, column and cell is commentable upon…

The Excel spreadsheets on the Isle of Wight Council website are a little more complete – here’s the data in Google Fusion tables again (click through the image to see the data):

(It would maybe worth comparing these columns with those identified as Mandatory or Desirable in the Local Spending Data Guidance? A comparison with the format the esd use for their Linked Data cross-council local spending data demo might also be interesting?)

Note that because the Excel files on the Isle of Wight Council were larger than the 1MB size limit on XLS spreadsheet uploads to Google Fusion Tables, I had to open the spreadsheets in Excel and then export them as CSV documents. (Google Fusion Tables accepts CSV uploads for files up to 100MB.) So if you’re writing an open data sabotage manual, this maybe something worth bearing in mind (i.e. publish data in very large Excel spreadsheets)!

It’s also worth noting that if different councils use similar column headings and CSV file formats, and include a column stating the name of the council, it should be trivial to upload all their data to a common Google Fusion Table allowing comparisons to be made across councils, contractors with similar names to be identified across councils, and so on… (i.e. Google Fusion tables would probably let you do as much as Spotlight on Spend, though in a rather clunkier interface… but then again, I think there is a fusion table API…?;-)

Although the data hasn’t appeared there yet, I’m sure it won’t be long before it’s made available on OpenlyLocal:

However, the Isle of Wight’s hyperlocal news site, Ventnorblog teamed up with a local developer to revise Adrian Short’s Armchair Auditor code and released the OnTheWIght Armchair Auditor site:

So that’s a round up of where the data is, and how it’s presented. If I get a chance, the next step is to:
- compare the offerings with each other in more detail, e.g. the columns each view provides;
- compare the offerings with the guidance on release of council spending data;
- see what interesting Google Fusion table views we can come up with as “top level” reports on the Isle of Wight data;
- explore the extent to which Google Fusion Tables can be used to aggregate and compare data from across different councils.

PS related – Nodalities blog: Linked Spending Data – How and Why Bother Pt2

PPS for a list of local councils and the data they have released, see Guardian datastore: Local council spending over £500, OpenlyLocal Council Spending Dashboard


While you’re waiting for Yahoo! to make its mind up about Delicious, sign up to Trunk.ly

Despite the incredible work done on the spreadsheet comparing social bookmarking services I am yet to find one that does everything that I use Delicious for (background here). One service I have been using, however, is Trunk.ly.

Once you’ve imported your existing bookmarks from Delicious Trunk.ly stores any new ones you bookmark on Delicious, keeping the backup up to date. In addition it can store any links you’ve shared on Twitter, Facebook, Google Reader and any RSS feed.

It is essentially a search engine for links you may have shared at some point – but its technical limitations stop it from being much more. For example, there do not appear to be any RSS feeds for tags*, and there is no facility to combine tags to find items that are, for example, tagged with ‘privacy’ and ‘tools’. (It would also be nice if it tagged links shared on Twitter with any hashtags in the tweet)

That said if, like me, you want to continue using Delicious but with an ongoing backup in case, Trunk.ly appears a sound choice. And it’s early days, so here’s hoping they add those features soon… *cough*.

*Planned apparently. See Trunk.ly in the comments below.

Social blogmarking

How is this different to Stumbleupon or even Digg? I guess it comes down to smaller, but more dedicated communities of users, and a limit to blog posts only. Worth a play.

Investigations tool DocumentCloud goes public (PS: documents drive traffic)

The rather lovely DocumentCloud – a tool that allows journalists to share, annotate, connect and organise documents – has finally emerged from its closet and made itself available to public searches.

This means that anyone can now search the powerful database (some tips here) of newsworthy documents. If you want to add your own, however, you still need approval.

If you do end up on this list you’ll find it’s quite a powerful tool, with quick conversion of PDFs into text files, analytic tools and semantic tagging (so you can connect all documents with a particular person, or organisation) among its best features. The site is open source and has an API too.

I asked Program Director Amanda B Hickman what she’s learned on the project so far. Her response suggests that documents have a particular appeal for online readers:

“If we’ve learned anything, it is that people really love documents. It is pretty clear that when there’s something interesting going on in the news, plenty of people want to dig a little deeper. When Arizona Republic posted an annotated version of that state’s new immigration law, it got more traffic than their weekly entertainment round up. WNYC told us that the page listing the indictments in last week’s mob roundup was still getting more traffic than any other single news story even a week later.

“These were big news documents, to be sure, but it still seems pretty clear that people do want to dig deeper and explore the documents behind the news, which is great for us and great for news.”

Content, context and code: verifying information online

Content Context and Code - Verifying Information online

When the telephone first entered the newsroom journalists were sceptical. “How can we be sure that the person at the other end is who they say they are?” The question seems odd now, because we have become so used to phone technology that we barely think of it as technology at all – and there are a range of techniques we use, almost unconsciously, to verify what the person on the other end of the phone is saying, from their tone of voice, to the number they are ringing from, and the information they are providing.

Dealing with online sources is no different. How do you know the source is telling the truth? You’re a journalist, for god’s sake: it’s your job to find out.

In many ways the internet gives us extra tools to verify information – certainly more than the phone ever did. The apparent ‘facelessness’ of the medium is misleading: every piece of information, and every person, leaves a trail of data that you can use to build a picture of its reliability.

The following is a three-level approach to verification: starting with the content itself, moving on to the context surrounding it; and finishing with the technical information underlying it. Most of the techniques outlined take very little time at all but the key thing is to look for warning signs and follow those up. Read more

Sri Lanka war crimes and the future of international journalism

Here’s a quick thought about a problem of international reporting: sources. Your viewers and readers are in your country, while your sources are largely not (there are exceptions such as CNN or the BBC, but humour me).

In order to make contact with the people and evidence who can help answer your questions, you have to rely far more on your personal network than, for example, a home affairs or education correspondent.

But the globalisation of modern news – and the ability of people to search on the internet for information related to their own experiences – has changed this. Now, if you report on an issue in another country, people in that country can see what you’ve written and contact you with further information.

In a nutshell this reflects the way that journalism has moved from a ‘push’ medium limited by transmission and distribution infrastructure, to a ‘pull’ (search) and ‘pass’ (social media) one.

Three particularly strong examples of this: Channel 4′s ongoing reporting on the civil war in Sri Lanka and evidence of war crimes. Video footage that was obtained as part of that journalism was, eventually, seen by someone who recognised one of the bodies. (A particularly good lesson for budding journalists is how photos of those bodies were dated using EXIF data, and correlated with documentary evidence from the Sri Lankan MOD - material that don’t lend themselves to broadcast, but can be put online)

Second, Paul Lewis’ investigation into the death of a man being deported to Angola. One of the passengers on the plane where he died was a US citizen who works in Angola. He contacted Lewis after coming across a tweet calling for witnesses.

Third, Paul Lewis again, and the death of Ian Tomlinson at G20 protests. This was again provided by a US citizen who happened to be in the UK at the time and came across the story after he returned home.

Curiously, of course, these two latter stories are not examples of international journalism in terms of their subject – but they do highlight how the web can make international newsgathering part of home affairs stories too.

A market for data called…

Datamarket.com – it’s just gone live. Looks interesting…

Hyperlocal voices interviewed elsewhere

While I’ve been blogging my series of interviews with hyperlocal bloggers I’ve come across a few more elsewhere that may be of interest – and thought it worth linking to them here.

Talk About Local is running a ‘Ten Questions’ series of interviews along the same lines.

Nick Booth of Podnosh (which I work for) is writing a blog about hyperlocal bloggers on the BBC website. He has also  interviewed Steph Jennings and James Clark of WV11 – audio embedded below:

Audio clip: Adobe Flash Player (version 9 or above) is required to play this audio clip. Download the latest version here. You also need to have JavaScript enabled in your browser.

I recently had Ventnor Blog founder Simon Perry talk to students on the MA in Online Journalism that I teach at Birmingham City University. Samuel Negredo filmed part of his visit, which can be seen on his blog post about the visit and is also embedded below:

Also interviewed elsewhere – by Philip John – is Brownhills Bob.

Lara O’Reilly interviews Dave Lee about Olympic Borough.

And in the US, Bob Yoder of the Redmond Neighborhood Blog is interviewed by Outside.in, which also gets some tips from Elllie Ashford in Annandale.

Organising your journalism: Springpad

For the last couple of weeks I’ve been playing with a new web service and mobile app called Springpad. LifeHacker describes it as a “super advanced personal assistant”. And I can see particular applications for journalists and editors. Here’s how it works:

Investigating on the move, and online

In Springpad you create a ‘notebook’ for each of your projects. You can then place Tasks, Notes, bookmarks and other objects in those notebooks.

For a journalist, the notebook format lends itself well to projects or investigations that you’re working on, especially as ideas occur to you on the move. As new tasks occur to you (‘I must interview that guy’, or ‘follow up that lead’) you add them to the relevant notebook (i.e. project or investigation) from the mobile app – or the website.

If you’re browsing the web and find a useful resource, you can use the Springpad bookmarklet to bookmark it, tag it, and add it to the relevant notebook(s).

And any emails or documents you receive that relate to the project you can forward to your Springpad account.

What’s particularly useful is the way you can choose to make public entire notebooks or individual items within them. So if you want others to be able to access your work, you can do so easily.

There are also a range of other features – such as events, contacts, barcode recognition, search, and a Chrome bookmarklet – some of which are covered in this video:

How I use it

Springpad seems to me a particularly individually-oriented tool rather than something that could be used for coordinating large groups (where Basecamp, for example, is better). None of its constituent elements – tagging, to-do lists, notes, etc. – are unusual, but it’s the combination, and the mobile application, that works particularly well.

If you have a number of projects on the go at any one time you tend to have to a) constantly remember what needs to be done on each of them; b) when; c) with whom; and d) keep track of documents relating to it. The management of these is often spread across To Do lists, a calendar, contacts book, and filing or bookmarks.

What Springpad effectively does is bring those together to one place on your mobile: the app (although at the moment there’s no real reason to use it for contacts). This means you can make notes when they occur to you, and in one place. The fact that this is both synced with the website and available on the app when offline gives it certain advantages over other approaches.

That said, I’ve adopted a few strategies that make it more useful:

Not a replacement for Delicious

You can import all of your Delicious bookmarks into Springpad, but I’ve chosen not to, partly because the site lacks much of the functionality that I’m looking for in a Delicious replacement, but also because I see it as performing a different task: I use Delicious as a catch-all, public filing system for anything that is or might be relevant to what I do and have done. Springpad is about managing what I’m doing right now, which means being more selective about the bookmarks that I save in it. Flooding it with almost 10,000 bookmarks would probably reduce its usefulness.

For the same reason I don’t see it as particularly comparable to Evernote. Dan Gold has an extensive guide explaining why he switched from Evernote to Springpad, and simplicity again plays a large role. It’s also worth reading to see how Dan uses the tool.

Perhaps the best description of the tool is as a powerful To Do list – allowing you to split projects apart while also keeping those parts linked to other items through notes, tags and categories.

Early days – room for improvement

The tool is a bit rough around the edges at the moment. Navigation of the app could be a lot quicker: to get from a list of all Tasks to those within one notebook takes 3 clicks at the moment – that’s too many.

Privacy could be more granular, allowing password-protection for instance. And the options to add contacts and events seem to be hidden away under ‘Add by type’ (in fact, the only way to add an event at the moment appears to be to sync with your Google account and then use a calendar app to add a new event through your Google calendar, or to go to an existing event in your app and create a new one from there).

The bookmarklet is slow to work, and alerts only come via RSS feed (you could use Feedburner to turn these into email alerts by the way).

That said, this is the first project management that I’ve actually found effective in getting stuff out of my head and onto virtual paper. Long may that continue.

Universities without walls

@majohns Economist believes in future their distinguished and knowledgable audience is as important as their editors #smart_2011

This post forms part of the Carnival of Journalism, whose theme this month is universities’ roles in their local community.

In traditional journalism the concept of community is a broad one, typically used when the speaker really means ‘audience’, or ‘market’.

In a networked age, however, a community is an asset: it is a much more significant source of information than in other media; an active producer of content; and, perhaps most importantly, at the heart of any online distribution system.

You can see this at work in some of the most successful content startups of the internet era – Boing Boing, The Huffington Post, Slashdot – and even in mainstream outlets such as The Guardian, with, for example, its productive community around the Data Blog.

Any fledgling online journalism operation which is not based on a distinct community is, to my thinking, simply inefficient – and any journalism course that features an online element should be built on communities – should be linking in to the communities that surround it.

Teaching community-driven journalism

My own experience is that leaving the walls of academia behind and hosting classes wherever the community meets can make an enormous difference. In my MA in Online Journalism at Birmingham City University, for example, the very first week is not about newsgathering or blogging or anything to do with content: it’s about community, and identifying which one the students are going to serve.

To that end students spend their induction week attending the local Social Media Cafe, meeting local bloggers and understanding that particular community (one of whom this year suggested the idea that led to Birmingham Budget Cuts). We hold open classes in a city centre coffee shop so that people from Birmingham can drop in: when we talked about online journalism and the law, there were bloggers, former newspaper editors, and a photographer whose contributions turned the event into something unlike anything you’d see in a classroom.

And students are sent out to explore the community as part of learning about blogging, or encouraged to base themselves physically in the communities they serve. Andy Brightwell and Jon Hickman’s hyperlocal Grounds blog is a good example, run out of another city centre coffee shop in their patch.

In my online journalism classes at City University in London, meanwhile (which are sadly too big to fit in a coffee shop) I ask students to put together a community strategy as one of their two assignments. The idea is to get them to think about how they can produce better journalism – that is also more widely read – by thinking explicitly about how to involve a community in its production.

Community isn’t a postcode

But I’ve also come to believe that we should be as flexible as possible about what we mean by community. The traditional approach has been to assign students to geographical patches – a relic of the commercial imperatives behind print production. Some courses are adapting this to smaller, hyperlocal, patches for their online assessment to keep up with contemporary developments. This is great – but I think it risks missing something else.

One moment that brought this home to me was when – in that very first week – I asked the students what they thought made a community. The response that stuck in my mind most was Alex Gamela‘s: “An enemy”. It illustrates how communities are created by so many things other than location (You could also add “a cause”, “a shared experience”, “a profession”, “a hobby” and others which are listed and explored in the Community part of the BASIC Principles of Online Journalism).

As journalism departments we are particularly weak in seeing community in those terms. One of the reasons Birmingham Budget Cuts is such a great example of community-driven journalism is that it addresses a community of various types: one of location, of profession, and of shared experience and – for the thousands facing redundancy – cause too. It is not your typical hyperlocal blog, but who would argue it does not have a strong proposition at its core?

There’s a further step, too, which requires particular boldness on the part of journalism schools, and innovativeness in assessment methods: we need to be prepared for students to create sites where they don’t create any journalism themselves at all. Instead, they facilitate its production, and host the platform that enables it to happen. In online journalism we might call this a community manager role – which will raise the inevitable questions of ‘Is It Journalism?’ But in traditional journalism, with the journalism being produced by reporters, a very similar role would simply be called being an editor.

PS: I spoke about this theme in Amsterdam last September as part of a presentation on ‘A Journalism Curriculum for the 21st Century’ at the PICNIC festival, organised by the European Journalism Centre. This is embedded below: