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.
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.
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:
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.
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.
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.