How to Calculate EMA in Excel

Learn how to calculate the exponential moving average in Excel and VBA, and get a free web-connected spreadsheet.

The spreadsheet retrieves stock data from Yahoo Finance, calculates EMA (over your chosen time window) and plots the results.

The download link is at the bottom. The VBA can be viewed and edited; it’s completely free.

But first disover why EMA is important to technical traders and market analysts.

Historical stock price charts are often polluted with a lot of high-frequency noise. This often obscures major trends. Moving averages help smooth out these minor fluctuations, giving you greater insight into the overall market direction.

The exponential moving average places greater importance on more recent data. The larger  the time period, the lower the importance of the most recent data.

EMA is defined by this equation.

where P is the price and T is the time period. Essentially, the EMA today is the sum of

• today’s price (multiplied by a weight)
• and yesterday’s EMA (multiplied by 1-weight)

You need to kickstart the EMA calculation with an initial EMA (EMA₀). This is usually a simple moving average of length T.

The chart above, for example, gives the EMA of Microsoft between 1st January 2013 and 14th January 2014.

Technical traders often use the cross-over of two moving averages – one with a short timescale and another with a long timescale – to generate buy/sell signals. Often 12- and 26-day moving averages are used.

When the shorter moving average rises above the longer moving average, the market is trending updwards; this is a buy signal. However, when the shorter moving averages falls below the long moving average, the market is falling; this is a sell signal.

Let’s first learn how to calculate EMA using worksheet functions. After that we’ll discover how to use VBA to compute EMA (and automatically plot charts!)

Calculate EMA in Excel with Worksheet Functions

Step 1. Let’s say that we want to calculate the 12-day EMA of Exxon Mobil’s stock price. We first need to get historic stock prices – you can do that with this bulk stock quote downloader.

Step 2. Calculate the simple average of the first 12 prices with Excel’s Average() function. In the screengrab below, in cell C16 we have the formula =AVERAGE(B5:B16) where B5:B16 contains the first 12 close prices

Step 3. Just below the cell used in Step 2, enter the EMA formula above

Step 4. Copy the formula entered in Step 3 down to calculate the EMA of the entire set of stock prices.

There you have it! You’ve succesfully calculated an important technical indicator, EMA, in a spreadsheet.

Calculate EMA with VBA

Now let’s mechanize the calculations with VBA, including the automatic creation of plots. I won’t show you the full VBA here (it’s available in the spreadsheet below), but we’ll discuss the most critical code.

Step 1. Download historical stock quotes for your ticker from Yahoo Finance (using CSV files), and load them into Excel or use the VBA in this spreadsheet to get historical quotes straight into Excel. Your data may look something like this:

Step 2. This is where we need to excercise a few braincells – we need to implement the EMA equation in VBA. We can use R1C1 style to programatically enter formulae into individual cells. Examine the code snippet below.

1
2

Sheets("Data").Range("h" & EMAWindow + 1) = "=average(R[-" & EMAWindow - 1 & "]C[-3]:RC[-3])"
Sheets("Data").Range("h" & EMAWindow + 2 & ":h" & numRows).FormulaR1C1 = "=R[0]C[-3]*(2/(EMAWindow + 1)) + R[-1]C[0] * (1-(2/(EMAWindow+1)))"

• EMAWindow is a variable that equals the desired time window
• numRows is the total number of data points + 1 (the “+ 1” is because we’re assuming that the actual stock data starts on row 2)
• the EMA is calculated in column h

Assuming that EMAWindow = 5 and numrows = 100 (that is, there are 99 data points)

• the first line places a formula in cell h6 that calculates the arithmetic average of the first 5 historical data points

• The second line places formulas in cells h7:h100 that calculates the EMA of the remaining 95 data points

Step 3
This VBA function creates a plot of the close price and EMA.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

Set EMAChart = ActiveSheet.ChartObjects.Add(Left:=Range("a12").Left, Width:=500, Top:=Range("a12").Top, Height:=300)
With EMAChart.Chart
 
    .Parent.Name = "EMA Chart"
 
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .Values = Sheets("data").Range("e2:e" & numRows)
        .XValues = Sheets("data").Range("a2:a" & numRows)
        .Format.Line.Weight = 1
        .Name = "Price"
    End With
 
    With .SeriesCollection.NewSeries
        .ChartType = xlLine
        .AxisGroup = xlPrimary
        .Values = Sheets("data").Range("h2:h" & numRows)
        .Name = "EMA"
        .Border.ColorIndex = 1
        .Format.Line.Weight = 1
    End With
 
    .Axes(xlValue, xlPrimary).HasTitle = True
    .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Price"
    .Axes(xlValue, xlPrimary).MaximumScale = WorksheetFunction.Max(Sheets("Data").Range("e2:e" & numRows))
    .Axes(xlValue, xlPrimary).MinimumScale = Int(WorksheetFunction.Min(Sheets("Data").Range("e2:e" & numRows)))
    .Legend.Position = xlLegendPositionRight
    .SetElement (msoElementChartTitleAboveChart)
    .ChartTitle.Text = "Close Price & " & EMAWindow & "-Day EMA"
 
End With

Get this spreadsheet for the full working implementation of the EMA calculator with automatic download of historical data.

Download Excel Spreadsheet to Calculate EMA with VBA