Charting Economic Time Series with Shaded Recessions

Posted on February 03, 2008

If you read the business press or government economic reports, then you have undoubtedly seen charts that show recessionary periods with shading. In this post I will demonstrate how to create this type of chart in Excel 2007. The same technique is applicable to all versions of Excel, though the mechanics of creating the charts in earlier versions are slightly different. Users of other spreadsheets, such as Open Office Calc should be able to use a similar procedure. 

Obviously, if we are to chart an economic data series and shade the recessions then we need data. For this post I will be charting the US civilian unemployment rate. This data is available from the Bureau of Labor Statistics, though I got it from the St. Louis Fed’s FRED database. We also need the dates of US recessions, which are available from the Business Cycle Dating Committee of the NBER. The chart that we are trying to create is pictured below: 

Creating the chart of the unemployment rate is simple. Just select the dates and the unemployment rate series. Go to the Insert tab and insert a Scatter chart. 

Charting Strategy

The more challenging problem is getting those shaded bars for recessionary periods. Here is the strategy in a nutshell: 

We need to create a boolean variable (true/false, or 0/1) to indicate the presence or absence of a recession. In this case, we will use a formula to return a 1 if the date is during a recession and a 0 otherwise. Then, we will plot this series as a column chart and use a secondary Y-axis (at the right) for the scale. The reason that you don’t see the secondary axis in the picture above is that it is hidden. The result is a mixed chart. That is, it has a series that is charted as an XY Scatter chart and also one that is charted as a column chart. 

Structuring the Worksheet and Data

The unemployment data is straightforward. Just paste the data into columns A and B in the worksheet. In column C we are going to calculate the boolean variable mentioned above. We also need the dates of the post-war recessions from the NBER. Go to column F and enter the dates of the expansion peaks, and in column G enter the recession troughs. The worksheet should look like the fragment below: 

A note about the recession dates: The NBER data gives a particular month that a recession began and ended. I chose to set the peak date to the beginning of that month, and the trough date to the end of the month. This simplifies the formula slightly, and it really doesn’t matter much in the grand scheme of things.

Building the Boolean Formula

Now we need a formula that will compare the relevant date in column A to the dates in columns F and G. If the date falls between any two of the dates in F and G, then it is in a recession. Clearly, then, we need to do some kind of a lookup in the table of recession dates. Your first thought might be to use VLookup(), and you probably could do it that way. However, I chose a different approach that I think is more efficient and probably works better. 

My approach is to use a combination of the Index() and Match() functions. The Match() function will look for our date in column F and then return its position, or the position of the largest date that is less than our date. So, the first date that we will be looking up is 1/1/1948. In this case, the Match() function will find that 2/1/1945 is the largest date that is less than or equal to our date (11/1/1948 is larger) and will return 1 (the first item in the list). We will need that 1 again in a moment to serve as the "key" to the ending date of the recession. 

Next, the Index() function will be used to get the actual date of the last economic peak (2/1/1945). So, at this point here is the formula that we will use to find the date of the last peak: 

=INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12))

Note that $F$2:$F$12 is the list of peaks, and A2 is the date that we are looking for. Now that we have the peak date, we need to find the corresponding trough date. That date will be in the same row as the peak date, so we will use the same Match() function to get the index number. We then feed that into the Index() function to get the trough date: 

=INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12))

Notice that I changed the array for the Index() function to $G$2:$G$12. That is what causes it to look in the trough column. 

So, we now know the dates of the last peak and trough in the economy. All we need to do is to see if our date is between those two dates. If it is, then our date falls in a recession. If it isn’t, then our date is in a period of expansion. Fortunately, Excel stores dates as an integer (the number of days since January 1, 1900) so we can do some simple mathematical comparisons. We want to check to see if our date is simultaneously greater than the peak date and less than the trough date. If it is, then the date is in a recession. 

To test to see if two (or more) conditions are simultaneously true, we use the And() function. So, we can do this: 

=AND(A2>=INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12)),
A2<=INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12)))

If that evaluates to true, then the date is in a recession. We could wrap that formula in an IF() statement and return a 1 or 0, but we don’t need to do that. The And() function will return either TRUE or FALSE, and Excel treats TRUE as a 1 and FALSE as a 0. Because of that, we can actually chart a series of TRUE/FALSE values. However, I prefer to see 0 or 1 so I will force the conversion to a number by multiplying by 1. The resulting formula for C2 is: 

=AND(A2>=INDEX($F$2:$F$12,MATCH(A2,$F$2:$F$12)),
A2<=INDEX($G$2:$G$12,MATCH(A2,$F$2:$F$12)))*1

Copy that formula down to C722 to do the calculation for all dates. 

Add the Recession Boolean Series to the Chart

Adding another data series to a chart in Excel 2007 is a bit different than in previous versions. It used to be that you could just drag and drop a range onto an existing chart to add another series. That functionality has, unfortunately, been removed in Excel 2007. Still, it isn’t difficult. Just right click in the chart area and choose Select Data from the shortcut menu. Finally, click the Add button and then enter the data ranges. 

At this point, your chart will look rather odd, as seen below: 

Given the vastly different scales of the data series, we need to do some reformatting. First, select the recession data series and then Format Selection. In the Series Options section of the dialog box choose to plot the series on the secondary axis. That will clean it up considerably, but we aren’t done. Now go to the Design tab, click Change Chart Type and set the chart type to Clustered Column. Select the series again and then change the fill color to a light gray (or whatever other color you like) and set the border color to No Line. Go to Series Options and set the Gap Width to No Gap (0%) to get solid filled bars. 

Now that the data series is set, we can reformat the X axis and add a chart title. The X axis scale should be set such that the minimum is about 17,500 (roughly the first date) and the maximum is 39,500 (a bit after the latest date). Finally, move the legend to the bottom, add a title, and remove the grid lines. 

At this point, your chart should look like this: 

Note that I have circled the secondary Y axis. We need to set the scale so that it has a minimum value of 0 and a maximum of 1. This will make it so that the gray bars go to the top of the chart. Now, we need to hide the axis. Set the Line Color to No Line, and then go back to Axis Options and set Major tick mark type and Axis labels to None. 

We now have the final version of the chart: 

This technique can be applied to any long-term economic data series, and makes it easy to see how the data behaves during recessions and expansions. I hope that you have found this to be useful. 

You can download a sample worksheet with the data and chart from this post. Note that I have placed the chart in the Chart1 sheet.

Comments:

Name:

Email (will not be displayed):

Location:

URL:

Smileys

Remember my personal information

Notify me of follow-up comments?

Submit the word you see below:

<< Back to main