Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 17

Thread: correlation matrix vba step by step

  1. #1
    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    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. #3
    Join Date
    14th July 2004
    Posts
    10,539

    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. #4
    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. #5
    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. #6
    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. #7
    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. #8
    Join Date
    26th July 2004
    Posts
    1,365

    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
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    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. #10
    Join Date
    26th July 2004
    Posts
    1,365

    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

    please clarify

    pangolin

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Code runs fine step by step but not when executed
    By Metalastic in forum EXCEL HELP
    Replies: 1
    Last Post: April 28th, 2006, 03:43
  2. how do u install excel? step by step?
    By gora in forum EXCEL HELP
    Replies: 1
    Last Post: May 9th, 2003, 10:24
  3. Replies: 4
    Last Post: April 30th, 2003, 00:29

Bookmarks

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