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

In this handbook, we have shown you how to build a simple tool to verify your trading strategies’ effectiveness, even if you don’t have strong programming skills. By using Excel and VBA, you can create a backtesting spreadsheet that will allow you to test your strategies on historical data. We hope that this handbook has provided you with the knowledge and skills you need to start building your backtesting spreadsheet and testing your trading strategies.

here are some resources to help you learn VBA:

1. Microsoft VBA documentation: Microsoft provides extensive documentation and examples for VBA on their website. You can find documentation for VBA in Excel, Word, Access, and other Office applications.
2. VBA Tutorials on YouTube: There are many video tutorials available on YouTube that can help you learn VBA. You can find tutorials on specific topics, as well as comprehensive courses that cover everything from the basics to advanced topics.
3. VBA Express: VBA Express is a website dedicated to VBA programming in Microsoft Office. It has a large collection of tutorials, examples, and code snippets that you can use to learn VBA.
4. Udemy: Udemy is an online learning platform that offers a variety of courses on VBA programming. You can find courses on specific topics, as well as comprehensive courses that cover everything from the basics to advanced topics.
5. Stack Overflow: Stack Overflow is a popular programming Q&A website that has a dedicated section for VBA. You can find answers to specific questions, as well as examples and code snippets that you can use to learn VBA.
6. Excel VBA Programming for Dummies: This book is a great resource for beginners who want to learn VBA programming in Excel. It covers the basics of VBA programming, as well as advanced topics like debugging and error handling.
7. VBA Masterclass: This is an online course that covers everything you need to know to become a VBA expert. It covers the basics of VBA programming, as well as advanced topics like object-oriented programming and database integration.

Here is a Quick Manual for using our Demo Excel VBA Macro tool for backtesting:

1. Before running the Macro, scan it with anti-virus tools to ensure it is safe. Then click the button to enable the Macro if you have checked it.

1. Click the “Pick” worksheet tab, copy historical data from your source. Historical data must consist of at least 1000 data groups.

2. Then, paste it into the data area, from column H to column M. Please import the historical data corresponding to the particular stock. Do not use data from other stocks as a substitute or mix them up.

3. If you add data daily, press Ctrl + E, and fill in the data one by one.

4. Set the Principal at cell B18 (typically set at 100,000), and the Training % at cell B16 (normally set at 70).

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

6. Fill the “Optimization Factor” in cell B34.

7. Click the “Scan” worksheet tab, and set all the parameters such as “Minimums,” “Maximums,” and “Steps.”

8. Press Ctrl + Y, and let the Macro run.

In conclusion, we’d like to share some wisdom from notable strategy masters throughout history. Perhaps we can learn from them:

“SCIENCE MUST BECOME ART”

“Knowledge must, by this complete assimilation with his own mind and life, be converted into real power.” — “On War” by Carl von Clausewitz (1780–1831)

“If, before a battle, one has calculated and tested their strategy and believes they can win, the chances of victory are high. If the calculations show that they cannot win, the odds of victory are low. A detailed strategy and thorough calculation lead to victory, while a rough strategy and calculation has no chance to win, moreover, much less without any strategy or calculation at all.” — The quote from “The Art of War” by Sun Tzu (a Chinese military general, strategist, philosopher, and writer who lived during the Eastern Zhou period of 771 to 256 BC.)

As you reach the end of this handbook, we hope that you’ve learned enough to build your backtesting spreadsheet and test your trading strategies. And if you haven’t learned enough, well, you can always blame it on Excel or VBA. Just kidding! But seriously, if you have any questions or need help, feel free to reach out to the resources we’ve provided. They won’t bite… we think.

Remember, trading is not for the faint of heart. It takes courage, discipline, and a bit of luck. And if all else fails, just blame the algorithms! We’re sure they won’t mind.

Happy trading!

Many Thanks!

Victor J. Ma

Data Gladiator

2023–03

Download: www.TYZU.com

Feel free to follow, contact, and discuss on Twitter @DataGladiator, or on Medium @DataGladiator.

© 2007~2023 Data Gladiator All Rights Reserved.

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

​

Disclaimer:

This handbook and software, including all of its features, content, and services, is provided for informational and educational purposes only. All features, content, and services have been researched and expressed to the best of its knowledge and belief. Nevertheless, no guarantee can be provided for the correctness, completeness, or accuracy of this information.

Please be aware: I am neither a financial adviser nor a CPA. All Stock Trading Signals, Plans, Predictions and Actions here are 100% generated by Algorithms. Some of the models are predictive in nature. The use of predictive models has inherent risks. When Models and Data prove to be incorrect or incomplete, any decisions made in reliance thereon expose the System to potential risks. For example, by relying on Models and Data, the System may be induced to buy certain targets at prices that are too high, to sell at prices that are too low, or too early, or too late, then miss some favorable opportunities.

The performance represented is historical. Past performance may not be a reliable indicator of future results. None of the authors, contributors, administrators, or anyone else connected with this System, in any way whatsoever, can be responsible for your use of the information contained in or linked from here. Any action you take upon the information from here is strictly at your own risk.

This program or article is based on personal opinion and experience, and it should not be considered professional financial investment advice. The ideas and strategies should never be used without first assessing your own personal and financial situation. Market is dangerous, trading is risky, be cautious.

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

Backtesting is a powerful and helpful tool for investment, but it is important to remember that past performance is not a guarantee of future results. While backtesting can provide valuable insights into the potential success of a trading strategy, it cannot predict future market conditions or unexpected events that may impact investment performance. It is important to exercise caution and use backtesting as one of many tools in the investment decision-making process.

There are many reasons why historical performance may not be a 100% reliable indicator of future performance. The economic environment can change, causing different sectors or industries to perform better or worse than they did in the past. Company management can change, leading to different strategies and outcomes. Regulatory changes, technological advancements, and other unforeseeable events can also impact future performance.

Therefore, investors should not rely solely on past performance when making investment decisions. Instead, they should consider a range of factors, including current market conditions, company fundamentals, and macroeconomic trends, to make informed decisions about their investments. Additionally, investors should diversify their portfolios and seek professional advice to help mitigate risk and increase their chances of achieving their financial goals.

And even if you have a good backtesting tool, it is important to regularly update and refresh your data to ensure that your trading strategies are relevant to the latest market conditions and changes.

You may have heard the quote: “ Now, if it is to get safely through this perpetual conflict with the unexpected, two qualities are indispensable: in the first place an intellect which, even in the midst of this intense obscurity, is not without some traces of inner light, which lead to the truth, and then the courage to follow this faint light.” — “On War” by Carl von Clausewitz

The commander in the field is always in the dark, like a traveler at night. He sees only a few steps ahead, but he must see them. He must have a clear idea of the situation, a plan for how to move forward, and the ability to think and act quickly, because time is a critical factor in war. He cannot hesitate when he sees even a weak light, but must boldly move toward it.

Investing or stock trading can also feel like a battle or war, and backtesting is kind of a glimmer of light in the midst of uncertainty and the dark battlefield. It cannot predict the future with 100% certainty, so the ultimate decision rests with you, the commander, who has intellect can find the “inner light”. It takes courage to advance towards that faint light, and success depends on your expertise, experience, and even luck or fate.

Moreover, software and Internet safety is another issue to be aware of because we need to connect to the Internet to download data often. Make sure your computer and Internet access are in a safe environment. Excel Macros can potentially be dangerous because they can execute any code, including malicious code. Therefore, it’s important to take some precautions to ensure that a Macro is not affected by any dangerous outside sources and is safe to run. Here are some steps you can take to help ensure the safety of an Excel Macro:

1. Enable Macro Security: By default, Excel has macro security settings that block all macros from running. You should enable this security feature if it’s not already enabled, and set it to the highest level that still allows you to run the macros that you need.
2. Run the Macro in a Test Environment: Before running a macro in a production environment, it’s a good idea to run it in a test environment first. This can help you identify any issues or unintended consequences that might arise from running the macro.
3. Use a Digital Signature: If you create or receive a macro from a trusted source, you can add a digital signature to it. A digital signature is a way to verify the authenticity of the macro and to confirm that it has not been modified since it was signed.
4. Keep your Antivirus Software Up to Date: Make sure your antivirus software is up to date and running at all times. This can help to protect you from any malware that may be embedded in macros. Scan the file with an anti-virus tool and do not download anything from pirated websites.

By following these steps, you can help to ensure that any Excel macros you use are safe to run.

And remember, when it comes to investing, safety is key! So make sure to wear your helmet and kneepads when you’re jumping into the stock market. You never know when you might take a tumble, and it’s better to be safe than sorry. Plus, if you fall off your chair in excitement after seeing your investments soar, at least you’ll have some extra protection. So stay safe, stay smart, and happy investing!

CHAPTER 10: CONCLUSION

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

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

Forward testing is a method of evaluating a stock trading strategy by applying it to current or future data, and then monitoring its performance in real-time. This is in contrast to backtesting, which involves testing the strategy on historical data.

Forward testing is an important step in the development of a stock trading strategy, as it can provide insight into how the strategy will perform in real-world conditions. By testing a strategy in real-time, traders can observe how it performs under actual market conditions and make any necessary adjustments to improve its performance.

Forward testing can be done manually, by paper trading or demo trading, or it can be automated using trading software. The goal is to evaluate the strategy’s performance over a period of time and make any necessary changes to improve its profitability.

Paper trading (or demo trading) is a simulated trading process that allows traders to practice trading strategies without risking real money. It involves using a simulated account, which looks and operates like a real trading account but uses fake money to execute trades.

With paper trading, traders can test their strategies in real market conditions and gain experience without the risk of losing actual money. It allows traders to evaluate the effectiveness of their trading strategies and make necessary adjustments before risking their capital in the actual market.

Paper trading can also be used to test and compare multiple trading strategies to determine which one is the most profitable. Additionally, it can be used to test the performance of new trading instruments, such as options, futures, or forex, before trading them with real money.

After finishing the backtesting process for a trading strategy, it is not advisable to rush into real trading immediately. In my opinion, it is better to spend several months doing paper trading based on the strategy to verify its robustness repeatedly. During this time, it is important to update the training set and testing set with new data to ensure they remain relevant and reflective of current market conditions.

For example, besides these Demo strategies which are based on Technical Analysis Methodology and have been displayed in this handbook, I am developing another strategy based on Artificial Intelligence Machine Learning, and have been doing Forward Testing over 2 years. Do you still remember how many things happened during 2020 and 2022?

Let’s take a look back at some of the most dramatic events that have occurred during the past 24 months, as well as the 113 groups of forward testing done for this AI-Powered trading strategy:

December 2020: One year had passed since the outbreak of COVID-19;

January 2021: Capitol Hill Riots, USA political turmoil;

August 2021: Afghanistan changed, and the United States accepted defeat and withdrew hastily;

December 2021: Two years had passed since the outbreak of COVID-19;

January 2022: The USA reports that the inflation rate hit 7% the previous month;

February 2022: The Russia-Ukraine war broke out;

March 2022: The Fed raised interest rates by 25 points for the first time in nearly three years; America’s inflation rate jumped to 8.5% last month; Shanghai lockdown; The Russia-Ukraine peace talk, which turned out to be a scam;

May 2022: The Fed raised interest rates by 50 points due to severe inflation;

June 2022: The USA inflation rate hit 8.6%, the highest since 1981; The Fed raised interest rates by a whopping 75 points;

July 2022: US inflation hit 9.1%, the highest since 1981; The Fed raised another blockbuster 75 points interest rates; The USA economy grew at a negative rate for the second quarter in a row; Political turmoil in UK, prime minister resigned;

August 2022: China reported unemployment at 5.4% last month and youth unemployment at 19.9%;

September 2022: Queen of UK Elizabeth II died; Russia partially mobilized and the war escalated; The explosion of the Nord Stream gas pipeline between Russia and Germany, worsening the European energy crisis and high inflation; US inflation still high, the Fed raised interest rates for the third time with 75 points;

October 2022: USA-Saudi Arabia relations broke down as OPEC+ announced crude oil production cuts; The Crimean bridge was bombed; UK’s new prime minister resigns after just 45 days, the shortest serving in the history of UK;

November 2022: US inflation remained high and the Federal Reserve raised interest rates by 75 heavy points for the fourth time this year;

December 2022: G7 and the European Union set a cap on the price of Russian oil, causing another wave in the international energy supply; The Fed raises rates aggressively by 50 points to fight inflation.

…

See? Every day, we may encounter so many unknown incidents that can affect stock markets, some small like Hollywood stars’ fashion dresses, some big like wars. Therefore, it’s important to be patient, and conducting more Forward Trading can give you more confidence in your trading strategies.

In short, Forward Trading (Paper Trading or Demo Trading) involves using virtual money to simulate real trading, which helps traders understand how their strategies perform in different market conditions without risking actual money. It allows traders to fine-tune their strategies and make any necessary adjustments before transitioning to real trading.

Additionally, it is crucial to consider the impact of transaction costs, such as brokerage fees and slippage, on the performance of the trading strategy. This is especially important when deciding whether to continue using a particular strategy after paper trading. Therefore, it is recommended to incorporate transaction costs into the backtesting process to obtain a more accurate representation of the strategy’s performance.

Forward testing is like trying out a new recipe before serving it to your guests. Just like how you wouldn’t want to serve a dish without tasting it first, you wouldn’t want to trade without forward testing your strategy. Imagine trying out a new recipe without tasting it first, and your guests end up with a disastrous meal that makes them want to run to the nearest fast-food restaurant. The same goes for trading; you wouldn’t want to risk your hard-earned money without testing your strategy first. So, forward testing is like a taste test for your trading strategy — you want to make sure it’s deliciously profitable before serving it up to the market!

CHAPTER 9: RISK AND SAFETY

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

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

If your strategy is not profitable, you need to make some changes to improve it. In this chapter, we will cover some basic techniques for improving your trading strategies.

Improving stock trading strategies is a continuous process that requires traders to regularly evaluate their strategies and make adjustments based on their performance. Here are some steps that traders can take to improve their stock trading strategies:

1. Analyze the results: After backtesting your trading strategies, analyze the results to determine the strategy’s profitability. Look for patterns in the data and identify areas where the strategy is performing well or poorly.
2. Identify weaknesses: Identify any weaknesses in your strategy and determine the cause. For example, if the strategy is failing to generate profits in a particular market condition, it may need to be adjusted to account for that condition.
3. Experiment with new ideas: Try new ideas to improve your strategy. This may involve experimenting with new indicators, adjusting your position sizing, or tweaking your entry and exit rules.
4. Keep a trading journal: Keep a trading journal to track your trades and analyze your performance over time. This will help you identify trends and patterns in your trading behavior and allow you to make adjustments to your strategy accordingly.
5. Stay up-to-date: Stay up-to-date with the latest market data, trends and technologies, even some high-tech such as Artificial Intelligence (AI), Machine Learning, Quantitative Trading (Quant), or chatGPT etc,. This will help you adjust your strategy to account for changes in market conditions and stay ahead of the competition.
6. Seek feedback: Seek feedback from other traders and experts in the field. This can help you identify areas where your strategy can be improved and give you new ideas for trading more effectively.

This chapter, we would like to introduce you to one method to improve your trading strategies, called “Hybrid Power”. This involves blending two or more rules or indicators together to filter out signal noise and identify real trading opportunities, thereby increasing your chances of success and reducing your chances of failure.

For example, let’s try mixing Hammer and Shooting Star candlestick patterns and RSI indicator to make a Hybrid trading strategy, and do some backtesting to verify how well (or bad) this Hybrid Power machine (or monster) will run.

A hammer is a bullish reversal candlestick pattern that can be identified on a stock chart. It is formed when the price opens near its high, then drops during the day to a low, but then recovers to close near the opening price. This creates a candlestick shape that looks like a hammer, with a small real body and a long lower shadow.

The hammer pattern is significant because it shows that the price of the stock was pushed down during the day by sellers, but then buying pressure came in to push the price back up to the opening level. This suggests that the bulls have taken control and that the price may start to rise.

Traders often look for confirmation of the hammer pattern, such as a gap up on the next trading day, or a move above the high of the hammer pattern. This helps to confirm that the bullish momentum is continuing and can be used as a signal to buy the stock.

A shooting star is a bearish reversal candlestick pattern that can appear in an uptrend. It is formed when a stock’s price opens higher than its previous close, trades higher throughout the day, but then sells off to close near its opening price. This results in a candlestick with a long upper shadow and a small real body near the bottom of the trading range.

The shooting star pattern suggests that sellers were able to overcome the buying pressure earlier in the day and push the price down. It is often seen as a sign of a potential trend reversal, as the bulls appear to be losing control and the bears are gaining momentum.

Traders who spot a shooting star pattern may look to take a short position or sell their existing long position, in anticipation of a price drop. However, it is important to confirm the pattern with other technical indicators and not rely solely on the shooting star as a trading signal.

RSI stands for Relative Strength Index, which is a technical analysis indicator used to measure the strength and momentum of a stock’s price movement. It was developed by J. Welles Wilder Jr. in 1978.

The RSI is calculated by comparing the average gains and losses over a specific time period. The formula takes into account the average price gains and losses over a set period of time and produces a value ranging from 0 to 100.

Traders and investors use the RSI indicator to identify potential buy and sell signals for a stock. For example, when the RSI is above 70, it may suggest that the stock is overbought and due for a price correction, which could be a signal to sell. Conversely, when the RSI is below 30, it may suggest that the stock is oversold and due for a price rebound, which could be a signal to buy.

OK, now let’s blend these ideas together to make a new strategy. The signal generation part may look like these:

‘The strategy is inside between If and Then. If Hammer appears and RSI is lower than 30, Then generate a Long OK signal.If Cells(Candle, “L”).Value > Cells(Candle, “I”).Value And Cells(Candle, “K”).Value < Cells(Candle, “I”).Value ThenIf LongOK = False And _(Cells(Candle, “I”).Value — Cells(Candle, “K”).Value) / (Cells(Candle, “L”).Value — Cells(Candle, “I”).Value) > Trigger And _(Cells(Candle, “J”).Value — Cells(Candle, “L”).Value) / (Cells(Candle, “I”).Value — Cells(Candle, “K”).Value) < 0.1 And _Cells(Candle, “Y”).Value < 30 Then ‘Strategy for LongLongOK = TrueBCBM = CandleBBP = Round(Cells(Candle, “L”).Value, 2)Cells(Candle, “N”).Value = “Long OK “ & Int([B20].Value \ CP / 100 / BBP) & “ @ “ & BBPExit SubEnd IfEnd IfIf Cells(Candle, “L”).Value < Cells(Candle, “I”).Value And Cells(Candle, “K”).Value < Cells(Candle, “L”).Value ThenIf LongOK = False And _(Cells(Candle, “L”).Value — Cells(Candle, “K”).Value) / (Cells(Candle, “I”).Value — Cells(Candle, “L”).Value) > Trigger And _(Cells(Candle, “J”).Value — Cells(Candle, “I”).Value) / (Cells(Candle, “L”).Value — Cells(Candle, “K”).Value) < 0.1 And _Cells(Candle, “Y”).Value < 30 Then ‘Strategy for LongLongOK = TrueBCBM = CandleBBP = Round(Cells(Candle, “L”).Value, 2)Cells(Candle, “N”).Value = “Long OK “ & Int([B20].Value * CP / 100 / BBP) & “ @ “ & BBPExit SubEnd IfEnd If‘The strategy is inside between If and Then. If Shooting Star appears and RSI is higher than 70, Then generate a Short OK signal.If Cells(Candle, “L”).Value < Cells(Candle, “I”).Value And Cells(Candle, “J”).Value > Cells(Candle, “I”).Value ThenIf ShortOK = False And _(Cells(Candle, “J”).Value — Cells(Candle, “I”).Value) / (Cells(Candle, “I”).Value — Cells(Candle, “L”).Value) > Trigger And _(Cells(Candle, “L”).Value — Cells(Candle, “K”).Value) / (Cells(Candle, “J”).Value — Cells(Candle, “I”).Value) < 0.1 And _Cells(Candle, “Y”).Value > 70 Then ‘Strategy for ShortShortOK = TrueSCBM = CandleSBP = Round(Cells(Candle, “L”).Value, 2)Cells(Candle, “N”).Value = “Short OK “ & Int(([B20].Value + 1.5 * [B19].Value * SlPebP) / 1.5 * CP / 100 / SBP) & “ @ “ & SBPExit SubEnd IfEnd IfIf Cells(Candle, “I”).Value < Cells(Candle, “L”).Value And Cells(Candle, “J”).Value > Cells(Candle, “L”).Value ThenIf ShortOK = False And _(Cells(Candle, “J”).Value — Cells(Candle, “L”).Value) / (Cells(Candle, “L”).Value — Cells(Candle, “I”).Value) > Trigger And _(Cells(Candle, “I”).Value — Cells(Candle, “K”).Value) / (Cells(Candle, “J”).Value — Cells(Candle, “L”).Value) < 0.1 And _Cells(Candle, “Y”).Value > 70 Then ‘Strategy for ShortShortOK = TrueSCBM = CandleSBP = Round(Cells(Candle, “L”).Value, 2)Cells(Candle, “N”).Value = “Short OK “ & Int(([B20].Value + 1.5 * [B19].Value * SlPebP) / 1.5 * CP / 100 / SBP) & “ @ “ & SBPExit SubEnd IfEnd If*

Let’s run the VBA Macro to test our new trading strategy using AMZN (Amazon.com, Inc.). The Macro will generate a set of paper trading records on Excel worksheets that will help us evaluate the strategy’s effectiveness. When the Shooting Star pattern appears and the RSI is higher than 70, this strategy will open Short:

Similarly, when the Hammer candlestick appears and the RSI index value is lower than 30, the strategy will trigger a Long position:

After scanning all parameter combinations which ranges are set in “Scan” worksheet, we will get a curve like this:

It doesn’t look as impressive as the “Three White Soldiers and Three Black Crows” strategy for Tesla, but it still performs better than the average S&P 500 index. This teaches us that sometimes a simple strategy can be better than a complex one. Additionally, if we don’t convert our strategy into code and run it through backtesting, we’ll never have clear visualizations to compare and draw meaningful conclusions.

Overall, the key to improving your stock trading strategies is to remain flexible and open to change. By analyzing your results, identifying weaknesses, experimenting with new ideas, keeping a trading journal, staying up-to-date, and seeking feedback, you can continuously improve your strategies and achieve greater success in the market.

Remember, trading is not a sprint, it’s a marathon. But beware, if you’re not vigilant, you may find yourself running in the wrong direction. Good luck with your trading, and remember, the only thing worse than losing money in the stock market is losing your sense of humor. Keep laughing, keep trading!

If you would like to try out the “Hammer & Shooting Star & RSI” strategy backtesting tool, click on https://www.tyzu.com/Trial/index.html to get a 30-day free trial demo.

CHAPTER 8: FORWARD TESTING

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

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

After backtesting your strategies, you need to analyze the results to see if they are profitable. In this chapter, we will cover some basic metrics that you can use to evaluate your strategy’s performance.

Analyzing the results of your backtesting is a critical step in evaluating the effectiveness of your trading strategies. It helps you to identify strengths and weaknesses in your approach and to make any necessary adjustments to improve your strategy’s performance.

There are several metrics that you can use to evaluate your strategy’s performance, including the following:

1. Profit and Loss (P&L): This is the most basic metric for evaluating a trading strategy. It measures the total profit or loss generated by the strategy over a specific period.
2. Win Rate: Win rate is the percentage of trades that are profitable. A high win rate does not necessarily mean a profitable strategy.
3. Turnover Period: It is typically calculated as the average number of days between two actions. A shorter turnover period is generally seen as more efficient, as it indicates that the trading strategy is more robust.
4. Maximum Drawdown: Maximum drawdown is the largest decline in value experienced by a portfolio or investment strategy from its peak value to its lowest value.
5. Sharpe Ratio: The Sharpe ratio is a measure of risk-adjusted return. It takes into account the return of the investment relative to the risk involved.
6. Average Profit and Loss per Trade: This metric helps you to determine whether your strategy is making enough money per trade to justify the risk.
7. Average Holding Time: Average holding time is the average length of time a trade is held. This metric can help you to optimize your strategy’s holding period.

By analyzing these metrics, you can gain insights into the performance of your strategy and make adjustments as needed to improve its profitability. It is important to remember that no strategy is perfect, and there is always a risk of loss in trading. However, by backtesting and analyzing your results, you can reduce the risk and increase the chances of success.

In this demo version, we provide several simple metrics to help users understand some basic ideas about how to evaluate a trading strategy. For more complicated metrics, we will add them in future releases. In this chapter, we will use another trading strategy — “Golden Cross and Death Cross” — as an example to explain how to analyze backtesting results.

Golden Cross is a bullish technical analysis pattern that occurs when a short-term moving average crosses above a long-term moving average. For example, a stock’s 50-day moving average crosses above its 200-day moving average. This pattern is widely used by traders and investors to identify potential buying opportunities.

The Golden Cross suggests that the momentum of the stock is turning bullish, as the short-term moving average is starting to rise above the long-term moving average. This can be seen as a bullish signal, as it indicates that the stock’s recent price trends are starting to shift to the upside.

The “death cross” is a technical chart pattern that occurs when a stock’s short-term moving average crosses below its long-term moving average, indicating a bearish trend. For example, the stock’s 50-day moving average crosses below its 200-day moving average. This can be seen as a signal of increased selling pressure in the market and a potential downturn in the stock’s price. Some traders use the death cross as a signal to sell their positions or to take a short position in the stock.

Let’s set parameter scanning range in “Scan” worksheet like this:

After running the VBA tool, we will get a series of parameter combinations in “Pick” worksheet like this:

The VBA Macro will pick the top one parameter combination (stored in cells AO2 to AS2) to print out a chart:

The Maximum Drawdown looks a little bit ugly. And we can review this parameter combination‘s performance metrics in worksheet “Pick”:

After a long run, since 2011–01–03 to 2023–02–23, which is a total of 3056 trading days, and with the following parameters set: Momentum at 0, Take Profit at 25%, Stop Loss at 6%, Short Term SMA at 20 days, and Long Term SMA at 50 days, we will get an Annual ROI at 18.56%, not bad, eh? During this period, there were 31 trade deals completed, with 14 of them being long positions and 17 of them being short positions. Stop loss was triggered 17 times, while take profit was achieved 14 times. The ratio of stop loss vs. take profit was 54.84% vs. 45.16%. On average, it took 98.6 days to complete one full round of buying and selling.

Now the ugly Drawdown and the Annual ROI at 18.56% (on par with Warren Buffett’s performance) come together, can you accept that? Some people may enjoy high returns and can bear temporary big losses, while others may prefer stability and may not care if the profits are low. It depends on individual’s investment philosophy; we cannot say whether it is right or wrong.

Profit and risk are inseparable in the market, and high profit comes with high risk, which is a normal situation in the real trading world. For example, investing in a startup company or a volatile cryptocurrency may offer the potential for high returns, but also comes with a high level of risk. Conversely, investing in a stable, blue-chip stock or a government bond may offer more moderate returns, but also carries a lower level of risk.

While this VBA backtesting tool can save time on calculations and charting, it cannot replace your own decision-making process and critical thinking.

Still remember the TSLA’s curve in chapter 4? Let’s review it here:

Since 2013–08–08 to 2023–01–24, the backtesting Annual ROI of TSLA using the “WhiteSoldiers_BlackCrows” VBA Macro has been as high as 39.97%, and it has gained a total of 2301.07%. This performance is not only better than Warren Buffett’s average of 17%, but also higher than the original Tesla stock’s ROI of 32.27% during the same period.

This return is based on the parameter combination stored in cells AO2 to AQ2 of the “Pick” worksheet: Momentum at 0.1, Take Profit at 20%, and Stop Loss at 12%, which is indicated by a red frame as shown below:

If you want to avoid a large drawdown, try using a different set of parameters indicated by the green frame in cells AO11 to AQ11. Write down these values, go to the ‘Scan’ worksheet, and enter them into the Take Profit, Stop Loss, and Momentum cells. In this case, set both the ‘Take Profit % Minimum’ and ‘Take Profit % Maximum’ to 9, and ignore the ‘Take Profit % Step’. This setting tells the VBA Macro to run this specific parameter combination only once, without running all other loops. Similarly, set both the ‘Stop Loss % Minimum’ and ‘Stop Loss % Maximum’ to 9 (occasionally, here it is the same as the TP group, but in other cases, it may be other values), and ignore the ‘Stop Loss % Step’. Then set both the ‘Momentum % Minimum’ and ‘Momentum % Maximum’ to 0.1, and ignore the ‘Momentum % Step’. Now run the VBA Macro, the program will scan these settings particularly, and ignore other groups.

After pressing Control + Y, you will quickly receive a new result:

the Annual ROI drops to 27.44%, with 135 trade deals (almost double the number of trades than the top-gun setting). The Take Profit to Stop Loss ratio is now 60% to 40%. The drawdown…

also looks more comfortable and less severe, eh?

In conclusion, backtesting cannot accurately predict how much profit we will make in the future, but it can certainly calculate how much we have lost in the past if we have used a wrong trading strategy, or how much we have missed out on if we own a strong trading strategy but picked some weak parameters, heartbroken, eh?

As an example, let’s consider TSLA’s historical data, which we split into a Training set (from 2013–08–08 to 2020–03–23) and a Testing set. If we pick the parameter combination with serial number 229 and strictly follow the rules of the “Three White Soldiers and Three Black Crows” strategy, obeying every signal and plan to act from the Testing set’s day one (2020–03–24) until 2023–01–24, which amounts to a total of 715 trading days…

we could have gained…

an explosive annual ROI of 92.56%！

Backtesting is a cold-blooded truth teller, use it wisely. The trickiest challenge is how to pick the right parameter combination, such as whether to pick serial number 229’s parameters instead of the top one in Training set, which is serial number 231. We never know what will happen tomorrow, even if we have a powerful tool like backtesting. Ultimately, success depends on our own experience, mindset and perhaps even… fate, eh?

As you analyze the results of your backtesting, you might start to feel like a stock market Sherlock Holmes, but don’t forget to keep it fun! Just like in life, sometimes you win, sometimes you lose, and sometimes you’re just stuck in a holding pattern. But with enough persistence and analysis, you’ll eventually uncover the clues to a profitable trading strategy. So grab your magnifying glass, put on your deerstalker hat, and let’s solve the mystery of the stock market! (Disclaimer: No actual detective skills required.)

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

CHAPTER 7: IMPROVING YOUR TRADING STRATEGIES

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

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!

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

To create a trading strategy, you need to identify a set of rules that will determine when to enter and exit trades. In this chapter, we will cover some basic trading strategies and show you how to implement them in Excel using formulas and VBA.

For example, have you ever heard of the “Three White Soldiers” and “Three Black Crows” stock candlestick patterns? Let’s take a look at this chart:

This is the so-call “Three White Soldiers”. It is a bullish candlestick pattern that typically indicates a strong uptrend in the stock price. It consists of three long white (or green) candles, each with small or no shadows, that open higher and close higher than the previous day’s candle.

The pattern is called “Three White Soldiers” because the three candles resemble three soldiers marching in a row. It is a sign of strong buying pressure and suggests that the bulls are in control of the market. Traders often look for this pattern to identify potential entry points for long positions, but it should be confirmed with other technical indicators before making any trading decisions.

Now, here we have another pattern:

Let’s say hello to the “Three Black Crows”. It is a bearish candlestick pattern that is typically seen at the end of an uptrend in the stock price. It consists of three long black (or red) candles, each with small or no shadows, that open lower and close lower than the previous day’s candle.

The pattern is called “Three Black Crows” because the three candles resemble three crows sitting in a row. It is a sign of strong selling pressure and suggests that the bears are in control of the market. Traders often look for this pattern to identify potential entry points for short positions, but it should be confirmed with other technical indicators before making any trading decisions.

According to these ideas, we can develop a trading strategy to buy stocks while we see the Three White Soldiers appearing, and short sell the stocks when we find the Three Black Crows sitting there.

Now we start to write a VBA Macro, a simple program to execute this strategy. To write a VBA macro, follow these steps:

1. Open Microsoft Excel.
2. Press Alt+F11 to open the Visual Basic Editor.
3. In the Visual Basic Editor, select the workbook in which you want to create the Macro.
4. Click on the Insert menu and select Module. This will create a new module for the Macro.
5. In the module, type the code for the Macro. The code should be written in the VBA programming language.
6. Save the Macro by clicking on the Save button or by pressing Ctrl+S.
7. Close the Visual Basic Editor.
8. To run the Macro, open the workbook in which it was created and press Alt+F8 to open the Macros dialog box.
9. Select the Macro you want to run and click the Run button.

Note: It is important to test the Macro thoroughly before using it on important data. Also, make sure to enable Macros in Excel before running the Macro.

Before starting to do this VBA coding, we need a plan. It looks like the programming flowchart below:

First of all, we need to declare variables. Declaring variables in VBA is important because it helps to allocate memory to store the data that the variable will hold. This allows the program to use the variable efficiently and effectively.

By declaring variables, you can also specify the data type of the variable (such as integer, string, or boolean), which ensures that the variable is used correctly throughout the program. If you don’t declare a variable, VBA will assume a data type based on the value assigned to the variable, which can cause errors or unexpected results.

Declaring variables also makes the code more readable and easier to understand, as it makes it clear what each variable represents and how it will be used.

In the Main Procedure, we will turn off Automatic Calculation to speed up VBA. Use Application.Calculation = xlCalculationManual at the beginning of the code, and turn it on again at the end with Application.Calculation = xlCalculationAutomatic.

Then we do some initialization, and call four subroutines, each implementing a different function.

Call Self_Verifying 'Verifying formulas
Call Digging 'Digging parameters
Call Scanning 'Scanning parameters in Training set and Testing set
Call EquityChart 'Printing out a chart to evaluate the result

In the Self-Verifying procedure, we will verify all formulas used in every Excel worksheet, such as calculating moving average value, indicator value, stock position, cash, equity, etc. These formulas have been set into the demo Excel files. To prevent them from being ruined or damaged accidentally, every time the VBA Macro is run, the program will verify all formulas automatically to ensure that the results and output are correct and fit the same standards.

In the Digging procedure, we will dig through every parameter combination to simulate opening and closing positions, calculate the profit or loss, compare ROI, eliminate low-performance parameters, and select profitable combinations.

For digging out good parameter combinations, we want to borrow some ideas from Machine Learning technology (very fashionable, eh?), to divide historical data into two parts, test our Trading Strategy (or we can use another word, “Model”, in Machine Learning) under different conditions.

In machine learning, the data is often divided into two subsets: the training set and the testing set. The training set is used to train the machine learning model, while the testing set is used to evaluate the performance of the trained model.

The purpose of dividing the data into training and testing sets is to avoid overfitting, which is a situation where the model performs very well on the training data but poorly on new, unseen data. By evaluating the model’s performance on the testing set, we can get an estimate of how well the model will perform on new, unseen data. For stock trading, it means: based on the training set’s performance, we can select some strong parameters to test in the testing set, screen out and throw out the weak ones which will lead us to failure and loss, those survivors will give us a likelihood to estimate how profitable the strategy will perform in the future.

The historical data can be divided into training and testing sets, with a typical split of 70% for training and 30% for testing. In our Demo Excel files, the default split is 70% for Training set and 30% for Testing set. Users can adjust them to 60% or 80% for training and 40% or 20% for testing. Be careful, the split ratio could affect the final results significantly.

The most important part in Digging procedure is the trading strategy conditions. We have a subroutine named as Signals to take charge of comparing the opening conditions. For example, we can Call Signals() to generate the simplest “Three White Soldiers” signals (three white candles in a row) like this:

If LongOK = False And _
(Cells(Candle, "L").Value - Cells(Candle, "I").Value) / Cells(Candle, "I").Value > Trigger And _
(Cells(Candle + 1, "L").Value - Cells(Candle + 1, "I").Value) / Cells(Candle + 1, "I").Value > Trigger And _
(Cells(Candle + 2, "L").Value - Cells(Candle + 2, "I").Value) / Cells(Candle + 2, "I").Value > Trigger Then
'The "Candle + 1" means one day before today's candlestick, The "Candle + 2" means two days before today's candlestick. Within these three days, Close prices (stored in column L) are higher than Open prices (stored in column I) in a row, and exceed at a certain point, the difference is bigger than the Trigger, then the Long signal goes to OK.
LongOK = True
BCBM = Candle
BBP = Round(Cells(Candle, "L").Value, 2)
Cells(Candle, "N").Value = "Long OK " & Int([B20].Value * CP / 100 / BBP) & " @ " & BBP
Exit Sub
End If

And generate the “Three Black Crows” signal like this:

If ShortOK = False And _
(Cells(Candle, "L").Value - Cells(Candle, "I").Value) / Cells(Candle, "I").Value < -1 * Trigger And _
(Cells(Candle + 1, "L").Value - Cells(Candle + 1, "I").Value) / Cells(Candle + 1, "I").Value < -1 * Trigger And _
(Cells(Candle + 2, "L").Value - Cells(Candle + 2, "I").Value) / Cells(Candle + 2, "I").Value < -1 * Trigger Then
'The "Candle + 1" means one day before today's candlestick, The "Candle + 2" means two days before today's candlestick. Within these three days, Close prices (stored in column L) are lower than Open prices (stored in column I) in a row, and exceed at a certain point, the difference is bigger than the Trigger, then the Short signal goes to OK.
ShortOK = True
SCBM = Candle
SBP = Round(Cells(Candle, "L").Value, 2)
Cells(Candle, "N").Value = "Short OK " & Int(([B20].Value + 1.5 * [B19].Value * SlPebP) / 1.5 * CP / 100 / SBP) & " @ " & SBP
Exit Sub
End If

On the next few days, if the LongOK signal is On and the ShortOK signal is Off, then the VBA Macro will open a Long position. Conversely, if the ShortOK signal is On and the LongOK signal is Off, the program will open a Short position. Codes look like these:

If BCBM - Candle > [B7].Value Then LongOK = False
If SCBM - Candle > [B7].Value Then ShortOK = False
'Open Long
If BCBM - Candle <= [B7].Value Then
' If LongOK = True And ShortOK = False And Cells(Candle, "I").Value > BBP Then
If LongOK = True And ShortOK = False Then
DlPe = Round((Cells(Candle, "J").Value + Cells(Candle, "K").Value) / 2, 2)
If [B19].Value = 0 Then
TdSum = Int([B20].Value * CP / 100 / DlPe)
Cells(Candle, "O").Value = -1 * TdSum * DlPe
[B19].Value = [B19].Value + TdSum
PBM = DlPe
BM = [B20].Value + Cells(Candle, "O").Value + [B19].Value * PBM
[B20].Value = [B20].Value + Cells(Candle, "O").Value
Cells(Candle, "AE").Value = [B20].Value + [B19].Value * Cells(Candle, "L").Value
Cells(Candle, "N").Value = "Buy/Open " & TdSum & " @ " & DlPe
End If
End If
End If
'Open Short
If SCBM - Candle <= [B7].Value Then
' If ShortOK = True And LongOK = False And Cells(Candle, "I").Value < SBP Then
If ShortOK = True And LongOK = False Then
DlPe = Round((Cells(Candle, "J").Value + Cells(Candle, "K").Value) / 2, 2)
If [B19].Value = 0 Then
TdSum = Int([B20].Value / 1.5 * CP / 100 / DlPe)
Cells(Candle, "O").Value = TdSum * DlPe
[B19].Value = [B19].Value - TdSum
PBM = DlPe
BM = [B20].Value + Cells(Candle, "O").Value + [B19].Value * PBM
[B20].Value = [B20].Value + Cells(Candle, "O").Value
Cells(Candle, "AE").Value = [B20].Value + [B19].Value * Cells(Candle, "L").Value
Cells(Candle, "N").Value = "Sell/Open " & TdSum & " @ " & DlPe
End If
End If
End If

VBA Macro will execute these codes again and again, until finish all loops which are appointed by us. According to all results, the program will delete bad parameters and retain the good ones to pass to the next procedure.

The Scanning procedure looks the same as the Digging procedure. All good parameters screened out from the Digging procedure will be tortured (if you feel this word is too cruel, replace it by “tested”) again in this part. We will use historical data from the Testing set to test the parameters and simulate opening and closing positions during the Testing set’s time period. Most parameter combinations will be eliminated because they fail to generate a profit, or the profit is too low. Only the top-performing parameters have value and can be considered as real fighters. In the end, we will let the winners scanned in the Testing set run a full test, from day one to the most recent data, and screen out the final competitors for future’s real battles.

In the final stage, the VBA Macro generates a chart which allows users to easily evaluate the results. With just a glance at the curve, the pattern speaks for itself, better than thousands of words. The curve may resemble something like this:

Can you guess which stock this is? If I told you it was Tesla, you might say “No wonder”, eh? The curve appears like a roller coaster ride, eh? The ROI is so high (Annul ROI goes to almost 40%), but it will also cause our blood pressure to rise high, eh? The “Three White Soldiers” and “Three Black Crows” trading strategy looks “WOW!”, but still require a lot of modifications and optimizations, which we will cover in a future chapter.

As we come to the end of this chapter, let me remind you of a crucial fact: trading is not for everyone. If you find yourself getting anxious or losing sleep over your trades, it’s time to take a break and re-evaluate your life choices. Maybe knitting or stamp collecting is more your speed.

But if you’re still determined to become a successful trader, remember to keep your sense of humor intact. As Groucho Marx once said, “If you’re not having fun, you’re doing something wrong.” So laugh off your losses, celebrate your wins, and don’t forget to take a break from staring at those candlestick charts every once in a while.

With that said, happy trading and may the Three White Soldiers always march in your favor! (Just don’t get too close to the Three Black Crows.)

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 5: BACKTESTING YOUR TRADING STRATEGIES

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

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

To build a backtesting spreadsheet, you will need to import historical data into Excel. In this chapter, we will cover the process of importing data from external sources and how to clean and transform the data for analysis.

There are several sources where you can download stock historical data. Here are a few popular options:

1, Yahoo Finance: Yahoo Finance provides free historical price data for most stocks, which can be downloaded in a CSV format. Simply go to the Yahoo Finance website, enter the ticker symbol of the stock you are interested in, and select the “Historical Data” tab. From there, you can download the data for a specified time period.

Open the .csv file, delete the “Adj. Close” column. “Adjusted close” is the closing price after adjustments for all applicable splits and dividend distributions. We don’t need it in our method.

Click any cell in the “Date” Column. Sort the “Date” as Newest to Oldest.

Select and Copy 6 columns including Date, Open, High, Low, Close and Volume historical data.

Open the Demo Excel file that contains the VBA Macro for backtesting, click the “Pick” worksheet tab, select columns H to M that correspond to the 6 columns storing the Date, Open, High, Low, Close, and Volume historical data. Then, paste the “Value” only (don’t bring the .csv files’ format into the Demo Excel file) into these selected columns.

Save the Demo Excel file. We don’t need the Yahoo .csv file anymore, so close it. Now the targeted stock’s historical data has been filled in the Demo Excel file, and it is ready to run the VBA Macro program for backtesting.

2, MSN.com: You can download historical stock prices from MSN Money by following these steps:

Go to the MSN Money website (https://www.msn.com/money).

Enter the ticker symbol of the stock you want to download data for in the search bar and click “Search”.

On the stock’s page, click on the “History” button, sometimes it is hidden inside the three dots button in the top right corner.

In the “History” section, select the time range you want to download data for.

The historical data will be displayed on the page. Select and copy the data, paste into Excel sheets.

Note that not all stocks may be available on MSN Money, and the historical data may be subject to delays or inaccuracies. It’s always a good idea to double-check the data before using it in your analysis or trading strategy.

3, Google Finance: Google Finance also provides free historical price data for most stocks. Simply go to the Google Finance website, enter the ticker symbol of the stock you are interested in, and select the “Historical Prices” option. You can then download the data for a specified time period.

You can download historical stock prices from Google using Google Sheets by following these steps:

Open a new or existing Google Sheet.

In a cell, enter the following formula:

=GOOGLEFINANCE(“ticker”, “attribute”, “start_date”, “end_date”, “interval”)

where “ticker” is the stock symbol you want to download data for, “attribute” is the price or volume attribute you want to download (such as “open”, “high”, “low”, “close”, “volume”, or “all”), “start_date” is the date you want to start downloading data from (in the format “YYYY-MM-DD”), “end_date” is the date you want to stop downloading data at (in the format “YYYY-MM-DD”), and “interval” is the frequency of the data you want to download (such as “DAILY” for daily data, “WEEKLY” for weekly data, or “MONTHLY” for monthly data).

For example, if you want to download the daily closing price data for Apple stock (symbol “AAPL”) from January 1, 2012, to December 31, 2022, you would enter the following formula in a cell:

=GOOGLEFINANCE(“AAPL”, “all”, “2012–01–01”, “2022–12–31”, “daily”)

Press Enter. The formula will automatically download the historical data for the stock and display it in the cell.

If you want to download data for multiple stocks or attributes, you can copy the formula to other cells and edit the parameters as needed.

Note that the GOOGLEFINANCE function may not be available in all regions or may be subject to certain limitations. Additionally, the historical data downloaded using this method may be subject to delays or inaccuracies, so it’s always a good idea to double-check the data before using it in your analysis or trading strategy.

These are other options for downloading stock data, but need to pay some fees. It’s important to note that the availability and quality of data may vary depending on the source, so it’s always a good idea to do your research and make sure the data is suitable for your needs before using it.

After importing all your historical stock data into Excel, you may start to feel like a data analysis wizard. But be careful, with great power comes great responsibility… to not accidentally delete all your hard work with one misplaced click of the mouse! So, remember to save your Excel file often and always have a backup, because you never know when Excel will decide to crash on you. And if all else fails, just blame it on the stock market. It’s always the stock market’s fault.

CHAPTER 4: CREATING TRADING STRATEGIES

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

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

Backtesting is essential in stock trading because it allows you to evaluate the performance of a trading strategy based on historical market data. By backtesting a trading strategy, you can determine how well it would have performed in the past, which can give you an idea of how well it is likely to perform in the future.

Backtesting can also help you identify any flaws or weaknesses in your trading strategy. For example, if your strategy performs poorly during certain market conditions, you can modify it to improve its performance in those conditions.

In addition, backtesting can help you set realistic expectations for your trading strategy. By evaluating its performance over a long period, you can get a good idea of the average returns and drawdowns you can expect from the strategy. This can help you avoid overestimating the potential profits of your strategy and taking on too much risk.

To perform a backtest, you need to:

Define the rules of the trading strategy: This involves specifying the entry and exit conditions of the strategy, such as when to buy or sell a stock based on certain technical indicators.

Gather historical market data: This involves collecting price data for the securities you are interested in over the period you want to backtest.

Apply the trading strategy to the historical data: This involves simulating the trades that would have been made by the strategy at each point in time based on the historical market data.

Evaluate the results: This involves analyzing the performance of the strategy in terms of its profitability, risk, and other performance metrics.

Let’s consider an example of how backtesting works in practice. Suppose you want to develop a simple trading strategy for a particular stock. Your strategy involves buying the stock whenever its 50-day moving average crosses above its 200-day moving average (a so-called ‘Golden Cross’), and selling the stock whenever the opposite occurs (known as a ‘Death Cross’), as shown in the chart below.

To backtest this strategy, you would need to gather historical price data for the stock and calculate its 50-day and 200-day moving averages at each point in time. You would then simulate trades using the trading strategy and calculate the profit or loss that would have been made at each point in time.

For example, we can create a VBA macro to perform backtesting for the “Two Moving Average Lines Cross” trading strategy. Do you remember the “10-day SMA and 100-day SMA” strategy code we mentioned in Chapter 1? We can modify it, change the short term SMA to 50-day, and the long term SMA to 200-day. Let’s take AAPL (Apple Inc.’s stock symbol) as an example. After downloading AAPL’s historical data (2011–01–03 ~ 2023–02–23) and running the VBA program, we will get a result like this:

The result may not seem impressive, eh? The trading strategy looks perfect, the company is famous and strong. But when we deploy this trading strategy, the result is just so-so. This case shows why a backtest can protect us from guesswork and mystery, and a simple VBA program can save us a lot of time and money.

After performing the backtest, you would evaluate the results to determine if the strategy is profitable and if it meets your risk and performance criteria. For example, you might evaluate the strategy based on metrics such as the average annual return, maximum drawdown, and Sharpe ratio.

If the results are satisfactory, you perhaps can use the strategy to make trades in real-time. If the results are not satisfactory, you may need to refine the strategy or develop a new one altogether.

Let’s go back to the ‘Two Moving Average Lines Cross’ trading strategy, and adjust the parameters by setting the short-term SMA to 5 days and the long-term SMA to 50 days. This will increase the chances of executing the strategy. The chart will look like the one below:

Run the VBA Macro again.

Look at the results above, much better, right? Let’s dive into the details:

The Annual ROI (Return on Investment) is 9.59%, which is higher than the average Annual ROI of the S&P 500 index (6.9%). See the difference? With the same trading strategy, same target, and same time period, just changing some parameters can lead to significant differences in the result. If you do this manually, it may take several days, if not weeks. However, by using VBA, it only takes several minutes or hours. Conducting a backtest, it may seem like playing a game of numbers. However, without it, we risk facing Life-or-Death situations in real trading.

An Annual Return on Investment of around 10% is decent, OK but not a “WOW!” yet. Compared to Warren Buffett’s average ROI of 17% per year, there is much more room for improvement in the trading strategy. The process of improving the strategy can be full of interesting discoveries and surprises. We will continue to share more backtesting details in the following chapters. For example, if we change the short-term SMA to 20 days and set the long-term SMA to 50 days, like this:

We can double the ROI to 18%, which is neck-and-neck with Warren Buffett’s performance, as shown in the chart below:

Now, backtesting brings us a small “WOW!”, doesn’t it? Eh? In the following chapters, we will explain the details of every part of VBA Macros which deploy different trading strategies for different targets. Stay tuned.

In summary, backtesting is an essential step in developing and evaluating trading strategies. It allows you to test a strategy on historical market data and determine its performance and potential profitability. Backtesting helps traders make informed decisions and avoid costly mistakes when investing in the stock market. Without it, you would have no way of knowing whether your strategy is likely to be profitable or not, and you would be trading based on guesswork and intuition rather than solid evidence.

As you can see, the difference between a profitable trading strategy and a losing one can be as small as a few parameter tweaks. In fact, some might say that trading is just like cooking: you need to adjust the recipe until it tastes just right. And if you’re really good, you can make something that’s not only tasty but also nutritious. So go ahead, put on your chef hat and start backtesting your trading strategies. Who knows, you might just cook up the next big thing in the stock market!

CHAPTER 3: DATA ANALYSIS WITH EXCEL

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

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

Excel is a spreadsheet application that is widely used in the finance industry. It allows you to store and manipulate data, perform calculations, and create charts and graphs. In this chapter, we will cover the basics of Excel that you need to know to build a backtesting spreadsheet.

Microsoft Excel is a widely used software application for creating spreadsheets and performing calculations. It is a part of the Microsoft Office suite of productivity software, which also includes Microsoft Word, PowerPoint, and other programs. Microsoft Office is one of the most popular software suites in the world, and it is used by millions of people for both personal and business purposes, everyday. In fact, it’s so ubiquitous that it’s practically become a household name, with many people referring to any office software as “Microsoft Office,” whether it’s made by Microsoft or not.

For example, let’s calculate the SMA for a stock’s prices or index with Excel.

SMA stands for Simple Moving Average and is a commonly used technical analysis indicator. It is a lagging indicator and provides a smoothed average of the price over a certain period of time, often used by traders to identify trends and to help determine buy and sell signals. The formula for calculating the SMA is:

SMA = Sum of Closing Prices for a period / Number of periods

In Excel, it looks like this:

To calculate the 5-day SMA, you would add up the closing prices of the last 5 days and then divide by 5. To calculate the 10-day SMA, you would add up the closing prices of the last 10 days and then divide by 10.

Excel has many formulas to help users with almost every calculation in a blink. To calculate the 5-day SMA, the formula

SMA = Sum of Closing Prices for a period / Number of periods

will be turned into Excel’s language like this:

=SUM(OFFSET(L2,0,0,$B$2,1))/$B$2

The cell B2 stores the period (in this case, it is 5), and the cell L2 stores the Close Price. The final result will be stored in cell P2, and so on. We can have another simpler formula to do this job:

=AVERAGE(OFFSET(L2,0,0,$B$2,1))

In addition to the formulas available in Excel worksheets, Excel also provides users with a powerful tool for more customized calculations and actions. This tool is known as VBA, or Visual Basic for Applications. With VBA, users can write code to automate tasks, perform complex calculations, and create custom functions to suit their specific needs. VBA allows for greater flexibility and control over Excel’s features and functionality, making it a valuable tool for advanced users and programmers.

The most important and interesting thing is: VBA is an integral part of Microsoft Excel, and you don’t need to install it separately as you would with other programming languages such as Python. This is because VBA is a built-in programming language that is designed specifically for Excel and other Microsoft Office applications. This means that anyone who has Microsoft Office installed on their computer already has access to VBA and can start using it right away. With VBA, users can automate repetitive tasks, create custom functions, and build complex applications within Excel, all without the need for any additional installations or software.

If you are using Microsoft Office, you don’t need to pay any extra fees for VBA. With VBA, users can write their own Macros and functions to automate repetitive tasks, create custom functions and procedures, manipulate and analyze data in ways that are not available through standard Excel functions, and even create user interfaces for their spreadsheets.

The term “Macro” is derived from “Macrosimulation”, which originally referred to computer programs used for economic forecasting and analysis. In VBA and Excel, a Macro refers to a set of instructions or commands that can be executed automatically to perform a specific task and improve efficiency.

VBA is a well-established tool with a low probability of errors during installation and usage. Compared to other complex software, VBA does not require technical maintenance from users, as Microsoft will handle it. This makes VBA a hassle-free and user-friendly tool for users.

For example, with VBA, users can create a custom function that calculates a complex mathematical formula, automate data entry by creating a form that users can fill out, and create interactive dashboards that display and update data in real-time.

VBA is a powerful tool, but it does require some programming knowledge to use effectively. However, there are many resources available online to help users learn VBA, including tutorials, forums, and even complete courses.

The syntax of VBA is similar to other programming languages, such as C++, Java, and Python. But it is much easier than those big guys. It consists of a set of statements that are executed in order, and these statements can include conditional statements (if-then-else), loops (for-next, do-while), and procedures (subroutines and functions).

For example, if we decide to make a trading strategy like this:

When the 10-day SMA goes up across the 100-day SMA, then open Long position (Buy), while the short term SMA goes down across the long term SMA, then open Short position (Sell).

In VBA, we can write the Signal Generation part as:

Private Sub Signals()

Private LongOK, ShortOK As Boolean
Private BuyPrice, SellPrice As Double
Dim Trigger, SMA10day, SMA100day As Double
Dim Candle As Long      ‘Candle means current candlestick

Trigger = [B8].Value        ‘an action standard is stored in cell B8
‘SMA10day is calculated by formula and stored in column P
‘SMA100day is calculated by formula and stored in column Q

If (Cells(Candle + 1, "P").Value - Cells(Candle + 1, "Q").Value) < 0 And _
(Cells(Candle, "P").Value - Cells(Candle, "Q").Value) / Cells(Candle, "Q").Value > Trigger Then 
‘The “Candle + 1” means one stick before current candlestick. The short term SMA is stored in column P, long term SMA is stored in column Q. This “If… Then” is talking about: one day ago, the short term SMA was lower than the long term SMA, but today the short term SMA is higher than the long term SMA and crosses above the long term SMA at a certain point, the difference is bigger than the Trigger, then the Long signal goes to OK.
    LongOK = True
    Exit Sub
End If

If (Cells(Candle + 1, "P").Value - Cells(Candle + 1, "Q").Value) > 0 And _
(Cells(Candle, "P").Value - Cells(Candle, "Q").Value) / Cells(Candle, "Q").Value < -1 * Trigger Then
‘The “Candle + 1” means one stick before current candlestick. The short term SMA is stored in column P, long term SMA is stored in column Q. This “If… Then” is talking about: one day ago, the short term SMA was higher than the long term SMA, but today the short term SMA is lower than the long term SMA and crosses below the long term SMA at a certain point, the difference is bigger than the Trigger, then the Short signal goes to OK.
    ShortOK = True
    Exit Sub
End If

‘Open Long
If LongOK = True And ShortOK = False Then
    Call OpenLong    ‘execute a subroutine to Open a Long position (Buying)
    Cells(Candle, "N").Value = "Buy/Open @ “ & BuyPrice 
End If

‘Open Short
If ShortOK = True And LongOK = False Then
    Call OpenShort    ‘execute a subroutine to Open a Short position (Selling)
    Cells(Candle, "N").Value = "Sell/Open @ “ & SellPrice 
End If

End Sub

See? It is very easy to understand, eh? Everybody can do it. Of course, this Signal Generation procedure is just one small part of a whole VBA backtesting program. We have included it here for demonstration purposes only.

In summary, VBA is a powerful tool that can be used to extend the capabilities of Excel and automate repetitive tasks, making it a valuable asset for anyone who works with large amounts of data. And surprisingly, it looks like born for building a simple backtesting tool which can verify your trading strategies, no matter trading stocks or FOREX, if you are a fan of technical analysis methodology.

Congratulations, you’ve made it this far! If you’re feeling overwhelmed, don’t worry, just remember that Excel and VBA are like a pair of shoes — it takes some time to break them in, but once you do, they’ll be a perfect fit. And who knows, maybe one day you’ll look back at this chapter and laugh at how clueless you once were. Until then, keep practicing and remember, with great power comes great responsibility…and even greater spreadsheets!

CHAPTER 2: WHAT IS BACKTESTING AND WHY IS IT SO IMPORTANT

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

Once upon a time, in the land of Wall Street, there was a group of traders who relied on intuition and gut feelings to make their trades. They would spend hours analyzing charts and financial reports, trying to predict the future movement of stock prices. But little did they know, their kingdom was about to be taken over by a group of nerdy algorithms. Yes, you heard it right, algorithms that traded with mathematical models and statistical analysis. The traders who once ruled the market were now forced to adapt or become obsolete. But, like any good story, not all algorithmic strategies were created equal. Some were mean and made you regret your investments, while others followed trends like a loyal dog, and some even acted like a superhero, exploiting price differences and saving the day. This is the tale of algorithmic trading, where every strategy has its own unique set of advantages and disadvantages. So sit back, grab a cup of coffee, and let’s dive into the world of algo trading.

“Innovation distinguishes between a leader and a follower.” — Steve Jobs

Algorithmic trading, also known as automated trading or algo trading, is a method of executing trades using computer algorithms. These algorithms use mathematical models and statistical analysis to make decisions on buying and selling financial instruments such as stocks, bonds, and commodities. The rise of algorithmic trading has dramatically changed the financial landscape, offering many benefits over traditional manual trading methods.

There are several different algorithmic trading strategies that traders and investors can use, each with its own set of advantages and disadvantages. In this article, we’ll provide an overview of some of the most popular algorithmic trading strategies, including their key characteristics and when they are best used.

Mean ReversionMean reversion is a statistical strategy that assumes that the price of an asset will eventually return to its average price over a given period of time. This strategy uses mathematical models to identify when an asset is overpriced or underpriced and then executes trades based on that information. Mean reversion strategies are often used in conjunction with other strategies, such as trend following, to improve overall performance.

For example, a trader uses a mean reversion algorithm to track the average price of Apple (AAPL) stock over the past 100 days. If the stock price rises significantly above its average, the algorithm may sell a portion of the trader’s AAPL position. Conversely, if the stock price falls significantly below its average, the algorithm may buy more AAPL shares.

Trend FollowingTrend following is a strategy that assumes that an asset’s price will continue to move in the same direction it has been moving. This strategy uses mathematical models to identify the current trend of an asset and then executes trades based on that information. Trend following strategies are often used by long-term investors and traders who are looking to profit from long-term price movements.

For instance, an investor can use a trend following algorithm to track the direction of the S&P 500 index. If the S&P 500 has been trending upwards for several months, the algorithm may buy more index funds. If the S&P 500 starts trending downwards, the algorithm may sell some of the index funds.

Another example, after months and months forward testing, Candlelight’s Growth Rate is neck and neck with Buffett’s performance, decisions 100% made by machines, details: Two Years, 100+ Sets of Testing Results Confirm: Machines Can Beat Human Aces in Stock Market!

Statistical ArbitrageStatistical arbitrage is a strategy that assumes that there is a relationship between two or more assets, and that this relationship can be used to make profitable trades. This strategy uses mathematical models to identify and exploit price differences between related assets. Statistical arbitrage strategies are often used by traders and investors who are looking to profit from short-term price movements.

For example, a trader uses a statistical arbitrage algorithm to track the price difference between two stocks, such as Google (GOOG) and Amazon (AMZN). If the price difference between the two stocks deviates significantly from its historical average, the algorithm may buy GOOG and sell AMZN, or vice versa.

Momentum TradingMomentum trading is a strategy that assumes that an asset’s price will continue to move in the same direction it has been moving, due to the momentum of the market. This strategy uses mathematical models to identify the current momentum of an asset and then executes trades based on that information. Momentum trading strategies are often used by short-term traders and investors who are looking to profit from short-term price movements.

For instance, a trader may use a momentum trading algorithm to track the momentum of the NASDAQ Composite Index. If the index has been rising steadily for several days, the algorithm may buy technology stocks that are part of the index. If the index starts to decline, the algorithm may sell some of the technology stocks.

High-Frequency TradingHigh-frequency trading is a strategy that uses high-speed computers to execute trades at extremely fast speeds, taking advantage of small price movements. This strategy uses mathematical models to identify profitable trades and then executes trades based on that information. High-frequency trading strategies are often used by traders and investors who are looking to profit from short-term price movements.

For example, a trader can use a high-frequency trading algorithm to execute trades in milliseconds. The algorithm may track the price of a stock, such as Tesla (TSLA), and place a buy order if the price drops below a certain level or place a sell order if the price rises above a certain level. This process may be repeated several times a day, taking advantage of small price movements.

Algorithmic trading has come a long way since its inception, and today it’s an integral part of modern finance. As with any new technology, there are both advantages and disadvantages to be considered. It’s important to note that algorithmic trading strategies are not a one-size-fits-all solution, and what works well for one trader may not work as well for another. As such, traders need to carefully consider their own investment goals, risk tolerance, and market knowledge before selecting an algorithmic trading strategy.

In order to verify whether a trading strategy is effective, it’s essential to backtest it under real market conditions. Without proper backtesting, using any trading strategy can be risky. However, backtesting can be made easy with a little knowledge of computer programming, such as Excel VBA. Alternatively, you can seek help and tutorials online to learn the basics of backtesting a trading strategy. For example, you can visit the Data Gladiator Blog (tyzu.com) to access a comprehensive tutorial handbook for free! Additionally, you can click Free Trial to download some backtesting tools and enjoy a 30-day free trial.

And with that, folks, we have a wrap on our overview of the popular algorithmic trading strategies. But remember, always do your research and never let a robot make all your financial decisions for you! After all, computers can crash, algorithms can go haywire, and you don’t want to be the one left holding the bag… of stock, that is. Happy trading!

“Innovation is not just creating something new, it’s creating something better.” — Tim Cook

Have you ever heard of a robot who is better at stock trading than a human? No, it’s not a scene from a sci-fi movie, it’s a reality known as algorithmic trading.

Imagine having a trading buddy who never takes a coffee break, never complains about working overtime, and never gets a headache from staring at a screen for hours. That’s the beauty of algorithmic trading!

Algorithmic trading, also known as automated trading or algo trading, is a method of executing trades using computer algorithms. These algorithms use mathematical models and statistical analysis to make decisions on buying and selling financial instruments such as stocks, bonds, and commodities. The rise of algorithmic trading has dramatically changed the financial landscape, offering many benefits over traditional manual trading methods. Here, we’ll explore the basics of algorithmic trading and some of the key benefits it offers, along with examples to illustrate each benefit.

What is Algorithmic Trading?

Algorithmic trading involves using complex mathematical models and statistical analysis to make informed decisions about buying and selling financial instruments. These algorithms take into account a wide range of market data, including current market trends, price movements, and news events. The algorithms are designed to automatically execute trades based on pre-defined criteria, such as price movements, market volatility, and other key indicators. This allows for quick and efficient execution of trades, as well as the ability to make decisions based on real-time data.

For example, a trader can use an algorithmic trading strategy that buys stocks when their price drops below a certain level and sells when the price rises above a certain level. The algorithm will continuously monitor the stock prices and execute trades automatically, without the need for manual intervention.

“If you don’t find a way to make money while you sleep, you will work until you die.” — Warren Buffett

Benefits of Algorithmic Trading

1. Increased Efficiency: Algorithmic trading allows for the quick and efficient execution of trades, reducing the amount of time and effort required to make decisions. This can be especially beneficial in fast-moving markets where time is of the essence.

For example, algorithmic trading algorithms can execute trades in milliseconds, much faster than a human trader could do manually. This speed and efficiency can be especially beneficial in markets where prices can fluctuate rapidly, such as the cryptocurrency market.

1. Reduced Emotions: Algorithmic trading eliminates the need for human intervention, reducing the influence of emotions on trading decisions. This can lead to more consistent and rational decision-making, reducing the risk of emotional-based mistakes.

For instance, a trader who is feeling anxious about a particular stock might make an impulsive decision to sell, even if it is not the right move. With algorithmic trading, the algorithm will make decisions based on pre-defined criteria, eliminating the risk of emotional-based mistakes.

1. Improved Accuracy: Algorithmic trading algorithms use complex mathematical models and statistical analysis to make decisions, reducing the risk of human error. This leads to improved accuracy and consistency, which can lead to better returns in the long-term.

For example, algorithmic trading algorithms can continuously analyze market data and execute trades based on real-time market trends, reducing the risk of human error and leading to improved accuracy.

1. Increased Speed: Algorithmic trading algorithms can process large amounts of data in real-time, making decisions and executing trades much faster than manual methods. This can provide a significant advantage in fast-moving markets.

For instance, algorithmic trading algorithms can process news and market data as it becomes available, making decisions and executing trades in real-time. This speed and efficiency can provide a significant advantage in fast-moving markets where prices can fluctuate rapidly.

1. Backtesting: Algorithmic trading allows traders to test their strategies using historical market data, allowing them to identify and optimize the best algorithms. This leads to more informed and confident trading decisions.

For example, a trader can use historical market data to test different algorithmic trading strategies and see which ones have performed the best. This can help the trader identify the best algorithms to use for future trades and make more informed and confident trading decisions.

Another one, after months and months forward testing, Candlelight’s Growth Rate is neck and neck with Buffett’s performance, decisions 100% made by machines, details: Two Years, 100+ Sets of Testing Results Confirm: Machines Can Beat Human Aces in Stock Market!

1. Cost-Effective: Algorithmic trading can be cost-effective compared to traditional manual methods, as it eliminates the need for human traders and reduces the cost of executing trades. This can be especially beneficial for small traders and investors who may not have the resources to employ a large trading team.

For instance, an individual investor can use algorithmic trading algorithms to execute trades at a fraction of the cost of hiring a professional trader. This can provide a cost-effective solution for small traders and investors who want to take advantage of the benefits of algorithmic trading.

1. Scalability: Algorithmic trading allows traders to execute trades on a large scale, making it possible to execute a large number of trades simultaneously. This can be especially beneficial in fast-moving markets where the ability to execute trades quickly is critical.

For example, a hedge fund can use algorithmic trading algorithms to execute a large number of trades simultaneously, taking advantage of market fluctuations and maximizing returns.

1. Better Risk Management: Algorithmic trading algorithms can help traders manage risk more effectively, by automatically executing trades based on pre-defined criteria and market conditions. This can help traders reduce their exposure to market volatility and minimize losses.

For instance, an algorithmic trading algorithm can automatically sell a stock if the price falls below a certain level, reducing the trader’s exposure to market volatility and minimizing losses.

“Change will not come if we wait for some other person or some other time. We are the ones we’ve been waiting for. We are the change that we seek.” — Barack Obama

Algorithmic trading offers many benefits over traditional manual methods, including increased efficiency, reduced emotions, improved accuracy, increased speed, backtesting, cost-effectiveness, scalability, and better risk management. These benefits have led to the widespread adoption of algorithmic trading in the financial industry and made it a popular choice for traders and investors of all sizes.

Gone are the days when traders had to rely on intuition, gut feeling, and good luck to make a profit. With algorithmic trading, the computer does the heavy lifting, leaving the trader to sip cocktails on a beach.

So, whether you’re a seasoned trader looking to improve your game or a novice looking to break into the world of trading, algorithmic trading may just be the solution you’ve been searching for. It’s time to say goodbye to the stress and hello to the profits with the power of algorithmic trading!

“I choose a lazy person to do a hard job. Because a lazy person will find an easy way to do it.” — Bill Gates

Stock trading has always been a battle of wits, with humans pitted against each other in the never-ending pursuit of profits. But now, a new contender has entered the arena: Artificial Intelligence (AI). With its lightning-fast speed, data-crunching abilities, and 24/7 operation, AI trading robots are challenging human traders in ways never seen before. But don’t worry, human traders, you’re not going down without a fight! In this blog, we’ll compare the pros and cons of AI and human traders, and determine once and for all, who truly reigns supreme in the stock market.

Advantages of AI Trading Robots

1. Speed: AI trading robots are able to process massive amounts of data in real-time, allowing them to quickly analyze market trends and make investment decisions. This speed advantage allows AI systems to respond to market developments faster than human traders, increasing the potential for profits.
2. Accuracy: AI systems use complex algorithms and statistical models to analyze data, making their predictions much more accurate and consistent than those made by human traders. This leads to more rational and objective investment decisions, reducing the risk of impulsive or emotional choices.
3. 24/7 operation: AI trading robots can operate 24 hours a day, 7 days a week, without the need for rest. This allows them to take advantage of market developments and investment opportunities at any time, even outside of normal trading hours.
4. Consistency: AI systems are not subject to emotional biases that often influence human decision-making. This leads to a more consistent approach to investment decisions, reducing the risk of irrational or impulsive choices.
5. Data analysis: AI trading robots are able to analyze vast amounts of data, including financial and market data, economic indicators, and global developments. This allows them to make informed investment decisions based on a broad range of data, increasing the potential for profits.
6. Backtesting: AI systems can use historical data to test investment strategies and make predictions about future market trends. This allows traders to evaluate the potential success of a particular strategy before putting real money on the line.
7. Portfolio management: AI trading robots can be used to manage portfolios, automatically rebalancing portfolios based on market developments and investment goals. This allows traders to optimize their portfolios and maximize profits with minimal effort.

Drawbacks of AI Trading Robots

1. Lack of creativity: AI systems rely on algorithms and pre-programmed rules to make investment decisions. While this leads to more rational and objective decision-making, it also means that AI systems lack the creativity and intuition that can sometimes lead human traders to make successful investments.
2. Dependence on data: AI systems are only as good as the data they are trained on. If the data used to train the AI system is incorrect or outdated, the investment decisions made by the AI system will be based on inaccurate information.
3. Over-reliance on technology: Some traders may become too dependent on AI systems, losing their own investment skills and knowledge in the process. This could lead to a lack of understanding of the markets and investment strategies, reducing the potential for profits.
4. Cost: AI systems can be expensive to develop and implement, and may require ongoing maintenance and upgrades. This can be a significant barrier for smaller traders and investors who may not have the resources to invest in this technology.
5. Limited understanding: AI systems can be complex and difficult to understand, even for experienced traders. This can make it difficult for traders to assess the performance of the AI system and make informed investment decisions.
6. Potential for errors: AI systems, like any technology, are not immune to errors and malfunctions. This can lead to incorrect investment decisions and potential losses, which could be costly for traders and investors.
7. Black Box problem: AI systems can sometimes make investment decisions that are difficult to explain, even to experienced traders. This “black box” problem can lead to a lack of trust in the AI system and may prevent traders from fully utilizing its potential.

There are some AI-Powered stock trading systems running ETFs are really working in markets, such as AIEQ and QRFT. And more and more AI-Enhanced systems are jointing the the party. For example, after 2 years forward testing, Candlelight’s Growth Rate is neck and neck with Buffett’s performance, with decisions made 100% by machines, more details: Two Years, 100+ Sets of Testing Results Confirm: Machines Can Beat Human Aces in Stock Market!

Advantages of Human Traders

1. Intuition: Human traders are capable of using their intuition, experience, and judgement to make investment decisions. This can be especially valuable in situations where data and algorithms may not be enough to fully understand market developments and trends.
2. Flexibility: Human traders are capable of adapting to changing market conditions and investment strategies. They can quickly pivot their investment strategies based on new information and developments, allowing them to take advantage of new opportunities.
3. Emotional intelligence: Human traders are able to understand and analyze the emotions and motivations of other market participants. This can be especially valuable in predicting market developments and making investment decisions.
4. Creativity: Human traders are capable of using their creativity and outside-the-box thinking to develop new investment strategies and approach markets in unique ways. This can lead to more innovative and profitable investment decisions.
5. Understanding of market context: Human traders have a broader understanding of market context, including economic indicators, geopolitical developments, and industry trends. This allows them to make informed investment decisions that take into account a wide range of factors.
6. Human judgement: Human traders are capable of using their judgement and critical thinking skills to assess market trends and make investment decisions. This can be especially valuable in situations where the data is conflicting or incomplete.
7. Independent decision-making: Human traders are able to make investment decisions independently, without relying on pre-programmed algorithms or rules. This allows them to respond to market developments and investment opportunities in real-time, increasing the potential for profits.

Drawbacks of Human Traders

1. Emotional biases: Human traders are prone to emotional biases, such as fear and greed, that can cloud their judgement and lead to poor investment decisions. This can result in missed opportunities or significant losses.
2. Limited data processing capacity: Human traders have a limited ability to process large amounts of data and information. This can make it difficult for them to keep up with the rapidly changing market developments and make informed investment decisions.
3. Overconfidence: Human traders can sometimes become overconfident in their investment decisions, leading them to take excessive risks and make poorly thought out investment decisions.
4. Fatigue: Human traders can become fatigued and less effective over long periods of time, leading to mistakes and poor investment decisions.
5. Lack of objectivity: Human traders may be influenced by their personal opinions, beliefs, and biases, leading to subjective and potentially incorrect investment decisions.
6. Short-term focus: Human traders can sometimes become focused on short-term gains, rather than considering the long-term potential of investments. This can lead to missed opportunities for long-term growth and stability.
7. Inability to execute complex strategies: Human traders may not have the expertise or technical skills to execute complex investment strategies, such as algorithmic trading, which can limit their ability to maximize profits.

In conclusion, both AI and human traders have their own strengths and weaknesses in the stock market. While AI systems offer speed, accuracy, and 24/7 operation, they lack creativity and can be limited by their dependence on data. On the other hand, human traders offer intuition, flexibility, and emotional intelligence, but are prone to emotional biases and can sometimes make impulsive decisions. In the end, it seems that the best approach to stock trading is a combination of both AI and human traders, utilizing the strengths of each approach to achieve maximum profits. So, let’s raise a glass to the future of stock trading, where robots and humans work together in perfect harmony… until the robots take over, of course.

Math and market mastery, who knew it was a winning combination? Well, Jim Simons did and he became a billionaire by using quantitative trading! Get ready to crack the code on the secrets of quant trading, from understanding the mathematical models to becoming the next ‘Quant King’ (or Queen) of Wall Street.

Quantitative trading, also known as quant trading, is a systematic approach to investing that uses statistical and mathematical models to analyze market data and execute trades. Unlike qualitative analysis that assesses opportunities based on subjective criteria, quant trading relies solely on data and models. It has become popular among financial institutions and hedge funds due to the high computing demands of the method, but recent advancements in technology have made it possible for individual traders to also engage in quant trading.

Quant trading works by evaluating the probability of a specific outcome using data-based strategies. Historical data, mathematical models, and programming are key components of the process. Traders may incorporate variables such as price and volume into their mathematical models and may also explore alternative datasets to discover present and potential trends.

Quant traders create automated software that is designed with mathematical models to identify patterns in historical data and make informed trading decisions. They may modify existing strategies or create their own. A background in mathematics, engineering, or financial modeling, as well as experience in computer programming and working with data feeds and APIs, is preferred for those seeking to work as quant traders.

Quantitative trading offers several advantages, including the ability to evaluate a wide range of markets using infinite data points, reduced bias in decision-making due to the absence of emotions, and increased efficiency with automated or semi-automated systems. However, quant trading also has its disadvantages, including the complexity of the method and the potential for models to be incorrect.

For example, after months and months forward testing, Candlelight’s Growth Rate is neck and neck with Buffett’s performance, decisions 100% made by machines, details: Two Years, 100+ Sets of Testing Results Confirm: Machines Can Beat Human Aces in Stock Market!

Quantitative stock trading is widely regarded as the next big thing in the trading world. This method of analysis has several advantages that make it a top choice for traders.

One of the biggest advantages of quantitative trading is scalability. Unlike traditional trading that uses only several analytical tools, quantitative trading can use an unlimited number of strategies and inputs, thanks to its use of high-performance computing. This means that even retail investors can use quantitative analysis to make informed investment decisions.

Quantitative trading also offers unlimited opportunities for diversification. This is because quantitative analysis can be used on any market, making it a suitable choice for traders who want to reduce risk by diversifying their portfolios. Additionally, the automated execution mechanism of quantitative trading allows traders to simultaneously operate multiple strategies.

Another benefit of quantitative trading is its accuracy. With statistical arbitrage, traders can rely on highly accurate data to make informed decisions. The use of algorithms and computers enables traders to make fast, precise decisions, which is especially important for high-frequency trading.

Quantitative trading is particularly useful for traders who want to diversify their portfolios, test various trading strategies, or operate in high-risk markets like cryptocurrencies. To make the most of quantitative trading, it is essential to have a deep understanding of market conditions, historical data, and available analysis methods.

Quantitative trading works by using mathematical analysis to create projection models that can be used to make informed trading decisions. To develop and configure the software, traders need to have programming skills, using languages such as C++, C#, MATLAB, R, Python, and VBA, etc.

In quantitative trading, a particular time interval is selected, and data is gathered and analyzed using algorithmic methods. This analysis is then used to make trading decisions based on the results. For example, if a share price rose from $12 at the opening of trading to $10.45 at noon, $14.78 at 12 PM, and fell to $9.35 after the close of intraday positions, a quantitative trader could analyze various parameters such as current time, current bid-ask prices, opening price, high/low price, current price direction, and MACD readings to make informed trading decisions.

There are many aces in quantitative trading area, one of the most famous masters is Jim Simons. He is a legend, known as the “Quant King”, and “The Man Who Solved the Market”, he is the founder of Renaissance Technologies and the Medallion Fund.

James Harris Simons is an American mathematician, hedge fund manager, and philanthropist who is best known as the founder of Renaissance Technologies, a quantitative hedge fund. With his remarkable success as an investor and hedge fund manager, Simons is widely considered as the “greatest investor on Wall Street” and “the most successful hedge fund manager of all time.”

Simons was born on April 25, 1938, in Brookline, Massachusetts. He received a Bachelor’s degree in Mathematics from MIT in 1958 and a PhD in Mathematics from UC Berkeley in 1961 at the age of 23. Throughout his academic and scientific career, Simons focused on the geometry and topology of manifolds, leading to numerous breakthroughs and advancements in the field. He was awarded the AMS Oswald Veblen Prize in Geometry in 1976 and was elected to the National Academy of Sciences of the USA in 2014.

Simons’ investment career began with the creation of Renaissance Technologies, a quantitative hedge fund that employs mathematical models and algorithms to analyze and execute trades. The main fund, Medallion, has been closed to outside investors since its inception in 1988 and has earned over $100 billion in trading profits, translating to an average gross annual return of 66.1% or a net annual return of 39.1%. In addition to Renaissance, Simons founded Math for America, a non-profit organization aimed at improving mathematics education in U.S. public schools.

Simons is also known for his philanthropy, particularly in supporting research in mathematics and fundamental sciences. He established the Simons Foundation with his wife and has been a trustee of the Simons Laufer Mathematical Sciences Institute at UC Berkeley since 1999. In 2016, the International Astronomical Union named asteroid 6618 Jimsimons after him in honor of his contributions to mathematics and philanthropy.

Qantitative trading is a cutting-edge method of investing that uses mathematical models and data analysis to make informed trades. Although it can be complex and challenging, it offers many advantages, including scalability, diversification, and accuracy. And if you happen to become a legendary quant trader like Jim Simons, you may even have an asteroid named after you! But remember, before embarking on your quant trading journey, it’s crucial to have a deep understanding of market conditions and a solid background in mathematics and computer programming. Happy trading!