CODEWITHSUNDEEP
vbaexcelcorrelation
Kristina
Kristina

Reputation: 699

Creating a correlation matrix via Excel VBA

What would be the best way to create a correlation matrix via Excel VBA? My data has 45 columns (which may eventually change) and 12000 rows (which can change as well). I was going to just use the correl function on the sheet but like I said, my columns and rows may change with time.

Any help will be greatly appreciated!

Upvotes: 3

Views: 15209

Answers (4)

Rho
Rho

Reputation: 21

This pair of functions give you the result in a matrix (select the range where it should appear, introduce the formula, then press F2 and then Ctrl+shift+enter to see the values). Copy both and paste on the VBA editor.

'function to create a correlation matrix given the data
Function CorrMatriz(Mat_data As Variant)
Dim i As Integer, j As Integer, corr As Variant, M1 As Variant, M2 As Variant
ReDim corr(1 To Mat_data.Columns.Count, 1 To Mat_data.Columns.Count)

ReDim M1(1 To Mat_data.Rows.Count, 1 To 1)
ReDim M2(1 To Mat_data.Rows.Count, 1 To 1)

For i = 1 To Mat_data.Columns.Count
    M1 = ExtraeMatriz(Mat_data, i)
    For j = 1 To Mat_data.Columns.Count
        M2 = ExtraeMatriz(Mat_data, j)
        corr(i, j) = Application.Correl(M1, M2)
    Next j
Next i
CorrMatriz = corr
End Function`

' function to extract one column
enter code here
Function ExtraeMatriz(Matriz As Variant, columna As Integer)
Dim i As Integer, data_final As Variant
ReDim data_final(1 To Matriz.Rows.Count, 1)
For i = 1 To Matriz.Rows.Count
    data_final(i, 1) = Matriz(i, columna)
Next i
ExtraeMatriz = data_final
End Function

Upvotes: 2

Moreno
Moreno

Reputation: 638

It works fine: 

Option base 1  
      Function MCorrelation(rango As Range) As Variant
        Dim x As Variant, y As Variant, s As Integer, t As Integer, c() As Variant
        ReDim c(rango.Columns.Count, rango.Columns.Count)
        For i = 1 To rango.Columns.Count Step 1
         For j = 1 To i Step 1
        c(i, j) = Application.Correl(Application.Index(rango, , i), Application.Index(rango, , j))
         Next j
        Next i
         MCorrelation = c
        End Function

Upvotes: 0

Algos
Algos

Reputation: 69

I have searched the web for VBA Correlation Matrix Code and havent found anything of substance. Did some coding myself, it aint beautiful, however it does the job. This code will create a matrix to the right of the last data series.

Sub CorrelationMatrix()

Dim y As Range
Dim z As Range

funds = Application.Workbooks("VBAcorrelation").Worksheets("Sheet1").Cells(1,       Columns.Count).End(xlToLeft).Column
rader = 0
For x = 1 To funds
        nyrad = Cells(Rows.Count, x).End(xlUp).Row
        If nyrad > rader Then
        rader = Cells(Rows.Count, x).End(xlUp).Row
    End If
Next x



p = 1
u = 2

For h = 1 To funds
For u = 1 To funds

        Set y = ActiveSheet.Range(Cells(2, h), Cells(rader, h))
        Set z = ActiveSheet.Range(Cells(2, u), Cells(rader, u))

  Correl = WorksheetFunction.Correl(y, z)

 Worksheets("Sheet1").Cells(h + 1, funds + u + 3).Select
 ActiveCell = Correl

 Next u
 Next h

MsgBox "Done with Matrix"
End Sub

Upvotes: 4

4pie0
4pie0

Reputation: 29734

 Application.Run "ATPVBAEN.XLAM!Mcorrel", ActiveSheet.Range("$C$3:$F$6"), _
    ActiveSheet.Range("$C$10"), "K", False //"K" might be "C"=column

to run this you have to enable Data Analysis Toolpack (package) first. You can use this via UI, tab Data Analysis->correlation matrix

here:

"$C$3:$F$6" - input (square matrix)
$C$10 - output cell
"K" (or "C") - group by columns
false - labels=no

Upvotes: 4

Related Questions