1
$\begingroup$

I am trying to store incoming tick data from the US equity markets on a database.

What are the most common problems that come up with respect to storing and managing such a large data set. A few that I already know are:
1) Missing data - you can add some dummy data or not include this in backtest.
2) Timestamp doesn't match.
3) Block trades/Cleaning the data.

Is there anything else I might be missing and if so how can I go about resolving them.
The main purpose is to backtest strategies. The data will not be huge - only Level 1 data for around 20 stocks.

|improve this question
$\endgroup$
  • 2
    $\begingroup$ stock splits (and for inflation, index resets). You need to decide how you're going to handle them - you need to decide if you want to be able to go back in time and use the data as if it has/hasn't had the split, so you can accurately replicate histroical trade prices before a split, etc. $\endgroup$ – will Feb 18 '17 at 14:09
  • $\begingroup$ Very true @Will. As far as equities are concerned I would say keeping track of relevant corporate actions (change in capital structure and/or capital distributions) is very important. $\endgroup$ – Quantuple Feb 19 '17 at 9:05
  • $\begingroup$ Also, you mention "cleaning" the data. This is a big challenge in itself and I'd not include it in this. An unrelated bit of advise I'd give you (which is really just programing advice) is to be ultra consistent. Make decisions on navigation conventions and stick to them. Make decisions about treatments of dates, and stick to them. Have tables for things like tenors, and use them instead of things like numbers of days, etc. Consistency will make things much saner later. $\endgroup$ – will Feb 19 '17 at 10:23
  • $\begingroup$ Oh, and also you need to think about the concept of a data source. Ie who generated the data (again put them in a look up db). This also allows you to treat the data cleaner as a source. $\endgroup$ – will Feb 19 '17 at 10:25
  • $\begingroup$ @will I only have one source of data. It's a broker API. $\endgroup$ – novice Feb 19 '17 at 14:22
6
$\begingroup$

I've designed large data stores (sitting on about $3-10M of hardware). The challenges that @jharonfe brought up are valid, but I don't feel they're the most interesting challenges in your case (20 stocks, L1).

For example, there's no reason why your system, even it's very naively designed and unoptimized, should fall behind on 20 symbols.

You also mentioned missing data - you might have seen that issue brought up by some of the older, publicly available texts on how to store market data. Bear in mind these were written years ago - that's not really an issue nowadays when the exchange networks are highly overprovisioned.

Here's a few that are worth thinking about:

  1. Non-homogenous trade sessions. For example, IBM still trades today but there was a period when its ticker stopped trading because of anti-trust investigations. People who design their data storage to handle expirations (e.g. options) tend to think about these issues more, but equities have their own peculiarities. Also, if your equities originate from different exchanges, different markets have different holiday schedules.
  2. Survivorship bias. This is a pretty obvious one. If you know after-the-fact today that you cannot trade XYZ and that's left out from your symbol universe, yet you were collecting data for XYZ 1 year ago, you're incurring survivorship bias by allowing your backtesting engine to be aware of this and be unable to backtest on XYZ 1 year ago or exclude XYZ from your search universe 1 year ago. The reason this gets challenging is that it's easy to describe but it involves interfacing several pieces of software, which people tend to avoid in the first pass of architecting their platform.
  3. Trade breaks. In certain venues, you can get info after-the-print that a trade has become nullified. This poses issues in deciding how to handle the fill in your backtest but people often ignore this type of edge case in their first pass design.
  4. Lossiness and transactional history. As you can see in a common theme between my above 3 points, the solution is often just to ignore the problem and worry about it later. This is fine so long as you don't lose the data when you come around to deal with the problem. This is one of the reasons why using a DBMS can be superior to a flat store, because many DBMSes are transactional and have a commit history.
  5. Corporate actions. Equities go through mergers and acquisitions, stock splits etc.
|improve this answer
$\endgroup$
  • $\begingroup$ Thanks for the answer. Could you please elaborate on your fourth point? $\endgroup$ – novice Feb 19 '17 at 22:59
  • $\begingroup$ Here's 2 very simple examples: (1) Exchanges often purge reference data (e.g. M&A, stock splits, dividends), the more mature exchanges charge a hefty fee for this type of historical data. If you choose not to store it (lossiness), then maybe one day you come up with an event-driven strategy that needs this, and you start storing it, then your research and backtesting system now needs to handle two cases. $\endgroup$ – madilyn Feb 19 '17 at 23:02
  • $\begingroup$ (2) Some market data sources come with additional fields which people often don't store, e.g. periodic refresh of open interest. Often there's a good reason why you don't store it - 1 market might provide it while another might not, so it messes with how you're unifying your software interface. If you choose not to store it (lossiness), then the same problem arises. $\endgroup$ – madilyn Feb 19 '17 at 23:03
  • $\begingroup$ Actually I forgot to provide an example about transactional history: You, or your third party data source, might apply adjustments (e.g. rollover, dividend) to the prices... this could be fine depending on the type of trading you do... but eventually you might want to explore a different kind of strategy, and reverse engineering those changes can be tricky unless you have designed your storage in a transactional way (like your bank statements, you can backtrack to its state 4 days ago, 1 month ago, etc.). $\endgroup$ – madilyn Feb 19 '17 at 23:05
2
$\begingroup$

A couple engineering issues I have encountered when persisting high frequency tick data:

  1. The database can grow quickly. For example, a busy symbol like SPY can have a couple hundred thousand trade events on an typical day. If you track all NBBO quote changes, these are about 2 to 4 times more numerous than trade events. If you track all BBO quote changes (which is included in Level 1 data) then multiply the number of events by the number of market data centers you are tracking. This can be a few million records for one symbol for one day. And on high volatility days, this traffic can double, triple, or more.
  2. There may be times when tick data is arriving faster than you can persist it to the database. Streaming tick data arrives in bursts, and is particularly busy at the market open and close. I have found that a queue processor implementing the producer consumer pattern is the best design to represent this work flow.
  3. In cases where tick data is arriving too quickly and your processor is falling behind, you will want to have bulk insert methods to help your processor catch up.
  4. Designing the data model for intraday data can be tricky. Intraday tick data is different than interday data. On the one hand this is obvious. On the other hand, I find most engineers first work with interday data and encounter intraday data problems second (myself included). Hence they bring their data models from what has worked on interday data, which is natural. Although there are similarities, intraday data often violates many of the assumptions that go into interday data models. My suggestion is in the beginning keep this code as agile as possible. Be deliberate about committing to a data model design. Where possible, leave yourself flexibility to refactor.
|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.