5.4. Crypto triangular arbitrage using ASOF JOIN

5.4. Crypto triangular arbitrage using ASOF JOIN

From Investopedia:

Triangular arbitrage is the result of a discrepancy between three foreign currencies that occurs when the currency’s exchange rates do not exactly match up.

In this guide we are going to see how you can leverage advanced joining capabilities of Quasar to find triangular arbitrage opportunities in cryptocurrencies.

More precisely, in this guide, we will:

  • Craft a suitable data model for cryptocurrency trade data

  • Capture and store the data into a Quasar cluster

  • Query and plot trade data for a single cryptocurrency

  • Query, align, and plot trade data for multiple single cryptocurrencies

We will be using Python, Pandas, and plotly. You can achieve similar results with any programming language or tool that can query Quasar.

5.4.1. Preparation

5.4.1.1. Install and launch QuasarDB

Please install & launch QuasarDB for your environment; the free community edition is sufficient.

For more information and instructions on how to install QuasarDB, please refer to our installation guide.

5.4.1.2. Prepare your Jupyter notebook

Please follow the steps below to create and set up your Jupyter notebook:

# Install requirements
$ python3 -m pip install jupyter \
                         pandas \
                         numpy \
                         quasardb

# Launch local notebook
$ jupyter notebook ./qdb-howto.ipynb

[I 17:16:02.496 NotebookApp] Serving notebooks from local directory: /home/user/qdb-howto/
[I 17:16:02.496 NotebookApp] Jupyter Notebook 6.4.6 is running at:
[I 17:16:02.496 NotebookApp] http://localhost:8888/?token=...
[I 17:16:02.496 NotebookApp]  or http://127.0.0.1:8888/?token=...
[I 17:16:02.496 NotebookApp] Use Control-C to stop this server and shut down all kernels (twice to skip confirmation).

A new jupyter notebook should automatically open, otherwise please manually navigate and you can navigate your browser to http://localhost:8888/ with the environment prepared.

You are now completed with the preparations.

5.4.2. Triangular arbitrage

Triangular arbitrage is done by analyzing the discrepancy between three currencies. We will look at USD, BTC, and ETH in our example.

In theory, converting from ETH to USD, should be equivalent to converting from ETH to BTC, and then BTC to USD. However, in carefully analyzing rates, one can find opportunities that result in small, consistent, and low-risk profit.

To find these opportunities, you need to analyze changes in rates and execute trades as soon as the discrepancy is large enough to profit.

This guide will focus on collecting data and ensuring the values are aligned to plot curves of each rate to find arbitrage opportunities. We will not take into account fees.

Warning

This guide is for educational purposes only. In particular, it doesn’t take into account trading fees and relies on trade data. We offer no guarantee regarding its accuracy or completeness.

5.4.3. Getting started

Coinbase offers a free, public API to pull realtime trade data from. For this demonstration, we will be retrieving realtime trade data for three different pairs:

  • BTC-USD

  • ETH-BTC

  • ETH-USD

Here is how we define a function to load the data for a single symbol into a Pandas Dataframe:

import numpy as np
import pandas as pd
import requests
import io

def pull_coinbase(product, max_pages=25):
    after = None

    xs = list()

    for i in range(max_pages):
        if after is None:
            url = 'https://api.pro.coinbase.com/products/{}/trades'.format(product)
        else:
            url = 'https://api.pro.coinbase.com/products/{}/trades?after={}'.format(product, after)

        r = requests.get(url, timeout=30)
        xs.extend(r.json())

        if not r.headers.get('cb-after'):
            break
        else:
            after = r.headers['cb-after']

        dtypes = {'time': np.dtype('datetime64[ns]'),
                  'price': np.dtype('float64'),
                  'size': np.dtype('float64'),
                   'side': np.dtype('U')}

    return pd.DataFrame(xs).astype(dtypes).set_index('time').reindex()

We can invoke this function to load BTC-USD symbol data:

btcusd = pull_coinbase('BTC-USD')
btcusd
trade_id price size side
time
2023-03-19 05:10:54.615872 511106814 27226.73 0.001913 sell
2023-03-19 05:10:54.559560 511106813 27226.73 0.000088 sell
2023-03-19 05:10:54.498673 511106812 27226.72 0.001746 sell
2023-03-19 05:10:53.091448 511106811 27225.20 0.000871 buy
2023-03-19 05:10:50.590936 511106810 27223.80 0.000232 sell
... ... ... ... ...
2023-03-19 02:39:11.540177 511081819 27236.93 0.022044 buy
2023-03-19 02:39:11.042499 511081818 27237.77 0.000927 buy
2023-03-19 02:39:09.835830 511081817 27240.93 0.001817 sell
2023-03-19 02:39:09.823034 511081816 27238.21 0.000369 buy
2023-03-19 02:39:09.032705 511081815 27240.93 0.003495 sell

25000 rows × 4 columns

This data looks like this:

import plotly.express as px
fig = px.line(x=btcusd.index, y=btcusd.price)
fig.update_layout(template='plotly_dark',
                  title='BTC-USD')

fig.show()