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.
Bookmarks