[PLUS] Transform Binance Trade Prints into Tick OHLCV Data With Custom Tick Size in Python
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)
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.
This is a premium post. Create Plus+ Account to view the live, working codebase for this article.
Notice: Information contained herein is not and should not be construed as an offer, solicitation, or recommendation to buy or sell securities. The information has been obtained from sources we
believe to be reliable; however no guarantee is made or implied with respect to its accuracy, timeliness, or completeness. Author does not own the any crypto currency discussed. The information
and content are subject to change without notice. CryptoDataDownload and its affiliates do not provide investment, tax, legal or accounting advice.
This material has been prepared for informational purposes only and is the opinion of the author, and is not intended to provide, and should not be relied on for, investment, tax, legal,
accounting advice. You should consult your own investment, tax, legal and accounting advisors before engaging in any transaction. All content published by CryptoDataDownload is not an
THE PERFORMANCE OF TRADING SYSTEMS IS BASED ON THE USE OF COMPUTERIZED SYSTEM LOGIC. IT IS HYPOTHETICAL.
PLEASE NOTE THE FOLLOWING DISCLAIMER.
CFTC RULE 4.41: HYPOTHETICAL OR SIMULATED PERFORMANCE RESULTS HAVE CERTAIN LIMITATIONS. UNLIKE AN ACTUAL
PERFORMANCE RECORD, SIMULATED RESULTS DO NOT REPRESENT ACTUAL TRADING. ALSO, SINCE THE TRADES HAVE NOT BEEN
EXECUTED, THE RESULTS MAY HAVE UNDER-OR-OVER COMPENSATED FOR THE IMPACT, IF ANY, OF CERTAIN MARKET FACTORS,
SUCH AS LACK OF LIQUIDITY. SIMULATED TRADING PROGRAMS IN GENERAL ARE ALSO SUBJECT TO THE FACT THAT THEY ARE
DESIGNED WITH THE BENEFIT OF HINDSIGHT. NO REPRESENTATION IS BEING MADE THAT ANY ACCOUNT WILL OR IS LIKELY
TO ACHIEVE PROFIT OR LOSSES SIMILAR TO THOSE SHOWN. U.S. GOVERNMENT REQUIRED DISCLAIMER: COMMODITY FUTURES
TRADING COMMISSION. FUTURES AND OPTIONS TRADING HAS LARGE POTENTIAL REWARDS, BUT ALSO LARGE POTENTIAL RISK.
YOU MUST BE AWARE OF THE RISKS AND BE WILLING TO ACCEPT THEM IN ORDER TO INVEST IN THE FUTURES AND OPTIONS MARKETS.
DON’T TRADE WITH MONEY YOU CAN’T AFFORD TO LOSE. THIS IS NEITHER A SOLICITATION NOR AN OFFER TO BUY/SELL FUTURES
OR OPTIONS. NO REPRESENTATION IS BEING MADE THAT ANY ACCOUNT WILL OR IS LIKELY TO ACHIEVE PROFITS OR LOSSES
SIMILAR TO THOSE DISCUSSED ON THIS WEBSITE. THE PAST PERFORMANCE OF ANY TRADING SYSTEM OR METHODOLOGY IS NOT
NECESSARILY INDICATIVE OF FUTURE RESULTS.