Solved: Macro to compute Lagged Correlations

[Nick_London]

Hi,

I have an Excel sheet with 15 variables, starting in Column A and ending Column O. Row 1 as variable names and the remainder of colums has 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. The key thing here is that I need 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 correlation 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 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 cols at different lags. The output doesn't have to be exactly like this though. Even if the macro takes 5 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

Nick

[mdmackillop]

Hi Nick,
Welcome to VBAX
While I have no idea about Correlations, this would be easier to test with an example. Can you post a sample with working data? Use Manage Attachments in the Go Advanced section. You can add notes to the spreadsheet to clarify further what you're looking for.
Regards
MD

[acw]

Nick

Based on your data covering columns A:O, try


Sub aaa()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
startrow = 2

'insert headings
For i = 0 To 6
Range("R2").Offset(i, 0).Value = "Lag " & i
Next i
'insert first formula
Range("S2").Formula = "=correl($a2:$a" & lastrow & ",b2:b" & lastrow & ")"
'insert incremental formulas
For i = 1 To 6
Range("S2").Offset(i, 0).Formula = "=correl($a" & startrow + i & _
":$a" & lastrow & ",b2:b" & lastrow - i & ")"
Next i

'fill formulas
Range("S2:S8").AutoFill Destination:=Range("S2:af8")
End Sub



Tony

[Nick_London]

Hi Tony,

Thanks for the code. It does exactly what I need. I have ammended it to the below to give me 10 lags worth of correlations (six in your code). I'd like take this code a step further. Instead of having the number of variables to calculate fixed as based on the code, I like to it make more flexible by letting the user select the columns employing some kind of input box.

So when you run the macro it would give you an input box to select data range:

Set RANGE = Application.InputBox("Select data range", "data selection", Type:=8) SO HERE WOULD GO COL A:0, or A:Q IF YOU WANT TO INCLUDE MORE VARIABLES

Then the user could select where to display the ouput (or this could be fixed)

Set result = Application.InputBox("Select output results", "data selection", Type:=8) 'user selection for output - HERE YOU SELECT OUTPUT - SAY Z1

They key thing about this macro is it would allow the number of columns to vary, the user can just select the input range and the macro will work out the correlations for each variable selected instead of having to go into the code and manually change it to have more or less variables.

I know how to create the input boxes but don't know how to intergrate the underlying code.

I'm assuming this is something easy to do?

Thanks,

Nick

Sub Calculate_Lagged_Correlations_10_Lags()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
startrow = 2

'insert headings
For i = 0 To 10 'lag headings
Range("R2").Offset(i, 0).Value = "Lag " & i
Next i
'insert first formula
Range("S2").Formula = "=correl($a2:$a" & lastrow & ",b2:b" & lastrow & ")"
'insert incremental formulas
For i = 1 To 10 'modify to change number of lags
Range("S2").Offset(i, 0).Formula = "=correl($a" & startrow + i & ":$a" & lastrow & ",b2:b" & lastrow - i & ")"
Next i

'fill formulas
Range("S2:S12").AutoFill Destination:=Range("S2:af12") 'change range here to reflect number of lags to use
End Sub

[acw]

Hi

See if the following gets you started.


Sub Calculate_Lagged_Correlations_Variable_Lags()
Dim DataRng As Range, OutRng As Range
Set DataRng = Application.InputBox("Select data range", "data selection", Type:=8)
Set OutRng = Application.InputBox("Select output results", "data selection", Type:=8)
firstcol = DataRng.Column
lastrow = Cells(Rows.Count, firstcol).End(xlUp).Row
startrow = 2
'insert headings
For i = 0 To DataRng.Columns.Count 'lag headings
OutRng.Offset(i, 0).Value = "Lag " & i
Next i
'insert first formula
OutRng.Offset(0, 1).Formula = "=correl(" & Range(Cells(startrow, firstcol), Cells(lastrow, firstcol)).Address & "," & Range(Cells(startrow, firstcol + 1), Cells(lastrow, firstcol + 1)).Address(rowabsolute:=False, columnabsolute:=False) & ")"
'insert incremental formulas
For i = 1 To DataRng.Columns.Count 'modify to change number of lags
OutRng.Offset(i, 1).Formula = "=correl(" & Range(Cells(startrow + i, firstcol), Cells(lastrow, firstcol)).Address & "," & Range(Cells(startrow, firstcol + 1), Cells(lastrow - i, firstcol + 1)).Address(rowabsolute:=False, columnabsolute:=False) & ")"
Next i
'fill formulas
'Range("S2:S12").AutoFill Destination:=Range("S2:af12") 'change range here to reflect number of lags to use
OutRng.Offset(0, 1).Resize(DataRng.Columns.Count + 1, 1).AutoFill Destination:=Range(OutRng.Offset(0, 1), OutRng.Offset(DataRng.Columns.Count, DataRng.Columns.Count - 1))
End Sub



I don't think I have the number of lags (and therefore headings) right as I've just made it relative to the number of columns selected.

But it may get you started.


Tony

[Nick_London]

Hi Tony,

Sometime ago you posted some code to help me calculate lagged correlations in Excel using a macro. I've ammended the orginal code you posted to accomadate calculating 10 lags worth of data, this works fine but currently the macro on the whole works on a very tempremental basis and if you select more than four columns of data it never ever works. It keeps returning the runtime error 424 "Object required" and debugging takes you to this line in the code:

Set DataRng = Application.InputBox("Select data range", "data selection", Type:=8)

It was returning the same error even before I ammended the code to return ten lags of data. Do you know what could be causing it? I've tried to work it out myself but I just seem to be going round in circles. Hope you can help.

Thanks

Nick

-------------------------------------------------------------------



Sub test()
'I don 't think I have the number of lags (and therefore headings) right as I
'just made it relative to the number of columns selected.
'But it may get you started.
Dim DataRng As Range, OutRng As Range
Set DataRng = Application.InputBox("Select data range", "data selection", Type:=8)
Set OutRng = Application.InputBox("Select output results", "data selection", Type:=8)
firstcol = DataRng.Column
lastrow = Cells(Rows.Count, firstcol).End(xlUp).Row
startrow = 3
'insert headings
For I = 0 To 10 'no of lag headings needed
OutRng.Offset(I, 0).Value = "Lag " & I
Next I
'insert first formula
OutRng.Offset(0, 1).Formula = "=correl(" & Range(Cells(startrow, firstcol), _
Cells(lastrow, firstcol)).Address & "," & Range(Cells(startrow, firstcol + 1), _
Cells(lastrow, firstcol + 1)).Address(rowabsolute:=False, columnabsolute:=False) & ")"
'insert incremental formulas
For I = 1 To 10 'modify to change number of lags needed
OutRng.Offset(I, 1).Formula = "=correl(" & Range(Cells(startrow + I, firstcol), _
Cells(lastrow, firstcol)).Address & "," & Range(Cells(startrow, firstcol + 1), _
Cells(lastrow - I, firstcol + 1)).Address(rowabsolute:=False, columnabsolute:=False) & ")"
Next I
'fill formulas
'Range("S2:S12").AutoFill Destination:=Range("S2:af12") 'change range here to
'reflect number of lags to use
OutRng.Offset(0, 1).Resize(DataRng.Columns.Count + 1, 1).AutoFill _
Destination:=Range(OutRng.Offset(0, 1), OutRng.Offset(DataRng.Columns.Count, _
DataRng.Columns.Count - 1))
End Sub

[mdmackillop]

Hi Nick,
When you post code, select it and click the VBA button to format it as shown. Also, use line breaks in long lines of code to avoid the need to scroll.
Regards
MD