Announcement

Collapse
No announcement yet.

correlation matrix vba step by step

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    "Doubt everything. Find your own light." -- Gautama Buddha

  • #2
    Re: correlation matrix vba step by step

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

    Andy, norie?
    "Doubt everything. Find your own light." -- Gautama Buddha

    Comment


    • #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.
      Boo!

      Comment


      • #4
        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
        "Doubt everything. Find your own light." -- Gautama Buddha

        Comment


        • #5
          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:

          Code:
              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, 12:06. Reason: spell check!
          Cheers,

          dr

          "Questions, help and advice for free, small projects by donation. large projects by quote"

          http://www.ExcelVBA.joellerabu.com

          Comment


          • #6
            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.
            "Doubt everything. Find your own light." -- Gautama Buddha

            Comment


            • #7
              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??
              "Doubt everything. Find your own light." -- Gautama Buddha

              Comment


              • #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
                Attached Files

                Comment


                • #9
                  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:

                  Code:
                  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.
                  "Doubt everything. Find your own light." -- Gautama Buddha

                  Comment


                  • #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

                    please clarify

                    pangolin

                    Comment


                    • #11
                      Re: correlation matrix vba step by step

                      here is the file again...as you can see the first column is populated and series1 is offset to start at c3 but I need to get back in the loop.
                      Attached Files
                      "Doubt everything. Find your own light." -- Gautama Buddha

                      Comment


                      • #12
                        Re: correlation matrix vba step by step

                        your solution is correct... i am just wondering if what I have can be 'cured' by finding a way to get back in the loop
                        "Doubt everything. Find your own light." -- Gautama Buddha

                        Comment


                        • #13
                          Re: correlation matrix vba step by step

                          Hi,

                          A little more info?

                          'Series1' moves over one column - to rawdata, C1, and resultcell moves over one col and up to the top (correl matrix, D1).

                          Where does startdata go (start) this time?

                          Cheers,

                          dr
                          Cheers,

                          dr

                          "Questions, help and advice for free, small projects by donation. large projects by quote"

                          http://www.ExcelVBA.joellerabu.com

                          Comment


                          • #14
                            Re: correlation matrix vba step by step

                            Originally posted by rbrhodes
                            Hi,

                            A little more info?

                            'Series1' moves over one column - to rawdata, C1, and resultcell moves over one col and up to the top (correl matrix, D1).

                            Where does startdata go (start) this time?

                            Cheers,

                            dr

                            resultcell moves down one row within the loop
                            startasset moves from b1 to the right c1 then d1 etc..all within the loop

                            after first column is populated (outside the loop) I am changing series1 to c3
                            and now I want to run the same drill again somehow get back in the loop

                            did I explain it clearly?
                            "Doubt everything. Find your own light." -- Gautama Buddha

                            Comment


                            • #15


                              Re: correlation matrix vba step by step

                              Ok, so the only thing that changes (really) is series one. I'll be right back...

                              Cheers

                              dr
                              Cheers,

                              dr

                              "Questions, help and advice for free, small projects by donation. large projects by quote"

                              http://www.ExcelVBA.joellerabu.com

                              Comment

                              Working...
                              X