Introduction
You are now in a position to use your new skills with EXCEL to work on some real historical datasets, with this week's objectives being to:

• learn more about formulas and formatting;
• charting data;
• transforming raw data so that is of more use to historians.

Resources

• As for last week.
• In addition, depending upon your interest in quantitative analysis in history, you may now want to start consulting the recommended reading.
• We have also prepared four background papers on economic statistics for historians. If you are completely at sea on what national income might be then have a look at these.

A note on cell addresses
Before embarking upon this week's exercises it is vitally important that you understand the difference between absolute and relative cell addresses. This is explained well in para 3.5 in Students' Excel, p. 11. Please ensure that you have read this and understand it. A good way to do so is to open up EXCEL, load the worksheet you created last week and then experiment with the $ sign in the formulas for totals.

Exercise 1: transforming raw data into index numbers and then charting the result
Launch EXCEL and load the file

d:\teaching\it97\data\eh005.xls

which is a large worksheet containing GDP and GNP by category of expenditure at constant prices for 1870-1965, i.e. some historical national income statistics.

We will work on the period 1870-1913 (rows 22-65 of the worksheet) and on GDP only (column I), and the procedure we will now outline will become standard for you when it comes to extratcting data from the department's datasets for analysis.

Click on the icon to create a new worksheet and save it to your floppy disk using the standard format with the file name ending 06. Thus Gordon Brown's worksheet would be named:

gobr10.xls

Next return to the source file (eh005.xls) and copy the years 1870-1913 (in column A) to your new worksheet so that they occupy the block of cells A5:A48. Next, insert the text label GDP (£m) in cell B4 and GDP (1870=100) in cel l C4. You will need to widen column C so that it can accommodate its label.

Copy over the GDP data for 1870-1913 from column I in the source file to the appropriate place in column B in your new worksheet. With this (hopefully) successfully achieved, now would be a good time to save your new file. Below is a snapshot (with the top left-hand cell as cell A1) of how your worksheet ought to look:

We are now going to transform the GDP data into index numbers. You will have come across these in your GCSE, but if that seems a long time ago, and/or you felt uneasy about them at the time, here is a simple explanation of how i ndex numbers are derived and why.

The raw data in column B, although adjusted for changes in prices to produce what economists call a real series, would be difficult to interpret as it stands. We will thus transform them into index numbers, with a base of 1870=100.

In cell C5 insert the formula =(b5/b$5)*100 and then copy that formula down through the remainder of column C so that you have index numbers right through to 1913. As a check you should get the result for 1913 of 213.6, from which you can see that nati onal income increased 2.136 times between 1870-1913. More correctly, we should measure over 1873-1913, which are both business cycle peaks. The increase would then be 213.6/104.9, or 2.036 times.

You now have a better sense of scale, but cannot from the index number series read off the percentage change from year to year. Label cell D4 % change, yr-to-yr and insert the formula =(c6-c5)/c5*100 in cell D6. This should show you that GDP increased by 6.2% between 1870-1. Now copy that formula down the remainder of column D. Again, as a check, we provide the answer for the growth rate for 1912-13 (5.2%). If you have not got that then you have made a mistake with your formula. Notice ho w much the growth rate varies. Indeed, using the =min and =max commands you can work out the range of growth rates.

In cells D50 and D51 insert the formulas =min(D6:D48) and =max(D6:D48) respectively. The difference between them should be 11.4 percentage points, a very substantial range.

Now let's get a better visual sense of this data by constructing two charts (at this point make sure you have read Students' Excel, pp. 19-28). You are aiming to reproduce exactly the two following charts, the first of the index numbers (Y axis) and years 1873-1913 (X axis) and the second the year-to-year growth rates (Y axis) and years 1873-1913 (X axis).

Hopefully, you will agree that the second graph makes it much easier to decide which are years of recession and which are boom years.

Exercise 2: transforming nominal values and combining different data types in one graph
This exercise takes as its source files eh003.xls (column F) and eh015.xls (column U) and works with data for the business cycle reference dates (and years of uninterrupted Conservative government) 1951-64.

Set up a worksheet on a new page of your existing worksheet which resembles the following
 

Again we assume your top left-hand cell is A1, and we therefore need the formula to be copied to be written in cell D5. You need to express column B as a percentage of column C, with your results at one decimal place. You will know you have the right f ormula if you get a deficit of 2.6 per cent of GDP in 1951 (the year of the Korean War) and a deficit of 1.2 per cent of GDP in 1964 (election year).

Once you have this series build the following column chart.

A line chart would have done here, but the columns brings out the variation more boldly and were you to decompose the balance of payments into its constituent parts - the sum of visible trade and invisible trade equals the current balance - you could h ave used a stacked column chart which would display the total and the components.

The final part of this exercise is to see whether there is any relationship between the years in which there were deficits on the balance of payments and the unemployment rate. The hypothesis we examine is whether the balance of payments was in deficit only in years in which the economy was booming (the unemployment rate was low).

Suitable unemployment data is in eh057.xls (column G). Copy the relevant data for 1951-64 to column E in your worksheet. Build a new chart with cols D and E as the two series, choosing - at step 1 of the chart wizard - the Custom Types when Column is highlighted, then Column on 2 axes. Ensure at step 2 of the wizard that you have the appropriate X axis coordinates and that your chart has a legend so that we can distinguish between the two series. If you have succeeded with all of this you should get the chart below

where the balance of payments is on the first Y axis (that on the left) and the unemployment rate is on the second Y axis (that on the right). You should see clearly that years of balance of payments problems are years of low unemployment when the econ omy is booming and imports are being sucked in because domestic capacity is insufficient to meet the very high level of domestic demand. With general elections in 1951, 1955, 1959 and 1964 you might also want to think about the coincidence of the business cycle with the electoral cycle.

Conclusions
You have now learnt the basics of:

• transforming raw data into index numbers and percentages of some reference series
• producing two of the basic chart types: line graphs and column graphs.

As you gain more experience of using EXCEL you will find it an invaluable software tool for conducting a wide range of tasks, but to get the most from the software you do need to practice.

To IT-MA home page
To Department of Historical Studies home page.

(c)R. Middleton 1997. Last modified 4 September 2000.