Time Series Analysis (Moving Average Filters)

Description

This article describes several filters for the analysis of time series data, including:

• Simple Moving Average (SMA)
• Exponential Moving Average (EMA)
• Linear Regression Moving Average (LRMA)

Model theory

This section is currently under construction. Please check back later for updates and revisions.

Excel

Figure 1. Example showing the selection of the TimeSeriesData (blue frame), k (red frame) and Results (light blue frame) arrays in Excel, for the Simple Moving Average (SMA) case.

The Simple Moving Average (SMA) filter may be invoked from the Excel formula bar with the following function calls:

=mdTimeSeries_SMA(TimeSeriesData as Range, k as Integer)

Invoking the function with no arguments will print Help text associated with the model, including a link to this page.

The input parameters and calculation results are defined below in matrix notation, along with an example image showing the selection of the same cells and arrays in the Excel interface:

${\begin{aligned}{\mathit {TimeSeriesData}}&={\begin{bmatrix}p_{1}\\\vdots \\p_{n}\\\end{bmatrix}}\\\\{\mathit {k}}&={\big [}k{\big ]}\\\end{aligned}}$ ${\begin{aligned}{\mathit {mdTimeSeries\_SMA}}&={\begin{bmatrix}{\rm {SMA}}_{k,1}\\\vdots \\{\rm {SMA}}_{k,n}\\\end{bmatrix}}\end{aligned}}$ where: $n$ is the number of data points $p$ are the data points to average $k$ is the number of previous data points over which the moving average is taken ${\rm {SMA}}_{k}$ is the value of the simple moving average over the previous $k$ data points

The Exponential Moving Average (EMA) filter may be invoked from the Excel formula bar with the following function calls:

=mdTimeSeries_EMA(TimeSeriesData as Range, alpha as Double)

Invoking the function with no arguments will print Help text associated with the model, including a link to this page.

The input parameters and calculation results are defined below in matrix notation, along with an example image showing the selection of the same cells and arrays in the Excel interface:

${\begin{aligned}{\mathit {TimeSeriesData}}&={\begin{bmatrix}p_{1}\\\vdots \\p_{n}\\\end{bmatrix}}\\\\{\mathit {alpha}}&={\big [}\alpha {\big ]}\\\end{aligned}}$ ${\begin{aligned}{\mathit {mdTimeSeries\_EMA}}&={\begin{bmatrix}{\rm {EMA}}_{k,1}\\\vdots \\{\rm {EMA}}_{\alpha ,n}\\\end{bmatrix}}\end{aligned}}$ where: $n$ is the number of data points $p$ are the data points to average $\alpha$ is the smoothing factor ${\rm {EMA}}_{\alpha }$ is the value of the exponential moving average

The Linear Regression Moving Average (LRMA) filter may be invoked from the Excel formula bar with the following function calls:

=mdTimeSeries_LRMA(TimeSeriesData as Range, k as Integer)

Invoking the function with no arguments will print Help text associated with the model, including a link to this page.

The input parameters and calculation results are defined below in matrix notation, along with an example image showing the selection of the same cells and arrays in the Excel interface:

${\begin{aligned}{\mathit {TimeSeriesData}}&={\begin{bmatrix}p_{1}\\\vdots \\p_{n}\\\end{bmatrix}}\\\\{\mathit {k}}&={\big [}k{\big ]}\\\end{aligned}}$ ${\begin{aligned}{\mathit {mdTimeSeries\_LRMA}}&={\begin{bmatrix}{\rm {LRMA}}_{k,1}\\\vdots \\{\rm {LRMA}}_{k,n}\\\end{bmatrix}}\end{aligned}}$ where: $n$ is the number of data points $p$ are the data points to average $k$ is the number of previous data points over which the linear regression moving average is taken ${\rm {LRMA}}_{k}$ is the value of the linear regression moving average of the previous $k$ data points