Rolling Daily Standard Deviation for Last 12 Months

[FMCaeiro]

Hi!

I have daily data for stock market returns in column B. And the corresponding date (dd/mm/yyyy) of each observation in column A.
I want to compute the STDEV of last 12 months daily returns. And get a standard deviation value for the first day of each month, which I will assume as the standard deviation of that month.

The problem is that not all months have the same number of observations / trading days/ daily returns.

Imagine for 2010 I have 250 observations / daily returns from 1/Jan/2010 to 31/Dec/2010
In column C, I could have run a simple standard deviation formula in cell number 251 (1/Jan/2011), selecting the data to insert in STDEV function as the 250 above cells of column B (daily returns).
The problem is when I drag it down, it will always select the last 250 observations and what I want is to select all the observations corresponding exactly to the last 12 months.
If I do the same for 1/4/2011 I will get the standard deviation for the previous 250 observations instead of the standard deviation of the trading days between 1/4/2010 and 31/03/2011 (the days of the last 12 months)

Is it clear?
I can send you the excel file it it helps.

Thanks in advance for your help.

 

[joeu2004]

If I do the same for 1/4/2011 I will get the standard deviation for the previous 250 observations instead of the standard deviation of the trading days between 1/4/2010 and 31/03/2011 (the days of the last 12 months)[.] Is it clear?

Yes. But in my opinion and experience, it is better to use the last 250 trade days, if that's what you define as a year.

(I use 252 because it is divisible by 12. I believe it is also the standard. And I use 21 for monthly, of course.)

The problem with calendar dates is: there is not always a trade day on the calendar date a year (or month) ago. That calendar date might be a weekend; or the market was simply closed for other reasons; or the data is missing.

What date do you want to choose in those cases?

(Usually, I use the closest trade date before the calendar year-old date. Or somethimes I interpolate based on the two surrounding trade dates. That is especially useful when there multiple consecutive missing trade days; and it makes for a smooth curve.)

The other problem with using trade days within calendar perioids is the very fact that because the number of trade days varies, it impacts the assumptions of some statistics. (Another reason why I started interpolating missing trade days.)

Do you still want to use calendar year instead of 250 (252) trade days?

 

[FMCaeiro]

Ok. I got your point. Actually it makes sense to use 252 rather than 250.
But what I actually want is to "tell" excel this: For the first day of each month give me the standard deviation of the last 12 months observations. It doesn't matter if excel picks 250, 251 or 252 values to compute the standard deviation.
I just want it to use exactly and only the daily returns of the previous 12 months.
So if I am in January 2010 I want it to select all the observations of January 2009, February 2009 (...) December 2010.
So the ideia is to get a t-12 to t-1 (t in months) standard deviation computed from daily returns. And not a t-252 to t-1 (t in days) standard deviation
Not sure if I am answering your question. And thank you for your help.

 

[joeu2004]

I just want it to use exactly and only the daily returns of the previous 12 months. So if I am in January 2010 I want it to select all the observations of January 2009, February 2009 (...) December 2010. So the ideia is to get a t-12 to t-1 (t in months) standard deviation computed from daily returns. And not a t-252 to t-1 (t in days) standard deviation[.] Not sure if I am answering your question.

Yes; we just have a difference of opinion. But now I am not sure what you mean by "previous 12 months". So I provide two interpretations: column D and column I. For each, I provide the relevant dates in columns E:G and J:M; they are provided for debugging purposes only. I hope that helps you make a choice.

Column A has dates starting from 1/1/2016 (A1). Column B has random numbers. See the formulas and comments below. Note: I write dates in the form MDY; you use DMY.


[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD="align: center"]422
[/TD]
[TD="align: right"]2/25/2017
[/TD]
[TD="align: right"]0.42882581
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Prev 12 Mon
[/TD]
[TD="align: right"]Edate-12
[/TD]
[TD="align: right"]Min
[/TD]
[TD="align: right"]Max
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]Prev Cal Yr
[/TD]
[TD="align: right"]EOMonth-13
[/TD]
[TD="align: right"]EOMonth-1
[/TD]
[TD="align: right"]Min
[/TD]
[TD="align: right"]Max
[/TD]
[/TR]
[TR]
[TD="align: center"]423
[/TD]
[TD="align: right"]2/26/2017
[/TD]
[TD="align: right"]0.52376683
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28856978
[/TD]
[TD="align: right"]2/26/2016
[/TD]
[TD="align: right"]2/26/2016
[/TD]
[TD="align: right"]2/25/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28805559
[/TD]
[TD="align: right"]1/31/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"]2/1/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]424
[/TD]
[TD="align: right"]2/27/2017
[/TD]
[TD="align: right"]0.95092180
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28800467
[/TD]
[TD="align: right"]2/27/2016
[/TD]
[TD="align: right"]2/27/2016
[/TD]
[TD="align: right"]2/26/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28805559
[/TD]
[TD="align: right"]1/31/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"]2/1/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]425
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"]0.38704623
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28825929
[/TD]
[TD="align: right"]2/28/2016
[/TD]
[TD="align: right"]2/28/2016
[/TD]
[TD="align: right"]2/27/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28805559
[/TD]
[TD="align: right"]1/31/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[TD="align: right"]2/1/2016
[/TD]
[TD="align: right"]1/31/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]426
[/TD]
[TD="align: right"]3/1/2017
[/TD]
[TD="align: right"]0.34574922
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28759250
[/TD]
[TD="align: right"]3/1/2016
[/TD]
[TD="align: right"]3/1/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28759250
[/TD]
[TD="align: right"]2/29/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"]3/1/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]427
[/TD]
[TD="align: right"]3/2/2017
[/TD]
[TD="align: right"]0.98189226
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28757540
[/TD]
[TD="align: right"]3/2/2016
[/TD]
[TD="align: right"]3/2/2016
[/TD]
[TD="align: right"]3/1/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28759250
[/TD]
[TD="align: right"]2/29/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"]3/1/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[/TR]
[TR]
[TD="align: center"]428
[/TD]
[TD="align: right"]3/3/2017
[/TD]
[TD="align: right"]0.88142242
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28785801
[/TD]
[TD="align: right"]3/3/2016
[/TD]
[TD="align: right"]3/3/2016
[/TD]
[TD="align: right"]3/2/2017
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]0.28759250
[/TD]
[TD="align: right"]2/29/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[TD="align: right"]3/1/2016
[/TD]
[TD="align: right"]2/28/2017
[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
D423: { =STDEV(IF(A57:A422>=EDATE(A423,-12),B57:B422)) }
E423: =EDATE(A423,-12)
F423: { =MIN(IF(A57:A422>=EDATE(A423,-12),A57:A422)) }
G423: { =MAX(IF(A57:A422>=EDATE(A423,-12),A57:A422)) }

I423: { =STDEV(IF($A$1:A422>EOMONTH(A423,-13),IF($A$1:A422<=EOMONTH(A423,-1),$B$1:B422))) }
J423: =EOMONTH(A423,-13)
K423: =EOMONTH(A423,-1)
L423: { =MIN(IF($A$1:A422>EOMONTH(A423,-13),IF($A$1:A422<=EOMONTH(A423,-1),$A$1:A422))) }
M423: { =MAX(IF($A$1:A422>EOMONTH(A423,-13),IF($A$1:A422<=EOMONTH(A423,-1),$A$1:A422))) }

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

Arguably, the use of the absolute reference $A$1:A422 is inefficient because it is an increasingly large range. But IMHO, it is better than using OFFSET and easier than using INDEX.

You might consider using STDEV.P instead of STDEV. It depends on how you use the std dev. STDEV.P is the actual std dev of the data. STDEV is a theoretically estimated std dev of a larger set of data from which the STDEV data is presumed to be a sample.

 

[joeu2004]

PS....

For each, I provide the relevant dates in columns E:G and J:M; they are provided for debugging purposes only.

Since my dates in column A are fully-populated, we don't see much difference between the Min and Max columns and the EDate-13, EOMonth-12 and EOMonth-1 columns. But you should, since Min and Max should reflect any missing trade dates.

 

[FMCaeiro]

Awesome! It works perfectely!

 

[FMCaeiro]

Thank you so much!