Using a Spreadsheet to Construct Moving Averages

In the article “Buy-and-Hold Versus Market Timing,” which begins on page 16 in this issue, we discuss the research of Theodore Wong, who tested a moving average crossover MAC system to see if it was possible to generate better returns than a buy-and-hold strategy over an extended period of time. He used the interplay between the market index and a moving average of the index to time when to be invested in the market and when to hold cash.

Market timers frequently make their investment decisions based on internal relative strength—whether a stock is stronger or weaker than its own average. Wong’s research used moving averages to determine if the market was in an uptrend or downtrend and to test whether it made sense to be long during measurable uptrends and move to cash during downtrends. While the argument continues over the efficacy of market timing, investors are still faced with the dilemma of whether to adjust their portfolios based on market conditions and which guidelines they should follow in this endeavor.

Moving Average Basics

One of the techniques many analysts use in judging internal relative strength involves the creation of moving averages of prices. A moving average is one of the simplest trend-following tools investors use. While moving averages come in different flavors, their underlying purpose remains the same: to help investors and traders track the trend in the prices of financial assets by smoothing out the periodic fluctuations in price (also called “noise”). In smoothing out price variations, moving averages emphasize price trends longer than the interval.

It is important to point out that moving averages do not predict price directions—rather they indicate the current price direction (with a lag). This lag stems from using past price data—prices lead and moving averages follow. Over time, as the name implies, a moving average will move as old data is dropped off and new data is added. There are three types of moving averages: simple, weighted and exponential.

Simple Moving Average

A simple moving average, or SMA, applies equal weights to all prices across the time interval used to calculate the average. As a result, a simple moving average assumes that prices from the beginning of the period are just as relevant as prices from the end of the period.

The SMA is constructed the same as a typical average—if you have three values, you would add them together and divide the sum by three.

Here is the calculation for a simple moving average:

(P_¹ + P_² + P_³ + … + P_n) ÷ n

Where:

P₁ = the price of the first period used to calculate the moving average
P_n = is the price of the last period used to calculate the moving average
n = the number of periods used in calculating the moving average

First:    Last:    Email:

Table 1 compares the results for 10-day simple, weighted, and exponential moving averages using daily closing values of the S&P 500 total return index from May 2010. The data is from the Yahoo! Finance website.

 

The May 14, 2010, SMA value of 1156.11 is derived by adding up the index values for the 10 days ending May 14 and then diving the total by 10.

Weighted Moving Average

A simple moving average assumes that all prices are equally important. However, some traders believe that recent prices are more important in identifying the current trend. A weighted moving average WMA explicitly assigns weights that determine the relative importance of the prices used. While higher weights are usually assigned to the most recent prices, you can use any scheme you wish.

The common weighted moving average calculation is a weighted average on n periods, where the weighting decreases by one with each previous price, such that:

((n × P_n) + ((n – 1) × P_n-1) + ((n – 2) × P_n-2) + … ((n – (n – 1)) × P_{n – (n – 1)}) ÷ (n + (n – 1) + (n – 2) + … + (n – (n – 1)))

Where:

n = the number of periods used in calculating the moving average
P_n = the price of the most recent period used to calculate the moving average

Referring again to Table 1, we see that the 10-day WMA for May 14, 2010, is 1151.09. The most recent price used for this calculation—1135.68 for May 14—is multiplied by the largest weighting factor, 10. By doing this, the most recent price has the biggest impact on the overall average. Moving back one period, the closing price for May 13 is multiplied by a weighting of nine, and so on until the oldest price, from May 3, is multiplied by a weighting of one. The sum of the closing prices multiplied by their respective periodic weightings is then divided by the sum of the weightings. For a 10-period WMA, the denominator will be 55 (10+9+8+7+6+5+4+3+2+1).

Exponential Moving Average

The last moving average we will discuss here is the exponential moving average. The exponential moving average is a bit more sophisticated in its calculation, but it requires less historical data than the other two moving averages.

Like the weighted moving average, the exponential moving average EMA reduces the lag by giving more emphasis to recent prices. Also like the weighted moving average, the weighting applied to the most recent price depends on the number of periods in the moving average. These weighting factors decrease exponentially, giving much more importance to recent prices, while still not discarding older observations entirely.

There are three steps to calculating an exponential moving average:

1. Calculate the weighting multiplier;
2. Derive the initial “EMA,” which can be a simple moving average of previous values or the price value of the previous period;
3. Calculate the exponential moving average.

Here are the equations:

Multiplier = (2 ÷ (n + 1))

EMA = [Close – EMA (previous day)] × multiplier + EMA (previous day)

A 10-period exponential moving average applies an 18.18% weighting to the most recent price (2 ÷ (10 + 1)). In contrast, a 20-period EMA would have a 9.5% weighting (2 ÷ (20 + 1)). Therefore, the weighting for shorter time periods is higher than the weighting for longer time periods. As we can see from this example, the weighting drops by roughly half every time the moving average period doubles. This reduces the lag between the actual price curve and the smoothed moving average curve.

Looking at Table 1, we see that the EMA calculation begins with a nine-day SMA from May 13 (1158.38). This value is deducted from the closing price on May 14 of 1135.68 and then multiplied by the weighting factor of 0.1818 (2 ÷ (10 +1)). Then, the May 13 SMA value is added back to arrive at the current EMA. It is worth noting that because this EMA calculation begins with a simple moving average, its “true” value will not be realized until 20 or so periods later. This is one reason why other EMA calculations merely start with the prior period’s closing price and forgo using the SMA as a starting point.

Using a Spreadsheet to Calculate Moving Averages

While moving averages are helpful in determining the underlying trend in an individual security or the overall market, they are reliant on a large amount of data in order to be meaningful. Furthermore, this data requires constant updating in order to stay “fresh.” While many financial websites plot moving averages on price charts, a spreadsheet is another means of calculating and displaying this data. The spreadsheet presented here uses template formulas as given in Microsoft Excel 1997–2003, a common format used by many PC users. However, it is also forward-compatible with newer versions, such as Excel 2008 and 2010.

Beyond crunching raw data with spreadsheets, you can also create graphs directly from the data you are analyzing.

The data in this spreadsheet was downloaded, for free, from the Yahoo! Finance website. There you can download daily, weekly, monthly or annual open, high, low, close, and volume data going back as far as 1950 (when available). You specify the periodicity of the data and the timeframe you are interested in and then you simply download the data into Excel.

The first data item to consider is the number of periods we wish to use for computing a moving average. Theodore Wong’s research indicated that a six-month moving average crossover system based on a market index yielded the “best” results.

Keep in mind that we are not suggesting that a six-month moving average is optimal for timing buy and sell decisions. You can experiment with other period lengths. However, be aware that the shorter the period length, the more responsive the moving average will be to changes in price. Statistical studies of using filter rules to time transactions suggest that the costs of making excessive transactions will eat up the profits that might be generated using these techniques. Remember, too, that what we are trying to do here is use historical prices to determine if the market or security is trending up or down.

Figure 1 shows a portion of the spreadsheet we created, with columns for the three moving averages discussed here—simple moving average SMA, weighted moving average WMA, and exponential moving average EMA. For these examples, we created six-month moving averages using the average monthly opening, high, low and closing prices of the S&P 500 total return index going back to the start of 1950 (if you wish to experiment with different period lengths, you will need to modify the underlying formulas).

By creating an average of an average, we are further eliminating the variability in the data. The various formulas used are as follows:

G7: = AVERAGE(C7:F7)
I13: = AVERAGE($G7:$G12)
K13: = ($G12*6+$G11*5+$G10*4+
$G9*3+$G8*2+$G7*1)/21
M12: = AVERAGE(G7:G11)
M13: = $M12+(2/(6+1))*($G12-$M12)

Cell G7 calculates the simple average of the open, high, low, and close prices in the S&P 500 total return index for January 1950 to arrive at 16.87.

Since we are calculating six-month averages, we must have at least six months of data (five months for the EMA, which we will discuss momentarily). Furthermore, we instituted a one-month lag between the index and the moving average. This is to mimic the “real world” experience of not having monthly price data until after trading for the month is over. So the SMA calculation in cell I13, which is July 1950, calculates the simple moving average of the average monthly values for the S&P 500 total return index for the six-month period of January through June.

Cell K13 contains the formula for the six-month weighted average price for that month. It takes the average price for the previous month (from cell G12) and weights it by a factor of six, adds to that the closing price from two months ago (in cell G11) weighted by a factor of five, and so on back to six months prior. The total of the weighted prices is then divided by 21, the sum of the weights we used (6+5+4+3+2+1).

An exponential moving average, in effect, assigns a weight to the prior period’s moving average value and then adds to it a portion of the current period’s price. Other EMA calculations simply start with the prior period’s price and go from there. Once again, the EMA value displayed in M13 (July 1950) is for the six months ending June 1950. Cell M12, which is the starting value for subsequent EMA values, is the simple moving average of the monthly averages prices for the five months ending May 1950.

Now that we have a “starting point” for the EMA in cell M12, cell M13 calculates the exponential moving average by first taking the SMA value from M12 (17.51). It then adds to it the difference between the average S&P 500 price for the period and the SMA (18.33 – 17.51) multiplied by the six-period weighting factor of 28.57% ((2 ÷ (6 + 1))):

EMA = 17.51 + (0.2857 × 0.82)
= 17.74

Figure 2 shows a chart of the actual month-end values of the S&P 500 total return index, the monthly average value of the index, and the six-month EMA of the average index values from January 2000 through the end of May 2010. We plotted the two index lines to show the difference between the month-end and average month values. As we can see, the two lines track each other rather closely.

In the article on page 16, Theodore Wong used crossovers between the six-month EMA and the average monthly market index to decide whether or not to be invested. Instead of trying to “eyeball” crossovers on the chart, we created formulas in the spreadsheet to generate buy and sell signals for the three six-month moving averages:

J13: = IF($G12>$I13,”BUY”,”SELL”)
L13: = IF($G12>$K13,”BUY”,”SELL”)
N13: = IF($G12>$M13,”BUY”,”SELL”)

As shown in Figure 1, for each moving average a BUY signal is generated when the average monthly index value is higher than the respective six-month moving average. When the average index value is lower than the six-month moving average, a SELL signal is generated.