This tip for Excel explains what moving averages are and how they can be used in Excel to help identify trends in your data and forecast future values.
A moving average simply takes the last n data values and creates an average. This average is then calculated across the entire dataset. A moving average of 7 days for example takes the average of the last 7 data values. A moving average therefore can only start once there are enough data observations to calculate the average, as the image below demonstrates, with the MA(7) series starting in the 7th row of data (row 8). The moving average of order 7 can’t start in rows 1 to 7 as there aren’t 7 prior observations to calculate the average. The average is calculated using one of the more simple Excel functions, =average(), as the image to the left demonstrates.
Why use a moving average?
A lot of people in the financial sector use moving averages to smooth out a data series to help highlight a trend, particularly as their data is quiet volatile and fluctuates around a trend. An average of the last 7 data points can also be used as a forecast for the next observation.
Which moving average should you use?
The most straight forward moving average is an unweighted mean, known as the simple moving average (SMA). This takes the average of the last n observations. Simple as that. You’ll find that SMAs can be heavily influenced by anomalies moving in or out of the subset being averaged. One alternative is to give less weight to older observations. This is done using a variety of methods, one of which is the exponential moving average (EMA). In an EMA the weighting for each previous data point decreases exponentially without ever reaching zero. The weighting applied to the most recent data point depends on the number of periods in the moving average.
There is no argument to use a EMA over an SMA. They simply act differently. EMAs have less lag so are more sensitive to recent changes in data. So this means you’ll see an EMA reacting to a price change before an SMA does, in general. An SMA is a true average over a period of time however.
Creating a moving average in Excel
So SMAs in Excel are easy. You just need to take the average from the last n observations. If your data is daily, then a weekly MA takes the last 7 data points to calculate the average. An EMA is slightly harder but just needs a smoothing constant included. It begins with the SMA value for it’s first observation then takes on the following formula: EMA= [price - price(previous day)] x multiplier + EMA(previous day). The multipler may be calculated like so, in a 20 day period: (2 / (# of time periods + 1) ) = (2 / (20 + 1) ) = 0.0952 (9.52%)