version: 5.8
help asrol Feb 29 1, 2024

Title

asrol - Generates rolling-window / groups descriptive statistics

Syntax

[bysort varlist]: asrol
varlist [if] [in],
stat(stat_options)
window(rangevar range_low range_high)
[gen(newvar)
by(varlist)
minimum:(#)
add(#)
ignorezero
perc(#)
xf([focal | rangevar])]

The underlined letters signify that the full words can be abbreviated only to the underlined letters.

Description

asrol calculates descriptive statistics in a user's defined rolling-window or over a grouping variable. asrol
can efficiently handle all types of data structures such as data declared as time series or panel data, undeclared data, or data with
duplicate values, missing values or data having time series gaps. asrol can
estimate most commonly used statistics such as mean, standard deviation, minimum,
maximum, percentiles, median, missing, count, first, last, product, and geometric
mean. asrol can also find these statistics while excluding focal observations,
or observations defined by a grouping variable.

What's New in Version 5.0

1. Changes made to the window() option

Version 5.0 of asrol introduces a more flexible window to identify observation
in a given range. Previously, the window() option of asrol would take two
inputs: the first one being the range variable (such as date) and the second
input as the length of the rolling window. In the older versions, the window
would always look backward. This has changed in version 5.

The window argument can now take up to three arguments. The window can now look
backward, forward, and both back and forward. More details can be read in Section

2. Improved algorithm for rolling window indices

This version of asrol (version 5.0) significantly improves the
calculation speed of the required statistics,
thanks to the development of a more efficient algorithm for extracting rolling
window indices. This has resulted in significant speed advantage for asrol compared
to its previous versions or other available programs. The speed efficiency matters
more in larger datasets. While writing the source code of asrol, I took
utmost care in making choices among available options. Therefore, every line of
code had to undergo several tests to ensure accuracy and speed. In fact, there
is a long list of of built-in routines in asrol which are meant to handle
different data structures. asrol intelligently identifies data structures
and applies the most relevant routine from its library. Hence, asrol speed
efficiency is ensured whether the data is rectangular (balanced panel),
non-rectangular, has duplicates, has missing values, or has both duplicates
and missing values.

Syntax Details

The program has one required option and 8 optional options: Details are given below:

Options

1. stat: to specify required statistics. This version of
asrol supports multiple statistics for multiple variables. The following
statistics are allowed;

Descriptive Statistics

sd Estimates the standard deviation of non-missing values

mean Finds the arithmetic mean of non-missing values

gmean Finds the geometric mean of positive values

sum Adds all the numbers in a given window

product Multiplies all the numbers in a given window

median Returns median of non-missing values

skewness Returns skewness of non-missing values

kurtosis Returns kurtosis of non-missing values

count Counts the number of non-missing observations in a given window

missing Counts the number of missing values in a given window

min Returns the smallest value in a given window

max Returns the largest value in a given window

max2 Returns the second largest value in a given window

max3 Returns the third largest value in a given window

max4 Returns the fourth largest value in a given window

max5 Returns the fifth largest value in a given window

first Returns the first observation in a given window

last Returns the last observation in a given window

perc(k) Returns the k-th percentile of values in a range. This option must be used
in combination with the option median. See more details in the section below that discusses the
perc(#) option.

add(#) Adds the value # to each value in a given window before computing the geometric mean or
products of values. See more details in Section 7-4.

ignorezero used with product and gmean statistics. See more details in Section 7-4.

Optional Options

1. The window():

The latest version of asrol accepts up to three
arguments and is written like this:

window(rangevar #from #upto)

The rangevar is usually a time variable such as date, monthly date,
or yearly date. However, it can also be any other numeric variable, such as age,
income, industry indicator, etc.
Both #from and #upto are numeric values that sets the lower and upper
bounds on the rolling window, using the current value of the rangevar as a benchmark.
Negative values of these inputs mean going back # periods from the current
value of the rangevar. Similarly, positive values of these imply going ahead # periods
of the current value of the rangevar.

Please note:

1. asrol considers the focal observation as part of the past.

2. Given the legacy of asrol, a window() with two inputs is still supported.
However, the folloiwng rule should be noted:

If option window() has two arguments, it shall always look back.
In other words, whether you enter a positive or a negative number for the second
argument, it will always be counted as a negative number. Therefore, the following
window() options mean the same thing.

window(year 50)

window(year -50 0)

window(year -50)

In the following paragraphs, I present some examples to make it easier to understand
option window().

Window examples and interpretations

The following table presents examples of the window() option and its interpretations.

window examples

window(year -5 0) A rolling window of past 5 observations. Therefore, if focal year is 2006, the window will include these years in the calculations: 2006, 2005, 2004, 2003, 2002. The window can also be written
as window(year 5)

window(year 1 5) A rolling window of 5 leading observations. Therefore, if focal year is 2006, the window will include these years in the calculations: 2007, 2008, 2009, 2010, 2011.

window(year -13 -2) A rolling window of past 11 observations from t-12 upto t-2.
Therefore, if focal year is 2006, the window will include these years in the calculations:
1994, 1995, 1996, 1997 , 1998, 1999, 2000, 2001, 2002, 2003, 2004.

window(year -5 5) A rolling window of 10 observations.
Therefore, if focal year is 2006, the window will include these years in the
calculations: 2002, 2003, 2004, 2005, 2006, 2007, 2008, 2009, 2010, 2011.

window(year 0 1) A rolling window of 2 observations.
Therefore, if focal year is 2006, the window will include these years in the
calculations: 2006, and 2007. Since asrol considers focal observation as
pertaining to the past, therefore, writing the window as window(year -1 1) will produce exactly the
same results.

Note on missing values in the rangevar: Some posts on the Statalist
show a confusion concerning the interpretation of the option window().
For example, using window(year -5), a user might incorrectly assume that
the observation in this range will always be 5. This might not be true in a
case where the rangevar has gaps. So if the current value of the rangevar
(in our case year) is 2006 and its previous values are 2000, 2001, 2003, 2005.
Though the window asks for 5 observations within the range of 2006, the available
values of the rangevar are only 2006, 2005, 2003, that are only 3 observations. The window
range ends at 2002, other values of the year are outside the window bounds.

Rolling window calculations

The default for rolling window is to calculate required statistics on
available observation that are within the range. Therefore, the calculations
of the required statistics start with one observation at the beginning of the
rolling window. As we progress in the data set, the number of observations gradually
increase until the maximum length of the rolling window is reached. Consider
the following data of 10 observations, where X is the variable of
interest for which we would like to calculate arithmetic mean in a rolling window
of past 5 observations; and months is the rangevar. To understand
the mechanics of the rolling window more clearly, we shall generate
three additional statistics: count, first, and last.

bys id: asrol X, window(months -5 0) stat(count) gen(count)
bys id: asrol X, window(months -5 0) stat(mean) gen(mean)
bys id: asrol X, window(months -5 0) stat(first) gen(first)
bys id: asrol X, window(months -5 0) stat(last) gen(last)

+--------------------------------------------------------+
| id months X mean count first last |
|--------------------------------------------------------|
| 1 2016m10 .6881 .6881 1 .6881 .6881 |
| 1 2016m11 .9795 .8338 2 .6881 .9795 |
| 1 2016m12 .6702 .7792 3 .6881 .6702 |
| 1 2017m1 .5949 .7331 4 .6881 .5949 |
| 1 2017m2 .7971 .7459 5 .6881 .7971 |
|--------------------------------------------------------|
| 1 2017m3 .7836 .765 5 .9795 .7836 |
| 1 2017m4 .6546 .7001 5 .6702 .6546 |
| 1 2017m5 .0968 .5854 5 .5949 .9689 |
| 1 2017m6 .6885 .6041 5 .7971 .6885 |
| 1 2017m7 .8725 .6192 5 .7836 .8725 |
+--------------------------------------------------------+

Explanation

For the first observation, that is 2016m10, the mean value is based on
a single observation, as there are no previous data. The same is reflected by
the variables count, first, and last. For the
second observation, the mean value is based on two observations of X,
i.e., (0.6881 + .9795) / 2 = .8338. We can also observe such details
from the variable count, that has a value of 2; variable
first which shows that the first value in the rolling window this
far is .6881 and last, which shows that the last value in the
rolling window is .9795. As we move down the data points, the rolling window
keeps on adding more observations until the fifth observation, i.e. 2017m2.
After this observation, the observations at the start of the rolling window
are dropped and more recent observations are added. It is pertinent to mention
that users can limit the calculations of required statistics until a minimum
number of observations are available, see option minimum
for more details.

No Window

Since the option window is optional, it can be dropped altogether. In such a case, asrol can be used like gen or egen.
When used with bysort prefix, asrol can closely match the performance of egen in calculating statistics by groups.

2. gen: This is an optional option to specify name of the new variable, where the variable name is enclosed in parenthesis after gen.
If we do not specify this option, asrol will automatically generate a new variable with the name format of stat_rollingwindow_varname. When finding mulitple statistics,
one statistic for multiple variables, or multiple statistics for multiple variables, asrol will automatically assign names to the new
variables. Therefore, option gen cannot be used in such cases.

3. minimum(#)

The option minimum forces asrol to find required statistics only when the minimum
number of observations are available. If a specific window does not have that many
observations, values of the new variable will be replaced with missing values. Please note that # is an integer and should be
greater than zero. Therefore, min(0), min(-5), or min(1.5) are treated as illegal commands. Examples of legal
commands are min(2), min(10), or min(100).

4. by( varlist )

asrol is byable and hence the required statistics can be calculated using a single variable or multiple variables as sorting filter. For example, we can find mean profitability
for each company in a rolling window of 5 years. Here, we use a single filter, that is company. Imagine that we have a data set of 40 countries, each one having 60 industries, and each industry
has 1000 firms. We might be interested in finding mean profitability of each industry within each country in a rolling window of 5 years. In that case, we shall use the option by or using the bysort prefix.
Hence both of the following commands yield similar results. However, the command with bysort prefix has some speed advantage.

asrol profitability, window(year -5 0) stat(mean), by(country industry)

bys country industry : asrol profitability, window(year -5 0) stat(mean)

6. perc(k)

This is an optional option. Without using perc(k) option, stat(median) finds the
median value or the 50th percentile of the values in a given window. However, if option perc(k) is specified, then
the stat(median) will find k-th percentile of the values in range. For example, if we are interested
in finding the 75th percentiles of the values in our desired rolling window, then we have to invoke the option perc(.75)
along with using the option stat(median). See the following example:

bys country industry : asrol profitability, window(year -5 0) stat(median) perc(.75)

Note :

The calculation of percentiles follows a similar method as used in summarize and _pctile as defaults. Therefore,
the percentile values might be slightly different from the values calculated with { help centile}. For details related to different definitions
of percentiles, see Hyndman and Fan (1996).

7. Options related to product and gmean : add(#) and ignorezero

This version of asrol improves the calculation of product of values and the geometric mean.
Since both the statistics involve multiplication of values in a given window, the presence of missing values and zeros present
a challenge to getting desired results. Following are the defaults in asrol to deal with missing values and zeros:

7.1 : Missing values are ignored when calculating the product or the geometric mean of values.

7.2 : To be consistent with Stata's default for geometric mean calculations,
(see ameans), the default in asrol is to ignore zeros and negative numbers. So the geometric mean
of 0,2,4,6 is 3.6342412, that is [2 * 4 * 6]^(1/3). And the
geometric mean of 0,-2,4,6 is 4.8989795, that is [4 * 6]^(1/2).

7.3 : Zeros are considered when calculating the product
of values. So the product of 0,2,4,6 is 0

Two variations are possible when we want to treat zeros differently. These are discussed below:

7.4 Option ignorezero: This option can be used to ignore zeros
when calculating the product of values. Therefore, when the zero is
ignored, the product of 0,2,4,6 is 48

7.5 Option add(#) : This option adds a constant #
to each values in the range before calculating the product or the
geometric mean. Once the required statistic is calculated, then the
constant is substracted back. So using option add(1), the product of 0,.2,.4,.6 is
1.6880001 that is [1+0 * 1+.2 * 1+.4 * 1+.6] - 1
and the geometric mean is .280434 is [(1+0 * 1+.2 * 1+.4 * 1+.6)^(1/4)] - 1.

The Stata's ameans command calculates three types of means, including the geometric mean.
The difference between asrol' gmean function and the Stata
ameans command lies in the treatment of option add(#).
ameans does not subtract the constant # from the results,
whereas asrol does.

8. xf(excluding focal observation)

The xf is an abbreviation that I use for "excluding focal". There might be circumstances where we want
to exclude the focal observation while calculating the required statistics. asrol allows excluding focal
observation with two flavors. The first one is to exclude only the current observation while
the second one is to exclude all observation of the relevant variable if there are similar (duplicate) values of
the rangevar elsewhere in the given window. An example will better explain the distinction between the two
options. Consider the following data of 5 observations, where X is the variable of interest for which we would
like to calculate arithmetic mean and year is the rangevar. Our calculations do not use any rolling window, therefore the option window is dropped.

Example A:

asrol X, stat(mean) xf(focal) gen(xfocal)

Example B:

asrol X, stat(mean) xf(year) gen(xfyear)

+---------------------------------------+
| year X xfocal xfyear |
|---------------------------------------|
| 2001 100 350 350 |
| 2002 200 325 325 |
| 2003 300 300 266.66667 |
| 2003 400 275 266.66667 |
| 2004 500 250 250 |
+---------------------------------------+

Explanation :

In Example A, we invoke the option xf() as xf(focal). asrol generates a new variable xfocal that contains the mean values of the rest of
the observations in the given window, excluding the focal observation. Therefore, in the year 2001, xfocal variable
has a value of 350, that is the average of the values of X in the years 2002, 2003, 2003, 2004 i.e.
(200+300+400+500)/4 = 350. Similarly, the second observation of the xfocal variable is 325, that is
(100+300+400+500)/4 = 325. Similar calculations are made when required statistics are estimated in a rolling window.

Example B
differs from Example A in definition of the focal observation(s). In Example B,
we invoke the option xf() as xf(year), where year is an existing numeric variable. With this option, the focal observation(s) is(are) defined as the current observation and other observations where the focal observation
of the rangevar has duplicates. Our data set has two duplicate values in the rangevar, i.e., year 2003.
Therefore, the mean values are calculated as shown bellow:

+-------------------------------------------------------+
|	obs 1: (200 + 300 + 400 + 500)/4 = 350 |
|	obs 2: (100 + 300 + 400 + 500)/4 = 325 |
|	obs 3: (100 + 200 + 500 ) /3 = 266.66667	|
|	obs 4: (100 + 200 + 500 ) /3 = 266.66667	|
|	obs 5: (100 + 200 + 300 + 400)/4 = 250 |
+-------------------------------------------------------+

Examples

Example 1: Find arithmetic mean in a rolling window of 4 observations for each group (company)

The following command calculates arithmetic mean for the variable invest
using a four years rolling window (current year and three past years) and
stores the results in a new variable, mean4_invest.

. webuse grunfeld

. bys company: asrol invest, stat(mean) win(year 4)

Or using the new window style of Version 5.0 *

. bys company: asrol invest, stat(mean) win(year -4 0)

* Note: Given the legacy of asrol that it used to accept only
two inputs in the window option, the
old behavior is still supported. Therefore, if you type just two inputs in the
window() option, asrol would always consider it as a back-looking window.
Therefore, window(year 4) and window(year -4 0) mean the same thing, i.e.,
it is a 4-years rolling window that includes the current observations and three
past observations.

Example 2: Geometric mean in a rolling window of 10 periods: 5 past and 5 forward

. webuse grunfeld

. bys company: asrol invest, stat(gmean) win(year -5 5)

Example 3: Geometric mean with add(1) option : 5-periods lead window

. bys company: asrol invest, stat(gmean) win(year 0 5) add(1) gen(gmean10)

This command calculates the geometric mean for the invest variable
using a 5-years rolling-window. The observations in range will include forward five years.
The results are stored in a new variable, gmean10.

Example 4: Find Standard Deviation: last 5 years excluding the current observation or the most recent one

. webuse grunfeld

. bys company: asrol invest, stat(sd) win(year -5 -1)

The above command calculates standard deviation for the invest variable
using a 5 years rolling window, from past 5 years, excluding the most recent
observation (the focal observation). Therefore, this window uses effectively
4 observations.

Example 5: Find 75th percentile in a rolling window of 4 years

. webuse grunfeld

. bys company: asrol invest, stat(median) win(year 4) perc(.75)

OR

. bys company: asrol invest, stat(median) win(year -4 0) perc(.75)

Example 6: 5-period rolling median with minimum of 3 observatons

. webuse grunfeld

. asrol invest, stat(median) win(year 5) min(3)

Example 7: Excluding focal observation

. webuse grunfeld

. bys company: asrol invest, stat(mean) xf(focal)

Example 8: Excluding focal by groups

. webuse grunfeld

. bys company: asrol invest, stat(mean) xf(company)

Explanation: The above command finds standard deviation
for whole sample while excluding the focal company. Therefore, when the company
is equal to 1, the standard deviation is based on the remaining 19 companies.
Similarly, when the comapny is 2, the standard deviation is based on the first
company, and companies 3 to 20.

Example 9: Find 5-periods rolling mean for three variables in one go

. webuse grunfeld

. bys company: asrol invest mvalue kstock, stat(mean) win(year -5 0)

Example 10: Find 5-periods rolling mean, standard deviation, and count for three variables in one go

. webuse grunfeld

. bys company: asrol invest mvalue kstock, stat(mean sd count) win(year -5 0)

Example 11: Using by option for two or three variables

We shall generate a dummy data of 5 countries, 10 industries, 100 months, and 5000 firms for further examples.

copy the following code and run from do editor

clear
set obs 5000
gen company=_n
expand 100
bys company: gen months=_n+600
format %tm months
bys company: gen industry=mod(company, 10)+1
bys industry: gen country=mod(industry, 5)+1
order company months industry country
sort company months
gen returns = 0.0071 + 0.0436*(rnormal())
sort company months

Example 12: Mean by country and industry in a rolling window of 10 months

. bys country industry: asrol returns, stat(mean) win(months -10 0)

Example 13: Mean by country and industry without a rolling window

. bys country industry: asrol returns, stat(mean)

Example 14: Mean by country and industry in a rolling window of 12 months and excluding focal observation

. bys country industry: asrol returns, stat(mean) win(months -12 -1)

Example 15: Mean by country in a rolling window of 12 months and excluding focal observation based on a grouping variable: in our case let us use the grouping variable indstry

. bys country: asrol returns, stat(mean) win(months -12 0) xf(industry)

Example 16: 11-months cumulative returns over t-12 up to t-2 months

. bys comapny: asrol returns, stat(product) add(1) win(months -13 -2)

Explanation:
The above interpretation of the option window might be confusing when
implementing a definition that is based on t-# up to t-#2. However,
the above example should clarify such a confusion, if any. Consider a
stock momentum strategy that asks for cumulative returns over the previous
11 months from t-12 upto t-2. Let's say that we estimate this cumulative
return using the product method and adding 1 to the returns, which asrol
will automatically deduct at the end. Now assume that we are currently
at the end of December 2019. This strategy will include the
following months in the calculations which are shown in bold face
and exclude the ones that are shown without the bold face :
Dec-18, Jan-19, Feb-19, Mar-19, Apr-19, May-19, Jun-19, Jul-19, Aug-19, Sep-19, Oct-19, Nov-19, Dec-19.
From Dec-18 upto Dec-19, there are 13 months.
To implement this using asrol, the window argument is written as:

window(months -13 -2)

Author

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: *
* *
* Dr. Attaullah Shah *
* Institute of Management Sciences, Peshawar, Pakistan *
* Email: attaullah.shah@imsciences.edu.pk *
* www.OpenDoors.Pk *
* www.FinTechProfessor.com *
*:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::*

Also see

asm : for momentum portfolios

asdocx : Easily create publication-quality tables from Stata and export to Word, Excel, LaTeX

asdoc : Easily create publication-quality tables from Stata

astile : for creating fastest quantile groups

asgen : for weighted average mean

asrol : for rolling-window statistics

asreg : for rolling-window, by-group, and Fama and MacBeth regressions

ascol : for converting asset returns and prices from daily to a weekly, monthly, quarterly, and yearly frequency

searchfor : for searching text in data sets

flexmat : Creates a flexible matrix of real, string and complex elements

"ssc desc fillmissing":fillmissing: Fills missing values in a variable based on a given criterion}

Acknowledgements

For creating group identifiers, I could have used egen's function, group. But for speed efficiency,
Nick Cox's solution of creating group idnetifier was preffered( See here).
For finding median in the Mata language, I used the approach suggested by Daniel Klein,
( See here)