New Package on SSC - asrol - module to calculate moving / rolling window statistics

[Attaullah Shah]

Thanks to Kit Baum, I have shared a new package on SSC, to describe and install, type

ssc desc asrol
ssc install asrol

TITLE
'ASROL': module to generate rolling-window descriptive statistics in time series or panel data

DESCRIPTION

This program calculates descriptive statistics in a user's
defined rolling-window. For example, in time series or panel
data, a user might be interested in knowing standard deviation
or coefficient of variation of a variable in a rolling window of
last 4 years. asrol provides efficient and simple way of
finding such statistics. It also offers various options to
specify minimum number of observations required for
calculation of desired statistics in a rolling window.

KW: descriptive statistics
KW: rolling window

Requires: Stata version 11

Distribution-Date: 20150905

Author: Attaullah Shah, Institute of Management Sciences
Support: email [email protected]

Title

asrol - Generates rolling-window descriptive statistics in
time series or panel data


Syntax

asrol varlist [if] [in] , gen(newvar) stat(statistic) window(#) [ nomiss minmum(#)]


Syntax Details

The program has 3 required options: They are
1. gen : to generate new variable, where the variable name is enclosed in parenthesis after gen
2. stat: to specify required statistics. The following statistics are allowed;

        sd : for standard deviation
        mean : for mean
        total : for sum or total
        median : for median
        pctile  : for percentiles
        min  : for minimum
        max : for maximum

3. window :is to specify the length of rolling window for calculation of the
required statistics. The length of window should be less than or equal to the total number
of time-series observations per panel.


Other Options

1. nmiss

The option nomiss forces asrol to find required statistics
with all available observations, which results in no missing values at the
start of the panel. Compare results of Example 1 below where n is not used with the results of
the Example 3 where n is used.
In the example 1, asrol finds mean starting with the
fourth observation of each panel, i.e. the rolling
window does not start working unless it
reaches the required level of 4 observations.


2. minmum
The option minmum forces asrol to find required statistics
where the minimum number of observations are available. If a specific
rolling window does not have that many observations, values
of the new variable will be replaced with missing values.


Example 1: Find Rolling Mean

    . webuse grunfeld
 
    . asrol invest, stat(mean) win(4) gen(mean_4)

This command calculates mean for the variable invest using
a four years rolling window and stores the results in a new variable called
mean_4.


Example 2: Find Rolling Standard Deviation

    . webuse grunfeld
 
    . asrol invest, stat(sd) win(6) gen(sd_6)
 ​

This command calculates standard deviation for the
variable invest using a six years rolling window and stores the
results in a new variable called sd_6

Example 3: For Rolling Mean with no missing values

    . webuse grunfeld
 
    . asrol invest, stat(mean) win(4) gen(sd_4) nomiss
 ​

This command calculates mean for the variable invest using
a four years rolling window and stores the results in a new variable
called mean_4. The nomiss option forces asrol to find mean with
all available observation, which results in no missing
values at the start of the panel. Compare results where
nomiss is not used in example 1 above.
In the example 1, asrol finds mean starting with the
fourth observation of each panel, i.e.
the rolling window does not start working unless it
reaches the required level of 4 observations.

Example 4: Rolling mean with minimum number of observaton

    . webuse grunfeld
 
    . asrol invest, stat(mean) win(4) gen(mean_4) min(3)
​

Example 5: Rolling mean with minimum number of observation including the start of the panel

    . webuse grunfeld
 
    . asrol invest, stat(mean) win(4) gen(mean_4) min(3) nomiss

 ​

This command forces asrol to calculate mean for the variable
invest using a four years rolling window and stores the results in a new variable
called mean_4. The n option and the min(3) force asrol to find mean with at least 3
available observations even at the start of each panel i.e. asrol will not wait
until 4 observations are are available, it will start calculation when at least
three observations are available.

[Robert Picard]

The following is copied from the current version of asrol (*! Attaullah Shah 2.1.0 31Aug2015, as obtained from SSC) and contains the core functionality of the program:

    local upper = `window' - 1
    tsrevar L(1/`upper').`varlist'
    local varlags `r(varlist)'

    egen `gen' = row`stat'(`varlist' `varlags') if `touse'
    
    if `minimum' > 0 {    
        tempvar NONM
        egen `NONM' = rownonmiss(`varlist' `varlags')
        qui replace `gen' = . if `NONM' < `minimum'
    }

The following is an excerpt for tsrollstat:

    local lastp = `window' - 1

    tsrevar L(1/`lastp').`varlist'
    local rollingvars `r(varlist)'
    
    qui egen `double' `generate' = row`stat'(`varlist' `rollingvars')
    
    tempvar n
    egen `n' = rownonmiss(`varlist' `rollingvars')
    qui replace `generate' = . if `n' < `minimum'

I posted the tsrollstat code on Statalist in April 2015 in this post. My program eventually morphed into tsegen (with Nick Cox, and available from SSC). I would encourage anyone who stumbles upon asrol to skip the copy and use tsegen instead.

[Attaullah Shah]

I had been working on asrol since the start of year 2014 and completed it in November 2014. Then I posted it as solution to a problem reported in http://www.statalist.org/forums/foru...dard-deviation. The first version of the program was based on the idea of creating a loop for the user defined rolling window. However, as is the problems with loops, the code was not time efficient. I do acknowledge that when Robert highlighted the importance of tsrevar command in a post, I gave it a serious thought and incorporated it in version 2 of my code. Now if my code has similarity with one of his posts in terms of the use of tsrevar , that might be that the Stata command follow a set structure of arguments. However, asrol still follows the same format as it had a year ago, and still follows the same output style as it had a year ago when tsegen was not even created. Specifically, asrol will not calculate, unless forced, desired statistics in the start of the panels until the required observations are available as specified in the user's rolling window. On the other hand, as a default, tsegen will calculate said statistics even for one observation in a rolling window of 30. So the in letter and spirit, asrol maintains its original approach to calculating rolling window statistics as it had since day one i.e November 2014.

[Mike Kraft]

Dear Attaullah and Robert

Does any of your programs solve the issue here in the following recent post?

http://www.statalist.org/forums/foru...esidual-values

Thanks
Mike

[Robert Picard]

Sure, see my suggestion here.

[Kate Lussy]

Hello,

I am trying to calculate momentum of returns as a control variable for a fama/macbeth multivariate regression. The momentum is based on the eleven-month return starting twelve months prior to the measurement month and ending one month prior to that month. I am wondering if I could use asrol for this, and if so, what the code would be?

I really appreciate any feedback. Thank you!

[Attaullah Shah]

Dear Attaullah and Robert

Does any of your programs solve the issue here in the following recent post?

http://www.statalist.org/forums/foru...esidual-values

Thanks

You can download asreg from SSC and read its help file.

ssc install asreg
help asreg

The help file provides several examples of estimating regressions, finding their fitted and residual values. And yes, the regressions can be recursive.

[Attaullah Shah]

Kate Lussy
To estimate regressions, you need asreg, not asrol
If your data is tsset, then yes, asreg can help you here. However, I would like to see your dataset before giving you any code. Please use dataex program and post a sample of your data.

[Kate Lussy]

Thank you Attaullah Shah for your response. I appreciate it. For the calculation for momentum, it is simply based on returns, not a regression. It is based on the eleven-month return starting twelve months prior to the measurement month and ending one month prior to that month. But, I have to do that for every firm and every month in my dataset.

* Example generated by -dataex-. To install: ssc install dataex
clear
DSCode RI Return Excess_USD ymdate HML MKT SMB BVMT_USD Size REV Beta COSK IVOL
"13259U" 2.37 -.024691384 -.02634972 703 -.054840703234 .00346251894147 .0386864559196 1.3333334 5.627365 -.024691384 -.16394405813383447 10.609911748829868 .07835993
"13259U" 2.29 -.033755228 -.035480227 704 .0183218799727 -.000739155121998 -.0284784797463 1.388889 5.590998 -.033755228 -.149676972914776 11.668920976875334 .07835993
"13259U" 2.27 -.008733608 -.010525274 705 .0764128307158 -.0566824583153 -.0231824754941 1.388889 5.583563 -.008733608 -.11539952221177874 9.59764314181156 .07835993
"13259U" 2.37 .04405287 .04214454 706 -.00164130725968 -.0146753957915 -.0410007995891 1.3333334 5.627365 .04405287 -.06774141268755865 9.5600131054209 .07835993
"13653C" 17.9 .28315413 .22273146 564 .047180898168 .0162405793499 .0266483189455 .5235294 5.228758 .22680646 . . .09179982
"13653C" 17.45 -.025139645 -.0247477 565 -.00716603637563 -.0135251981654 .0163826726498 .54966444 5.224848 -.02058937 . . .09179982
"13653C" 18.15 .04011457 .02867883 566 .0191693360983 .0296540636145 .000227463615779 .52838707 5.264481 .03285383 2.4128017182909502 . .09179982
"13653C" 19.1 .05234162 .03930787 567 -.0258189869023 .0674881603753 .0301072445097 .4993976 5.327122 .0433912 .24070722992957494 -71.41228002708613 .09179982
end

[Attaullah Shah]

OK, so you are interested in implementing a momentum strategy. Since such strategies involve the overlapping formation and holding periods, the steps are usually complicated. I had developed a customized Stata program, called asm.ado. This program offers lots of options related to momentum strategies. The program is available for a nominal fee. You can see further details here on my website
https://fintechprofessor.com/stata-p...um-portfolios/

[Lu Asegu]

Dear all,

I am beginner in Stata and new to Statalist.
I am working on a panel data (5 companies and 5 year, as an example) on 3 year rolling window. I looked at previous posts about most related posts but could not find one related to my problem:
I wanted to generate a relative volatility (sd) for company1 calculated as: [sd of company1 - average sd of the remaining companies] calculated on the same window for each year and for each company.
I used the grunfeld data for illustration. This data has no missing value; it seems to me that once I get around the sum of {remaining company's sd}, the denominator is 4, but my panel has some firms with missing sd in some years, so I also want the denominator (number of remaining companies) to account for the missing values.

I used both rangestat and asrol codes to get similar resulsts:

rangestat (sd) invest (count) invest,interval(year -4 0) by(company year)

and

by company : asrol invest , stat( count) window( year 5) gen( c_5)

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(company year invest) double(invest_sd invest_count c_5)
1 1935 317.6                  . 1 1
1 1936 391.8  52.46731021652997 2 2
1 1937 410.6 49.173295549502065 3 3
1 1938 257.7  70.39016196984072 4 4
1 1939 330.8  61.26344248216677 5 5
1 1940 461.2  78.40294701117284 5 5
2 1935 209.9                  . 1 1
2 1936 355.3 102.81332166868673 2 2
2 1937 469.9 130.30369630471188 3 3
2 1938 262.3 114.15152266416001 4 4
2 1939 230.4 107.41623704182074 5 5
2 1940 361.6  94.26035923051472 5 5
3 1935  33.1                  . 1 1
3 1936    45  8.414571775079237 2 2
3 1937  77.2 22.815417686293515 3 3
3 1938  44.6 18.970217023300876 4 4
3 1939  48.1  16.45007524096321 5 5
3 1940  74.4 16.462624355533045 5 5
4 1935 40.29                  . 1 1
4 1936 72.76 22.959758048294656 2 2
4 1937 66.26 17.180376124500224 3 3
4 1938  51.6 14.610411384251309 4 4
4 1939 52.41 12.874519569082045 5 5
4 1940 69.41  9.845948748137495 5 5
5 1935 39.68                  . 1 1
5 1936 50.73  7.813529392631689 2 2
5 1937 74.24   17.6503821274382 3 3
5 1938 53.51 14.427826282436541 4 4
5 1939 42.65 13.579250216891362 5 5
5 1940 46.48 12.296554063517455 5 5
end
format %ty year

Any help or advice please?
Thank you in advance.

Kind Regards,
Lu

[Nick Cox]

I find this difficult to follow. You say you want a 3 year rolling window but work with 5. You say you want SDs, but your code doesn't calculate them.

I haven't seen

SD for this panel MINUS mean of SDs for other panels

used as a measure in any literature. Attaullah Shah will know your literature better than I do and may be able to comment. (I am pretty queasy about averaging SDs, especially those based on small samples, but there you go.)

As for how to get that, in rangestat (SSC) you would need to write your own code, or use two passes, something like this:

webuse grunfeld, clear
rangestat (sd) invest (count) invest, interval(year -4 0) by(company)
rangestat (count) count_others=sd_invest (mean) sd_others=sd_invest, interval(company 0 0) by(year) excludeself
gen wanted = sd_invest - sd_others

rangestat naturally takes account of the denominator to use if there are missing values and you can and should keep track of how many values you have.


Note that

 
 rangestat (sd) invest (count) invest,interval(year -4 0) by(company year)

really shouldn't produce similar results, as you are asking for the SD of individual values, which Stata will return as missings.