CHAPTER 5: BACKTESTING YOUR TRADING STRATEGIES

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.

(If you would like to try out the “Three White Soldiers & Three Black Crows” strategy backtesting tool, click on Free Trial to get a 30-day free trial demo.)

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 below.

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 above.

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.

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.

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.