Cleaning raw tick data...

I've finally got the new data downloaded and am getting the pairs I want to work with now extracted and ready for cleaning.

In the past I've worked with smaller consecutive batches of data that fit into excel... this time around the sample size is too large for excel. Has anyone ever cleaned bad ticks with a SQL string? I think it would be pretty straight forward but that's what I always think right before a train wreck that ends up taking 12 hours to fix...

Thought I would get some input before I jumped into the tar...

DAMN... don't everybody answer all at once!!! LOL

Okay I'm getting the SQL stuff worked out slow but sure and am getting the raw ticks built into the time series I'm going to need.

I've also been doing some probability optimization work on a model over the weekend (not my model). The problem with the model right now is, it carries way too much open & correlated leverage, making the model almost nearly impossible to implament from a reasonable margin standpoint. As soon as we deconstruct the returns into something more reasonable, returns of course fall off as they are directly related to leverage...

The anwer may lie in optimizing the model through a probability study that essentially says, good trade, bad trade. It's all in the early stages but here are some of the initial returns and work...

EUR/USD Pre Optimization vs Original All Trade Data
Average Open Trades dropped from five to two. (Model positive)
Max Open Trades dropped from sixteen to five. (Model positive)
Max Net Exposure dropped from eleven to four. (Model positive)
Period Returns dropped from 24.8% to 4.06%. (Model negative)

EUR/USD Post Optimization vs Original All Trade Data
Average Open Trades dropped from five to two. (Model positive)
Max Open Trades dropped from sixteen to five. (Model positive)
Max Net Exposure dropped from eleven to four. (Model positive)
Period Returns dropped from 24.8% to 15.38%. (*Model negative)

* This drop in returns vs the All Trade Data is also a significant increase in returns over the original EUR/USD returns and brings the model up to our benchmark average and well above the actual returns the benchmark has generated during the same period.

(See picture below)...

Understanding Model Risk and where Your trading and model are effective and where they aren't is always worth a look. Start thinking about your trading activities in the bigger picture and look for measurments and relationships that you haven't considered in the past; you may be suprised.

Don't know if this will help or not, here is a site I have used for excel related functions, but it may have something you can use as the site says:
"TA-Lib provides common functions for the technical analysis of financial market data.
Widely used by trading software developers working with Excel, .NET, Java, Perl, Python or C/C++."

http://www.ta-lib.org/

Can you please explain how tick data is recorded, in what format, and how you determine what is "clean" and what it not? I'm very interested because I need good data myself for an machine learning (artificial intelligence) study on major pairs. I don't use excel (or most ugly microsoft products) but I do know SQL and can help you with that if needed.

I will check that out... thanks for the link!!!

I appreciate the offer; I think I've got the SQL string figured out...

What is clean data and what is not, is the question that we all have to answer (individually) when we prepare data for modeling; unfortunately, the answer will be different for everyone.

The smaller the time frame you use in your AI app, the more sensitive you'll become to bad data. There is a balance between over-scrubbing and under-scrubbing your data. As with any modeling, there is a compromise: you want to remove as many of the bad ticks as possible without removing actual volatility.

An Introduction to High Frequency Finance (co-authored by Mr Olsen himself - OANDA) lays some good ground work for data cleaning when tick data is used for modeling. As you move farther out from the tick, you become less sensative and under/over-scrubbing is less of an issue.

On a different note, the launguage you used to describe your modeling efforts leads me to believe you are working with a brute force application (I won't name it to protect your work). My first suggestion would be, get yourself a distributive grid set up and take advantage of the parallel processing environment. Your going to find it very, very difficult to get a good fit running data through one machine.

Secondly, garbage in, garbage out; your dataset construction is 100 times more important than how you clean the ticks. All the AI algorithms have an inherent cyclical, regressive bias, wich is both good and bad. Most indicators amplifiy this effect, that is bad. Every test I conducted with standard candidate predictors ended up with way to much volatility to be useful as an actual trade model.

Historically, AI on any level has not worked very well in the markets, but I will tell you (based only on my experience) it can be done. It takes time and creativity to get it right, but you can do it... That's pretty much all I can without comprimising my own work!!!

Good luck with your project!!!

green... I gave the site the once over; I've worked with a product like that in the past and found the add-in to be problematic. Calling the function inside Excel created so much bloat in the the spreadsheet it would barely move. It's usually better if you can just code the indicator directly into the sheet itself. There wasn't anything there for data cleaning, but I think I've pretty much got it worked out.

Thanks again for the link though - I'm always looking for new stuff and new thinking!