The S.D. of returns calculated using a trailing twelve monthly returns

Reply
Contributor
Posts: 24

The S.D. of returns calculated using a trailing twelve monthly returns

Hi guys,

I have stock returns data for so many stocks/Tickers. Out of these returns, i want to compute standard deviation using trailing twelve months returns. What code can i use to get this?

tickerdateReturn
MC:AFI01/02/20120.119293
MC:AFI01/03/2012-0.04931
MC:AFI02/04/20120.003213
MC:AFI01/05/20120.058013
MC:AFI01/06/20120.081692
MC:AFI02/07/2012-0.01738
MC:AFI01/08/20120.031073
MC:AFI03/09/2012-0.04876
MC:AFI01/10/2012-7.7E-18
MC:AFI01/11/2012-0.00358
MC:AFI03/12/2012-1.7E-18
MC:AFI01/01/2013-0.01807
MC:AFI01/02/2013-0.01294
MC:AFI01/03/20130.016577
MC:AFI01/04/2013-3.4E-19
MC:AFI01/05/2013-0.01843
MC:AFI03/06/20130.071997
MC:AFI01/07/2013-0.01888
MC:AFI01/08/20130.000167
MC:AFI02/09/2013-0.01898
MC:AFI01/10/20130.030281
MC:AFI01/11/2013-0.0113
MC:AFI02/12/20130.062201
MC:AFI01/01/2014-0.03606
MC:AFI03/02/2014-0.00744
MC:AFI03/03/20140.027423
MC:AFI01/04/20140.028399
MC:AFI01/05/20140.049554
MC:AFI02/06/20140.021179
MC:ALM02/11/1998-0.06051
MC:ALM01/12/19980.086504
MC:ALM01/01/1999-0.08341
MC:ALM01/02/1999-0.0093
MC:ALM01/03/1999-0.01582
MC:ALM01/04/1999-0.04226
MC:ALM03/05/19990.003297
MC:ALM01/06/19990.026105
MC:ALM01/07/19990.094456
MC:ALM02/08/19990.03068
MC:ALM01/09/1999-0.00608
MC:ALM01/10/19990.006082
MC:ALM01/11/19995.25E-19
MC:ALM01/12/1999-0.03381
MC:ALM03/01/2000-6.9E-18
MC:ALM01/02/2000-0.07148
MC:ALM01/03/20000.105293
MC:ALM03/04/2000-0.0544
MC:ALM01/05/20000.03913
MC:ALM01/06/2000-0.05202
MC:ALM03/07/2000-0.02296
MC:ALM01/08/20000.076617
MC:ALM01/09/20000.040158
MC:ALM02/10/2000-0.00632
MC:ALM01/11/2000-0.12292
MC:ALM01/12/20000.126085
MC:ALM01/01/2001-0.07033
MC:ALM01/02/20010.081329
MC:ALM01/03/20010.11498
MC:ALM02/04/2001-0.12282
MC:ALM01/05/20010.020262
MC:ALM01/06/2001-0.09375
MC:ALM02/07/20010.008857
MC:ALM01/08/20010.001888
MC:ALM03/09/2001-0.06694
MC:ALM01/10/2001-0.1578
MC:ALM01/11/20010.114995
MC:ALM03/12/2001-0.07123
MC:ALM01/01/2002-0.0996
MC:ALM01/02/20027.54E-18
MC:ALM01/03/2002-0.12672
MC:ALM01/04/2002-0.05835
MC:ALM01/05/20020.039278
MC:ALM03/06/20020.005442
MC:ALM01/07/2002-0.00833
MC:ALM01/08/20020.066769
MC:ALM02/09/20020.017166
MC:ALM01/10/20020.065583
MC:ALM01/11/2002-0.00793
MC:ALM02/12/2002-0.04925
MC:ALM01/01/20030.113764
MC:ALM03/02/20030.106617
MC:ALM03/03/20034.69E-18
MC:ALM01/04/20030.03095
MC:ALM01/05/20030.120911
MC:ALM02/06/20030.070482
MC:ALM01/07/20030.040185
MC:ALM01/08/20030.073979
MC:ALM01/09/20030.0591
MC:ALM01/10/2003-4E-18
MC:ALM03/11/20030.016197
MC:ALM01/12/2003-0.01129
MC:ALM01/01/20040.14689
MC:ALM02/02/20040.141561
MC:ALM01/03/20040.014553
MC:ALM01/04/20040.133983
MC:ALM03/05/2004-0.04194
MC:ALM01/06/20040.00085
MC:ALM01/07/20040.032334
MC:ALM02/08/20040.006762
MC:ALM01/09/2004-0.11341
MC:ALM01/10/2004-0.07478
MC:ALM01/11/20040.021581
MC:ALM01/12/20040.070716
MC:ALM03/01/20050.037824
MC:ALM01/02/20050.056945
MC:ALM01/03/20050.030057
MC:ALM01/04/2005-0.02669
MC:ALM02/05/20050.024505
MC:ALM01/06/20050.002185
MC:ALM01/07/20050.066808
MC:ALM01/08/20050.04384
MC:ALM01/09/2005-0.02118
MC:ALM03/10/20050.045959
MC:ALM01/11/20050.059406
MC:ALM01/12/20050.026583
MC:ALM02/01/20060.241707
MC:ALM01/02/20060.027552
MC:ALM01/03/2006-0.0079
MC:ALM03/04/20060.038869
Contributor
Posts: 44

Re: The S.D. of returns calculated using a trailing twelve monthly returns

I added also other calculated statistics just to check that program is working as supposed:

proc sql;

  create table want as

  select h1.ticker,

         h1.date,

         std(h2.return) as return_Std,

         mean(h2.return) as return_mean,

         min(h2.return)  as return_min,

         max(h2.return)  as return_max,

         n(h2.return)    as return_n

  from   have h1 ,

         have h2

  where  h1.ticker = h2.ticker

     and h2.date ge h1.date

     and h1.date ge h2.date-365

group by h1.ticker, h1.date;

quit;

Contributor
Posts: 24

Re: The S.D. of returns calculated using a trailing twelve monthly returns

Hi. i dont get the same result when i cross check in Excel sheet. I want to use the past 12 months returns to compute the standard deviation. So for each month the trailing standard deviation = S.D of previous 12 months.

Contributor
Posts: 44

Re: The S.D. of returns calculated using a trailing twelve monthly returns

Sorry that program calculated 12 forthcoming months:

This should calculate 12 previous:

proc sql;

  create table want as

  select h1.ticker,

         h1.date,

         std(h2.return) as return_Std,

         mean(h2.return) as return_mean,

         min(h2.return)  as return_min,

         max(h2.return)  as return_max,

         n(h2.return)    as return_n

  from   have h1 ,

         have h2

  where  h1.ticker = h2.ticker

     and h1.date ge h2.date

     and h1.date le h2.date+365.25

group by h1.ticker, h1.date;

quit;

Ask a Question
Discussion stats
  • 3 replies
  • 232 views
  • 0 likes
  • 2 in conversation