Building and Backtesting a Buy-Sell Model with Close Prices and EMAs
Buy-Sell models are among my favorite analytical techniques because they simulate what traders do on a regular basis – namely, buy and sell securities. However, no amount of backtesting is enough to guarantee how well a buy-sell model reflects actual market performance. This is because publicly traded financial security prices are driven by diverse factors that can change over time. Nevertheless, buy-sell models can offer useful guidelines for buying and selling securities when current market conditions are similar to previously backtested market scenarios.
EMAs are another of my favorite analytical tools for modeling the behavior of security prices. EMA is an abbreviation for an exponential moving average. However, an EMA is really not an averaging technique. Instead, EMAs are a smoothing technique for time series data. The period, or more precisely period length, of an EMA denotes the amount of smoothing for its underlying time series. The smaller the period for an EMA, the more responsive the EMA is to recent price changes. The larger the period for an EMA, the more responsive the EMA is to less recent price changes.
This post presents guidelines for computing EMAs with different periods. The focus is on computing EMAs within a Google Sheets worksheet, but the guidelines are broadly applicable to other computing platforms, such as Microsoft SQL Server (Exponential Moving Average Calculation in SQL Server).
This post also backtests a buy-sell model based on close prices and EMAs for two different tickers (SPY and SPXL). The SPY ticker is an ETF that tracks the S & P 500 index, and the SPXL ticker is an ETF that tracks three times the daily performance of the S & P 500 index. Ticker prices are tracked from their launch date through March 17, 2025. Historical prices are collected from the Google Finance site with the GOOGLEFINANCE function in Google Sheets.
A Quick Introduction to Computing EMAs
A common way to define an EMA value is with the following equations and expressions.
s1 = x0,
st = αxt + (1 – α)st-1,
where α is between 0 and 1, and the sum of α plus (1 – α) equals one.
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 zero for the initial underlying value. Because the second equation requires a prior exponentially smoothed value, the exponential moving average for time zero 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’s 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 10-period EMA, then the value of α would be 2/(10 + 1). The Investopedia recommendation is the approach used in this post because this blog’s target audience is the financial trading community.
The following screenshot shows an excerpt from a download from the Google Finance site for the SPY ticker in columns A and B of a Google Sheets worksheet.
The GOOGLEFINANCE function implementing the download resides in cell A6. The expression in the cell is =GOOGLEFINANCE("SPY", "close", date(1993,1,22), date(1993,12,31)). This expression extracts all the available SPY close price data during 1993 from the Google Finance site. Although the official SPY launch date is January 22, 1993, the Google Finance starts reporting close prices as of January 29, 1993.
Columns C through F display period length values for the computed EMA values in row 1.
Rows 2 and 3 in columns C through F show the computed values for α and 1-α. Notice that α values diminish as the period length grows. Conversely, the values of 1-α become larger as the period length grows. Also notice that the sum of α and 1-α is 1 for each column.
Rows 7 through 11 in columns C through F show the computed EMA values for the close values in column C.
o The EMA values in row 7 are empty because EMAs require a preceding close row value.
o Also, the close value in cell B7 is arbitrarily assigned to columns C through F in row 8. This assignment sets the initial EMA for all period lengths to the initial close value in B7.
o The values in rows 9 through 11 of columns C through F reflect the computed EMA values. The computed EMA values are based on α and 1-α as well as the close value for the current row.
The next screenshot shows the formula view for the cells in the preceding screenshot. As you can see, the computed EMA values for the current row in a column are equal to the sum of
· the close value for the current row times α plus
· the EMA for the preceding row in a column times 1-α.
The column expressions extend through the row with the final date for a timeframe, which in this section is 1993,12,31.
Line Charts for Close and EMA values for SPY and SPXL
Line charts for close prices and EMA values offer a convenient means for revealing price trends during a timeframe. For example, the following line chart shows the close prices and EMA values for the SPY ticker in 1993. The legend below the chart title indicates that close prices are depicted by a solid black circle. EMA names are denoted by EMA_ followed by the period length value.
The EMA_200 line in the following chart shows that SPY daily close prices are generally rising throughout 1993. This is because the EMA_200 line is upward sloping throughout the year. On the other hand, the daily close prices and the EMA_10 values indicate there are multiple crests and valleys throughout 1993. On some occasions when prices crater, the daily close prices fall below the EMA_10 values and sometimes daily close prices even fall below EMA_200 values. When trading a security, it is important to own it when daily prices are rising but sell the security before it gives up most of its gains. The next section presents a simple model along with some historical close prices and EMA values that you may find helpful for achieving that goal.
The next line chart shows 234 trading days of data for the SPXL ticker from when the Google Finance site initially started tracking close prices for the ticker. The SPY aims to show price trends that follow its underlying index on a daily basis. If the index goes up 1 percent during the day, then the SPY prices also increase about 1 percent during the same day. In contrast, if then the S & P 500 index goes up about 1 percent during a trading day, then the SPXL ticker aims to return about a 3 percent gain during the day. On the other hand, if the underlying index drops 1 percent during a trading day, then the SPXL daily close declines around 3 percent during that trading day. For these reasons, it is especially important to buy SPXL positions as soon as possible after they start to rise. It is equally critical to exit SPXL positions as soon as possible after prices begin to fall.
The EMAs and close prices in the following chart show SPXL prices declining from their initial coverage in early November 2008 through early October 2009. Some close price gains start after early March 2009, but close price gains are not consistent until after early July 2009.
The two prior line charts were individually presented and analyzed because they covered different timeframes. This is because the SPY and SPXL tickers have substantially different launch dates. The next pair of line charts are presented one over the other because both line charts share a common timeframe – namely from January 2, 2020 through March 17, 2025. The y axis ranges are different between the two charts because SPY close prices extend over a different range of values than SPXL close prices.
If you carefully examine the following line charts you might be able to discern that the volatility for SPXL close prices is larger than the volatility for SPY close prices. This is especially evident by the SPXL dips in close prices being deeper in early 2020 and March, 2025 than the SPY dips for the same two date ranges.
To verify the greater volatility for SPXL close prices relative to SPY close prices, this post reports the coefficient of variation for both SPXL and SPY close prices. The SPXL coefficient of variation is slightly more than twice as large as the SPY coefficient of variation. This confirms that the volatility of SPXL close prices is greater than volatility of SPY close prices. When you correctly project the direction of the volatility, higher volatility can lead to higher gains.
Comparing Model Price Changes for SPY and SPXL Tickers
This section builds on the introduction and the prior two sections, delving into EMA calculations and showcasing line charts that highlight closing prices and EMA values for a given ticker. The three objectives of this section are to
describe the rationale for a model that depends on owning a security based on historical close prices and EMA values,
demonstrate how to identify buy and sell dates for a stock ticker within a timeframe, and
summarize and compare model performance for SPY and SPXL tickers over different timeframes.
Describing the Model Rationale
The model examined in this post depends on mining close prices as well as EMA_10 and EMA_200 values to determine when are profitable times to buy or to sell a security.
It is a profitable time to buy security when a ticker’s close price switches to being greater than both EMA_10 and EMA_200, after not being greater than both EMA_10 and EMA_200.
o The model assumes that this change marks a transition from a declining close price trend to an increasing close price trend.
o Buying a ticker at this transition point is equivalent to buying low in anticipation that close prices will rise until something changes (more specifically, until the close price switches to not being greater than both EMA_10 and EMA_200).
It is a profitable time to sell a security when a ticker’s close price switches from not being greater than both EMA_10 and the EMA_200, after being greater than EMA_10 and EMA_200.
o The model assumes that this change marks a transition from an increasing close price trend to a decreasing close price trend.
o Selling a ticker at this transition point is the equivalent to selling high in anticipation that close prices will decline until something changes (more specifically, until the close price switches to being greater than both EMA_10 and EMA_200).
Demonstrate How to Identify Buy and Sell Dates for a Ticker
This subsection displays excerpts from a worksheet with SPY close prices and EMA values for dates from February 1, 1993, through March 11, 1993. Inputs to the buy-sell model appear in columns A, B, C, and F. The model also depends critically on the contents of column H and J and to a lesser extent on the contents of column K.
The following screenshot shows cell H2 selected. You can see at the top of the screenshot an expression (=and(B2>C2,B2>F2)) that returns a value or TRUE or FALSE for each cell to which it is copied. The cells to which the expression is copied should correspond to an evaluation period for the model. The expression returns a value of TRUE when the close price for a trading date is greater than both EMA_10 and EMA_200. Otherwise, the expression returns a value of FALSE.
Trades for a ticker extend from the first row that has a value of TRUE or from the first row with a value of TRUE that is preceded by a return value of FALSE in column H.
If a date has an orphan TRUE or FALSE value with preceding and trailing values of the opposite value, then the model does not specify a trade for that date.
The value in cell J10 (0.69) reflects the close price change from cell B2 through cell B10; the expression for the J10 cell value is =B10-B2. Rows 2 through 10 correspond to the first set of TRUE values in column H. The value in cell J10 reflects the share price change for owning a share of the SPY ticker from February 1, 1993, through February 11, 1993.
The second block of consecutive TRUE values in column H start on February 24, 1993, and run through March 11, 1993. The J column value for March 11, 1993, reflects the change in close values over this interval. The expression for the J column value on March 11, 1993, is =B29-B18. The J29 column value of 1.31 is the price change for the second consecutive block of TRUE values in column H.
The values in column K correspond to the price change for consecutive trading days with a value of FALSE in column H. The model does not dictate that ticker shares, such as those for SPY, should be bought and sold for consecutive blocks of FALSE values in column H. Nevertheless, comparing the price changes for consecutive blocks of FALSE values versus price changes for consecutive blocks of TRUE values is one way you can depict the power of the model.
In the screenshot below, there is just one consecutive block of FALSE values in column H. The price change (-0.90) of a single SPY share for the corresponding trading days appears in cell K17. The difference in column J and K values hint at the power of the model. Both of the values in column J are positive, and the column K value in the screenshot is negative. Therefore, these three values hint at the validity of the model for picking winning buy-and-sell trade dates.
Summarize and Compare Model Performance
The following table displays start and end dates for the first and second timeframes on which backtesting was performed for the buy-sell model described in the preceding two subsections. The start and end dates are different for the first timeframe because the SPY and SPXL tickers have different launch dates. The start and end dates are different for the second timeframe because the distribution of TRUE and FALSE values for when to buy and sell a security can vary by trading date across tickers.
The following four tables present summary performance results for the SPY and SPXL tickers in their first and second backtesting timeframes. Within each combination of tickers and timeframes, two buy-sell rules are backtested. The rule for buying when the close price switches to being larger than both EMA_10 and EMA_200 and selling on the last consecutive trading day when the close price is larger than both EMA_10 and EMA_200 is displayed on the left side of each of the four tables. The rule for buying when the close price switches to not being larger than both EMA_10 and EMA_200 and selling on the last consecutive trading day when the close price is not greater than both EMA_10 and EMA_200 is displayed on the right side of each of the four tables.
There are four metrics for each set of backtest results. Additionally, the four metrics are computed separately for consecutive trading days with a TRUE value in column H and a FALSE value in column H. Three of the metrics are based on the change in price from the buy close price to the sell close price. The fourth metric is a count of the buy-sell transactions for each set of consecutive TRUE or FALSE values in column H. The six buy-sell metric labels related to price changes are
TRUE Price Change for the change in close price for the sets of consecutive trading days with a TRUE value in column H,
Gain from 100 shares for the change in close price multiplied by 100 for the sets of consecutive trading days with a TRUE value in column H,
Average TRUE Price Change for the Gain from 100 shares divided by the count of consecutive TRUE value sets in a timeframe,
FALSE Price Change for the change in close price for the sets of consecutive trading days with a FALSE value in column H,
Loss from 100 shares for the change in close price multiplied by 100 for the sets of consecutive trading days with a FALSE value in column H, and
Average FALSE Price Change for the Loss from 100 shares divided by the count of consecutive FALSE value sets in a timeframe.
Whenever a price change metric is positive, it has a green backdrop color in the following four tables. Similarly, whenever a price change metric is negative, it has a red backdrop color. These backdrop colors validate the model because the backdrop color is always green for timeframes and tickers with a TRUE value in column H. The TRUE Price Change metrics are for sets of consecutive trading days that are selected by the buy-sell model.
Concluding Comments
This post examines a buy-sell model for a pair of securities (SPY and SPXL). The model is backtested for each security over two different timeframes. The aim of the model is to designate when it is profitable to own a security. The post’s concluding subsection summarizes and compares the model’s success at achieving its goals for the two tickers examined in this post.
The buy-sell model designates when to buy and sell a security’s ticker based on the relationship between the security’s close prices and its EMA values. The model assumes it is profitable to hold a security whenever its close prices are greater than both its EMA_10 and EMA_200 values. When the close price is not greater than both EMA values, then the model assumes it is not likely to be a profitable time to own the security.
The robustness of a model is reflected by its ability to achieve its goals under many different circumstances. One way to assess the robustness of the model presented in this post is by examining the ability of the model to designate profitable dates for trades in more than just two timeframes. Another way to assess the robustness of a buy-sell model is to verify if the model succeeds at achieving its goals for more than the two tickers examined in this post. I encourage you to perform both kinds of robustness tests for tickers and timeframes that are of special interest to you.
The model’s buy and sell dates are selected in this post by visual inspection of the outcome of an expression in a Google Sheets worksheet. Before evaluating the model’s validity for different combinations of tickers and timeframes, it would be beneficial to automate the backtesting process for designating buy and sell dates. This would help to validate and speed the backtesting process, which, in turn, would permit more backtest analyses.
This post closes by reminding you that the model examined in this post is just one of many possible buy-sell models based on EMA and close values. By examining different models, you will learn more about this model as well as other models. Also, you should understand that models do not necessarily reflect real-world outcomes. The more diverse your backtesting strategy, the more you will be able to derive values from your models and other analytical tools that enable you to discover profitable trades.
Comments
Post a Comment