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
2022-12-02 20:17:29.759884 464762540 17001.43 0.029260 sell
2022-12-02 20:17:29.445783 464762539 17001.43 0.003683 sell
2022-12-02 20:17:29.445783 464762538 17001.20 0.001708 sell
2022-12-02 20:17:29.445783 464762537 17001.19 0.004675 sell
2022-12-02 20:17:29.354922 464762536 17001.19 0.000528 sell
... ... ... ... ...
2022-12-02 19:03:25.648902 464737545 16948.68 0.002128 sell
2022-12-02 19:03:25.092162 464737544 16947.64 0.000157 buy
2022-12-02 19:03:25.092162 464737543 16947.65 0.002000 buy
2022-12-02 19:03:24.672383 464737542 16947.64 0.003100 buy
2022-12-02 19:03:24.672383 464737541 16948.10 0.002000 buy

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()