Gearbox_CA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "af35a1fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install web3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f5f24285",
   "metadata": {},
   "outputs": [],
   "source": [
    "#!pip install multicall"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a1b8e9cf",
   "metadata": {},
   "source": [
    "**Gearbox Protocol**\n",
    "\n",
    "Website:        https://gearbox.fi/ <br>\n",
    "dApp:           https://app.gearbox.fi/ <br>\n",
    "Docs:           https://docs.gearbox.finance/ <br>\n",
    "Developer Docs: https://dev.gearbox.fi/ <br>\n",
    "\n",
    "**Gearbox Credit Accout Report** <br>\n",
    "https://datastudio.google.com/reporting/a95186ae-29b4-4d72-8807-612bb5f54dd0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8df336d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4085e990",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 21:48:17 <module> INFO Ethereum connected: True\n"
     ]
    }
   ],
   "source": [
    "from gearbox_ca import (pull_abi_etherscan, parse_abi, chunks, get_data_multicall, get_logs, get_token_balance, getCreditAccountData) # see gearbox_ca.py\n",
    "import os\n",
    "from datetime import datetime\n",
    "from pprint import pformat\n",
    "import logging\n",
    "import pandas as pd\n",
    "from web3 import Web3\n",
    "from web3.datastructures import AttributeDict\n",
    "from web3.exceptions import (ContractLogicError, ABIFunctionNotFound)\n",
    "\n",
    "######### Enable logging for debug\n",
    "logging.basicConfig(\n",
    "        format='%(asctime)s %(funcName)s %(levelname)s %(message)s',\n",
    "        level='INFO',\n",
    "        datefmt='%Y-%m-%d %H:%M:%S',\n",
    "        )\n",
    "\n",
    "\n",
    "Etherscan_APIKEY   = None #optional, but recommended\n",
    "GearboxAddressProvider = '0xcF64698AFF7E5f27A11dff868AF228653ba53be0'\n",
    "\n",
    "# The dafault RPC from ethersjs, change it if it doesn't work: https://infura.io/docs\n",
    "RPC_Endpoint = 'https://mainnet.infura.io/v3/84842078b09946638c03157f83405213'\n",
    "\n",
    "w3_eth = Web3(Web3.HTTPProvider(RPC_Endpoint, request_kwargs={'timeout': 30}))\n",
    "logging.info (f'Ethereum connected: {w3_eth.isConnected()}')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "48738a8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 21:48:17 <module> INFO Start\n",
      "2023-07-09 21:48:17 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:23 <module> INFO AccountFactory: 0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04\n",
      "2023-07-09 21:48:23 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:29 <module> INFO countCreditAccounts: 5001\n",
      "2023-07-09 21:48:29 <module> INFO countCreditAccountsInStock: 4920\n",
      "2023-07-09 21:48:30 <module> INFO masterCreditAccount: 0x373A292b93ff9017D28E64154ef83b99D5C4e270\n",
      "2023-07-09 21:48:30 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:36 <module> INFO DataCompressor: 0x0a2CA503153Cd5CB2892a0928ac0F71F49a3c194\n",
      "2023-07-09 21:48:36 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:42 <module> INFO ContractsRegister: 0xA50d4E7D8946a7c90652339CDBd262c375d54D99\n",
      "2023-07-09 21:48:42 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:48 <module> INFO CreditManagers: ['0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB', '0x2664cc24CBAd28749B3Dd6fC97A6B402484De527', '0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0', '0xC38478B0A4bAFE964C3526EEFF534d70E1E09017', '0x672461Bfc20DD783444a830Ad4c38b345aB6E2f7', '0x95357303f995e184A7998dA6C6eA35cC728A1900', '0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B', '0xe0bCE4460795281d39c91da9B0275BcA968293de', '0xc62BF8a7889AdF1c5Dc4665486c7683ae6E74e0F', '0xA3E1e0d58FE8dD8C9dd48204699a1178f1B274D8']\n",
      "2023-07-09 21:48:48 <module> INFO CreditManager 1 of 10\n",
      "2023-07-09 21:48:48 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:48:54 <module> INFO version 1\n",
      "2023-07-09 21:48:55 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:49:01 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:49:07 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:49:34 <module> INFO CreditManager 2 of 10\n",
      "2023-07-09 21:49:34 <module> INFO version 1\n",
      "2023-07-09 21:49:56 <module> INFO CreditManager 3 of 10\n",
      "2023-07-09 21:49:56 <module> INFO version 1\n",
      "2023-07-09 21:50:18 <module> INFO CreditManager 4 of 10\n",
      "2023-07-09 21:50:18 <module> INFO version 1\n",
      "2023-07-09 21:50:39 <module> INFO CreditManager 5 of 10\n",
      "2023-07-09 21:50:40 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:50:45 <module> INFO version 2\n",
      "2023-07-09 21:50:47 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:50:54 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:51:00 pull_abi_etherscan INFO etherscan apikey is not set: 5 sec wait due to rate-limit\n",
      "2023-07-09 21:51:44 <module> INFO CreditManager 6 of 10\n",
      "2023-07-09 21:51:45 <module> INFO version 2\n",
      "2023-07-09 21:52:26 <module> INFO CreditManager 7 of 10\n",
      "2023-07-09 21:52:26 <module> INFO version 2\n",
      "2023-07-09 21:53:07 <module> INFO CreditManager 8 of 10\n",
      "2023-07-09 21:53:07 <module> INFO version 2\n",
      "2023-07-09 21:53:48 <module> INFO CreditManager 9 of 10\n",
      "2023-07-09 21:53:49 <module> INFO version 2\n",
      "2023-07-09 21:54:28 <module> INFO CreditManager 10 of 10\n",
      "2023-07-09 21:54:29 <module> INFO version 2\n",
      "2023-07-09 21:55:08 <module> INFO {'0x2664cc24CBAd28749B3Dd6fC97A6B402484De527': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 22,\n",
      "                                                'creditFacade': None,\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 6,\n",
      "                                                'priceOracle': '0x0e74a08443c5E39108520589176Ac12EF65AB080',\n",
      "                                                'symbol': 'USDC',\n",
      "                                                'token': '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48',\n",
      "                                                'version': 1},\n",
      " '0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04': {'creditFacade': None,\n",
      "                                                'decimals': None,\n",
      "                                                'symbol': None,\n",
      "                                                'token': None,\n",
      "                                                'version': None},\n",
      " '0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 43,\n",
      "                                                'creditFacade': '0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'WETH',\n",
      "                                                'token': '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2',\n",
      "                                                'version': 2},\n",
      " '0x672461Bfc20DD783444a830Ad4c38b345aB6E2f7': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 43,\n",
      "                                                'creditFacade': '0xd4fe3eD38250C38A0094224C4B0224b5D5d0e7d9',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'DAI',\n",
      "                                                'token': '0x6B175474E89094C44Da98b954EedeAC495271d0F',\n",
      "                                                'version': 2},\n",
      " '0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 22,\n",
      "                                                'creditFacade': None,\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x0e74a08443c5E39108520589176Ac12EF65AB080',\n",
      "                                                'symbol': 'DAI',\n",
      "                                                'token': '0x6B175474E89094C44Da98b954EedeAC495271d0F',\n",
      "                                                'version': 1},\n",
      " '0x95357303f995e184A7998dA6C6eA35cC728A1900': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 43,\n",
      "                                                'creditFacade': '0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 6,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'USDC',\n",
      "                                                'token': '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48',\n",
      "                                                'version': 2},\n",
      " '0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 22,\n",
      "                                                'creditFacade': None,\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x0e74a08443c5E39108520589176Ac12EF65AB080',\n",
      "                                                'symbol': 'WETH',\n",
      "                                                'token': '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2',\n",
      "                                                'version': 1},\n",
      " '0xA3E1e0d58FE8dD8C9dd48204699a1178f1B274D8': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 43,\n",
      "                                                'creditFacade': '0xf4ecc4e950b563F113b17C5606B31a314B99BFe3',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'FRAX',\n",
      "                                                'token': '0x853d955aCEf822Db058eb8505911ED77F175b99e',\n",
      "                                                'version': 2},\n",
      " '0xC38478B0A4bAFE964C3526EEFF534d70E1E09017': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 22,\n",
      "                                                'creditFacade': None,\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 8,\n",
      "                                                'priceOracle': '0x0e74a08443c5E39108520589176Ac12EF65AB080',\n",
      "                                                'symbol': 'WBTC',\n",
      "                                                'token': '0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599',\n",
      "                                                'version': 1},\n",
      " '0xc62BF8a7889AdF1c5Dc4665486c7683ae6E74e0F': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 43,\n",
      "                                                'creditFacade': '0x700De428aa940000259B1c58F3E44445d360303c',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 8,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'WBTC',\n",
      "                                                'token': '0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599',\n",
      "                                                'version': 2},\n",
      " '0xe0bCE4460795281d39c91da9B0275BcA968293de': {'allowedTokens': {},\n",
      "                                                'allowedTokensCount': 44,\n",
      "                                                'creditFacade': '0x4Ae3EDbDf1C42e3560Cc2D52B8F353F026F67b44',\n",
      "                                                'creditFacade_upgraded': [],\n",
      "                                                'decimals': 18,\n",
      "                                                'priceOracle': '0x6385892aCB085eaa24b745a712C9e682d80FF681',\n",
      "                                                'symbol': 'wstETH',\n",
      "                                                'token': '0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0',\n",
      "                                                'version': 2}}\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 21:55:08 <module> INFO {'0x056Fd409E1d7A124BD7017459dFEa2F387b6d5Cd': {'Price_USD': 1.00167607,\n",
      "                                                'decimals': 2,\n",
      "                                                'symbol': 'GUSD'},\n",
      " '0x06325440D014e39736583c165C2963BA99fAf14E': {'Price_USD': 2010.26013319,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'steCRV'},\n",
      " '0x0A1D4A25d0390899b90bCD22E1Ef155003EA76d7': {'Price_USD': 1.02450287,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxLUSD3CRV-f'},\n",
      " '0x0bc529c00C6401aEF6D220BE8C6Ea1667F6Ad93e': {'Price_USD': 7057.217207552814,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'YFI'},\n",
      " '0x111111111117dC0aa78b770fA6A738034120C302': {'Price_USD': 0.3068821757035416,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': '1INCH'},\n",
      " '0x117A0bab81F25e60900787d98061cCFae023560c': {'Price_USD': 1.00075416,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxcrvFRAX'},\n",
      " '0x11D200ef1409cecA8D6d23e6496550f707772F11': {'Price_USD': 1.06320513,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxcrvPlain3andSUSD'},\n",
      " '0x1494CA1F11D487c2bBe4543E90080AeBa4BA3C2b': {'Price_USD': 70.79501953441024,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'DPI'},\n",
      " '0x15c2471ef46Fa721990730cfa526BcFb45574576': {'Price_USD': 1.03691138,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxgusd3CRV'},\n",
      " '0x1f9840a85d5aF5bf1D1762F925BDADdC4201F984': {'Price_USD': 5.248458093236214,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'UNI'},\n",
      " '0x2260FAC5E5542a773Aa44fBCfeDf7C193bc2C599': {'Price_USD': 30301.48505315,\n",
      "                                                'decimals': 8,\n",
      "                                                'symbol': 'WBTC'},\n",
      " '0x26F7D7509490B292AFBB0443A7A371d345B5f684': {'Price_USD': 0.0,\n",
      "                                                'decimals': 6,\n",
      "                                                'symbol': 'G-OBS'},\n",
      " '0x276187f24D41745513cbE2Bd5dFC33a4d8CDc9ed': {'Price_USD': 1.00075416,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxcrvFRAX'},\n",
      " '0x30D9410ED1D5DA1F6C8391af5338C93ab8d4035C': {'Price_USD': 1.0262445,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvx3Crv'},\n",
      " '0x3175Df0976dFA876431C2E9eE6Bc45b65d3473CC': {'Price_USD': 1.00075416,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'crvFRAX'},\n",
      " '0x3432B6A60D23Ca0dFCa7761B7ab56459D9C964D0': {'Price_USD': 6.3377956,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'FXS'},\n",
      " '0x34fB99abBAFb4e87e256960D572664c6ADc301B8': {'Price_USD': 1.03691138,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxgusd3CRV'},\n",
      " '0x4E15361FD6b4BB609Fa63C81A2be19d873717870': {'Price_USD': 0.27296962877771314,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'FTM'},\n",
      " '0x4e3FBD56CD56c3e72c1403e103b45Db9da5B9D2B': {'Price_USD': 4.03758611,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'CVX'},\n",
      " '0x514910771AF9Ca656af840dff83E8264EcF986CA': {'Price_USD': 6.176638515668133,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LINK'},\n",
      " '0x57Ab1ec28D129707052df4dF418D58a2D46d5f51': {'Price_USD': 0.99882412,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'sUSD'},\n",
      " '0x5A98FcBEA516Cf06857215779Fd812CA3beF1B32': {'Price_USD': 0.0,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LDO'},\n",
      " '0x5f98805A4E8be255a32880FDeC7F6728C6568bA0': {'Price_USD': 1.00059866,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LUSD'},\n",
      " '0x6B175474E89094C44Da98b954EedeAC495271d0F': {'Price_USD': 0.99990354,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'DAI'},\n",
      " '0x6B3595068778DD592e39A122f4f5a5cF09C90fE2': {'Price_USD': 0.6325828062983819,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'SUSHI'},\n",
      " '0x6DEA81C8171D0bA574754EF6F8b412F2Ed88c54D': {'Price_USD': 0.0,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LQTY'},\n",
      " '0x6c3F90f043a72FA612cbac8115EE7e52BDe6E490': {'Price_USD': 1.0262445,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': '3Crv'},\n",
      " '0x7Fc66500c84A76Ad7e9c93437bFc5Ac33E2DDaE9': {'Price_USD': 72.6822696294724,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'AAVE'},\n",
      " '0x7e1992A7F28dAA5f6a2d34e2cd40f962f37B172C': {'Price_USD': 1.06320513,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxcrvPlain3andSUSD'},\n",
      " '0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0': {'Price_USD': 2089.3341501,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'wstETH'},\n",
      " '0x853d955aCEf822Db058eb8505911ED77F175b99e': {'Price_USD': 0.99949728,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'FRAX'},\n",
      " '0x9518c9063eB0262D791f38d8d6Eb0aca33c63ed0': {'Price_USD': 2010.26013319,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxsteCRV'},\n",
      " '0x956F47F50A910163D8BF957Cf5846D573E7f87CA': {'Price_USD': 0.9539693123921915,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'FEI'},\n",
      " '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48': {'Price_USD': 1.00003476,\n",
      "                                                'decimals': 6,\n",
      "                                                'symbol': 'USDC'},\n",
      " '0xA696a63cc78DfFa1a63E9E50587C197387FF6C7E': {'Price_USD': 30963.504020200002,\n",
      "                                                'decimals': 8,\n",
      "                                                'symbol': 'yvWBTC'},\n",
      " '0xB4AdA607B9d6b2c9Ee07A275e9616B84AC560139': {'Price_USD': 1.13707234,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'yvCurve-FRAX'},\n",
      " '0xC011a73ee8576Fb46F5E1c5751cA3B9Fe0af2a6F': {'Price_USD': 2.076,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'SNX'},\n",
      " '0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2': {'Price_USD': 1868.94,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'WETH'},\n",
      " '0xC25a3A3b969415c80451098fa907EC722572917F': {'Price_USD': 1.06320513,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'crvPlain3andSUSD'},\n",
      " '0xD2967f45c4f384DEEa880F807Be904762a3DeA07': {'Price_USD': 1.03691138,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'gusd3CRV'},\n",
      " '0xD533a949740bb3306d119CC777fa900bA034cd52': {'Price_USD': 0.78998152,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'CRV'},\n",
      " '0xEd279fDD11cA84bEef15AF5D39BB4d4bEE23F0cA': {'Price_USD': 1.02450287,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LUSD3CRV-f'},\n",
      " '0xFB9B2f06FDb404Fd3E2278E9A9edc8f252F273d0': {'Price_USD': 1.02450287,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxLUSD3CRV-f'},\n",
      " '0xa258C4606Ca8206D8aA700cE2143D7db854D168c': {'Price_USD': 1945.59652793,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'yvWETH'},\n",
      " '0xa354F35829Ae975e850e23e9615b11Da1B3dC4DE': {'Price_USD': 1.03802408,\n",
      "                                                'decimals': 6,\n",
      "                                                'symbol': 'yvUSDC'},\n",
      " '0xaF314b088B53835d5cF4e4CB81beABa5934a61fe': {'Price_USD': 1.01006714,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxFRAX3CRV-f'},\n",
      " '0xae7ab96520DE3A18E5e111B5EaAb095312D7fE84': {'Price_USD': 1868.26034352,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stETH'},\n",
      " '0xbAc7a431146aeAf3F57A16b9954f332Fd292F270': {'Price_USD': 1.0262445,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvx3Crv'},\n",
      " '0xbE0F6478E0E4894CFb14f32855603A083A57c7dA': {'Price_USD': 1.01006714,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'cvxFRAX3CRV-f'},\n",
      " '0xc00e94Cb662C3520282E6f5717214004A7f26888': {'Price_USD': 52.31637992177216,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'COMP'},\n",
      " '0xd2877702675e6cEb975b4A1dFf9fb7BAF4C91ea9': {'Price_USD': 0.0,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'LUNA'},\n",
      " '0xd632f22692FaC7611d2AA1C0D552930D43CAEd3B': {'Price_USD': 1.01006714,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'FRAX3CRV-f'},\n",
      " '0xdA816459F1AB5631232FE5e97a05BBBb94970c95': {'Price_USD': 1.05942737,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'yvDAI'},\n",
      " '0xdAC17F958D2ee523a2206206994597C13D831ec7': {'Price_USD': 1.00037768,\n",
      "                                                'decimals': 6,\n",
      "                                                'symbol': 'USDT'},\n",
      " '0xdCD90C7f6324cfa40d7169ef80b12031770B4325': {'Price_USD': 2208.12197574,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'yvCurve-stETH'},\n",
      " '0xe15B7D80a51e1fe54aC355CaBE848Efce5289BDB': {'Price_USD': 2010.26013319,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'stkcvxsteCRV'},\n",
      " '0xf568F6C71aE0439B8d3FFD60Ceba9B1DcB5819bF': {'Price_USD': 0.0,\n",
      "                                                'decimals': 18,\n",
      "                                                'symbol': 'G-BLOCK'}}\n"
     ]
    }
   ],
   "source": [
    "logging.info('Start')\n",
    "AddressProvider = Web3.toChecksumAddress(GearboxAddressProvider)\n",
    "AddressProvider_abi = pull_abi_etherscan(AddressProvider)\n",
    "\n",
    "AccountFactory = w3_eth.eth.contract(address=AddressProvider, abi=AddressProvider_abi).functions.getAccountFactory().call()\n",
    "logging.info(f'AccountFactory: {AccountFactory}')\n",
    "AccountFactory_abi = pull_abi_etherscan(AccountFactory)\n",
    "countCreditAccounts = w3_eth.eth.contract(address=AccountFactory, abi=AccountFactory_abi).functions.countCreditAccounts().call()\n",
    "countCreditAccountsInStock = w3_eth.eth.contract(address=AccountFactory, abi=AccountFactory_abi).functions.countCreditAccountsInStock().call()\n",
    "logging.info(f'countCreditAccounts: {countCreditAccounts}')\n",
    "logging.info(f'countCreditAccountsInStock: {countCreditAccountsInStock}')\n",
    "masterCreditAccount = w3_eth.eth.contract(address=AccountFactory, abi=AccountFactory_abi).functions.masterCreditAccount().call()\n",
    "logging.info(f'masterCreditAccount: {masterCreditAccount}')\n",
    "CreditAccount_abi = pull_abi_etherscan(masterCreditAccount)\n",
    "\n",
    "DataCompressor = w3_eth.eth.contract(address=AddressProvider, abi=AddressProvider_abi).functions.getDataCompressor().call()\n",
    "logging.info(f'DataCompressor: {DataCompressor}')\n",
    "DataCompressor_abi = pull_abi_etherscan(DataCompressor)\n",
    "\n",
    "ContractsRegister = w3_eth.eth.contract(address=AddressProvider, abi=AddressProvider_abi).functions.getContractsRegister().call()\n",
    "logging.info(f'ContractsRegister: {ContractsRegister}')\n",
    "ContractsRegister_abi = pull_abi_etherscan(ContractsRegister)\n",
    "\n",
    "CreditManagers = w3_eth.eth.contract(address=ContractsRegister, abi=ContractsRegister_abi).functions.getCreditManagers().call()\n",
    "logging.info(f'CreditManagers: {CreditManagers}')\n",
    "version = [None] * len(CreditManagers)\n",
    "\n",
    "cm_dict = {AccountFactory: {'token': None, 'symbol': None, 'decimals': None, 'version': None,'creditFacade': None}}\n",
    "allowedTokens = {}\n",
    "CreditManager_abi = Token_abi = CreditFilter_abi = creditFacade_abi = priceOracle_abi = creditConfigurator_abi = ''\n",
    "\n",
    "for i, CreditManager in enumerate(CreditManagers):\n",
    "    logging.info(f'CreditManager {i+1} of {len(CreditManagers)}')\n",
    "    \n",
    "    if not CreditManager_abi:\n",
    "        CreditManager_abi = pull_abi_etherscan(CreditManager)\n",
    "\n",
    "    version[i] = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.version().call() \n",
    "    \n",
    "    if i>0 and version[i]!=version[i-1]:\n",
    "        CreditManager_abi = pull_abi_etherscan(CreditManager)\n",
    "    \n",
    "    logging.info(f'version {version[i]}')\n",
    "    \n",
    "    \n",
    "    if version[i] == 1:\n",
    "        Token = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.underlyingToken().call()  \n",
    "        CreditFilter = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.creditFilter().call()\n",
    "        if not CreditFilter_abi:\n",
    "            CreditFilter_abi = pull_abi_etherscan(CreditFilter) \n",
    "        allowedTokensCount = w3_eth.eth.contract(address=CreditFilter, abi=CreditFilter_abi).functions.allowedTokensCount().call()  \n",
    "        priceOracle        = w3_eth.eth.contract(address=CreditFilter, abi=CreditFilter_abi).functions.priceOracle().call()\n",
    "        wethAddress        = w3_eth.eth.contract(address=CreditFilter, abi=CreditFilter_abi).functions.wethAddress().call()\n",
    "        CreditManager_v1_abi = CreditManager_abi\n",
    "        creditFacade = None\n",
    "    else:\n",
    "        Token = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.underlying().call() \n",
    "        allowedTokensCount = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.collateralTokensCount().call()\n",
    "        priceOracle        = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.priceOracle().call()\n",
    "        wethAddress        = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.wethAddress().call()\n",
    "\n",
    "        creditFacade = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.creditFacade().call()\n",
    "        if not creditFacade_abi:\n",
    "            creditFacade_abi = pull_abi_etherscan(creditFacade)\n",
    "            \n",
    "        if not creditConfigurator_abi:\n",
    "            creditConfigurator = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.creditConfigurator().call()\n",
    "            creditConfigurator_abi = pull_abi_etherscan(creditConfigurator)\n",
    "    \n",
    "    if not Token_abi:\n",
    "        Token_abi = pull_abi_etherscan(Token) \n",
    "    \n",
    "    if not priceOracle_abi or version[i]!=version[i-1]:\n",
    "        priceOracle_abi = pull_abi_etherscan(priceOracle)\n",
    "    \n",
    "    Token_symbol = w3_eth.eth.contract(address=Token, abi=Token_abi).functions.symbol().call()\n",
    "    Token_decimals = w3_eth.eth.contract(address=Token, abi=Token_abi).functions.decimals().call()\n",
    "    cm_dict[CreditManager] = {'version' : version[i],\n",
    "                              'creditFacade': creditFacade,\n",
    "                              'creditFacade_upgraded':[],\n",
    "                              'token': Token,\n",
    "                              'symbol': Token_symbol,\n",
    "                              'decimals': Token_decimals,\n",
    "                              'priceOracle' : priceOracle,\n",
    "                              'allowedTokensCount': allowedTokensCount,\n",
    "                              'allowedTokens':{},\n",
    "                             }\n",
    "    for token_id in range(allowedTokensCount):\n",
    "        if version[i] == 1:\n",
    "            allowed_token = w3_eth.eth.contract(address=CreditFilter, abi=CreditFilter_abi).functions.allowedTokens(token_id).call()\n",
    "        else:\n",
    "            allowed_token = w3_eth.eth.contract(address=CreditManager, abi=CreditManager_abi).functions.collateralTokens(token_id).call()\n",
    "            allowed_token = allowed_token[0]\n",
    "            \n",
    "        allowed_token_symbol = w3_eth.eth.contract(address=allowed_token, abi=Token_abi).functions.symbol().call()\n",
    "        allowed_token_decimals = w3_eth.eth.contract(address=allowed_token, abi=Token_abi).functions.decimals().call()\n",
    "        \n",
    "        \n",
    "        \n",
    "        if version[i] == 1:\n",
    "            #try:\n",
    "            #    allowed_token_weth_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.getLastPrice(allowed_token, wethAddress).call()\n",
    "            #    allowed_token_weth_priceOracle = allowed_token_weth_priceOracle*(10**allowed_token_decimals)/1e18\n",
    "            #except ContractLogicError:\n",
    "            #    allowed_token_weth_priceOracle = None\n",
    "            #    logging.info(allowed_token_symbol+'-WETH getLastPrice error')\n",
    "            #try:\n",
    "            #    allowed_token_underlying_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.getLastPrice(allowed_token, Token).call()\n",
    "            #    allowed_token_underlying_priceOracle = allowed_token_underlying_priceOracle*(10**allowed_token_decimals)/(10**Token_decimals)\n",
    "            #except ContractLogicError:\n",
    "            #    allowed_token_underlying_priceOracle = None\n",
    "            #    logging.info(allowed_token_symbol+'-underlying getLastPrice error')\n",
    "            USDC_Address = '0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48'    \n",
    "            allowed_token_usd_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.getLastPrice(allowed_token, USDC_Address).call()/1e18\n",
    "        else:\n",
    "            try:                \n",
    "                #allowed_token_weth_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.convert(10**8, allowed_token, wethAddress).call()\n",
    "                #allowed_token_weth_priceOracle = allowed_token_weth_priceOracle*10**(-8)\n",
    "                #allowed_token_underlying_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.convert(10**8, allowed_token, Token).call()\n",
    "                #allowed_token_underlying_priceOracle = allowed_token_underlying_priceOracle*10**(-8)                \n",
    "\n",
    "                allowed_token_usd_priceOracle = w3_eth.eth.contract(address=priceOracle, abi=priceOracle_abi).functions.getPrice(allowed_token).call()\n",
    "                allowed_token_usd_priceOracle=allowed_token_usd_priceOracle*10**(-8) \n",
    "            except ContractLogicError:\n",
    "                #allowed_token_usd_priceOracle = allowed_token_underlying_priceOracle= None\n",
    "                allowed_token_usd_priceOracle = None\n",
    "                logging.info(allowed_token_symbol+'- getPrice error')            \n",
    "            \n",
    "        Token_dict={'symbol'          : allowed_token_symbol, \n",
    "                    'decimals'        : allowed_token_decimals,\n",
    "                    'Price_USD'       : allowed_token_usd_priceOracle,\n",
    "                    #'Price_WETH'      : allowed_token_weth_priceOracle,\n",
    "                    #'Price_'+cm_dict[CreditManager]['symbol']: allowed_token_underlying_priceOracle,\n",
    "                    }\n",
    "\n",
    "        if allowed_token in allowedTokens:\n",
    "            allowedTokens[allowed_token].update(Token_dict)\n",
    "        else:\n",
    "            allowedTokens[allowed_token] = Token_dict\n",
    "    \n",
    "df_abi = parse_abi({'AddressProvider':AddressProvider_abi, \n",
    "                    'AccountFactory': AccountFactory_abi, \n",
    "                    'DataCompressor': DataCompressor_abi,\n",
    "                    'CreditAccount': CreditAccount_abi,                     \n",
    "                    'CreditManager': CreditManager_abi,\n",
    "                    'creditFacade': creditFacade_abi,               \n",
    "                    'CreditManager_v1': CreditManager_v1_abi,\n",
    "                    'creditConfigurator':creditConfigurator_abi,\n",
    "                   })\n",
    "\n",
    "logging.info(pformat(cm_dict)) \n",
    "logging.info(pformat(allowedTokens))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "3a94c68e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame()\n",
    "df['id'] = range(countCreditAccounts)\n",
    "for ids in list(chunks(list(df['id']), 1000)): #chunk size for multicall = 1000 (reduce in case of issues)\n",
    "    id_range = list(df['id'].isin(ids))\n",
    "    d_ca = get_data_multicall(w3_eth, df.loc[id_range], 'creditAccounts', df_abi[(df_abi['contract_type']=='AccountFactory')], AccountFactory)\n",
    "    df.loc[id_range,'CA'] = df.loc[id_range].apply(lambda x: d_ca[x['id']], axis=1)\n",
    "\n",
    "    d_cm = get_data_multicall(w3_eth, df.loc[id_range], 'creditManager', df_abi[(df_abi['contract_type']=='CreditAccount')])\n",
    "    df.loc[id_range,'CM'] = df.loc[id_range].apply(lambda x: d_cm[x['id']], axis=1)\n",
    "    \n",
    "    d_amount = get_data_multicall(w3_eth, df.loc[id_range], 'borrowedAmount', df_abi[(df_abi['contract_type']=='CreditAccount')])\n",
    "    df.loc[id_range,'borrowedAmount'] = df.loc[id_range].apply(lambda x: d_amount[x['id']] if d_amount[x['id']] > 1 else 0 , axis=1)\n",
    "    \n",
    "    d_since = get_data_multicall(w3_eth, df.loc[id_range], 'since', df_abi)\n",
    "    df.loc[id_range,'Since'] = df.loc[id_range].apply(lambda x: d_since[x['id']], axis=1)\n",
    "\n",
    "df['Since'] = df['Since'].astype(int)\n",
    "df['Decimals'] = df.apply(lambda x: cm_dict[x['CM']]['decimals'], axis=1)\n",
    "df['Symbol'] = df.apply(lambda x: cm_dict[x['CM']]['symbol'] , axis=1)\n",
    "df['CF'] = df.apply(lambda x: cm_dict[x['CM']]['creditFacade'] , axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "da695e8a",
   "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>CA</th>\n",
       "      <th>CM</th>\n",
       "      <th>borrowedAmount</th>\n",
       "      <th>Since</th>\n",
       "      <th>Decimals</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>CF</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>2000000000000000000000</td>\n",
       "      <td>13858003</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>1250000000000000000</td>\n",
       "      <td>13862947</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>3000000000000000000000</td>\n",
       "      <td>13862953</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>900000000000000000</td>\n",
       "      <td>13862987</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>854784188060446794</td>\n",
       "      <td>13864162</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4996</th>\n",
       "      <td>4996</td>\n",
       "      <td>0x7E0177E914e3Cf5B69067874d4316BF552037eB2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4997</th>\n",
       "      <td>4997</td>\n",
       "      <td>0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4998</th>\n",
       "      <td>4998</td>\n",
       "      <td>0xD1168F931863CCcEf0102041f275cBA5C49907a2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4999</th>\n",
       "      <td>4999</td>\n",
       "      <td>0xee6B3a0B5a750902c4513508bef90963e88F1c80</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5000</th>\n",
       "      <td>5000</td>\n",
       "      <td>0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5001 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                          CA  \\\n",
       "0        0  0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1   \n",
       "1        1  0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1   \n",
       "2        2  0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c   \n",
       "3        3  0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5   \n",
       "4        4  0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654   \n",
       "...    ...                                         ...   \n",
       "4996  4996  0x7E0177E914e3Cf5B69067874d4316BF552037eB2   \n",
       "4997  4997  0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515   \n",
       "4998  4998  0xD1168F931863CCcEf0102041f275cBA5C49907a2   \n",
       "4999  4999  0xee6B3a0B5a750902c4513508bef90963e88F1c80   \n",
       "5000  5000  0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3   \n",
       "\n",
       "                                              CM          borrowedAmount  \\\n",
       "0     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB  2000000000000000000000   \n",
       "1     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     1250000000000000000   \n",
       "2     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB  3000000000000000000000   \n",
       "3     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0      900000000000000000   \n",
       "4     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0      854784188060446794   \n",
       "...                                          ...                     ...   \n",
       "4996  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04                       0   \n",
       "4997  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04                       0   \n",
       "4998  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04                       0   \n",
       "4999  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04                       0   \n",
       "5000  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04                       0   \n",
       "\n",
       "         Since  Decimals Symbol    CF  \n",
       "0     13858003      18.0    DAI  None  \n",
       "1     13862947      18.0   WETH  None  \n",
       "2     13862953      18.0    DAI  None  \n",
       "3     13862987      18.0   WETH  None  \n",
       "4     13864162      18.0   WETH  None  \n",
       "...        ...       ...    ...   ...  \n",
       "4996  13819220       NaN   None  None  \n",
       "4997  13819221       NaN   None  None  \n",
       "4998  13819222       NaN   None  None  \n",
       "4999  13819222       NaN   None  None  \n",
       "5000  13819223       NaN   None  None  \n",
       "\n",
       "[5001 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "e90372db",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = df[(df['CA']=='0x24Ed6EB99C9AA7229336aaA05b4f3B07827404eA')]\n",
    "#display(df)\n",
    "\n",
    "#cm_dict['0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B']['creditFacade_upgraded'] = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "27017e07",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 21:56:01 <module> INFO OpenCreditAccount_topics: 0x7b20ae77867a263a1074203a2da261ef0d096c99395c59c9d4a0104b9f334a27, 0xfa2baf5d3eb95569f312f22477b246f9d4c50276f1cb3ded8e1aeadcbc07a763\n",
      "2023-07-09 21:56:01 <module> INFO CloseCreditAccount_topic: 0xca05b632388199c23de1352b2e96fd72a0ec71611683330b38060c004bbf0a76, 0x460ad03b1cf79b1d64d3aefa28475f110ab66e84649c52bb41ed796b9b391981\n",
      "2023-07-09 21:56:01 <module> INFO RepayCreditAccount_topic: 0xe7c7987373a0cc4913d307f23ab8ef02e0333a2af445065e2ef7636cffc6daa7\n",
      "2023-07-09 21:56:01 <module> INFO LiquidateCreditAccount_topic: 0x5e5da6c348e62989f9cfe029252433fc99009b7d28fa3c20d675520a10ff5896, 0x7dfecd8419723a9d3954585a30c2a270165d70aafa146c11c1e1b88ae1439064\n",
      "2023-07-09 21:56:01 <module> INFO LiquidateExpiredCreditAccount_topic: 0xfada13ff2d5e1a1d9da37ad4a4130893b34e3c69c32b17f87ec31fd661b86707\n",
      "2023-07-09 21:56:05 <module> INFO CreditFacade: 0xf6f4F24ae50206A07B8B32629AeB6cb1837d854F\n",
      "2023-07-09 21:56:05 <module> INFO CF_logs count: 299\n",
      "2023-07-09 21:56:06 <module> INFO CreditFacade: 0xd4fe3eD38250C38A0094224C4B0224b5D5d0e7d9\n",
      "2023-07-09 21:56:06 <module> INFO CF_logs count: 0\n",
      "2023-07-09 21:56:07 <module> INFO CreditFacade: 0x61fbb350e39cc7bF22C01A469cf03085774184aa\n",
      "2023-07-09 21:56:08 <module> INFO CF_logs count: 774\n",
      "2023-07-09 21:56:08 <module> INFO CreditFacade: 0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245\n",
      "2023-07-09 21:56:08 <module> INFO CF_logs count: 3\n",
      "2023-07-09 21:56:09 <module> INFO CreditFacade: 0xC59135f449bb623501145443c70A30eE648Fa304\n",
      "2023-07-09 21:56:10 <module> INFO CF_logs count: 724\n",
      "2023-07-09 21:56:10 <module> INFO CreditFacade: 0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62\n",
      "2023-07-09 21:56:10 <module> INFO CF_logs count: 0\n",
      "2023-07-09 21:56:11 <module> INFO CreditFacade: 0x04d692bCB03D4b410CBB5Bf09967eB3ce8D12546\n",
      "2023-07-09 21:56:11 <module> INFO CF_logs count: 17\n",
      "2023-07-09 21:56:11 <module> INFO CreditFacade: 0x4Ae3EDbDf1C42e3560Cc2D52B8F353F026F67b44\n",
      "2023-07-09 21:56:11 <module> INFO CF_logs count: 0\n",
      "2023-07-09 21:56:12 <module> INFO CreditFacade: 0xAfae62D1b38d635a3089A36B27f3c1Acc4fa3243\n",
      "2023-07-09 21:56:12 <module> INFO CF_logs count: 26\n",
      "2023-07-09 21:56:12 <module> INFO CreditFacade: 0x700De428aa940000259B1c58F3E44445d360303c\n",
      "2023-07-09 21:56:13 <module> INFO CF_logs count: 0\n",
      "2023-07-09 21:56:13 <module> INFO CreditFacade: 0xbA5B2C3b259F0ceA02CFC6Da86f0fc4F4F2c1aE3\n",
      "2023-07-09 21:56:14 <module> INFO CF_logs count: 23\n",
      "2023-07-09 21:56:14 <module> INFO CreditFacade: 0xf4ecc4e950b563F113b17C5606B31a314B99BFe3\n",
      "2023-07-09 21:56:14 <module> INFO CF_logs count: 0\n",
      "2023-07-09 21:56:18 <module> INFO 500\n",
      "2023-07-09 21:56:22 <module> INFO 1000\n",
      "2023-07-09 21:56:25 <module> INFO 1372 end\n"
     ]
    }
   ],
   "source": [
    "#newConfigurator, CreditFacadeUpgraded \n",
    "newConfigurator_topic = df_abi[(df_abi['name']=='NewConfigurator') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "CreditFacadeUpgraded_topic = df_abi[(df_abi['name']=='CreditFacadeUpgraded') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "\n",
    "#Open, Close, Repay, Liquidate\n",
    "OpenCreditAccount_topic             =  df_abi[(df_abi['name']=='OpenCreditAccount') &(df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "OpenCreditAccount_topic_v1          =  df_abi[(df_abi['name']=='OpenCreditAccount') &(df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "AddCollateral_topic                 =  df_abi[(df_abi['name']=='AddCollateral') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "AddCollateral_topic_v1              =  df_abi[(df_abi['name']=='AddCollateral') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "CloseCreditAccount_topic            =  df_abi[(df_abi['name']=='CloseCreditAccount') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "CloseCreditAccount_topic_v1         =  df_abi[(df_abi['name']=='CloseCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "RepayCreditAccount_topic_v1         =  df_abi[(df_abi['name']=='RepayCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateCreditAccount_topic        =  df_abi[(df_abi['name']=='LiquidateCreditAccount') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateCreditAccount_topic_v1     =  df_abi[(df_abi['name']=='LiquidateCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateExpiredCreditAccount_topic =  df_abi[(df_abi['name']=='LiquidateExpiredCreditAccount')  & (df_abi['contract_type']=='creditFacade') &(df_abi['type']=='event')]['topic'].values[0]\n",
    "\n",
    "logging.info(f'OpenCreditAccount_topics: {OpenCreditAccount_topic_v1}, {OpenCreditAccount_topic}' )\n",
    "logging.info(f'CloseCreditAccount_topic: {CloseCreditAccount_topic_v1}, {CloseCreditAccount_topic}')\n",
    "logging.info(f'RepayCreditAccount_topic: {RepayCreditAccount_topic_v1}')\n",
    "logging.info(f'LiquidateCreditAccount_topic: {LiquidateCreditAccount_topic_v1}, {LiquidateCreditAccount_topic}')\n",
    "logging.info(f'LiquidateExpiredCreditAccount_topic: {LiquidateExpiredCreditAccount_topic}')\n",
    "\n",
    "logs = pd.DataFrame()\n",
    "for CM in CreditManagers:\n",
    "    \n",
    "    if len(df.loc[df['CM']==CM]['Since'])>0:\n",
    "        block_from = df.loc[df['CM']==CM]['Since'].min()\n",
    "        \n",
    "        CM_logs = get_logs(w3_eth, CM, df_abi,\n",
    "                           [[OpenCreditAccount_topic_v1,\n",
    "                             AddCollateral_topic_v1,\n",
    "                             CloseCreditAccount_topic_v1,\n",
    "                             RepayCreditAccount_topic_v1,\n",
    "                             LiquidateCreditAccount_topic_v1,]\n",
    "                             ],\n",
    "                             block_from,\n",
    "                             'latest')\n",
    "        logs = logs.append(CM_logs, ignore_index = True)\n",
    "        \n",
    "        if cm_dict[CM]['creditFacade']:\n",
    "            cm_dict[CM]['creditFacade_upgraded'] = []\n",
    "            cc_upgraded = get_logs(w3_eth, CM, df_abi, [[newConfigurator_topic]], 0, 'latest')['args'].values\n",
    "            cc_upgraded = [x['newConfigurator'] for x in cc_upgraded]\n",
    "            \n",
    "            for cc in cc_upgraded:\n",
    "                cf_upgraded = get_logs(w3_eth, cc, df_abi, [[CreditFacadeUpgraded_topic]], 0, 'latest')['args'].values\n",
    "                cf_upgraded = [x['newCreditFacade'] for x in cf_upgraded]\n",
    "                \n",
    "                for cf in cf_upgraded:\n",
    "                    logging.info(f'CreditFacade: {cf}')\n",
    "                    if cf not in cm_dict[CM]['creditFacade_upgraded']:\n",
    "                        CF_logs = get_logs(w3_eth, cf, df_abi,\n",
    "                                           [[OpenCreditAccount_topic,\n",
    "                                             AddCollateral_topic, \n",
    "                                             CloseCreditAccount_topic,\n",
    "                                             LiquidateCreditAccount_topic,\n",
    "                                             LiquidateExpiredCreditAccount_topic,]\n",
    "                                             ],\n",
    "                                             block_from,\n",
    "                                             'latest')\n",
    "                        cm_dict[CM]['creditFacade_upgraded'] = cm_dict[CM]['creditFacade_upgraded']+[cf]\n",
    "                        logs = logs.append(CF_logs, ignore_index = True)\n",
    "                        \n",
    "                        logging.info(f'CF_logs count: {len(CF_logs)}')                    \n",
    "\n",
    "i=0\n",
    "for row in df.loc[df['CM'].isin(CreditManagers)].itertuples():\n",
    "    i+=1\n",
    "    open_events       = logs[(logs['address'].isin([row.CM]+cm_dict[row.CM]['creditFacade_upgraded'])) & (logs['blockNumber']>=row.Since) & (logs['event']=='OpenCreditAccount')]['args'].values \n",
    "    collateral_events = logs[(logs['address'].isin([row.CM]+cm_dict[row.CM]['creditFacade_upgraded'])) & (logs['blockNumber']>=row.Since) & (logs['event']=='AddCollateral')]['args'].values \n",
    "    close_events      = logs[(logs['address'].isin([row.CM]+cm_dict[row.CM]['creditFacade_upgraded'])) & (logs['blockNumber']>=row.Since) & (logs['event']=='CloseCreditAccount')]['args'].values \n",
    "    repay_events      = logs[(logs['address'].isin([row.CM]+cm_dict[row.CM]['creditFacade_upgraded'])) & (logs['blockNumber']>=row.Since) & (logs['event']=='RepayCreditAccount')]['args'].values\n",
    "    liquidate_event   = logs[(logs['address'].isin([row.CM]+cm_dict[row.CM]['creditFacade_upgraded'])) & (logs['blockNumber']>=row.Since) & (logs['event'].isin(['LiquidateCreditAccount', 'LiquidateExpiredCreditAccount']))]['args'].values\n",
    "\n",
    "    CA_open_event = [x for x in open_events if x['creditAccount']== row.CA] # Open\n",
    "    if len(CA_open_event) > 0:\n",
    "        Borrower = CA_open_event[0]['onBehalfOf']\n",
    "        df.loc[df['id']==row.id, 'Borrower'] = Borrower\n",
    "        \n",
    "        Collateral = sum([x['amount'] for x in CA_open_event if 'amount' in x])\n",
    "        CA_collateral_event = [x for x in collateral_events if x['onBehalfOf']== Borrower] # Open\n",
    "        if len(CA_collateral_event) > 0:\n",
    "            Collateral = Collateral + sum(\n",
    "                                            [(x['value']*(allowedTokens[x['token']]['Price_USD'])*(10**-allowedTokens[x['token']]['decimals']))\n",
    "                                             /(allowedTokens[cm_dict[row.CM]['token']]['Price_USD']*(10**-row.Decimals))\n",
    "                                            for x in CA_collateral_event \n",
    "                                            if allowedTokens[cm_dict[row.CM]['token']]['Price_USD']>0\n",
    "                                            ]\n",
    "                                         )\n",
    "        df.loc[df['id']==row.id, 'Collateral'] = Collateral\n",
    "                           \n",
    "        CA_close_event = [x for x in close_events if x['to']== Borrower] # Close\n",
    "        if len(CA_close_event) > 0:\n",
    "            df.loc[df['id']==row.id, ['Borrower', 'borrowedAmount', 'Collateral']] = [None, 0, 0]\n",
    "\n",
    "        CA_repay_event = [x for x in repay_events if x['to']== Borrower] # Repay\n",
    "        if len(CA_repay_event) > 0:\n",
    "            df.loc[df['id']==row.id, ['Borrower', 'borrowedAmount', 'Collateral']] = [None, 0, 0]\n",
    "\n",
    "        CA_liquidate_event = [x for x in liquidate_event if ('owner' in x and x['owner']==Borrower) or ('borrower' in x and x['borrower']==Borrower)] # Liquidate\n",
    "        if len(CA_liquidate_event) > 0:\n",
    "            df.loc[df['id']==row.id, ['Borrower', 'borrowedAmount', 'Collateral']] = [None, 0, 0]\n",
    "            \n",
    "    if i % 500 == 0:\n",
    "        logging.info (i)\n",
    "\n",
    "logging.info(f'{i} end')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "1aebe686",
   "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>CA</th>\n",
       "      <th>CM</th>\n",
       "      <th>borrowedAmount</th>\n",
       "      <th>Since</th>\n",
       "      <th>Decimals</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>CF</th>\n",
       "      <th>Borrower</th>\n",
       "      <th>Collateral</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>0</td>\n",
       "      <td>13858003</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13862947</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>0</td>\n",
       "      <td>13862953</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13862987</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13864162</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4996</th>\n",
       "      <td>4996</td>\n",
       "      <td>0x7E0177E914e3Cf5B69067874d4316BF552037eB2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4997</th>\n",
       "      <td>4997</td>\n",
       "      <td>0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4998</th>\n",
       "      <td>4998</td>\n",
       "      <td>0xD1168F931863CCcEf0102041f275cBA5C49907a2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4999</th>\n",
       "      <td>4999</td>\n",
       "      <td>0xee6B3a0B5a750902c4513508bef90963e88F1c80</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5000</th>\n",
       "      <td>5000</td>\n",
       "      <td>0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5001 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                          CA  \\\n",
       "0        0  0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1   \n",
       "1        1  0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1   \n",
       "2        2  0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c   \n",
       "3        3  0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5   \n",
       "4        4  0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654   \n",
       "...    ...                                         ...   \n",
       "4996  4996  0x7E0177E914e3Cf5B69067874d4316BF552037eB2   \n",
       "4997  4997  0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515   \n",
       "4998  4998  0xD1168F931863CCcEf0102041f275cBA5C49907a2   \n",
       "4999  4999  0xee6B3a0B5a750902c4513508bef90963e88F1c80   \n",
       "5000  5000  0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3   \n",
       "\n",
       "                                              CM borrowedAmount     Since  \\\n",
       "0     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB              0  13858003   \n",
       "1     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13862947   \n",
       "2     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB              0  13862953   \n",
       "3     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13862987   \n",
       "4     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13864162   \n",
       "...                                          ...            ...       ...   \n",
       "4996  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819220   \n",
       "4997  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819221   \n",
       "4998  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819222   \n",
       "4999  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819222   \n",
       "5000  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819223   \n",
       "\n",
       "      Decimals Symbol    CF Borrower  Collateral  \n",
       "0         18.0    DAI  None     None         0.0  \n",
       "1         18.0   WETH  None     None         0.0  \n",
       "2         18.0    DAI  None     None         0.0  \n",
       "3         18.0   WETH  None     None         0.0  \n",
       "4         18.0   WETH  None     None         0.0  \n",
       "...        ...    ...   ...      ...         ...  \n",
       "4996       NaN   None  None      NaN         NaN  \n",
       "4997       NaN   None  None      NaN         NaN  \n",
       "4998       NaN   None  None      NaN         NaN  \n",
       "4999       NaN   None  None      NaN         NaN  \n",
       "5000       NaN   None  None      NaN         NaN  \n",
       "\n",
       "[5001 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "c2225c7f",
   "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>CA</th>\n",
       "      <th>CM</th>\n",
       "      <th>borrowedAmount</th>\n",
       "      <th>Since</th>\n",
       "      <th>Decimals</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>CF</th>\n",
       "      <th>Borrower</th>\n",
       "      <th>Collateral</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>0xF91027C04807c71c7771a2314A622dFA0fE509A1</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>1000000000000000000</td>\n",
       "      <td>13864789</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x896b94f4f27f12369698C302e2049cAe86936BbB</td>\n",
       "      <td>1.000000e+18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>0x0B9851B7bcE408dd36d3473a0A0eE8F14ce64ca3</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>290000000000000000</td>\n",
       "      <td>13864924</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0xC16414AC1fedfDAC4F8A09674D994e1BbB9d7113</td>\n",
       "      <td>1.000000e+18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>0xdAC87546b0ea6A448c7F3b69e079b0a616F4CC10</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>5000000000000000000</td>\n",
       "      <td>13868591</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x99BC02c239025E431D5741cC1DbA8CE77fc51CE3</td>\n",
       "      <td>2.500000e+18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>0xB8c3FFFE2E13EFd96594EC6Ea9bBE0b2ABA6210e</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>1000000000000000000</td>\n",
       "      <td>13869000</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x2bb8Ab3C2A9837de97A83c228A07E16928B4f07f</td>\n",
       "      <td>5.000000e+17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>87</td>\n",
       "      <td>0xf74e4AEAA3B75c0662bF6024dcA385bF5fDBa8c0</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>900000000000000000</td>\n",
       "      <td>13884707</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x8CBC2D4c114D6f4411bBC991cE68Db994e45Eb4F</td>\n",
       "      <td>3.000000e+17</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",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1363</th>\n",
       "      <td>1363</td>\n",
       "      <td>0x8137351530f9CD81C87EAe9457A6CAF9f910f000</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>468920089983699958680</td>\n",
       "      <td>17321436</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62</td>\n",
       "      <td>0x316BE293C8f2380769e7b7e7382679FE5a3b6600</td>\n",
       "      <td>9.999015e+19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1365</th>\n",
       "      <td>1365</td>\n",
       "      <td>0x2C0C914DC4ed0d850dDdb978ee04F4a3Ff445dc0</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>300000000000</td>\n",
       "      <td>17482075</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x055B29979f6BC27669Ebd54182588FeF12ffBFc0</td>\n",
       "      <td>1.000000e+11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1367</th>\n",
       "      <td>1367</td>\n",
       "      <td>0x813Ac69E22c3D1c2A7CE95A7A641DB580b9363A0</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>300000000000</td>\n",
       "      <td>17491767</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x9874f9B05B073771c7b98e192543FA2684e61246</td>\n",
       "      <td>6.000000e+10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1370</th>\n",
       "      <td>1370</td>\n",
       "      <td>0xF9eBC97A52E94DA558d4aEE46403D7ef7E465a46</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>120000000000000000000</td>\n",
       "      <td>17600969</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62</td>\n",
       "      <td>0x6e58309CD851A5B124E3A56768a42d12f3B6D104</td>\n",
       "      <td>2.000000e+19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1371</th>\n",
       "      <td>1371</td>\n",
       "      <td>0x1ad45BFBDe1045FDC53da1a7ef3d407eE60402ca</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>125000000180</td>\n",
       "      <td>17647010</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x166E58eA56399d5809FFfe195196554E9934bfF7</td>\n",
       "      <td>4.368875e+10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                          CA  \\\n",
       "8        8  0xF91027C04807c71c7771a2314A622dFA0fE509A1   \n",
       "11      11  0x0B9851B7bcE408dd36d3473a0A0eE8F14ce64ca3   \n",
       "31      31  0xdAC87546b0ea6A448c7F3b69e079b0a616F4CC10   \n",
       "33      33  0xB8c3FFFE2E13EFd96594EC6Ea9bBE0b2ABA6210e   \n",
       "87      87  0xf74e4AEAA3B75c0662bF6024dcA385bF5fDBa8c0   \n",
       "...    ...                                         ...   \n",
       "1363  1363  0x8137351530f9CD81C87EAe9457A6CAF9f910f000   \n",
       "1365  1365  0x2C0C914DC4ed0d850dDdb978ee04F4a3Ff445dc0   \n",
       "1367  1367  0x813Ac69E22c3D1c2A7CE95A7A641DB580b9363A0   \n",
       "1370  1370  0xF9eBC97A52E94DA558d4aEE46403D7ef7E465a46   \n",
       "1371  1371  0x1ad45BFBDe1045FDC53da1a7ef3d407eE60402ca   \n",
       "\n",
       "                                              CM         borrowedAmount  \\\n",
       "8     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    1000000000000000000   \n",
       "11    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     290000000000000000   \n",
       "31    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    5000000000000000000   \n",
       "33    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    1000000000000000000   \n",
       "87    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     900000000000000000   \n",
       "...                                          ...                    ...   \n",
       "1363  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B  468920089983699958680   \n",
       "1365  0x95357303f995e184A7998dA6C6eA35cC728A1900           300000000000   \n",
       "1367  0x95357303f995e184A7998dA6C6eA35cC728A1900           300000000000   \n",
       "1370  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B  120000000000000000000   \n",
       "1371  0x95357303f995e184A7998dA6C6eA35cC728A1900           125000000180   \n",
       "\n",
       "         Since  Decimals Symbol                                          CF  \\\n",
       "8     13864789      18.0   WETH                                        None   \n",
       "11    13864924      18.0   WETH                                        None   \n",
       "31    13868591      18.0   WETH                                        None   \n",
       "33    13869000      18.0   WETH                                        None   \n",
       "87    13884707      18.0   WETH                                        None   \n",
       "...        ...       ...    ...                                         ...   \n",
       "1363  17321436      18.0   WETH  0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62   \n",
       "1365  17482075       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "1367  17491767       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "1370  17600969      18.0   WETH  0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62   \n",
       "1371  17647010       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "\n",
       "                                        Borrower    Collateral  \n",
       "8     0x896b94f4f27f12369698C302e2049cAe86936BbB  1.000000e+18  \n",
       "11    0xC16414AC1fedfDAC4F8A09674D994e1BbB9d7113  1.000000e+18  \n",
       "31    0x99BC02c239025E431D5741cC1DbA8CE77fc51CE3  2.500000e+18  \n",
       "33    0x2bb8Ab3C2A9837de97A83c228A07E16928B4f07f  5.000000e+17  \n",
       "87    0x8CBC2D4c114D6f4411bBC991cE68Db994e45Eb4F  3.000000e+17  \n",
       "...                                          ...           ...  \n",
       "1363  0x316BE293C8f2380769e7b7e7382679FE5a3b6600  9.999015e+19  \n",
       "1365  0x055B29979f6BC27669Ebd54182588FeF12ffBFc0  1.000000e+11  \n",
       "1367  0x9874f9B05B073771c7b98e192543FA2684e61246  6.000000e+10  \n",
       "1370  0x6e58309CD851A5B124E3A56768a42d12f3B6D104  2.000000e+19  \n",
       "1371  0x166E58eA56399d5809FFfe195196554E9934bfF7  4.368875e+10  \n",
       "\n",
       "[81 rows x 10 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df[pd.notna(df['Borrower'])]) # active CAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "084613be",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_cols = [x['name'] for x in df_abi[df_abi['name']=='getCreditAccountData']['abi'].values[0]['outputs'][0]['components']]\n",
    "\n",
    "batchtime = datetime.utcnow()\n",
    "df['batchtime'] = batchtime\n",
    "\n",
    "for ids in list(chunks(list(df[pd.notna(df['Borrower'])].loc[:,'id']), 1000)): #chunk size for multicall = 1000 (reduce in case of issues)\n",
    "    id_range = list(df['id'].isin(ids))\n",
    "    try:\n",
    "        d_data = get_data_multicall(w3_eth, df.loc[id_range], 'getCreditAccountData', df_abi, DataCompressor)\n",
    "    except ContractLogicError:\n",
    "        logging.error('getCreditAccountData: Multicall error, calling it one by one')\n",
    "        d_data = {x:getCreditAccountData(x,y,z) for x,y,z in zip(df.loc[id_range]['id'],df.loc[id_range]['CM'],df.loc[id_range]['Borrower'])}\n",
    "        d_data = AttributeDict.recursive(d_data)\n",
    "    for data in data_cols:\n",
    "        if data not in ['balances', 'inUse', 'borrower', 'addr', 'borrower', 'creditManager', 'since']: #duplicate columns\n",
    "            df.loc[id_range, data] = df.loc[id_range].apply(lambda x: {y:z for y, z in zip(data_cols, d_data[x['id']])}[data] if d_data[x['id']] else None\n",
    "                                                            , axis=1)\n",
    "        \n",
    "    tokens_to_frame = [allowedTokens[x]['symbol'] for x in allowedTokens] \n",
    "    for token in tokens_to_frame:\n",
    "        df.loc[id_range, 'Balance_'+token] = df.loc[id_range].apply(lambda x: get_token_balance(x, token, data_cols, d_data, allowedTokens)\n",
    "                                                        , axis=1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "0bdd5747",
   "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>CA</th>\n",
       "      <th>CM</th>\n",
       "      <th>borrowedAmount</th>\n",
       "      <th>Since</th>\n",
       "      <th>Decimals</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>CF</th>\n",
       "      <th>Borrower</th>\n",
       "      <th>Collateral</th>\n",
       "      <th>...</th>\n",
       "      <th>Balance_FXS</th>\n",
       "      <th>Balance_LQTY</th>\n",
       "      <th>Balance_stkcvxFRAX3CRV-f</th>\n",
       "      <th>Balance_stkcvxLUSD3CRV-f</th>\n",
       "      <th>Balance_stkcvxgusd3CRV</th>\n",
       "      <th>Balance_stkcvxcrvPlain3andSUSD</th>\n",
       "      <th>Balance_stkcvx3Crv</th>\n",
       "      <th>Balance_stkcvxcrvFRAX</th>\n",
       "      <th>Balance_stkcvxsteCRV</th>\n",
       "      <th>Balance_wstETH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>0</td>\n",
       "      <td>13858003</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13862947</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>0</td>\n",
       "      <td>13862953</td>\n",
       "      <td>18.0</td>\n",
       "      <td>DAI</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13862987</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>0</td>\n",
       "      <td>13864162</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</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",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4996</th>\n",
       "      <td>4996</td>\n",
       "      <td>0x7E0177E914e3Cf5B69067874d4316BF552037eB2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819220</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4997</th>\n",
       "      <td>4997</td>\n",
       "      <td>0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819221</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4998</th>\n",
       "      <td>4998</td>\n",
       "      <td>0xD1168F931863CCcEf0102041f275cBA5C49907a2</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4999</th>\n",
       "      <td>4999</td>\n",
       "      <td>0xee6B3a0B5a750902c4513508bef90963e88F1c80</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819222</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5000</th>\n",
       "      <td>5000</td>\n",
       "      <td>0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3</td>\n",
       "      <td>0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04</td>\n",
       "      <td>0</td>\n",
       "      <td>13819223</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>None</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5001 rows × 81 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                          CA  \\\n",
       "0        0  0xb5DE854F7Db3164c8F9eFeFFED4c06317BCdbBF1   \n",
       "1        1  0xA3F1E5d5fb80B3bB0F1b04819F0930C4E0f32AF1   \n",
       "2        2  0xe541B88d68602E5f3Fb511633bAc708BACD8EA4c   \n",
       "3        3  0xC581Ff1a21f42B315DEDbE219a7Ed2B0fA47aBd5   \n",
       "4        4  0x5Ce9C22aC551d3b72136B3fe446902B1af0f3654   \n",
       "...    ...                                         ...   \n",
       "4996  4996  0x7E0177E914e3Cf5B69067874d4316BF552037eB2   \n",
       "4997  4997  0xD6Cdc10FB4eCf7201e91488Ae64C4AD55e6c2515   \n",
       "4998  4998  0xD1168F931863CCcEf0102041f275cBA5C49907a2   \n",
       "4999  4999  0xee6B3a0B5a750902c4513508bef90963e88F1c80   \n",
       "5000  5000  0x24b511DFA58D5c21DEd63ee8fe2fF0C37115e8F3   \n",
       "\n",
       "                                              CM borrowedAmount     Since  \\\n",
       "0     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB              0  13858003   \n",
       "1     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13862947   \n",
       "2     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB              0  13862953   \n",
       "3     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13862987   \n",
       "4     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0              0  13864162   \n",
       "...                                          ...            ...       ...   \n",
       "4996  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819220   \n",
       "4997  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819221   \n",
       "4998  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819222   \n",
       "4999  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819222   \n",
       "5000  0x444CD42BaEdDEB707eeD823f7177b9ABcC779C04              0  13819223   \n",
       "\n",
       "      Decimals Symbol    CF Borrower  Collateral  ... Balance_FXS  \\\n",
       "0         18.0    DAI  None     None         0.0  ...         NaN   \n",
       "1         18.0   WETH  None     None         0.0  ...         NaN   \n",
       "2         18.0    DAI  None     None         0.0  ...         NaN   \n",
       "3         18.0   WETH  None     None         0.0  ...         NaN   \n",
       "4         18.0   WETH  None     None         0.0  ...         NaN   \n",
       "...        ...    ...   ...      ...         ...  ...         ...   \n",
       "4996       NaN   None  None     None         NaN  ...         NaN   \n",
       "4997       NaN   None  None     None         NaN  ...         NaN   \n",
       "4998       NaN   None  None     None         NaN  ...         NaN   \n",
       "4999       NaN   None  None     None         NaN  ...         NaN   \n",
       "5000       NaN   None  None     None         NaN  ...         NaN   \n",
       "\n",
       "     Balance_LQTY Balance_stkcvxFRAX3CRV-f Balance_stkcvxLUSD3CRV-f  \\\n",
       "0             NaN                      NaN                      NaN   \n",
       "1             NaN                      NaN                      NaN   \n",
       "2             NaN                      NaN                      NaN   \n",
       "3             NaN                      NaN                      NaN   \n",
       "4             NaN                      NaN                      NaN   \n",
       "...           ...                      ...                      ...   \n",
       "4996          NaN                      NaN                      NaN   \n",
       "4997          NaN                      NaN                      NaN   \n",
       "4998          NaN                      NaN                      NaN   \n",
       "4999          NaN                      NaN                      NaN   \n",
       "5000          NaN                      NaN                      NaN   \n",
       "\n",
       "     Balance_stkcvxgusd3CRV Balance_stkcvxcrvPlain3andSUSD  \\\n",
       "0                       NaN                            NaN   \n",
       "1                       NaN                            NaN   \n",
       "2                       NaN                            NaN   \n",
       "3                       NaN                            NaN   \n",
       "4                       NaN                            NaN   \n",
       "...                     ...                            ...   \n",
       "4996                    NaN                            NaN   \n",
       "4997                    NaN                            NaN   \n",
       "4998                    NaN                            NaN   \n",
       "4999                    NaN                            NaN   \n",
       "5000                    NaN                            NaN   \n",
       "\n",
       "      Balance_stkcvx3Crv Balance_stkcvxcrvFRAX Balance_stkcvxsteCRV  \\\n",
       "0                    NaN                   NaN                  NaN   \n",
       "1                    NaN                   NaN                  NaN   \n",
       "2                    NaN                   NaN                  NaN   \n",
       "3                    NaN                   NaN                  NaN   \n",
       "4                    NaN                   NaN                  NaN   \n",
       "...                  ...                   ...                  ...   \n",
       "4996                 NaN                   NaN                  NaN   \n",
       "4997                 NaN                   NaN                  NaN   \n",
       "4998                 NaN                   NaN                  NaN   \n",
       "4999                 NaN                   NaN                  NaN   \n",
       "5000                 NaN                   NaN                  NaN   \n",
       "\n",
       "     Balance_wstETH  \n",
       "0               NaN  \n",
       "1               NaN  \n",
       "2               NaN  \n",
       "3               NaN  \n",
       "4               NaN  \n",
       "...             ...  \n",
       "4996            NaN  \n",
       "4997            NaN  \n",
       "4998            NaN  \n",
       "4999            NaN  \n",
       "5000            NaN  \n",
       "\n",
       "[5001 rows x 81 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "6d1b60fc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['id', 'CA', 'CM', 'borrowedAmount', 'Since', 'Decimals', 'Symbol', 'CF',\n",
       "       'Borrower', 'Collateral', 'batchtime', 'inUse', 'underlying',\n",
       "       'borrowedAmountPlusInterest', 'borrowedAmountPlusInterestAndFees',\n",
       "       'totalValue', 'healthFactor', 'borrowRate', 'repayAmount',\n",
       "       'liquidationAmount', 'canBeClosed', 'cumulativeIndexAtOpen', 'version',\n",
       "       'enabledTokenMask', 'Balance_DAI', 'Balance_1INCH', 'Balance_AAVE',\n",
       "       'Balance_COMP', 'Balance_DPI', 'Balance_FEI', 'Balance_LINK',\n",
       "       'Balance_SNX', 'Balance_UNI', 'Balance_USDC', 'Balance_WBTC',\n",
       "       'Balance_WETH', 'Balance_YFI', 'Balance_yvDAI', 'Balance_yvUSDC',\n",
       "       'Balance_CRV', 'Balance_SUSHI', 'Balance_LDO', 'Balance_FTM',\n",
       "       'Balance_LUNA', 'Balance_G-OBS', 'Balance_G-BLOCK', 'Balance_stETH',\n",
       "       'Balance_USDT', 'Balance_sUSD', 'Balance_FRAX', 'Balance_GUSD',\n",
       "       'Balance_LUSD', 'Balance_steCRV', 'Balance_cvxsteCRV', 'Balance_3Crv',\n",
       "       'Balance_cvx3Crv', 'Balance_FRAX3CRV-f', 'Balance_cvxFRAX3CRV-f',\n",
       "       'Balance_LUSD3CRV-f', 'Balance_cvxLUSD3CRV-f',\n",
       "       'Balance_crvPlain3andSUSD', 'Balance_cvxcrvPlain3andSUSD',\n",
       "       'Balance_gusd3CRV', 'Balance_cvxgusd3CRV', 'Balance_crvFRAX',\n",
       "       'Balance_cvxcrvFRAX', 'Balance_yvWETH', 'Balance_yvWBTC',\n",
       "       'Balance_yvCurve-stETH', 'Balance_yvCurve-FRAX', 'Balance_CVX',\n",
       "       'Balance_FXS', 'Balance_LQTY', 'Balance_stkcvxFRAX3CRV-f',\n",
       "       'Balance_stkcvxLUSD3CRV-f', 'Balance_stkcvxgusd3CRV',\n",
       "       'Balance_stkcvxcrvPlain3andSUSD', 'Balance_stkcvx3Crv',\n",
       "       'Balance_stkcvxcrvFRAX', 'Balance_stkcvxsteCRV', 'Balance_wstETH'],\n",
       "      dtype='object')"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9f437c74",
   "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>CA</th>\n",
       "      <th>CM</th>\n",
       "      <th>borrowedAmount</th>\n",
       "      <th>Since</th>\n",
       "      <th>Decimals</th>\n",
       "      <th>Symbol</th>\n",
       "      <th>CF</th>\n",
       "      <th>Borrower</th>\n",
       "      <th>Collateral</th>\n",
       "      <th>...</th>\n",
       "      <th>Balance_FXS</th>\n",
       "      <th>Balance_LQTY</th>\n",
       "      <th>Balance_stkcvxFRAX3CRV-f</th>\n",
       "      <th>Balance_stkcvxLUSD3CRV-f</th>\n",
       "      <th>Balance_stkcvxgusd3CRV</th>\n",
       "      <th>Balance_stkcvxcrvPlain3andSUSD</th>\n",
       "      <th>Balance_stkcvx3Crv</th>\n",
       "      <th>Balance_stkcvxcrvFRAX</th>\n",
       "      <th>Balance_stkcvxsteCRV</th>\n",
       "      <th>Balance_wstETH</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>0xF91027C04807c71c7771a2314A622dFA0fE509A1</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>1000000000000000000</td>\n",
       "      <td>13864789</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x896b94f4f27f12369698C302e2049cAe86936BbB</td>\n",
       "      <td>1.000000e+18</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>0x0B9851B7bcE408dd36d3473a0A0eE8F14ce64ca3</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>290000000000000000</td>\n",
       "      <td>13864924</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0xC16414AC1fedfDAC4F8A09674D994e1BbB9d7113</td>\n",
       "      <td>1.000000e+18</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>31</td>\n",
       "      <td>0xdAC87546b0ea6A448c7F3b69e079b0a616F4CC10</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>5000000000000000000</td>\n",
       "      <td>13868591</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x99BC02c239025E431D5741cC1DbA8CE77fc51CE3</td>\n",
       "      <td>2.500000e+18</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>33</td>\n",
       "      <td>0xB8c3FFFE2E13EFd96594EC6Ea9bBE0b2ABA6210e</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>1000000000000000000</td>\n",
       "      <td>13869000</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x2bb8Ab3C2A9837de97A83c228A07E16928B4f07f</td>\n",
       "      <td>5.000000e+17</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>87</td>\n",
       "      <td>0xf74e4AEAA3B75c0662bF6024dcA385bF5fDBa8c0</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>900000000000000000</td>\n",
       "      <td>13884707</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>None</td>\n",
       "      <td>0x8CBC2D4c114D6f4411bBC991cE68Db994e45Eb4F</td>\n",
       "      <td>3.000000e+17</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</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",
       "      <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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1363</th>\n",
       "      <td>1363</td>\n",
       "      <td>0x8137351530f9CD81C87EAe9457A6CAF9f910f000</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>468920089983699958680</td>\n",
       "      <td>17321436</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62</td>\n",
       "      <td>0x316BE293C8f2380769e7b7e7382679FE5a3b6600</td>\n",
       "      <td>9.999015e+19</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1365</th>\n",
       "      <td>1365</td>\n",
       "      <td>0x2C0C914DC4ed0d850dDdb978ee04F4a3Ff445dc0</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>300000000000</td>\n",
       "      <td>17482075</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x055B29979f6BC27669Ebd54182588FeF12ffBFc0</td>\n",
       "      <td>1.000000e+11</td>\n",
       "      <td>...</td>\n",
       "      <td>2308468535450713983290</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1367</th>\n",
       "      <td>1367</td>\n",
       "      <td>0x813Ac69E22c3D1c2A7CE95A7A641DB580b9363A0</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>300000000000</td>\n",
       "      <td>17491767</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x9874f9B05B073771c7b98e192543FA2684e61246</td>\n",
       "      <td>6.000000e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>350699387406909024167277</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1370</th>\n",
       "      <td>1370</td>\n",
       "      <td>0xF9eBC97A52E94DA558d4aEE46403D7ef7E465a46</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>120000000000000000000</td>\n",
       "      <td>17600969</td>\n",
       "      <td>18.0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62</td>\n",
       "      <td>0x6e58309CD851A5B124E3A56768a42d12f3B6D104</td>\n",
       "      <td>2.000000e+19</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1371</th>\n",
       "      <td>1371</td>\n",
       "      <td>0x1ad45BFBDe1045FDC53da1a7ef3d407eE60402ca</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>125000000180</td>\n",
       "      <td>17647010</td>\n",
       "      <td>6.0</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x166E58eA56399d5809FFfe195196554E9934bfF7</td>\n",
       "      <td>4.368875e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>144608503220944392906651</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>81 rows × 81 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                          CA  \\\n",
       "8        8  0xF91027C04807c71c7771a2314A622dFA0fE509A1   \n",
       "11      11  0x0B9851B7bcE408dd36d3473a0A0eE8F14ce64ca3   \n",
       "31      31  0xdAC87546b0ea6A448c7F3b69e079b0a616F4CC10   \n",
       "33      33  0xB8c3FFFE2E13EFd96594EC6Ea9bBE0b2ABA6210e   \n",
       "87      87  0xf74e4AEAA3B75c0662bF6024dcA385bF5fDBa8c0   \n",
       "...    ...                                         ...   \n",
       "1363  1363  0x8137351530f9CD81C87EAe9457A6CAF9f910f000   \n",
       "1365  1365  0x2C0C914DC4ed0d850dDdb978ee04F4a3Ff445dc0   \n",
       "1367  1367  0x813Ac69E22c3D1c2A7CE95A7A641DB580b9363A0   \n",
       "1370  1370  0xF9eBC97A52E94DA558d4aEE46403D7ef7E465a46   \n",
       "1371  1371  0x1ad45BFBDe1045FDC53da1a7ef3d407eE60402ca   \n",
       "\n",
       "                                              CM         borrowedAmount  \\\n",
       "8     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    1000000000000000000   \n",
       "11    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     290000000000000000   \n",
       "31    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    5000000000000000000   \n",
       "33    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0    1000000000000000000   \n",
       "87    0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     900000000000000000   \n",
       "...                                          ...                    ...   \n",
       "1363  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B  468920089983699958680   \n",
       "1365  0x95357303f995e184A7998dA6C6eA35cC728A1900           300000000000   \n",
       "1367  0x95357303f995e184A7998dA6C6eA35cC728A1900           300000000000   \n",
       "1370  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B  120000000000000000000   \n",
       "1371  0x95357303f995e184A7998dA6C6eA35cC728A1900           125000000180   \n",
       "\n",
       "         Since  Decimals Symbol                                          CF  \\\n",
       "8     13864789      18.0   WETH                                        None   \n",
       "11    13864924      18.0   WETH                                        None   \n",
       "31    13868591      18.0   WETH                                        None   \n",
       "33    13869000      18.0   WETH                                        None   \n",
       "87    13884707      18.0   WETH                                        None   \n",
       "...        ...       ...    ...                                         ...   \n",
       "1363  17321436      18.0   WETH  0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62   \n",
       "1365  17482075       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "1367  17491767       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "1370  17600969      18.0   WETH  0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62   \n",
       "1371  17647010       6.0   USDC  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "\n",
       "                                        Borrower    Collateral  ...  \\\n",
       "8     0x896b94f4f27f12369698C302e2049cAe86936BbB  1.000000e+18  ...   \n",
       "11    0xC16414AC1fedfDAC4F8A09674D994e1BbB9d7113  1.000000e+18  ...   \n",
       "31    0x99BC02c239025E431D5741cC1DbA8CE77fc51CE3  2.500000e+18  ...   \n",
       "33    0x2bb8Ab3C2A9837de97A83c228A07E16928B4f07f  5.000000e+17  ...   \n",
       "87    0x8CBC2D4c114D6f4411bBC991cE68Db994e45Eb4F  3.000000e+17  ...   \n",
       "...                                          ...           ...  ...   \n",
       "1363  0x316BE293C8f2380769e7b7e7382679FE5a3b6600  9.999015e+19  ...   \n",
       "1365  0x055B29979f6BC27669Ebd54182588FeF12ffBFc0  1.000000e+11  ...   \n",
       "1367  0x9874f9B05B073771c7b98e192543FA2684e61246  6.000000e+10  ...   \n",
       "1370  0x6e58309CD851A5B124E3A56768a42d12f3B6D104  2.000000e+19  ...   \n",
       "1371  0x166E58eA56399d5809FFfe195196554E9934bfF7  4.368875e+10  ...   \n",
       "\n",
       "                 Balance_FXS Balance_LQTY Balance_stkcvxFRAX3CRV-f  \\\n",
       "8                          0          0.0                        0   \n",
       "11                         0          0.0                        0   \n",
       "31                         0          0.0                        0   \n",
       "33                         0          0.0                        0   \n",
       "87                         0          0.0                        0   \n",
       "...                      ...          ...                      ...   \n",
       "1363                       0          0.0                        0   \n",
       "1365  2308468535450713983290          0.0                        0   \n",
       "1367                       0          0.0                        0   \n",
       "1370                       0          0.0                        0   \n",
       "1371                       0          0.0                        0   \n",
       "\n",
       "      Balance_stkcvxLUSD3CRV-f    Balance_stkcvxgusd3CRV  \\\n",
       "8                            0                         0   \n",
       "11                           0                         0   \n",
       "31                           0                         0   \n",
       "33                           0                         0   \n",
       "87                           0                         0   \n",
       "...                        ...                       ...   \n",
       "1363                         0                         0   \n",
       "1365                         0                         0   \n",
       "1367  350699387406909024167277                         0   \n",
       "1370                         0                         0   \n",
       "1371                         0  144608503220944392906651   \n",
       "\n",
       "     Balance_stkcvxcrvPlain3andSUSD  Balance_stkcvx3Crv Balance_stkcvxcrvFRAX  \\\n",
       "8                                 0                 0.0                   0.0   \n",
       "11                                0                 0.0                   0.0   \n",
       "31                                0                 0.0                   0.0   \n",
       "33                                0                 0.0                   0.0   \n",
       "87                                0                 0.0                   0.0   \n",
       "...                             ...                 ...                   ...   \n",
       "1363                              0                 0.0                   0.0   \n",
       "1365                              0                 0.0                   0.0   \n",
       "1367                              0                 0.0                   0.0   \n",
       "1370                              0                 0.0                   0.0   \n",
       "1371                              0                 0.0                   0.0   \n",
       "\n",
       "     Balance_stkcvxsteCRV Balance_wstETH  \n",
       "8                       0            0.0  \n",
       "11                      0            0.0  \n",
       "31                      0            0.0  \n",
       "33                      0            0.0  \n",
       "87                      0            0.0  \n",
       "...                   ...            ...  \n",
       "1363                    0            0.0  \n",
       "1365                    0            0.0  \n",
       "1367                    0            0.0  \n",
       "1370                    0            0.0  \n",
       "1371                    0            0.0  \n",
       "\n",
       "[81 rows x 81 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df[pd.notna(df['Borrower'])]) # active CAs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "25f929fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 21:56:38 <module> INFO batchtime 2023-07-09 09:56:26.053598\n",
      "2023-07-09 21:56:38 <module> INFO countCreditAccounts - countCreditAccountsInStock = 81\n"
     ]
    }
   ],
   "source": [
    "logging.info(f'batchtime {batchtime}' )\n",
    "logging.info(f'countCreditAccounts - countCreditAccountsInStock = {countCreditAccounts - countCreditAccountsInStock}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "179a0596",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_price_oracle = pd.DataFrame.from_dict([allowedTokens[x] for x in allowedTokens])\n",
    "df_price_oracle = df_price_oracle.set_index('symbol', drop=True).transpose()\n",
    "df_price_oracle.columns = ['Price_'+x for x in df_price_oracle.columns]\n",
    "df_price_oracle[[x.replace('Price','Decimals') for x in df_price_oracle.columns]] = df_price_oracle.loc['decimals',:]\n",
    "df_price_oracle = df_price_oracle.drop(index = 'decimals')\n",
    "\n",
    "df_price_oracle = df_price_oracle.reset_index()\n",
    "df_price_oracle = df_price_oracle.rename(columns={'index': 'Price_Asset'})\n",
    "df_price_oracle['Price_Asset'] = df_price_oracle['Price_Asset'].apply(lambda x: x.replace('Price_',''))\n",
    "\n",
    "df_price_oracle['batchtime'] = batchtime\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "e9d18d35",
   "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>Price_Asset</th>\n",
       "      <th>Price_DAI</th>\n",
       "      <th>Price_1INCH</th>\n",
       "      <th>Price_AAVE</th>\n",
       "      <th>Price_COMP</th>\n",
       "      <th>Price_DPI</th>\n",
       "      <th>Price_FEI</th>\n",
       "      <th>Price_LINK</th>\n",
       "      <th>Price_SNX</th>\n",
       "      <th>Price_UNI</th>\n",
       "      <th>...</th>\n",
       "      <th>Decimals_LQTY</th>\n",
       "      <th>Decimals_stkcvxFRAX3CRV-f</th>\n",
       "      <th>Decimals_stkcvxLUSD3CRV-f</th>\n",
       "      <th>Decimals_stkcvxgusd3CRV</th>\n",
       "      <th>Decimals_stkcvxcrvPlain3andSUSD</th>\n",
       "      <th>Decimals_stkcvx3Crv</th>\n",
       "      <th>Decimals_stkcvxcrvFRAX</th>\n",
       "      <th>Decimals_stkcvxsteCRV</th>\n",
       "      <th>Decimals_wstETH</th>\n",
       "      <th>batchtime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>USD</td>\n",
       "      <td>0.999904</td>\n",
       "      <td>0.306882</td>\n",
       "      <td>72.68227</td>\n",
       "      <td>52.31638</td>\n",
       "      <td>70.79502</td>\n",
       "      <td>0.953969</td>\n",
       "      <td>6.176639</td>\n",
       "      <td>2.076</td>\n",
       "      <td>5.248458</td>\n",
       "      <td>...</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 116 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  Price_Asset  Price_DAI  Price_1INCH  Price_AAVE  Price_COMP  Price_DPI  \\\n",
       "0         USD   0.999904     0.306882    72.68227    52.31638   70.79502   \n",
       "\n",
       "   Price_FEI  Price_LINK  Price_SNX  Price_UNI  ...  Decimals_LQTY  \\\n",
       "0   0.953969    6.176639      2.076   5.248458  ...           18.0   \n",
       "\n",
       "   Decimals_stkcvxFRAX3CRV-f  Decimals_stkcvxLUSD3CRV-f  \\\n",
       "0                       18.0                       18.0   \n",
       "\n",
       "   Decimals_stkcvxgusd3CRV  Decimals_stkcvxcrvPlain3andSUSD  \\\n",
       "0                     18.0                             18.0   \n",
       "\n",
       "   Decimals_stkcvx3Crv  Decimals_stkcvxcrvFRAX  Decimals_stkcvxsteCRV  \\\n",
       "0                 18.0                    18.0                   18.0   \n",
       "\n",
       "   Decimals_wstETH                  batchtime  \n",
       "0             18.0 2023-07-09 09:56:26.053598  \n",
       "\n",
       "[1 rows x 116 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_price_oracle)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "190078b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_token_price = pd.DataFrame.from_dict([allowedTokens[x] for x in allowedTokens])\n",
    "df_token_price.columns = [x.replace('Price_','') for x in df_token_price.columns]\n",
    "df_token_price['batchtime'] = batchtime\n",
    "df_token_price = df_token_price.rename(columns = {'symbol':'token'})\n",
    "numeric_cols = [x for x in df_token_price.columns if x not in ['token', 'batchtime','decimals']]\n",
    "df_token_price[numeric_cols] = df_token_price[numeric_cols]\n",
    "df_token_price[numeric_cols] = df_token_price[numeric_cols].astype('float64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "cf0606ff",
   "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>token</th>\n",
       "      <th>decimals</th>\n",
       "      <th>USD</th>\n",
       "      <th>batchtime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>DAI</td>\n",
       "      <td>18</td>\n",
       "      <td>0.999904</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1INCH</td>\n",
       "      <td>18</td>\n",
       "      <td>0.306882</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AAVE</td>\n",
       "      <td>18</td>\n",
       "      <td>72.682270</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>COMP</td>\n",
       "      <td>18</td>\n",
       "      <td>52.316380</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>DPI</td>\n",
       "      <td>18</td>\n",
       "      <td>70.795020</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>FEI</td>\n",
       "      <td>18</td>\n",
       "      <td>0.953969</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LINK</td>\n",
       "      <td>18</td>\n",
       "      <td>6.176639</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SNX</td>\n",
       "      <td>18</td>\n",
       "      <td>2.076000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UNI</td>\n",
       "      <td>18</td>\n",
       "      <td>5.248458</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>USDC</td>\n",
       "      <td>6</td>\n",
       "      <td>1.000035</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>WBTC</td>\n",
       "      <td>8</td>\n",
       "      <td>30301.485053</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>WETH</td>\n",
       "      <td>18</td>\n",
       "      <td>1868.940000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>YFI</td>\n",
       "      <td>18</td>\n",
       "      <td>7057.217208</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>yvDAI</td>\n",
       "      <td>18</td>\n",
       "      <td>1.059427</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>yvUSDC</td>\n",
       "      <td>6</td>\n",
       "      <td>1.038024</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CRV</td>\n",
       "      <td>18</td>\n",
       "      <td>0.789982</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>SUSHI</td>\n",
       "      <td>18</td>\n",
       "      <td>0.632583</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>LDO</td>\n",
       "      <td>18</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>FTM</td>\n",
       "      <td>18</td>\n",
       "      <td>0.272970</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>LUNA</td>\n",
       "      <td>18</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>G-OBS</td>\n",
       "      <td>6</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>G-BLOCK</td>\n",
       "      <td>18</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>stETH</td>\n",
       "      <td>18</td>\n",
       "      <td>1868.260344</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>USDT</td>\n",
       "      <td>6</td>\n",
       "      <td>1.000378</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>sUSD</td>\n",
       "      <td>18</td>\n",
       "      <td>0.998824</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>FRAX</td>\n",
       "      <td>18</td>\n",
       "      <td>0.999497</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>GUSD</td>\n",
       "      <td>2</td>\n",
       "      <td>1.001676</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>LUSD</td>\n",
       "      <td>18</td>\n",
       "      <td>1.000599</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>steCRV</td>\n",
       "      <td>18</td>\n",
       "      <td>2010.260133</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>cvxsteCRV</td>\n",
       "      <td>18</td>\n",
       "      <td>2010.260133</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>3Crv</td>\n",
       "      <td>18</td>\n",
       "      <td>1.026245</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>cvx3Crv</td>\n",
       "      <td>18</td>\n",
       "      <td>1.026245</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>FRAX3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.010067</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>cvxFRAX3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.010067</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>LUSD3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.024503</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>cvxLUSD3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.024503</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>crvPlain3andSUSD</td>\n",
       "      <td>18</td>\n",
       "      <td>1.063205</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>cvxcrvPlain3andSUSD</td>\n",
       "      <td>18</td>\n",
       "      <td>1.063205</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>gusd3CRV</td>\n",
       "      <td>18</td>\n",
       "      <td>1.036911</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>cvxgusd3CRV</td>\n",
       "      <td>18</td>\n",
       "      <td>1.036911</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>crvFRAX</td>\n",
       "      <td>18</td>\n",
       "      <td>1.000754</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>cvxcrvFRAX</td>\n",
       "      <td>18</td>\n",
       "      <td>1.000754</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>yvWETH</td>\n",
       "      <td>18</td>\n",
       "      <td>1945.596528</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>yvWBTC</td>\n",
       "      <td>8</td>\n",
       "      <td>30963.504020</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>yvCurve-stETH</td>\n",
       "      <td>18</td>\n",
       "      <td>2208.121976</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>yvCurve-FRAX</td>\n",
       "      <td>18</td>\n",
       "      <td>1.137072</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>CVX</td>\n",
       "      <td>18</td>\n",
       "      <td>4.037586</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>FXS</td>\n",
       "      <td>18</td>\n",
       "      <td>6.337796</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>LQTY</td>\n",
       "      <td>18</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>stkcvxFRAX3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.010067</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>stkcvxLUSD3CRV-f</td>\n",
       "      <td>18</td>\n",
       "      <td>1.024503</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>stkcvxgusd3CRV</td>\n",
       "      <td>18</td>\n",
       "      <td>1.036911</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>stkcvxcrvPlain3andSUSD</td>\n",
       "      <td>18</td>\n",
       "      <td>1.063205</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>stkcvx3Crv</td>\n",
       "      <td>18</td>\n",
       "      <td>1.026245</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>stkcvxcrvFRAX</td>\n",
       "      <td>18</td>\n",
       "      <td>1.000754</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>stkcvxsteCRV</td>\n",
       "      <td>18</td>\n",
       "      <td>2010.260133</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>wstETH</td>\n",
       "      <td>18</td>\n",
       "      <td>2089.334150</td>\n",
       "      <td>2023-07-09 09:56:26.053598</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     token  decimals           USD                  batchtime\n",
       "0                      DAI        18      0.999904 2023-07-09 09:56:26.053598\n",
       "1                    1INCH        18      0.306882 2023-07-09 09:56:26.053598\n",
       "2                     AAVE        18     72.682270 2023-07-09 09:56:26.053598\n",
       "3                     COMP        18     52.316380 2023-07-09 09:56:26.053598\n",
       "4                      DPI        18     70.795020 2023-07-09 09:56:26.053598\n",
       "5                      FEI        18      0.953969 2023-07-09 09:56:26.053598\n",
       "6                     LINK        18      6.176639 2023-07-09 09:56:26.053598\n",
       "7                      SNX        18      2.076000 2023-07-09 09:56:26.053598\n",
       "8                      UNI        18      5.248458 2023-07-09 09:56:26.053598\n",
       "9                     USDC         6      1.000035 2023-07-09 09:56:26.053598\n",
       "10                    WBTC         8  30301.485053 2023-07-09 09:56:26.053598\n",
       "11                    WETH        18   1868.940000 2023-07-09 09:56:26.053598\n",
       "12                     YFI        18   7057.217208 2023-07-09 09:56:26.053598\n",
       "13                   yvDAI        18      1.059427 2023-07-09 09:56:26.053598\n",
       "14                  yvUSDC         6      1.038024 2023-07-09 09:56:26.053598\n",
       "15                     CRV        18      0.789982 2023-07-09 09:56:26.053598\n",
       "16                   SUSHI        18      0.632583 2023-07-09 09:56:26.053598\n",
       "17                     LDO        18      0.000000 2023-07-09 09:56:26.053598\n",
       "18                     FTM        18      0.272970 2023-07-09 09:56:26.053598\n",
       "19                    LUNA        18      0.000000 2023-07-09 09:56:26.053598\n",
       "20                   G-OBS         6      0.000000 2023-07-09 09:56:26.053598\n",
       "21                 G-BLOCK        18      0.000000 2023-07-09 09:56:26.053598\n",
       "22                   stETH        18   1868.260344 2023-07-09 09:56:26.053598\n",
       "23                    USDT         6      1.000378 2023-07-09 09:56:26.053598\n",
       "24                    sUSD        18      0.998824 2023-07-09 09:56:26.053598\n",
       "25                    FRAX        18      0.999497 2023-07-09 09:56:26.053598\n",
       "26                    GUSD         2      1.001676 2023-07-09 09:56:26.053598\n",
       "27                    LUSD        18      1.000599 2023-07-09 09:56:26.053598\n",
       "28                  steCRV        18   2010.260133 2023-07-09 09:56:26.053598\n",
       "29               cvxsteCRV        18   2010.260133 2023-07-09 09:56:26.053598\n",
       "30                    3Crv        18      1.026245 2023-07-09 09:56:26.053598\n",
       "31                 cvx3Crv        18      1.026245 2023-07-09 09:56:26.053598\n",
       "32              FRAX3CRV-f        18      1.010067 2023-07-09 09:56:26.053598\n",
       "33           cvxFRAX3CRV-f        18      1.010067 2023-07-09 09:56:26.053598\n",
       "34              LUSD3CRV-f        18      1.024503 2023-07-09 09:56:26.053598\n",
       "35           cvxLUSD3CRV-f        18      1.024503 2023-07-09 09:56:26.053598\n",
       "36        crvPlain3andSUSD        18      1.063205 2023-07-09 09:56:26.053598\n",
       "37     cvxcrvPlain3andSUSD        18      1.063205 2023-07-09 09:56:26.053598\n",
       "38                gusd3CRV        18      1.036911 2023-07-09 09:56:26.053598\n",
       "39             cvxgusd3CRV        18      1.036911 2023-07-09 09:56:26.053598\n",
       "40                 crvFRAX        18      1.000754 2023-07-09 09:56:26.053598\n",
       "41              cvxcrvFRAX        18      1.000754 2023-07-09 09:56:26.053598\n",
       "42                  yvWETH        18   1945.596528 2023-07-09 09:56:26.053598\n",
       "43                  yvWBTC         8  30963.504020 2023-07-09 09:56:26.053598\n",
       "44           yvCurve-stETH        18   2208.121976 2023-07-09 09:56:26.053598\n",
       "45            yvCurve-FRAX        18      1.137072 2023-07-09 09:56:26.053598\n",
       "46                     CVX        18      4.037586 2023-07-09 09:56:26.053598\n",
       "47                     FXS        18      6.337796 2023-07-09 09:56:26.053598\n",
       "48                    LQTY        18      0.000000 2023-07-09 09:56:26.053598\n",
       "49        stkcvxFRAX3CRV-f        18      1.010067 2023-07-09 09:56:26.053598\n",
       "50        stkcvxLUSD3CRV-f        18      1.024503 2023-07-09 09:56:26.053598\n",
       "51          stkcvxgusd3CRV        18      1.036911 2023-07-09 09:56:26.053598\n",
       "52  stkcvxcrvPlain3andSUSD        18      1.063205 2023-07-09 09:56:26.053598\n",
       "53              stkcvx3Crv        18      1.026245 2023-07-09 09:56:26.053598\n",
       "54           stkcvxcrvFRAX        18      1.000754 2023-07-09 09:56:26.053598\n",
       "55            stkcvxsteCRV        18   2010.260133 2023-07-09 09:56:26.053598\n",
       "56                  wstETH        18   2089.334150 2023-07-09 09:56:26.053598"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_token_price)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "838a1e96",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "2023-07-09 22:30:03 <module> INFO from_block=0\n",
      "2023-07-09 22:30:03 <module> INFO get_logs from CM 0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB\n",
      "2023-07-09 22:30:04 <module> INFO get_logs from CM 0x2664cc24CBAd28749B3Dd6fC97A6B402484De527\n",
      "2023-07-09 22:30:05 <module> INFO get_logs from CM 0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0\n",
      "2023-07-09 22:30:07 <module> INFO get_logs from CM 0xC38478B0A4bAFE964C3526EEFF534d70E1E09017\n",
      "2023-07-09 22:30:07 <module> INFO get_logs from CM 0x672461Bfc20DD783444a830Ad4c38b345aB6E2f7\n",
      "2023-07-09 22:30:07 <module> INFO get_logs from CF 0xf6f4F24ae50206A07B8B32629AeB6cb1837d854F\n",
      "2023-07-09 22:30:08 <module> INFO get_logs from CF 0xd4fe3eD38250C38A0094224C4B0224b5D5d0e7d9\n",
      "2023-07-09 22:30:08 <module> INFO get_logs from CM 0x95357303f995e184A7998dA6C6eA35cC728A1900\n",
      "2023-07-09 22:30:08 <module> INFO get_logs from CF 0x61fbb350e39cc7bF22C01A469cf03085774184aa\n",
      "2023-07-09 22:30:09 <module> INFO get_logs from CF 0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245\n",
      "2023-07-09 22:30:09 <module> INFO get_logs from CM 0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B\n",
      "2023-07-09 22:30:09 <module> INFO get_logs from CF 0xC59135f449bb623501145443c70A30eE648Fa304\n",
      "2023-07-09 22:30:10 <module> INFO get_logs from CF 0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62\n",
      "2023-07-09 22:30:11 <module> INFO get_logs from CM 0xe0bCE4460795281d39c91da9B0275BcA968293de\n",
      "2023-07-09 22:30:11 <module> INFO get_logs from CF 0x04d692bCB03D4b410CBB5Bf09967eB3ce8D12546\n",
      "2023-07-09 22:30:11 <module> INFO get_logs from CF 0x4Ae3EDbDf1C42e3560Cc2D52B8F353F026F67b44\n",
      "2023-07-09 22:30:11 <module> INFO get_logs from CM 0xc62BF8a7889AdF1c5Dc4665486c7683ae6E74e0F\n",
      "2023-07-09 22:30:11 <module> INFO get_logs from CF 0xAfae62D1b38d635a3089A36B27f3c1Acc4fa3243\n",
      "2023-07-09 22:30:12 <module> INFO get_logs from CF 0x700De428aa940000259B1c58F3E44445d360303c\n",
      "2023-07-09 22:30:12 <module> INFO get_logs from CM 0xA3E1e0d58FE8dD8C9dd48204699a1178f1B274D8\n",
      "2023-07-09 22:30:12 <module> INFO get_logs from CF 0xbA5B2C3b259F0ceA02CFC6Da86f0fc4F4F2c1aE3\n",
      "2023-07-09 22:30:12 <module> INFO get_logs from CF 0xf4ecc4e950b563F113b17C5606B31a314B99BFe3\n",
      "2023-07-09 22:30:13 <module> INFO number of events=4110\n",
      "2023-07-09 22:30:13 <module> INFO cf_dict = {'0xf6f4F24ae50206A07B8B32629AeB6cb1837d854F': {'CreditManager': '0x672461Bfc20DD783444a830Ad4c38b345aB6E2f7'}, '0xd4fe3eD38250C38A0094224C4B0224b5D5d0e7d9': {'CreditManager': '0x672461Bfc20DD783444a830Ad4c38b345aB6E2f7'}, '0x61fbb350e39cc7bF22C01A469cf03085774184aa': {'CreditManager': '0x95357303f995e184A7998dA6C6eA35cC728A1900'}, '0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245': {'CreditManager': '0x95357303f995e184A7998dA6C6eA35cC728A1900'}, '0xC59135f449bb623501145443c70A30eE648Fa304': {'CreditManager': '0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B'}, '0x67479449b2cf25AEE2fB6EF6f0aEc54591154F62': {'CreditManager': '0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B'}, '0x04d692bCB03D4b410CBB5Bf09967eB3ce8D12546': {'CreditManager': '0xe0bCE4460795281d39c91da9B0275BcA968293de'}, '0x4Ae3EDbDf1C42e3560Cc2D52B8F353F026F67b44': {'CreditManager': '0xe0bCE4460795281d39c91da9B0275BcA968293de'}, '0xAfae62D1b38d635a3089A36B27f3c1Acc4fa3243': {'CreditManager': '0xc62BF8a7889AdF1c5Dc4665486c7683ae6E74e0F'}, '0x700De428aa940000259B1c58F3E44445d360303c': {'CreditManager': '0xc62BF8a7889AdF1c5Dc4665486c7683ae6E74e0F'}, '0xbA5B2C3b259F0ceA02CFC6Da86f0fc4F4F2c1aE3': {'CreditManager': '0xA3E1e0d58FE8dD8C9dd48204699a1178f1B274D8'}, '0xf4ecc4e950b563F113b17C5606B31a314B99BFe3': {'CreditManager': '0xA3E1e0d58FE8dD8C9dd48204699a1178f1B274D8'}}\n",
      "2023-07-09 22:30:13 <module> INFO pull event timestamp begin\n",
      "2023-07-09 22:45:41 <module> INFO pull event timestamp end\n",
      "2023-07-09 22:45:41 <module> INFO pull transaction fee begin\n",
      "2023-07-09 23:01:55 <module> INFO pull transaction fee end\n"
     ]
    }
   ],
   "source": [
    "#Open, Close, Repay, Liquidate\n",
    "OpenCreditAccount_topic             =  df_abi[(df_abi['name']=='OpenCreditAccount') &(df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "OpenCreditAccount_topic_v1          =  df_abi[(df_abi['name']=='OpenCreditAccount') &(df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "AddCollateral_topic                 =  df_abi[(df_abi['name']=='AddCollateral') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "AddCollateral_topic_v1              =  df_abi[(df_abi['name']=='AddCollateral') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "CloseCreditAccount_topic            =  df_abi[(df_abi['name']=='CloseCreditAccount') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "CloseCreditAccount_topic_v1         =  df_abi[(df_abi['name']=='CloseCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "RepayCreditAccount_topic_v1         =  df_abi[(df_abi['name']=='RepayCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateCreditAccount_topic        =  df_abi[(df_abi['name']=='LiquidateCreditAccount') & (df_abi['contract_type']=='creditFacade') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateCreditAccount_topic_v1     =  df_abi[(df_abi['name']=='LiquidateCreditAccount') & (df_abi['contract_type']=='CreditManager_v1') & (df_abi['type']=='event')]['topic'].values[0]\n",
    "LiquidateExpiredCreditAccount_topic =  df_abi[(df_abi['name']=='LiquidateExpiredCreditAccount')  & (df_abi['contract_type']=='creditFacade') &(df_abi['type']=='event')]['topic'].values[0]\n",
    "\n",
    "from_block = 0\n",
    "\n",
    "logging.info(f'from_block={from_block}')\n",
    "\n",
    "df_events = pd.DataFrame()\n",
    "\n",
    "for CM in CreditManagers:\n",
    "    logging.info(f'get_logs from CM {CM}')    \n",
    "    logs_v1 = get_logs(w3_eth, CM, df_abi, \n",
    "                    [[OpenCreditAccount_topic_v1,\n",
    "                      AddCollateral_topic_v1,\n",
    "                      CloseCreditAccount_topic_v1,\n",
    "                      RepayCreditAccount_topic_v1,\n",
    "                      LiquidateCreditAccount_topic_v1,]\n",
    "                    ],\n",
    "                    from_block,\n",
    "                    'latest')\n",
    "    df_events = df_events.append(logs_v1, ignore_index = True)  \n",
    "        \n",
    "    if cm_dict[CM]['creditFacade']:\n",
    "        for cf in cm_dict[CM]['creditFacade_upgraded']:\n",
    "            logging.info(f'get_logs from CF {cf}') \n",
    "            logs = get_logs(w3_eth, cf, df_abi, \n",
    "                            [[OpenCreditAccount_topic,\n",
    "                              AddCollateral_topic, \n",
    "                              CloseCreditAccount_topic, \n",
    "                              LiquidateCreditAccount_topic, \n",
    "                              LiquidateExpiredCreditAccount_topic]\n",
    "                            ],\n",
    "                            from_block,\n",
    "                            'latest')\n",
    "            df_events = df_events.append(logs, ignore_index = True)  \n",
    "        \n",
    "logging.info(f'number of events={len(df_events)}')\n",
    "cf_dict = {x:cm_dict[x]['creditFacade_upgraded'] for x in cm_dict if 'creditFacade_upgraded' in cm_dict[x]}\n",
    "cf_dict = {x:cf_dict[x] for x in cf_dict if len(cf_dict[x])>0}\n",
    "cf_dict = {y:{'CreditManager':x} for x in cf_dict for y in cf_dict[x]}\n",
    "logging.info(f'cf_dict = {cf_dict}')\n",
    "\n",
    "if len(df_events)>0:\n",
    "    df_events['CF'] = df_events['address'].apply(lambda x: x if x in cf_dict else None)\n",
    "    df_events['CM'] = df_events['address'].apply(lambda x: x if x in cm_dict else cf_dict[x]['CreditManager'])\n",
    "    \n",
    "    \n",
    "    df_events['CM_Token'] = df_events['CM'].apply(lambda x: cm_dict[x]['symbol'])\n",
    "    df_events['blockHash'] = df_events['blockHash'].apply(lambda x: x.hex())\n",
    "    df_events['transactionHash'] = df_events['transactionHash'].apply(lambda x: x.hex())\n",
    "    df_events['Borrower'] = df_events.apply(lambda x: x['args']['to'] \n",
    "                                                  if x['event'] in ['CloseCreditAccount','CloseCreditAccount'] \n",
    "                                                  else x['args']['onBehalfOf'] if x['event'] in ['OpenCreditAccount','AddCollateral']\n",
    "                                                  else x['args']['borrower'] if x['event'] in ['IncreaseBorrowedAmount']\n",
    "                                                  else x['args']['owner'] if 'owner' in x['args'] and x['event'] in ['LiquidateCreditAccount', 'LiquidateExpiredCreditAccount']\n",
    "                                                  else x['args']['borrower'] if 'borrower' in x['args'] and x['event'] in ['LiquidateCreditAccount', 'LiquidateExpiredCreditAccount']\n",
    "                                                  else None\n",
    "                                        ,axis=1)\n",
    "    df_events['Liquidator'] = df_events.apply(lambda x: x['args']['liquidator'] if x['event'] in ['LiquidateCreditAccount','LiquidateExpiredCreditAccount']\n",
    "                                                  else None\n",
    "                                        ,axis=1)\n",
    "    df_events['Collateral_Token'] = df_events.apply(lambda x: allowedTokens[x['args']['token']]['symbol'] if x['event']=='AddCollateral'\n",
    "                                                    else cm_dict[x['CM']]['symbol']\n",
    "                                        ,axis=1)\n",
    "    df_events['Collateral'] = df_events.apply(lambda x: x['args']['amount']*(10**-cm_dict[x['CM']]['decimals']) if x['event']=='OpenCreditAccount' and 'amount' in x['args']\n",
    "                                                    else x['args']['value']*(10**-allowedTokens[x['args']['token']]['decimals']) if x['event']=='AddCollateral'\n",
    "                                                    else None\n",
    "                                        ,axis=1)\n",
    "\n",
    "    logging.info('pull event timestamp begin')\n",
    "    df_events['blockTimestamp'] = df_events['blockNumber'].apply(lambda x: datetime.utcfromtimestamp(w3_eth.eth.getBlock(x)['timestamp']))\n",
    "    logging.info('pull event timestamp end')\n",
    "    \n",
    "    logging.info('pull transaction fee begin')\n",
    "    df_events['transaction_receipt'] = df_events['transactionHash'].apply(lambda x: dict(w3_eth.eth.get_transaction_receipt(x)))\n",
    "    df_events['transaction_fee'] = df_events['transaction_receipt'].apply(lambda x: x['gasUsed']*x['effectiveGasPrice']/1e18)\n",
    "    logging.info('pull transaction fee end')    \n",
    "    \n",
    "    #df_events[['args','transaction_receipt']] = df_events[['args','transaction_receipt']].apply(str)\n",
    "    #df_events['Liquidator'] = df_events['Liquidator'].astype('object')    \n",
    "    #df_events['Collateral'] = df_events['Collateral'].astype('float64')\n",
    "    df_events.drop(columns = 'logIndex')\n",
    "    df_events = df_events.sort_values('blockTimestamp', ignore_index = True)\n",
    "\n",
    "else:\n",
    "    logging.info(f'No new events since block {from_block}')\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "af181d55",
   "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>address</th>\n",
       "      <th>args</th>\n",
       "      <th>blockHash</th>\n",
       "      <th>blockNumber</th>\n",
       "      <th>event</th>\n",
       "      <th>logIndex</th>\n",
       "      <th>transactionHash</th>\n",
       "      <th>transactionIndex</th>\n",
       "      <th>CF</th>\n",
       "      <th>CM</th>\n",
       "      <th>CM_Token</th>\n",
       "      <th>Borrower</th>\n",
       "      <th>Liquidator</th>\n",
       "      <th>Collateral_Token</th>\n",
       "      <th>Collateral</th>\n",
       "      <th>blockTimestamp</th>\n",
       "      <th>transaction_receipt</th>\n",
       "      <th>transaction_fee</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>(sender, onBehalfOf, creditAccount, amount, bo...</td>\n",
       "      <td>0x6191b1dc0938794510eb96bc4e9b1ec8d1a91fa9352b...</td>\n",
       "      <td>13858003</td>\n",
       "      <td>OpenCreditAccount</td>\n",
       "      <td>316</td>\n",
       "      <td>0xc322fdd8d731a58b3e95414d3201fa53be82fd780e82...</td>\n",
       "      <td>220</td>\n",
       "      <td>None</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>DAI</td>\n",
       "      <td>0x8a4B89D76A1a745a4A1aDEBd3793253FBa0adADc</td>\n",
       "      <td>None</td>\n",
       "      <td>DAI</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>2021-12-22 23:40:25</td>\n",
       "      <td>{'blockHash': b'a\\x91\\xb1\\xdc\\t8yE\\x10\\xeb\\x96...</td>\n",
       "      <td>0.029386</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>(sender, onBehalfOf, creditAccount, amount, bo...</td>\n",
       "      <td>0x880436c3d9131fc9a17a6d79ad2acdcd50974d1cde97...</td>\n",
       "      <td>13862947</td>\n",
       "      <td>OpenCreditAccount</td>\n",
       "      <td>458</td>\n",
       "      <td>0x37aa66296c27993bdc5e81e27c6d819e7466df0c070c...</td>\n",
       "      <td>356</td>\n",
       "      <td>None</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>1.250000</td>\n",
       "      <td>2021-12-23 18:09:38</td>\n",
       "      <td>{'blockHash': b'\\x88\\x046\\xc3\\xd9\\x13\\x1f\\xc9\\...</td>\n",
       "      <td>0.047534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>(sender, onBehalfOf, creditAccount, amount, bo...</td>\n",
       "      <td>0xef59810b1015fa4f1a64d68a6166fe9d23e40cb1f585...</td>\n",
       "      <td>13862953</td>\n",
       "      <td>OpenCreditAccount</td>\n",
       "      <td>168</td>\n",
       "      <td>0xae23bb735281dbfede5cc3352dad8b7bdaa529b27989...</td>\n",
       "      <td>125</td>\n",
       "      <td>None</td>\n",
       "      <td>0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB</td>\n",
       "      <td>DAI</td>\n",
       "      <td>0x7BAFC0D5c5892f2041FD9F2415A7611042218e22</td>\n",
       "      <td>None</td>\n",
       "      <td>DAI</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>2021-12-23 18:11:05</td>\n",
       "      <td>{'blockHash': b'\\xefY\\x81\\x0b\\x10\\x15\\xfaO\\x1a...</td>\n",
       "      <td>0.036783</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>(onBehalfOf, token, value)</td>\n",
       "      <td>0x60a3c671e4f5d06281611a576410de04c4ec1478971b...</td>\n",
       "      <td>13862954</td>\n",
       "      <td>AddCollateral</td>\n",
       "      <td>434</td>\n",
       "      <td>0xf37a9984138c6b9c02bc657593ef6d0680dd69e2f002...</td>\n",
       "      <td>312</td>\n",
       "      <td>None</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0.550000</td>\n",
       "      <td>2021-12-23 18:11:07</td>\n",
       "      <td>{'blockHash': b'`\\xa3\\xc6q\\xe4\\xf5\\xd0b\\x81a\\x...</td>\n",
       "      <td>0.011669</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>(owner, to, remainingFunds)</td>\n",
       "      <td>0x6db00f3e2855ea71eb99701dfd5adb413666e13b2e72...</td>\n",
       "      <td>13862971</td>\n",
       "      <td>CloseCreditAccount</td>\n",
       "      <td>300</td>\n",
       "      <td>0x380f4f2acff142b5a407b51cabbd50b611d9702aa99b...</td>\n",
       "      <td>160</td>\n",
       "      <td>None</td>\n",
       "      <td>0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-12-23 18:14:12</td>\n",
       "      <td>{'blockHash': b'm\\xb0\\x0f&gt;(U\\xeaq\\xeb\\x99p\\x1d...</td>\n",
       "      <td>0.029957</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",
       "      <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>4105</th>\n",
       "      <td>0xC59135f449bb623501145443c70A30eE648Fa304</td>\n",
       "      <td>(onBehalfOf, creditAccount, borrowAmount, refe...</td>\n",
       "      <td>0xc97d2ee1e1f1babc0702a28bbfd0183e840ccc2861fb...</td>\n",
       "      <td>17600969</td>\n",
       "      <td>OpenCreditAccount</td>\n",
       "      <td>173</td>\n",
       "      <td>0x94673eb6fd9f4b06aec2203fbcb5ed05f9b47b922c10...</td>\n",
       "      <td>103</td>\n",
       "      <td>0xC59135f449bb623501145443c70A30eE648Fa304</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x6e58309CD851A5B124E3A56768a42d12f3B6D104</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-07-01 18:26:11</td>\n",
       "      <td>{'blockHash': b'\\xc9}.\\xe1\\xe1\\xf1\\xba\\xbc\\x07...</td>\n",
       "      <td>0.008769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4106</th>\n",
       "      <td>0xC59135f449bb623501145443c70A30eE648Fa304</td>\n",
       "      <td>(onBehalfOf, token, value)</td>\n",
       "      <td>0xc97d2ee1e1f1babc0702a28bbfd0183e840ccc2861fb...</td>\n",
       "      <td>17600969</td>\n",
       "      <td>AddCollateral</td>\n",
       "      <td>176</td>\n",
       "      <td>0x94673eb6fd9f4b06aec2203fbcb5ed05f9b47b922c10...</td>\n",
       "      <td>103</td>\n",
       "      <td>0xC59135f449bb623501145443c70A30eE648Fa304</td>\n",
       "      <td>0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B</td>\n",
       "      <td>WETH</td>\n",
       "      <td>0x6e58309CD851A5B124E3A56768a42d12f3B6D104</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>20.000000</td>\n",
       "      <td>2023-07-01 18:26:11</td>\n",
       "      <td>{'blockHash': b'\\xc9}.\\xe1\\xe1\\xf1\\xba\\xbc\\x07...</td>\n",
       "      <td>0.008769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4107</th>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>(onBehalfOf, creditAccount, borrowAmount, refe...</td>\n",
       "      <td>0xa456ce921940a097d2d78c8a9cf2985f351c0cb58145...</td>\n",
       "      <td>17647010</td>\n",
       "      <td>OpenCreditAccount</td>\n",
       "      <td>149</td>\n",
       "      <td>0x20d0e8943c2455d3495954fd12321fd24f5e14a0cf16...</td>\n",
       "      <td>53</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x166E58eA56399d5809FFfe195196554E9934bfF7</td>\n",
       "      <td>None</td>\n",
       "      <td>USDC</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-07-08 05:36:23</td>\n",
       "      <td>{'blockHash': b'\\xa4V\\xce\\x92\\x19@\\xa0\\x97\\xd2...</td>\n",
       "      <td>0.005985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4108</th>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>(onBehalfOf, token, value)</td>\n",
       "      <td>0xa456ce921940a097d2d78c8a9cf2985f351c0cb58145...</td>\n",
       "      <td>17647010</td>\n",
       "      <td>AddCollateral</td>\n",
       "      <td>152</td>\n",
       "      <td>0x20d0e8943c2455d3495954fd12321fd24f5e14a0cf16...</td>\n",
       "      <td>53</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x166E58eA56399d5809FFfe195196554E9934bfF7</td>\n",
       "      <td>None</td>\n",
       "      <td>USDC</td>\n",
       "      <td>25000.000036</td>\n",
       "      <td>2023-07-08 05:36:23</td>\n",
       "      <td>{'blockHash': b'\\xa4V\\xce\\x92\\x19@\\xa0\\x97\\xd2...</td>\n",
       "      <td>0.005985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4109</th>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>(onBehalfOf, token, value)</td>\n",
       "      <td>0x32e5b4c900cab2497e24850c52e032d9162b4ce7128f...</td>\n",
       "      <td>17647076</td>\n",
       "      <td>AddCollateral</td>\n",
       "      <td>86</td>\n",
       "      <td>0xb9805b8499ffec0e335f3b924cabf34eac1e0e44b530...</td>\n",
       "      <td>55</td>\n",
       "      <td>0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245</td>\n",
       "      <td>0x95357303f995e184A7998dA6C6eA35cC728A1900</td>\n",
       "      <td>USDC</td>\n",
       "      <td>0x166E58eA56399d5809FFfe195196554E9934bfF7</td>\n",
       "      <td>None</td>\n",
       "      <td>WETH</td>\n",
       "      <td>10.000000</td>\n",
       "      <td>2023-07-08 05:49:35</td>\n",
       "      <td>{'blockHash': b'2\\xe5\\xb4\\xc9\\x00\\xca\\xb2I~$\\x...</td>\n",
       "      <td>0.004244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4110 rows × 18 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         address  \\\n",
       "0     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB   \n",
       "1     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0   \n",
       "2     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB   \n",
       "3     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0   \n",
       "4     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0   \n",
       "...                                          ...   \n",
       "4105  0xC59135f449bb623501145443c70A30eE648Fa304   \n",
       "4106  0xC59135f449bb623501145443c70A30eE648Fa304   \n",
       "4107  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "4108  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "4109  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "\n",
       "                                                   args  \\\n",
       "0     (sender, onBehalfOf, creditAccount, amount, bo...   \n",
       "1     (sender, onBehalfOf, creditAccount, amount, bo...   \n",
       "2     (sender, onBehalfOf, creditAccount, amount, bo...   \n",
       "3                            (onBehalfOf, token, value)   \n",
       "4                           (owner, to, remainingFunds)   \n",
       "...                                                 ...   \n",
       "4105  (onBehalfOf, creditAccount, borrowAmount, refe...   \n",
       "4106                         (onBehalfOf, token, value)   \n",
       "4107  (onBehalfOf, creditAccount, borrowAmount, refe...   \n",
       "4108                         (onBehalfOf, token, value)   \n",
       "4109                         (onBehalfOf, token, value)   \n",
       "\n",
       "                                              blockHash  blockNumber  \\\n",
       "0     0x6191b1dc0938794510eb96bc4e9b1ec8d1a91fa9352b...     13858003   \n",
       "1     0x880436c3d9131fc9a17a6d79ad2acdcd50974d1cde97...     13862947   \n",
       "2     0xef59810b1015fa4f1a64d68a6166fe9d23e40cb1f585...     13862953   \n",
       "3     0x60a3c671e4f5d06281611a576410de04c4ec1478971b...     13862954   \n",
       "4     0x6db00f3e2855ea71eb99701dfd5adb413666e13b2e72...     13862971   \n",
       "...                                                 ...          ...   \n",
       "4105  0xc97d2ee1e1f1babc0702a28bbfd0183e840ccc2861fb...     17600969   \n",
       "4106  0xc97d2ee1e1f1babc0702a28bbfd0183e840ccc2861fb...     17600969   \n",
       "4107  0xa456ce921940a097d2d78c8a9cf2985f351c0cb58145...     17647010   \n",
       "4108  0xa456ce921940a097d2d78c8a9cf2985f351c0cb58145...     17647010   \n",
       "4109  0x32e5b4c900cab2497e24850c52e032d9162b4ce7128f...     17647076   \n",
       "\n",
       "                   event  logIndex  \\\n",
       "0      OpenCreditAccount       316   \n",
       "1      OpenCreditAccount       458   \n",
       "2      OpenCreditAccount       168   \n",
       "3          AddCollateral       434   \n",
       "4     CloseCreditAccount       300   \n",
       "...                  ...       ...   \n",
       "4105   OpenCreditAccount       173   \n",
       "4106       AddCollateral       176   \n",
       "4107   OpenCreditAccount       149   \n",
       "4108       AddCollateral       152   \n",
       "4109       AddCollateral        86   \n",
       "\n",
       "                                        transactionHash  transactionIndex  \\\n",
       "0     0xc322fdd8d731a58b3e95414d3201fa53be82fd780e82...               220   \n",
       "1     0x37aa66296c27993bdc5e81e27c6d819e7466df0c070c...               356   \n",
       "2     0xae23bb735281dbfede5cc3352dad8b7bdaa529b27989...               125   \n",
       "3     0xf37a9984138c6b9c02bc657593ef6d0680dd69e2f002...               312   \n",
       "4     0x380f4f2acff142b5a407b51cabbd50b611d9702aa99b...               160   \n",
       "...                                                 ...               ...   \n",
       "4105  0x94673eb6fd9f4b06aec2203fbcb5ed05f9b47b922c10...               103   \n",
       "4106  0x94673eb6fd9f4b06aec2203fbcb5ed05f9b47b922c10...               103   \n",
       "4107  0x20d0e8943c2455d3495954fd12321fd24f5e14a0cf16...                53   \n",
       "4108  0x20d0e8943c2455d3495954fd12321fd24f5e14a0cf16...                53   \n",
       "4109  0xb9805b8499ffec0e335f3b924cabf34eac1e0e44b530...                55   \n",
       "\n",
       "                                              CF  \\\n",
       "0                                           None   \n",
       "1                                           None   \n",
       "2                                           None   \n",
       "3                                           None   \n",
       "4                                           None   \n",
       "...                                          ...   \n",
       "4105  0xC59135f449bb623501145443c70A30eE648Fa304   \n",
       "4106  0xC59135f449bb623501145443c70A30eE648Fa304   \n",
       "4107  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "4108  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "4109  0x8d2f33d168cca6D2436De16c27d3f1cEa30aC245   \n",
       "\n",
       "                                              CM CM_Token  \\\n",
       "0     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB      DAI   \n",
       "1     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     WETH   \n",
       "2     0x777E23A2AcB2fCbB35f6ccF98272d03C722Ba6EB      DAI   \n",
       "3     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     WETH   \n",
       "4     0x968f9a68a98819E2e6Bb910466e191A7b6cf02F0     WETH   \n",
       "...                                          ...      ...   \n",
       "4105  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B     WETH   \n",
       "4106  0x5887ad4Cb2352E7F01527035fAa3AE0Ef2cE2b9B     WETH   \n",
       "4107  0x95357303f995e184A7998dA6C6eA35cC728A1900     USDC   \n",
       "4108  0x95357303f995e184A7998dA6C6eA35cC728A1900     USDC   \n",
       "4109  0x95357303f995e184A7998dA6C6eA35cC728A1900     USDC   \n",
       "\n",
       "                                        Borrower Liquidator Collateral_Token  \\\n",
       "0     0x8a4B89D76A1a745a4A1aDEBd3793253FBa0adADc       None              DAI   \n",
       "1     0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA       None             WETH   \n",
       "2     0x7BAFC0D5c5892f2041FD9F2415A7611042218e22       None              DAI   \n",
       "3     0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA       None             WETH   \n",
       "4     0x9Fb7C29a87a2C5Eaf01ED3f0f4Eff040a81c9eCA       None             WETH   \n",
       "...                                          ...        ...              ...   \n",
       "4105  0x6e58309CD851A5B124E3A56768a42d12f3B6D104       None             WETH   \n",
       "4106  0x6e58309CD851A5B124E3A56768a42d12f3B6D104       None             WETH   \n",
       "4107  0x166E58eA56399d5809FFfe195196554E9934bfF7       None             USDC   \n",
       "4108  0x166E58eA56399d5809FFfe195196554E9934bfF7       None             USDC   \n",
       "4109  0x166E58eA56399d5809FFfe195196554E9934bfF7       None             WETH   \n",
       "\n",
       "        Collateral      blockTimestamp  \\\n",
       "0      1000.000000 2021-12-22 23:40:25   \n",
       "1         1.250000 2021-12-23 18:09:38   \n",
       "2      1000.000000 2021-12-23 18:11:05   \n",
       "3         0.550000 2021-12-23 18:11:07   \n",
       "4              NaN 2021-12-23 18:14:12   \n",
       "...            ...                 ...   \n",
       "4105           NaN 2023-07-01 18:26:11   \n",
       "4106     20.000000 2023-07-01 18:26:11   \n",
       "4107           NaN 2023-07-08 05:36:23   \n",
       "4108  25000.000036 2023-07-08 05:36:23   \n",
       "4109     10.000000 2023-07-08 05:49:35   \n",
       "\n",
       "                                    transaction_receipt  transaction_fee  \n",
       "0     {'blockHash': b'a\\x91\\xb1\\xdc\\t8yE\\x10\\xeb\\x96...         0.029386  \n",
       "1     {'blockHash': b'\\x88\\x046\\xc3\\xd9\\x13\\x1f\\xc9\\...         0.047534  \n",
       "2     {'blockHash': b'\\xefY\\x81\\x0b\\x10\\x15\\xfaO\\x1a...         0.036783  \n",
       "3     {'blockHash': b'`\\xa3\\xc6q\\xe4\\xf5\\xd0b\\x81a\\x...         0.011669  \n",
       "4     {'blockHash': b'm\\xb0\\x0f>(U\\xeaq\\xeb\\x99p\\x1d...         0.029957  \n",
       "...                                                 ...              ...  \n",
       "4105  {'blockHash': b'\\xc9}.\\xe1\\xe1\\xf1\\xba\\xbc\\x07...         0.008769  \n",
       "4106  {'blockHash': b'\\xc9}.\\xe1\\xe1\\xf1\\xba\\xbc\\x07...         0.008769  \n",
       "4107  {'blockHash': b'\\xa4V\\xce\\x92\\x19@\\xa0\\x97\\xd2...         0.005985  \n",
       "4108  {'blockHash': b'\\xa4V\\xce\\x92\\x19@\\xa0\\x97\\xd2...         0.005985  \n",
       "4109  {'blockHash': b'2\\xe5\\xb4\\xc9\\x00\\xca\\xb2I~$\\x...         0.004244  \n",
       "\n",
       "[4110 rows x 18 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(df_events)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f3476b7",
   "metadata": {},
   "source": [
    "# Data Studio Report\n",
    "https://datastudio.google.com/reporting/a95186ae-29b4-4d72-8807-612bb5f54dd0"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "544d19dd",
   "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
}