April 10, 2023 -

Open-High-Low-Close (OHLC) Coin Analytics

Akadenia recently had the opportunity to develop an API to provide Open-High-Low-Close (OHLC) data for Binance Smart Chain (BSC) and Ethereum (ETH) exchanges.

We tackled this project with a mental model based on the New York Stock Exchange. There, the opening and closing bell signal the official start and end of the trading day. So it is straightforward to determine the open price and the close price each day. Another property of the NYSE is that all assets are traded for US Dollars (USD). The rules to calculate OHLC are straightforward.

Analyzing cryptocurrency exchanges is not as straightforward. There is no standard trading day. This can make it difficult to compare data sources as it is not immediately obvious if open-high-low-close prices for a given coin on a given day are presented in my local time zone, UTC, or some other time zone, like that of the coin's headquarters. Additionally, cryptocurrencies are not directly traded for fiat currencies. Instead, traders swap coins in much the same way foreign exchange traders do. In fact, a foreign exchange is more representative of a crypto exchange.

Approach

For this project we were concerned only with Ethereum (ETH) and Binance Smart Chain (BSC) networks. Those networks use smart contracts to enable one coin to be swapped for another. OHLC data can be derived from the transactions on any smart contract. However, the amount of each transaction is recorded as the number of coins that were swapped. In order to present meaningful data and compare the value of coins across networks these values need to be converted into a common currency; in this case, USD.

Ethereum (ETH) and Binance (BNB) both have what's called stablecoins (e.g. USDT and USDC) that can be used as a proxy for USD. Unfortunately not all coins are paired directly with these stablecoins. Even when they are, these smart contracts typically trade at lower volumes that smart contracts that pair altcoins with the network's native currency. In order to be traded with altcoins, native currencies like BNB and ETH must comply with BEP20 and ERC20 respectively. This is done through a process called wrapping, and generates the wBNB and wETH tokens. These are paired with most, if not all tokens on the BSC and Ethereum networks, including the stable tokens, like USDT.

As such, wBNB and wETH, can be used as an intermediate currency to convert altcoin values to USD. At the time this post was written GHC (Galaxy Heroes Coin) traded at 5728560111 for 0.59999961 wBNB. To get the value of GHC in wBNB we divide the amount of wBNB by the amount of GHC it was traded for:

Equation of GHC

Then we can use the current price of BNB in USD to convert to dollars:

BNB to USD

Using this technique we can calculate the value of GHC in USDT at any point in time so long as we can query a wBNB/GHC and USDT/wBNB trade at that time. So if we set our open time to 00:00:00.000Z and our close time to 23:59:59.999Z we can get the first set of trades that occurs after the open time and the last set of trades that occurs before the close time and use that data to calculate open and close USD price.

Calculating high and low is a little more tricky since you have to find the maximum and minimum prices wBNB/GHC traded for and find the corresponding trades for USDT/wBNB. You can simplify this by using the average price for USDT/wBNB that day.

Unfortunately, this technique does not scale well, and our client intended to use the OHLC data to create a chart. Each chart displays tens or hundreds of OHLC data values over user configurable time intervals and durations.

OHLC Chart

Scanning thousands of transactions to locate high and low token values would be time consuming and impractical. It would likewise be impractical to use an API service like bscscan or etherscan to build a database of open-high-low-close data for every smart contract in the exchange as this would generate a large number of API calls. One might as well connect a light node to the ETH and BSC blockchains, collect the transaction data, do the analysis, and build up a blockchain data service.

Implementation

Our client's timeline was days, not weeks, so none of these options were particularly viable. Fortunately, bitquery.io has developed a powerful set of GraphQL APIs that provide a rich set of data across, according to their website, 30 blockchains. They provide an online playground with many example queries developed by their user community as well as a forum where you can get quick answers to popular questions. What bitquery lacks is documentation. The GraphQL schema and online playground make it easy to discover the available data fields and build queries. However it can be challenging to interpret what information those data values represent. Furthermore, some fields are only available when specific arguments are provided. It quickly becomes a time consuming game of trial and error to find the right combination of arguments and fields to get at the desired information. This is especially frustrating because some queries take a particularly long time to run and inexplicably break with particular combinations of fields and arguments.

Difficulties validating query results compound the issues with API ambiguity. Since the canonical data source is the blockchain, it is difficult to determine if the derived data provided by any API service is correct. There are many online crypto dashboards, however most of them are built on bscscan. Having only one other data source to compare to makes it impossible to determine which data source is in error when discrepancies arise. In addition, it can be presumed that each service analyzes the blockchain at different intervals. So it is reasonable that small discrepancies will always be present between APIs that update frequently. In some cases, these discrepancies can make it difficult to determine whether or not you are actually comparing corresponding fields.

Querying OHLC data is a prime example of these difficulties. There are several fields related to the price of a coin. Some of them are buyAmount, sellAmount, tradeAmount, and price. Through a long process of trial and error we determined that price is the best option for querying open, high, low, and close values. The price field cannot be converted to USD via the API, and the values are expressed in terms of the sell currency. This can make these fields confusing to work with. For each time interval there are two sets of open-high-low-close values, the buys and the sells.

   {
       "ethereum": {
           "dexTrades": [
               {
                   "timeInterval": {
                       "day": "2021-10-02"
                   },
                   "buyCurrency": {
                       "symbol": "GHC",
                       "address": "0x683fae4411249ca05243dfb919c20920f3f5bfe0"
                   },
                   "sellCurrency": {
                       "symbol": "WBNB",
                       "address": "0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c"
                   },
                   "buyAmount": 52629760342133.35,
                   "sellAmount": 1330.2831722651576,
                   "trades": 757,
                   "maximum_price": 3.012810811470441e-11,
                   "minimum_price": 3.3010006032419705e-14,
                   "open_price": "2.1849808222089758e-11",
                   "close_price": "1.9809589988794388e-11"
               },
               {
                   "timeInterval": {
                       "day": "2021-10-02"
                   },
                   "buyCurrency": {
                       "symbol": "WBNB",
                       "address": "0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c"
                   },
                   "sellCurrency": {
                       "symbol": "GHC",
                       "address": "0x683fae4411249ca05243dfb919c20920f3f5bfe0"
                   },
                   "buyAmount": 1209.3644319967595,
                   "sellAmount": 46850409338900.555,
                   "trades": 1140,
                   "maximum_price": 50436477243.59607,
                   "minimum_price": 33018375186.852077,
                   "open_price": "45531754484.664116",
                   "close_price": "50033796242.784676"
               }
           ]
       }
   }

You can see in the example response above that we have a set of values for buying GHC, and a second set for selling GHC. From here it is possible to convert the GHC values to WBNB and compare the buys to the sells and determine which represents the open, high, low and close for the interval. The next step is do do the same query for USDT/WBNB and use those values to convert the GHC OHLC to USDT.

Experiment for yourself at:

https://graphql.bitquery.io/ide/d0xSBFG0Qz#

Overall the bitquery GraphQL API simplifies the complex problem of generating OHLC data for smart contracts. The blog articles, GraphQL playground examples, and forum provide valuable resources for designing queries. These benefits outweigh the annoyances of poor documentation and enable developers to focus on visualization rather than the hard problem of data mining a crypto blockchain.

Engineering Team

Written by Engineering Team

Development

More posts

Bits, Bytes and Qubits—Here Comes the Quantum Computer

Exploring the Quantum Frontier: Journey into advanced computing

Checkout this post

Customizable map styles in React Native and Mapbox

Mapbox Guides: A look into map styles

Checkout this post