An Introduction to Computing and Interpreting EMAs with Excel

 

This post offers introductory coverage for computing and interpreting exponential moving averages (EMAs) with Excel.  I have written about and regularly use EMAs in my own securities trading.  A couple of my prior articles (Differences Between Exponential Moving Average and Simple Moving Average in SQL Server and Revisiting Time Series Model Performance Assessment with T-SQL) focus on how to compute and interpret EMAs.   Because of the significance of EMAs to security trading analytics, I expect to issue multiple posts on this topic in the next couple of years.

This is the initial post with coverage of EMAs in this blog.  Therefore, the coverage will be both basic and simple from an analytical perspective.  The focus of this post is to show how to use Excel for computing EMAs and interpreting EMA values.  Excel’s charting capabilities are tapped to illustrate how to interpret EMAs.  The post also includes a spreadsheet analysis of how to make profitable decisions about when to own a security.

One time series will be used in this post – namely Close prices for the SPY ticker, which is an ETF for the S&P 500 index.  The dates tracked in this post start at 1/2/2024 and run through 6/5/2024.  Much longer timeframes with many more tickers will be required to reach generally applicable conclusions.  Nevertheless, the analytical demonstrations in this post have value if they inspire you and/or others to extend the analysis shown in this post.

What are EMAs?

Although EMA stands for exponential moving average, it is not an average value like a simple moving average value.  A simple moving average series is a set of arithmetic average values for a set of underlying time series values.  A window of n periods moves successively over periods of the underlying time series values.  For a ten-period simple moving average, the first simple moving average is the average of the first ten time series values; this value is for the eleventh period.  The ten-period simple moving average is undefined until after the first ten periods.

After the first simple moving average is computed for the eleventh period, subsequent moving average values are computed by moving the window forward by one period.  Therefore, the second simple moving average value is the arithmetic average for underlying values from periods 2 through 11.  This value is for the twelfth period.

EMA values do not rely on a window of the n most recent values.  Instead, EMA computations always start with the second period and extend through the most recent current period.  One additional period is added to the computation with each successive period.

Also, simple moving average values assign the same weight to all their underlying values, namely 1/n.  In contrast, EMA values do not weight their underlying values identically.  Instead, EMA values are the weighted average of two terms: the current most recent underlying value, and the EMA value from the prior period.  With each successive period, the computed EMA value from the last period becomes the EMA from the prior period.

The iterative nature of the computation for EMA values results in the weight for prior most recent underlying value being weighted in an exponentially reducing fashion.  Furthermore, the weights for prior underlying values grows progressively smaller with successively more recent periods.

A common way to define an EMA value is with the following equations and expressions. 

s₁ = x_0,

s_t =  αx_t + (1 – α)s_t-1,

where α is between 0 and 1, and the sum of α plus (1 – α) equals 1.

• The set of x values are the underlying series values, such as the successive Close prices for a ticker symbol.
• The s values are the exponentially smoothed values.
• The t values begin at time 0 for the initial underlying value.  Because the second equation requires a prior exponentially smoothed value, the exponential moving average for time 0 is undefined, and the computed EMA values commence at time 1.
• The value of α is the weight for the current period’s underlying value, and the value of (1 – α) is the weight for the prior period exponentially smoothed value.

Wikipedia asserts there is no formally correct way for assigning a value to α.  It also indicates that least squares estimation techniques are sometimes used to calculate a value to α.  For these reasons, authors from different disciplines demonstrate different techniques for computing the initially computed EMA value.   Investopedia recommends basing the α value on the period length for an EMA series.  For example, if you were computing a 20-perod EMA, then the value of α would be 2/(20 + 1).  The Investopedia recommendation is the approach used in this post because this blog’s target audience is the financial trading community.  Nevertheless, alternative α estimation techniques may be examined in future blog posts to evaluate the relative efficacy of the Investopedia recommendation.

An Example of a Ten-period EMA for the SPY Ticker with Excel

The data and computed EMA values for this post reside in the SPY_EMA_WORKSHEET.xlsx file.  The following screenshot shows an excerpt from the top portion of the SPY_EMA_10 tab in the file.

• Columns A and E display Date and Close values for the SPY ticker from 1/2/24 through 2/21/24.
• Columns B through D as well as F through H contain hidden price and volume data for each Date.  The hidden values are not required for the computation of ten-period EMA Close values.
• The ten-period EMA Close values in column E appear in column I.  These EMA values are based on the equations and expressions presented in the preceding section.
• The values in column A are downloaded for the SPY ticker from the Yahoo Finance web site.  The blog post titled How to Download and Display Historical Price and Volume Data illustrates how to download historical price and volume data without charge as a csv file from Yahoo Finance and import the file contents into an Excel worksheet.
• Columns L, M, and N in rows 1 and 2 contain the names for the EMA period length along with the corresponding values for α and (1–α).

The chart appearing in columns M through W of the preceding screenshot contains a scattergram of the Close values and EMA values along the vertical axis and Date along the horizontal axis.  The Close values show as black circle markers.  The EMA values are represented along the red line without markers.  Notice how the red line most often falls below the black circle markers.   This is because the Close values for the sample dataset used in this example generally rise across the dates from 1/2/24 through the Date for last Close value on 6/5/24.

By the way, Excel has multiple formats for representing Date values.  Two common ones used interchangeably in this post are mm/dd/yy and mm/dd/yyyy.  In the preceding chart, Date values are represented with a mm/dd/yy format.

The next screenshot shows an excerpt from the bottom portion of the SPY_EMA_10 tab in the SPY_EMA_WORKSHEET.xlsx file.  This excerpt confirms that Close values run through 6/5/24.  Also, ten-period EMA values extend through the same date.

• The initial Close value (472.649994), which appears in the preceding screenshot, is on 1/2/24.
• The initial EMA value, which also appears in the preceding screenshot, is the same value, but it does not occur until 1/3/24.  Recall from the commentary for the equations at the end of the preceding section that the first underlying Close value occurs one period before the first EMA value and that the first period’s underlying value is assigned to the initial EMA value.

The next pair of screen shots show the top and bottom several rows of the spreadsheet in a formula view.  This view serves two roles.

• First, it shows the Excel expressions for computing α and (1-α).
• Second, it shows how to translate the equations for computing exponentially smoothed values into Excel expressions based on cell addresses.

The discussion of the top chart below starts with a review of the Excel formulas and values computing both α and (1-α).

• The two computed values (α and 1-α) are based on the period length for the EMA values.  In this example, the value of 10 is entered into cell L2.  The text in cell L1 (period length) labels the value in cell L2 as the period length for the EMA values computed in column I.
• The expressions in cells M2 and N2 are, respectively, for the computed values of α and (1-α).
• It is necessary to populate cell L10 before attempting to assign or compute EMA values. 

Columns A, E, and I contain values and expressions for computing the first five EMA values.

The top screenshot shows values and expressions for computing the first five EMA values in rows 3 through 7.

• Column A contains unformatted Date number values, which is the default way of showing dates in Excel’s formula view.
• Cell I3, which is for the first EMA value, is assigned the value in cell E2, which is the first underlying Close value.
• Cell I4 holds the expression for the first computed EMA value.  This cell contains the following expression: =$M$2*E4+$N$2*I3.  The terms in this expression correspond to the second equation for computing an exponentially smoothed value.
• $M$2 maps to α
• E4 maps to x_t
• $N$2 maps to (1 – α)
• I3 maps to s_t-1, which for cell I4 references the value in cell E2; recall that E2 contains the initial underlying Close value
• Cell I5 holds the expression for the second computed EMA value.  The expression for cell I5 (and all the remaining EMA values in column I) was derived just by copying the expression from cell I4 to cell I5.  Cell I5 contains the following expression: $M$2*E5+$N$2*I4.  The mappings of the expression terms to the second equation for computing an exponentially smoothed value are as follows:
• $M$2 maps to α
• E5 maps to x_t
• $N$2 maps to (1 – α)
• I4 maps to s_t-1, which for cell I5 references the value in cell E3; cell E3 holds the previously computed exponentially smoothed value (s_t-1)

The bottom screenshot in the following table shows values and expressions for computing the last five EMA values in rows 105 through 109.

An Overview of EMAs with 30, 50, and 200 Period Lengths

Period lengths are a fundamental property of EMAs.  The shorter the period length, the more dependent the EMA value is on the most recent underlying value.  The longer the period length, the less dependent the EMA value is on the most recent underlying value.  When an EMA series with a shorter period length crosses from a below to above an EMA series with a longer period length, it is possible that an uptrend in the underlying values may be beginning.  When using more than two EMAs series with different period lengths, and all the EMA series start to move upward and to the right, this confirms that an uptrend is ongoing.  Finally, when the underlying values for multiple EMA series rise above the EMA series with the shortest period length and all series are moving upward and to the right, this indicates that an even stronger uptrend is ongoing.  All these EMA series patterns are generally good ones to buy and hold for as long as the pattern holds.

Before you dive into trading based on multiple EMA series, you may be interested in learning how to create the series.  My advice is to compute the EMA with the shortest period length first.  Then, successively add EMA series with longer period lengths for the same underlying time series.  After computing all the EMA series that you care to examine and compare, then combine all the EMA series with different period lengths into a single dataset, which can be on a single worksheet tab when working with an Excel file.

This section shows how to compute EMA series with 30, 50, and 200 period lengths.  All the EMA series are for the same underlying values used in the prior section – namely, the SPY ticker starting on 1/2/2024 and continuing through 6/5/2024.  The next section shows one approach to consolidating the individual EMA series into a single dataset.  The performance of the underlying Close values are then analyzed in relation to their EMA series values to discern prospective profitable periods during which to own a security.

The following screenshot shows an excerpt from the SPY_EMA_30 tab.  It displays underlying Close values and their computed EMA values from the first 35 underlying Close values in rows 2 through 36.  However, EMA_30 series values are only available in the 34 rows from rows 3 through 36.

As with the EMA_10 series from the preceding section, the EMA values commence on 1/3/2024.  Recall that this is because an EMA series needs two consecutive periods in order to start computing EMA values.  The SPY_EMA_30 tab is basically a copy of the SPY_EMA_10 tab with a very important exception.  The period length value in cell L2 in the following excerpt has a value of 30 in cell L2 while the period length setting for the SPY_EMA_10 tab is 10.  As a result of the new period length setting, the computed values for α and (1-α) are different between the two tabs.  The tab with a period length of 30 has a smaller value for α than the tab with a period length of 10.  This means that EMA series values with the longer period length (30) puts a smaller weight on the current period than the EMA series values with a period length of 10 from the SPY_EMA_10 tab.

The following screenshot shows an excerpt from the SPY_EMA_50 tab.  The value of α from the following screenshot is just 0.039215686274510, but the value of α from the preceding screenshot is 0.064516129032258.  This means the value of α for the EMA series with a 30-period length is about 64 percent larger than for the EMA series with a 50-period length.  Because underlying Close values are generally for the sample dataset in this post, the EMA series values based on a 50-period length are also generally below the EMA series values based on a 30-period length.  For example, the last row in the preceding screenshot has an EMA value of 490.297266, but the last row in the following screenshot has a value of 485.744774.

The next screenshot is an excerpt from the SPY_EMA_200 tab.  The EMA values on this tab have the same underlying Close values as in the preceding two screenshots within this section.  However, the period length in the SPY_EMA_200 tab is 200.  This period captures long-run trends in comparisons to the two preceding screenshots.

After the first ten periods, the blue line of EMA_200 values in the following screenshot are all below their corresponding Close values.  In contrast, the yellow and green lines of EMA_30 and EMA_50 values  in the two preceding screenshots are closer to their underlying Close values.  As a result of this closeness, several underlying Close values from April in the preceding two screenshots are below their EMA lines.  However, in the following screenshot, there is no overlap of underlying Close values with their EMA values during April.  Also, the EMA_200 value for 2/21/2024 is 476.938765, but the EMA_30 and EMA_50 values for 2/21/2024 are, respectively, 490.297266 and 485.744774.  This is yet another confirmation that EMAs with longer period lengths in this example can return smaller values than EMAs with shorter period lengths.

The Impact of EMA Period Length on Profit and Loss from Holding a Security

This section presents an exploratory analysis to assess the relation of Close values to EMA values based on different period lengths.  The analysis is for the SPY ticker as well as the overall beginning and end dates for the SPY ticker EMA values.  Changing either the ticker or the beginning and end Date can easily lead to radically different analysis outcomes.  This section uses the same period lengths examined in prior sections – namely, 10, 30, 50, and 200 period length EMA value series.  The main objective of the analysis is to assess if holding a security when its Close value is greater than or equal to its corresponding EMA series values with 10 and 30 period lengths yield better returns than when its Close value is not greater than or equal to its corresponding EMA series values with 10 and 30 period lengths.

There are four steps for completing the analysis in this section.

• Begin by consolidating the Close values for the SPY ticker with each set of EMA values in an Excel tab.
• Next, specify a set of rules for designating when to begin holding and when to stop holding the SPY ticker for successive Date ranges.
• Apply the rules for holding and releasing the SPY ticker to the Close values with matching EMA series values.
• Finally, compute summary Close price differences for successive blocks defined by the relationships between Close values and EMA values.

Here are the rules for holding and releasing a ticker in this post.  If the rules are not perfectly clear after reading them, excerpts from a sample worksheet with Close and EMA values will help to clarify them.

• Begin holding a ticker when its Close value is initially above the EMA_10 value in a block.  There are two criteria that can trigger this event.
• The Close value initially rises above its corresponding EMA_10 value for the first time in the overall time series.
• The Close value rises above its corresponding EMA_10 value after the end of a preceding block where the Close values are not above their corresponding EMA_10 or EMA_30 values.
• After you begin holding a ticker, continue holding the ticker for as many periods as its Close value is greater than its EMA_10 value or greater than or equal to EMA_30 value.
• Stop holding a ticker when its Close value falls below its corresponding EMA_10 and EMA_30 values or when there are no remaining time series values.

The consolidated data with Close values across all dates and with EMA values for all four period lengths are saved in the SPY_EMA_10_30_50_200 tab of the SPY_EMA_WORKSHEET.xlsx file.  The data are segmented into the following three sections for display within this post.

Here is an excerpt showing the data for the first 38 rows. Columns A and E are for Date and Close values, respectively.  Columna F, G, H, and I are, respectively, for corresponding EMA_10, EMA_30, EMA_50, and EMA_200 values.  The cursor rests in cell F3.  The expression of the cell value indicates it was copied from the I3 cell of the worksheet in the SPY_EMA_10 tab.

The next excerpt is for the second segment of rows.  These rows are for rows 39 through 75 of the SPY_EMA_10_30_50_200 tab of the SPY_EMA_WORKSHEET.xlsx file.

The final third of rows from the SPY_EMA_10_30_50_200 tab of the SPY_EMA_WORKSHEET.xlsx file appears next.  As you can see there are 109 rows of Date, Close, and EMA values in the SPY_EMA_10_30_50_200 tab.  This row count matches row counts for values from each of the preceding tabs for individual tabs for 10, 30, 50, and 200 period length EMA value sets.  Recall that the discussion of the first segment  the of the three segments for this section indicates that values for the worksheet in this section are copied from the preceding four worksheets.

The text color of the rows in the preceding three worksheet segments denote the relationship of the Close value to the EMA values on a row.  In general, the text color of a row is either green (to denote it is time to hold a ticker) or red (to denote it is not time to hold a ticker after previously holding it).  A black text color indicates that a row of cells is not being used in the analysis because the row has not yet met the criterion to initiate holding or releasing a ticker.  This color is used for rows 2 through 5.

• Row 2 has black text because there is no EMA_10 value to compare to the Close value on the row.  Recall that the first row EMA value is always missing because an EMA requires both the EMA value for the preceding row as well as underlying value for the current row.
• Rows 3 through 5 have black text because the Close values for each of these rows is less than their EMA_10 and EMA_30 column values.
• Row 6 has green text because it is the first row with a Close value greater than its EMA_10 value.
• Rows 7 through 11 have green text because their Close values are greater than their corresponding EMA_10 columns values.
• Row12 has red text because its Close value is less than both its EMA_10 and EMA_30 column values.
• Row 13 has green text because it is the first row after a row with red text, and its Close value is greater than its EMA_10 value.
• Rows 14 though 65 have green text because their Close values are greater than their EMA_10 value or greater than or equal to their EMA_30 value.
• For example, row 14 has green text because its Close value is greater than its EMA_10 value.
• This is also true for many of the rest of the rows in the block beginning at row 13 and running through row 65.
• However, at least several other rows have Close values
• that are less than their corresponding EMA_10 value
• but are greater than or equal to their corresponding EMA_30 value.
• Three examples of rows like this are rows 22, 31, and 35.
• Row 66 has red text because its Close value is less than both its EMA_10 and EMA_30 values.  It is worth noting that row 66 is not part of a block of cells.
• Rows 67 through 69 have green text because their Close values are greater than their EMA_10 value or greater than or equal to their EMA_30 value.
• Row 70 is another example of a row with red text that is not part of a block of cells.
• Row 71, in contrast, is a row with green text that is not part of a block of rows.
• Rows 72 through 78 have red text.  Each of these rows has a Close value that is lower than both its EMA_10 and EMA_30 values.
• Rows 79 and 80 have green text.  The Close values for both rows are greater than the corresponding EMA_10 values.
• Row 81 has red text because its Close value is less than both its EMA_10 and EMA_30 values.  Like row 66, row 81 is not part of a block of cells.
• Rows 82 and 83 have green text.  For this pair of rows, the first row’s Close value is greater than the EMA_10 value.  The second row also has green text because its Close value exceeds its EMA_10 value.
• Rows 84 through 85 have red text.  Each of these rows has a Close value that is lower than both its EMA_10 and EMA_30 values.
• Rows 86 through 109 have green text.
• Row 86 has green text because it is the first row after a row with red text and its Close value is greater than its EMA_10 value.
• Rows 87 through row 109 have green text because each of these rows has a Close value that is greater than either its corresponding EMA_10 value or greater than or equal to its EMA_30 value.

A main purpose for color coding the text in row blocks is to denote when the Close values and EMA values jointly indicate when it may be profitable to hold shares of a ticker (green text) as well as times when it is likely not profitable to hold ticker shares (red text).

The analysis implemented in this post requires at least two contiguous rows with the same color text to denote a block of rows.  The Close price for the last row in a block of rows having similarly colored text represents a time to sell ticker shares, and the Close price for the first row in a block of rows with similarly colored text represents a time to buy ticker shares.  The Close price at the sell time for a row block less the Close price at the buy time for a row block indicates the change in Close values within a row block.

To clarify the computation of change values for row blocks, this post shows three screenshots.

The following screenshot displays one complete row block with green text in rows 6 through 11.  The change value for this row block is the value in cell E11 less the value in cell E6.  The expression for this difference (=E11-E6) is in cell J11, but the value for the expression appears below in cell J11.

The next screenshot is for rows 66 through 85.  This screenshot displays change values for 3 green row blocks and two red row blocks.

• The three green row blocks start and end, respectively in rows 67 and 69, rows 79 and 80, as well as rows 82 and 83.
• The two red row blocks start and end, respectively, in rows 72 and 78 as well as rows 84 and 85.
• The values in column J are for green row block changes, and the values in column K are for red row block changes.
• The block change values are computed by expressions in cells.  For example, the value in cell K78 is computed with this expression =E78-E72.
• Also note that the change value for green blocks are in column J and the change value for red blocks are in column K.

The final screenshot for row block changes appears next.  This row block begins in row 86 and extends through row 109.  All these rows have green text and the series ends on row 109 with a Date of 6/5/2024.  The Close change value for this row block appears in cell J109.  The change value of the row block is 29.639984.

One obvious question is: do the buy and sell rules for the row blocks lead to larger, more positive change  results than just for buying once at the beginning of the time series and selling again after reaching the last value in the time series?

The following screeshot of a table presents some summary results that help to answer the question.  There are three columns in the table and multiple rows.

• The first column shows the Close change value for a buy and hold strategy.  This strategy calls for buying at the first Close value eligible for analysis in cell E6 (474.600006) and selling at the last Close value eligible for analysis in cell E109(534.669983).  The difference between these beginning and ending values is 60.069977.
• There are two sets of Close change values for the buy and sell strategy.
• The sum of the Close change values across all rows with green text is 75.339966.  All except one Close change value for row blocks with green text are positive, and the one non-positive value is nearly 0.
• The sum of the Close change values for row blocks with red text is -12.76001.  There are only two row blocks with red text, and both of these Close change values are negative.
• The fact that the sum of the green_change Close values is positive while the sum of the red_change Close values is negative, indicates that the buy-and-sell strategy based on Close values and EMA values is very effective at discerning when prices are likely to rise.
• It is also possible to evaluate the buy-and-sell strategy versus a simpler buy-and-hold strategy by comparing the sum of the green_change Close values (75.339966) to the Close change (60.069977) for the buy-and-hold strategy.  The following table shows the sum of the green_change Close values to be slightly more than 25% larger than for the buy-and-hold.
• As a result of these two successes for the buy-and-sell strategy based on Close values and moving averages, we can conclude that EMA values versus Close values are a powerful pair of tools for estimating when to make buy and sell decisions for securities prices.

Concluding Comments

This post aims to introduce you to the basics of computing and applying EMAs for financial time series values.  I hope this post inspires you to learn more about EMAs and apply them to make more profitable trades.  Future posts on this blog will show more detailed steps and examples for the techniques introduced in this post.

Also, I want to emphasize that the outcomes in this post are for a very small sample.  As a result, they are likely not necessarily representative of what you might find with a larger sample or with different tickers or with different timeframes.  Nevertheless, the analytic design for arriving at the outcomes has broad applicability if you work with a much larger sample and/or deal with more than one ticker.  My hope is that the analytical demonstrations in this post will inspire you to perform your own analyses for your personal trading and/or the trading of clients that depend on your analytical results for making trades.

This blog will definitely publish additional posts on EMAs.  In the meantime, please consider sending a message to RickDobsonBlogs@gmail.com with a request for the sample Excel file used in this post as well as any questions you may have about the analyses presented in this post, security trading analytics, or other indicators for financial time series values.  I look forward to answering your questions in upcoming posts.