Exponential Moving Average in Excel

A primer on EMA:

The moving average method is a commonly used technical analysis indicator. All moving averages typically use a historical data series and the current price in the calculation. An Exponential Moving Average or EMA assigns a weighting factor to each value in the data series based on its age. The most recent data gets the greatest weight and each price gets a smaller weight as the series is traversed chronologically. Exponential moving average in excel can be built manually through spreadsheet formulas or programmed via Yahoo Finance Web Services and VBA.

The weighting factors 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, 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:

exponential-moving-average

where,

P = current price

α = Smoothing factor  =eq2

N = Number of Time periods

So, current EMA is the sum of yesterday’s EMA (times 1 – weight) and today’s price (times by a 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. The shorter the period, the more weight applied to the most recent price.

In this example, the chart gives the EMA of Yahoo between Jan-01-2012 and Dec-31-2012. 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. To begin with, let’s calculate the 15-day EMA of Yahoo’s stock. The first step is to import historic stock prices from a web service.

2. The simple average is calculated of the first 15 prices of the stock using AVERAGE() function. The cells B3 through B17 contains the first 15 closing prices.

3. Enter the EMA formula as shown in the screen shot.

4. Repeat the above step by copying the formula for the entire set of stock prices.

Congratulations. You’ve calculated the EMA using simple spreadsheet techniques.

Build Exponential Moving Average in Excel through VBA:

EMA calculation and plotting of chart can be achieved with the help of VBA.

Let us begin by importing historical stock prices from Yahoo web service in CSV format. The most relevant data columns are Date and Close. Your import should look very similar to this screen grab:

Exponential Moving Average in Excel

 Once the stock prices are imported, we can make use of 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 accepts the following parameters:

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

Exponential Moving Average in Excel

In this example, I have defaulted start date to be the first day of the year and end date to be as current date. You are free to modify the parameters. The time window for plotting Exponential Moving Average in this example is 13 days  and there are 184 trading days. Columns A through G are simply the data points from the web service sorted by trading date. Column H contains the interesting piece where we calculate the EMA.

Exponential Moving Average in Excel

The highlighted cell H14 calculates the arithmetic mean of first 12 historical prices of Yahoo, Cells E2 through E13. The cells H15 through H185 contains the formula for calculating EMA of the remaining trading days.Once the data sheet is constructed with EMA, VBA function does the job of plotting a chart on the closing price vs EMA.

1. Following code does the initialization

Exponential Moving Average in Excel

2. The following snippets of code imports the historical prices from Yahoo web service.

Exponential Moving Average in Excel

3. Finally, the following function plots the chart

Exponential Moving Average in Excel

Download Exponential Moving Average in Excel

3 Comments

 Add your comment
  1. Jish,
    The spreadsheet works great, but the VBA source is locked…
    -Martin

  2. Jish,
    Is it possible to get access to the VBA source code?
    Klaus

  3. Hi, Can it add multiple tabs or sheets for to cover multiple stocks?

Leave a Comment

Your email address will not be published.