Skip to Content
Author's profile photo Former Member

Validating the BBC’s statement that “England has had the wettest January since records began in 1766”

Working at home with the rain pounding on the window and iTunes shuffle brings up Storm by Vanessa Mae, random, coincidental or inspirational?  Well for me it brought together an article I have read about in the UK news about the weather we are experiencing this winter and a tweet by @steverumsby this morning.

bbc news.JPG


So the hunt was on to find UK rainfall statistics for as many years as I can.   Thankfully the UK Met Office has a fairly open view to data and historical climate data was easy to find.

The data download looks fine to identify spot numbers but I needed a way to easily visualise it in a way to validate the statement in the BBC article that “England has had the wettest January since records began in 1766”

data 1.JPG

Firstly I did a bit of “Quick Analysis” using MSOffice 2013

Quick Analysis.JPG

HHmmm   The red dot which identifies the High point is not on the last record, 2014.  So is the BBC wrong ?

Alas no, the dataset is for the whole of the UK and not just for England.  Try again….

Yep the high point for Jan is in 2014, so in this data set Jan 2014 has the highest rain fall since 1910.

data 2.JPG

Now I wanted to analyse this further as this data brought on loads of other ideas and hunches to validate on the wider UK dataset.

I wanted to use SAP Lumira but the shape of the data as a crosstab (pivot) table wouldn’t work well in SAP Lumira so I needed to flatten it into a columnar format.  Easy you’d think, alas no, well not for me anyway.

I resorted to google and found a small piece of VBA to do a large portion of the work.

The VBA on transpose crosstab table to list:

Sub ConvertTableToList()
Const TEST_COLUMN As String = “A”
Dim i As Long, j As Long
Dim iLastRow As Long
Dim iLastCol As Long
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 2 Step -1
iLastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
For j = iLastCol To 3 Step -1
.Rows(i + 1).Insert
.Cells(i + 1, 2).Value = .Cells(i, j).Value
.Cells(i, j).Value = “”
Next j
Next i
End With
Application.ScreenUpdating = True
End Sub

With a little more effort in Excel I now had a good data table to be used.

lumira data.jpg

After importing the data into SAP Lumira, I simply plotted the Year and Monthly rainfall figures.

Data Quality.JPG

Spot the data quality issue? 

Whilst working in excel I had labelled the year 1923 against the records for 1923 and 1924.  A great example of why visualising data draws out things you’d miss in a 1250 row table. After fixing the data in Excel it was easy to refresh the visualisation in SAP Lumira.


Adding on the predictive calculations really didn’t give much hope for lower rainfall for the next 10 years…


You can download the SAP Lumira file here:

Dropbox – UK Rainfall .lums

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Steve Rumsby
      Steve Rumsby

      You realise, of course, that my tweet was a joke, right? There's lies, damn lies and statistics... 🙂

      Author's profile photo Henry Banks
      Henry Banks

      Very enjoyable, good contribution.   But is it "the wrong kind of rain"?   😉

      you've got me worried now, i'm off the check the Thames!

      Author's profile photo Steve Rumsby
      Steve Rumsby

      The Thames isn't looking good upstream. But the Thames isn't alone. Lots of rivers aren't coping with the wettest January on record...

      I'm glad I live a long way from any significant rivers.


      Author's profile photo Henry Banks
      Henry Banks

      I was very surprised to hear that even the river Lambourne in West Berkshire has flooded, and that's at source!

      Author's profile photo Tammy Powlas
      Tammy Powlas

      VBA with Lumira?  Very creative - why didn't I think of that??


      Author's profile photo Former Member
      Former Member

      Very well done, and thanks for sharing the VBA script with all of us! --Will be using that with other pivot tables.

      As for flooding *sigh*

      I was flooded out in the Southern Alberta floods last year. Lost everything. --And those insurance companies, not happy to shell out anything "Act of God" and all, y'know.

      Let's hope that everyone gets out of this alive, and well~