0
$\begingroup$

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?

$\endgroup$

1 Answer 1

1
$\begingroup$

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.

$\endgroup$
1
  • $\begingroup$ Yeah, I think it will be expensive to do via clusters. I am doing this as a non-profit project. $\endgroup$
    – rallen2lk
    Commented Feb 29 at 8:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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