Macro for correlation matrix with varying data length

[Stebaker]

Hi All,

I'm trying to create a macro to calculate a correlation matrix of data series.

I have a worksheet called "All" with 17 columns of data, each column is a data series of numbers.

I would like to create a 17 x 17 correlation matrix in another sheet for the multiple data series using the Pearson formula.

Depending on a user input "TimePeriod" (a number from 1-7), the number of rows of data in "All", used for the calculation, varies.

So far I have:

Dim TimePeriod, rowcount, colcount, EndRow As Integer
Dim myrange1, myrange2 as Range

TimePeriod = Worksheets("Corr").Cells(9, "B").Value

End_Row = IIf(TimePeriod = 1, 13, IIf(TimePeriod = 2, 25, IIf(TimePeriod = 3, 69, IIf(TimePeriod = 4, 137, _
        IIf(TimePeriod = 5, 247, IIf(TimePeriod = 6, 507, 1005))))))


' I would now like to cycle through the 17x17 cell range and calculate the correlation values in a macro, I guess using a couple of "for" statements.

With Range("E27:G29")
    
    For rowcount = 1 to 17
        For colcount = 1 to 17

            myrange1 = [rowcount][1]:[rowcount][EndRow] ' incorrect code but I require help (at least) here
            myrange2 = [colcount][1]:[colcount][EndRow]   ' and here
                .Value = Application.WorksheetFunction.Pearson(myrange1, MyRange2)
        Next colcount
    Next rowcount
End With 
End Sub

My problem stems from the use of syntax for the range in the Pearson parameters, and entering a letter to represent the column during the loops. Can I use a numerical reference to the columns or can I loop through the Column letters?

Could any of you guys possibly help?

Thanks very much

Steve

 

[shg]

On what sheet does the data appear, starting in what cell?

Do the 13, 25, 69 ... values mark the number of rows of data or the last row of data?

On what sheet should the output appear, starting in what cell?

You want to calculate the correlation of each column of the data with each other column?

 

[AlphaFrog]

This is an example of the syntax you can use to define a range of cells using integers. I don't follow exactly what range you want to define.

            myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))

 

[shg]

 myrange1 = Range(Cells(rowcount, 1), Cells(rowcount, colcount))

In the code, myrange1 is a Variant and myrange2 is a Range. That line would return the values of the first range to the Variant (which would be fine), but the next line would give a run-time error.

 

[Stebaker]

Hi,

Thanks for quick responses....

shg4421

• data should appear E27:G29 on my active sheet (called "Corr")
• data series appears in sheet called "All", range B2:Rx
• x = EndRow defined by user (13, 25,...)
• Yes, a standard correlation matrix with 1's on the top left to bottom right diagonal

AlphaFrog

Thanks for the line of code, I have tried this below, but there is an error with

 myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))

Sub CorrMatrix()
'
'

'
    
Dim TimePeriod, rowcount, colcount, EndRow As Integer
Dim myrange1, myrange2 As Range

TimePeriod = Worksheets("Corr").Cells(9, "B").Value

End_Row = IIf(TimePeriod = 1, 13, IIf(TimePeriod = 2, 25, IIf(TimePeriod = 3, 69, IIf(TimePeriod = 4, 137, _
        IIf(TimePeriod = 5, 247, IIf(TimePeriod = 6, 507, 1005))))))


' I would now like to cycle through the 17x17 cell range and calculate the correlation values in a macro, I guess using a couple of "for" statements.

With Worksheets("Corr").Range("E27:G29")
    
    For rowcount = 1 To 17
        For colcount = 1 To 17
        
            myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))
            myrange2 = Range(Worksheets("All").Cells(1, colcount), Worksheets("All").Cells(EndRow, colcount))
            .Value = Application.WorksheetFunction.Pearson(myrange1, myrange2)
            
        Next colcount
    Next rowcount
                
End With

End Sub

Cheers Guys!

Steve

 

[shg]

Try this:

Sub x()
    Dim iPer        As Long
    Dim lRow        As Long
    Dim rInp        As Range
    Dim iCol1       As Long
    Dim iCol2       As Long
 
    iPer = Worksheets("Corr").Range("B9").Value
    If iPer < 1 Or iPer > 7 Then Exit Sub
    
    lRow = Choose(iPer, 13, 25, 69, 137, 247, 507, 1005)
    With Worksheets("All")
        Set rInp = .Range("B2", .Cells(lRow, "R"))
    End With
 
    With Worksheets("Corr").Range("E27")
        For iCol1 = 1 To 17
            For iCol2 = 1 To 17
                .Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
                                             rInp.Columns(iCol1).Value, _
                                             rInp.Columns(iCol2).Value)
            Next iCol2
        Next iCol1
    End With
End Sub

 

[AlphaFrog]

Need the Set keyword. Sorry.

[COLOR="Red"]Set [/COLOR]myrange1 = Range(Worksheets("All").Cells(1, rowcount), Worksheets("All").Cells(EndRow, rowcount))

Or this...

With Worksheets("All")
   [COLOR="Red"]Set[/COLOR] myrange1 = .Range(.Cells(1, rowcount), .Cells(EndRow, rowcount))
End With

 

[Stebaker]

Thanks again for replies...

shg4421 your code works great, apart from when the user input = 1 there is a time out code 1004 on the line:

.Cells(iCol1, iCol2).Value = WorksheetFunction.Pearson( _
                                             rInp.Columns(iCol1).Value, _
                                             rInp.Columns(iCol2).Value)

Seems a strange error, and can't understand why it occurs???¿¿¿ Otherwise perfect, thanks a lot!

Cheers

Steve

 

[shg]

That's curious -- 1 is the only value I tested with, and it worked fine.

All the values in the input range must be numbers.

 

[Stebaker]

Resolved... first 7 rows of series data missing in "All" - empty rows deleted and this now works perfectly as per your testing!

Thanks again for your help, awesome...

S