Asset Correlation Macro

Hi Folks,

Wondering if someone could help me in automating this task. I've a asset correlation s/s with two tabs -
Correl_Matrix - which produce the final result based on excel correl formula.
Returns - correlation is based on the figures from this sheet.

though the data on the returns tab is 95 rows but i only want to create a macro to correlate assets for 30 days period.

I can do this manually with excel correl formula and range to row 30 but when ever there is an addition to the asset i have to add the column in correl tab with all the formula's which is pretty manual and in case i want a correlation for 60 days then i have to change all formulas manually again.

is there any way to create a macro to automate this task say for 30 days period ?

any help will be much appreciated.

Many thanks - Jay

I think you can make this a lot easier without using a macro. Try this:

In the RETURNS tab, highlight the header row and the number of rows you want to include in the analysis. Then go to "Formulas" and under "Defined Names" select "Create from Selection". Check the "top row" box. This will create name ranges using your headers.

Next, go to cell B3 in of CORREL_MATRIX and input the formula:

"=CORREL(INDIRECT($A3),INDIRECT(B$2))"

Copy this throughout the matrix and you have an easily updatable table.

Hope this helps

SAE