* Update – Trade and Investing models that were earlier written using Yahoo! Finance Stock API have been rewritten to pull quotes from Bloomberg Markets API. At this time only US stocks and ETF quotes can be retrieved from this model spreadsheet. See setup instructions.
A primer on Exponential Moving Average
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:
P = current price
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:
Once the stock prices are imported, we can make use of R1C1 and Rc 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:
- Stock Symbol
- Date Start
- Date End
- EMA Days or Time Window
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.
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.
The spreadsheet model retrieves quote information from Bloomberg Markets API through a lightweight data-interchange format c
Under Developer->Visual Basic->Tools->References, add the highlighted objects.