Cross-border bank liabilities

The chord diagram below, which I produced using the excellent new Javascript visualisation library, D3, shows how much banks in each country owe banks in other countries.

Thematic maps... (in Excel!!!)

I recently figured out how to make thematic maps in Excel, like the one below which shows average attainment in English and maths within English primary schools, available from the Department for Education.  First I adjusted the cells in the workbook to make the square grid.  Then I matched the postcode level test score data to a database (from Ordnance Survey) to return the grid reference of each school.  Next I calculated the average attainment for each 5km square, mapped the value between 1 and 10 (to help with the next step) and populated the grid with the results.  I then wrote a couple of macros (see below), which first of all edit Excel's standard colour palette, allowing the use of a nice light green to dark red colour scale, and then sets the colours in the grid according to the cell's value.

Excel VBA macros:

Sub ChangePalette()

Workbooks("name of your workbook.xls").Colors(1) = RGB(229, 255, 204)

Workbooks("name of your workbook.xls").Colors(2) = RGB(204, 255, 153)

Workbooks("name of your workbook.xls").Colors(3) = RGB(178, 255, 102)

Workbooks("name of your workbook.xls").Colors(4) = RGB(153, 255, 51)

Workbooks("name of your workbook.xls").Colors(5) = RGB(102, 204, 0)

Workbooks("name of your workbook.xls").Colors(6) = RGB(255, 102, 102)

Workbooks("name of your workbook.xls").Colors(7) = RGB(255, 51, 51)

Workbooks("name of your workbook.xls").Colors(8) = RGB(255, 0, 0)

Workbooks("name of your workbook.xls").Colors(9) = RGB(204, 0, 0)

Workbooks("name of your workbook.xls").Colors(10) = RGB(153, 0, 0)

End Sub

Sub ColorChange()

Dim rRange As Range

Dim rCell As Range

     Set rRange = Range("A1", Range("Z26").End(xlDown))

     For Each rCell In rRange 

     rCell.Interior.ColorIndex = rCell.Value

    Next rCell

End Sub

Data matrix visualisation using Processing

The visualisation below, which I developed using the Java-based processing object orientated programming language, shows intra-european trade origins and destinations by volume.  The code reads data from a couple of xml files and utilises a custom object to draw a selection of bezier curves for each country's exports and imports.


The height of the curve shows the volume of trade (normalised by population), and the colour shows the direction of travel, i.e. the red cures in the top half show exports from Ireland, while the red curves in the bottom half show imports to Ireland.


You can view the code here, and download a larger (and clearer) PDF version here.

Ease of doing business heatmap

I put together a heatmap of the World Bank's ease of doing business rankings using R, which quickly highlights which countries are doing relatively well/badly on a variety of measures.  Singapore come out top, Canada need to do something about their electricity supply, and apparently getting credit seems to be easy in the UK.  I've pasted the R syntax below.

WBBP <- read.csv("WorldBankBestPlaces.csv", sep=",") *reads in csv to 'WBBP' data object

WBBP <- WBBP[order(WBBP$Ease.of.Doing.Business.Rank),] *orders by ranking index

row.names(WBBP) <- WBBP$Economy *labels rows with economy name

WBBP <- WBBP[,2:12] *drops economy name col (not needed after step above)

WBBP_matrix <- data.matrix(WBBP) *converts data frame to data matrix

WBBP_heatmap <- heatmap(WBBP_matrix, Rowv=NA, Colv=NA, col = heat.colors(256), scale="column", margins=c(16,8))

Hello world!