{ "cells": [ { "cell_type": "markdown", "id": "8b4405f3-ff20-45fc-b83b-8ab976285d28", "metadata": {}, "source": [ "\n", "\n", "Exchange order book data is one of the most foundational data types in the crypto asset industry— arguably, even more foundational than trades data, as two orders must be matched for a trade to occur. Order book data is useful for various entities, including market makers, systematic or quantitative traders, and funds studying trade execution patterns. The Coin Metrics **Market Data Feed** offering includes various API endpoints that allow users to retrieve order book snapshots and updates across a collection of top crypto exchanges. \n", "\n", "## Resources\n", "\n", "This notebook demonstrates basic functionality offered by the Coin Metrics Python API Client and Market Data Feed.\n", "\n", "Coin Metrics offers a vast assortment of data for hundreds of cryptoassets. The Python API Client allows for easy access to this data using Python without needing to create your own wrappers using `requests` and other such libraries.\n", "\n", "To understand the data that Coin Metrics offers, feel free to peruse the resources below.\n", "\n", "- The [Coin Metrics API v4](https://docs.coinmetrics.io/api/v4) website contains the full set of endpoints and data offered by Coin Metrics.\n", "- The [Coin Metrics Product Documentation](https://docs.coinmetrics.io/info) gives detailed, conceptual explanations of the data that Coin Metrics offers.\n", "- The [API Spec](https://docs.coinmetrics.io/python-api-client/reference) contains a full list of functions." ] }, { "cell_type": "markdown", "id": "16c3476d-c33a-421e-811e-344b59735ec0", "metadata": {}, "source": [ "## Notebook Setup" ] }, { "cell_type": "code", "execution_count": 1, "id": "7fbd2927-9184-41fd-8403-b259146ee1a2", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:35.989267Z", "start_time": "2022-10-31T14:50:34.313000Z" }, "tags": [] }, "outputs": [], "source": [ "import os\n", "from os import environ\n", "import sys\n", "import pandas as pd\n", "import numpy as np\n", "import seaborn as sns\n", "import logging\n", "from datetime import date, datetime, timedelta\n", "from coinmetrics.api_client import CoinMetricsClient\n", "import json\n", "import logging\n", "from pytz import timezone as timezone_conv\n", "from datetime import timezone as timezone_info\n", "import matplotlib.ticker as mticker\n", "from matplotlib.ticker import ScalarFormatter\n", "import matplotlib.pyplot as plt\n", "import matplotlib.dates as mdates\n", "%matplotlib inline\n", "import ast\n", "import plotly.express as px\n", "from tqdm import tqdm\n", "from plotly import graph_objects as go" ] }, { "cell_type": "code", "execution_count": 2, "id": "6ab06f8c-e7f4-41b4-b92e-18a3de36a427", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:35.995348Z", "start_time": "2022-10-31T14:50:35.991570Z" }, "tags": [] }, "outputs": [], "source": [ "sns.set_theme()\n", "sns.set(rc={'figure.figsize':(12,8)})" ] }, { "cell_type": "code", "execution_count": 3, "id": "abc14b24-03f4-457c-94aa-0c33ff01c79b", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.010493Z", "start_time": "2022-10-31T14:50:35.997480Z" }, "tags": [] }, "outputs": [], "source": [ "logging.basicConfig(\n", " format='%(asctime)s %(levelname)-8s %(message)s',\n", " level=logging.INFO,\n", " datefmt='%Y-%m-%d %H:%M:%S'\n", ")\n", "now = datetime.utcnow()\n", "last_day_date_time = now - timedelta(hours = 24)" ] }, { "cell_type": "code", "execution_count": 4, "id": "ecbe22c4-ee5a-4130-bc7f-9bf9a61b4bc1", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.054963Z", "start_time": "2022-10-31T14:50:36.047101Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "2024-09-16 16:02:07 INFO Using API key found in environment\n" ] } ], "source": [ "# We recommend privately storing your API key in your local environment.\n", "try:\n", " api_key = environ[\"CM_API_KEY\"]\n", " logging.info(\"Using API key found in environment\")\n", "except KeyError:\n", " api_key = \"\"\n", " logging.info(\"API key not found. Using community client\")\n", "\n", "client = CoinMetricsClient(api_key)" ] }, { "cell_type": "markdown", "id": "3f309b3d-ef46-478b-b2da-dfdbb660e819", "metadata": {}, "source": [ "# Order Book Depth" ] }, { "cell_type": "markdown", "id": "3aa070cf-a22c-450a-adf6-16d9f2995de3", "metadata": {}, "source": [ "Coin Metrics collects and serves 3 types of order book snapshots.\n", "- One type (*depth_limit=100*) consists of a snapshot of the top 100 bids and top 100 asks taken once every 10 seconds for major markets. \n", "- The second type (*depth_limit=10pct_mid_price*) includes all levels where the price is within 10 percent of the midprice taken once every 10 seconds. \n", "- The third type (*depth_limit=full_book*) consists of a full order book snapshot (every bid and every ask) taken once every hour for all markets that we are collecting order book data for . All of these snapshots are served through our HTTP API endpoint /timeseries/market-orderbooks." ] }, { "cell_type": "code", "execution_count": 5, "id": "9fba75a1-83b2-47ae-9c51-de449f0f46c7", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.063782Z", "start_time": "2022-10-31T14:50:36.058738Z" } }, "outputs": [], "source": [ "def get_order_books(market,start_time,end_time,depth_limit='full_book'):\n", " df = client.get_market_orderbooks(markets=market,\n", " start_time=start_time,\n", " end_time=end_time,\n", " depth_limit=depth_limit).to_dataframe()\n", " df.sort_values(by='time',inplace=True)\n", " return df" ] }, { "cell_type": "code", "execution_count": 6, "id": "6e555cf3-473a-4801-8b69-659837788b38", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.477152Z", "start_time": "2022-10-31T14:50:36.066270Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
markettimecoin_metrics_idasksbidsdatabase_time
163coinbase-btc-usd-spot2022-10-26 19:00:00+00:0048876152429[{'price': '20682.21', 'size': '0.001'}, {'pri...[{'price': '20680.45', 'size': '0.01098864'}, ...2022-10-26 19:02:04.331554+00:00
164coinbase-btc-usd-spot2022-10-26 20:00:00+00:0048880595574[{'price': '20777.57', 'size': '0.00182293'}, ...[{'price': '20776.49', 'size': '0.01019999'}, ...2022-10-26 20:01:16.705315+00:00
165coinbase-btc-usd-spot2022-10-26 21:00:00+00:0048885094324[{'price': '20748.12', 'size': '0.004'}, {'pri...[{'price': '20747.05', 'size': '0.00207858'}, ...2022-10-26 21:00:31.916195+00:00
166coinbase-btc-usd-spot2022-10-26 22:00:00+00:0048888140842[{'price': '20741.88', 'size': '0.47814895'}, ...[{'price': '20741.87', 'size': '0.00007231'}, ...2022-10-26 22:02:24.185607+00:00
167coinbase-btc-usd-spot2022-10-26 23:00:00+00:0048892147093[{'price': '20819.81', 'size': '0.03138949'}, ...[{'price': '20817.37', 'size': '0.09902'}, {'p...2022-10-26 23:01:42.564517+00:00
\n", "
" ], "text/plain": [ " market time coin_metrics_id \\\n", "163 coinbase-btc-usd-spot 2022-10-26 19:00:00+00:00 48876152429 \n", "164 coinbase-btc-usd-spot 2022-10-26 20:00:00+00:00 48880595574 \n", "165 coinbase-btc-usd-spot 2022-10-26 21:00:00+00:00 48885094324 \n", "166 coinbase-btc-usd-spot 2022-10-26 22:00:00+00:00 48888140842 \n", "167 coinbase-btc-usd-spot 2022-10-26 23:00:00+00:00 48892147093 \n", "\n", " asks \\\n", "163 [{'price': '20682.21', 'size': '0.001'}, {'pri... \n", "164 [{'price': '20777.57', 'size': '0.00182293'}, ... \n", "165 [{'price': '20748.12', 'size': '0.004'}, {'pri... \n", "166 [{'price': '20741.88', 'size': '0.47814895'}, ... \n", "167 [{'price': '20819.81', 'size': '0.03138949'}, ... \n", "\n", " bids \\\n", "163 [{'price': '20680.45', 'size': '0.01098864'}, ... \n", "164 [{'price': '20776.49', 'size': '0.01019999'}, ... \n", "165 [{'price': '20747.05', 'size': '0.00207858'}, ... \n", "166 [{'price': '20741.87', 'size': '0.00007231'}, ... \n", "167 [{'price': '20817.37', 'size': '0.09902'}, {'p... \n", "\n", " database_time \n", "163 2022-10-26 19:02:04.331554+00:00 \n", "164 2022-10-26 20:01:16.705315+00:00 \n", "165 2022-10-26 21:00:31.916195+00:00 \n", "166 2022-10-26 22:02:24.185607+00:00 \n", "167 2022-10-26 23:01:42.564517+00:00 " ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "market = 'coinbase-btc-usd-spot'\n", "start_time = '2022-10-20'\n", "end_time = '2022-10-26'\n", "depth_limit = 'full_book'\n", "\n", "df = get_order_books(market,start_time,end_time,depth_limit)\n", "df.tail()" ] }, { "cell_type": "code", "execution_count": null, "id": "1dfa3049-3404-4830-a008-9f71f25183d2", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.479436Z", "start_time": "2022-10-31T14:50:34.173Z" } }, "outputs": [], "source": [ "def get_depth(df_orderbook,within=2):\n", " \"\"\"\n", " Takes orderbook as returned by API and returns\n", " cumulative qty bid/offered at each snapshot and where the liquidity is: how far from best\n", " \"\"\"\n", " dfs=[]\n", " for row in df_orderbook.itertuples():\n", " timestamp_ = row.time\n", " #asks\n", " asks = pd.DataFrame(ast.literal_eval(row.asks))\n", " asks[\"price\"]=asks.price.apply(float)\n", " best_ask = float(asks.price.min())\n", " asks[\"size\"]=asks['size'].apply(float)*-1\n", " asks[\"percent_from_best\"]=((asks.price/best_ask)-1)*100\n", " #asks[\"best_ask\"] = best_ask\n", " asks[\"time\"] = timestamp_\n", " asks[\"side\"] = \"ask\"\n", " asks[\"position\"] = range(len(asks))\n", " asks[\"cumulative_vol\"] = asks['size'].cumsum()\n", " asks[\"size_usd\"] = asks[\"size\"] * asks[\"price\"]\n", " asks[\"cumulative_vol_usd\"] = asks.size_usd.cumsum()\n", " #bids\n", " bids = pd.DataFrame(ast.literal_eval(row.bids))\n", " bids[\"price\"]=bids.price.apply(float)\n", " best_bid = float(bids.price.max())\n", " bids[\"size\"]=bids['size'].apply(float)\n", " bids[\"percent_from_best\"]=abs(((bids.price/best_bid)-1)*100)\n", " #bids[\"best_bid\"] = best_bid\n", " bids[\"time\"] = timestamp_\n", " bids['side'] = 'bid'\n", " bids[\"position\"] = range(len(bids))\n", " bids[\"cumulative_vol\"] = bids['size'].cumsum()\n", " bids[\"size_usd\"] = bids[\"size\"] * bids[\"price\"]\n", " bids[\"cumulative_vol_usd\"] = bids.size_usd.cumsum()\n", " # within depth limit - default 2%\n", " asks = asks[asks.percent_from_best <= within].copy()\n", " bids = bids[bids.percent_from_best <= within].copy()\n", " # group into bins of 0.01% (1 bps)\n", " bins = np.arange(0, 2.001, 0.10)\n", " bids['grouping'] = pd.cut(bids.percent_from_best, bins=bins, precision=1, include_lowest=True)\n", " asks['grouping'] = pd.cut(asks.percent_from_best, bins=bins, precision=1, include_lowest=True)\n", " # collapse\n", " bids = bids.groupby('grouping').agg({\"size\":[sum],\"size_usd\":[sum]})#.cumsum()\n", " bids.index = [x/100 for x in range(1,201,10)]\n", " bids['side']='bid'\n", "\n", " asks = asks.groupby('grouping').agg({\"size\":[sum],\"size_usd\":[sum]})#.cumsum()\n", " asks.index = [x/100 for x in range(1,201,10)]\n", " asks['side']='asks'\n", " \n", " #concat together\n", " bids_asks = pd.concat([bids,asks])\n", " dfs.append(bids_asks)\n", " df_liquidity = pd.concat(dfs)\n", " df_liquidity['time'] = df_orderbook.time.iloc[0]\n", " df_liquidity.columns = [\"size_ntv\",\"size_usd\",\"side\",\"time\"]\n", " #df_resampled_hourly = df_liquidity.groupby(['side','position']).resample('1h',on='time').mean()\n", " return df_liquidity" ] }, { "cell_type": "code", "execution_count": 8, "id": "d96645ce-ae55-473d-ab4a-308f3d0d4f28", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.480510Z", "start_time": "2022-10-31T14:50:34.174Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Getting order book data for coinbase-btc-usd-spot...\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "100%|█████████████████████████████████████████████████████████████████████████████| 168/168 [02:21<00:00, 1.19it/s]\n" ] } ], "source": [ "# collapse into depth by distance from best bid/ask \n", "print(\"Getting order book data for {}...\".format(market))\n", "dfs=[]\n", "for i in tqdm(range(len(df))):\n", " dfs.append(get_depth(df.iloc[i:i+1]))\n", " \n", "# get rolling 3 hour window\n", "df_aggregated = pd.concat(dfs) \n", "df_aggregated['pct_from_best'] = df_aggregated.index\n", "df_aggregated.sort_values([\"side\",\"pct_from_best\",\"time\"],inplace=True)\n", "df_aggregated['rolling_3hr_usd'] = df_aggregated.reset_index().groupby(['side','pct_from_best']).size_usd.rolling(3).mean().values\n", "df_aggregated = df_aggregated[df_aggregated['rolling_3hr_usd'].notnull()].copy()" ] }, { "cell_type": "code", "execution_count": 9, "id": "3ced09dd", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.481541Z", "start_time": "2022-10-31T14:50:34.175Z" } }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
size_ntvsize_usdsidetimepct_from_bestrolling_3hr_usd
0.01-77.155769-1.470296e+06asks2022-10-20 02:00:00+00:000.01-1.105986e+06
0.01-66.058756-1.259527e+06asks2022-10-20 03:00:00+00:000.01-1.295243e+06
0.01-126.462072-2.409340e+06asks2022-10-20 04:00:00+00:000.01-1.713054e+06
0.01-230.090826-4.417017e+06asks2022-10-20 05:00:00+00:000.01-2.695295e+06
0.01-67.504299-1.292230e+06asks2022-10-20 06:00:00+00:000.01-2.706196e+06
.....................
1.913.1506186.389480e+04bid2022-10-26 19:00:00+00:001.914.899971e+04
1.910.2842055.790217e+03bid2022-10-26 20:00:00+00:001.914.139945e+04
1.912.0586244.189135e+04bid2022-10-26 21:00:00+00:001.913.719212e+04
1.911.3286152.703422e+04bid2022-10-26 22:00:00+00:001.912.490526e+04
1.910.2436784.974376e+03bid2022-10-26 23:00:00+00:001.912.463332e+04
\n", "

6640 rows × 6 columns

\n", "
" ], "text/plain": [ " size_ntv size_usd side time pct_from_best \\\n", "0.01 -77.155769 -1.470296e+06 asks 2022-10-20 02:00:00+00:00 0.01 \n", "0.01 -66.058756 -1.259527e+06 asks 2022-10-20 03:00:00+00:00 0.01 \n", "0.01 -126.462072 -2.409340e+06 asks 2022-10-20 04:00:00+00:00 0.01 \n", "0.01 -230.090826 -4.417017e+06 asks 2022-10-20 05:00:00+00:00 0.01 \n", "0.01 -67.504299 -1.292230e+06 asks 2022-10-20 06:00:00+00:00 0.01 \n", "... ... ... ... ... ... \n", "1.91 3.150618 6.389480e+04 bid 2022-10-26 19:00:00+00:00 1.91 \n", "1.91 0.284205 5.790217e+03 bid 2022-10-26 20:00:00+00:00 1.91 \n", "1.91 2.058624 4.189135e+04 bid 2022-10-26 21:00:00+00:00 1.91 \n", "1.91 1.328615 2.703422e+04 bid 2022-10-26 22:00:00+00:00 1.91 \n", "1.91 0.243678 4.974376e+03 bid 2022-10-26 23:00:00+00:00 1.91 \n", "\n", " rolling_3hr_usd \n", "0.01 -1.105986e+06 \n", "0.01 -1.295243e+06 \n", "0.01 -1.713054e+06 \n", "0.01 -2.695295e+06 \n", "0.01 -2.706196e+06 \n", "... ... \n", "1.91 4.899971e+04 \n", "1.91 4.139945e+04 \n", "1.91 3.719212e+04 \n", "1.91 2.490526e+04 \n", "1.91 2.463332e+04 \n", "\n", "[6640 rows x 6 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df_aggregated" ] }, { "cell_type": "code", "execution_count": 10, "id": "2893f25f-b707-482a-b5bd-ab83602a0fef", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.482697Z", "start_time": "2022-10-31T14:50:34.176Z" } }, "outputs": [], "source": [ "def generate_depth_bar(df,title):\n", " fig = px.bar(df,\n", " x=df.time,\n", " y=\"rolling_3hr_usd\",\n", " height=550,\n", " width=850,\n", " color=df.index,\n", " color_continuous_scale=px.colors.diverging.RdYlGn[::-1],\n", " range_color=[0,2])\n", " fig.update_yaxes(matches=None, showticklabels=True, visible=True)\n", " fig.update_layout(\n", " title={\n", " 'text': title,\n", " 'font': {'family': 'arial', 'size': 18 }\n", " },\n", " font = { 'family': 'arial' },\n", " margin=dict(b=0),\n", " plot_bgcolor = 'white',\n", " yaxis_showgrid=True)\n", " fig.update_traces(marker_line_width=0)\n", " fig.update_yaxes(title=\"Total Ask Qty (-) Total Bid Qty (+)\", gridwidth=1, gridcolor='#ECECED')\n", " fig.update_xaxes(title=\"\",matches=None, showticklabels=True, visible=True)\n", " fig.update_layout(coloraxis_colorbar=dict(\n", " title=\"% from best\",\n", " thicknessmode=\"pixels\", thickness=30,\n", " lenmode=\"pixels\", len=180,\n", " yanchor=\"top\", y=1, ticksuffix=\"%\"\n", " ))\n", " fig.for_each_yaxis(lambda yaxis: yaxis.update(tickprefix=\"$\"))\n", " return go.Figure(fig,layout=go.Layout(margin={'t': 30,'r': 10,'b': 0,'l': 30,'pad': 2,\n", " }))" ] }, { "cell_type": "code", "execution_count": 11, "id": "caa2738f-18dd-4f2b-8e2e-e75387bee014", "metadata": { "ExecuteTime": { "end_time": "2022-10-31T14:50:36.483718Z", "start_time": "2022-10-31T14:50:34.176Z" } }, "outputs": [], "source": [ "depth_chart = generate_depth_bar(df_aggregated,f\"{market} SPOT:
USD Depth Within 2% of Best Bid/Ask\")" ] }, { "cell_type": "code", "execution_count": null, "id": "5912baa8-4dcb-4572-bb64-5cb5ad77256b", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.8.8" }, "toc": { "base_numbering": 1, "nav_menu": {}, "number_sections": false, "sideBar": true, "skip_h1_title": false, "title_cell": "Table of Contents", "title_sidebar": "Contents", "toc_cell": false, "toc_position": {}, "toc_section_display": true, "toc_window_display": false } }, "nbformat": 4, "nbformat_minor": 5 }