Backtesting is crucial for evaluating trading strategies. It allows you to assess their potential profitability and risks without risking capital. It helps refine strategies by identifying flaws and optimising performance metrics like win rates and drawdowns. You don’t need to be a seasoned developer to backtest; basic Excel skills are sufficient.
Excel provides a straightforward platform for simulating trades using historical data, making it accessible to anyone interested in trading strategy development. This simplicity allows traders to test ideas and make informed decisions regardless of their programming background.
What should you expect from this article?
This article covers backtesting with Excel using EODHD (a leading financial data provider) and creating a dashboard for strategy evaluation.
- Using EODHD Excel Add-On: Download and install EODHD to access historical stock data directly in Excel.
- Creating Formulas: Use Excel functions to simulate trades based on strategy rules.
- Creating a Dashboard: Utilize charts and pivot tables to visualize performance metrics like returns and drawdowns.
EODHD Excel Add-On
After installing the Add-On from the EODHD website, you will be able to see a new ribbon in your Excel as below:
For this article, we will just use the functionality to get historical prices of Apple Inc. (AAPL), but as you can see, the EODHD Excel Add-on can support much, much more.
By selecting the “Get historical data”, you will be prompted with a new window.
Just insert the stock you are interested in (in our case, AAPL.US), select the period, and press “Load.” You should have a new spreadsheet with all the daily candles for the period you requested. Rename this spreadsheet as AAPL.
Implementing Our Strategy
The strategy we are going to backtest is straightforward. The scope of the article is not to do some magic and find an edge in a complicated strategy. The scope is to give you a new perspective on using Excel for backtesting.
So, I will use a beginner’s strategy with two simple moving averages, a slow and a fast one. Depending on which is higher than the other, we will be invested long or short.
For nebies in trading the logic of this strategy is to use momentum. If the fast MA is higher than the Slow MA, practically this means that the stock is moving up faster than it was moving before. So we want to be long in this case. The opposite means that we should go short.
Now, create a new spreadsheet called “Dashboard” and add 2 parameters: the periods for the slow MA in cell C2 (add 21) and the period for the fast MA in cell C3 (add 9). Also add to C4 the initial capital of your backtest.
Now go to the spreadsheet where you downloaded the prices using EODHD and add the following columns and the respective formulas.
Slow — column L
I will calculate the slow MA with the formula below. This formula gets the period parameters from the dashboard spreadsheet and calculates the average. You should add it to cell L2 and copy-paste it all the way down.
=IFERROR(AVERAGE(OFFSET(J2, -1 * (Dashboard!$D$5-1), 0, Dashboard!$D$5, 1)),0)
Fast — column M
Will calculate the fast MA with the below formula. The logic is the same as the slow. You should add it to cell M2.
=IFERROR(AVERAGE(OFFSET(J2, -1 * (Dashboard!$D$6-1), 0, Dashboard!$D$6, 1)),0)
Direction-column N
We will calculate the direction. The values should be 1 for long and -1 for short. We will compare slow and fast MAs and decide whether to invest long or short. Be careful! You should add the formula below to cell N3, comparing the MAs from line 2. That is because you compare the closing prices MA, meaning you will know the direction after the previous candle was closed. Also we will check if some MA is 0 (means that still is not calculated) so the direction will be 0 (not invested for this candle)
=IF(OR(L2=0,M2=0),0,IF(M2>L2,1,-1))
Pct_Change- column O
That is the column where we will calculate the percentage change in the stock’s price from the previous candle. You should add the formula below in cell O3.
=(J3-J2)/J2
Earning-column P
That is where the action takes place. We will multiply the direction with the pct_change. The logic is pretty simple. If the sign (+/-) of the direction and the pct_change are the same you have a positive earning — if the signs are opposite (your direction was different than what actually happened) then you have a negative earning. Add the below formula to P3.
=N3*O3
Equity-column Q
This column will hold the equity of your backtest. After closing each candle, you will calculate your equity based on your previous equity multiplied by your candles’ earnings. That is why initially, you are going to put in cell Q2 as the previous balance the initial capital from the dashboard, and in cell Q3 onwards, the equity from the previous candle.
=> Cell Q2
=IF(ISNUMBER(P2),Dashboard!$C$4*(1+P2),Dashboard!$C$4)
=> Cell Q3
=IF(ISNUMBER(P3),Q2*(1+P3),Q2)
The results
Besides scrolling down all the way in your Excel to see the final outcome, it would be better to have the result in your dashboard. So, let’s add the final return to the dashboard spreadsheet by adding the below formula to cell Dashboard!C5:
=INDEX(AAPL!Q:Q,MATCH(2,1/(AAPL!Q:Q<>"")),0)
Using the above formula, we configure this cell to get the last available value of column Q (Equity), which is also our final result.
Compare to Buy and Hold
While the strategy is profitable, it is not even near to the buy and hold. So, let’s try including this information on our dashboard.
First, we should add a new column to the prices that will keep the close price (column R) normalised based on the initial capital we configured. So, at cell R2, we are going to add the initial capital, and from then on, we will calculate based on the stock’s change (like being extended for the full-time)
=> Cell R2
=Dashboard!C4
=> Sell R3
=R2*(1+O3)
Now go back to the dashboard spreadsheet and add the information there at cell BnH.
=INDEX(AAPL!R:R,MATCH(2,1/(AAPL!R:R<>"")),0)
As you can see, the buy and hold almost tripled the price, while our strategy nearly made a 12%. But as I said, this is not an article to provide a successful plan but to give you a perspective on using Excel to backtest.
Additionally, acquiring basic Excel formatting skills falls outside the scope of this article, so without getting into specifics, explore how much more engaging the dashboard can become.
If you get stuck somewhere and the formulas do not work for you, I have a copy of the Excel for you to download at this link
What to do next
The sky is the limit. What you can try out can be (but is not limited to):
- Add logic where you will be out of the market — have direction cells as 0 (zero) when you do not want to be invested, and the rest of the logic will work the same
- Add commissions— Let’s assume you are using leverage. Add a parameter with the percentage and reduce the final amount based on the balance in the sale of the equity.
- Add new parameters or even technical indicators
Conclusions
Backtesting with Excel is straightforward and accessible, using EODHD for historical data. You don’t have to be an expert in programming; Excel allows you to simulate your strategy and visualise its performance. This enables trades based on data and not a gut feeling!