## ASROL Version update: calculation of geometric mean and products in a rolling window and over groups in Stata

Category:Uncategorized## 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 be used for the calculation of a variety of statistics [see table of contents].

## Installation

ssc install asrol

After installation, you can read the help file by typing:

help asrol

## Options and defaults

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

**a. Missing values** are ignored when calculating the product or the geometric mean of values.

**b. Handling zeros in geometric mean: **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)

**c. Handling zeros in products: **Zeros are considered when calculating the product of values. So the product of 0,2,4,6 is 0

**d. 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

**e. 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.

## Examples

Let us start with simple examples of calculating the geometric mean and products. Our example data has stock prices, company identifiers (symbols) and time identifier (date)

use http://fintechprofessor.com/stocks.dta, clear* Generae numeric identifier for each firm

encode symbol, gen(id)* Declear the data as panel data

tsset id date

*Create stock returns

gen returns = d.close/l.close* Note the above formula for stock returns is analogous to

gen returns2 = (close - L.close) / L.close

### Geometric mean

** **Now find geometric mean for stock returns, adding 1 before calculation and subtracting the same after calculation. The calculations are made for each firm in a rolling window of 20 observations

bys id: asrol returns, stat(gmean) window(date 20) add(1)

### Products – the case of cumulative returns

Since we find products of (1+returns) for finding cumulative returns over n-periods, we can use the product function of asrol [read this blog entry for more more details on simple and log returns.

Cumulativen-period simple returns =(1+simple_r1) * (1+simple_r2)

*(1+simple_r3) ... (1+simple_rn) - 1 ---(Eq. 1)

The asrol command for the 20-periods rolling window cumulative returns would be:

bys id: asrol returns, stat(product) window(date 20) add(1)

## Option to ignore zeros

Option ** ignorezero **or

**can be useful when we want to exclude zeros from the calculation of products. So let’s say we have the variable x that has values of 1, 2, 3, 0, and 5. Finding**ig

asrol x, stat(product) ig

list

+--------------+

| x produc~x |

|--------------|

| 1 30 |

| 2 30 |

| 3 30 |

| 0 30 |

| 5 30 |

+--------------+

Without using the option ig, the product would be zero

asrol x, stat(product) gen(pro_withoutig)

. list`+-------------------------+`

`| x produc~x pro_wi~g |`

`|-------------------------|`

| 1 30 0 |

| 2 30 0 |

| 3 30 0 |

| 0 30 0 |

| 5 30 0 |

+-------------------------+

## A note on the methods used

Previous versions of asrol used *n*th root of the products of *n* numbers.

or a set of numbers *x*_{1}*,* *x*_{2}, …, * x _{n}*, the geometric mean is defined as

where the capital pi notation shows a series of multiplications.

Similarly, the products are calculated as :

Product =x1 *x2 ...xn