Mashing up electoral data – follow on from oUseful post

I just saw a great post by Tony Hirst on his oUseful blog where he nicely laid out how to use Open refine plus various APIs to research which candidates were standing for election in the wards in which they live. This kind of mashup of various tools is something I cannot resist playing around with, so I ‘borrowed’ his scraper wiki data to show how to do the same thing in one shot in Excel. I gave myself a time limit of 2 hours to see how much I could get done, including writing it up.

This is pretty straightforward since I already have all the raw material I need from previous posts concerning the rest/Excel library. I’ve pushed it all in one module so the process is automated from start to finish,

First – the scraperwiki data. 

Tony has already created a database which i’m going to pick up using a scraperwiki query – the same one I posted about on Excel Liberation some months ago. Here’s how to do that.

    worksheetName = “questionElection”
    scraperName = “iw_poll_notices_scrape”
    ‘ get data from Tony’s scraperwiki and populate sheet
    With scraperWikiStuff(scraperName, worksheetName)
        Set ds = New cDataSet
        ds.load worksheetName
    End With

Now we have an excel sheet with all that scraper wiki data loaded – it starts like this

Extract the postcodes and add a couple of extra columns

You’ll notice that I’m not really accessing any Excel ranges here. That’s because the cDataSet class handles all that drudgery for me. All I have to do to add a couple of columns, and extract out the postcode from the address is this.
    ‘ add extra columns
    With lastCell(ds.headingRow.where)
        .Offset(, 1).value = “postcode”
        .Offset(, 2).value = “in ward”
    End With
    ‘ repopulate with new columns
    Set ds = New cDataSet
    With ds.load(worksheetName)
        ‘ extract post code
        For Each dr In ds.rows
            a = Split(dr.toString(“address”), “,”)
            If arrayLength(a) > 0 Then
                dr.cell(“postcode”).value = Trim(CStr(a(UBound(a))))
            End If
        Next dr
    End With

See if the address is in the ward

mySociety API can take a postcode and return all sorts of government classifications, so we need to to create a restlibrary entry for that API. It looks like this

        With .add(“my society”)
            .add “restType”, erQueryPerRow
            .add “url”, “”
            .add “results”, “areas”
            .add “treeSearch”, True
            .add “ignore”, vbNullString
            .add “alwaysEncode”, True
        End With

Lookup mySociety and extract ward name for given postcode

Now we’ve defined the API, we can just reference it and let it know which column contains the post code to analyze. The data returned contains the name in an area object of type “UTE”, so it’s just a question of looking at the UTE entry returned by the API for the postcode and comparing it to the ward

    ‘ use mysociety api to get ward info
    ‘ these options will not bother trying to populate

    With restQuery(worksheetName, “my society”, , “postcode”, _
        , , , False, False)
        ‘ check for jobjects of type UTE
        n = 0
        For Each job In .jObjects
            n = n + 1
            inWard = “out”
            If Not job.childExists(“areas”) Is Nothing Then
                For Each joc In job.child(“areas”).children
                    If Not joc.childExists(“type”) Is Nothing Then
                        If joc.child(“type”).value = “UTE” Then
                        ‘ we have the right type, check name matches
                            If makeKey(joc.child(“name”).value) = _
                                makeKey(.dSet.value(n, “ward”)) Then
                                inWard = “in”
                                Exit For
                            End If
                        End If
                    End If
                Next joc
                ‘ mark whether its in our out
                .dSet.cell(n, “in ward”).value = inWard
            End If
        Next job
    End With


There were a couple of missing postcodes, so it needs cleaned up, but here’s a quick chart of the results.

Next steps

In his post, Tony went on to do some mapping using Google Fusion Tables and Google Maps. I planned to use Excel to Google Earth/Maps vizApps to create an earth representation of this data, and also to make a Google Apps Script restlibrary app – but I only gave myself a couple of hours and time’s up. I’ll maybe get round to those in a further post – I already have all the components so it’s a small job. 

Complete code

You can find the code by downloading the cDataSet.xlsm workbook from Excel liberation. It’s in the scraperWiki module.

(function() {
var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true;
po.src = ‘’;
var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s);

Author: brucemcp

Leave a Reply

Your email address will not be published. Required fields are marked *