Last Updated on September 19, 2023 by Mark Ursell
Do you trade Stocks, Forex, ETFs, Futures, Indices, Bonds or Commodities?
There is one thing that every successful trader needs. Whether starting out, moving to the next level or developing new strategies.
When you can do this you can:
- Get better and more reliable trade entries.
- Optimize your stop-loss and profit target settings.
- Test your trading ideas (and only trade the best ones).
The magic trick to doing all this and more is Backtesting.
Backtesting gets you more Winning Trades and fewer Losing Trades
Once you learn to backtest you can do all this and more.
Table of Contents
However, there are some things to beware when you are backtesting. One of the biggest problems that we all face is getting quality historical price data.
Historical data is useless if is wrong – in fact it is worse than useless.
One bad price in 10,000 lines of data could ruin your results
Just one bad data winning trade could falsely convince you to trade a losing strategy.
Or, one bad data losing trade could falsely convince you NOT to trade a winning strategy.
Read this article and videos to see how you can check for errors and then clean historical data price data.
Use Excel Formulas to Check for Errors
Formulas are the simplest and sometimes the easiest way to check for errors in your data. Especially if you are using a small amount of data.
IF Statements – The Building Blocks
Data checking Excel formulas usually start with an IF Statement. In Excel, IF Statements look like:
=IF(A is greater than B, THEN, “Yes”, “No”)
Find Bad Tick Data With Formulas
In the example, shown in the video below, I show you how you can use formulas to find errors in your data.
I downloaded the sample data used in this example from Yahoo Finance: QQQ on Yahoo Finance
One thing you might want to search for is data when the high Price is lower than either the Open or the Close. Or the Low Price is higher than the Open or the Close.
I use the formula:
Cell H2 =IF(OR(C2<B2,C2<E2),”High Price Error”, IF(OR(D2>B2,D2>E2),”Low Price Error”,””))
Use Formulas to Check for Errors – Video
Use Visual Basic (VBA) to Clean Historical Price Data
Using code to check automatically and clean data opens up a new world of possibilities.
You might be worried that coding sounds complicated. But there are shortcuts and templates that you can use with no experience.
You can download the spreadsheet that I demonstrate in the video for free using the link below: Sign Up and Get the Spreadsheet
Visual Basic (VBA)
Visual Basic or VBA is a coding language that allows you to run programs or macros directly from Excel.
Two key advantages of learning to use VBA to identify problems and clean your historical data are:
- Usually quicker than formulas. This can be important when you have a large set of data.
- Allows you to automatically clean a data series. You can use VBA to highlight potential problems. And then build a clean data series.
Find Bad Tick Data With Coding (VBA)
In this second video I show how you can do exactly the same task of checking the High-Low Price errors. But I use VBA to identify problems and give me an alert.
This macro runs very quickly and then brings up a message box to tell you how many errors it has found.
If the macro finds any problems, you can then open up the VBA Editor.
Then press CTRL G and see a list of the errors.
Use Code to Check for Errors – Video
Sign Up To Get The Free Template
Fill in your details below to get the template.