Overview
Tick data in financial markets is often used because it provides detailed and granular information about the trades and quotes occurring in the market. It captures every individual transaction (tick) that takes place, including the price, volume, and time of each trade. From a charting perspective, it changes the x axis of the series from the concept of time (most charts) to a representation of trading activity/volumes (in the form of ticks). In tick OHLC charts, more activity corresponds to an increase number of tick intervals (based on the tick size). How does this work? OHLCV ticks are created by simple trade counts. If my "tick interval" or "trade count size" is 144, this means that every OHLCV candle is represented by the open, high, low, close of 144 sequential trades. This makes trade counts (market activity) very important, and can help magnify areas of high activity and price conviction. Tick data is also frequently used in algorithmic and systematic back testing strategies. This article will be one of our most advanced from a coding perspective as we are going to download Binance BTCUSDT historical trade print data from the official repository, save it to a local SQLite database, then transform the raw transaction data into Open, High, Low, Close and Volume (OHLCV) tick data by trade counts using a combination of SQL and Python.Transforming Trade to Tick
You can think of the transformation process as aggregating trades together by in groups of the set tick size, and then finding the first, max, min, and last from that grouping. Starting with trade number 1 on the day, we set the "Open" equal to the price of trade #1. The low and high and close are also set to the price of trade #1 at this time. We will then inspect the 2nd trade of the day and compare it to our OHLC values that we just set. Is the price of trade #2 greater than our "High"? If yes, then we have a new temporary "high" that equals the price of transaction two. We do the same for Lows. (At this stage, the "open" price is already set to transaction #1). Now we do this for the first 144 trades. On trade 144, we set the "Close" of the period equal to the price of trade #144. We now have our OHLC values for a single, 144 tick period. We will then continue through the rest of the transactions creating as many OHLC candlestick bars as there are even numbers of 144 transactions. Important to remember that you cannot have a candlestick until there are at least 144 trades to group together. If you had 1440 raw trade print transactions, you could create exactly 10 OHLC candlesticks. If you had 1500 trade prints, you could still only create 10 OHLC candlesticks (you would need 1440+144=1584 transactions to create 11 OHLC candles)The Code
In this script, we will use a class object to do the multiple steps (previously we've only written functions). It can be argued that an object oriented style of the programming makes it easier to use and import this functionality into other scripts (more portable). First step is to download the data from Binance, which we've covered here. Once the data is downloaded and saved, we transform it to ticks and save to another table. The script is very customizable, and already accepts parameters for 1) Symbol 2) Date 3) Tick Size. We use a database because the data for just a few days can easily grow to over 150mb in size! (depending on tick size and trading activity from the respective day)**BONUS**
Script already has ability to download multiple days of data for any cryptocurrency pair on Binance and will automatically create new OHLCV tick data from where it left off previously!Each line of code and thought process will be explained line by line ... as always ... to modify to your purpose or benefit.