Calculate Correlations Based on Changing Parameters

[LucasLondon]

Hi,

I have data in a the attached workbook (fictional for illustrative purposes only) that I am using to calculate correlations based on data in column D and Column E.

The correlation outputs is shown in H10.

I would like to make column D stat based on taking different moving averages of column C. So currently the data in column D is based on a moving average of 10 of col C.

I would like to run the correlation based on moving averages to say 2 to 20 and show the different results and display in the output tab.

I guess I need to

a) Work out a way of making the moving average formula in column D dynamic based on differerent MA values. I guess I need to link this to cell G3. So if G3 = 7, column D will calculate based on MA 7.

b) Loop through each different MA value and record the results somewhere for example in the output tab.

Can anyone suggest the most efficient/fastest way of doing this with or without code.

Thanks,

Lucas

[CatDaddy]

Sub recalculate()
Dim g As Integer, lr As Integer, r As Integer
Dim cell As Range
ActiveWorkbook.Sheets(1).Activate
g = Range("G3").Value
lr = Range("C" & Rows.Count).End(xlUp).Row
For Each cell In Range("C2:C" & lr)
If cell <> "" And cell.Offset(-1, 0) = "" Then
r = cell.Row
Exit For
End If
Next cell
For Each cell In Range("D" & (r + g) & ":D" & lr)
cell.FormulaR1C1 = "=AVERAGE(R[-" & (g - 1) & "]C[-1]:RC[-1])"
Next cell
End Sub

this doesnt loop through each ma value let me add that

[CatDaddy]

Sub recalculate()
Dim g As Integer, lr As Integer, r As Integer, r2 As Integer
Dim cell As Range, cell2 As Range
ActiveWorkbook.Sheets(1).Activate
For Each cell2 In Sheets("Output").Range("A2:A9")
r2 = cell2.Row
g = cell2.Value
lr = Range("C" & Rows.Count).End(xlUp).Row
For Each cell In Range("C2:C" & lr)
If cell <> "" And cell.Offset(-1, 0) = "" Then
r = cell.Row
Exit For
End If
Next cell
For Each cell In Range("D" & (r + g) & ":D" & lr)
cell.FormulaR1C1 = "=AVERAGE(R[-" & (g - 1) & "]C[-1]:RC[-1])"
Next cell
Sheets("Output").Range("B" & r2).Value = Range("H3").Value
Next cell2
End Sub

[LucasLondon]

Thanks CatDaady for the code.

I'm slightly confused as the numbers I am getting when doing this manually are different from yours based on the code!

I suspect this is due to slight differences in methodolgy!

MACorrelationManual Calculation9-0.20413-0.2041310-0.20627-0.2063911-0.20889-0.2174912-0.20895-0.2202713-0.20828-0.1982614-0.20576-0.1769915-0.20198-0.1683616-0.1982-0.16285

In terms of how I am doing it - see columns K to M for examples (MA 10, MA11 and MA 5 respectively).

Anyway to match the same methodolgy in the code

Thanks,

Lucas

[CatDaddy]

I'm not sure what you are getting at, you were using the average function correct? does your formula in cell H3 need to change each time the rolling average value changes perhaps?

[LucasLondon]

Hi,

Yes I'm using the average function to calculate the moving average but it's the parameters in here that need to change. The correlation formula in g3 does not need to change at all, i.e formula in row 3 of K,L,M and N are the same as H3 which is fine.

I only need to change the formulas that feed the average function in column D as per examples in col K to O (I've added in some more examples to make clearer). So for 10 period the avg is based on c15-c24, 11 period - c15-c25, 12 period - c15 -26 etc. For a five period the average is based on A20 to A24. This is because the values in column E do not begin until E24 so I only need to go five periods back likewise for column O based on 4 period averages.

Hope this clarifies what I'm trying to do and how I would like the formulas to work.

Thanks

Lucas

[CatDaddy]

I dont see how my code doesnt do what your looking for, I'm sorry maybe I'm missing something?

[LucasLondon]

Sorry,

I had to put this project on the back burner because something unexpected came up. Before posting again, I'll create some examples of how my results differ from the VBA and attempt to understand why.

Lucas

[Bob Phillips]

Sub recalculate()
Dim g As Integer, lr As Integer, r As Integer, r2 As Integer
Dim cell As Range, cell2 As Range

ActiveWorkbook.Sheets(1).Activate

Application.Calculation = xlCalculationManual

For Each cell2 In Sheets("Output").Range("A2:A9")

r2 = cell2.Row
g = cell2.Value
lr = Range("C" & Rows.Count).End(xlUp).Row
For Each cell In Range("C2:C" & lr)

If cell <> "" And cell.Offset(-1, 0) = "" Then

r = cell.Row
Exit For
End If
Next cell

Cells(r, "D").Resize(g - 1).ClearContents
For Each cell In Range("D" & (r + g - 1) & ":D" & lr)

cell.FormulaR1C1 = "=AVERAGE(R[-" & (g - 1) & "]C[-1]:RC[-1])"
Next cell

ActiveSheet.Calculate

Sheets("Output").Range("B" & r2).Value = Range("H3").Value
Next cell2

Application.Calculation = xlCalculationAutomatic
End Sub

[LucasLondon]

Hi XLD.

I have now manage to test the code you provided against different sets of data and gives the same result as when doing caculations manually so it works exactly how I wanted it to.

And despite reading up on it, I am though quite confused by the R1C1 style formula. I tried converting to the regular type but could not get it to give me the right results.

cell.FormulaR1C1 = "=AVERAGE(R[-" & (g - 1) & "]C[-1]:RC[-1])"

Thanks,

Lucas

[Bob Phillips]

Sorry Lucas, I am not quite clear as to what you need here. What can I do to help you get to where you want to be?

[LucasLondon]

Oh sorry for confusion, may not have expressed myself clearly.

The code works fine but I wanted to translate this part of the code - the R1C1 style formula - to normal type, e.g =average(C1:C5) but I'm not sure how to reconstruct (i.e where the g goes etc).

cell.FormulaR1C1 = "=AVERAGE(R[-" & (g - 1) & "]C[-1]:RC[-1])"

Cheers,

Lucas

[Bob Phillips]

Why do you want A1 notation? R1C1 works much better in this case.

[LucasLondon]

Hi,

I'm just use to the A1 format and so it's easier for me to make changes to the formula whereas I find the R1C1 format much more confusing. But if you think R1C1 is more efficient for this macro to work, I'll leave it as such.

Thanks,

Lucas

[Bob Phillips]

Not more efficient, more flexible. Because R1C1 is using numbers for both column and row, whereas A1 has text columns, it better facilitates the inclusion of variables.

[LucasLondon]

OK, but wouldn't using cell references give the same flexibility in terms of numbers for both columns and rows?

e.g. Range(cells(1,4),cells(50,4))

Lucas

[Bob Phillips]

Yes, it would in that example, but we are talking about creating formulas in worksheet cells, comparing A1 to R1C1 - not the same beast.