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

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.