correlation matrix vba step by step

[milyenabox]

I am sure there is an easier way of doing this, but I would like to struggle through the long way.

I get an object-defined error '1004' when the sub gets to the correl calc

I have a number of return streams on sheet(rawdata), the macro would calc correlation and create the table on the second tab. I was successful in setting up the 'grid' and would like to have it calculated column by column. i fixed series1 until all 12 coefficients are populated in rows C2 to C14 on the second tab...after that I will offset it another column and so fort...am i being clear?

I struggled with this for a few hours...and I keep hitting a wall...can someone please direct me in the right direction using the same logic I laid out. (i am sure it can be done using various other, simpler macros, but at this point I want to do it my way, unless the logic is flawed)

any help is appreciated

[milyenabox]

is there anyone who can lend me a hand with this? I am stuck.

Andy, norie?

[norie]

I had a look at the worksheet and code you posted.

I got the same error you got.

Is Correl a user defined function? If it is then as far as I know you can't use it with WorksheetFunction.

I'm also not quite sure what you want as a result.

[milyenabox]

Correlation is an excel function.

the result would be a grid of each asset's correlation to another.


like this


a b c
a 1 0.5 -0.8
b 0.5 1 0.6
c -0.8 0.6 1

[rbrhodes]

Hi,

Took me long enough to find it! arrrgggh...

You are comparing the correl version of apples and oranges. The line that contains the correl function has an error in addressing so the two ranges do not equal and correl chokes.

Series1 is B3, Series2 is C3, startasset is B1

So range1 for correl is B3:B33 and range2 is B4:B33 according to your formula. If you're trying to loop across the columns try:

For x = 1 To 11 
     
    resultcell.Value = Application.WorksheetFunction.Correl(Sheets("rawdata").Range(series1, series1.End(xlDown)), Sheets("rawdata").Range(startasset.Offset(2, x), startasset.Offset(2, x).End(xlDown))) 
     
    Set resultcell = resultcell.Offset(1, 0) 
    Set startasset = startasset.Offset(0, 1) 
     
Next 

Anyways, make the two ranges equal and it will work!

Cheers,

dr

[milyenabox]

rbrhodes,

thanks for the response...I just realized that in my code startasset is b1 however when calling it in the correl function I offset it by 3 rows and 0 columns...it should be offset by only 2 rows.

the next step after having the first column of correl done is too move across the grid...I guess I have to reset the location of series1 and do the same drill...perhaps a nested loop within this current loop.

[milyenabox]

rbrhodes,

I fixed the offset for startasset...but now in a 'set' command I also offset series1 by one column, how can i get back to the original loop and run the new series1 through all 12 columns on rawdata sheet...after that's done...it would offset series one by another column (it would start at d3)..run through it all 12 columns...offset it by another 1...and so on

any suggestions...I am not sure how I could nest another loop within the original one??

[pangolin]

Hi

I think I have solved your problem...have a look at the attached file and let me know if anything more is required

pangolin

[milyenabox]

thanks a lot pangolin..that's one way to do it.

however, I need it in a matrix...hence the results will be doubled.

I do have another much simpler macro to do the job but I was wondering if it can be done by applying the logic I laid out in the following macro:

Sub create_correl_matrix() 
     
    Dim series1 As Range 
    Dim series2 As Range 
    Dim resultcell As Range 
    Dim destcell As Range 
    Dim startasset As Range 
     
    Set series1 = Sheets("rawdata").Range("b3") 
    Set startasset = Sheets("rawdata").Range("b1") 
    Set destcell = Sheets("correl_matrix").Range("b2") 
    Set resultcell = Sheets("correl_matrix").Range("c2") 
     
    Sheets("correl_matrix").Cells.ClearContents 
    startasset.Resize(1, 12).Copy 
    destcell.PasteSpecial xlPasteValues, , , True 
    destcell.Offset(-1, 1).PasteSpecial xlPasteValues 
     
    Do Until startasset = Empty 
         
        resultcell.Value = Application.WorksheetFunction.Correl(Sheets("rawdata").Range(series1, series1.End(xlDown)), _ 
        Sheets("rawdata").Range(startasset.Offset(2, 0), startasset.Offset(2, 0).End(xlDown))) 
         
        Set resultcell = resultcell.Offset(1, 0) 
        Set startasset = startasset.Offset(0, 1) 
    Loop 
     
    Set series1 = series1.Offset(0, 1) 
     ' after series1 is offset I would like to get back to the loop and run it through all the columns again...once it finished series1 would be offset again...and so on
     
End Sub 

can this macro be just modified slightly? don't want to come through as bullheaded but I am trying to learn different ways of applying code...your help is appreciated.

[pangolin]

Hi

I havent understood

I think the results in the file I attached are generated by way of a matrix (It is in the same format as used by us in research reports while presenting correlation factors over multiple asset classes??? )

...did you really click on the button labelled "click me" on the sheet correl matrix

please clarify

pangolin