UCSB - ECON 101 - Spring 2024
Problem Set 1 - Exploring Data
A macroeconomic time series data is made up of a trend component and a cyclical component. In this question, you are asked to isolate the trend component from the US consumption and investment data from 1997 to 2021 available from the Economic Report of the President using the Hodrick Prescott Filter (HP Filter) add-in in Excel. To learn what HP filter is, please refer to this video.
To download the consumption data, navigate to the website of the Economic Report of the President (click here) and download the ”Table B-10: Personal consumption expenditures, 1972-2022” from the 2023 report in an XLS file format. For the investment data, download the ”Table B-12: Private fixed investment by type, 1972-2022” from the same link. Please note that the two datasets provide nominal values, and the latter dataset does not include inventory investment. After downloading the two datasets, open a blank Excel file. Copy the ”Personal consumption expenditures” column for the corresponding years from 1972 to 2022 from the first downloaded file and paste it into the blank Excel file. Next, copy the ”Private fixed investment” column from the second downloaded file and paste it into the same file where you inserted the previous column. Add a year column to the right of the pasted columns and assign the proper years. Also, to convert the nominal values to real values presented in billion dollars in the year 2022, copy the Consumer Price Index values available in the ”CPI.xlsx” dataset on Canvas, and paste them into your dataset. Now, using the nominal values and the CPI, create two columns that keep the real values of personal consumption and private fixed investment. Remember that when converting nominal values to real values with a specific reference year, you need to divide the nominal values by the CPI values, where
the CPI values are adjusted so that the CPI for the reference year is set to be 1. In this example, the reference year is 2022, and the CPI values given in the ”CPI.xlsx” file are already properly adjusted. Therefore, there’s no need for further adjustments to the CPI values on your end.
To install the HP filter in Excel, navigate to this link and click the first link (https://dge.repec.org/codes/annen/HPFilter.xla) under the ”Downloads” section. This will download the HP filter add-in file, ”HPFilter.xla,” to your default download folder. The file you downloaded might be saved as ”HPFilter.xla.xlsx.” In this case, change the file name to ”HPFilter.xla” so that the file is correctly saved in the xla format. Now open the dataset file you’ve created in the earlier step.
If you are a Windows user:
From the top-level menubar, Click File > Options > Add-ins > Go... (next to Manage: Excel Add-ins) > Browse... in order, and import the “HPFil ter.xla” add-in file from your default download folder. Ensure that the checkbox for the Hodrick Prescott Filter is checked, then click OK.
If you are a Mac user:
From the top-level menubar, click Tools > Excel Add-ins > Browse... in order, and import the “HPFilter.xla” add-in file from your default download folder. Ensure that the checkbox for the Hodrick Prescott Filter is checked, then click OK.
In case you encounter an error message stating “Microsoft has blocked macros,” troubleshoot by applying the relevant method listed in this link (for Windows users) or this link (for Mac users).
To learn how to apply the HP filter in Excel through an example, read this material thoroughly. Now, to directly apply the HP filter to our example, create two columns that keep the natural log values of the consumption and investment data (name the two new columns as “ln Real C” and “ln Real I”) to the right of the pasted columns in your dataset. Also, name the two blank columns to the right of the natural log columns as “ln Real C trend” and “ln Real I trend” that will keep the trend com ponent of real consumption and investment, respectively. Please refer to the first image attached to the next page to confirm if you have completed everything correctly.
To find the trend component of real consumption, select the blank rows of the “ln Real C trend” column that correspond to the filled rows of the “ln Real C” column. While keeping the rows selected, fill out the formula bar using the HP function as shown in the image below. After completing writing the formula, hit “Shift + Ctrl + Enter” together if you are a Windows user, or hit “Shift + Ctrl + Return” if you are a Mac user. This will automatically compute the trend component of personal consumption expenditures. Do a similar procedure to compute the trend component of investment as well.
Lastly, to find the cyclical component of real consumption, name a blank col umn as “ln Real C cyclical” and keep the values that subtract “ln Real C trend” from “ln Real C”. Do a similar procedure to find the cyclical component of real private fixed investment as well. If you have done everything correctly, your dataset should look like the second image on the next page.
(a) Find the correlation coefficient between the detrended real personal consumption expenditures and the detrended real private fixed investment for the years from 1972 to 2022. You MUST round the values to three decimal places.