Rolling Volatility for past 12 month returns if 9-month return data is available for mulriple firm

Takdir — Sat, 09 Nov 2019 20:19:27 GMT

HI,

I want to calculate volatility using stanadard deviation over past months only if there are atleast 9 month observations. I have attached an excel sheet for illustration. For example in Dec 1985, I calculate volatility based on monthly returns from January-December 1985 and it has data for all 12 months in 1985. However, I calculate volatility based on monthly returns from August 1985 to July 1985 but it has data for 10 months in a 12 month period. I do not calculate volatility for May1987 because it does not have 9 month observations in past 12 months i.e from June 1986 to May 1987.

the above mentioned problem was given by another user. I made modification to it by adding another stock. so can anyone help me with how can I calculate volatility for multiple stocks?

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret stdret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505 0.029861527
1 198601 -0.04 0.030770009
1 198602 -0.1023 0.030523464
1 198603 -0.0163 0.035403028
1 198604 -0.14465 0.040839661
1 198607 -0.07383 0.043861268
1 198608 -0.0315 0.046163063
1 198609 0.05495 0.060118401
1 198610 -0.15368 0.061979498
1 198611 -0.11494 0.063860041
1 198612 -0.03578 0.06457195
1 198705 -0.1234
2 198501 -0.10435
2 198502 -0.04762
2 198503 -0.08154
2 198504 -0.06757
2 198505 -0.08911
2 198506 -0.05481
2 198507 -0.0628
2 198508 -0.0989
2 198509 -0.10811
2 198510 -0.14205
2 198511 -0.04286
2 198512 -0.05505 0.029861527
2 198601 -0.04 0.030770009
2 198602 -0.1023 0.030523464
2 198603 -0.0163 0.035403028
2 198604 -0.14465 0.040839661
2 198607 -0.07383 0.043861268
2 198608 -0.0315 0.046163063
2 198609 0.05495 0.060118401
2 198610 -0.15368 0.061979498
2 198611 -0.11494 0.063860041
2 198612 -0.03578 0.06457195
2 198705 -0.1234
;
run;

the original problem and solution from the previous post by that user is given below:

I think this solution has a problem as well. it calculates volatility for 9 months as well even if there is no missing months.

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret stdret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505 0.029861527
1 198601 -0.04 0.030770009
1 198602 -0.1023 0.030523464
1 198603 -0.0163 0.035403028
1 198604 -0.14465 0.040839661
1 198607 -0.07383 0.043861268
1 198608 -0.0315 0.046163063
1 198609 0.05495 0.060118401
1 198610 -0.15368 0.061979498
1 198611 -0.11494 0.063860041
1 198612 -0.03578 0.06457195
1 198705 -0.1234
;
run;
proc sql;
create table want as
select *,case when (
(select count(ret) from have where stock=a.stock and
date between intnx('month',a.date,-11) and a.date) ge 9 )
then (select std(ret) from have where stock=a.stock and
date between intnx('month',a.date,-11) and a.date)
else . end as volatility
from have as a;
quit;

Re: Rolling Volatility for past 12 month returns if 9-month return data is available for mulriple fi

unison — Sun, 10 Nov 2019 15:07:32 GMT

I'm trying to piece together what exactly you want. Please post an example of the "want" dataset in the future. From what I understand, you want to calculate a rolling 12 month volatility for a number of stocks (but only if 9 of the 12 months are nonmissing). Adapting @s_lassen's response on this thread (https://communities.sas.com/t5/SAS-Programming/calculating-rolling-standard-deviation-of-stock-return/td-p/488095😞

data have;
infile cards expandtabs truncover;
input stock date : yymmn6. ret;
format date yymmn6.;
cards;
1 198501 -0.10435
1 198502 -0.04762
1 198503 -0.08154
1 198504 -0.06757
1 198505 -0.08911
1 198506 -0.05481
1 198507 -0.0628
1 198508 -0.0989
1 198509 -0.10811
1 198510 -0.14205
1 198511 -0.04286
1 198512 -0.05505
1 198601 -0.04
1 198602 -0.1023
1 198603 -0.0163
1 198604 -0.14465
1 198607 -0.07383
1 198608 -0.0315
1 198609 0.05495
1 198610 -0.15368
1 198611 -0.11494
1 198612 -0.03578
1 198705 -0.1234
2 198501 -0.10435
2 198502 -0.04762
2 198503 -0.08154
2 198504 -0.06757
2 198505 -0.08911
2 198506 -0.05481
2 198507 -0.0628
2 198508 -0.0989
2 198509 -0.10811
2 198510 -0.14205
2 198511 -0.04286
2 198512 -0.05505
2 198601 -0.04
2 198602 -0.1023
2 198603 -0.0163
2 198604 -0.14465
2 198607 -0.07383
2 198608 -0.0315
2 198609 0.05495
2 198610 -0.15368
2 198611 -0.11494
2 198612 -0.03578
2 198705 -0.1234
;
run;

proc sort data=have;
by stock date;
run;

proc timeseries data=have out=have_ts;
	by stock;
	id date interval=month;
	var ret;
run;

data want;
	set have_ts;
	by stock;
	array returns(0:11) 8 _temporary_;
	if first.stock then call missing(of returns(*));
	returns(mod(_n_,12))=ret;
	if n(of returns(*))>9 then stdret=std(of returns(*));
run;

It's best to try to fill in missing months with observations so that the calculation works properly (this is why I first create a timeseries with the have set).

-unison

topic Re: Rolling Volatility for past 12 month returns if 9-month return data is available for mulriple fi in New SAS User

Rolling Volatility for past 12 month returns if 9-month return data is available for mulriple firm

Re: Rolling Volatility for past 12 month returns if 9-month return data is available for mulriple fi