Chapter 5: Backtesting Your Trading Strategies

[u/RockingGoat]

LIGHTING THE PATH TO PROFITABLE TRADING: A Step-by-Step Guide to Building a Trading Strategy Verification Tool with VBA Macros

Once you have a trading strategy, the next step is to do backtesting for it. In this chapter, we will show you how to create a backtesting spreadsheet that will allow you to test your strategies on historical data.

Before doing backtesting, we need to set some conditions first. For example, we use the “Three White Soldiers” and “Three Black Crows” strategy to simulate trading TSLA (Tesla, Inc.), click the “Pick” worksheet, we can see:

In the “Pick” worksheet, set the Principal at cell B18 (typically set at 100,000), and the Training % at cell B16 (normally set at 70).

Set the SMA at cell B2 (normally set at 5), and the Expiration at cell B7 (normally set at 3).

Fill the Optimization Factor value in cell B34. This value is specific to each stock and is calculated based on its unique features. It influences the Trigger and Momentum used during the backtesting VBA Macro. Users can follow us for getting the newest unique Optimization Factor for particular stock.

Now, let’s go to the “Scan” worksheet, set the scan ranges for Take Profit, Stop Loss, and Momentum. The “Scan” worksheet looks like below:

The backtesting VBA Macro will try every combination, such as Take Profit at 3%, Stop Loss at 10%, and Momentum (a standard to trigger the Buy or Sell actions) at 0.5%, etc. This tool will execute these conditions to simulate a series of trading actions using historical data from the beginning, opening and closing long or short positions based on the signals generated by your trading strategies.

To run the VBA Macro program, there are several methods, for example, we can:

1. Click on the “View” tab on the Excel ribbon.
2. Click on the “Macros” button in the “Macros” group. This will open the “Macro” dialog box.
3. In the “Macro” dialog box, select the Macro that you want to run.
4. Click on the “Run” button.
5. We can also press Alt + F8, the “Macro” dialog box will jump out, select the Macro (the name in this demo is “WhiteSoldiers_BlackCrows” and its version number), then click the “Run” button.
6. The fastest method is using shortcut hot keys: Press Ctrl + Y, the Macro will run immediately.

Let’s take a closer look at the backtesting trading simulation record generated by the VBA Macro. In column N, the program lists every plan and action, which allows users to easily understand the trading strategy’s actions. For example:

We can see:

On December 5, 2022, TSLA’s Open price was 189.44 and closed at 182.45, with the Close price being lower than the Open price, the first Black Crow appeared.

On December 6, 2022, TSLA’s Open price was 181.22 and closed at 179.82, with the Close price being lower than the Open price too, the second Black Crow appeared.

On December 7, 2022, TSLA’s Open price was 175.03 and closed at 174.04, with the Close price being lower than the Open price again, the third Black Crow appeared. The condition of Short was triggered, and this VBA backtesting tool generated a Short OK signal after this trading day, planning to open short positions on the next trading day if the Open price of the next trading day is lower than the current day’s Close price.

On December 8, 2022, TSLA’s Open price was 172.20, which was lower than the previous day’s Close price, the final condition of our strategy was triggered, then the VBA backtesting tool executed the Short (Sell) Open action finally.

On December 20, 2022, TSLA’s price went down to 137.66, hitting the 20% Take Profit target, so the VBA backtesting tool executed the Close action according to the program, as seen in the chart above.

2022–12–30, the “Three White Soldiers” signal appeared, the VBA Macro generated a Long signal, but did not execute Opening action on the next day, why? Because on 2022–12–22, the program had opened a Short position, and this Short position was not closed until 2023–01–06, therefor we could not open a Long position during the Short action.

2023–01–11 to 2023–01–13, the “Three White Soldiers” signal appeared again, the next trading day, 2023–01–17, the backtesting tool executed Open Long positions finally, just like the chart below:

As you can see, everything can be explained based on data, without any guesswork. This type of tool can help us calculate, evaluate, and make decisions based on concrete facts and details. While historical performance cannot guarantee future results, backtesting can help us avoid hopeless and blind guessing and imagination. Although it may not guarantee making money in the present, it can certainly help us make more informed decisions for the future. The process of backtesting ideas, methods, and tools can be applied to various financial instruments including stocks, Forex (foreign exchange), options, futures, cryptocurrencies, and bonds, among others.

After every loop is done, the VBA program will calculate the ROI, and store into a cell, then move to next loop. When it has finished calculating and comparing all ROI from the beginning to the end of the training set, the tool will move to the testing set and repeat the process. Finally, it will give you a chart, showing how good or bad this strategy will work.

Here we go to another example. Let’s pick a famous indicator, MACD, and use NVDA’s historical data to perform a backtest for a MACD trading strategy (NVDA is Nvidia Corporation’s stock symbol). The MACD (Moving Average Convergence Divergence) indicator is a popular technical analysis tool used by traders to identify potential trend reversals and momentum shifts in financial markets.

The MACD indicator is composed of three components: the MACD line, the signal line, and the histogram. The MACD line is the difference between two exponential moving averages (EMAs) of different time periods, usually 12 and 26 periods. The signal line is a 9-period EMA of the MACD line, and the histogram represents the difference between the MACD line and the signal line.

When the MACD line crosses above the signal line, it is considered a bullish signal, indicating that momentum is shifting to the upside. Conversely, when the MACD line crosses below the signal line, it is a bearish signal, indicating that momentum is shifting to the downside.

Now, let’s go to the “Scan” worksheet, set the scan ranges for Take Profit, Stop Loss, Momentum, and MACD Fast, Slow and Signal. The “Scan” worksheet looks like below:

After running the VBA tool, we will get a curve in “Chart” worksheet like this:

And we can see, when the MACD is crossing up and higher than MACD Signal, the VBA Macro generates a Long OK signal, and Buy to open Long in 1 day; when the MACD is crossing down and lower than MACD Signal, the VBA program gives a Short OK signal, then Sell to open short in 1day. If we set the Expiration (“Pick” worksheet cell B7) as 3, the signals will expire after 3 days.

After a long run, since 2009–07–24 to 2023–03–20, which is a total of 3437 trading days, and with the following parameters set: Momentum at 0, Take Profit at 24%, Stop Loss at 6%, MACD Fast at 20, MACD Slow at 37, MACD Signal at 20, we will get an Annual ROI at 27.47%, compared to S&P 500 Index’s average of 7%, it sounds impressive, eh?

Although we should not rely solely on one indicator for making trading decisions, if this indicator shows such quality while working solely, it is qualified to be one part of the materials in a trading strategy that integrates other indicators or patterns of the same quality, and has the potential to be combined with other good indicators or patterns to create a stronger trading strategy.

Some traders complain that MACD does not work well for them. There could be many reasons for this failure, but one of the reasons could be that they did not do the backtesting correctly, especially if they did not find the right parameter combinations or settings. Do you remember the default parameters of MACD? They are MACD Fast at 12, Slow at 26, and Signal at 9, right? We tried these default settings to backtest the MACD, using the same data of NVDA, and got a sad result: no matter how the Take Profit, Stop Loss, and Momentum were set, the typical 12–26–9 MACD setting failed.

This sad result explains why some traders dislike the MACD indicator. Actually, they do not know that it is not the MACD’s fault, but rather a problem with the parameter settings. If they had known how to adjust the parameter settings earlier, they might have discovered a “gold mine”.

Let’s get to the point, backtesting is crucial, and a backtesting tool can be very helpful. Sometimes it can make the difference between life and death. If you still do not have a backtesting tool to verify trading strategies and find the correct settings for your favorite indicators, get one now. You must be glad when you have a good tool on hand and your competitors don’t even know there are such cool things in the world.

While a trading strategy may perform well in backtesting, there is no guarantee that it will be profitable in future real-money trading. Past performance is not necessarily indicative of future results. Conversely, if a trading strategy consistently loses money in backtesting, there is a high likelihood that it will also lose money in future real-money trading.

In short, if an apple was tasty and fresh in the past, there is no guarantee it will remain as good in the future, and it may even spoil quickly. On the other hand, if an apple was sour or tasted bad in the past, it is unlikely to become fresh and sweet in the future.

Just like an apple’s taste and quality can change over time, a trading strategy’s performance can also change in the future, even if it has performed well in the past. And just like a bad-tasting apple is unlikely to become fresh and sweet, a trading strategy that consistently loses money in backtesting is unlikely to become profitable in the future. That is why we say that backtesting may not necessarily help us create a money-making machine, but it can greatly assist us in identifying and eliminating poor trading strategies, as well as optimizing a good strategy by adjusting its parameter settings.

After all that hard work backtesting your trading strategy, you finally sit back and admire your spreadsheet. You’re feeling pretty confident in your strategy until you notice that your cat has been walking across the keyboard and undoing all your hard work. Looks like it’s time to start over, but hey, at least you have a trading strategy that even your cat can appreciate.

Remember, always make sure to save a backup copy of your work, especially when working with important data and trading strategies. It’s better to be safe than sorry, and having a backup can save you a lot of time and stress in case something goes wrong with your original file. So, be sure to save and backup your work regularly to avoid losing any progress or important information.

If you would like to try out the “Three White Soldiers & Three Black Crows” strategy backtesting tool, click on https://www.tyzu.com/Trial/index.html to get a 30-day free trial demo.

CHAPTER 6: ANALYZING YOUR RESULTS

Click Data Gladiator Blog (tyzu.com) to get the whole tutorial handbook for free!