topic Re: Rolling Volatility based on past 12 month returns if 9-month return data is available in SAS Programming

Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 03:30:40 GMT

Dear all,

I want to calculate rolling volatility based on past 12 month returns i.e., from July 1997 to June 1998. Furthermore, if a month is missing in past 12 months then volatility calculation should be based on 11 months between the period of July 1997 to June 1998 instead of using one earlier month i.e, June 1997.

Thanks a lot for your time.

Best,

MAC

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Patrick — Sat, 13 Aug 2016 03:09:57 GMT

Please provide sample data as as SAS Data step creating such data. Then explain us how the expected result should look like (also as an example using data).

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Reeza — Sat, 13 Aug 2016 03:08:00 GMT

How are you defining volatility?

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 03:31:12 GMT

Thanks a lot for your prompt response. Sorry, I did not explain it earlier. 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.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 03:33:16 GMT

Dear Reeza,

Thansk for your prompt response. I am defining volatility based on standard deviation form past 12 month returns. I have attached the file as well.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Reeza — Sat, 13 Aug 2016 03:40:21 GMT

Do you have a SAS ETS licence? If so, look at proc Expand. The documentation, example on transformations, is what you're looking for.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 03:54:39 GMT

Dear Reeza,

Thanks. YEs, I have SAS license and I used it to calculate volatility. But the problem is that it calculates based on past 12 observations instead of just past 12 months. For example, if one month is missing in past 12 months period i.e., July is missing from January to December 1985 then it will use monthly return from December 1984 too. I am using teh following code in SAS:

Data ret;
infile 'D:\My SAS Files\9.4\US\US paper data\ret.dat';
Input stock $1-5 date1 $7-14 exc $16-17 sharecode $19-20 ret;
run;
data ret1;
set ret;
if sharecode^=10 and sharecode^=11 then delete;
date=SUBSTRN(date1,1,6);
year= SUBSTRN(date1,1,4);
month= SUBSTRN(date1,5,2);
run;
data have(keep=stock ret monthDate);
set ret1;
monthDate = mdy(month, 1, year);
if ret=-66 then delete;
if ret=-77 then delete;
if ret=-88 then delete;
if ret=-99 then delete;
if ret=. then delete;
run;
proc sql;
create table roll as
select
h2.stock,
h2.monthDate as periodEndDate format=yymmd7.,
h2.ret,
mean(h1.ret) as meanRet,
std(h1.ret) as stdRet
from
have as h1 inner join
have as h2 on h1.stock=h2.stock and
intck("MONTH", h1.monthDate, h2.monthDate) between 0 and 11
group by h2.stock, h2.monthDate, h2.ret
having count(h2.stock)=12;
quit;

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Reeza — Sat, 13 Aug 2016 05:14:06 GMT

SAS ETS licence. SAS licences different modules that do different things. ETS is Econometrics and Time Series which handles serial data such as time and date, and is capable of filling in missing periods or accounting for it.

Your query looks close, except for your having clause which requires that you have 12 months of data. This is contradictory to what is specified in your requirements, I think it's 9 now?

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Ksharp — Sat, 13 Aug 2016 05:16:44 GMT

You'd better post your data at this forum, not attachment. No one would like to download it .


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 based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 05:26:26 GMT

Thanks a lot, its really helpful. I will post data in future if I have a question.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 05:29:43 GMT

Thanks a lot for your help, another member posted the solution I was looking for.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 06:43:29 GMT

I just found a small problem with it, the code works very well for the samll data-set but it stops working for large data-set. Any idea how to fix the code to work for a large data-set? Thanks

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Reeza — Sat, 13 Aug 2016 07:05:39 GMT

What does stop working mean? Why does it stop working?

Whats a large data set?

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 07:10:46 GMT

Its like 4 million observations. I am running the code and its taking ages to provide results.

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

Ksharp — Sat, 13 Aug 2016 08:25:19 GMT

OK. If you have a big table, Try Hash Table. 
If the following code still couldn't work. I can use DOW+Hash Table .



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;
data want;
 if _n_=1 then do;
  if 0 then set have;
  declare hash h(dataset:'have',hashexp:20);
  h.definekey('stock','date');
  h.definedata('ret');
  h.definedone();
 end;
set have;
array x{12} _temporary_;
n=0;
call missing(of x{*});

do i=-11 to 0;
 temp=intnx('month',date,i);
 rc=h.find(key:stock,key:temp);
 if rc=0 then do;n+1;x{n}=ret;end;
end;

if n ge 9 then volatility=std(of x{*});
 else volatility=.;
drop i n temp rc;
run;

Re: Rolling Volatility based on past 12 month returns if 9-month return data is available

MAC1430 — Sat, 13 Aug 2016 08:38:40 GMT

Thanks a lot for it, its working. I am new to this community, will try to provide more details in the future. Enjoy the weekend.