# How do I convert order book data into OHCL( Open,High,Low,close) format?

The image represents the order book data with columns having following attributes:

a0: Best ASK price (i.e. the lowest posted price at which someone is willing to sell an asset)

b0: Best BID price (i.e. the highest posted price at which someone is willing to buy an asset)

az0: Best ASK size (i.e. the number of lots being offered for selling at the lowest ask price)

bz0: Best BID size (i.e. the number of lots that people are trying to buy at the bid price)

What I want is to convert order book data into OHCL formate (Open, High, Low and Close). The other information that I have is the following:

Features:
atv: feature representing one fraction of trading volume ( in number of lots )

btv: feature representing another fraction of trading volume ( in number of lots )

(atv + btv = total number of trades in the day so far)

tbq: sum of all the BID ( buy ) sizes in the market

tsq: sum of all the ASK ( sell ) sizes in the market

All the above variables are for a derivative instrument 2.

• I am new to Stak Exchange and I have added the relevant image which is it is not showing in the post. You can find the image in the following link: dropbox.com/s/138iwzhrky5us59/order_book_data.png?dl=0 – Vijay Kumar Jun 2 '18 at 18:47
• Strictly speaking OHLC is generated from transaction data (prices at which trades occurred), not from Bid Ask data. Do you have information about trades in this dataset? – Alex C Jun 2 '18 at 22:17
• Could you please provide the method to compute OHCLin the context of transaction data? I have only been provided the Ask price and Bid prize in order to compute OHCL. The other information that I have is the following : Features: atv: feature representing one fraction of trading volume ( in number of lots ) btv: feature representing another fraction of trading volume ( in number of lots ) (atv + btv = total number of trades in the day so far) tbq: sum of all the BID ( buy ) sizes in the market tsq: sum of all the ASK ( sell ) sizes in the market – Vijay Kumar Jun 2 '18 at 23:24
• You read the file sequentiallly, examining the timestamp in each row. If time stamp is less than current interval end, you use the data in this row to update current o,h,l,c values, else (if time stamp > interval end) you output the previous o,h,l,c, and start collecting information for the new interval, re-initializing o,h,l,c and updating the interval end time (incrementing it by 1 hour for ex, if intervals are 1 hour long). At the end-of-file, you output the last o,h,l,c values. (If you don't have trade prices, you might use (bid+ask)/2 for open and close , ask for high and bid for low). – Alex C Jun 3 '18 at 4:34

As Alex C. notes, OHLC bars are meant to be calculated using transaction ticks. However, you could try to make bars from bid/ask individually (or perhaps even the mean of the two as an approximation), but bear in mind that they are not the 'real thing'.

But assuming you acquire transaction data, there are a number of possible methods for forming OHLC bars (roughly in increasing order of quality, but also implementation difficulty).

1. Time bars – this is the default method. Essentially, you decide on a fixed amount of time (for example we will use 1-min bars), then split your dataset into subsets corresponding to every 1 minute interval. Each 1 minute interval will have a different number of ticks. The first tick in each 1 minute bar is the Open, the last tick is the 'close', and high/low are self-explanatory. Although time bars are the most popular, time-sampled series often have poor statistical properties like serial correlation, non-normality etc.

2. Tick bars – these sample every time x transactions occur (e.g every 1000 ticks). The main advantage over time bars is that market information is not produced at a constant frequency (e.g more trades occur at market open), so time bars do not accurately capture information flow. Tick bars don't suffer from this problem, and empirically produce timeseries with better statistical properties – the resulting returns are closer to i.i.d Gaussians, which is an assumption of many models.

3. Volume bars – minor improvement over tick bars, in which you sample every time x units/dollars of the asset are traded: this takes into account the fact that ticks are different sizes. This will allow for better analysis of price-volume action, and return distributions should be closer to i.i.d Gaussians.

4. Tick Imbalance bars – getting a little bit more complex now, but essentially these bars sample when there is asymmetric information. Roughly speaking, it samples every time there is a certain level of imbalance in a series of ticks (net buy or sell).

5. Volume Imbalance bars – similar to tick imbalance bars, but sample when there is a volume imbalance.

6. Tick Runs Bars – sample whenever the sequence of ticks diverges from expectations.

Assuming you have a dataframe of price/volume transaction tick data, here is some example python code (a bit ugly) to form tick bars where period is the number of ticks in a bar:

all_bars = []
for _, bar in price_volume.groupby(np.arange(len(price_volume)) // period):
open_price = bar['price'][0]
high_price = bar['price'].max()
close_price = bar['price'][-1]
low_price = bar['price'].min()
volume = bar['volume'].sum()
timestamp = bar.index[-1]
all_bars.append([timestamp, open_price,
high_price, close_price,
low_price, volume])

ohlc = pd.DataFrame(all_bars, columns=['timestamp', 'open',
'high', 'low',
'close', 'volume'])