Technical trading is a little out of my wheelhouse, but when I came across the exponential moving average (EMA), I thought it was interesting enough to build a model. You can do a Google search to see how various traders use a simple moving average (SMA) and an EMA. As is true in all my Excel workbooks, yellow cells are input cells. You can use technical trading for all kinds of markets such as, stocks, currencies, interest rates, commodities, etc. I choose the ratio between the U.S. dollar and the Euro. I pulled the data off FRED (Saint Louis Federal Reserve Economic Research site) https://research.stlouisfed.org/.
I used the dates and closing prices. If you use this sheet for another set of data, remember to delete any NA() or blank rows. To calculate the SMA, I placed the formula below into cell C5 and copied it down over 8000 rows (although that many prices is probably too many):
Periods = Days looking back to average
The Periods +4 was because the data starts on row 5. The formula creates a range going back in time, that is equal to as many prices as the name Period represents and divides the sum of the range by the name Period, or if there are insufficient prices to average the number in Periods, it returns errors that are replaced with blanks.
Next, for the EMA, I put this formula in cell D5, and copied it down:
Alpha = 2/(Periods+1)
This formula will return blanks, unless the date in column A is equal to the starting date ($j$2). The formula then uses the SMA next to that date as the starting point for the first EMA. From that date on to the Ending Date it uses Alpha*(price in column B-previous EMA in column D) +previous EMA in column D.
Now we come to the other input cells:
For the calculations and the chart dates I used the starting and ending dates above in the yellow cells, and for the number of periods to average I used 200. The date cells are dropdown boxes, so that only a date in column A can be entered. You can cause an error by either having starting and ending dates backwards, or using a starting date that does not allow the SMA formula to go back the number of periods needed.
Excel will always default to a zero as the lowest value in a line chart. By clicking the button “Adjust Chart” the chart will adjust to the minimum and maximum prices in the range.
The “Buy” and “Sell” points are not part of the model, they are there just to show you how some traders might use the SMA and EMA when they cross each other.
Download Excel Workbook: EMA