Descriptive Statistics

	Attachment	Size
	TUTORIAL-DESC_STAT.xls

To better understand the data series, we examine its probabilistic distribution characteristics: mean, standard deviation, skew, and kurtosis (or excess-kurtosis).

To do so in Excel, we can either compute the values manually, or we can use the NumXL toolbar. For our purposes, we'll use the NumXL toolbar.

Excel 2007: Using the NumXL tab, click DESC STAT.
Excel 97-2003: Using either the NumXL menu or toolbar, click DESC STAT.
The Descriptive Statistics dialog pops up. Fill in the fields with your data location, series time order, options and location of the results on your worksheet.
The Descriptive Statistics dialog will print out the selected statistics and tests (along with the formulas) into your worksheet. Note that our sample mean (average) is -0.03%.

TIP: One of the main advantages of NumXL is the ability for a function to process a data series with missing values (e.g. lagged or differenced).

Conditional Mean (WMAⁱ)

One of the primary objectives of an econometric analysis is to better understand and capture the time dynamics of a process's mean and volatility.

In practice, we use the Weighted Moving Average (WMA) as a proxy for the conditional mean, weighting the most recent observations highest to track the change in the process.
In general, the WMA smoothes the returns series and can give an accurate view of how the mean changes over time.

$wma_t^k = \frac{\sum_{i=0}^{k-1}x_{t-k+i}w_i}{\sum_{i=0}^{k-1}w_i}$

Where:

$w_i$ : Weight for i-th data point in rolling window

$k$ : Size of rolling window

$x_t$ : Time series value at time t

TIP: The WMA function calculates the weighted moving average for the whole series and returns an array.

For our illustration, we are using a 20-day equal-weighted WMA for MSFT daily log-returns series (black line).

WMA Plot

Please note that the WMA (and many other econometric functions) requires us to specify the direction of time within the data set as either ascending or descending. In our sample, we started with least recent data set on top and moved forward as we scroll down, so it is ascending. Your data set may be the reverse, in which case you should specify your direction as descending.

Conditional Volatility (EWV/EWMAⁱ)

In the case of volatility, the Exponential-Weighted Volatility (EWV) (also known as the Exponential-Weighted Moving Average (EWMA)) is widely used to approximate conditional volatility. This function is relatively simple to compute, yet robust enough to often give an accurate view of underlying volatility (red line and the right-side scale).

$\sigma_t^2 = \lambda\sigma_{t-1}^2+(1-\lambda)x_{t-1}^2$

Where:

$x_t$ : Time series value at time t

$\lambda$ : Smoothing constant (between 0 and 1)

EWMA Plot

TIP: The EWMA function calculates the Exponential-Weighted Volatility for the whole series and returns an array.

Please note, for the MSFT daily returns case, the volatility exhibits very high variability over time in comparison to the conditional mean. This will influence our choice of an econometric model, as we will see later in this document.

Summary

Examining the descriptive statistics gives us a basic idea of the daily returns distribution: the sample mean, standard deviation and probability of extreme movement (i.e. kurtosis). However, they do not shed any information on how those statistical properties change throughout the sample time frame or whether the sample values exhibit any interdependency.

The Exponential-Weighted Moving Average (EWMA) (a.k.a. Exponential-Weighted Volatility (EWV)) presents a proxy for the conditional mean and volatility, giving us a view of their dynamics over time.