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 below
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”
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.
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.