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;
... View more
