How to Improve And Clean Historical Price Data

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.

Simply put:

Backtesting gets you more Winning Trades and fewer Losing Trades

Once you learn to backtest you can do all this and more.

Backtesting Pitfalls

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.