Backtesting Technical Analysis Strategies Using Excel: SMA, RSI, MACD

Explore how to backtest technical analysis strategies using Excel and EODHD’s API, leveraging tools like SMA, RSI, and MACD to simulate and evaluate trading performance based on historical data.

Backtesting Technical Analysis Strategies is a crucial step for assessing the effectiveness of a trading strategy before applying it in real markets. By leveraging historical data, you can simulate the strategy’s performance, evaluate its risks, and estimate potential returns. In this article, we will demonstrate how to create an Excel template that fetches essential financial statement data from EODHD’s APIs using cell formulas, enabling you to analyze the financials of any public company.

You can also explore our guides for creating an Excel Stock Portfolio Tracker and Market Dashboard. These resources offer additional information on our Ready-to-Go solutions.

Download our Backtesting Tool Template to follow along with the article:

EODHD_Stratagy_Sim_Spreadsheet

Obtain Your EODHD API Key

To get started, register for a free EODHD account at https://eodhd.com/register (you can easily sign up with a Google account) and receive your API key. The free plan provides 20 API requests per day and one year of EOD historical data. Paid plans, starting at $19.99 per month, offer more API calls and access to over 30 years of data.

Test the Basic Functionality

For this tutorial, you can use the “demo” key to test the basic functionality of our APIs. The demo key gives you access to the Fundamentals and Historical data for AAPL, TSLA, MSFT, and MCD tickers.

Retrieve Data from EODHD API

The primary method to pull data into an Excel spreadsheet cell from an API is the `=WEBSERVICE()` Excel formula. This formula allows you to access all our Fundamental and Historical APIs. It is essential to refer to the official Microsoft documentation for more details.

To backtest technical analysis strategies, you need price time series data. Use the End-of-Day API to retrieve the OHLC data for your chosen stock:

=LET(raw_data,WEBSERVICE("https://eodhd.com/api/eod/"&$C$3&"?api_token="&$B$2&"&order=a&fmt=json&from="&TEXT($C$4,"YYYY-MM-DD")&"&to="&TEXT($C$5,"YYYY-MM-DD")&"&filter="&C11),
filtered_data,FILTERXML("<t><s>"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),",","</s><s>")&"</s></t>","//s"),
VALUE(SUBSTITUTE(filtered_data,"""","")))

- `WEBSERVICE` Function sends a web request to EODHD’s End-of-Day data API and retrieves the data.

- `$B$2` is the API token.

- `$C$3` is the ticker symbol.

- `$C$4` and `$C$5` are the date range.

- `C11` is the filter (date, open, high, low, close).

The `LET` Function defines variables within a formula:

- `raw_data` stores the raw data fetched from the web service.

- `filtered_data` uses `FILTERXML` to parse XML content and return the data based on an XPath query.

- `VALUE` Function converts text into a numerical format.

- The `SUBSTITUTE` Function cleans up the format before converting it into numerical values.

Defining the Technical Analysis Strategy

Next, we will define the technical analysis strategies we want to backtest. In this example, we will focus on three commonly used strategies: the Simple Moving Average (SMA) crossover, the Relative Strength Index (RSI) Strategy, and the Moving Average Convergence Divergence (MACD) Crossover Strategy.

Simple Moving Average (SMA) Crossover Strategy

The SMA crossover strategy is defined as follows:

• Buy Signal: When the 50-day SMA crosses above the 200-day SMA.
• Sell Signal: When the 50-day SMA crosses below the 200-day SMA.

To implement this strategy, we need to calculate the 50-day and 200-day SMA for the price time series. The Excel formula =AVERAGE(range) can be used to calculate the SMA for the specified period.

For an SMA indicator, you can also use EODHD’s Technical API. The cell function to get the SMA data will look like this:

=LET(raw_data,WEBSERVICE("https://eodhd.com/api/technical/AAPL.US?order=d&from=2017-08-01&to=2020-01-01&function=sma&period=50&api_token=demo&fmt=json"),
filtered_data,FILTERXML("<t><s>"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),",","</s><s>")&"</s></t>","//s"),
SUBSTITUTE(filtered_data,"""",""))

• WEBSERVICE Function fetches the SMA data for the specified period using the EODHD Technical API.
• LET Function stores the raw data and processes it.
• FILTERXML Function parses the XML content and returns the data.
• SUBSTITUTE Function cleans up the format.

By using this method, you can easily retrieve and calculate the SMA values needed for your backtesting strategy.

Relative Strength Index (RSI) Strategy

The RSI strategy can be summarized as follows:

• Buy Signal: When the RSI crosses above 30, indicating the stock is oversold.
• Sell Signal: When the RSI crosses below 70, indicating the stock is overbought.

The formula for RSI is defined as:

Implementing RSI in Excel

To calculate RSI in Excel, use the following formula:

=LET(
period, 14,
    prices, F12:F110,
    changes, prices - INDEX(prices,1),
    gains, IF(changes > 0, changes, 0),
    losses, IF(changes < 0, ABS(changes), 0),
    avg_gain, AVERAGE(IF(ROW(prices) > (ROW(prices)-period), gains)),
    avg_loss, AVERAGE(IF(ROW(prices) > (ROW(prices)-period), losses)),
    rs, IF(avg_loss = 0, 0, avg_gain / avg_loss),
    100 - (100 / (1 + rs)))

This formula computes the RSI values for the price data in the range F12:F110 and generates buy and sell signals based on the RSI thresholds.

Moving Average Convergence Divergence (MACD) Crossover Strategy

The MACD crossover strategy involves:

• Buy Signal: When the MACD line crosses above the signal line.
• Sell Signal: When the MACD line crosses below the signal line.

To calculate MACD and the MACD signal line, we need to compute EMA12 and EMA26. The formulas for MACD are:

• MACD: EMA12(close) — EMA26(close)
• MACD Signal: EMA9(MACD)

The formula for EMA is:

EMA = PreviousEMA + (1 / (period + 1)) * (close — PreviousEMA)

For the first PreviousEMA, we use the average of the previous range.

Implementing EMA in Excel

To retrieve EMA values using EODHD’s Technical API, use the following cell function:

=LET(raw_data,WEBSERVICE("https://eodhd.com/api/technical/AAPL.US?order=d&from=2017-08-01&to=2020-01-01&function=ema&period=50&api_token=demo&fmt=json"),
filtered_data,FILTERXML("<t><s>"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),",","</s><s>")&"</s></t>","//s"),
SUBSTITUTE(filtered_data,"""",""))

This function fetches EMA values from the API. To access the technical API, you need to register and use your API key.

By implementing these technical analysis strategies in Excel, you can backtest their performance using historical data. This allows you to evaluate their effectiveness before applying them to live markets.

Generating Trading Signals and Trades

To generate trading signals based on your defined strategies, create new columns in your spreadsheet. Use Excel’s IF function to determine if the criteria for a specific strategy are met.

Example: Crossover Strategy Signal

For a crossover strategy, you can use a formula to check if the short-term moving average crosses the long-term moving average. Here’s an example of how to implement this:

=IF(AND((L215-M215)>0,(L216-M216)<0),-F216,IF(AND((L215-M215)<0,(L216-M216)>0),F216,))

This formula will generate a trading signal by comparing the differences between the short-term and long-term moving averages on consecutive days. If the short-term moving average crosses above the long-term moving average, it generates a buy signal. Conversely, if the short-term moving average crosses below the long-term moving average, it generates a sell signal.

Example: RSI Strategy Signal

For the RSI strategy, use a formula to check if the RSI crosses above or below the specified thresholds. Here’s an example formula:

=IF(AND(I216 > 30, I215 < 30), F216, IF(AND(I215 < 70, I216 > 70), -F216, ))

This formula will generate a trading signal based on the RSI values. If the RSI crosses above 30, it generates a buy signal, and if the RSI crosses below 70, it generates a sell signal.

Calculating Total Returns

The formulas above will result in trading signals (buy or sell) depending on the criteria of the strategy. By summing all trades generated per strategy, you can calculate the total return of a specific strategy.

Conclusion

Backtesting Technical Analysis Strategies is an essential step in assessing the potential effectiveness of a given approach. By utilizing EODHD’s API and Excel, traders can efficiently gather historical data, simulate trades, and evaluate the performance of their technical analysis strategies. However, it is crucial to remember that past performance does not guarantee future results. Traders should use backtesting as one of many tools in their decision-making process, always mindful of the limitations and assumptions involved in the backtesting process.

Please note that this article is for informational purposes only and should not be taken as financial advice. We do not bear responsibility for any trading decisions made based on the content of this article. Readers are advised to conduct their own research or consult with a qualified financial professional before making any investment decisions.

For those eager to delve deeper into such insightful articles and broaden their understanding of different strategies in financial markets, we invite you to follow our account and subscribe for email notifications.

Stay tuned for more valuable articles that aim to enhance your data science skills and market analysis capabilities.