Getting and Organizing
European Temperature Data
The European Climate Assessment & Dataset (ECAD)
site provides daily temperature data for over 960 stations throughout
Europe. Users can download a 10mb zipped file that includes text files
of historical mean daily temperature data for these stations.
I have created a
workbook that includes a list of the 960 European stations, their
latitude and longitude as well as the start and end dates for the data
records. A VBA procedure allows me to select a station and have it
automatically opened in Excel.
To set up your own European temperature trend analysis
system, download my workbook and the ECAD files, unzip and save them
in the same folder.
Understanding the ECAD Text Files
Once you open an ECAD text files in Excel, you will find
one column of text. The first 19 lines document the file. The actual
temperature data starts on row 20. There are 4 data items per row:
1) SOUID is the 6 digit station ID, 2) Date is 8
digits with the format (yyyymmdd), 3) TG is the 5 digit representation
mean daily temperature in 0.1 oC units, 4) the Q_TG is a 5
digit quality code for the measurement.
Now's a good time to save your file with a useful name.
Converting Text Data to Usable Numeric Data
Now that we have our historical data in text format, we
need to get it into a numeric format. Excel provides a powerful Text to
Columns Wizard that let's us make this conversion quickly.
Excel convert your single column text data
into 4 columns of data.
We still need to make some data adjustments to
get our temperature trend data into suitable form for charting and data
First, we need to convert the temperature data form
0.1°C units to °C units. We do this by dividing the column C values by
Next, we need to do something with the date column so
that we can summarize temperature data by year, month, or day. since 67 of the
European stations have temperature records before 1/1/1900, we'll need
to develop a technique for handling these pre-1900 dates in Excel.
Converting ECAD Date Format to Excel
To simplify calculation of annual mean temperatures, I
have found that creating a new columns for the year, month and day with
Left, Mid and Right formulas works well. the resulting table looks like
The daily data is now in a format that you can use to summarize
temperature trends by month, year, decade or any other time period that
|Climate Data Sources
||U.S. Historical Climatology Network (USHCN) -
NDP-019 - provides access to stations by state.
||European Climate Assessment & Dataset