Processing Large Order-Book Event Data to Measure Spreads
Summary
The document describes a research problem involving hundreds of trading days of equity order-event records, with each day containing tens of millions of rows. The events include placements, cancellations, and trades, so the researcher must reconstruct the evolving order book before calculating each ticker’s average intraday spread. This differs from working with periodic snapshots: best quotes can change when a better order arrives, when the best order is executed, or when it is canceled.
The response recommends using a SQL-oriented system and suggests SAS, citing its documentation and established use in finance. It also proposes processing separate days in parallel across a computing cluster, while noting that this requires cluster access and can be expensive. The answer does not compare database engines, provide an order-book reconstruction algorithm, or report performance benchmarks. Its advice is therefore a high-level infrastructure suggestion rather than a tested solution to the spread calculation itself.
Key ideas
- Dynamic order-event data must be replayed to reconstruct the order book before spreads can be measured.
- The event stream includes order placements, cancellations, and executions that can each alter the best quote.
- The response recommends a SQL-oriented workflow and specifically suggests SAS for large-scale processing.
- Independent trading days can be processed in parallel, subject to available cluster resources and cost.
Tags
Full text
# What program should I use to handle large high frequency data? # What program should I use to handle large high frequency data? I am going to do research about exchange. For this purpose I get daily data for all stocks to reconstruct order book. I have data on 900 trading days. Each day have data about ticker, timestamp, action - B or S, order ID, type of action - 0 cancel order, 1 place order, 2 deal, price, volume. Each day consists of about 50 million lines, so it takes a long time to do it in Python. I tried to use QuestDb and for now it looks like a best option for me. Perhaps there are some other extensions over SQL? Or some special languages to work with this data type? As a result, I want to calculate the average spread for each ticker during the day. For this I need to restore order book, I tried to do it in Python, but because I have not snapshot, but dynamic data in which I need to take into account 3 actions to calculate the spread - someone put a better order, someone executed a market order, the owner of the best order canceled it. Due to the fact that I have big data it is difficult to take everything into account when calculating the spread. What is the best way to approach this issue? ## Answer by phdstudent (score 1) https://quant.stackexchange.com/a/78506 The right thing is indeed some SQL type language. I strongly recommend SAS since it has good documentation and people have been using it in finance for a long time. With 45 billion data points that's your best bet. If you have access to a cluster you can do it in parallel (i.e. you load each day at a time into each node of the cluster). So you can do 900 at a time. This should be the fastest way, but you need to have access to a cluster that allows you to do this (which can get expensive). If you give me an estimate of how long it takes to run the average for 1 day using 1 core and how much ram memory it requires, I/you can get an estimate of an AWS cost for the task.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.