Exponential Moving Average in Excel

A primer on Exponential Moving Average

Moving average method is a commonly used technical analysis indicator. It is often called as Simple Moving Average or SMA. All moving averages use a historical data series and the current price in the calculation. An Exponential Moving Average or EMA assigns a weighing factor to each value in the data series based on its age. The most recent data gets the greatest weight and each asset price receives a smaller weight as the series is traversed chronologically. EMA for an asset price can be built manually through spreadsheet formulas or programmed in VBA. This post guides you how to calculate exponential moving average in excel.

The weighing factor in an EMA is based on a smoothing factor generated from the length of the input. The common weighting method for EMA, is to add the difference between the previous average and the current price of an asset, multiplied by the smoothing factor, into the previous average. The exponential moving average places greater importance on more recent data.

EMA is expressed by the following equation:where,

P = current price

α = Smoothing factor  =

N = Number of Time periods

So, current EMA is the sum of yesterday’s EMA X (1 – weight) and today’s price X (weight)

The EMA works by weighting the difference between the current period’s price and previous EMA, and adding the result to the previous EMA. Shorter the period, more weight applied to the most recent price. Line chart plots the closing price and trailing EMA of NYSE:GM between Jun-29-19 and Sep-27-19. Buy or sell signals are often generated using a cross over of two moving averages – short and long time scale.

Calculate EMA using simple worksheet techniques:

Exponential Moving Average in Excel

1. Let us get our feet wet with 13-day EMA for GM stock. You can get closing prices of a tradable asset from your broker or from your online trading account. Fill in columns D and E with trading date and close prices.

2. The simple average is calculated for the first 13 closing prices of the stock through AVERAGE() function. Cells E2 through E14 contain the first 13 closing prices.

3. EMA formula from cell H15 onward becomes –

E15*(2/(13+ 1)) + H14 * (1-(2/(13+1)))

4. Drag the formula starting at H15 to the end of the sheet.

There you go. You’ve just finished calculating EMA through simple spreadsheet hacks.

Build EMA with VBA:

EMA calculation and plotting of chart can be accomplished with some VBA. The best part is that it’s fully automatic!

Let us begin by importing historical stock prices from Bloomberg Open Markets in a JSON format. The data columns are Date and Close. Once the stock prices are imported,  use R1C1 and R[1]c[1] style of programming in VBA. Click here for a quick reference on R1C1 and FormulaR1C1 properties in Excel. The VBA program takes these inputs-

1. Stock Symbol
2. Date Start
3. Date End
4. EMA Days or Time Window

The default date ranges are 90 calendar days. You are free to modify the inputs. The time window for plotting Exponential Moving Average is 13 days and there are 62 trading days between these dates. Columns D and E are simply data points from the web service sorted by trading date. Column H has to do all the fun with EMA.

Cell H14 calculates the arithmetic mean of first 12 historical prices of NYSE:GM from E2 through E13. Cells H15 through H63 contains formula for calculating EMA of the remaining trading days. Once the sheet is filled with EMA, VBA function does the remaining job of plotting a chart on closing price vs EMA.

 

Usage Instructions

Trading models that were written in Yahoo! Finance Stock API have been rewritten to pull quotes from Bloomberg Open Markets. At this time only US stocks and ETF quotes can be retrieved from this model spreadsheet. See setup instructions.

The spreadsheet model retrieves quote information from Bloomberg Markets API through a lightweight data-interchange format called JSON or JavaScript Object Notification. If this is your first time programming JSON in Excel, you must add certain libraries before using Bloomberg Markets Web Service.

Under Developer->Visual Basic->Tools->References, add the highlighted objects.