Data Analysis in Excel - Moving Averages

Data analysis is all about identifying patterns and trends. But raw data often jumps around, making it hard to see the big picture. 

The moving average (MA), is a technique that smooths out fluctuations and reveals underlying trends. 

A moving average takes a set number of recent data points (e.g., past 7 days) and calculates their average. 

As you move through the data, you keep recalculating the average, giving you a constantly updated trend line. This line helps you see if values are generally increasing, decreasing, or holding steady. 

The key benefits of a MA are therefore:

• Reduced Noise: MAs filter out random fluctuations, making it easier to spot genuine trends.

• Trend Confirmation: A rising MA suggests an uptrend, while a falling MA indicates a downtrend.

• Support & Resistance: Moving averages (MAs) act as dynamic support and resistance levels, highlighting potential buying and selling zones, particularly in finance. By combining MAs of different lengths (e.g., 7-day and 14-day), you can gain insights into possible future trends. A short-term MA crossing above a long-term MA often signals an upward price movement, while the opposite (short-term MA falling below the long-term MA) suggests a downward trend. 

Although very useful MA have some drawbacks

• Lag: MAs react to past data, so they may not capture sudden shifts.

• Window Size: Choosing the right window size is crucial. A short window reacts quickly but is noisy, while a long window is smoother but reacts slower.

This post will explain how to write VBA code to calculate a MA over a set of data for a period selected by the user  (7 days, 14 days,....) before running the code

The initial dataset consists of two columns, with each row featuring a date and a corresponding numerical value. Upon completion, the output will unveil a new column where each row contains the calculated moving average (MA). This MA is computed based on a user-specified number of days, extending backward from the date aligned with the respective row. 

The below picture indicates the outcome that will be achieved when the user indicates to calculated the MA on a 7 days period

The code assumes that there is a sheet called Closing with dates in column A and the closing of the Dow Jones Industrial Index in column B 

Before running the code the user will have to introduce in cell H1 the number of days for which he wants to calculate the MA

In this example it is assumed that the user will choose a 7 days period

The first step is to define the variables that will be used throughout the code 

Sub movavg()

'i' is the variable that will be used for the loop; 'k' is the variable equal to the value inserted in H1 

'lstrw' is the last row of the used range; 'xavg' is the variable that will contain the moving average calculated over the number of days reported in 'k'

Dim lstrw As Integer, xavg As Long

Dim i As Integer, k As Integer

' The value in cell H1 is allocated to the variable 'k'

Column C is cleared of any previous content

The last used row is identified and stored in the variable 'lstrw' 

k = Range("h1").Value

Columns("C:C").ClearContents

lstrw = Range("a1").End(xlDown).Row

The code starts to loop through the rows. 

The variable 'i' is set to start at K+1. K is number of days for which we want to calculate the MA and adding 1 is required as the first row contains column headers.

For i = k + 1 To lstrw

The code calculates the average of the current cell's value and the values in the preceding K cells. This average is then stored in the variable 'xavg'

 xavg = WorksheetFunction.Average(Range(Cells(i, 2), Cells(i + 1 - k, 2)))

The cells in column 3 (i.e. Column C)  of the current row is populated with the value stored in variable 'xavg'

The variable is then set to zero and the loop starts again

Cells(i, 3).Value = xavg

   xavg = 0

Next i

The first line of column C is named after the number of days contained in cell H1

Range("c1").Value = Range("H1") & " Days Moving Average"

Range("c1").Select

End Sub