De-Trending

It was easy. I couldn't even remember the name Hodrick, so I Googled prescott filter excel. Jackpot.

There is a how to use PDF, short, says you give it a range of cells and a number (a constant). The long version: Select a range of cells for your results, type =HP( , select the range of cells containing the data to be filtered, type in a comma, type in the constant, and close the parentheses. But don't hit ENTER.

Instead, hold down CTRL and SHIFT and then press ENTER. That's it. Then you can make a graph or whatever, from the results.

Holding down CTRL and SHIFT while you press ENTER is standard Excel stuff. They call it "array formulas" which sounds pretty complicated... but all you have to do is hold down CTRL and SHIFT while pressing ENTER. How complicated is that?

None of that works, though, if you don't have the Hodrick Prescott filter installed. No problem. Among the search results is a link to Kurt Annen's HP-Filter Excel Add-In at IDEAS. There are three separate files you can download:

1. an XLA file, an Excel add-in which adds the HP( ) function to the built-in Excel functions.
2. the Visual BASIC source code for the HP( ) function.
3. an example.

For most people the simple thing would be to install the add-in. (The PDF linked above tells how.) For me, the simple thing was to copy the Visual BASIC source code and paste it into a code module. That way I got to look the code over a bit. It looks like a very complicated (arithmetically) version of a "moving average" calculation, or something comparable. That's a crude description; I didn't work through all the arithmetic. But the result you get from the HP filter serves the same sort of purpose as the result you get from the moving average calc.

Anyway, after it's installed one way or the other, it's as easy to use as =SUM( ) or any other Excel function. I expect to explore it, and to use it.


What number do you use for the constant?... for the "Lambda" as they call it?

At the EViews User Forum, Trubador explains:

Rule of thumb is:
Lambda = 100*(number of periods in a year)^2

In this respect, for:
Annual data = 100*1^2 = 100
Quarterly data = 100*4^2 = 1,600
Monthly data = 100*12^2 = 14,400
Weekly data = 100*52^2 = 270,400

I think you have got the idea...

1600 for quarterly data. That was easy to remember. I didn't even have to look it up again, the first time I tried it.

Drewtedlock quotes Trubador

Rule of thumb is:
Lambda = 100*(number of periods in a year)^2

and responds:

There is additional research that suggests using a power of 4 instead of 2. See Ravn and Uhlig (2002). http://ideas.repec.org/a/tpr/restat/v84 ... 1-375.html

(I didn't explore his link.)

And a hint from Trubador: "I'd suggest you to seasonally adjust your series first."


When I have an hour or nine to spend on it, web:reg provides an interesting discussion.

// Update 30 March 2014: For a look at how changing the constant affects the result, see mine of 19 March 2014.

// Update 16 September 2014, Recommended Reading: There is an interesting analysis of the Hodrick-Prescott filter, how it works, and some problems with it, in Blogs review: HP Filters and business cycles at bruegel.org.