Search code examples
excelexcel-formulacorrelationexcel-indirect

Dynamic rolling correlation excel

I have a financial time series data set. I need to make a rolling window correlation, where I can control the window size, 1 dependent variable and 2 or more independent varaibles. And it needs to be dynamic when updating the data history. I also want to be able to choose the dependent.

I already managed to make the first dynamic correlation, where i changes as I change the constant of the window size

="Data!"&ADRESS($A11;MATCH(B$7;TitleArray;0))&":"&ADRESS($B$1+ROW(Data!$A$1);MATCH(B$$7;TitleArray;0))

The cell now holds an address to the range of the data in the window of the first variable

  • Data is reffering to my data set.
  • $A11 is my starting row number of the window. might need to relative?
  • B$7 is a cell with the name of my dependent variable (I want to know how this corr with several other variables, but I need to be able shift between, which is the dependent one)
  • TitleArray is name of a range holding the variable names
  • $B$1 refers the the constant explaining the size of the window
  • +ROW(Data!$A$1) makes sure that it adjusts when I add more history

Then I just copied this formula to the next cell in the column next to, replacing B7 with C7

Then, in a new cell I found the corr between the two in this way:

CORRELATION(INDIRECT($o$16);INDIRECT(P16))

Here I made the corr between the dependent and another variable for the set window (I tested it with 24 months).

Can anyone help me get the last steps in order to extend it to show the last corr windows for the remaining history? I suppose it should be rather easy, but I can't seem find the right way. I guess I have to remove some $ signs and then drag it down, but then I will get a lot of rows with adresses follows by correlations. Can it be made in a smarter way?

If any details are missing please let me know

thank you!

Solution

  • A little hard to follow the specifics of your objectives, but here is an approach using OFFSET.

    =CORRELATION( OFFSET( Data!$A$1; $A11-1; MATCH($B7;TitleArray;0 )-1; ($B$1+2-$A$11); 1 ); 
              OFFSET( Data!$A$1; $A11-1; MATCH(C7;TitleArray;0 )-1; ($B$1+2-$A$11); 1 ) )

    B7 is the name of your dependent variable and C7 would be the independent variable name. As you drag this formula to the right, it would compare B7 to C7, then D7, etc.

    NB: my OS is in English, so here is the working version in my machine just in case I errored in translation:

    =CORREL( OFFSET( Data!$A$1, $A11-1, MATCH($B7,TitleArray,0 )-1, ($B$1+2-$A$11), 1 ), 
         OFFSET( Data!$A$1, $A11-1, MATCH(C7,TitleArray,0 )-1, ($B$1+2-$A$11), 1 ) )

    With this approach, you can avoid having the helper cells with the formula:

    ="Data!"&ADDRESS($A11;MATCH(B$7;TitleArray;0))&":"&ADDRESS($B$1+ROW(Data!$A$1);MATCH(B$7;TitleArray;0))