PDA

View Full Version : Calculating Correlations using VBA



LucasLondon
05-16-2006, 02:59 AM
I'm seeking to obtain/write some code that will output correlations between variablein Columns A and other variables in other columns, including automatic calculation of correlations at different lags.

Currently I have an Excel sheet with 15 variables, one per each column. Row 1 as variable names and the remainder of columns have equal number of rows of time series data.

I'm looking tro automate calculating the cross correlations between variable 1 in Colum A and all other variables in the other remaining columns. But I want to calculate lagged as well as contemperaous correlations between the variables.

Right now, I'm calculating the correlations using excel's Correl function, manually adjusting the series range in the formula every time I want to work out the lagged correlations. For example, the correl function takes two arguments:

Array1, Array2
So correl (A2:A100, B2:B100) gives me the correlation between Col A and Col B at zero lags, likewise correl (A2:A100, c2:c100) gives me correlation for col C vs. Col A Etc

To work out the correlation between column A and Column B at 1 Lag I change the formula to (A3:A100, B2:B99), at lag 2 the formula becomes (A4:A100, B2:B98) etc. Then I do this for all the other columns.

Is there anyway to automate this so that it work out the correlations up to say 10 lags of for each variable against the variable in column A and pastes the results in some kind of readble grid in an excel sheet as per below:

A B C D E F

Lag0 X X X X X
Lag1 X X X X X
Lag2 X X X X X
Lag3 X X X X X
Lag4 X X X X X
Lag5 X X X X X
Lag6 X X X X X


Where X represents the correlation coefficient between Col A and the other columns at different lags. The output doesn't have to be exactly like this though. Even if the macro takes 10 mins to run - it would save a hell a lot of time doing it manually. I have looked at the correlation tool featured in Excels Anaysis Tool Pack Add-in but it doesn't give me what I need.

If anyone can help that would be great.

Thanks

Jamyhamy