Results 1 to 1 of 1

Macro for calculating pairwise rolling correlations

Threaded View

  1. #1
    Registered User
    Join Date
    02-25-2013
    Location
    L.A
    MS-Off Ver
    Excel 2010
    Posts
    19

    Macro for calculating pairwise rolling correlations

    Please find the workbook attached.

    The first row contains the names of the banks and all other entries are the daily returns of each bank. What I want to do is find the rolling correlation (from the past 1 year of returns) of pairwise banks beginning from 1/1/2008, I'll provide an example of what I want to do:

    Starting with ERSTE GROUP BANK, on the date JAN 1st 2008, I want to find the correlation between ERSTE GROUP BANK and RAIFFEISEN BANK INTL based on the past 1 year worth of returns, so the formula would be =CORREL(B2:B263,C2:C263), similarly on JAN 2nd 2008 the formula would be =CORREL(B3:B264,C3:C264) and so on until Feb 28th 2013.

    Then I want to do the same thing but now on the date JAN 1st 2008, I find the pairwise correlation between ERSTE GROUP BANK and DEXIA, hence the formula on JAN 1st 2008 would be =CORREL(B2:B263,D2:D263) and so on until Feb 28th 2013.

    So essentially I want to find the pairwise correlations between ERSTE GROUP BANK and every other bank like this, from 1/1/2008-28/2/2013.

    Then I want to repeat this with ALL the other banks, eg, now take RAIFFEISEN BANK INTL as the 'primary' bank and from 1/1/2008-28/2/2013, I want to find the pairwise correlation between RAIFFEISEN BANK INTL and ERSTE GROUP BANK, then RAIFFEISEN BANK INTL and DEXIA etc.

    Does anyone have an idea on how to write a macro for this? I am completely stuck. Any help would be appreciated.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 08-24-2012, 06:05 PM
  2. Calculating Rolling IRRs
    By PVilly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2010, 10:07 AM
  3. Calculating rolling annual value
    By Pete_M in forum Excel General
    Replies: 3
    Last Post: 08-06-2008, 12:10 PM
  4. [SOLVED] calculating a rolling mean
    By Delboy in forum Excel General
    Replies: 3
    Last Post: 07-05-2005, 12:05 PM
  5. [SOLVED] calculating a rolling mean
    By Delboy in forum Excel General
    Replies: 5
    Last Post: 07-05-2005, 08:05 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1