Calculate monthly volatility from daily prices

[art297]

Show some sample data with all the calculations in Excel and I'm sure that one of us on the forum can figure out what the difference is.

[Costasg]

I am attaching  a sample file. I did the calculations for the first months.

Many thanks

[art297 · Accepted solution]

Yes, there were definitely some things that had to be changed in order to match the results.  I think that the following calculates exactly what you were doing in Excel.  The main differences from what I had originally proposed are: (1) using the log10 function rather than the log function; (2) multiplying the differences by 100; and (3) using the corrected rather than the uncorrected sums of squares.

data need;

  set have;

  array vars(*) a1--a100;

  array diff(100);

  yrmon=put(DATE,yymmd.);

  do i=1 to dim(vars);

    diff(i)=(log10(vars(i))-log10(lag(vars(i))))*100;

  end;

run;

proc means data=need mean uss nway noprint;

  var diff1--diff100;

  class yrmon;

  output out=want (drop=_:)

   mean= css= /autoname;

run;

data want;

  set want;

  array diffs(*) diff1_CSS--diff100_CSS;

  do i=1 to dim(diffs);

    if diffs(i) gt 0 then

    diffs(i)=diffs(i)**0.5;

  end;

run;

[Costasg]

I don't have words to thank you for your help and time. I will check it in a few days that I will return to the uni but I am sure it is correct.

Many thanks again