How Limit Orders and Market Orders Change the Best Bid and Ask

# Aggregating/Simplifying Tick Data


# Aggregating/Simplifying Tick Data












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):

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

## 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.

## Answer by nimbus3000 (score 1, accepted)

https://quant.stackexchange.com/a/33303

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:

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

- 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

- 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.

- 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

- 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.

- 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.