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:
Click on the "View" tab on
the Excel ribbon.
Click on the "Macros" button
in the "Macros" group. This will open the "Macro" dialog box.
In the "Macro" dialog box,
select the Macro that you want to run.
Click on the "Run" button.
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
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
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
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.
LIGHTING THE PATH TO PROFITABLE TRADING
(the whole tutorial handbook pdf Free Download)
A Step-by-Step Guide to Building a Trading
Strategy Verification Tool with VBA Macros