{"id":5521,"date":"2024-04-29T05:49:05","date_gmt":"2024-04-29T05:49:05","guid":{"rendered":"https:\/\/eodhd.com\/financial-academy\/?p=5521"},"modified":"2024-07-24T16:11:46","modified_gmt":"2024-07-24T16:11:46","slug":"backtesting-technical-analysis-strategies-with-excel","status":"publish","type":"post","link":"https:\/\/eodhd.com\/financial-academy\/backtesting-strategies-examples\/backtesting-technical-analysis-strategies-with-excel","title":{"rendered":"Backtesting Technical Analysis Strategies with Excel(SMA, RSI, MACD)"},"content":{"rendered":"\n

Backtesting <\/a>Technical Analysis Strategies is an essential process for evaluating the effectiveness of a trading strategy before deploying it in live markets. By using historical data, you can simulate the performance of your strategies and assess risk and potential returns. In this article, we’ll walk through how to build an Excel template that pulls key financial statement data from EODHD’s APIs using cell formulas, allowing you to analyze any public company’s financials. <\/p>\n\n\n\n

EODHD <\/a>provides simple JSON APIs covering fundamentals, stock prices, and more for 150,000+ tickers across 70+ worldwide exchanges.<\/p>\n\n\n\n

Register & Get Data<\/span><\/a><\/p>\n\n\n\n

You can also check our guides for Excel Stock Portfolio Tracker<\/a> and Market Dashboard<\/a>. Here you’ll find more information on our Ready-to-Go<\/a> solutions.<\/p>\n\n\n\n

Download our Backtesting Tool Template to follow the article:<\/p>\n\n\n\n

EODHD_Stratagy_Sim_Spreadsheet<\/a>Download<\/a><\/div>\n\n\n\n

<\/p>\n\n\n\n

Sign up for EODHD and get your API key<\/h2>\n\n\n\n

Register for a free EODHD account at https:\/\/eodhd.com\/register<\/a> to receive your API key. The free plan allows 20 API requests\/day and 1 year of EOD historical data. Paid plans starting at $19.99\/month<\/a> enable more API calls and access to 30+ years of data.<\/p>\n\n\n\n

Register & Get Data<\/span><\/a><\/p>\n\n\n\n

To follow the article and test the basic functionality <\/a>of our APIs you can use our “demo” key. The key gives you access to the Fundamentals and Historical data for AAPL, TSLA, MSFT, and MCD tickers.<\/p>\n\n\n\n

Pull in data from EODHD API <\/h2>\n\n\n\n

The main tool to retrieve data into the spreadsheet cell from an API is a “=WEBSERVICE()” Excel formula. By using the formula you can get access to all our Fundamental And Historical APIs. Thus, It is important to check the official documentation from Microsoft<\/a>.<\/p>\n\n\n\n

Backtesting Technical Analysis Strategies Requires price time series. Use the End-of-Day API<\/a> to retrieve the EOD OHLC data for your chosen stock:<\/p>\n\n\n\n

=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),\nfiltered_data,FILTERXML(\"<t><s>\"&SUBSTITUTE(MID(raw_data,2,LEN(raw_data)-2),\",\",\"<\/s><s>\")&\"<\/s><\/t>\",\"\/\/s\"),\nVALUE(SUBSTITUTE(filtered_data,\"\"\"\",\"\")))\n<\/pre>\n\n\n\n
The formula’s WEBSERVICE <\/a>Function<\/strong> sends a web request to the EODHD’s End-of-Day data API<\/a> and retrieves the data from it. Where $B$2 is an API token, $C$3 is a ticker symbol,$C$4 and $C$5 is a date range, and C11 is a filter (date, open, high, low, close). LET <\/a>Function<\/strong> is used to define variables within a formula. raw_data<\/strong> stores the raw data fetched from the web service. filtered_data<\/strong> uses FILTERXML<\/a> <\/strong>to parse XML content and returns the data based on an XPath query. VALUE <\/a>Function<\/strong> converts text into the number format. The SUBSTITUTE <\/a>Function<\/strong> is used to clean up the format before converting it into numerical values.<\/p>\n\n\n\n
Defining the Technical Analysis Strategy <\/h2>\n\n\n\n
Next, define the technical analysis strategy you wish to backtest. For this example, we will use three commonly used technical analysis strategies: the Simple Moving Average (SMA) crossover, the Relative Strength Index (RSI) Strategy, and the Moving Average Convergence Divergence (MACD) Crossover Strategy.<\/p>\n\n\n\n
Simple moving average (SMA) crossover strategy<\/h3>\n\n\n\n
SMA crossover<\/a> strategy is defined in the following way:<\/p>\n\n\n\n