4.4. Crypto triangular arbitrage using ASOF JOIN

4.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.

4.4.1. Preparation

4.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.

4.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.

4.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.

4.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
2022-04-05 14:41:33.045032 309037484 45910.11 0.000045 sell
2022-04-05 14:41:32.981606 309037483 45909.22 0.014721 buy
2022-04-05 14:41:32.981606 309037482 45909.22 0.006634 buy
2022-04-05 14:41:32.981606 309037481 45909.22 0.001000 buy
2022-04-05 14:41:32.981606 309037480 45909.23 0.001000 buy
... ... ... ... ...
2022-04-05 14:07:49.943230 309012489 46154.01 0.012373 buy
2022-04-05 14:07:49.943230 309012488 46156.69 0.001927 buy
2022-04-05 14:07:49.943230 309012487 46156.70 0.001000 buy
2022-04-05 14:07:49.890882 309012486 46159.93 0.007450 sell
2022-04-05 14:07:49.834396 309012485 46161.89 0.000750 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()