DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎08-13-2016 01:25 AM
Super User
Posts: 9,681

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

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;


View solution in original post


All Replies
Respected Advisor
Posts: 3,893

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

[ Edited ]

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).

Contributor
Posts: 57

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

[ Edited ]

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.

Super User
Posts: 17,836

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

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

Contributor
Posts: 57

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

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;

Super User
Posts: 17,836

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

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? 

 

Contributor
Posts: 57

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

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

Super User
Posts: 17,836

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

How are you defining volatility?

Contributor
Posts: 57

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

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.

Solution
‎08-13-2016 01:25 AM
Super User
Posts: 9,681

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

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;


Contributor
Posts: 57

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

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

Contributor
Posts: 57

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

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

Super User
Posts: 17,836

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

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

Whats a large data set? 

Contributor
Posts: 57

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

Its like 4 million observations. I am running the code and its taking ages to provide results.
Super User
Posts: 9,681

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

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;


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 1348 views
  • 5 likes
  • 4 in conversation