Resampling and OHLC in Python

Darwinex provides free historical tick data for various currency pairs. The .csv file contains top of the book, tick-by-tick market data, with fractional pip spreads in millisecond details. This data is more than sufficient for our analysis. I use Quant Data Manager to download the .csv file from the server.

The data that we downloaded will look like this:

Steps In Python

As you can see the data is without any header. We will include the header and accomplish the required task programmatically.

Import pandas package

import pandas as pd

Load the data

Data is stored in my working directory with a name ‘AUDJPY-2016–01.csv’. As we saw earlier, the data is without a header. Hence we would add header to the data while importing it. Thus importing and adding header take place in the same line of code.

data_frame = pd.read_csv('AUDJPY-2016-01.csv', names=['Symbol', 'Date_Time', 'Bid', 'Ask'],
index_col=1, parse_dates=True)
data_frame.head()

This is how the data frame looks like:-

We use the resample attribute of pandas data frame. The resample attribute allows to resample a regular time-series data. We shall resample the data every 15 minutes and divide it into OHLC format. If you want to resample for smaller time frames (milliseconds/microseconds/seconds), use L for milliseconds, U for microseconds, and S for seconds.

data_ask = data_frame['Ask'].resample('15Min').ohlc()
data_bid =data_frame['Bid'].resample('15Min').ohlc()

A snapshot of tick-by-tick data converted into OHLC format can be viewed with the following commands:-

data_ask.head()
data_bid.head()

You may concatenate ask price and bid price to have a combined data frame

data_ask_bid=pd.concat([data_ask, data_bid], axis=1, keys=['Ask', 'Bid'])

The Complete Code

import pandas as pd
data_frame = pd.read_csv('AUDJPY-2016-01.csv', names=['Symbol', 'Date_Time', 'Bid', 'Ask'],
index_col=1, parse_dates=True)
data_frame.head()
data_ask =  data_frame['Ask'].resample('15Min').ohlc()
data_bid =  data_frame['Bid'].resample('15Min').ohlc()
data_ask.head()
data_bid.head()
data_ask_bid=pd.concat([data_ask, data_bid], axis=1, keys=['Ask', 'Bid'])

Conclusion

This was a quick way of computing the OHLC using TBT data. This can be applied across assets and one can devise different strategies based on the OHLC data. We can also plot charts based on OHLC, and generate trade signals.