Partial autocorrelation

[brian.field]

Hi @David Harper CFA FRM

I am a bit confused by the description that partial autocorrelation function as the coefficient if the most distant term after regressing the current in the past series. Don't we have only one series here, and therefore, only 1 sample point for each independent variable?

For example, say series (X3 at time -3 = -1.2, X2 at time -2 = -0.4, X1 at time -1 = 1, and the current value of the series at time 0 = 0.5). Then we would have:

X3 = -1.2
X2 = -0.4
X1 = +1.0
Y = 0.5

Then how would we regress Y on the Xs here? Can you provide a small example in excel illustrating the determination of the partial autocorrelation function?

 

[brian.field]

Found a fantastic reference.

http://pecar-uk.com/Autocorrelations.pdf

 

[jairamjana]

@brian.field thank you sir for the pdf file which explains the matrix inverse method for calculating PAC Function in such detail.. Of course we have R and other software which does the job much quickly but still learning through Excel is learning stuff the hardway..
My question is
Given these formulas

ACF =SUMPRODUCT($D$3:INDEX($D$3:$D$52,ROWS(D4$52)),$D4$52)/DEVSQ($D$3:$D$52).
or
=SUMPRODUCT(OFFSET($D$3:$D$52,0,0,COUNT($D$3:$D$52)-A3),OFFSET($D$3:$D$52,A3,0,COUNT($D$3:$D$52)-A3))/DEVSQ($D$3:$D$52)

How do i form a Correlation matrix as shown in Page 17 of the pdf.. Is there a quick fix to this in excel instead of manually sorting the cells according to the requirement?

 

[jairamjana]

Ok I just got the way to do a ACF and PACF test in excel.. you can use the excel if you want to to test for autocorrelation and Partial Auto Correlation for whatever variable in A as input... I don't know anything about VBA codes so this is the best I could.. You could do upto 11 Lags .. Useful for making a correlogram.. This excel was too much work but learnt a lot in the process..

Hope you appraciate it..

 

[brian.field]

Sweet! I'll take a look and offe mr suggestions, if warranted. I hadn't had a chance to respond to your previous post.

 

[jairamjana]

@brian.field no problem.. it so happens I was looking for a way to do the Yule Walker in matrix form and how to incorporate in excel.. And I saw your post and also the pdf link.. That helped me a lot and with a little bit of tweaking I could do it in a much more refined way..
Actually there is a online calculator as well..
http://www.wessa.net/rwasp_autocorrelation.wasp

But I have not come across anyone demonstrating the PACF in excel in youtube or anywhere.. I know there are more sophiscated tools available.. But for me excel helps me to grasp the mathematical way of doing it.. So I spent a total of 2 days..

 

[brian.field]

Thanks for putting it together. Excel is my strongest skill set, so I will take a look and see if I can make it more efficient and/or add to it. If so, I will repost to you. Thanks again.

 

[jairamjana]

Well I made it so that you should only touch Column A ... Btw I named Column A as VariableA for convenience sake.. I don't know if the excel file also stores the keys in formula name manager so any third person can make use of it... So see to it that the name box displayed while highlighting Column A says it as VariableA. Or manually set it like that... Hope that's clear..
Basically delete the ones highlighted in blue.. Only VariableA should remain..

And thank you for testing it out.. I don't like that the ACF Matrix has a lot of formula inputs clustered.. Hope you can try simplify and make it more elegant..