Moving Averages

Overview

Moving averages are smoothing operators that convert a noisy sequence into a more stable signal by averaging nearby observations. In time-series analysis, the moving average is a practical low-pass filter: it suppresses short-term fluctuations while preserving broader level and trend behavior. This matters in forecasting, process monitoring, and financial analytics because many downstream decisions depend on separating persistent structure from transient noise.

The common foundation across this category is windowed aggregation and recursive smoothing. Windowed methods compute a local statistic over a finite neighborhood, while recursive methods update a state from the previous estimate and the newest sample. For a window size w, the simple moving average is \mathrm{SMA}_t = \frac{1}{w}\sum_{i=0}^{w-1} x_{t-i}, and for exponential smoothing with factor \alpha, \mathrm{EMA}_t = \alpha x_t + (1-\alpha)\mathrm{EMA}_{t-1}. Choosing w, \alpha, and boundary handling controls the bias-variance tradeoff, lag, and responsiveness of the smoothed output.

Implementation relies primarily on SciPy Signal and NumPy. SciPy provides production-grade filtering and convolution primitives (including recursive filters and configurable convolution methods), while NumPy provides efficient array operations used in rolling and cumulative computations. Together, these libraries form a standard numerical stack for robust, reproducible time-series preprocessing.

For exponential smoothing, EMA_LFILTER and EMA_PERIOD provide two parameterizations of the same core idea. EMA_LFILTER exposes the smoothing constant directly and computes the series with a first-order recursive filter, which is useful when analysts tune responsiveness explicitly. EMA_PERIOD derives \alpha from a user-facing period, making configuration more intuitive in dashboards and spreadsheet workflows. These functions are commonly used for denoising KPI streams, generating trend overlays, and creating responsive baseline signals for anomaly detection.

For simple rolling means, SMA_CONV and SMA_CUMSUM compute equivalent smoothing targets through different algorithms. SMA_CONV applies a uniform kernel through convolution and exposes output modes such as valid/same/full, which is helpful when boundary behavior or output length must be controlled. SMA_CUMSUM uses cumulative sums to compute full-window rolling means efficiently, which is often preferable for long univariate series and repeated recalculation. In practice, these tools support exploratory smoothing, feature engineering, and preprocessing prior to decomposition or forecasting.

For weighted windows, WINMA_CONV and WMA extend averaging beyond equal weights. WINMA_CONV performs normalized convolution with an arbitrary weight vector and configurable convolution backend, making it suitable for custom kernels and signal-processing-style workflows. WMA computes rolling weighted averages on complete windows only, which is convenient when each output must correspond to a fully observed local segment. These weighted variants are useful when recent observations should contribute more strongly, when domain-specific kernels are known, or when analysts need controlled smoothing without fully recursive dynamics.

EMA_LFILTER

This function computes an exponential moving average (EMA) by applying a first-order recursive filter.

With smoothing factor \alpha, the recursion is:

\text{EMA}_t = \alpha x_t + (1-\alpha)\,\text{EMA}_{t-1}

Lower \alpha values produce stronger smoothing, while higher \alpha values react more quickly to new observations.

Excel Usage

=EMA_LFILTER(data, alpha, initial)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • alpha (float, optional, default: 0.2): Smoothing factor between 0 and 1 (unitless).
  • initial (float, optional, default: null): Optional initial EMA level used before processing the first observation (value).

Returns (list[list]): Column vector of exponentially smoothed values.

Example 1: EMA with default smoothing factor

Inputs:

data
10 12 11 13 15

Excel formula:

=EMA_LFILTER({10,12,11,13,15})

Expected output:

Result
2
4
5.4
6.92
8.536
Example 2: EMA with faster response smoothing

Inputs:

data alpha
5 8 7 10 0.6

Excel formula:

=EMA_LFILTER({5,8,7,10}, 0.6)

Expected output:

Result
3
6
6.6
8.64
Example 3: EMA with explicit initial level

Inputs:

data alpha initial
20 19 21 22 0.3 18

Excel formula:

=EMA_LFILTER({20,19,21,22}, 0.3, 18)

Expected output:

Result
18.6
18.72
19.404
20.1828
Example 4: Scalar input EMA

Inputs:

data alpha
9 0.5

Excel formula:

=EMA_LFILTER(9, 0.5)

Expected output:

4.5

Python Code

Show Code 
import numpy as np
from scipy.signal import lfilter as sp_lfilter

def ema_lfilter(data, alpha=0.2, initial=None):
    """
    Compute an exponential moving average using recursive linear filtering.

    See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.signal.lfilter.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        alpha (float, optional): Smoothing factor between 0 and 1 (unitless). Default is 0.2.
        initial (float, optional): Optional initial EMA level used before processing the first observation (value). Default is None.

    Returns:
        list[list]: Column vector of exponentially smoothed values.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        if alpha <= 0 or alpha > 1:
            return "Error: alpha must be greater than 0 and less than or equal to 1"

        series = []
        for row in data:
            for item in row:
                try:
                    series.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not series:
            return "Error: data must contain at least one numeric value"

        b = [float(alpha)]
        a = [1.0, -(1.0 - float(alpha))]
        arr = np.asarray(series, dtype=float)

        if initial is None:
            ema = sp_lfilter(b, a, arr)
        else:
            zi = [(1.0 - float(alpha)) * float(initial)]
            ema, _ = sp_lfilter(b, a, arr, zi=zi)

        return [[float(x)] for x in np.asarray(ema, dtype=float).tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
Smoothing factor between 0 and 1 (unitless).
Optional initial EMA level used before processing the first observation (value).

EMA_PERIOD

This function computes an exponential moving average (EMA) where the smoothing factor is derived from a selected period.

The smoothing factor is computed as:

\alpha = \frac{2}{p+1}

where p is the EMA period. The recursive EMA update is:

\text{EMA}_t = \alpha x_t + (1-\alpha)\,\text{EMA}_{t-1}

The first EMA value is initialized with the first numeric observation.

Excel Usage

=EMA_PERIOD(data, period)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • period (int, optional, default: 10): EMA period used to derive smoothing factor (periods).

Returns (list[list]): Column vector of exponential moving-average values.

Example 1: EMA with default period

Inputs:

data
10 11 13 12 14

Excel formula:

=EMA_PERIOD({10,11,13,12,14})

Expected output:

Result
10
10.1818
10.6942
10.9316
11.4895
Example 2: EMA with short period responds quickly

Inputs:

data period
5 8 7 9 2

Excel formula:

=EMA_PERIOD({5,8,7,9}, 2)

Expected output:

Result
5
7
7
8.33333
Example 3: EMA with longer period smooths changes

Inputs:

data period
20 18 19 21 22 6

Excel formula:

=EMA_PERIOD({20,18,19,21,22}, 6)

Expected output:

Result
20
19.4286
19.3061
19.7901
20.4215
Example 4: Scalar input with unit period

Inputs:

data period
6 1

Excel formula:

=EMA_PERIOD(6, 1)

Expected output:

6

Python Code

Show Code 
import numpy as np

def ema_period(data, period=10):
    """
    Compute an exponential moving average using a period-derived smoothing constant.

    See: https://en.wikipedia.org/wiki/Moving_average#Exponential_moving_average

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        period (int, optional): EMA period used to derive smoothing factor (periods). Default is 10.

    Returns:
        list[list]: Column vector of exponential moving-average values.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        if period < 1:
            return "Error: period must be at least 1"

        series = []
        for row in data:
            for item in row:
                try:
                    series.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not series:
            return "Error: data must contain at least one numeric value"

        alpha = 2.0 / (float(period) + 1.0)
        ema = [series[0]]
        for value in series[1:]:
            ema.append(alpha * value + (1.0 - alpha) * ema[-1])

        return [[float(x)] for x in np.asarray(ema, dtype=float).tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
EMA period used to derive smoothing factor (periods).

SMA_CONV

This function smooths a time series by applying a simple moving-average kernel through discrete convolution.

For a window length w, the uniform kernel assigns equal weight 1/w to each value in the window. The smoothed value at index t is:

\text{SMA}_t = \frac{1}{w}\sum_{i=0}^{w-1} x_{t-i}

Different convolution modes control output length and boundary behavior.

Excel Usage

=SMA_CONV(data, window, mode)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • window (int, optional, default: 3): Number of observations per averaging window (points).
  • mode (str, optional, default: “valid”): Convolution output mode controlling boundary handling and output length.

Returns (list[list]): Column vector of simple moving-average values computed from the input series.

Example 1: Valid-mode SMA on increasing series

Inputs:

data window mode
1 2 3 4 5 3 valid

Excel formula:

=SMA_CONV({1,2,3,4,5}, 3, "valid")

Expected output:

Result
2
3
4
Example 2: Same-mode SMA preserves series length

Inputs:

data window mode
10 12 14 16 2 same

Excel formula:

=SMA_CONV({10,12,14,16}, 2, "same")

Expected output:

Result
5
11
13
15
Example 3: Full-mode SMA includes boundary overlap

Inputs:

data window mode
3 6 9 2 full

Excel formula:

=SMA_CONV({3,6,9}, 2, "full")

Expected output:

Result
1.5
4.5
7.5
4.5
Example 4: Scalar input is normalized to 2D range

Inputs:

data window mode
8 1 valid

Excel formula:

=SMA_CONV(8, 1, "valid")

Expected output:

8

Python Code

Show Code 
import numpy as np

def sma_conv(data, window=3, mode='valid'):
    """
    Compute a simple moving average using discrete convolution with a uniform window.

    See: https://numpy.org/doc/stable/reference/generated/numpy.convolve.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        window (int, optional): Number of observations per averaging window (points). Default is 3.
        mode (str, optional): Convolution output mode controlling boundary handling and output length. Valid options: Full, Same, Valid. Default is 'valid'.

    Returns:
        list[list]: Column vector of simple moving-average values computed from the input series.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        if mode not in ("full", "same", "valid"):
            return "Error: mode must be one of 'full', 'same', or 'valid'"

        if window < 1:
            return "Error: window must be at least 1"

        values = []
        for row in data:
            for item in row:
                try:
                    values.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not values:
            return "Error: data must contain at least one numeric value"

        if mode == "valid" and len(values) < window:
            return "Error: data length must be at least as large as window for valid mode"

        kernel = np.ones(int(window), dtype=float) / float(window)
        result = np.convolve(np.asarray(values, dtype=float), kernel, mode=mode)
        return [[float(x)] for x in np.asarray(result, dtype=float).tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
Number of observations per averaging window (points).
Convolution output mode controlling boundary handling and output length.

SMA_CUMSUM

This function computes a rolling simple moving average by subtracting offset cumulative sums.

If c_t = \sum_{i=1}^{t} x_i, then the average over a window of length w ending at index t is:

\text{SMA}_t = \frac{c_t - c_{t-w}}{w}

This approach avoids repeated summation inside each window and is efficient for long series.

Excel Usage

=SMA_CUMSUM(data, window)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • window (int, optional, default: 3): Number of observations per averaging window (points).

Returns (list[list]): Column vector of rolling simple moving-average values using full-window positions.

Example 1: Rolling SMA from cumulative sums

Inputs:

data window
1 2 3 4 5 3

Excel formula:

=SMA_CUMSUM({1,2,3,4,5}, 3)

Expected output:

Result
2
3
4
Example 2: Two-point rolling average

Inputs:

data window
5 7 9 11 2

Excel formula:

=SMA_CUMSUM({5,7,9,11}, 2)

Expected output:

Result
6
8
10
Example 3: Window length one returns original values

Inputs:

data window
4 6 8 1

Excel formula:

=SMA_CUMSUM({4,6,8}, 1)

Expected output:

Result
4
6
8
Example 4: Scalar input with unit window

Inputs:

data window
12 1

Excel formula:

=SMA_CUMSUM(12, 1)

Expected output:

12

Python Code

Show Code 
import numpy as np

def sma_cumsum(data, window=3):
    """
    Compute a simple moving average using cumulative-sum differencing.

    See: https://numpy.org/doc/stable/reference/generated/numpy.cumsum.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        window (int, optional): Number of observations per averaging window (points). Default is 3.

    Returns:
        list[list]: Column vector of rolling simple moving-average values using full-window positions.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"

        if window < 1:
            return "Error: window must be at least 1"

        values = []
        for row in data:
            for item in row:
                try:
                    values.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not values:
            return "Error: data must contain at least one numeric value"

        if len(values) < window:
            return "Error: data length must be at least as large as window"

        arr = np.asarray(values, dtype=float)
        csum = np.cumsum(np.insert(arr, 0, 0.0))
        means = (csum[int(window):] - csum[:-int(window)]) / float(window)
        return [[float(x)] for x in np.asarray(means, dtype=float).tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
Number of observations per averaging window (points).

WINMA_CONV

This function applies a weighted moving-average filter by convolving the input series with a supplied weight vector.

For window weights w_0,\dots,w_{m-1} and series values x_t, the weighted output is proportional to:

y_t = \sum_{i=0}^{m-1} w_i x_{t-i}

The result is normalized by the sum of weights so that constant input levels are preserved when weights sum to a nonzero value.

Excel Usage

=WINMA_CONV(data, weights, mode, conv_method)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • weights (list[list], required): Weight coefficients as a 2D range (relative weights).
  • mode (str, optional, default: “same”): Convolution output mode controlling boundary handling and output length.
  • conv_method (str, optional, default: “auto”): Convolution algorithm selection mode.

Returns (list[list]): Column vector of weighted moving-average values.

Example 1: Weighted moving average in same mode

Inputs:

data weights mode conv_method
1 2 3 4 5 1 2 1 same auto

Excel formula:

=WINMA_CONV({1,2,3,4,5}, {1,2,1}, "same", "auto")

Expected output:

Result
1
2
3
4
3.5
Example 2: Weighted moving average in valid mode

Inputs:

data weights mode conv_method
2 4 6 8 10 1 1 valid direct

Excel formula:

=WINMA_CONV({2,4,6,8,10}, {1,1}, "valid", "direct")

Expected output:

Result
3
5
7
9
Example 3: Weighted moving average in full mode

Inputs:

data weights mode conv_method
3 6 9 0.2 0.8 full fft

Excel formula:

=WINMA_CONV({3,6,9}, {0.2,0.8}, "full", "fft")

Expected output:

Result
0.6
3.6
6.6
7.2
Example 4: Scalar input with single weight

Inputs:

data weights mode conv_method
7 1 same auto

Excel formula:

=WINMA_CONV(7, 1, "same", "auto")

Expected output:

7

Python Code

Show Code 
import numpy as np
from scipy.signal import convolve as sp_convolve

def winma_conv(data, weights, mode='same', conv_method='auto'):
    """
    Compute a weighted moving average by convolving data with a user-defined weight window.

    See: https://docs.scipy.org/doc/scipy/reference/generated/scipy.signal.convolve.html

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        weights (list[list]): Weight coefficients as a 2D range (relative weights).
        mode (str, optional): Convolution output mode controlling boundary handling and output length. Valid options: Full, Same, Valid. Default is 'same'.
        conv_method (str, optional): Convolution algorithm selection mode. Valid options: Auto, Direct, FFT. Default is 'auto'.

    Returns:
        list[list]: Column vector of weighted moving-average values.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)
        weights = to2d(weights)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"
        if not isinstance(weights, list) or not all(isinstance(row, list) for row in weights):
            return "Error: Invalid input - weights must be a 2D list"

        if mode not in ("full", "same", "valid"):
            return "Error: mode must be one of 'full', 'same', or 'valid'"
        if conv_method not in ("auto", "direct", "fft"):
            return "Error: conv_method must be one of 'auto', 'direct', or 'fft'"

        series = []
        for row in data:
            for item in row:
                try:
                    series.append(float(item))
                except (TypeError, ValueError):
                    continue

        win = []
        for row in weights:
            for item in row:
                try:
                    win.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not series:
            return "Error: data must contain at least one numeric value"
        if not win:
            return "Error: weights must contain at least one numeric value"

        weight_sum = float(np.sum(win))
        if weight_sum == 0:
            return "Error: weights must sum to a nonzero value"

        conv = sp_convolve(np.asarray(series, dtype=float), np.asarray(win, dtype=float), mode=mode, method=conv_method)
        normalized = np.asarray(conv, dtype=float) / weight_sum
        return [[float(x)] for x in normalized.tolist()]
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
Weight coefficients as a 2D range (relative weights).
Convolution output mode controlling boundary handling and output length.
Convolution algorithm selection mode.

WMA

This function computes a rolling weighted moving average (WMA) where each window position uses a specified weight.

For window length m and weights w_0,\dots,w_{m-1}, each output value is:

\text{WMA}_t = \frac{\sum_{i=0}^{m-1} w_i x_{t-m+1+i}}{\sum_{i=0}^{m-1} w_i}

The function returns values only for complete windows, beginning at the first index where all weights can be applied.

Excel Usage

=WMA(data, weights)
  • data (list[list], required): Time-series observations as a 2D range (data points).
  • weights (list[list], required): Weight coefficients as a 2D range (relative weights).

Returns (list[list]): Column vector of rolling weighted moving-average values for complete windows.

Example 1: WMA with linearly increasing weights

Inputs:

data weights
1 2 3 4 5 1 2 3

Excel formula:

=WMA({1,2,3,4,5}, {1,2,3})

Expected output:

Result
2.33333
3.33333
4.33333
Example 2: WMA with equal weights matches rolling mean

Inputs:

data weights
2 4 6 8 1 1

Excel formula:

=WMA({2,4,6,8}, {1,1})

Expected output:

Result
3
5
7
Example 3: Single weight returns original series

Inputs:

data weights
7 9 11 5

Excel formula:

=WMA({7,9,11}, {5})

Expected output:

Result
7
9
11
Example 4: Scalar input with scalar weight

Inputs:

data weights
10 1

Excel formula:

=WMA(10, 1)

Expected output:

10

Python Code

Show Code 
import numpy as np

def wma(data, weights):
    """
    Compute a rolling weighted moving average using user-supplied weights.

    See: https://en.wikipedia.org/wiki/Moving_average#Weighted_moving_average

    This example function is provided as-is without any representation of accuracy.

    Args:
        data (list[list]): Time-series observations as a 2D range (data points).
        weights (list[list]): Weight coefficients as a 2D range (relative weights).

    Returns:
        list[list]: Column vector of rolling weighted moving-average values for complete windows.
    """
    try:
        def to2d(x):
            return [[x]] if not isinstance(x, list) else x

        data = to2d(data)
        weights = to2d(weights)

        if not isinstance(data, list) or not all(isinstance(row, list) for row in data):
            return "Error: Invalid input - data must be a 2D list"
        if not isinstance(weights, list) or not all(isinstance(row, list) for row in weights):
            return "Error: Invalid input - weights must be a 2D list"

        series = []
        for row in data:
            for item in row:
                try:
                    series.append(float(item))
                except (TypeError, ValueError):
                    continue

        win = []
        for row in weights:
            for item in row:
                try:
                    win.append(float(item))
                except (TypeError, ValueError):
                    continue

        if not series:
            return "Error: data must contain at least one numeric value"
        if not win:
            return "Error: weights must contain at least one numeric value"

        weight_sum = float(np.sum(win))
        if weight_sum == 0:
            return "Error: weights must sum to a nonzero value"

        window = len(win)
        if len(series) < window:
            return "Error: data length must be at least as large as the number of weights"

        out = []
        for end_idx in range(window - 1, len(series)):
            segment = series[end_idx - window + 1:end_idx + 1]
            val = float(np.dot(segment, win) / weight_sum)
            out.append([val])

        return out
    except Exception as e:
        return f"Error: {str(e)}"

Online Calculator

Time-series observations as a 2D range (data points).
Weight coefficients as a 2D range (relative weights).