Calculating a moving average in Excel VBA

Moving Average Calculation Overview:

A moving average is commonly used in time series analysis to smooth out short-term fluctuations and highlight longer-term trends or cycles. The moving average is typically calculated by taking the average of a subset of data within a specified window size, which then « moves » along the series.

This code will allow you to calculate a moving average for a given range of data in Excel, where the average is calculated over a specified number of data points (e.g., a 5-point window). The results will be written to another column.

VBA Code to Calculate Moving Average:

Sub CalculateMovingAverage()
    ' Declare variables
    Dim ws As Worksheet
    Dim rangeData As Range
    Dim rangeResult As Range
    Dim windowSize As Integer
    Dim i As Integer, j As Integer
    Dim sum As Double
    Dim currentCell As Range
    ' Initialize variables
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change the sheet name if needed
    Set rangeData = ws.Range("A2:A100") ' Range containing the data (adjust as needed)
    Set rangeResult = ws.Range("B2:B100") ' Range where the results will be displayed
    windowSize = 5 ' Size of the moving window (this can be adjusted)
    ' Loop through each cell in the data range
    For i = windowSize To rangeData.Rows.Count
        sum = 0       
        ' Calculate the sum of values in the window of size windowSize
        For j = i - windowSize + 1 To i
            sum = sum + rangeData.Cells(j, 1).Value
        Next j    
        ' Calculate the moving average and display it in the result range
        rangeResult.Cells(i, 1).Value = sum / windowSize
    Next i
    MsgBox "Moving average calculation completed.", vbInformation
End Sub

Code Explanation:

1. Declare Variables:
   • ws is a variable that represents the worksheet where the data is stored.
   • rangeData is the range of cells that contains the data you want to calculate the moving average for (in this case, A2:A100).
   • rangeResult is the range where the moving averages will be written (in this case, B2:B100).
   • windowSize is the size of the window used to calculate the moving average (in this example, it’s set to 5).
2. Initialize Variables:
   • The code references Sheet1 for the data sheet, but you can change this to the appropriate sheet name in your workbook.
   • The range A2:A100 is used for the data, and B2:B100 is where the results are displayed. Adjust these ranges to match your actual data.
   • The windowSize is set to 5, meaning the average will be calculated using the last 5 data points.
3. Calculate the Moving Average:
   • The outer loop (starting from i = windowSize) iterates through each data point in the range starting from the 5th value (since we need at least 5 data points to calculate the first average).
   • The inner loop calculates the sum of the windowSize values leading up to the current data point.
   • The moving average is calculated by dividing the sum by the windowSize.
   • The result is written into the corresponding cell in the rangeResult column.
4. Display a Message:
   • Once the moving averages have been calculated, a message box will pop up saying « Moving average calculation completed. »

How to Use the Code:

1. Open the VBA Editor: Press Alt + F11 in Excel to open the Visual Basic for Applications editor.
2. Insert a New Module: Click Insert > Module to add a new module.
3. Copy and Paste the Code: Copy the code above and paste it into the new module.
4. Run the Macro: Press F5 or go to Run > Run Sub/UserForm to execute the macro.

Customization:

• Window Size: You can adjust the windowSize variable to change how many data points the moving average is calculated over (e.g., changing it from 5 to 10 for a larger window).
• Data Range: Modify the rangeData and rangeResult ranges to fit the location of your data and where you want to place the results.
• Result Placement: You can change the result range to any other column (e.g., C2:C100) if you prefer to place the moving averages elsewhere.

Example:

• If you have the following data in column A (from A2 to A100):

1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...

• With a window size of 5, the first moving average (in cell B6) would be the average of A2:A6, the second moving average (in cell B7) would be the average of A3:A7, and so on.

This VBA code makes calculating moving averages in Excel automated and much easier, especially for larger datasets or when doing repeated calculations.