Good day,
Had to return to VBA coding because I'm a bit embarrassed these days, and want to save time. It's been a long time that I didn't code and I can't find the solution. I'm requesting your help.
I have basically 3 columns, one for a data set(daily), one for a price_1 and the other one for price_2
What I want is to create a correlation matrix per month.
Example: For each month (MMMM) and year(YYYY) in my dataset, put me the right month correlation.
Note: the date 11/30/2015 is on range("A4")
<tbody>
</tbody>
<tbody>
</tbody>
So basically, to compute correlation I use =correl()
Now here's my code. I can't find any solution to manage to get the good result:
Sub Correlation_matrix()
Dim Column_A, Column_B, Column_C, Date_reference_A, Date_reference_B, Date_reference_C, Selection_B, Selection_C As Range
Dim columnA_cell As Range
'Dim selection_cells As Range
Dim result_cell As Range
Dim n As Integer
Set Date_reference_A = Range("A4")
Set Date_reference_B = Range("B4")
Set Date_reference_C = Range("C4")
Set result_cell = Range("F5")
Set Column_A = Range(Date_reference_A, Date_reference_A.End(xlDown))
Set Column_B = Range(Date_reference_B, Date_reference_B.End(xlDown))
Set Column_C = Range(Date_reference_C, Date_reference_C.End(xlDown))
For Each columnA_cell In Column_A
For n = 0 To 22
If Month(columnA_cell) <> Month(columnA_cell.Offset(n, 0)) Then
'Set Selection_B = Range(Column_B.Offset(0, 0), Column_B.Offset(n, 0))
'Set Selection_C = Range(Column_C.Offset(0, 0), Column_C.Offset(n, 0))
result_cell = WorksheetFunction.Correl(Column_B, Column_C)
End If
Next
Exit For
Next
End Sub
Thanks for your kind help,
Had to return to VBA coding because I'm a bit embarrassed these days, and want to save time. It's been a long time that I didn't code and I can't find the solution. I'm requesting your help.
I have basically 3 columns, one for a data set(daily), one for a price_1 and the other one for price_2
What I want is to create a correlation matrix per month.
Example: For each month (MMMM) and year(YYYY) in my dataset, put me the right month correlation.
Note: the date 11/30/2015 is on range("A4")
Date | P_1 | P_2 |
11/30/2015 | 1 | 66 |
11/27/2015 | 2 | 65 |
11/25/2015 | 5 | 65 |
11/24/2015 | 89 | 65 |
10/30/2015 | 1446 | 11546 |
10/29/2015 | 1145 | 1164 |
10/28/2015 | 145687 | 5 |
<tbody>
</tbody>
Nov Correlation | -0.3588 |
Oct Correlation | -0.05056 |
<tbody>
</tbody>
So basically, to compute correlation I use =correl()
Now here's my code. I can't find any solution to manage to get the good result:
Sub Correlation_matrix()
Dim Column_A, Column_B, Column_C, Date_reference_A, Date_reference_B, Date_reference_C, Selection_B, Selection_C As Range
Dim columnA_cell As Range
'Dim selection_cells As Range
Dim result_cell As Range
Dim n As Integer
Set Date_reference_A = Range("A4")
Set Date_reference_B = Range("B4")
Set Date_reference_C = Range("C4")
Set result_cell = Range("F5")
Set Column_A = Range(Date_reference_A, Date_reference_A.End(xlDown))
Set Column_B = Range(Date_reference_B, Date_reference_B.End(xlDown))
Set Column_C = Range(Date_reference_C, Date_reference_C.End(xlDown))
For Each columnA_cell In Column_A
For n = 0 To 22
If Month(columnA_cell) <> Month(columnA_cell.Offset(n, 0)) Then
'Set Selection_B = Range(Column_B.Offset(0, 0), Column_B.Offset(n, 0))
'Set Selection_C = Range(Column_C.Offset(0, 0), Column_C.Offset(n, 0))
result_cell = WorksheetFunction.Correl(Column_B, Column_C)
End If
Next
Exit For
Next
End Sub
Thanks for your kind help,