A Random Choropleth

A few days ago an Intergenerational Foundation colleague showed me an Excel workbook published by the London Datastore with which it was possible to produce maps like this

and wondered if I could extend this to maps beyond the M25.

The answer to such questions is almost always “yes” – what varies is how long it takes, and how neatly it can be done. Click here to download what I have managed, which can produce maps such as this – called choropleths – and which is discussed belowOutput

How this works

This workbook has two sheets, one for the choropleth, the other for data, which need to be named “choropleth” and “data” respectively.  On the choropleth sheet, there has to be a cell with the text “Legend”, and also these with the text “Colour0”, “Colour1” and “Boxes”

Controls

which define the colour range for the choropleth.  To change the colours, use the normal Excel fill colour for those cells.

The data sheet has to have a table with one column headed “ShapeNames” and another “Data” – and any number of other columns.

Data

Here the data are just random numbers between 0 and 1 – but this is where the values to be shown in the choropleth are entered.

The emboldened cells in this workbook are ones which the user should not change – although there is no protection attempting to prevent this.

Under the bonnet, it works by the map in the choropleth sheet comprising a collection of Excel shapes – of type “Freeform” – named according to the values in the column headed ShapeName in the data sheet.  Change these names, and the “Run” macro fails.

How this was constructed

The first step was to reverse engineer the workbook published by the London Datastore, to see how its macro to fill a collection of Excel shapes with varying colours worked.

The next step was to obtain a collection of Excel shapes for a different geography. Standard shapefiles, with the extension .shp, can be obtained from many public sources – in this case the UK ONS.   This can be read with the free software QGIS, exported as a .emf file, and then this read into Excel as an image.  The Excel image can then be ungrouped, which turns it into the desired collection of Freeform shapes.  Unfortunately, information linking these shapes to the names of the regions in the shapefile are lost, and a manual process is needed to restore the link.  This manual process can be greatly accelerated by copying and pasting the attributes table for the QGIS map layer into Excel, since the Excel Freeform shapes come in the same order as the region names in the attributes table.  The process cannot be completely automated, because when a region is geographically broken up – which happens with islands – there are multiple shapes for the same region.  In this case – e.g. with Isles of Scilly – it is necessary to pick one, e.g. the largest.  There are also several degenerate shapes, with either height or width zero – which can be eliminated with a macro.

How this could be extended

The main limitations of this are:

  • there are only shapes for one set of regions
  • the Excel shapes have to be obtained from a copy of a workbook, with all the Excel risk of something getting changed when it shouldn’t be
  • The VBA code is contained within the workbook, so would need to be copied to new workbooks

It would be nice to have definitive datasets which define the Excel Freeform shapes available from some web site for download.  If this has not already been done, it could be achieved with some VBA code to read KML files, as exportable from QGIS, and convert their latitudes and longitudes first to Easting and Northings, and then to displacements from the top and left of a document in the construction of a Freeform shape in Excel.  The first – and hardest – part of how to do this is described in a workbook downloadable from the Ordnance Survey here.

If sets of Excel shapes could be constructed on request from authoritative publicly available files, it would be better to move the VBA code here into an add in, which, once installed, would allow the user to construct the shapes which comprise the map in the sheet “Choropleth” and the entries in the column “ShapeNames” in the sheet “Data”.

There are always ways a user interface can be improved, especially if the code is wrapped up in an add in, but there is also always an element of judgement / taste in this, so no observations on that here.

2 thoughts on “A Random Choropleth”

  1. Or you could just not use excel. This is what GIS products were designed to do – it’s called Thematic mapping. QGIS probably does this already (though I don’t know the specifics of that GIS package). Most GIS can import .csv files (or even .xls/.xlsx), and then manipulate the data as necessary, and shapefiles are what GIS are designed to cope with, so personally I’d use the GIS package to do what it’s designed to do rather than writing VBA code to get excell doing something it’s not designed for. but that’s just me.

    I’m impressed with what you’ve done though.

    1. You certainly can do this in QGIS – I have myself. The point is that many people don’t feel comfortable doing anything with data other than in Excel – that’s why the London Datastore published their spreadsheet. You’ll probably find that in many organisations, there are only a few paid for GIS app licences – e.g. for ArcGIS – and policies against other users installing applications such as QGIS.

      I hope I made it clear I didn’t think Excel was the best way of doing this – but it what some customers at least will want!

Leave a Reply

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