HadCET Data

Met Office Hadley Centre Central England Temperature Data

The UK Meteorological ("Met") Office, Hadley Centre website has a page with download links for archived temperature records for central England, taken daily from 1772 to present day. The file is a little unwieldy, being a single line of text with 107,632 data values separated or delimited by a 'space' character and NOT in the stated format

Column 1: year
Column 2: day
Columns 3-14: daily CET values expressed in tenths of a degree. There are 12 columns; one for each of the 12 months.
1772    1   32  -15   18   25   87  128  187  177  105  111   78  112
1772    2   20    7   28   38   77  138  154  158  143  150   85   62
1772    3   27   15   36   33   84  170  139  153  113  124   83   60
1772    4   27  -25   61   58   96   90  151  160  173  114   60   47
1772    5   15   -5   68   69  133  146  179  170  173  116   83   50
1772    6   22  -45   51   77  113  105  175  198  160  134  134   42

The data can readily be parsed into an Excel spreadsheet using the VBA code below (assuming the text file is downloaded to desktop and called hadcet.txt). Alternatively you could use the FLUKE csv version File:hadcet.csv

Sub ParseHADCETfile() 'VBA for Excel
   Dim strFileName As String 'path and filename
   Dim LCount As Long
   Dim DataArray() As String 'string array to hold the individual data values
   Dim strRecordData As String 'read-in data from the HADCET file
   Dim i As Integer, j As Integer 'column and row counters
   strFileName = Environ("USERPROFILE") & "\Desktop" & "\hadcet.txt"
   LCount = 0 'pointer to current data value
   Open strFileName For Input As #1
   Input #1, strRecordData 'as one long space delimited string
   DataArray() = Split(strRecordData) 'split the string into 107632 data values
   For j = 1 To 7688 'rows 1-7688
       For i = 1 To 14 'columns 1=A, 2=B etc.
           If i < 3 Then
           Range(Chr(64 + i) & j) = DataArray(LCount)
           Else
           Range(Chr(64 + i) & j) = DataArray(LCount) / 10
           End If
           LCount = LCount + 1
       Next i
   Next j
   Close #1
End Sub