CAI/cai_June_2023.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "098511ce-c7e3-49dd-a41b-23657ecabf6f",
   "metadata": {},
   "source": [
    "## CAI - Rebalancing data for June 2023 "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d1f1d7e0-f33f-466f-a327-756617b3aba8",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Configuring packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "5cc35cd0-4d79-4135-819d-bce7d6d021c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import requests\n",
    "import decouple\n",
    "import sys \n",
    "import datetime\n",
    "import time\n",
    "sys.path.append('../')\n",
    "\n",
    "from web3 import Web3\n",
    "from abis import index_anatomy\n",
    "\n",
    "\n",
    "infura_url = decouple.config(\"AVALANCHE_INFURA_URL\")\n",
    "web3_object = Web3(Web3.HTTPProvider(infura_url))\n",
    "\n",
    "import db_funcs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "f7ec58bd-df9f-47f7-b145-450d08e6fcfc",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pycoingecko import CoinGeckoAPI\n",
    "key = decouple.config(\"CG_KEY\")\n",
    "cg = CoinGeckoAPI(api_key= key)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "26168a37-e401-4e26-b63b-9c708ca0902c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import plotly.express as px\n",
    "import plotly.graph_objects as go"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "9eb0768b-c6fb-4401-9205-31490edb4cf2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "        <script type=\"text/javascript\">\n",
       "        window.PlotlyConfig = {MathJaxConfig: 'local'};\n",
       "        if (window.MathJax && window.MathJax.Hub && window.MathJax.Hub.Config) {window.MathJax.Hub.Config({SVG: {font: \"STIX-Web\"}});}\n",
       "        if (typeof require !== 'undefined') {\n",
       "        require.undef(\"plotly\");\n",
       "        requirejs.config({\n",
       "            paths: {\n",
       "                'plotly': ['https://cdn.plot.ly/plotly-2.12.1.min']\n",
       "            }\n",
       "        });\n",
       "        require(['plotly'], function(Plotly) {\n",
       "            window._Plotly = Plotly;\n",
       "        });\n",
       "        }\n",
       "        </script>\n",
       "        "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from plotly.offline import plot, iplot, init_notebook_mode\n",
    "init_notebook_mode(connected=True)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "ae536759",
   "metadata": {},
   "source": [
    "## Index Variables and settings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "3584c38f",
   "metadata": {},
   "outputs": [],
   "source": [
    "db_key_date = '2023-06-01'\n",
    "db_liquidity_table = 'cai_liquidities'\n",
    "db_benchmark_table = 'cai_benchmark_data'\n",
    "min_weight = 0.005\n",
    "max_weight = 0.5\n",
    "supply_threshold = 0.3\n",
    "liquidity_consistency = 90 # In days\n",
    "avax_to_savax_split = 0.6\n",
    "liveness_threshold = 30*6 # In days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "3fe13f51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-0.09"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "liq = 2*10**6\n",
    "liq_os = liq / 2 # one-side liquidity \n",
    "trade_value_tiny = 1e2\n",
    "trade_value = 1e5\n",
    "max_slippage = (liq_os + trade_value_tiny) / (liq_os + trade_value) - 1\n",
    "# round to the nearest .5\n",
    "max_slippage = round(2 * max_slippage, 2) / 2 \n",
    "max_slippage"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f54edc25-7b1b-41e3-8b84-bd8159ab60b5",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Data retrieving and filtering"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "06459122-606f-4b8b-9dcd-98490a913211",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Token inclusion criteria\n",
    "\n",
    "1. **Project and token characteristics**\n",
    "    1. The project’s token should have been listed on CoinGecko with pricing data at least 6 months prior to the date of inclusion in the index.\n",
    "    1. The project should have a token that is native to Avalanche. This excludes wrapped variants, where the underlying tokens are locked on an alt-L1.\n",
    "    1. The project should be a going concern, with a dedicated team actively building, supporting and maintaining the project.\n",
    "    1. No rebasing or deflationary tokens.\n",
    "    1. The project must be widely considered to be building a useful protocol or product. Projects that have ponzi characteristics at the core of their offering will not be considered.\n",
    "    1. Synthetic tokens which derive their value from external price feeds are not permissible.\n",
    "    1. The project’s token must not have the ability to pause token transfers.\n",
    "    1. The project’s protocol or product must have significant usage.\n",
    "    <br>\n",
    "1. **Pricing requirements**\n",
    "    1. Token should have a Chainlink price feed to allow for manipulation resistant pricing.\n",
    "    <br>\n",
    "1. **Token Supply requirements**\n",
    "    1. The project's token must have a circulating supply greater than 30% of the max supply. In cases where a token does not have a max supply, the minting mechanics would need to be assessed.\n",
    "    <br>\n",
    "1. **Liquidity Requirements**\n",
    "    1. The token must be listed on a supported exchange. \n",
    "    1. The token should have in aggregate at least $2mm of onchain liquidity across Trader Joe, Platypus, Pangolin.\n",
    "    1. The token must have shown consistent DeFi liquidity on Avalanche.\n",
    "    <br>\n",
    "1. **Security Requirements**\n",
    "    1. The project must have been audited by smart contract security professionals with the audit report(s) publicly available. Alternatively, the protocol must have been operating long enough to create a consensus about its safety in the decentralised finance community."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "785b167b-e990-4d6d-a8d5-75a6378e1197",
   "metadata": {},
   "source": [
    "### Tokens list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "602314b9-24a8-421c-bbb4-c00ef886139d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def normalize_symb(symb):\n",
    "    return ''.join(c for c in symb if c.isalnum()).upper()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c80728c3-e3d5-4270-9400-f39c1e9c0e55",
   "metadata": {},
   "source": [
    "#### CoinGecko"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "375c513b-7aa0-4ec9-82aa-475feabf6f84",
   "metadata": {},
   "source": [
    "##### Tokens addresses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "7788b765-2a08-4661-8b4b-1cb20ac72e44",
   "metadata": {},
   "outputs": [],
   "source": [
    "coins_list = cg.get_coins_list(include_platform=True)\n",
    "coins = {}\n",
    "for coin in coins_list:\n",
    "    if 'avalanche' in coin['platforms']:\n",
    "        coin['addr'] = coin['platforms']['avalanche']\n",
    "        coin['symbol'] = normalize_symb(coin['symbol'])\n",
    "        coins[coin['id']] = coin\n",
    "\n",
    "# Adding AVAX\n",
    "coins['avalanche-2'] = {'id': 'avalanche-2','symbol':normalize_symb('avax'), 'name':'Avalanche','addr':'0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7',}\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "74a45df3-0ae7-4907-b444-67b1c5a88b73",
   "metadata": {
    "tags": []
   },
   "source": [
    "##### Market data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "dce08acf-3b3e-4042-a0f2-7de6c8219f1d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/4t/6j5ypx_s6r771shjr5dcct5w0000gn/T/ipykernel_29515/3118279002.py:8: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cat_id = \"avalanche-ecosystem\"\n",
    "cg_tokens_data = cg.get_coins_markets(vs_currency='USD', order='market_cap_desc', category=cat_id)\n",
    "for i in range(2, 30):\n",
    "    cg_tokens_data.extend(cg.get_coins_markets(vs_currency='USD', order='market_cap_desc', category=cat_id, page=i))\n",
    "    \n",
    "cg_tokens_data = pd.DataFrame.from_records(cg_tokens_data)\n",
    "\n",
    "cg_tokens_data = cg_tokens_data.append(cg.get_coins_markets(ids='avalanche-2',vs_currency='USD'),ignore_index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "054f69e9-0dbb-4427-b9ca-1b4287f2f795",
   "metadata": {},
   "outputs": [],
   "source": [
    "tokens_market_data = cg_tokens_data.set_index('id')[\n",
    "    ['name', 'symbol', 'market_cap', 'market_cap_rank', 'current_price', 'total_supply', 'circulating_supply']\n",
    "]\n",
    "tokens_market_data['symbol'] = tokens_market_data['symbol'].apply(normalize_symb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "d33dbd5c-4967-41a2-99ee-6dc9a0b4490f",
   "metadata": {},
   "outputs": [],
   "source": [
    "tokens_market_data = tokens_market_data[tokens_market_data['market_cap'] > 0]\n",
    "tokens_market_data = tokens_market_data[~tokens_market_data.index.duplicated(keep='first')]\n",
    "tokens_market_data = tokens_market_data.join(pd.DataFrame.from_dict(coins, orient='index')['addr'])\n",
    "tokens_market_data = tokens_market_data[~tokens_market_data['addr'].isna()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "30a6d624-9247-45a0-95af-5f79dce6703f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>market_cap_rank</th>\n",
       "      <th>current_price</th>\n",
       "      <th>total_supply</th>\n",
       "      <th>circulating_supply</th>\n",
       "      <th>addr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>avalanche-2</th>\n",
       "      <td>Avalanche</td>\n",
       "      <td>AVAX</td>\n",
       "      <td>4.850057e+09</td>\n",
       "      <td>17.0</td>\n",
       "      <td>14.100000</td>\n",
       "      <td>4.307263e+08</td>\n",
       "      <td>3.440069e+08</td>\n",
       "      <td>0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wrapped-bitcoin</th>\n",
       "      <td>Wrapped Bitcoin</td>\n",
       "      <td>WBTC</td>\n",
       "      <td>4.258552e+09</td>\n",
       "      <td>19.0</td>\n",
       "      <td>27247.000000</td>\n",
       "      <td>1.563486e+05</td>\n",
       "      <td>1.563486e+05</td>\n",
       "      <td>0x50b7545627a5162f82a992c33b87adc75187b218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniswap</th>\n",
       "      <td>Uniswap</td>\n",
       "      <td>UNI</td>\n",
       "      <td>3.775962e+09</td>\n",
       "      <td>20.0</td>\n",
       "      <td>5.010000</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>7.537667e+08</td>\n",
       "      <td>0x8ebaf22b6f053dffeaf46f4dd9efa95d89ba8580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chainlink</th>\n",
       "      <td>Chainlink</td>\n",
       "      <td>LINK</td>\n",
       "      <td>3.349058e+09</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6.480000</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>5.171000e+08</td>\n",
       "      <td>0x5947bb275c521040051d82396192181b413227a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>the-graph</th>\n",
       "      <td>The Graph</td>\n",
       "      <td>GRT</td>\n",
       "      <td>1.205333e+09</td>\n",
       "      <td>41.0</td>\n",
       "      <td>0.133859</td>\n",
       "      <td>1.000000e+10</td>\n",
       "      <td>8.996180e+09</td>\n",
       "      <td>0x8a0cac13c7da965a312f08ea4229c37869e85cb9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>bscstarter</th>\n",
       "      <td>Starter.xyz</td>\n",
       "      <td>START</td>\n",
       "      <td>9.831690e+03</td>\n",
       "      <td>3569.0</td>\n",
       "      <td>0.010494</td>\n",
       "      <td>1.000000e+06</td>\n",
       "      <td>9.369142e+05</td>\n",
       "      <td>0xf44fb887334fa17d2c5c0f970b5d320ab53ed557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>hakuswap</th>\n",
       "      <td>HakuSwap</td>\n",
       "      <td>HAKU</td>\n",
       "      <td>8.338870e+03</td>\n",
       "      <td>3580.0</td>\n",
       "      <td>0.000229</td>\n",
       "      <td>7.376759e+07</td>\n",
       "      <td>3.640177e+07</td>\n",
       "      <td>0x695fa794d59106cebd40ab5f5ca19f458c723829</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ape-in</th>\n",
       "      <td>Ape In</td>\n",
       "      <td>APEIN</td>\n",
       "      <td>4.044940e+03</td>\n",
       "      <td>3618.0</td>\n",
       "      <td>0.107865</td>\n",
       "      <td>3.750000e+04</td>\n",
       "      <td>3.750000e+04</td>\n",
       "      <td>0x938fe3788222a74924e062120e7bfac829c719fb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avaware</th>\n",
       "      <td>Avaware</td>\n",
       "      <td>AVE</td>\n",
       "      <td>2.815650e+03</td>\n",
       "      <td>3631.0</td>\n",
       "      <td>0.000183</td>\n",
       "      <td>1.536350e+07</td>\n",
       "      <td>1.536350e+07</td>\n",
       "      <td>0x78ea17559b3d2cf85a7f9c2c704eda119db5e6de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>spice-trade</th>\n",
       "      <td>Spice Trade</td>\n",
       "      <td>SPICE</td>\n",
       "      <td>5.524700e+02</td>\n",
       "      <td>3655.0</td>\n",
       "      <td>0.000379</td>\n",
       "      <td>5.762059e+07</td>\n",
       "      <td>1.457682e+06</td>\n",
       "      <td>0xf30c5083a1479865c9a8916dec6ddadd82e8907b</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>171 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            name symbol    market_cap  market_cap_rank  \\\n",
       "id                                                                       \n",
       "avalanche-2            Avalanche   AVAX  4.850057e+09             17.0   \n",
       "wrapped-bitcoin  Wrapped Bitcoin   WBTC  4.258552e+09             19.0   \n",
       "uniswap                  Uniswap    UNI  3.775962e+09             20.0   \n",
       "chainlink              Chainlink   LINK  3.349058e+09             21.0   \n",
       "the-graph              The Graph    GRT  1.205333e+09             41.0   \n",
       "...                          ...    ...           ...              ...   \n",
       "bscstarter           Starter.xyz  START  9.831690e+03           3569.0   \n",
       "hakuswap                HakuSwap   HAKU  8.338870e+03           3580.0   \n",
       "ape-in                    Ape In  APEIN  4.044940e+03           3618.0   \n",
       "avaware                  Avaware    AVE  2.815650e+03           3631.0   \n",
       "spice-trade          Spice Trade  SPICE  5.524700e+02           3655.0   \n",
       "\n",
       "                 current_price  total_supply  circulating_supply  \\\n",
       "id                                                                 \n",
       "avalanche-2          14.100000  4.307263e+08        3.440069e+08   \n",
       "wrapped-bitcoin   27247.000000  1.563486e+05        1.563486e+05   \n",
       "uniswap               5.010000  1.000000e+09        7.537667e+08   \n",
       "chainlink             6.480000  1.000000e+09        5.171000e+08   \n",
       "the-graph             0.133859  1.000000e+10        8.996180e+09   \n",
       "...                        ...           ...                 ...   \n",
       "bscstarter            0.010494  1.000000e+06        9.369142e+05   \n",
       "hakuswap              0.000229  7.376759e+07        3.640177e+07   \n",
       "ape-in                0.107865  3.750000e+04        3.750000e+04   \n",
       "avaware               0.000183  1.536350e+07        1.536350e+07   \n",
       "spice-trade           0.000379  5.762059e+07        1.457682e+06   \n",
       "\n",
       "                                                       addr  \n",
       "id                                                           \n",
       "avalanche-2      0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7  \n",
       "wrapped-bitcoin  0x50b7545627a5162f82a992c33b87adc75187b218  \n",
       "uniswap          0x8ebaf22b6f053dffeaf46f4dd9efa95d89ba8580  \n",
       "chainlink        0x5947bb275c521040051d82396192181b413227a3  \n",
       "the-graph        0x8a0cac13c7da965a312f08ea4229c37869e85cb9  \n",
       "...                                                     ...  \n",
       "bscstarter       0xf44fb887334fa17d2c5c0f970b5d320ab53ed557  \n",
       "hakuswap         0x695fa794d59106cebd40ab5f5ca19f458c723829  \n",
       "ape-in           0x938fe3788222a74924e062120e7bfac829c719fb  \n",
       "avaware          0x78ea17559b3d2cf85a7f9c2c704eda119db5e6de  \n",
       "spice-trade      0xf30c5083a1479865c9a8916dec6ddadd82e8907b  \n",
       "\n",
       "[171 rows x 8 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Stable coins to remove from tokens list\n",
    "stablecoins = pd.DataFrame(cg.get_coins_markets('usd',category='stablecoins')).set_index('id')\n",
    "\n",
    "#Adjusting Pangolin's max supply to reflect tokenomic changes here: https://app.pangolin.exchange/#/vote/10\n",
    "tokens_market_data.loc[['pangolin'],[\"total_supply\"]] = 230000000.0\n",
    "\n",
    "tokens_market_data =  tokens_market_data[~tokens_market_data.index.isin(stablecoins.index)]\n",
    "tokens_market_data = tokens_market_data.sort_values('market_cap', ascending=False)\n",
    "\n",
    "tokens_market_data"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c0c93d4d-da55-46ad-bdba-0beb4904384b",
   "metadata": {},
   "source": [
    "### Token's supply check "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "2e8f966a-441d-4e40-ba0e-1d140cdd8e74",
   "metadata": {},
   "outputs": [],
   "source": [
    "sp_passed_index = (tokens_market_data['circulating_supply'] / tokens_market_data['total_supply']) > supply_threshold\n",
    "sp_passed = tokens_market_data[sp_passed_index]"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a6bbaa4d-eb21-4d5c-9965-438e549e2fe4",
   "metadata": {},
   "source": [
    "### Retrieve historical data & token age check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "95211db9-aaa9-415b-9442-173aa282a6dd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Excluding NXRA, prices data available only for 98 < 180 days\n",
      "Excluding MELD, prices data available only for 24 < 180 days\n",
      "Excluding USP, prices data available only for 13 < 180 days\n",
      "Note: GMD, marketcap data available only for 1 < 180 days\n",
      "Excluding GRAIN, prices data available only for 48 < 180 days\n",
      "Note: BETS, marketcap data available only for 130 < 180 days\n",
      "Note: KENSHI, marketcap data available only for 109 < 180 days\n",
      "Excluding CINU, prices data available only for 82 < 180 days\n",
      "Note: PLN, marketcap data available only for 1 < 180 days\n",
      "Note: MAGIC, marketcap data available only for 140 < 180 days\n",
      "Note: FRF, marketcap data available only for 113 < 180 days\n"
     ]
    }
   ],
   "source": [
    "exclude_list = []\n",
    "\n",
    "prices_data = pd.DataFrame()\n",
    "marketcaps = pd.DataFrame() \n",
    "for id_, row in sp_passed.iterrows():\n",
    "    symbol = row['symbol']\n",
    "    data = cg.get_coin_market_chart_by_id(id_, vs_currency='USD', days='max')\n",
    "  \n",
    "    df_prices = pd.DataFrame(data['prices'], columns=['date', id_])\n",
    "    df_prices = df_prices[df_prices[id_] > 0]\n",
    "    df_prices['date'] = pd.to_datetime(df_prices['date'], unit='ms').dt.date\n",
    "    df_prices['date'] = pd.to_datetime(df_prices['date'])\n",
    "    df_prices = df_prices.set_index('date', drop=True)\n",
    "    df_prices = df_prices.loc[~df_prices.index.duplicated(keep='first')]\n",
    "    \n",
    "    if len(df_prices) < liveness_threshold:\n",
    "        print(f'Excluding {symbol}, prices data available only for {len(df_prices)} < {liveness_threshold} days')\n",
    "        exclude_list.append(id_)\n",
    "        continue\n",
    "    prices_data = pd.concat([prices_data, df_prices], axis=1)\n",
    "    \n",
    "    df_mcaps = pd.DataFrame(data['market_caps'], columns=['date', id_])\n",
    "    df_mcaps = df_mcaps[df_mcaps[id_] > 0]\n",
    "    df_mcaps['date'] = pd.to_datetime(df_mcaps['date'], unit='ms').dt.date\n",
    "    df_mcaps['date'] = pd.to_datetime(df_mcaps['date'])\n",
    "    df_mcaps = df_mcaps.set_index('date', drop=True)\n",
    "    df_mcaps = df_mcaps.loc[~df_mcaps.index.duplicated(keep='first')]\n",
    "    \n",
    "    if len(df_mcaps) < liveness_threshold:\n",
    "        print(f'Note: {symbol}, marketcap data available only for {len(df_mcaps)} < {liveness_threshold} days')\n",
    "        ## continue\n",
    "    df_mcaps = df_mcaps.reindex(df_prices.index)\n",
    "    marketcaps = pd.concat([marketcaps, df_mcaps], axis=1)\n",
    "    \n",
    "    "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "bae26b9a-9b08-4558-b472-18d3cf9f1342",
   "metadata": {},
   "source": [
    "### Non quantative checks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "d6ae8446-7b24-4b35-9c29-cb06547ccfa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "manual_exclusions = [\n",
    "    {\n",
    "        'gecko_id': 'spell-token',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'stacktical',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'zookeeper',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'pendle',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'everrise',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'wrapped-bitcoin',\n",
    "        'reason' : 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'bitcoin-avalanche-bridged-btc-b',\n",
    "        'reason': 'Not a native Avalanche asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'gmx',\n",
    "        'reason': 'Bridged asset'\n",
    "    },\n",
    "    {\n",
    "        'gecko_id': 'woo-network',\n",
    "        'reason': 'Bridged asset'\n",
    "    }\n",
    "]\n",
    "for token in manual_exclusions:\n",
    "    exclude_list.append(token['gecko_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "771b0608",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>market_cap_rank</th>\n",
       "      <th>current_price</th>\n",
       "      <th>total_supply</th>\n",
       "      <th>circulating_supply</th>\n",
       "      <th>addr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>avalanche-2</th>\n",
       "      <td>Avalanche</td>\n",
       "      <td>AVAX</td>\n",
       "      <td>4.850057e+09</td>\n",
       "      <td>17.0</td>\n",
       "      <td>1.410000e+01</td>\n",
       "      <td>4.307263e+08</td>\n",
       "      <td>3.440069e+08</td>\n",
       "      <td>0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uniswap</th>\n",
       "      <td>Uniswap</td>\n",
       "      <td>UNI</td>\n",
       "      <td>3.775962e+09</td>\n",
       "      <td>20.0</td>\n",
       "      <td>5.010000e+00</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>7.537667e+08</td>\n",
       "      <td>0x8ebaf22b6f053dffeaf46f4dd9efa95d89ba8580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>chainlink</th>\n",
       "      <td>Chainlink</td>\n",
       "      <td>LINK</td>\n",
       "      <td>3.349058e+09</td>\n",
       "      <td>21.0</td>\n",
       "      <td>6.480000e+00</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>5.171000e+08</td>\n",
       "      <td>0x5947bb275c521040051d82396192181b413227a3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>the-graph</th>\n",
       "      <td>The Graph</td>\n",
       "      <td>GRT</td>\n",
       "      <td>1.205333e+09</td>\n",
       "      <td>41.0</td>\n",
       "      <td>1.338590e-01</td>\n",
       "      <td>1.000000e+10</td>\n",
       "      <td>8.996180e+09</td>\n",
       "      <td>0x8a0cac13c7da965a312f08ea4229c37869e85cb9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>aave</th>\n",
       "      <td>Aave</td>\n",
       "      <td>AAVE</td>\n",
       "      <td>9.184598e+08</td>\n",
       "      <td>52.0</td>\n",
       "      <td>6.370000e+01</td>\n",
       "      <td>1.600000e+07</td>\n",
       "      <td>1.441504e+07</td>\n",
       "      <td>0x63a72806098bd3d9520cc43356dd78afe5d386d9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>havven</th>\n",
       "      <td>Synthetix Network</td>\n",
       "      <td>SNX</td>\n",
       "      <td>7.391476e+08</td>\n",
       "      <td>64.0</td>\n",
       "      <td>2.320000e+00</td>\n",
       "      <td>3.189505e+08</td>\n",
       "      <td>3.182266e+08</td>\n",
       "      <td>0xbec243c995409e6520d7c41e404da5deba4b209b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>maker</th>\n",
       "      <td>Maker</td>\n",
       "      <td>MKR</td>\n",
       "      <td>5.718394e+08</td>\n",
       "      <td>79.0</td>\n",
       "      <td>6.342800e+02</td>\n",
       "      <td>9.776310e+05</td>\n",
       "      <td>9.013109e+05</td>\n",
       "      <td>0x88128fd4b259552a9a1d457f435a6527aab72d42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>frax-share</th>\n",
       "      <td>Frax Share</td>\n",
       "      <td>FXS</td>\n",
       "      <td>4.721462e+08</td>\n",
       "      <td>98.0</td>\n",
       "      <td>6.540000e+00</td>\n",
       "      <td>9.982298e+07</td>\n",
       "      <td>7.220851e+07</td>\n",
       "      <td>0x214db107654ff987ad859f34125307783fc8e387</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>basic-attention-token</th>\n",
       "      <td>Basic Attention</td>\n",
       "      <td>BAT</td>\n",
       "      <td>3.185805e+08</td>\n",
       "      <td>118.0</td>\n",
       "      <td>2.139010e-01</td>\n",
       "      <td>1.500000e+09</td>\n",
       "      <td>1.489139e+09</td>\n",
       "      <td>0x98443b96ea4b0858fdf3219cd13e98c7a4690588</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1inch</th>\n",
       "      <td>1inch</td>\n",
       "      <td>1INCH</td>\n",
       "      <td>3.117669e+08</td>\n",
       "      <td>122.0</td>\n",
       "      <td>3.815030e-01</td>\n",
       "      <td>1.500000e+09</td>\n",
       "      <td>8.172785e+08</td>\n",
       "      <td>0xd501281565bf7789224523144fe5d98e8b28f267</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>compound-governance-token</th>\n",
       "      <td>Compound</td>\n",
       "      <td>COMP</td>\n",
       "      <td>2.455635e+08</td>\n",
       "      <td>149.0</td>\n",
       "      <td>3.581000e+01</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>6.856086e+06</td>\n",
       "      <td>0xc3048e19e76cb9a3aa9d77d8c03c29fc906e2437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>yearn-finance</th>\n",
       "      <td>yearn.finance</td>\n",
       "      <td>YFI</td>\n",
       "      <td>2.140107e+08</td>\n",
       "      <td>167.0</td>\n",
       "      <td>6.485840e+03</td>\n",
       "      <td>3.666600e+04</td>\n",
       "      <td>3.297931e+04</td>\n",
       "      <td>0x9eaac1b23d935365bd7b542fe22ceee2922f52dc</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ankr</th>\n",
       "      <td>Ankr Network</td>\n",
       "      <td>ANKR</td>\n",
       "      <td>2.079084e+08</td>\n",
       "      <td>169.0</td>\n",
       "      <td>2.548030e-02</td>\n",
       "      <td>1.000000e+10</td>\n",
       "      <td>8.162899e+09</td>\n",
       "      <td>0x20cf1b6e9d856321ed4686877cf4538f2c84b4de</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0x</th>\n",
       "      <td>0x Protocol</td>\n",
       "      <td>ZRX</td>\n",
       "      <td>1.916931e+08</td>\n",
       "      <td>178.0</td>\n",
       "      <td>2.261920e-01</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>8.474961e+08</td>\n",
       "      <td>0x596fa47043f99a4e0f122243b841e55375cde0d2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sushi</th>\n",
       "      <td>Sushi</td>\n",
       "      <td>SUSHI</td>\n",
       "      <td>1.617811e+08</td>\n",
       "      <td>195.0</td>\n",
       "      <td>8.389940e-01</td>\n",
       "      <td>2.489231e+08</td>\n",
       "      <td>1.927893e+08</td>\n",
       "      <td>0x37b608519f91f70f2eeb0e5ed9af4061722e4f76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>joe</th>\n",
       "      <td>JOE</td>\n",
       "      <td>JOE</td>\n",
       "      <td>1.432017e+08</td>\n",
       "      <td>209.0</td>\n",
       "      <td>4.211720e-01</td>\n",
       "      <td>4.764376e+08</td>\n",
       "      <td>3.401820e+08</td>\n",
       "      <td>0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>uma</th>\n",
       "      <td>UMA</td>\n",
       "      <td>UMA</td>\n",
       "      <td>1.377248e+08</td>\n",
       "      <td>214.0</td>\n",
       "      <td>1.930000e+00</td>\n",
       "      <td>1.136913e+08</td>\n",
       "      <td>7.129512e+07</td>\n",
       "      <td>0x3bd2b1c7ed8d396dbb98ded3aebb41350a5b2339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>cartesi</th>\n",
       "      <td>Cartesi</td>\n",
       "      <td>CTSI</td>\n",
       "      <td>1.347574e+08</td>\n",
       "      <td>219.0</td>\n",
       "      <td>1.828260e-01</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>7.365854e+08</td>\n",
       "      <td>0x6b289cceaa8639e3831095d75a3e43520fabf552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>insure</th>\n",
       "      <td>inSure DeFi</td>\n",
       "      <td>SURE</td>\n",
       "      <td>1.166036e+08</td>\n",
       "      <td>236.0</td>\n",
       "      <td>4.384920e-03</td>\n",
       "      <td>8.800000e+10</td>\n",
       "      <td>2.646429e+10</td>\n",
       "      <td>0x5fc17416925789e0852fbfcd81c490ca4abc51f9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>synapse-2</th>\n",
       "      <td>Synapse</td>\n",
       "      <td>SYN</td>\n",
       "      <td>1.136322e+08</td>\n",
       "      <td>240.0</td>\n",
       "      <td>5.973480e-01</td>\n",
       "      <td>1.849078e+08</td>\n",
       "      <td>1.901176e+08</td>\n",
       "      <td>0x1f1e7c893855525b303f99bdf5c3c05be09ca251</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kyber-network-crystal</th>\n",
       "      <td>Kyber Network Crystal</td>\n",
       "      <td>KNC</td>\n",
       "      <td>1.108295e+08</td>\n",
       "      <td>245.0</td>\n",
       "      <td>6.079720e-01</td>\n",
       "      <td>2.244296e+08</td>\n",
       "      <td>1.822982e+08</td>\n",
       "      <td>0x39fc9e94caeacb435842fadedecb783589f50f5f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi-liquid-staked-avax</th>\n",
       "      <td>BENQI Liquid Staked AVAX</td>\n",
       "      <td>SAVAX</td>\n",
       "      <td>9.553761e+07</td>\n",
       "      <td>275.0</td>\n",
       "      <td>1.531000e+01</td>\n",
       "      <td>6.243765e+06</td>\n",
       "      <td>6.243765e+06</td>\n",
       "      <td>0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>alpha-finance</th>\n",
       "      <td>Stella</td>\n",
       "      <td>ALPHA</td>\n",
       "      <td>8.400537e+07</td>\n",
       "      <td>303.0</td>\n",
       "      <td>1.085200e-01</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>7.740000e+08</td>\n",
       "      <td>0x2147efff675e4a4ee1c2f918d181cdbd7a8e208f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beta-finance</th>\n",
       "      <td>Beta Finance</td>\n",
       "      <td>BETA</td>\n",
       "      <td>6.928042e+07</td>\n",
       "      <td>353.0</td>\n",
       "      <td>1.022310e-01</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>6.772727e+08</td>\n",
       "      <td>0x511d35c52a3c244e7b8bd92c0c297755fbd89212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>snowbank</th>\n",
       "      <td>Snowbank</td>\n",
       "      <td>SB</td>\n",
       "      <td>3.487096e+07</td>\n",
       "      <td>518.0</td>\n",
       "      <td>2.184300e+02</td>\n",
       "      <td>1.596400e+05</td>\n",
       "      <td>1.596400e+05</td>\n",
       "      <td>0x7d1232b90d3f809a54eeaeebc639c62df8a8942f</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>beefy-finance</th>\n",
       "      <td>Beefy.Finance</td>\n",
       "      <td>BIFI</td>\n",
       "      <td>3.343132e+07</td>\n",
       "      <td>541.0</td>\n",
       "      <td>4.180400e+02</td>\n",
       "      <td>8.000000e+04</td>\n",
       "      <td>8.000000e+04</td>\n",
       "      <td>0xd6070ae98b8069de6b494332d1a1a81b6179d960</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi</th>\n",
       "      <td>BENQI</td>\n",
       "      <td>QI</td>\n",
       "      <td>2.756958e+07</td>\n",
       "      <td>599.0</td>\n",
       "      <td>7.391440e-03</td>\n",
       "      <td>7.200000e+09</td>\n",
       "      <td>3.725578e+09</td>\n",
       "      <td>0x8729438eb15e2c8b576fcc6aecda6a148776c0f5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ampleforth</th>\n",
       "      <td>Ampleforth</td>\n",
       "      <td>AMPL</td>\n",
       "      <td>2.724441e+07</td>\n",
       "      <td>602.0</td>\n",
       "      <td>1.120000e+00</td>\n",
       "      <td>3.035722e+07</td>\n",
       "      <td>2.439108e+07</td>\n",
       "      <td>0x027dbca046ca156de9622cd1e2d907d375e53aa7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trustswap</th>\n",
       "      <td>Trustswap</td>\n",
       "      <td>SWAP</td>\n",
       "      <td>2.155766e+07</td>\n",
       "      <td>704.0</td>\n",
       "      <td>2.152930e-01</td>\n",
       "      <td>9.999516e+07</td>\n",
       "      <td>9.999516e+07</td>\n",
       "      <td>0xc7b5d72c836e718cda8888eaf03707faef675079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>idia</th>\n",
       "      <td>Impossible Finance Launchpad</td>\n",
       "      <td>IDIA</td>\n",
       "      <td>2.128347e+07</td>\n",
       "      <td>697.0</td>\n",
       "      <td>3.056388e-02</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>6.964680e+08</td>\n",
       "      <td>0xfcaf13227dcbfa2dc2b1928acfca03b85e2d25dd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>xen-crypto</th>\n",
       "      <td>XEN Crypto</td>\n",
       "      <td>XEN</td>\n",
       "      <td>1.607604e+07</td>\n",
       "      <td>798.0</td>\n",
       "      <td>1.540000e-06</td>\n",
       "      <td>1.046426e+13</td>\n",
       "      <td>1.046426e+13</td>\n",
       "      <td>0xc0c5aa69dbe4d6dddfbc89c0957686ec60f24389</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kalmar</th>\n",
       "      <td>KALM</td>\n",
       "      <td>KALM</td>\n",
       "      <td>1.264504e+07</td>\n",
       "      <td>882.0</td>\n",
       "      <td>1.620000e+00</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>7.851274e+06</td>\n",
       "      <td>0x62aceea3e666c5706ce1c61055fac1a669d31d93</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>spookyswap</th>\n",
       "      <td>Spookyswap</td>\n",
       "      <td>BOO</td>\n",
       "      <td>9.850762e+06</td>\n",
       "      <td>963.0</td>\n",
       "      <td>9.932940e-01</td>\n",
       "      <td>5.739882e+06</td>\n",
       "      <td>9.915805e+06</td>\n",
       "      <td>0xbd83010eb60f12112908774998f65761cf9f6f9a</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oath</th>\n",
       "      <td>OATH</td>\n",
       "      <td>OATH</td>\n",
       "      <td>9.350709e+06</td>\n",
       "      <td>984.0</td>\n",
       "      <td>8.648900e-02</td>\n",
       "      <td>1.081124e+08</td>\n",
       "      <td>1.081122e+08</td>\n",
       "      <td>0x2c69095d81305f1e3c6ed372336d407231624cea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>openocean</th>\n",
       "      <td>OpenOcean</td>\n",
       "      <td>OOE</td>\n",
       "      <td>8.651933e+06</td>\n",
       "      <td>1017.0</td>\n",
       "      <td>1.712263e-02</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>5.044258e+08</td>\n",
       "      <td>0x0ebd9537a25f56713e34c45b38f421a1e7191469</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>avalaunch</th>\n",
       "      <td>Avalaunch</td>\n",
       "      <td>XAVA</td>\n",
       "      <td>8.448037e+06</td>\n",
       "      <td>1025.0</td>\n",
       "      <td>2.558420e-01</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>3.303321e+07</td>\n",
       "      <td>0xd1c3f94de7e5b45fa4edbba472491a9f4b166fc4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>rai</th>\n",
       "      <td>Rai Reflex Index</td>\n",
       "      <td>RAI</td>\n",
       "      <td>8.210075e+06</td>\n",
       "      <td>1033.0</td>\n",
       "      <td>2.810000e+00</td>\n",
       "      <td>2.923265e+06</td>\n",
       "      <td>2.923265e+06</td>\n",
       "      <td>0x97cd1cfe2ed5712660bb6c14053c0ecb031bff7d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>step-app-fitfi</th>\n",
       "      <td>Step App</td>\n",
       "      <td>FITFI</td>\n",
       "      <td>7.680398e+06</td>\n",
       "      <td>1055.0</td>\n",
       "      <td>5.047560e-03</td>\n",
       "      <td>4.600000e+09</td>\n",
       "      <td>1.520525e+09</td>\n",
       "      <td>0x714f020c54cc9d104b6f4f6998c63ce2a31d1888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>realfevr</th>\n",
       "      <td>RealFevr</td>\n",
       "      <td>FEVR</td>\n",
       "      <td>7.145997e+06</td>\n",
       "      <td>1083.0</td>\n",
       "      <td>6.346300e-04</td>\n",
       "      <td>1.600000e+10</td>\n",
       "      <td>1.125751e+10</td>\n",
       "      <td>0x73178fceb736a9d6c1a9ef1fe413f09cba2d4a68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>gmd-protocol</th>\n",
       "      <td>GMD</td>\n",
       "      <td>GMD</td>\n",
       "      <td>6.877835e+06</td>\n",
       "      <td>1100.0</td>\n",
       "      <td>1.162300e+02</td>\n",
       "      <td>8.000000e+04</td>\n",
       "      <td>5.914673e+04</td>\n",
       "      <td>0x1fe70939c2cec8f31e8f7729442658586b469972</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pangolin</th>\n",
       "      <td>Pangolin</td>\n",
       "      <td>PNG</td>\n",
       "      <td>4.981159e+06</td>\n",
       "      <td>1214.0</td>\n",
       "      <td>2.927780e-02</td>\n",
       "      <td>2.300000e+08</td>\n",
       "      <td>1.701582e+08</td>\n",
       "      <td>0x60781c2586d68229fde47564546784ab3faca982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>insurace</th>\n",
       "      <td>InsurAce</td>\n",
       "      <td>INSUR</td>\n",
       "      <td>4.578359e+06</td>\n",
       "      <td>1259.0</td>\n",
       "      <td>7.311800e-02</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>6.260293e+07</td>\n",
       "      <td>0x544c42fbb96b39b21df61cf322b5edc285ee7429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>defi-yield-protocol</th>\n",
       "      <td>Dypius</td>\n",
       "      <td>DYP</td>\n",
       "      <td>4.294433e+06</td>\n",
       "      <td>1289.0</td>\n",
       "      <td>1.817990e-01</td>\n",
       "      <td>2.496343e+07</td>\n",
       "      <td>2.363112e+07</td>\n",
       "      <td>0x961c8c0b1aad0c0b10a51fef6a867e3091bcef17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ferrum-network</th>\n",
       "      <td>Ferrum Network</td>\n",
       "      <td>FRM</td>\n",
       "      <td>4.086905e+06</td>\n",
       "      <td>1307.0</td>\n",
       "      <td>3.534983e-02</td>\n",
       "      <td>3.317187e+08</td>\n",
       "      <td>1.155314e+08</td>\n",
       "      <td>0xe5caef4af8780e59df925470b050fb23c43ca68c</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>qi-dao</th>\n",
       "      <td>Qi Dao</td>\n",
       "      <td>QI</td>\n",
       "      <td>3.753408e+06</td>\n",
       "      <td>1352.0</td>\n",
       "      <td>4.476560e-02</td>\n",
       "      <td>2.000000e+08</td>\n",
       "      <td>8.388022e+07</td>\n",
       "      <td>0xa56f9a54880afbc30cf29bb66d2d9adcdcaeadd6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>colony</th>\n",
       "      <td>Colony</td>\n",
       "      <td>CLY</td>\n",
       "      <td>3.646766e+06</td>\n",
       "      <td>1369.0</td>\n",
       "      <td>4.028448e-02</td>\n",
       "      <td>1.500000e+08</td>\n",
       "      <td>9.056079e+07</td>\n",
       "      <td>0xec3492a2508ddf4fdc0cd76f31f340b30d1793e6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>yield-yak</th>\n",
       "      <td>Yield Yak</td>\n",
       "      <td>YAK</td>\n",
       "      <td>3.621705e+06</td>\n",
       "      <td>1372.0</td>\n",
       "      <td>3.623700e+02</td>\n",
       "      <td>1.000000e+04</td>\n",
       "      <td>1.000000e+04</td>\n",
       "      <td>0x59414b3089ce2af0010e7523dea7e2b35d776ec7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>betswirl</th>\n",
       "      <td>BetSwirl</td>\n",
       "      <td>BETS</td>\n",
       "      <td>3.510405e+06</td>\n",
       "      <td>1386.0</td>\n",
       "      <td>7.177600e-04</td>\n",
       "      <td>7.231261e+09</td>\n",
       "      <td>4.890781e+09</td>\n",
       "      <td>0xc763f8570a48c4c00c80b76107cbe744dda67b79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tomb</th>\n",
       "      <td>Tomb</td>\n",
       "      <td>TOMB</td>\n",
       "      <td>3.289122e+06</td>\n",
       "      <td>1419.0</td>\n",
       "      <td>1.067817e-02</td>\n",
       "      <td>3.092123e+08</td>\n",
       "      <td>3.092123e+08</td>\n",
       "      <td>0xb84527d59b6ecb96f433029ecc890d4492c5dce1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dextf</th>\n",
       "      <td>Domani Protocol</td>\n",
       "      <td>DEXTF</td>\n",
       "      <td>2.856081e+06</td>\n",
       "      <td>1481.0</td>\n",
       "      <td>5.184600e-02</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>5.508824e+07</td>\n",
       "      <td>0x03e8d118a1864c7dc53bf91e007ab7d91f5a06fa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>kenshi</th>\n",
       "      <td>Kenshi</td>\n",
       "      <td>KENSHI</td>\n",
       "      <td>2.361511e+06</td>\n",
       "      <td>1603.0</td>\n",
       "      <td>4.724380e-07</td>\n",
       "      <td>5.000000e+12</td>\n",
       "      <td>5.000000e+12</td>\n",
       "      <td>0x164caf66c45e483f7ee647ccad275b35b4c75719</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>juggernaut</th>\n",
       "      <td>Juggernaut</td>\n",
       "      <td>JGN</td>\n",
       "      <td>2.226438e+06</td>\n",
       "      <td>1633.0</td>\n",
       "      <td>2.220646e-02</td>\n",
       "      <td>1.500000e+08</td>\n",
       "      <td>1.002104e+08</td>\n",
       "      <td>0x4e3642603a75528489c2d94f86e9507260d3c5a1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>allianceblock</th>\n",
       "      <td>AllianceBlock</td>\n",
       "      <td>ALBT</td>\n",
       "      <td>2.077688e+06</td>\n",
       "      <td>1663.0</td>\n",
       "      <td>4.510260e-03</td>\n",
       "      <td>1.000000e+09</td>\n",
       "      <td>4.608161e+08</td>\n",
       "      <td>0x9e037de681cafa6e661e6108ed9c2bd1aa567ecd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>o3-swap</th>\n",
       "      <td>O3 Swap</td>\n",
       "      <td>O3</td>\n",
       "      <td>1.840766e+06</td>\n",
       "      <td>1721.0</td>\n",
       "      <td>5.066200e-02</td>\n",
       "      <td>3.817199e+07</td>\n",
       "      <td>3.572569e+07</td>\n",
       "      <td>0xee9801669c6138e84bd50deb500827b776777d28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>checkdot</th>\n",
       "      <td>CheckDot</td>\n",
       "      <td>CDT</td>\n",
       "      <td>1.609138e+06</td>\n",
       "      <td>1788.0</td>\n",
       "      <td>2.177440e-01</td>\n",
       "      <td>9.999250e+06</td>\n",
       "      <td>7.390041e+06</td>\n",
       "      <td>0x0cbd6fadcf8096cc9a43d90b45f65826102e3ece</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>zeroswap</th>\n",
       "      <td>ZeroSwap</td>\n",
       "      <td>ZEE</td>\n",
       "      <td>1.439826e+06</td>\n",
       "      <td>1843.0</td>\n",
       "      <td>1.939333e-02</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>7.426984e+07</td>\n",
       "      <td>0x44754455564474a89358b2c2265883df993b12f0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vector-finance</th>\n",
       "      <td>Vector Finance</td>\n",
       "      <td>VTX</td>\n",
       "      <td>1.407465e+06</td>\n",
       "      <td>1859.0</td>\n",
       "      <td>7.415400e-02</td>\n",
       "      <td>1.887405e+07</td>\n",
       "      <td>1.887405e+07</td>\n",
       "      <td>0x5817d4f0b62a59b17f75207da1848c2ce75e7af4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>oddz</th>\n",
       "      <td>Oddz</td>\n",
       "      <td>ODDZ</td>\n",
       "      <td>1.240864e+06</td>\n",
       "      <td>1924.0</td>\n",
       "      <td>1.572893e-02</td>\n",
       "      <td>1.000000e+08</td>\n",
       "      <td>8.138499e+07</td>\n",
       "      <td>0xb0a6e056b587d0a85640b39b1cb44086f7a26a1e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>dragon-crypto-aurum</th>\n",
       "      <td>Dragon Crypto Aurum</td>\n",
       "      <td>DCAU</td>\n",
       "      <td>1.190433e+06</td>\n",
       "      <td>1937.0</td>\n",
       "      <td>7.800000e+00</td>\n",
       "      <td>1.541299e+05</td>\n",
       "      <td>1.526434e+05</td>\n",
       "      <td>0x100cc3a819dd3e8573fd2e46d1e66ee866068f30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>atlas-usv</th>\n",
       "      <td>Atlas USV</td>\n",
       "      <td>USV</td>\n",
       "      <td>1.016841e+06</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>9.640000e+00</td>\n",
       "      <td>2.844978e+05</td>\n",
       "      <td>1.054834e+05</td>\n",
       "      <td>0xb0a8e082e5f8d2a04e74372c1be47737d85a0e73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                   name  symbol    market_cap  \\\n",
       "id                                                                              \n",
       "avalanche-2                                   Avalanche    AVAX  4.850057e+09   \n",
       "uniswap                                         Uniswap     UNI  3.775962e+09   \n",
       "chainlink                                     Chainlink    LINK  3.349058e+09   \n",
       "the-graph                                     The Graph     GRT  1.205333e+09   \n",
       "aave                                               Aave    AAVE  9.184598e+08   \n",
       "havven                                Synthetix Network     SNX  7.391476e+08   \n",
       "maker                                             Maker     MKR  5.718394e+08   \n",
       "frax-share                                   Frax Share     FXS  4.721462e+08   \n",
       "basic-attention-token                   Basic Attention     BAT  3.185805e+08   \n",
       "1inch                                             1inch   1INCH  3.117669e+08   \n",
       "compound-governance-token                      Compound    COMP  2.455635e+08   \n",
       "yearn-finance                             yearn.finance     YFI  2.140107e+08   \n",
       "ankr                                       Ankr Network    ANKR  2.079084e+08   \n",
       "0x                                          0x Protocol     ZRX  1.916931e+08   \n",
       "sushi                                             Sushi   SUSHI  1.617811e+08   \n",
       "joe                                                 JOE     JOE  1.432017e+08   \n",
       "uma                                                 UMA     UMA  1.377248e+08   \n",
       "cartesi                                         Cartesi    CTSI  1.347574e+08   \n",
       "insure                                      inSure DeFi    SURE  1.166036e+08   \n",
       "synapse-2                                       Synapse     SYN  1.136322e+08   \n",
       "kyber-network-crystal             Kyber Network Crystal     KNC  1.108295e+08   \n",
       "benqi-liquid-staked-avax       BENQI Liquid Staked AVAX   SAVAX  9.553761e+07   \n",
       "alpha-finance                                    Stella   ALPHA  8.400537e+07   \n",
       "beta-finance                               Beta Finance    BETA  6.928042e+07   \n",
       "snowbank                                       Snowbank      SB  3.487096e+07   \n",
       "beefy-finance                             Beefy.Finance    BIFI  3.343132e+07   \n",
       "benqi                                             BENQI      QI  2.756958e+07   \n",
       "ampleforth                                   Ampleforth    AMPL  2.724441e+07   \n",
       "trustswap                                     Trustswap    SWAP  2.155766e+07   \n",
       "idia                       Impossible Finance Launchpad    IDIA  2.128347e+07   \n",
       "xen-crypto                                   XEN Crypto     XEN  1.607604e+07   \n",
       "kalmar                                             KALM    KALM  1.264504e+07   \n",
       "spookyswap                                   Spookyswap     BOO  9.850762e+06   \n",
       "oath                                               OATH    OATH  9.350709e+06   \n",
       "openocean                                     OpenOcean     OOE  8.651933e+06   \n",
       "avalaunch                                     Avalaunch    XAVA  8.448037e+06   \n",
       "rai                                    Rai Reflex Index     RAI  8.210075e+06   \n",
       "step-app-fitfi                                 Step App   FITFI  7.680398e+06   \n",
       "realfevr                                       RealFevr    FEVR  7.145997e+06   \n",
       "gmd-protocol                                        GMD     GMD  6.877835e+06   \n",
       "pangolin                                       Pangolin     PNG  4.981159e+06   \n",
       "insurace                                       InsurAce   INSUR  4.578359e+06   \n",
       "defi-yield-protocol                              Dypius     DYP  4.294433e+06   \n",
       "ferrum-network                           Ferrum Network     FRM  4.086905e+06   \n",
       "qi-dao                                           Qi Dao      QI  3.753408e+06   \n",
       "colony                                           Colony     CLY  3.646766e+06   \n",
       "yield-yak                                     Yield Yak     YAK  3.621705e+06   \n",
       "betswirl                                       BetSwirl    BETS  3.510405e+06   \n",
       "tomb                                               Tomb    TOMB  3.289122e+06   \n",
       "dextf                                   Domani Protocol   DEXTF  2.856081e+06   \n",
       "kenshi                                           Kenshi  KENSHI  2.361511e+06   \n",
       "juggernaut                                   Juggernaut     JGN  2.226438e+06   \n",
       "allianceblock                             AllianceBlock    ALBT  2.077688e+06   \n",
       "o3-swap                                         O3 Swap      O3  1.840766e+06   \n",
       "checkdot                                       CheckDot     CDT  1.609138e+06   \n",
       "zeroswap                                       ZeroSwap     ZEE  1.439826e+06   \n",
       "vector-finance                           Vector Finance     VTX  1.407465e+06   \n",
       "oddz                                               Oddz    ODDZ  1.240864e+06   \n",
       "dragon-crypto-aurum                 Dragon Crypto Aurum    DCAU  1.190433e+06   \n",
       "atlas-usv                                     Atlas USV     USV  1.016841e+06   \n",
       "\n",
       "                           market_cap_rank  current_price  total_supply  \\\n",
       "id                                                                        \n",
       "avalanche-2                           17.0   1.410000e+01  4.307263e+08   \n",
       "uniswap                               20.0   5.010000e+00  1.000000e+09   \n",
       "chainlink                             21.0   6.480000e+00  1.000000e+09   \n",
       "the-graph                             41.0   1.338590e-01  1.000000e+10   \n",
       "aave                                  52.0   6.370000e+01  1.600000e+07   \n",
       "havven                                64.0   2.320000e+00  3.189505e+08   \n",
       "maker                                 79.0   6.342800e+02  9.776310e+05   \n",
       "frax-share                            98.0   6.540000e+00  9.982298e+07   \n",
       "basic-attention-token                118.0   2.139010e-01  1.500000e+09   \n",
       "1inch                                122.0   3.815030e-01  1.500000e+09   \n",
       "compound-governance-token            149.0   3.581000e+01  1.000000e+07   \n",
       "yearn-finance                        167.0   6.485840e+03  3.666600e+04   \n",
       "ankr                                 169.0   2.548030e-02  1.000000e+10   \n",
       "0x                                   178.0   2.261920e-01  1.000000e+09   \n",
       "sushi                                195.0   8.389940e-01  2.489231e+08   \n",
       "joe                                  209.0   4.211720e-01  4.764376e+08   \n",
       "uma                                  214.0   1.930000e+00  1.136913e+08   \n",
       "cartesi                              219.0   1.828260e-01  1.000000e+09   \n",
       "insure                               236.0   4.384920e-03  8.800000e+10   \n",
       "synapse-2                            240.0   5.973480e-01  1.849078e+08   \n",
       "kyber-network-crystal                245.0   6.079720e-01  2.244296e+08   \n",
       "benqi-liquid-staked-avax             275.0   1.531000e+01  6.243765e+06   \n",
       "alpha-finance                        303.0   1.085200e-01  1.000000e+09   \n",
       "beta-finance                         353.0   1.022310e-01  1.000000e+09   \n",
       "snowbank                             518.0   2.184300e+02  1.596400e+05   \n",
       "beefy-finance                        541.0   4.180400e+02  8.000000e+04   \n",
       "benqi                                599.0   7.391440e-03  7.200000e+09   \n",
       "ampleforth                           602.0   1.120000e+00  3.035722e+07   \n",
       "trustswap                            704.0   2.152930e-01  9.999516e+07   \n",
       "idia                                 697.0   3.056388e-02  1.000000e+09   \n",
       "xen-crypto                           798.0   1.540000e-06  1.046426e+13   \n",
       "kalmar                               882.0   1.620000e+00  1.000000e+07   \n",
       "spookyswap                           963.0   9.932940e-01  5.739882e+06   \n",
       "oath                                 984.0   8.648900e-02  1.081124e+08   \n",
       "openocean                           1017.0   1.712263e-02  1.000000e+09   \n",
       "avalaunch                           1025.0   2.558420e-01  1.000000e+08   \n",
       "rai                                 1033.0   2.810000e+00  2.923265e+06   \n",
       "step-app-fitfi                      1055.0   5.047560e-03  4.600000e+09   \n",
       "realfevr                            1083.0   6.346300e-04  1.600000e+10   \n",
       "gmd-protocol                        1100.0   1.162300e+02  8.000000e+04   \n",
       "pangolin                            1214.0   2.927780e-02  2.300000e+08   \n",
       "insurace                            1259.0   7.311800e-02  1.000000e+08   \n",
       "defi-yield-protocol                 1289.0   1.817990e-01  2.496343e+07   \n",
       "ferrum-network                      1307.0   3.534983e-02  3.317187e+08   \n",
       "qi-dao                              1352.0   4.476560e-02  2.000000e+08   \n",
       "colony                              1369.0   4.028448e-02  1.500000e+08   \n",
       "yield-yak                           1372.0   3.623700e+02  1.000000e+04   \n",
       "betswirl                            1386.0   7.177600e-04  7.231261e+09   \n",
       "tomb                                1419.0   1.067817e-02  3.092123e+08   \n",
       "dextf                               1481.0   5.184600e-02  1.000000e+08   \n",
       "kenshi                              1603.0   4.724380e-07  5.000000e+12   \n",
       "juggernaut                          1633.0   2.220646e-02  1.500000e+08   \n",
       "allianceblock                       1663.0   4.510260e-03  1.000000e+09   \n",
       "o3-swap                             1721.0   5.066200e-02  3.817199e+07   \n",
       "checkdot                            1788.0   2.177440e-01  9.999250e+06   \n",
       "zeroswap                            1843.0   1.939333e-02  1.000000e+08   \n",
       "vector-finance                      1859.0   7.415400e-02  1.887405e+07   \n",
       "oddz                                1924.0   1.572893e-02  1.000000e+08   \n",
       "dragon-crypto-aurum                 1937.0   7.800000e+00  1.541299e+05   \n",
       "atlas-usv                           2008.0   9.640000e+00  2.844978e+05   \n",
       "\n",
       "                           circulating_supply  \\\n",
       "id                                              \n",
       "avalanche-2                      3.440069e+08   \n",
       "uniswap                          7.537667e+08   \n",
       "chainlink                        5.171000e+08   \n",
       "the-graph                        8.996180e+09   \n",
       "aave                             1.441504e+07   \n",
       "havven                           3.182266e+08   \n",
       "maker                            9.013109e+05   \n",
       "frax-share                       7.220851e+07   \n",
       "basic-attention-token            1.489139e+09   \n",
       "1inch                            8.172785e+08   \n",
       "compound-governance-token        6.856086e+06   \n",
       "yearn-finance                    3.297931e+04   \n",
       "ankr                             8.162899e+09   \n",
       "0x                               8.474961e+08   \n",
       "sushi                            1.927893e+08   \n",
       "joe                              3.401820e+08   \n",
       "uma                              7.129512e+07   \n",
       "cartesi                          7.365854e+08   \n",
       "insure                           2.646429e+10   \n",
       "synapse-2                        1.901176e+08   \n",
       "kyber-network-crystal            1.822982e+08   \n",
       "benqi-liquid-staked-avax         6.243765e+06   \n",
       "alpha-finance                    7.740000e+08   \n",
       "beta-finance                     6.772727e+08   \n",
       "snowbank                         1.596400e+05   \n",
       "beefy-finance                    8.000000e+04   \n",
       "benqi                            3.725578e+09   \n",
       "ampleforth                       2.439108e+07   \n",
       "trustswap                        9.999516e+07   \n",
       "idia                             6.964680e+08   \n",
       "xen-crypto                       1.046426e+13   \n",
       "kalmar                           7.851274e+06   \n",
       "spookyswap                       9.915805e+06   \n",
       "oath                             1.081122e+08   \n",
       "openocean                        5.044258e+08   \n",
       "avalaunch                        3.303321e+07   \n",
       "rai                              2.923265e+06   \n",
       "step-app-fitfi                   1.520525e+09   \n",
       "realfevr                         1.125751e+10   \n",
       "gmd-protocol                     5.914673e+04   \n",
       "pangolin                         1.701582e+08   \n",
       "insurace                         6.260293e+07   \n",
       "defi-yield-protocol              2.363112e+07   \n",
       "ferrum-network                   1.155314e+08   \n",
       "qi-dao                           8.388022e+07   \n",
       "colony                           9.056079e+07   \n",
       "yield-yak                        1.000000e+04   \n",
       "betswirl                         4.890781e+09   \n",
       "tomb                             3.092123e+08   \n",
       "dextf                            5.508824e+07   \n",
       "kenshi                           5.000000e+12   \n",
       "juggernaut                       1.002104e+08   \n",
       "allianceblock                    4.608161e+08   \n",
       "o3-swap                          3.572569e+07   \n",
       "checkdot                         7.390041e+06   \n",
       "zeroswap                         7.426984e+07   \n",
       "vector-finance                   1.887405e+07   \n",
       "oddz                             8.138499e+07   \n",
       "dragon-crypto-aurum              1.526434e+05   \n",
       "atlas-usv                        1.054834e+05   \n",
       "\n",
       "                                                                 addr  \n",
       "id                                                                     \n",
       "avalanche-2                0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7  \n",
       "uniswap                    0x8ebaf22b6f053dffeaf46f4dd9efa95d89ba8580  \n",
       "chainlink                  0x5947bb275c521040051d82396192181b413227a3  \n",
       "the-graph                  0x8a0cac13c7da965a312f08ea4229c37869e85cb9  \n",
       "aave                       0x63a72806098bd3d9520cc43356dd78afe5d386d9  \n",
       "havven                     0xbec243c995409e6520d7c41e404da5deba4b209b  \n",
       "maker                      0x88128fd4b259552a9a1d457f435a6527aab72d42  \n",
       "frax-share                 0x214db107654ff987ad859f34125307783fc8e387  \n",
       "basic-attention-token      0x98443b96ea4b0858fdf3219cd13e98c7a4690588  \n",
       "1inch                      0xd501281565bf7789224523144fe5d98e8b28f267  \n",
       "compound-governance-token  0xc3048e19e76cb9a3aa9d77d8c03c29fc906e2437  \n",
       "yearn-finance              0x9eaac1b23d935365bd7b542fe22ceee2922f52dc  \n",
       "ankr                       0x20cf1b6e9d856321ed4686877cf4538f2c84b4de  \n",
       "0x                         0x596fa47043f99a4e0f122243b841e55375cde0d2  \n",
       "sushi                      0x37b608519f91f70f2eeb0e5ed9af4061722e4f76  \n",
       "joe                        0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd  \n",
       "uma                        0x3bd2b1c7ed8d396dbb98ded3aebb41350a5b2339  \n",
       "cartesi                    0x6b289cceaa8639e3831095d75a3e43520fabf552  \n",
       "insure                     0x5fc17416925789e0852fbfcd81c490ca4abc51f9  \n",
       "synapse-2                  0x1f1e7c893855525b303f99bdf5c3c05be09ca251  \n",
       "kyber-network-crystal      0x39fc9e94caeacb435842fadedecb783589f50f5f  \n",
       "benqi-liquid-staked-avax   0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be  \n",
       "alpha-finance              0x2147efff675e4a4ee1c2f918d181cdbd7a8e208f  \n",
       "beta-finance               0x511d35c52a3c244e7b8bd92c0c297755fbd89212  \n",
       "snowbank                   0x7d1232b90d3f809a54eeaeebc639c62df8a8942f  \n",
       "beefy-finance              0xd6070ae98b8069de6b494332d1a1a81b6179d960  \n",
       "benqi                      0x8729438eb15e2c8b576fcc6aecda6a148776c0f5  \n",
       "ampleforth                 0x027dbca046ca156de9622cd1e2d907d375e53aa7  \n",
       "trustswap                  0xc7b5d72c836e718cda8888eaf03707faef675079  \n",
       "idia                       0xfcaf13227dcbfa2dc2b1928acfca03b85e2d25dd  \n",
       "xen-crypto                 0xc0c5aa69dbe4d6dddfbc89c0957686ec60f24389  \n",
       "kalmar                     0x62aceea3e666c5706ce1c61055fac1a669d31d93  \n",
       "spookyswap                 0xbd83010eb60f12112908774998f65761cf9f6f9a  \n",
       "oath                       0x2c69095d81305f1e3c6ed372336d407231624cea  \n",
       "openocean                  0x0ebd9537a25f56713e34c45b38f421a1e7191469  \n",
       "avalaunch                  0xd1c3f94de7e5b45fa4edbba472491a9f4b166fc4  \n",
       "rai                        0x97cd1cfe2ed5712660bb6c14053c0ecb031bff7d  \n",
       "step-app-fitfi             0x714f020c54cc9d104b6f4f6998c63ce2a31d1888  \n",
       "realfevr                   0x73178fceb736a9d6c1a9ef1fe413f09cba2d4a68  \n",
       "gmd-protocol               0x1fe70939c2cec8f31e8f7729442658586b469972  \n",
       "pangolin                   0x60781c2586d68229fde47564546784ab3faca982  \n",
       "insurace                   0x544c42fbb96b39b21df61cf322b5edc285ee7429  \n",
       "defi-yield-protocol        0x961c8c0b1aad0c0b10a51fef6a867e3091bcef17  \n",
       "ferrum-network             0xe5caef4af8780e59df925470b050fb23c43ca68c  \n",
       "qi-dao                     0xa56f9a54880afbc30cf29bb66d2d9adcdcaeadd6  \n",
       "colony                     0xec3492a2508ddf4fdc0cd76f31f340b30d1793e6  \n",
       "yield-yak                  0x59414b3089ce2af0010e7523dea7e2b35d776ec7  \n",
       "betswirl                   0xc763f8570a48c4c00c80b76107cbe744dda67b79  \n",
       "tomb                       0xb84527d59b6ecb96f433029ecc890d4492c5dce1  \n",
       "dextf                      0x03e8d118a1864c7dc53bf91e007ab7d91f5a06fa  \n",
       "kenshi                     0x164caf66c45e483f7ee647ccad275b35b4c75719  \n",
       "juggernaut                 0x4e3642603a75528489c2d94f86e9507260d3c5a1  \n",
       "allianceblock              0x9e037de681cafa6e661e6108ed9c2bd1aa567ecd  \n",
       "o3-swap                    0xee9801669c6138e84bd50deb500827b776777d28  \n",
       "checkdot                   0x0cbd6fadcf8096cc9a43d90b45f65826102e3ece  \n",
       "zeroswap                   0x44754455564474a89358b2c2265883df993b12f0  \n",
       "vector-finance             0x5817d4f0b62a59b17f75207da1848c2ce75e7af4  \n",
       "oddz                       0xb0a6e056b587d0a85640b39b1cb44086f7a26a1e  \n",
       "dragon-crypto-aurum        0x100cc3a819dd3e8573fd2e46d1e66ee866068f30  \n",
       "atlas-usv                  0xb0a8e082e5f8d2a04e74372c1be47737d85a0e73  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "passed_q_and_non_q_checks = sp_passed.query('index not in @exclude_list')\n",
    "passed_q_and_non_q_checks.drop(passed_q_and_non_q_checks[passed_q_and_non_q_checks['market_cap'] < 1e6].index)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "038e3d9e-2841-451f-9066-8d6009da1ed9",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Liquidity check"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "44432461-712c-40ed-90aa-a447cdbc2239",
   "metadata": {},
   "source": [
    "To derive a slippage threshold, consider an abstract univ2-like pool with $5m\\$$ overall liquidity. \n",
    "I.e if pool has reserves $R_1$, $R_2$ and $p_1$ and $p_2$ are the correspondent tokens prices (in USD), then\n",
    "$$R_1p_1 = R_2p_2 = 2.5m \\$$$\n",
    "\n",
    "Swapping $x$ token1 for token2 will result in the output amount $y$ such that\n",
    "$$R_1\\cdot R_2 = (R_1 + x) \\cdot (R_2 - y)$$\n",
    "$$y = \\frac{R_2\\cdot x}{R_1 + x}$$\n",
    "Then the delivery price will be\n",
    "$$p_d = \\frac{y}{x} = \\frac{R_2}{R_1 + x}$$\n",
    "The spot price may be estimated as a delivery price for relatively small input amount $x_0$\n",
    "$$p_s = \\frac{y_0}{x_0} = \\frac{R_2}{R_1 + x_0}$$\n",
    "\n",
    "Then the theoretical slippage for this pool will be\n",
    "$$slippage = \\frac{p_d}{p_s} - 1 = \\frac{R_1+x_0}{R_1+x} - 1 = \\frac{p_1R_1+p_1x_0}{p_1R_1+p_1x}-1$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "9297450e-bdc5-424b-8ec2-08fb7ec5a9df",
   "metadata": {},
   "outputs": [],
   "source": [
    "url_0x = \"https://avalanche.api.0x.org/swap/v1/quote\"\n",
    "header =  {'0x-api-key': decouple.config(\"ZEROEX_KEY\")}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "21129b64-07e4-4286-9734-402420c7f7aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "havven\n",
      "cartesi\n",
      "idia\n",
      "oath\n",
      "realfevr\n",
      "kenshi\n",
      "checkdot\n",
      "build\n",
      "spherium\n",
      "shack\n",
      "bios\n"
     ]
    }
   ],
   "source": [
    "decimals = 18\n",
    "slippages = []\n",
    "for id, data in passed_q_and_non_q_checks.iterrows():\n",
    "    try:\n",
    "        query = {\n",
    "            'buyToken': data['addr'],\n",
    "            'sellToken': 'AVAX',\n",
    "            'sellAmount': int(trade_value_tiny / tokens_market_data.loc['avalanche-2']['current_price'] * 10 ** decimals),\n",
    "            'enableSlippageProtection':'true'\n",
    "        }\n",
    "        \n",
    "        # spot price is calculated as a price for 100$ swap\n",
    "        resp = requests.get(url_0x, params=query,headers=header)\n",
    "        swap = resp.json()\n",
    "        spot_price = float(swap['price'])\n",
    "        \n",
    "        query['sellAmount'] = int(trade_value / tokens_market_data.loc['avalanche-2']['current_price'] * 10 ** decimals)\n",
    "        resp = requests.get(url_0x, params=query,headers=header)\n",
    "        swap = resp.json()\n",
    "        del_price = float(swap['price'])\n",
    "        \n",
    "        slippage = del_price / spot_price - 1\n",
    "        slippages.append({\n",
    "            'id': id,\n",
    "            'spot_price': spot_price,\n",
    "            'delivery_price': del_price,\n",
    "            'slippage': slippage\n",
    "        })\n",
    "        time.sleep(2)\n",
    "    except KeyError:\n",
    "        print(id)\n",
    "        continue\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "163461d3-e40a-46e2-a1b5-81d670b27e01",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>spot_price</th>\n",
       "      <th>delivery_price</th>\n",
       "      <th>slippage</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>benqi-liquid-staked-avax</td>\n",
       "      <td>9.213828e-01</td>\n",
       "      <td>9.213901e-01</td>\n",
       "      <td>0.000008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>avalanche-2</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>1.000000e+00</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>joe</td>\n",
       "      <td>3.341038e+01</td>\n",
       "      <td>3.310617e+01</td>\n",
       "      <td>-0.009105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>benqi</td>\n",
       "      <td>1.906489e+03</td>\n",
       "      <td>1.717865e+03</td>\n",
       "      <td>-0.098938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>avalaunch</td>\n",
       "      <td>5.600936e+01</td>\n",
       "      <td>4.829287e+01</td>\n",
       "      <td>-0.137771</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>pangolin</td>\n",
       "      <td>4.847816e+02</td>\n",
       "      <td>4.144119e+02</td>\n",
       "      <td>-0.145158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chainlink</td>\n",
       "      <td>2.176273e+00</td>\n",
       "      <td>1.760229e+00</td>\n",
       "      <td>-0.191173</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>dragon-crypto-argenti</td>\n",
       "      <td>3.254817e+02</td>\n",
       "      <td>2.317721e+02</td>\n",
       "      <td>-0.287910</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>yield-yak</td>\n",
       "      <td>3.876344e-02</td>\n",
       "      <td>2.519896e-02</td>\n",
       "      <td>-0.349930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>vector-finance</td>\n",
       "      <td>1.897012e+02</td>\n",
       "      <td>1.220653e+02</td>\n",
       "      <td>-0.356539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>colony-avalanche-index</td>\n",
       "      <td>1.236688e-01</td>\n",
       "      <td>7.446296e-02</td>\n",
       "      <td>-0.397884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>aave</td>\n",
       "      <td>2.204864e-01</td>\n",
       "      <td>1.284174e-01</td>\n",
       "      <td>-0.417572</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>alpha-finance</td>\n",
       "      <td>1.296542e+02</td>\n",
       "      <td>7.120692e+01</td>\n",
       "      <td>-0.450794</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ampleforth</td>\n",
       "      <td>1.257845e+01</td>\n",
       "      <td>6.436278e+00</td>\n",
       "      <td>-0.488309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>dragon-crypto-aurum</td>\n",
       "      <td>1.796190e+00</td>\n",
       "      <td>8.116065e-01</td>\n",
       "      <td>-0.548151</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>colony</td>\n",
       "      <td>3.536870e+02</td>\n",
       "      <td>1.248504e+02</td>\n",
       "      <td>-0.647003</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>nftrade</td>\n",
       "      <td>7.567746e+02</td>\n",
       "      <td>2.444286e+02</td>\n",
       "      <td>-0.677013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>dextf</td>\n",
       "      <td>2.647311e+02</td>\n",
       "      <td>8.103953e+01</td>\n",
       "      <td>-0.693880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>cosmic-universe-magic-token</td>\n",
       "      <td>3.581565e+03</td>\n",
       "      <td>1.071751e+03</td>\n",
       "      <td>-0.700759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>spore</td>\n",
       "      <td>8.819481e+11</td>\n",
       "      <td>2.182938e+11</td>\n",
       "      <td>-0.752487</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             id    spot_price  delivery_price  slippage\n",
       "0      benqi-liquid-staked-avax  9.213828e-01    9.213901e-01  0.000008\n",
       "1                   avalanche-2  1.000000e+00    1.000000e+00  0.000000\n",
       "2                           joe  3.341038e+01    3.310617e+01 -0.009105\n",
       "3                         benqi  1.906489e+03    1.717865e+03 -0.098938\n",
       "4                     avalaunch  5.600936e+01    4.829287e+01 -0.137771\n",
       "5                      pangolin  4.847816e+02    4.144119e+02 -0.145158\n",
       "6                     chainlink  2.176273e+00    1.760229e+00 -0.191173\n",
       "7         dragon-crypto-argenti  3.254817e+02    2.317721e+02 -0.287910\n",
       "8                     yield-yak  3.876344e-02    2.519896e-02 -0.349930\n",
       "9                vector-finance  1.897012e+02    1.220653e+02 -0.356539\n",
       "10       colony-avalanche-index  1.236688e-01    7.446296e-02 -0.397884\n",
       "11                         aave  2.204864e-01    1.284174e-01 -0.417572\n",
       "12                alpha-finance  1.296542e+02    7.120692e+01 -0.450794\n",
       "13                   ampleforth  1.257845e+01    6.436278e+00 -0.488309\n",
       "14          dragon-crypto-aurum  1.796190e+00    8.116065e-01 -0.548151\n",
       "15                       colony  3.536870e+02    1.248504e+02 -0.647003\n",
       "16                      nftrade  7.567746e+02    2.444286e+02 -0.677013\n",
       "17                        dextf  2.647311e+02    8.103953e+01 -0.693880\n",
       "18  cosmic-universe-magic-token  3.581565e+03    1.071751e+03 -0.700759\n",
       "19                        spore  8.819481e+11    2.182938e+11 -0.752487"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "slippages_df = pd.DataFrame(slippages).sort_values('slippage',ascending=False).reset_index(drop=True)\n",
    "slippages_df.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "fa3e4fc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Writing to database\n",
    "slippages_to_save = slippages_df[['id','slippage']].head(20)\n",
    "asset_columns = db_funcs.convert_to_sql_strings(list(slippages_to_save['id']))\n",
    "slippage_values = list(slippages_to_save['slippage'])\n",
    "db_funcs.insert_values(db_key_date,asset_columns,slippage_values,db_liquidity_table)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "de3b734d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "benqi-liquid-staked-avax   -0.000067\n",
       "avalanche-2                 0.000000\n",
       "joe                        -0.022488\n",
       "benqi                      -0.086376\n",
       "dtype: float64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def check_avg_liquidity(date_delta):\n",
    "    start_date = str(datetime.date.fromisoformat(db_key_date) - datetime.timedelta(days=date_delta))\n",
    "    dataframe = pd.read_sql(f'Select * from {db_liquidity_table} where date >= ? ',db_funcs.create_connection(db_funcs.db),index_col='date',parse_dates=['date'],params=[start_date])\n",
    "    dataframe = dataframe.mean(axis=0)\n",
    "    dataframe = dataframe[dataframe > max_slippage]\n",
    "    dataframe.index = db_funcs.convert_from_sql_strings(list(dataframe.index))\n",
    "    return dataframe, list(dataframe.index)\n",
    "\n",
    "avg_liq_dataframe ,include_list = check_avg_liquidity(liquidity_consistency)\n",
    "avg_liq_dataframe\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "b597ff4d-9598-4d29-9e10-98b58a1aa2f3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>market_cap_rank</th>\n",
       "      <th>current_price</th>\n",
       "      <th>total_supply</th>\n",
       "      <th>circulating_supply</th>\n",
       "      <th>addr</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>avalanche-2</th>\n",
       "      <td>Avalanche</td>\n",
       "      <td>AVAX</td>\n",
       "      <td>4.850057e+09</td>\n",
       "      <td>17.0</td>\n",
       "      <td>14.100000</td>\n",
       "      <td>4.307263e+08</td>\n",
       "      <td>3.440069e+08</td>\n",
       "      <td>0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>joe</th>\n",
       "      <td>JOE</td>\n",
       "      <td>JOE</td>\n",
       "      <td>1.432017e+08</td>\n",
       "      <td>209.0</td>\n",
       "      <td>0.421172</td>\n",
       "      <td>4.764376e+08</td>\n",
       "      <td>3.401820e+08</td>\n",
       "      <td>0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi-liquid-staked-avax</th>\n",
       "      <td>BENQI Liquid Staked AVAX</td>\n",
       "      <td>SAVAX</td>\n",
       "      <td>9.553761e+07</td>\n",
       "      <td>275.0</td>\n",
       "      <td>15.310000</td>\n",
       "      <td>6.243765e+06</td>\n",
       "      <td>6.243765e+06</td>\n",
       "      <td>0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi</th>\n",
       "      <td>BENQI</td>\n",
       "      <td>QI</td>\n",
       "      <td>2.756958e+07</td>\n",
       "      <td>599.0</td>\n",
       "      <td>0.007391</td>\n",
       "      <td>7.200000e+09</td>\n",
       "      <td>3.725578e+09</td>\n",
       "      <td>0x8729438eb15e2c8b576fcc6aecda6a148776c0f5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              name symbol    market_cap  \\\n",
       "id                                                                        \n",
       "avalanche-2                              Avalanche   AVAX  4.850057e+09   \n",
       "joe                                            JOE    JOE  1.432017e+08   \n",
       "benqi-liquid-staked-avax  BENQI Liquid Staked AVAX  SAVAX  9.553761e+07   \n",
       "benqi                                        BENQI     QI  2.756958e+07   \n",
       "\n",
       "                          market_cap_rank  current_price  total_supply  \\\n",
       "id                                                                       \n",
       "avalanche-2                          17.0      14.100000  4.307263e+08   \n",
       "joe                                 209.0       0.421172  4.764376e+08   \n",
       "benqi-liquid-staked-avax            275.0      15.310000  6.243765e+06   \n",
       "benqi                               599.0       0.007391  7.200000e+09   \n",
       "\n",
       "                          circulating_supply  \\\n",
       "id                                             \n",
       "avalanche-2                     3.440069e+08   \n",
       "joe                             3.401820e+08   \n",
       "benqi-liquid-staked-avax        6.243765e+06   \n",
       "benqi                           3.725578e+09   \n",
       "\n",
       "                                                                addr  \n",
       "id                                                                    \n",
       "avalanche-2               0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7  \n",
       "joe                       0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd  \n",
       "benqi-liquid-staked-avax  0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be  \n",
       "benqi                     0x8729438eb15e2c8b576fcc6aecda6a148776c0f5  "
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qualified_assets = passed_q_and_non_q_checks.query('index in @include_list')\n",
    "qualified_assets\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "f37255c2-3901-4432-ae9a-2fe4be0da6b0",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Marketcap ranking & filtering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "63307972-a354-407e-8981-bc16e700b3d7",
   "metadata": {},
   "outputs": [],
   "source": [
    "chosen_tokens = qualified_assets.sort_values(\"market_cap\", ascending=False)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "aace23a3-65b0-4d5c-8e8a-ef6e5bf5fa75",
   "metadata": {},
   "source": [
    "## Weighting "
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "6f09b1a8-c752-4cd6-bbfa-0a79ec7e15a0",
   "metadata": {},
   "source": [
    "**Weighting requirements**\n",
    "* The maximum weight any one token can have is 50%.\n",
    "* All excess weight is proportionally redistributed to all uncapped tokens. \n",
    "* Any asset with a weight below 0.5% will be removed from the index.\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "1b09f606-595a-4497-849f-77aaa65684a2",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Weight mcaps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "30397f34-29d6-401e-b8c1-233b5679c4ca",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# exclude savax from weighting since it should be a part of avax weight\n",
    "if 'benqi-liquid-staked-avax' in chosen_tokens.index:\n",
    "    chosen_tokens_ex_savax = chosen_tokens.drop('benqi-liquid-staked-avax')\n",
    "    weights = chosen_tokens_ex_savax['market_cap'].div(chosen_tokens_ex_savax['market_cap'].sum())\n",
    "else:\n",
    "    weights = chosen_tokens['market_cap'].div(chosen_tokens['market_cap'].sum())\n",
    "\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a0e004fb-94f0-4540-9103-c81b1edcddc4",
   "metadata": {},
   "source": [
    "### Adjust weights"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b81afd8a-8635-4d0e-b977-b2a3ec809c33",
   "metadata": {},
   "source": [
    "This process adjusts weights to adhere to the weight constraints described in our methodology. In addition the `remove_tiny_weights` function checks for any assets with a weight below the minimum threshold of $0.5\\%$ and proportionally increases all remaining weights (which are $> 0.5\\%$). Note that after this procedure readjusting weights to respect maximum may be needed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "f34cb625-e58d-424c-b33f-f3c393d1efb3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def adjust_weights(weights, max_weight):\n",
    "    w = weights.copy()\n",
    "    \n",
    "    while (w > max_weight).any(axis=None):\n",
    "        w[w > max_weight] = max_weight\n",
    "        c = 1 - w.sum()\n",
    "        w_less = w[w < max_weight]\n",
    "        w[(w < max_weight) & (w > 0)] += w_less.div(w_less.sum()).mul(c)\n",
    "    \n",
    "    return w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "e291c891-011e-49c8-80c1-3024207d89c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_tiny_weights(weights, min_weight):\n",
    "    w = weights.copy()\n",
    "    \n",
    "    w[w < min_weight] = 0\n",
    "    w = w.div(w.sum())\n",
    "    \n",
    "    return w"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "fa93ce86-f09a-49da-9555-2a8917d225e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "adjusted_weights = adjust_weights(weights, max_weight=max_weight) \n",
    "adjusted_weights = remove_tiny_weights(adjusted_weights, min_weight=min_weight)\n",
    "adjusted_weights = adjust_weights(adjusted_weights, max_weight=max_weight)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "5ccc806e-2c1f-4555-a6ba-98c8ed3063af",
   "metadata": {},
   "source": [
    "### Split AVAX "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "f7f18f7b-f202-4732-87e5-f90ab7137262",
   "metadata": {},
   "outputs": [],
   "source": [
    "if 'avalanche-2' and 'benqi-liquid-staked-avax' in chosen_tokens.index:\n",
    "    \n",
    "    adjusted_weights['benqi-liquid-staked-avax'] = adjusted_weights['avalanche-2'] * avax_to_savax_split\n",
    "    adjusted_weights['avalanche-2'] = adjusted_weights['avalanche-2'] * (1 - avax_to_savax_split)\n",
    "\n",
    "adjusted_weights =  adjusted_weights.sort_values(ascending = False)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "7a29b38b-441a-48f6-aca7-4ca1c0ea4207",
   "metadata": {},
   "source": [
    "### Convert weights to the [1, 255] scale"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b478e9d0-feb7-4f4c-b81d-e989e39ebbc0",
   "metadata": {},
   "source": [
    "On the contract side weights are integer numbers from the interval $[1, 255]$ with total sum $255$, so it's needed to convert retrieved weights to this format.\n",
    "\n",
    "Note that one can't just round weights to integers after scaling, since it doesn't guarantee that their sum will be $255$. To fix that firstly floor function is applied to the weights and then $1$ is being added to the $k$ weights with largest fractional parts."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "5dcd7bd0-0094-4eb9-9c83-e51b108071a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_weights(weights):\n",
    "    w_scaled = weights * 255\n",
    "    w_res = np.floor(w_scaled).astype(int)    \n",
    "    remainders = w_scaled - w_res    \n",
    "    k = round(remainders.sum())\n",
    "    w_below_max = w_res[w_res<128]\n",
    "    for i in range(k):\n",
    "        w_below_max[i] +=1\n",
    "    for i in w_below_max.index:\n",
    "        w_res[i] = w_below_max[i]\n",
    "    return w_res"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "02330a37-5e19-4c7a-95e6-21aa80199afe",
   "metadata": {},
   "outputs": [],
   "source": [
    "converted_weights = convert_weights(adjusted_weights)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c4c5d752-6063-4676-9a5e-97a884e21b0f",
   "metadata": {},
   "source": [
    "## Summary table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "477090af-03b4-4616-839c-0046e9590d4c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>price</th>\n",
       "      <th>weight</th>\n",
       "      <th>weight_converted</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>avalanche-2</th>\n",
       "      <td>Avalanche</td>\n",
       "      <td>AVAX</td>\n",
       "      <td>4.850057e+09</td>\n",
       "      <td>14.100000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>51</td>\n",
       "      <td>0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>joe</th>\n",
       "      <td>JOE</td>\n",
       "      <td>JOE</td>\n",
       "      <td>1.432017e+08</td>\n",
       "      <td>0.421172</td>\n",
       "      <td>0.419279</td>\n",
       "      <td>107</td>\n",
       "      <td>0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi-liquid-staked-avax</th>\n",
       "      <td>BENQI Liquid Staked AVAX</td>\n",
       "      <td>SAVAX</td>\n",
       "      <td>9.553761e+07</td>\n",
       "      <td>15.310000</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>77</td>\n",
       "      <td>0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi</th>\n",
       "      <td>BENQI</td>\n",
       "      <td>QI</td>\n",
       "      <td>2.756958e+07</td>\n",
       "      <td>0.007391</td>\n",
       "      <td>0.080721</td>\n",
       "      <td>20</td>\n",
       "      <td>0x8729438eb15e2c8b576fcc6aecda6a148776c0f5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              name symbol    market_cap  \\\n",
       "id                                                                        \n",
       "avalanche-2                              Avalanche   AVAX  4.850057e+09   \n",
       "joe                                            JOE    JOE  1.432017e+08   \n",
       "benqi-liquid-staked-avax  BENQI Liquid Staked AVAX  SAVAX  9.553761e+07   \n",
       "benqi                                        BENQI     QI  2.756958e+07   \n",
       "\n",
       "                              price    weight  weight_converted  \\\n",
       "id                                                                \n",
       "avalanche-2               14.100000  0.200000                51   \n",
       "joe                        0.421172  0.419279               107   \n",
       "benqi-liquid-staked-avax  15.310000  0.300000                77   \n",
       "benqi                      0.007391  0.080721                20   \n",
       "\n",
       "                                                             address  \n",
       "id                                                                    \n",
       "avalanche-2               0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7  \n",
       "joe                       0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd  \n",
       "benqi-liquid-staked-avax  0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be  \n",
       "benqi                     0x8729438eb15e2c8b576fcc6aecda6a148776c0f5  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cai = pd.DataFrame()\n",
    "cai.index = chosen_tokens.index\n",
    "cai['name'] = chosen_tokens['name']\n",
    "cai['symbol'] = chosen_tokens['symbol']\n",
    "cai['market_cap'] = chosen_tokens['market_cap']\n",
    "cai['price'] = chosen_tokens['current_price']\n",
    "cai['weight'] = adjusted_weights\n",
    "cai['weight_converted'] = converted_weights\n",
    "cai['address'] = chosen_tokens['addr']\n",
    "\n",
    "cai = cai[cai['weight'] > 0]\n",
    "cai = cai.sort_values(\"market_cap\", ascending=False)\n",
    "cai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "7e72f79e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add assets and weights to database\n",
    "db_assets = db_funcs.convert_to_sql_strings(list(cai.index))\n",
    "db_weights = list(cai['weight'])\n",
    "db_funcs.insert_values(db_key_date,db_assets,db_weights,db_benchmark_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "2cf759e4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>symbol</th>\n",
       "      <th>market_cap</th>\n",
       "      <th>price</th>\n",
       "      <th>weight</th>\n",
       "      <th>weight_converted</th>\n",
       "      <th>address</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>avalanche-2</th>\n",
       "      <td>Avalanche</td>\n",
       "      <td>AVAX</td>\n",
       "      <td>4.850057e+09</td>\n",
       "      <td>14.100000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>51</td>\n",
       "      <td>0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>joe</th>\n",
       "      <td>JOE</td>\n",
       "      <td>JOE</td>\n",
       "      <td>1.432017e+08</td>\n",
       "      <td>0.421172</td>\n",
       "      <td>0.419279</td>\n",
       "      <td>107</td>\n",
       "      <td>0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi-liquid-staked-avax</th>\n",
       "      <td>BENQI Liquid Staked AVAX</td>\n",
       "      <td>SAVAX</td>\n",
       "      <td>9.553761e+07</td>\n",
       "      <td>15.310000</td>\n",
       "      <td>0.300000</td>\n",
       "      <td>77</td>\n",
       "      <td>0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>benqi</th>\n",
       "      <td>BENQI</td>\n",
       "      <td>QI</td>\n",
       "      <td>2.756958e+07</td>\n",
       "      <td>0.007391</td>\n",
       "      <td>0.080721</td>\n",
       "      <td>20</td>\n",
       "      <td>0x8729438eb15e2c8b576fcc6aecda6a148776c0f5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              name symbol    market_cap  \\\n",
       "id                                                                        \n",
       "avalanche-2                              Avalanche   AVAX  4.850057e+09   \n",
       "joe                                            JOE    JOE  1.432017e+08   \n",
       "benqi-liquid-staked-avax  BENQI Liquid Staked AVAX  SAVAX  9.553761e+07   \n",
       "benqi                                        BENQI     QI  2.756958e+07   \n",
       "\n",
       "                              price    weight  weight_converted  \\\n",
       "id                                                                \n",
       "avalanche-2               14.100000  0.200000                51   \n",
       "joe                        0.421172  0.419279               107   \n",
       "benqi-liquid-staked-avax  15.310000  0.300000                77   \n",
       "benqi                      0.007391  0.080721                20   \n",
       "\n",
       "                                                             address  \n",
       "id                                                                    \n",
       "avalanche-2               0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7  \n",
       "joe                       0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd  \n",
       "benqi-liquid-staked-avax  0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be  \n",
       "benqi                     0x8729438eb15e2c8b576fcc6aecda6a148776c0f5  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Querying current contract to ascertain which assets will be dropped\n",
    "\n",
    "cai_token_address = '0x48f88A3fE843ccb0b5003e70B4192c1d7448bEf0'\n",
    "ia_contract = web3_object.eth.contract(address=cai_token_address,abi=index_anatomy)\n",
    "addresses, weights = ia_contract.functions.anatomy().call()\n",
    "\n",
    "\n",
    "for address in addresses:\n",
    "    address = address.lower()\n",
    "    if address not in list(cai['address']):\n",
    "        data = tokens_market_data[tokens_market_data['addr']== address]\n",
    "        cai.loc[data.index[0]] = [data['name'][0],data['symbol'][0],data['market_cap'][0],data['current_price'][0],0,0,address]\n",
    "\n",
    "cai"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "52487c36",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0x2b2c81e08f1af8835a78bb2a90ae924ace0ea4be,0x6e84a6216ea6dacc71ee8e6b0a5b7322eebc0fdd,0x8729438eb15e2c8b576fcc6aecda6a148776c0f5,0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7\n",
      "[77, 107, 20, 51]\n"
     ]
    }
   ],
   "source": [
    "zipped_assets = list(zip(list(cai['address']),list(cai['weight_converted'])))\n",
    "sorted_assets = sorted(zipped_assets,key = lambda x: int(x[0],base=0) )\n",
    "\n",
    "asset_string = ''\n",
    "for i in sorted_assets:\n",
    "    asset_string += f'{i[0]},'\n",
    "asset_string = asset_string[:-1]\n",
    "print(asset_string)\n",
    "print([x[1] for x in sorted_assets])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.13 ('base')",
   "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.16"
  },
  "vscode": {
   "interpreter": {
    "hash": "40d3a090f54c6569ab1632332b64b2c03c39dcf918b08424e98f38b5ae0af88f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}