Detrending method for nonstationary data

Could anybody help me with detrending data that is nonstationary? I have already made the mistake of trying to detrend it by plotting the residuals of a linear regression in excel but it was pointed out to me by 'Zach' and 'cardinal' that it is not a valid method. Unfortunately, I had used that method after many hours of internet searching about detrending data. I have searched for ARIMA models in the context of detrending a line but I only get formulaic results when what I really need is a descriptive answer. I recognize that the math behind it is important, and maybe I can work on it in time, but now I just need to detrend the line. My questions are 1. Is it possible to detrend the nonstationary data in excel. 2. If not, is there a simple free stats software I can download to work with. 3. Is there anyone who might kindly help me in achieving this small objective as I have spent several days trying to solve the problem and I haven't really achieved very much. Any help will be gratefully received.

• You was advised to do differencing, a general method to detrend series. Did you try it? 1-order differencing is subtracting Y(t) from Y(t+1). In your case, it will yield a horizontal line disturbed by raised bed in the middle. Will that suit you? – ttnphns Jul 31 '11 at 8:20
• Hi. Yes I did difference the data and it is stationary and I am happy with it. But in one article I read said that "to avoid obtaining misleading results both differencing and detrending should be applied". On the other hand, another article says differencing is a method of detrending. So it is a little difficult to find the right path through what seems like quite a maze. I have looked online for a tutor as I am happy to pay for good education but, surprisingly, I can't seem to find one who truly understands the A-Z of this stuff. – paul Jul 31 '11 at 10:09
• "differencing is a method of detrending" are correct words. Though one is free to apply other methods to take away trend (such as regressions). Pity you didn't declare what's your eventual aim is. Still, you said you are happy... – ttnphns Jul 31 '11 at 12:16
• Your interpretation of what "Zach" stated isn't correct (after locating that response in one of your prior questions). They never said that using the residuals from a linear regression model is invalid; they merely said it was invalid for your data (which, like IrishStat notes, is more like a level shift paired with a linear trend). Detrending via regression is a highly usable method (cf. Cowpertwait & Metcalf, 2009). I just don't want people trying to learn time series methods to become misinformed. – ATJ Feb 14 '15 at 20:52

Why do you need to de-trend this data?

The simplest solution I can see is to difference the series, and see what that looks like.

The second simplest solution would be to fit 3 trend lines:
1. Start - May '06
2. May '06 - May'07
3. May '07 - End

If I were analyzing this data, I would install R, then install the forecast package, and then use the auto.arima function in the forecast package to automatically build an arima model, which I could then use to forecast or smooth the series. All this software is free and open source, so there's nothing preventing you from taking this route.

Your objective isn't very clear, so I don't think we will be able to help you beyond this.

• Hi Zach. Thanks for taking the time to respond again, it is appreciated. My objective is to show a relationship between two series of nonstationary data. One can be made stationary by differencing (as I learnt here) but the other cannot, according to a comment from a member here. It is not imperative that I detrend the line. It was only that I had read that differencing and detrending are not the same and that I should do both. But that now appears to be not so...cont – paul Jul 31 '11 at 23:28
• ...cont. The image of the data that I want to associate is at the link showing the differenced blue line and the original 'always' nonstationary red line imageshack.us/photo/my-images/801/redvsdifferencd.jpg. What I would like to be able to say (show) statistically is that the red line is associated with the drastic moves in the blue line from Sep 05. With that as my objective is your advice to follow your suggestion regarding R? Thanks. – paul Jul 31 '11 at 23:28
• @paul What is the source of this data? One idea would be a regression with 2 terms, time and the red line vs your original blue line. Another idea would be to build an ARMAX model in R, which is probably the best choice. – Zach Aug 1 '11 at 1:25
• @paul I don't think statistics will help you much in that case. Maybe you'll manage to show that there was a correlation between the two lines. But it will never show you the causality between the two. red line high impacted only once the blue line in the history you consider. This is definitely not enough and will hence give you anyway non significant results. – RockScience Aug 1 '11 at 5:55
• @RockScience. Maybe I'm looking at this the wrong way. There was an event (the red line) which affected the blue line in a big way. After a while the event faded and the blue line reverted to somethine similar to its original path. This must happen all the time (a stock market crash on bankruptcies, a nurses strike on patient deaths, for example). What do statisticians do if they want to show that the event affected the blue line? Or is it an impossible task? – paul Aug 1 '11 at 6:10

Detrending can be done by applying a low pass filter that calculates the trend. The remaining part is your detrended data. Two examples of low pass filters here:

• apply a Hodrick-Prescott filter. I would advise to use R and the package mfilter for instance, but apparentyl you can do it in excel through a free plugin (never tried)

• perform a multiresolution analysis using a wavelet method. In my experience, this gives very good results. Again, R will do that using package waveslim and will give you the best flexibility, but I am sure some people have implemented it in excel.

R is free. If you have already worked with Matlab, you will not be too much surprised. It may take you a bit of time to get used to it, but it is worth it.

• Hi RockScience, I downloaded the HP-filter add-in (provided by Kurt Annen) and ran the prog. The peculiar thing is that the detrended line looks not much different to the detrended line I got from plotting the residuals of a excel regression of blue line on time. The image of both the detrends is here img199.imageshack.us/img199/9916/hpfilter.jpg The light blue line is what was left after HP-filter. Does this result look sensible? It has a unit root at the 5% level but does not at the 10% level. I will also download R and see if I can get familiar with it. Thanks – paul Aug 1 '11 at 6:59
• It seems normal that the two methods give similar results. The good thing with HP filter is that it is a frequency filter, which means that you can adjust the lambda which is linked to the cut off frequency. Any frequency above the cutoff frequency is considered as cycle, any frequency below the cutoff frequency is considered as part of the trend. – RockScience Aug 1 '11 at 7:39
• It puzzles me still that a detrended line appears visually to have a trend (at the beginning). Also that differencing and HP-filter are both methods of detrending yet give visually such different results. Probably there are many versions of a detrended line from the same seriesof data. Would you happen to have any thoughts on my question about how statisticians express the effect of, say, a nurses strike on patient deaths, or a war on child mortality? Both of those scenarios might look similar to my blue-line red-line scenario. Thanks again for all your comments. – paul Aug 1 '11 at 8:38

Trending data is nonstationary by definition, so "nonstationary" does not add anything to your description. What linear regression in Excel are you talking about? The one that would use the index of the period (e.g. 1,2,3...) as the regressor would be a natural start. If you have an idea on what goes on in the data, detrending is not inherently statistical, just a trick to get you something like a "sample" - a realization of a stochastic process that is (in some sense) stationary, so at least its mean does not change.

• As far as I am aware, excel only has one method of (automatic) regression analysis and it is found in the data analysis functions. So that is the one I used. I regressed against period. But as commented by another forum member, it isn't valid for this data. – paul Jul 31 '11 at 10:15
• @Alex: Did you mean "Trending is nonstationary by definition..."? – cardinal Jul 31 '11 at 12:51

An objective evaluation of this time series might suggest a Level Shift i.e. an Intercept Change in addition to either differencing or a multiple time-trend structure while incorprating ARMA structure and Intervention Detected anomalies. Why don't you post the data and we can actually deliver an analysis. Assuming certain structure can sometimes lead to poor models.

• You would analyse it for me? That is a great idea, thanks. I will edit the question and add the data. – paul Aug 1 '11 at 17:55
• I'm sorry but I can't work out how to get the data into the question without it looking just like rows of numbers. Is there a way i can post an excel file? Sorry for the hassle. – paul Aug 1 '11 at 18:05
• :paul Please send me the data at dave@autobox.com and I will try and post it to the group. I am not sure exactly as the most efficient way to do that. – IrishStat Aug 2 '11 at 13:03
• Hi there. I finally managed to work out what to do with this data through a combination of helpful hints from this site and some searching on the net. So I haven't sent the data as I don't want to waste your time. Thanks a lot though for offering to have a look at it, it was very generous of you. – paul Aug 3 '11 at 7:42
• pAUL, you might (probably) will be missing out on some exceptional advise. – IrishStat Aug 3 '11 at 11:21