Calculating Lagged Correlations Matrix using Macro

[Jamyhamy]

I have an Excel sheet with 15 variables, one per each column. Row 1 as variable names and the remainder of coloums 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 function takes two arguments:

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

To work out the correlation 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 teh 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 cols 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

 

[Bulsara]

Mate,

Have you tried a stats/econometric package such as Eviews? I'm sure that would offer the functionalty you need.

If not, you should be able to do it quite easily in Excel because you already have the built in function to work out the correlations, you just need some code that automates the processing.

H

 

[Jamyhamy]

Macro to Calculate Cross Correlations

Right,

This is what I was thinking. One way I guess to do this would be to run each variable through the Correl function adjusting the range in the formula using the offset command each time to work out the lagged correlations. Add then apply thee same procedure through some kind of loop to all the other variables.

Problem is I am completely new to VBA, have only worked with recorded macros. I am familiar with using these functions directly within the spreadsheet but not in a VBA context. Can anyone help with the code?

I do not have access to any statistical software but most software packages I have used in the past at university don't even offer this functionalty.

Thanks,

Jamy Hamy

 

[tusharm]

No need for VBA, no need for any other software -- well, to the extent that one trusts XL's functions to be accurate.

Suppose your data are in A:F (I used only 6 variables for testing). Now, suppose in H you have the lag specified. So, H2 contains 0, H3 contains 1, H4 2, etc. Also, suppose I1:N1 contain the variable names (the same names as in A1:F1).

Then, in I2 enter the formula =CORREL(OFFSET($A$2,H2,0,COUNT($A:$A)-$H2,1),OFFSET($A$2,0,MATCH(I$1,$A$1:$F$1,0)-1,COUNT($A:$A)-$H2,1))
Copy I2 to J2:N2. Then, copy I2:N2 as far down as you have numbers in H.

The formula automatically adjusts for however many rows of data you have in the original data set. You will have to adjust it to account for the appropriate starting column.

 

[Jamyhamy]

Re: Calculating Lagged Correlations - with varying column lengths

Hi,

As suggested by Tusharm, I'm using the following formula in Column C1991 to Column AD2020 to calculate lagged correlations between data in column B and variables in the other columns. The formula below lets me calculate the relationship between lagged values in the columns (C to AD) and column B based on the lag number specified in Column A.

=CORREL(OFFSET(C$3,$A1991,0,COUNT($B$3:$B$1985)-$A1991,1),OFFSET($B$3,0,MATCH(C$1990,C$1:$AG$1,0)-1,COUNT(C$3:C$1985)-$A1991,1))

However this setup only works when the number of observations in column B versus the other columns is the same. But now I have data that has different lengths of data. For example, data for the Column B variable starts in B3 and ends in B359 - that's 357 obsrervations. The maximum number of observations that any of other columns can have is 357 but some can have much fewer than this. For example, column C data is C3:C358, Column D is D269:D358.

None of the correlations therefore calculate. Is it possible to amend the formula to overcome this so that the rows to analyse are determined automatically based on the start and end of each variable in columns C to AD?

Hope someone can help.

Thanks,

Jamyhamy