rolling standard deviation

[Felipe Damasceno]

Hi Guys,

I'm using STATA 13 with windows 10 OS.

I have quarterly firm observations and I want to calculate the standard deviation of earnings for 2 years window (8 quarters). I'm completely lost on how to do this. Can anybody give me some directions? I'm sending a pick of my dataset bellow (I have more than one firm id)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 ibtic float(anndatsq anndats EPS_sup)
"AA2G" 16204 16544    0
"AA2G" 16296 16544 -.06
"AA2G" 16383 16544  .01
"AA2G" 16544 16544 -.05
"AA2G" 16685 16909 -.18
"AA2G" 16761 16909  .33
"AA2G" 16909 16909 -.08
"AA2G" 17027 17258  .13
"AA2G" 17119 17258  -.1
"AA2G" 17258 17258 -.01
"AA2G" 17301     .    .
"AA2G" 17387 17622   .5
"AA2G" 17484 17622  .33
"AA2G" 17622 17622  .73
"AA2G" 17667 17979 1.66
"AA2G" 17744 17979  .34
"AA2G" 17849 17979 1.47
"AA2G" 17979 17979 -.01
"AA2G" 18029 18350 -.36
"AA2G" 18122 18350 1.72
"AA2G" 18213 18350  .76
"AA2G" 18350 18350    .
end
format %td anndatsq
format %td anndats

[Attaullah Shah]

Download asrol program from SSC and use the following code.

ssc install asrol
gen qofd=qofd( anndatsq)
format %tq qofd
bys ibtic: asrol EPS_sup, stat(sd) w(qofd 8)

Note: We have to generate the quarter identifier to have regularly-spaced time variable. The option w(qofd 8) specifies a rolling window of 8 quarters, while the by ibtic limits the rolling window for each id in the calculation of the standard deviation.

list in 1/20

     +--------------------------------------------------------------+
     | ibtic    anndatsq     anndats   EPS_sup     qofd   sd8_EPS~p |
     |--------------------------------------------------------------|
  1. |  AA2G   13may2004   18apr2005         0   2004q2           . |
  2. |  AA2G   13aug2004   18apr2005      -.06   2004q3   .04242641 |
  3. |  AA2G   08nov2004   18apr2005       .01   2004q4   .03785939 |
  4. |  AA2G   18apr2005   18apr2005      -.05   2005q2   .03511885 |
  5. |  AA2G   06sep2005   18apr2006      -.18   2005q3   .07569677 |
     |--------------------------------------------------------------|
  6. |  AA2G   21nov2005   18apr2006       .33   2005q4   .17151288 |
  7. |  AA2G   18apr2006   18apr2006      -.08   2006q2   .17535679 |
  8. |  AA2G   14aug2006   02apr2007       .13   2006q3   .18051778 |
  9. |  AA2G   14nov2006   02apr2007       -.1   2006q4    .1879805 |
 10. |  AA2G   02apr2007   02apr2007      -.01   2007q2   .18619882 |
     |--------------------------------------------------------------|
 11. |  AA2G   15may2007           .         .   2007q2   .18619882 |
 12. |  AA2G   09aug2007   31mar2008        .5   2007q3   .24227395 |
 13. |  AA2G   14nov2007   31mar2008       .33   2007q4   .24227395 |
 14. |  AA2G   31mar2008   31mar2008       .73   2008q1   .31722007 |
 15. |  AA2G   15may2008   23mar2009      1.66   2008q2   .60204413 |
     |--------------------------------------------------------------|
 16. |  AA2G   31jul2008   23mar2009       .34   2008q3   .58775601 |
 17. |  AA2G   13nov2008   23mar2009      1.47   2008q4   .62235344 |
 18. |  AA2G   23mar2009   23mar2009      -.01   2009q1   .63093892 |
 19. |  AA2G   12may2009   29mar2010      -.36   2009q2   .69066738 |
 20. |  AA2G   13aug2009   29mar2010      1.72   2009q3   .79643851 |
     +--------------------------------------------------------------+

[Felipe Damasceno]

Thanks for the reply Attaullah. I tried your code and I'm getting the following error


variable __000002 not found
st_data(): 3598 Stata returned error
fasrol(): - function returned error
<istmt>: - function returned error

r(3598);

[Attaullah Shah]

Yes, you are right. I posted a code for the new version of asrol, which is not yet released. please try this code

gen qofd=qofd( anndatsq)
format %tq qofd
asrol EPS_sup, stat(sd) w(qofd 8) by(ibtic)

[Robert Picard]

With all due respect to Attaullah and his efforts for make a contribution to the Stata community, I would recommend you use either rangestat or tsegen for this task. Both are more capable programs written by experienced Stata programmers who are frequent contributors on this forum.

To install them, type in Stata's Command window:

ssc install rangestat
ssc install tsegen

With respect to your data, your sample does not include quarterly dates. So the first step is to convert daily dates to Stata quarterly dates. In doing so, I notice that you have one repeat observation for the second quarter of 2007. Since the earnings are missing for that observation, I just drop the repeat.

With rangestat, you can calculate more than one statistic at a time. In the example below, I include a count variable that will contain the number of observations that were used to calculate each result. You can use that to decide if you want to exclude results if there are not enough observations in the sample.

With tsegen, you declare the data to be panel and use time-series varlists (see help tsvarlist) in conjunction with egen row functions to make the same calculation.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 ibtic float(anndatsq anndats EPS_sup)
"AA2G" 16204 16544    0
"AA2G" 16296 16544 -.06
"AA2G" 16383 16544  .01
"AA2G" 16544 16544 -.05
"AA2G" 16685 16909 -.18
"AA2G" 16761 16909  .33
"AA2G" 16909 16909 -.08
"AA2G" 17027 17258  .13
"AA2G" 17119 17258  -.1
"AA2G" 17258 17258 -.01
"AA2G" 17301     .    .
"AA2G" 17387 17622   .5
"AA2G" 17484 17622  .33
"AA2G" 17622 17622  .73
"AA2G" 17667 17979 1.66
"AA2G" 17744 17979  .34
"AA2G" 17849 17979 1.47
"AA2G" 17979 17979 -.01
"AA2G" 18029 18350 -.36
"AA2G" 18122 18350 1.72
"AA2G" 18213 18350  .76
"AA2G" 18350 18350    .
end
format %td anndatsq
format %td anndats

* convert to Stata quarterly dates
gen qtr = qofd(anndatsq)
format %tq qtr

* you have more than one observation per quarter
bysort ibtic qtr (EPS_sup): gen repeat = _n > 1
list if repeat
drop if repeat

* get the sd and the sample size of past 7 quarters, including the current obs
* using rangestat (from SSC)
rangestat (sd) EPS_sup (count) EPS_sup, interval(qtr -7 0) by(ibtic)

* create a numeric identifier for the firm and declare data as panel
egen nibtic = group(ibtic)
tsset nibtic qtr

* get the sd using -tsegen- (from SSC)
tsegen sd = rowsd(L(0/7).EPS_sup)

[Attaullah Shah]

Robert Picard I respect you as a senior contributor to this forum. For the specific example, both asrol and rangestat do similar calculations, so I do not see any reason why do you downplay the role of asrol here?

[Felipe Damasceno]

I used the following code. I needed to change from what you gave to me to work. (the by part)

ssc install asrol
gen qofd=qofd( anndatsq)
format %tq qofd
asrol EPS_sup, stat(sd) w(qofd 8) by(ibtic)

I'm not sure it worked as it should. It's not giving the standard deviation for the first obs and it's creating 3 more in the end. For what I understand, shouldn't start calculating the standard deviation after 8 observation (window specification) and stop in the last one? What is going on here?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 ibtic float(anndatsq anndats EPS_sup) double sd8_EPS_sup
"AA2H" 16189 16483    .009999996                   .
"AA2H" 16280 16483           .06  .03535534353743003
"AA2H" 16371 16483   -.009999998   .0360555137568534
"AA2H" 16483 16483   -.010000003 .033040380688054365
"AA2H" 16560 16861          -.06  .04324349677422077
"AA2H" 16652 16861  7.152557e-09  .03868677657984773
"AA2H" 16742 16861          -.07  .04375255139953484
"AA2H" 16861 16861           .03  .04307385682220712
"AA2H" 16931 17211          -.05 .045019836923745504
"AA2H" 17015 17211   -.020000007  .03377974831898597
"AA2H" 17107 17211          -.06 .035456210113922595
"AA2H" 17211 17211 1.0728836e-08 .036425070979609724
"AA2H" 17289 17576    -.03000001 .034226140128172385
"AA2H" 17379 17576     .01999999  .03693623788436081
"AA2H" 17471 17576    -.02999999  .03195979506996589
"AA2H" 17576 17576  2.384186e-09 .026958963114829586
"AA2H" 17653 17946    -.06999999 .030676886018387927
"AA2H" 17744 17946     .06999999  .04527692241896591
"AA2H" 17835 17946     .03999999  .04407784756794301
"AA2H" 17946 17946           .05  .04749059522313644
"AA2H" 18017 18311   -.009999993  .04580626221485525
"AA2H" 18108 18311     .14999998  .06803359425565378
"AA2H" 18199 18311    -.01999998  .06696213116629841
"AA2H" 18311 18311           .12  .07337524797859622
"AA2H" 18388 18675   -.030000024  .06566962170462665
"AA2H" 18472 18675            .1  .06803360096138575
"AA2H" 18575 18675    -.01999998  .07245687697461282
"AA2H" 18675 18675    .069999985   .0730948429222708
"AA2H" 18752 19030    -.08000003  .08399617288280865
"AA2H" 18843 19030          -.21  .10749584736654644
"AA2H" 18934 19030          -.25  .13750324285773544
"AA2H" 19030 19030           .17  .14652522666664983
"AA2H" 19116 19396 1.0728836e-08   .1469450796007605
"AA2H" 19207 19396     .06999999   .1435705358163105
"AA2H" 19298 19396   -.010000007  .14372592499184447
"AA2H" 19396 19396     .03999999  .14111165398363357
"AA2H" 19487 19786  4.172325e-09  .14019755938972195
"AA2H" 19571 19786 -1.192093e-09  .11829140265501005
"AA2H" 19667 19786           -.1  .07735585483919334
"AA2H" 19786 19786           -.4   .1495708151203541
"AA2H" 19850     .             .  .16007438736323273
"AA2H" 19940     .             .  .16424575149081702
"AA2H" 20032     .             .   .1797776417700863
"AA2P" 17435 17654          -.21                   .
"AA2P" 17483 17654          -.14 .049497469625436503
end
format %td anndatsq
format %td anndats

[Attaullah Shah]

Standard deviation for a single observation is not possible, so the program skips the first observation. The default of the program is to find the required statistics from minimum possible observations. If you need to force the calculation of statistics from minimum specified number of observations, then you can use the option of minimum. For example, if you specify that the standard deviation should be calculated only when at least 4 observations are available, then the command would be.

 asrol EPS_sup, stat(sd) w(qofd 8) by(ibtic) min(4)

And to see how many observations were used in the calculation of each sd, you can use the count option

 asrol EPS_sup, stat(count) w(qofd 8) by(ibtic)

You can confirm accuracy of the calculations manually, if you are interested.