Calculating a moving median in Excel using VBA

Calculating a moving median (also called a « sliding median ») in Excel using VBA is a common task for time series analysis or numerical data smoothing. The moving median is used to smooth the data by calculating the median of a sliding window of values within a dataset.

Objective

The goal here is to write a VBA code to calculate the moving median over a defined window size (e.g., 3 periods, 5 periods, etc.) within a range of data in Excel.

Requirements

1. Data: A column of numerical data.
2. Window size for the moving median: A defined number of periods (e.g., 3 or 5).
3. Output: A separate column where the moving median results will be displayed.

Example Data

Assume your data is in column A, from cell A2 to A100. You want to calculate the moving median with a 3-period window and display the results starting from cell B3.

Detailed VBA Code

Here’s the VBA code that performs this calculation:

Sub CalculateMovingMedian()
    Dim dataRange As Range
    Dim resultRange As Range
    Dim windowSize As Integer
    Dim i As Long
    Dim j As Long
    Dim window() As Double
    Dim median As Double   
    ' Define the data range (column A from A2 to A100)
    Set dataRange = Range("A2:A100")   
    ' Define the window size (e.g., 3 periods)
    windowSize = 3   
    ' Define the result range (column B starting from B3)
    Set resultRange = Range("B3:B100")   
    ' Check if the result range is large enough
    If resultRange.Rows.Count < dataRange.Rows.Count - windowSize + 1 Then
        MsgBox "The result range is too small!"
        Exit Sub
    End If   
    ' Calculate the moving median
    For i = windowSize To dataRange.Rows.Count
        ' Create an array to store the window values
        ReDim window(windowSize - 1)       
        ' Fill the array with the window data
        For j = 0 To windowSize - 1
            window(j) = dataRange.Cells(i - j, 1).Value
        Next j        
        ' Sort the array to find the median
        Call SortArray(window)       
        ' Calculate the median (middle value of the sorted array)
        median = window(Int(windowSize / 2))       
        ' Display the median in the result column
        resultRange.Cells(i - windowSize + 1, 1).Value = median
    Next i
End Sub

Sub SortArray(ByRef arr() As Double)
    Dim i As Long, j As Long
    Dim temp As Double   
    ' Bubble sort to sort the array in ascending order
    For i = LBound(arr) To UBound(arr) - 1
        For j = i + 1 To UBound(arr)
            If arr(i) > arr(j) Then
                ' Swap the values
                temp = arr(i)
                arr(i) = arr(j)
                arr(j) = temp
            End If
        Next j
    Next i
End Sub

Explanation of the Code

1. Defining Data and Result Ranges:
   • dataRange specifies the range of cells that contain the raw data. Here, it refers to column A from A2 to A100.
   • resultRange is where the calculated moving medians will be stored. It starts at cell B3 to avoid overwriting the initial rows that don’t have enough data for the median.
2. Moving Median Window (n periods):
   • The variable windowSize defines the size of the moving window. In this example, a window of 3 periods is used, but this can be adjusted based on your needs.
3. Calculating the Moving Median:
   • For each position i in the data range, a window of n values is extracted (the last n values).
   • These values are sorted, and the median is calculated as the middle value of the sorted array.
   • The result of the median is stored in the corresponding cell in the result range.
4. Sorting the Window Values:
   • The code uses an auxiliary procedure SortArray that sorts the window of values in ascending order using the Bubble Sort algorithm.
   • After sorting, the median is simply the middle value in the sorted array (for an odd-sized window).

How to Run the Code:

1. Open Excel.
2. Press Alt + F11 to open the VBA editor.
3. Go to Insert > Module and paste the code.
4. Press F5 to run the macro and calculate the moving median.

Example Use Case:

• Data in column A (e.g., A2:A100).
• Moving Median Window Size: 3 periods.
• Results in column B (starting from B3).

Additional Improvements:

• The sorting function uses Bubble Sort, which can be slow for large datasets. For performance, you might want to use faster sorting algorithms like QuickSort or MergeSort if you’re dealing with large amounts of data.
• You can add an input box or a dialog to let the user choose the window size dynamically instead of hardcoding it in the code.