uniswap_vs_chainlink.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "2fa0364c",
   "metadata": {},
   "source": [
    "**Support code for Exploratory Report** <br>\n",
    "https://datastudio.google.com/u/0/reporting/f8bd5e2a-589c-4344-9cfc-36ef1fa471b4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "79230c40",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Blockchain connected: True\n"
     ]
    }
   ],
   "source": [
    "import logging\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pandas_gbq\n",
    "from datetime import datetime\n",
    "from web3 import Web3\n",
    "from google.oauth2 import service_account\n",
    "\n",
    "from uniswap_utils import getPoolPrice #https://github.com/amantay-a/misc/blob/master/uniswap_utils.py\n",
    "\n",
    "token_dict = {'ETH':'0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2', #WETH\n",
    "              'BTC':'0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599', #WBTC\n",
    "              'USDC':'0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48',\n",
    "               'DAI':'0x6B175474E89094C44Da98b954EedeAC495271d0F',\n",
    "             '1INCH':'0x111111111117dC0aa78b770fA6A738034120C302',\n",
    "              'AAVE':'0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9',\n",
    "              'COMP':'0xc00e94Cb662C3520282E6f5717214004A7f26888',\n",
    "               'DPI':'0x1494CA1F11D487c2bBe4543E90080AeBa4BA3C2b',\n",
    "               'FEI':'0x956F47F50A910163D8BF957Cf5846D573E7f87CA',\n",
    "              'LINK':'0x514910771AF9Ca656af840dff83E8264EcF986CA',\n",
    "               'SNX':'0xC011a73ee8576Fb46F5E1c5751cA3B9Fe0af2a6F',\n",
    "               'UNI':'0x1f9840a85d5aF5bf1D1762F925BDADdC4201F984',\n",
    "               'YFI':'0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e',\n",
    "              'LUNA':'0xd2877702675e6cEb975b4A1dFf9fb7BAF4C91ea9', #WLUNA\n",
    "               'FTM':'0x4E15361FD6b4BB609Fa63C81A2be19d873717870',\n",
    "             'SUSHI':'0x6B3595068778DD592e39A122f4f5a5cF09C90fE2',\n",
    "               'CRV':'0xD533a949740bb3306d119CC777fa900bA034cd52',\n",
    "               'CVX':'0x4e3FBD56CD56c3e72c1403e103b45Db9da5B9D2B',\n",
    "               'LDO':'0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32',\n",
    "             'STETH':'0xDFe66B14D37C77F4E9b180cEb433d1b164f0281D',\n",
    "              'FRAX':'0x853d955aCEf822Db058eb8505911ED77F175b99e',\n",
    "              'LUSD':'0x5f98805A4E8be255a32880FDeC7F6728C6568bA0',\n",
    "              'SUSD':'0x57Ab1ec28D129707052df4dF418D58a2D46d5f51',\n",
    "              'GUSD':'0x056Fd409E1d7A124BD7017459dFEa2F387b6d5Cd',\n",
    "              'USDT':'0xdAC17F958D2ee523a2206206994597C13D831ec7',\n",
    "              'FXS' :'0x3432B6A60D23Ca0dFCa7761B7ab56459D9C964D0',\n",
    "             'SPELL':'0x090185f2135308BaD17527004364eBcC2D37e5F6',\n",
    "             'LQTY' :'0x6DEA81C8171D0bA574754EF6F8b412F2Ed88c54D',\n",
    "            }\n",
    "# To be able to call historical prices, RPC must support archive mode, suggest using alchemy as it provide archive rpc for free\n",
    "RPCEndpoint = ''\n",
    "assert RPCEndpoint, \"Set up Archive Endpoint\"\n",
    "\n",
    "w3 = Web3(Web3.HTTPProvider(RPCEndpoint, request_kwargs={'timeout': 20}))\n",
    "print(f'Blockchain connected: {w3.isConnected()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "fa433c74",
   "metadata": {},
   "outputs": [],
   "source": [
    "twapwindows =  [1, 60*10, 60*20]\n",
    "poolfee = [100, 500, 3000, 10000] # 0.01%, 0.05%, 0.3%, 1.00%\n",
    "\n",
    "def get_uni_price(w3, tokenA, tokenB, block, TWAPWindows, PoolFees):\n",
    "    \n",
    "    price_twap = getPoolPrice(w3, \n",
    "                              tokenA, \n",
    "                              tokenB,\n",
    "                              TWAPWindows = twapwindows,\n",
    "                              blocks = [block],\n",
    "                              PoolFees = poolfee\n",
    "                             )\n",
    "    \n",
    "    #print(price_twap)\n",
    "    prices = []    \n",
    "    ret = {}\n",
    "    for w in TWAPWindows:\n",
    "        for i, f in enumerate(PoolFees):\n",
    "            price = price_twap[block][i]['twap'][w]\n",
    "            #print('price', price)\n",
    "            if price:\n",
    "                prices.append(price)    \n",
    "        if prices:\n",
    "            median_price = np.percentile(prices,50, interpolation ='lower')\n",
    "            ret.update({'twap_'+str(w):median_price}) \n",
    "        else:\n",
    "            ret.update({'twap_'+str(w):None})\n",
    "    return ret, price_twap"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a31c3a85",
   "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>ticker</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>price_chainlink</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754004.0</td>\n",
       "      <td>2022-05-11 09:16:26+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754095.0</td>\n",
       "      <td>2022-05-11 09:38:23+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754607.0</td>\n",
       "      <td>2022-05-11 11:30:47+00:00</td>\n",
       "      <td>0.000415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754762.0</td>\n",
       "      <td>2022-05-11 12:09:44+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754901.0</td>\n",
       "      <td>2022-05-11 12:38:41+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757142.0</td>\n",
       "      <td>2022-05-11 21:06:43+00:00</td>\n",
       "      <td>5.964237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757162.0</td>\n",
       "      <td>2022-05-11 21:11:25+00:00</td>\n",
       "      <td>5.900000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>942</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757177.0</td>\n",
       "      <td>2022-05-11 21:13:52+00:00</td>\n",
       "      <td>5.839932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>943</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757222.0</td>\n",
       "      <td>2022-05-11 21:26:22+00:00</td>\n",
       "      <td>5.771640</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757264.0</td>\n",
       "      <td>2022-05-11 21:36:16+00:00</td>\n",
       "      <td>5.707723</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>945 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ticker  blockNumber                updated_at  price_chainlink\n",
       "0    1inch-eth   14754004.0 2022-05-11 09:16:26+00:00         0.000433\n",
       "1    1inch-eth   14754095.0 2022-05-11 09:38:23+00:00         0.000424\n",
       "2    1inch-eth   14754607.0 2022-05-11 11:30:47+00:00         0.000415\n",
       "3    1inch-eth   14754762.0 2022-05-11 12:09:44+00:00         0.000424\n",
       "4    1inch-eth   14754901.0 2022-05-11 12:38:41+00:00         0.000433\n",
       "..         ...          ...                       ...              ...\n",
       "940    yfi-eth   14757142.0 2022-05-11 21:06:43+00:00         5.964237\n",
       "941    yfi-eth   14757162.0 2022-05-11 21:11:25+00:00         5.900000\n",
       "942    yfi-eth   14757177.0 2022-05-11 21:13:52+00:00         5.839932\n",
       "943    yfi-eth   14757222.0 2022-05-11 21:26:22+00:00         5.771640\n",
       "944    yfi-eth   14757264.0 2022-05-11 21:36:16+00:00         5.707723\n",
       "\n",
       "[945 rows x 4 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "credentials = service_account.Credentials.from_service_account_file(\n",
    "    'gearbox-336415-5ed144668529.json',\n",
    ")\n",
    "gcp_project_id = 'gearbox-336415'\n",
    "\n",
    "bq_select = '''with u as (select ticker, max(blockNumber) as twap_max_block from gearbox.uniswap_prices group by ticker) \n",
    "               select \n",
    "                   cl.ticker, cl.blockNumber, cl.updated_at, cl.price_decimal as price_chainlink\n",
    "               from \n",
    "                   gearbox.oracle_price_history cl\n",
    "                   left join u on u.ticker = cl.ticker\n",
    "               where cl.base !='usd'\n",
    "                   and cl.blockNumber > coalesce(twap_max_block,0)\n",
    "               order by cl.ticker, cl.blockNumber\n",
    "\n",
    "            '''\n",
    "df_cl = pandas_gbq.read_gbq(bq_select, \n",
    "                             project_id=gcp_project_id,\n",
    "                             progress_bar_type = None,)\n",
    "display(df_cl)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4851ee63",
   "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>ticker</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>price_chainlink</th>\n",
       "      <th>tokenA</th>\n",
       "      <th>tokenB</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754004.0</td>\n",
       "      <td>2022-05-11 09:16:26+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754095.0</td>\n",
       "      <td>2022-05-11 09:38:23+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754607.0</td>\n",
       "      <td>2022-05-11 11:30:47+00:00</td>\n",
       "      <td>0.000415</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754762.0</td>\n",
       "      <td>2022-05-11 12:09:44+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754901.0</td>\n",
       "      <td>2022-05-11 12:38:41+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757142.0</td>\n",
       "      <td>2022-05-11 21:06:43+00:00</td>\n",
       "      <td>5.964237</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757162.0</td>\n",
       "      <td>2022-05-11 21:11:25+00:00</td>\n",
       "      <td>5.900000</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>942</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757177.0</td>\n",
       "      <td>2022-05-11 21:13:52+00:00</td>\n",
       "      <td>5.839932</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>943</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757222.0</td>\n",
       "      <td>2022-05-11 21:26:22+00:00</td>\n",
       "      <td>5.771640</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757264.0</td>\n",
       "      <td>2022-05-11 21:36:16+00:00</td>\n",
       "      <td>5.707723</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>945 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ticker  blockNumber                updated_at  price_chainlink  \\\n",
       "0    1inch-eth   14754004.0 2022-05-11 09:16:26+00:00         0.000433   \n",
       "1    1inch-eth   14754095.0 2022-05-11 09:38:23+00:00         0.000424   \n",
       "2    1inch-eth   14754607.0 2022-05-11 11:30:47+00:00         0.000415   \n",
       "3    1inch-eth   14754762.0 2022-05-11 12:09:44+00:00         0.000424   \n",
       "4    1inch-eth   14754901.0 2022-05-11 12:38:41+00:00         0.000433   \n",
       "..         ...          ...                       ...              ...   \n",
       "940    yfi-eth   14757142.0 2022-05-11 21:06:43+00:00         5.964237   \n",
       "941    yfi-eth   14757162.0 2022-05-11 21:11:25+00:00         5.900000   \n",
       "942    yfi-eth   14757177.0 2022-05-11 21:13:52+00:00         5.839932   \n",
       "943    yfi-eth   14757222.0 2022-05-11 21:26:22+00:00         5.771640   \n",
       "944    yfi-eth   14757264.0 2022-05-11 21:36:16+00:00         5.707723   \n",
       "\n",
       "                                         tokenA  \\\n",
       "0    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "1    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "2    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "3    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "4    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "..                                          ...   \n",
       "940  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "941  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "942  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "943  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "944  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "\n",
       "                                         tokenB  \n",
       "0    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "1    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "2    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "3    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "4    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "..                                          ...  \n",
       "940  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "941  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "942  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "943  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "944  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2  \n",
       "\n",
       "[945 rows x 6 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df_cl\n",
    "df['tokenA'] = df['ticker'].apply(lambda x: token_dict[x.split('-')[0].upper()])\n",
    "df['tokenB'] = df['ticker'].apply(lambda x: token_dict[x.split('-')[1].upper()])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "137b067f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-05-11 22:01:03.091026 Start\n",
      "2022-05-11 22:01:03.096342 0/945 (1inch-eth)..\n",
      "2022-05-11 22:01:25.958456 17/945 (aave-eth)..\n",
      "2022-05-11 22:01:46.690975 32/945 (btc-eth)..\n",
      "2022-05-11 22:01:59.662355 34/945 (comp-eth)..\n",
      "2022-05-11 22:02:24.541244 47/945 (crv-eth)..\n",
      "2022-05-11 22:02:52.628712 67/945 (cvx-eth)..\n",
      "2022-05-11 22:03:33.643853 105/945 (dai-eth)..\n",
      "2022-05-11 22:04:26.236083 143/945 (dpi-eth)..\n",
      "2022-05-11 22:04:43.810043 153/945 (fei-eth)..\n",
      "2022-05-11 22:05:04.662789 165/945 (frax-eth)..\n",
      "2022-05-11 22:05:33.070054 183/945 (ftm-eth)..\n",
      "2022-05-11 22:06:08.614282 209/945 (fxs-eth)..\n",
      "2022-05-11 22:06:39.242964 229/945 (gusd-eth)..\n",
      "2022-05-11 22:07:03.226878 246/945 (ldo-eth)..\n",
      "2022-05-11 22:07:29.882306 269/945 (link-eth)..\n",
      "2022-05-11 22:08:00.941572 295/945 (luna-eth)..\n",
      "2022-05-11 22:11:57.041869 500/945 (luna-eth)..\n",
      "2022-05-11 22:13:20.157458 570/945 (lusd-eth)..\n",
      "2022-05-11 22:13:45.432370 586/945 (snx-eth)..\n",
      "2022-05-11 22:14:11.272560 600/945 (spell-eth)..\n",
      "2022-05-11 22:16:30.689953 747/945 (steth-eth)..\n",
      "2022-05-11 22:16:36.745065 748/945 (susd-eth)..\n",
      "2022-05-11 22:17:25.520222 786/945 (sushi-eth)..\n",
      "2022-05-11 22:17:52.918604 804/945 (uni-eth)..\n",
      "2022-05-11 22:18:06.894078 811/945 (usdc-eth)..\n",
      "2022-05-11 22:19:09.936622 854/945 (usdt-eth)..\n",
      "2022-05-11 22:19:56.997289 896/945 (yfi-eth)..\n"
     ]
    },
    {
     "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>ticker</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>price_chainlink</th>\n",
       "      <th>tokenA</th>\n",
       "      <th>tokenB</th>\n",
       "      <th>twap_1</th>\n",
       "      <th>twap_600</th>\n",
       "      <th>twap_1200</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754004.0</td>\n",
       "      <td>2022-05-11 09:16:26+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754095.0</td>\n",
       "      <td>2022-05-11 09:38:23+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754607.0</td>\n",
       "      <td>2022-05-11 11:30:47+00:00</td>\n",
       "      <td>0.000415</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754762.0</td>\n",
       "      <td>2022-05-11 12:09:44+00:00</td>\n",
       "      <td>0.000424</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1inch-eth</td>\n",
       "      <td>14754901.0</td>\n",
       "      <td>2022-05-11 12:38:41+00:00</td>\n",
       "      <td>0.000433</td>\n",
       "      <td>0x111111111117dC0aa78b770fA6A738034120C302</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>940</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757142.0</td>\n",
       "      <td>2022-05-11 21:06:43+00:00</td>\n",
       "      <td>5.964237</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>941</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757162.0</td>\n",
       "      <td>2022-05-11 21:11:25+00:00</td>\n",
       "      <td>5.900000</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>942</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757177.0</td>\n",
       "      <td>2022-05-11 21:13:52+00:00</td>\n",
       "      <td>5.839932</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>943</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757222.0</td>\n",
       "      <td>2022-05-11 21:26:22+00:00</td>\n",
       "      <td>5.771640</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>944</th>\n",
       "      <td>yfi-eth</td>\n",
       "      <td>14757264.0</td>\n",
       "      <td>2022-05-11 21:36:16+00:00</td>\n",
       "      <td>5.707723</td>\n",
       "      <td>0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e</td>\n",
       "      <td>0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>945 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        ticker  blockNumber                updated_at  price_chainlink  \\\n",
       "0    1inch-eth   14754004.0 2022-05-11 09:16:26+00:00         0.000433   \n",
       "1    1inch-eth   14754095.0 2022-05-11 09:38:23+00:00         0.000424   \n",
       "2    1inch-eth   14754607.0 2022-05-11 11:30:47+00:00         0.000415   \n",
       "3    1inch-eth   14754762.0 2022-05-11 12:09:44+00:00         0.000424   \n",
       "4    1inch-eth   14754901.0 2022-05-11 12:38:41+00:00         0.000433   \n",
       "..         ...          ...                       ...              ...   \n",
       "940    yfi-eth   14757142.0 2022-05-11 21:06:43+00:00         5.964237   \n",
       "941    yfi-eth   14757162.0 2022-05-11 21:11:25+00:00         5.900000   \n",
       "942    yfi-eth   14757177.0 2022-05-11 21:13:52+00:00         5.839932   \n",
       "943    yfi-eth   14757222.0 2022-05-11 21:26:22+00:00         5.771640   \n",
       "944    yfi-eth   14757264.0 2022-05-11 21:36:16+00:00         5.707723   \n",
       "\n",
       "                                         tokenA  \\\n",
       "0    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "1    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "2    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "3    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "4    0x111111111117dC0aa78b770fA6A738034120C302   \n",
       "..                                          ...   \n",
       "940  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "941  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "942  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "943  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "944  0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e   \n",
       "\n",
       "                                         tokenB twap_1 twap_600 twap_1200  \n",
       "0    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "1    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "2    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "3    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "4    0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "..                                          ...    ...      ...       ...  \n",
       "940  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "941  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "942  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "943  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "944  0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2   None     None      None  \n",
       "\n",
       "[945 rows x 9 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "twapwindow_cols = ['twap_'+str(x) for x in twapwindows]\n",
    "df[twapwindow_cols] = None\n",
    "cnt = 0\n",
    "print(datetime.utcnow(),'Start')\n",
    "for ticker in df['ticker'].unique():\n",
    "    df_ticker = df[df['ticker']==ticker].copy()\n",
    "    print(datetime.utcnow(),f'{cnt}/{len(df)} ({ticker})..')\n",
    "    for x in df_ticker.itertuples():\n",
    "        prices, raw = get_uni_price(w3, x.tokenA, x.tokenB, int(x.blockNumber), twapwindows, poolfee)\n",
    "        prices = list(prices.values())\n",
    "        df_ticker.loc[(df['blockNumber'] == x.blockNumber)&(df['ticker'] == x.ticker), twapwindow_cols+['raw']] = prices + [str(raw)]\n",
    "        cnt+=1\n",
    "        if cnt%500==0:\n",
    "            print(datetime.utcnow(),f'{cnt}/{len(df)} ({ticker})..')\n",
    "    df_ticker[twapwindow_cols] = df_ticker[twapwindow_cols].astype('float64')\n",
    "    pandas_gbq.to_gbq(df_ticker, \n",
    "                      'gearbox.uniswap_prices',\n",
    "                      project_id=gcp_project_id,\n",
    "                      if_exists = 'append', #replace,\n",
    "                      progress_bar = False)\n",
    "display(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6760d9d",
   "metadata": {},
   "source": [
    "# Exploratory Report\n",
    "https://datastudio.google.com/u/0/reporting/f8bd5e2a-589c-4344-9cfc-36ef1fa471b4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8480273",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}