Dickey-Fuller test and augmented Dickey-Fuller test - unit roots and stationarity (Excel and EViews)

In time series analysis, establishing that the variable you investigate is stationary is very important as it is an assumption of many common estimation techniques. Dickey-Fuller test is an extremely flexible tool one can use to detect unit roots and stationarity violation under different settings. Today, we are applying Dickey-Fuller and augmented Dickey-Fuller tests to stock market data in Excel and EViews. Econometrics is easy with NEDL!
Please consider supporting NEDL on Patreon: / nedleducation

[@NEDLeducation]

You can find the spreadsheets for this video and some additional materials here: drive.google.com/drive/folders/1sP40IW0p0w5IETCgo464uhDFfdyR6rh7 Please consider supporting NEDL on Patreon: www.patreon.com/NEDLeducation

[@nbatdrago1]

Thank you so much!!

[@EvilSpeculator]

Wow that was brilliant. Showing this done in a spreadsheet really explains the respective steps taken and the differences between the ADF and CADF. Thanks a lot for taking the time to post this! :-)

[@NEDLeducation]

Hi, and many thanks for the feedback, really glad you liked the video! Stay tuned for more content in financial econometrics :)

[@NayeemMohamad]

Thanks a million. You are a GENIUS, and so generous sharing your great knowledge with us. Thanks again

[@NEDLeducation]

Hi Nayeem, and many thanks for such kind words.

[@juliusvinson5004]

Thanks for sharing your wisdom. Sincerely appreciate this.

[@Daniel-zb3tt]

thank you so much!!! this was extremely helpful for me!

[@SamehSenosi]

Thank you so much for sharing valuable information

[@mariateresabuenavista8560]

Brilliant! Many thanks!

[@thedon758]

God bless you man! Keep it up!

[@user-rt7fl4hd8g]

Hi Sava, thanks so much for the knoeledge sharing. I really enjoyed your teaching approach. Can you please share video on the areas of testing for seasonality effects such as December effects, Halloween effects etc. I will really appreciate this

[@nishapatel8195]

This video has explained many challenging calculation tasks in excel with great simplicity. Pleasingly Surprised by same results by both excel and Eviews software calculation ADF test. Looking forward to one of the critical & significant outputs of ADF TEST in Eviews was P-VAULE, which is not calculated in EXCEL. Request if P-VALUE calculation is included would to helpful. Thanks Nisha Patel

[@NWKastaways]

If I'm not mistaken, you can get the P Value by inputting the T value into T.Distr function using two tail and degree of freedom would be number of observations. Someone will correct me if I'm wrong hopefully.

[@ivanklful]

Awesome video! The best I've ever seen about this. Thank you! What would like to ask is following. After we calculate the coefficient of linear regression between first difference and lagged, it needs to be calculated standard error. At your example standard error is automatically calculated by using 2-cell and using formula =LINEST(Difference; Lagged;0;1)(without drift) and =LINEST(Difference;Lagged;1;1) (with drift). After I tried the same only coefficient shows up but not the standard error. So knowing that standard error (which is standard error of the sample) is Standard deviation/SQRT(Number of observations). So I calculated standard error by using the formula =STDEV(LINEST(Difference;Lagged))/(SQRT(Number of observations))( in this case the number of obesrvations would be the number of days)! There after t-statistics = coefficient/Standard error! Is my approach right? Thank you again for this awesome video!

[@NEDLeducation]

Hi Ivan, and glad you liked the video! I believe the issue is that you need to select a 2x1 array or a 2x2 array, respectively, before you enforce the LINEST functions for the coefficient standard errors to appear. Unfortunately, as these are coefficient standard errors, you cannot calculate in directly as a standard deviation. Hope it helps!

[@ivanklful]

@@NEDLeducation I've tried and everything is ok! Thank you!

[@ignaworld2514]

This video si great!

[@santoshkumari8030]

I want to calculate p value without using any extra software or addin...u r genius u made it just p value left

[@NEDLeducation]

Hi Santosh and many thanks for the question! The Dickey-Fuller statistic follows a tau-distribution, which is a slightly adjusted T-distribution. We might do a video on this someday. For practical purposes though, just referring to critical value tables or using a rule of thumb (t-stat < -3.5) should suffice.

[@ghcmartins]

Hi! Would make a video on Hurst Exponent in Excel? This video here was really useful! Thanks for the great work man!

[@NEDLeducation]

Hi Guilherme, and glad the video helped! Thanks for the suggestion! A video on Hurst exponent and long memory in time series is in our plans, I feel I might release one in couple of weeks time.

[@santoshkumari8030]

How probability can be calculated in adf test in excel without extra addin

[@peterc.2301]

Dear Sava, once more a big thank you for your amazing channel. Could you make a video with johansen cointegration test on excel? Thank you again!

[@NEDLeducation]

Hi Peter, and glad you are enjoying the channel! As for your suggestion, Johansen test is pretty messy to do in Excel but I will see what I can do, perhaps sometime in the future :)

[@sharmilapatil4250]

Can you pls tell how to do robustness measures test for index data? And your video is really helpful. Thank you for sharing this

[@NEDLeducation]

Hi, and glad you liked the video! Could you elaborate on what you mean by a "robustness measure" in this context, might be able to advise in greater detail then.

[@anmolpreet8959]

Thanks, this is beautifully explained in excel. Just one question - would it be safe to assume non stationarity = good for mean reversion statergies ? Is there any other tests one can perform to confirm the absence of stationarity or autocorrleation?

[@NEDLeducation]

Hi Anmol, and glad you enjoyed the video! For purposes of trading strategies, it is necessary to know the direction of autocorrelation, as obviously positive autocorrelation would imply series are trending, and only negative autocorrelation is conducive to mean-reversion strategies. You can therefore look at various market efficiency tests that distinguish between trending and mean-reverting behaviour and are designed specifically for that. From my experience, academic research most frequently applies variance ratio tests (have got a series of videos on different version of that, the simplest would be: kzbin.info/www/bejne/gourgpeZlqhsbJY) or runs tests (kzbin.info/www/bejne/hKe6nmpjhqlmerM), while practitioners in this regard prefer the Hurst exponent (kzbin.info/www/bejne/omGbfXx5r518pM0) or Markov chains (kzbin.info/www/bejne/ZmHMaJirhtKJe5Y). Hope this helps!

[@faizaahmed6488]

It is too good and informational.plz make some vedio on how to construct portfolio in excel

[@NEDLeducation]

Hi Faiza, and glad you liked the video! As for your suggestion, I have already got some videos on portfolio theory and portfolio management on the channel, please check it out if you are interested: kzbin.info/www/bejne/sJuuk519balprtE

[@Mehulvaru]

Thanks for video, if can help to identify p value for given example in excel for probability of stationarity.

[@NEDLeducation]

Hi Mehul, and glad you liked the video! The t-stat for the Dickey-Fuller test is not distributed according to the conventional Student's T distribution so the best strategy would be to look up the significance tables. Hope it helps!

[@phalanxz11_]

God bless this guy

[@NEDLeducation]

Glad you found this video helpful. Stay tuned for more Econometrics tutorials coming soon! :)

[@yaqeenfatima9486]

have you applied on closing price of stock?

[@saipri]

Hi...thanks for this video! If I were to increase the lag substantially and if the t-stat indicates the existence of unit root... which statistical test (no lags vs. lower lags vs. higher lags) would be relevant?

[@NEDLeducation]

Hi Priya and thanks for your feedback! As for the question, for augmented Dickey-Fuller tests, you do not look at the t-stat for lagged coefficients, it is just the single t-stat which is relevant. To determine how many lags are required, you can apply various lag length criteria, based primarily on the minimisation of Akaike or Schwartz information criteria. We will do videos on these sometime in the future when we go deeper into econometrics eventually. Hope it helps!

[@saipri]

@@NEDLeducation thanks for the clarification! your videos have been very helpful!!

[@jamesroche5934]

Can I run this test in conjunction with the runs test and variance ratio test in your previous videos if I am conducting a study for random walk behaviour?

[@NEDLeducation]

Hi James, yes, absolutely, this is a very common battery of tests used in market efficiency research.

[@anilgupta1426]

Need your help and guidance, I am using XLSTAT plugin and the ADF test result is different than the calculation of your with the same data of S&P500 need to understand where I am going wrong. can I share the spreadsheet, please?

[@tas3159]

Thanks, I love your channel. Aren't we suppose normally to to check for unit root on the price? why are you testing the returns where it is clear they are stationary?.-

[@NEDLeducation]

Hi, and glad you are enjoying the channel! Unit root tests can be applied to any time series, depending on what your objective is. Here, applying these to returns tests for market efficiency, as non-stationarity of these would imply clear dependencies. Hope it helps!

[@pradiptanugraha4828]

Thanks for sharing. As u know in Eviews there are 3 models to be included in test equation (intercept, trend and intercept, and None). If I get p0.05 (all is in same lags or on the same level/1st difference), can I say those are stationery? Or all 3 models should have p

[@NEDLeducation]

Hi Pradipta, and thanks for the question! Ultimately, it depends on the nature of your time series. If there is little reason to believe a series can have an intercept/trend/both, and the respective models accept the null, then you can comfortably stick with the simpler model that rejects the null and confirms stationarity. Overall, such "grey areas" are most of the time left for the interpretation and discretion of a researcher. Hope it helps!

[@pradiptanugraha4828]

@@NEDLeducation Oh thankss. And I want to ask 1 more.. If I want to use granger causality test for 1 dependent (GDP) and independent (CPI) from 36 quarterly data, should I test for normality, multicollinearity, heteroskedacity, autocorrelation? and the reason? Or just go straight from stationarity test and then granger?

[@zaig7401]

How can we apply dickey fuller test for cointegration in pairs trading?

[@NEDLeducation]

Hi Dingxin, thanks for the question! For cointegration in pairs trading, you need both series (stock prices) to HAVE unit roots. Therefore, before you apply any cointegration techniques, you need to ideally make sure that both stock prices have insignificant t-stats in a respective Dickey-Fuller test. Then, if you follow the Engle-Granger cointegration procedure, you can test for the stationarity of some linear combination of the two series (x + b*y) using a Dickey-Fuller test of your choice. Here, you can verify that cointegration is present if the combination is stationary (the t-stat is singnificant). We will definitely make a detailed video on cointegration and its applications to pair trading specifically in the near future.

[@austinliu667]

Why can we mix lagged reutrns and lagged return differences for ADF here? Doesn't it mean different things? The weak stationarity of price or return or even return growth rate?

[@NEDLeducation]

Hi Austin, and thanks for the question! It depends on the objective of the test. When investigating stationarity of prices, you can regress differences (log-differences) onto lagged levels (or their logs). This would most commonly show prices are not stationary (obviously). When investigating stationarity of returns, you can regress differences in returns onto lagged returns. This would most commonly show returns are stationary.

[@nourmaged123]

Hello, thanks for the video, really appreciate your effort. just a quick question, I applied the same procedure on a time series data of 1 minute interval, s&p returns as well, the t-stat was +88.xxx%, what can I conclude? (no trend approach)

[@Regular.Biceps]

What should be the sample size for checking ADF test? For instance, is it useful to calculate ADF test, say on 12 sample period, where a new observation is added and the last one removed (running data). Will that be useful for trading or is this sample size or a certain sample size too small to give a fair picture of time series ? Moreover, are the observations must be based on day's prices or can it be based on smaller timeframes like 1 hour or 15 minute

[@NEDLeducation]

Hi, and thanks for the question! ADF generally has a much higher power on large samples so I would suggest using a rolling sample of at least 30 or ideally 50 or 100 observations. It can be applied to high-frequency data (hourly or 15-minute candles) without any issues as well.

[@Regular.Biceps]

@@NEDLeducation what exactly is the trading implications of results ? For instance, if using m=3 filter, my approximate entropy is 0.40 What exactly is it telling me to do? My hypothesis is, that technical indicators become more reliable when approximate entropy values are low. Is it true ? (I've used your file for another instrument)

[@ramesdias6879]

Thank you. Is it wrong if the standard error is zero?

[@NEDLeducation]

Hi Rames, and thanks for the question. A standard error of zero is generally a problem. Please check which arrays you are referring to when estimating the regression. Alternatively, it can present itself as zero due to rounding (try increasing the number of decimal places in the representation).

[@ainaridzuan5669]

Hai, this is really helpful but may I know how to do first differencing of adf test using excel?

[@NEDLeducation]

Hi Aina, and glad you liked the video! First differencing can be done by simply subtracting lagged values and dropping the very first value.

[@ainaridzuan5669]

@@NEDLeducation got it, thank you so much!

[@anilgupta1426]

Great Learning!! Can I request you to make a video on Multivariate Regression? How to use it for Pair trading

[@NEDLeducation]

Hi Anil and thanks for the question! For pairs trading, cointegrating regressions are generally used. I will definitely make a video on it next week. Hope it helps!

[@anilgupta1426]

Thank you so very much ❤️

[@anilgupta1426]

Waiting for cointegrating regression sir!!

[@NEDLeducation]

The video on cointegrating regression and pair trading is the first in the line, you can expect it on Monday :)

[@anilgupta1426]

@@NEDLeducation Really really thank you so much!!!

[@nidhidhankhar8220]

How we can calculate p value in excel for this

[@NEDLeducation]

Hi Nidhi, and thanks for the question! As far as I am aware, there is no neat and tidy way of implementing the Dickey-Fuller tau distribution in Excel unfortunately, so the oldschool approach with looking up a value in a table will do.

[@pablosoriano8502]

Thanks for this video. Very clear. So once you realised that a serie has a unit root but using the first difference is stationary. You should now run the regression using the first difference. Right ? And is this possible to generate in eviews aswell?

[@NEDLeducation]

Hi Pablo and many thanks for the feedback! Glad the video helped. As for your question, identifying unit roots (or lack thereof) in your data is useful when deciding which models to apply. For example, for cointegrating regression, the series must have unit roots, but their linear combination should be stationary, so you need to make sure this is the case before applying the model (I will definitely make a video on cointegration and pairs trading sometime soon). For usual regression analysis, a series with a unit root is autocorrelated, so it is a good idea to use its first difference as a dependent variable instead. In EViews, you can compute first differences of any series (let's call it "series") using the notation d(series). For example, you can regress first differences of y onto x using the command "d(y) c x". Hope it helps!

[@pablosoriano8502]

@@NEDLeducation Thank you so much!

[@carlamausi]

@@NEDLeducation Hi, thank you so much for your useful videos! Just to be clear, I have one more question regarding this topic. I'm currently estimating a time series model with up to 4 independent variables. The first difference of my dependent variable is stationary. The independent variables are stationary on level. Do I now use the first difference for the dependent variable and leave the independent variables as they are?

[@NEDLeducation]

@@carlamausi Hi Carla, and glad you liked the video! As for your question - yes, you can now estimate the model with the first difference for the dependent variable and levels of independent variables. Hope it helps!

[@carlamausi]

@@NEDLeducation Thank you so much for your quick answer! Couldn't find the answer in any textbook

[@Gavandeee4655]

Hello sir, how can I contact you please tell me.. I need your help in this please🙏

[@elijahkengura2021]

You deriver as usual

[@meenaltayal798]

Sir what will be null hypothesis here

[@NEDLeducation]

Hi Meenal, and thanks for the question! The null hypothesis is that the time series has a unit root (is non-stationary). The alternative hypothesis is the absence of unit root (stationarity). Hope it helps!

[@meenaltayal798]

Sir the value of adf test shows that unit root is not present in an ar model sir does it mean it is a ar(0) model

[@alexanderlang9900]

But you use the difference in return… i dont understand why you say the opposit at the end?

[@joaovictornc2572]

Fala querido Guerra, achei um vídeo em que o autor fala como achar o star t de forma que é possível generalizar no excell. Ele utiliza a função proj.lin (traduzido). Vou colocar o link do vídeo aqui:kzbin.info/www/bejne/gXSpfZmHfb-Ceq8

[@AlikeKZ]

На английском не понятно. Жаль...

[@NEDLeducation]

Планируем в скором времени добавить русские субтитры, не переживайте :)

[@user-qs4ud9zk4o]

not easy to understand, as a beginner

[@user-qs4ud9zk4o]

not easy to understand , as a begineer