Correlation of the last 1250days, but starting after 750 days of non missing data

[ExcelMA0273]

Hello,

I´ve got a quite specific problem. In order to calculate the beta of the Capital Asset Pricing Model (CAPM), I need to calculate the volatility of stock returns as well as their correlation with the S&P 500.
Thereby I follow the approach of a specific paper ("Betting against Beta"). The volatility is calculated for the last 250 trading days and the correlation for the last 1250 days. Until this point, there is no problem.

But: The authors start calculating the volatility/correlation as 120/750 days of non-missing data are reached.
That means: Starting with the 120th,121th,122th,.../750th,751th,752th,... day of non-missing data, I need the volatility/correlation for the last 120,121,122,.../750,751,752,...days. The used days should rise until the "normal" 250/1250 days are reached.

Doing it manually is almost impossible, because I use >1000 stocks, which have different dates of IPO´s/delistings. I literally have no idea how to implement the approach. Maybe a nested if- and count-if function within the volatility/correlation-function?

I appreciate every kind of help!
(Please excuse, that the syntax of my question might be wrong at some point. I'm not a native english speaker)

Best Regards

[Bernie Deitrick]

You can filter the data that is passed to any Array-Entered formula using nested conditionals, like =MEDIAN(IF(DateRange>IPODate+120,DataRange)) entered using Ctrl-Shift-Enter. SUMPRODUCT also allows filtering of data. How you would do that specifically depends on your data structures and what functions you want to use.

You could also use a VBA solution - a custom User-Defined-Function that checks the values for existence (and counts) before beginning the actual calculation. How that would be done depends on how your data is arranged, and what indicates the start of the valid period of data, etc...

[ExcelMA0273]

Thank you Bernie,

The data are structured as follows:

I have daily price data for ~1500 stocks from 1980 to 2015. Out of those I calculate daily stock returns and index returns (value weighted by their market value). I have no specific data on the date of the IPO, it´s just the first day when prices are available. All days before and all days after delisting are marked as "#VALUE!".
So I have to determine the IPO date with sth. like "first day unequal #VALUE!" (or if I mark them as "999", first day with data less than 999).

The volatility of returns is calculated for the stocks itself, the correlation is calculated using the stock- and index-returns.

Another problem would be: How can I make Excel know, when it should calculate the correlation for 120,121,....,249 and finally 250 days? When facing other problems I use auxiliary numbers, which are filled into the formula, if you know what I mean..

What I forgot to mention: I calculate a rolling volatility/correlation, which means that I have volatility/correlation data for each day after 250/1250 (respectively 120+n/750+n) listed days.
I hope you understand me, it's somewhat hard for me to explain it in English.

Best regards

[Bernie Deitrick]

Is an individual stock's price in a row, or in a column? Is newer at the top, or at the bottom?

Anyway, if your prices are in a column, headers in row 1, with older at the top, then an array formula like this (entered using Ctrl-Shift-Enter):

=XXXX(OFFSET($L$1,SMALL(IF(NOT(ISERROR($L$2:$L$30000)),ROW($L$2:$L$30000)),1)-1,0,120))

will pass the first 120 cells after the #VALUE! entries in column L to the function XXXX.

So, if you need to use CORREL with, say, 120 values of A and L based on values in L (again, entered using Ctrl-Shift-Enter):

=CORREL(OFFSET($A$1,SMALL(IF(NOT(ISERROR($L$2:$L$30000)),ROW($L$2:$L$30000)),1)-1,0,120),OFFSET($L$1,SMALL(IF(NOT(ISERROR($L$2:$L$30000)),ROW($L$2:$L$30000)),1)-1,0,120))

You could also Parameterize the formulas so that it isn't repeated - for example, put this part into cell AAA1:

=SMALL(IF(NOT(ISERROR($L$2:$L$30000)),ROW($L$2:$L$30000)),1)

and use

=CORREL(OFFSET($A$1,AAA1-1,0,120),OFFSET($L$1,AAA1-1,0,120))

You could reduce the calculational load by using that formula in a row to find all the first data points, then copying and pasting values so that it is only calculated once.