1
$\begingroup$

Disclosure: I have 0 background in economics, finance or any directly related field.

I have been given multiple sets of supposedly daily tick data (I say supposedly because I could not tell if it wasn't), that I would like to simplify for analysis. I have spent the last couple of hours trying to squeeze the necessary information out of google, but what I found is either too high level or the data in the examples were already pre-filtered/-aggregated.

If possible I would like to simplify the dataset so that I get a relatively accurate representation of inter-day change in ask and bid price.

The data I have currently looks like this (top rows of ~1.5 Million):

enter image description here

The results should, if possible, looks similar to this:

enter image description here

Thanks!

PS: The images are not from the same datasets or source, so they will not match (column names, volume, prices, etc.)

PPS: I'm more than happy to provide additional information, if necessary.

Improve this question
$\endgroup$
4
  • $\begingroup$ It seems you have to make the L1 order book from tick data. I'd check the volume column in the 2nd table and what o means in the first column. once you have that sorted, it should be not very difficult. $\endgroup$
    – nimbus3000
    Mar 28, 2017 at 11:04
  • $\begingroup$ @nimbus3000, thanks for the comment. Could you maybe extend that a bit? $\endgroup$
    – Moritz
    Mar 28, 2017 at 11:07
  • $\begingroup$ if you mean that about the coding bit, I suggest you look online. You might find some implementation of the same. the idea is simple, if the new buy order is at a higher price compared to the existing buy order but lower than the current ask, you get a new level in the bid side of the order book. Works similarly on the sell side. For the volume bit, it must be an integer as long as it isnt normalized (which i've never seen). Plus a bid volume of 0 with logical bid price doesnt make a lot of sense $\endgroup$
    – nimbus3000
    Mar 28, 2017 at 11:17
  • $\begingroup$ @nimbus3000, sorry that still doesn't make much sense to me :\ Any chance you could extend that into a full answer with maybe a mock calculation or recommend some sources that go into more detail and give more background? $\endgroup$
    – Moritz
    Mar 28, 2017 at 11:37

1 Answer 1

Reset to default
1
$\begingroup$

It is worthwhile to understand what the best bid/best ask are. They are prices at which people are ready to trade.

Assume that the present best bid and best ask are at 43.234 and 43.288 with a 100 lots at each level. Now a new buy order comes. A few things can happen:

  1. Buy order price is less than the best bid, so this order doesnt change the best bid price or quantity.

  2. Buy order price = the best bid price. In this case, you update the quantity. If the new order had 5 lots, the quantity changes to 105 lots and the best bid remains same

  3. The new order price > best bid*, so the new best bid becomes this order. Order price = the price of this order and order quantity = this orde quantity.
  4. If the order price in point 3 above is more than the best ask, then a trade happens. Now, the bid side of the book doesnt change but the best ask quantity reduces by a size equal to that of the incoming order.

You can use a similar logic for an incoming sell order.

---Edit for the 4th point above---

there are two aspects

  1. if there is not price specified in the order and the order has a large size. In this case, assume that there are only 1000 lots available to be sold, across the entire order book and the incoming buy order wants to buy 1100 lots. So the guy would buy 1000 lots and clear up the order book. What happens to the rest of the 100 lots is what is decided by the regulator/exchange. In my case, the exchange i trade on, the 100 lots would become standing buy orders at the last trade price. If the incoming order size is less than the total quantity available in the order book, the entire order is filled at the respective prices at which sell orders were available. So is there were 100 lots to be sold at price x, x+1 and so no, the orders will be filled accordingly.

  2. If the orders specified a price and a quantity where the quantity is larger than what is available at best ask. So assume that best bid is 100 and best ask is 101 both have 100 lots available. the level on the buy side has 50 lots with price of 102 and 50 lots with price of 104. The incoming order has a price of 103 and order size of 175. So this guy is ready to buy upto a max price of 103. So this guy would buy 100 lots at 101, 50 lots at 102 and the rest of his order would become a limit order at a price of 103 and size of 25. So L1 order book would look like: best bid at 103, qty at 25 and best ask at 104, qty at 50

@amdopt: thanks for pointing out the flaws.

Improve this answer
$\endgroup$
1
  • 1
    $\begingroup$ I would add some logic to your 4th point to account for a new orders' size being larger than the size that is currently available at the ask. Assuming the new order is not marketable. $\endgroup$
    – amdopt
    Mar 28, 2017 at 13:34

Your Answer

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

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