Cleansing Price History

If you're looking at a chart you will notice ridiculous ticks straight away. Because the price will be going up and down quite smoothly, and then all of a sudden there will be a price bar that is incredibly out of whack with all the others.

In some of the older Gain Capital files especially, you can get negative prices, and prices that are 100 times bigger than those surrounding them.

What you have to do first is find the erroneous ticks. Then delete those ticks. And then re-create the price bars.

The method I use to identify the bad ticks, is to create the Hourly intervals from those ticks, and then chart the Hourly bars. If the chart flows nicely, then there is nothing to do. If however there are massive spikes in either direction, then there is a bad tick (at least one, maybe more) within that hour.

Make sure you do this for the bid prices, and then do it again for the ask prices. Sometimes one can be fine and the other has a problem.

Now that you've narrowed down the location of the bad ticks to within an hour, you can use the Price History Search window ("Price data..." > "Price bars, ticks") in order to search for and delete those ticks.

Enter the search criteria. Narrow the from and to date fields so that only that hour's ticks are retrieved. Ensure that the "# of rows" setting is enough to retrieve all ticks within that hour.

Once you have all the ticks for that hour in the result grid, click the "Bid" column header. That will sort the results by that column. The bad ticks will either be at the very start or very end of the results. Select the ticks that are obviously bad (negative numbers, stupidly high or low values compared to the others), and delete them. And do the same for the ask column.

Now the bad ticks are gone, you have to recreate the Hourly intervals and repeat the process. And once all the bad ticks are gone, then you can go about creating the other intervals you want to backtest with also.

The files from Gain Capital seemed to become cleaner as time went on. So the older the file, the more likely it is that some bad ticks will be present. Personally, we aren't going to use any of the data before January 2004.