Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: correlation matrix vba step by step

1. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## correlation matrix vba step by step

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

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## Re: correlation matrix vba step by step

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

Andy, norie?

Excel Video Tutorials / Excel Dashboards Reports

3. ## Re: correlation matrix vba step by step

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.

Excel Video Tutorials / Excel Dashboards Reports

4. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## Re: correlation matrix vba step by step

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

Excel Video Tutorials / Excel Dashboards Reports

5. Have VBA, will travel
Join Date
24th December 2004
Location
Nanaimo, Vancouver Island, British Columbia, Canada
Posts
2,464

## Re: correlation matrix vba step by step

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:

VB:
```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
Last edited by rbrhodes; February 3rd, 2005 at 11:06. Reason: spell check!

Excel Video Tutorials / Excel Dashboards Reports

6. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## Re: correlation matrix vba step by step

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.

Excel Video Tutorials / Excel Dashboards Reports

7. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## Re: correlation matrix vba step by step

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??

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: correlation matrix vba step by step

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

Excel Video Tutorials / Excel Dashboards Reports

9. Senior Member
Join Date
1st February 2005
Location
New York
Posts
122

## Re: correlation matrix vba step by step

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:

VB:
```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.

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: correlation matrix vba step by step

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

pangolin

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno