Calculating Total Value Locked in Liquidity Pools using DEX Data

from os import environ
import sys
import pandas as pd
import numpy as np
import logging
from datetime import date, datetime, timedelta
from coinmetrics.api_client import CoinMetricsClient
import plotly.graph_objs as go
import logging
from pytz import timezone as timezone_conv
from datetime import timezone as timezone_info
from dateutil.relativedelta import relativedelta
import matplotlib.dates as mdates
import matplotlib.pyplot as plt
%matplotlib inline

logging.basicConfig(
    format='%(asctime)s %(levelname)-8s %(message)s',
    level=logging.INFO,
    datefmt='%Y-%m-%d %H:%M:%S'
)

# We recommend privately storing your API key in your local environment.
try:
    api_key = environ["CM_API_KEY"]
    logging.info("Using API key found in environment")
except KeyError:
    api_key = ""
    logging.info("API key not found. Using community client")
    
client = CoinMetricsClient(api_key)

2024-10-09 14:36:29 INFO     Using API key found in environment

uni_v3_markets = client.reference_data_markets(
    exchange = 'uniswap_v3_eth'
).to_dataframe()

uni_v3_markets

weth_usdc_markets = uni_v3_markets.loc[(uni_v3_markets['base']=='usdc') & (uni_v3_markets['quote']=='weth')]

weth_usdc_pools = weth_usdc_markets.dropna(subset=['contract_address'])
weth_usdc_pools

assets = ['usdc','weth']
pools = weth_usdc_pools['contract_address'].to_list()
pools_tvl = pd.DataFrame()
start = datetime.now() - timedelta(days=30)

for asset in assets:
    tvl = client.get_list_of_balance_updates_v2(
        asset=asset,
        accounts=pools,
        start_time = start
    ).parallel(max_workers=10,time_increment=relativedelta(days=1)).to_dataframe()
    
    # Add the asset name to a new 'asset' column
    tvl['asset'] = asset
    pools_tvl = pd.concat([pools_tvl, tvl], axis=0)

Exporting to dataframe type: 100%|█████████████████████████████████████████████████████████████| 31/31 [00:28<00:00,  1.09it/s]
Exporting to dataframe type: 100%|█████████████████████████████████████████████████████████████| 31/31 [00:41<00:00,  1.34s/it]

# Create a mapping from contract_address to market
contract_to_market = weth_usdc_pools.set_index('contract_address')['market']
contract_to_market

contract_address
e0554a476a092703abdb3ef35c80e0d76d32939f    uniswap_v3_eth-1-usdc-weth-spot
88e6a0c2ddd26feeb64f039a2c41296fcb3f5640    uniswap_v3_eth-2-usdc-weth-spot
8ad599c3a0ff1de082011efddc58f1908eb6e6d8    uniswap_v3_eth-3-usdc-weth-spot
7bea39867e4169dbe237d55c8242a8f2fcdcc387    uniswap_v3_eth-4-usdc-weth-spot
Name: market, dtype: string

pools_tvl['market'] = pools_tvl['account'].map(contract_to_market)

pools_tvl

# Sort by 'consensus_time', 'market', and 'chain_sequence_number'
pools_tvl_sorted = pools_tvl.sort_values(by=['consensus_time', 'market', 'chain_sequence_number'], ascending=[True, True, False])

# Drop duplicates, keeping only the first occurrence (in this case, the highest 'chain_sequence_number')
pools_tvl_deduplicated = pools_tvl_sorted.drop_duplicates(subset=['consensus_time', 'market','asset'], keep='first')

pools_tvl_clean = pools_tvl_deduplicated[['market','consensus_time','asset','new_balance']].copy()
pools_tvl_clean['new_balance'] = pools_tvl_clean.apply(
    lambda row: -row['new_balance'] if row['asset'] == 'weth' else row['new_balance'],
    axis=1
)
pools_tvl_clean

df = pd.DataFrame(pools_tvl_clean)
df['consensus_time'] = pd.to_datetime(df['consensus_time'])
# Split the DataFrame by market and store in a dictionary
market_dfs = {market: group.pivot(index='consensus_time', columns='asset', values='new_balance').ffill()
              for market, group in df.groupby('market')}

display(market_dfs)

{'uniswap_v3_eth-1-usdc-weth-spot': asset                              usdc         weth
 consensus_time                                      
 2024-09-09 14:37:35+00:00  6.006776e+05  -466.442498
 2024-09-09 14:37:47+00:00  5.927582e+05  -469.913266
 2024-09-09 14:38:11+00:00  5.834235e+05  -474.007791
 2024-09-09 14:38:23+00:00  5.852796e+05  -473.193522
 2024-09-09 14:38:35+00:00  5.783144e+05  -476.250599
 ...                                 ...          ...
 2024-10-09 18:35:11+00:00  2.504156e+06 -2408.677115
 2024-10-09 18:35:35+00:00  2.507156e+06 -2408.677115
 2024-10-09 18:36:11+00:00  2.503081e+06 -2408.677115
 2024-10-09 18:36:23+00:00  2.503766e+06 -2408.677115
 2024-10-09 18:36:59+00:00  2.493272e+06 -2408.677115
 
 [93275 rows x 2 columns],
 'uniswap_v3_eth-2-usdc-weth-spot': asset                              usdc          weth
 consensus_time                                       
 2024-09-09 14:37:23+00:00  7.261307e+07 -31266.590416
 2024-09-09 14:37:35+00:00  7.261070e+07 -31267.627526
 2024-09-09 14:37:47+00:00  7.244846e+07 -31338.733700
 2024-09-09 14:37:59+00:00  7.240161e+07 -31359.275300
 2024-09-09 14:38:11+00:00  7.227468e+07 -31414.964479
 ...                                 ...           ...
 2024-10-09 18:34:47+00:00  7.997092e+07 -27354.125311
 2024-10-09 18:35:35+00:00  7.998951e+07 -27354.125311
 2024-10-09 18:36:11+00:00  8.006068e+07 -27354.125311
 2024-10-09 18:36:23+00:00  8.005964e+07 -27354.125311
 2024-10-09 18:36:47+00:00  8.005916e+07 -27354.125311
 
 [89932 rows x 2 columns],
 'uniswap_v3_eth-3-usdc-weth-spot': asset                              usdc          weth
 consensus_time                                       
 2024-09-09 14:40:11+00:00  9.802691e+06 -22517.703981
 2024-09-09 15:06:59+00:00  9.815312e+06 -22512.198607
 2024-09-09 15:07:11+00:00  9.836937e+06 -22502.771507
 2024-09-09 15:13:23+00:00  9.817752e+06 -22511.184829
 2024-09-09 15:13:47+00:00  9.797839e+06 -22519.924545
 ...                                 ...           ...
 2024-10-09 18:15:47+00:00  1.306390e+07 -19482.217019
 2024-10-09 18:16:59+00:00  1.293324e+07 -19536.165795
 2024-10-09 18:17:11+00:00  1.276007e+07 -19607.732168
 2024-10-09 18:18:23+00:00  1.255446e+07 -19692.807793
 2024-10-09 18:23:47+00:00  1.255437e+07 -19692.792301
 
 [7850 rows x 2 columns],
 'uniswap_v3_eth-4-usdc-weth-spot': asset                               usdc        weth
 consensus_time                                      
 2024-09-09 15:17:35+00:00  288875.833924 -166.205531
 2024-09-09 16:12:35+00:00  291361.212465 -165.137952
 2024-09-09 16:56:47+00:00  293088.964462 -164.400079
 2024-09-09 17:10:23+00:00  293188.964462 -164.357478
 2024-09-09 19:54:35+00:00  293683.964462 -164.146777
 ...                                  ...         ...
 2024-10-08 21:45:59+00:00  311219.129512 -157.116899
 2024-10-08 21:56:11+00:00  311402.896623 -157.042040
 2024-10-09 01:33:23+00:00  312960.239993 -156.409115
 2024-10-09 01:38:11+00:00  314224.468721 -155.897241
 2024-10-09 18:30:11+00:00  313919.457241 -156.023085
 
 [435 rows x 2 columns]}

ref_rate = client.get_asset_metrics(
    assets=['usdc','weth'],
    metrics='ReferenceRateUSD',
    start_time=start,
    frequency='1m'
).parallel(max_workers=10,time_increment=relativedelta(days=1)).to_dataframe()

Exporting to dataframe type: 100%|█████████████████████████████████████████████████████████████| 62/62 [00:06<00:00,  8.93it/s]

ref_rate = ref_rate.pivot(index='time', columns='asset', values='ReferenceRateUSD')
ref_rate

# Iterate over each market DataFrame
for market, df in market_dfs.items():
    # Resample the DataFrame to 1-minute intervals
    df_resampled = df.resample('min').last().dropna()

    # Reindex the market DataFrame to the ref_rate DataFrame's index
    aligned_df = df_resampled.reindex(ref_rate.index, method='nearest')

    # Multiply the 'usdc' and 'weth' columns by the corresponding rate
    aligned_df['usdc'] = aligned_df['usdc'] * ref_rate['usdc']
    aligned_df['weth'] = aligned_df['weth'] * ref_rate['weth']

    # Replace the original DataFrame in the dictionary with the updated one
    market_dfs[market] = aligned_df

first_pool_key = list(market_dfs.keys())[0]
first_pool = market_dfs[first_pool_key]  
first_pool

def generate_area_figure(df, layout, columns, diverging_colors=False):
    traces = []
    for series in columns:
        traces.append(
            go.Scatter(
                x=df.index,
                y=df[series],
                name=series,
                fill='tozeroy'  # Ensures filling to the zero line on the y-axis
            ))
    return go.Figure(data=traces, layout=layout)

# Plotting for each market using Matplotlib
market_to_contract = contract_to_market.reset_index().set_index('market')
for market, data in market_dfs.items():
    address = market_to_contract.loc[market, 'contract_address']
    print(f'{market}')
    print(f'{address}')
    print(f'USDC: https://atlas.coinmetrics.io/address-details?asset=usdc&address={address}')
    print(f'WETH: https://atlas.coinmetrics.io/address-details?asset=weth&address={address}')

    # Create the figure and axis
    fig, ax = plt.subplots(figsize=(10, 6))

    # Plot the area chart for each series in the data
    for column in data.columns:
        ax.fill_between(data.index, data[column], label=column)

    # Set chart title and labels
    ax.set_title(f'{market.upper()}\nLiquidity Pool TVL', fontsize=16, color='grey')
    ax.set_xlabel('', color='grey')
    ax.set_ylabel('TVL Value (USD)', color='grey')

    # Set grid and background colors
    ax.set_facecolor('#49494a')
    ax.grid(True, color='white', linestyle='--', linewidth=0.5)

    # Customize tick parameters (color and size)
    ax.tick_params(axis='x', colors='grey', rotation=45)
    ax.tick_params(axis='y', colors='grey')
    ax.xaxis.set_major_locator(mdates.DayLocator(interval=4))
    ax.set_xlim([data.index.min(), data.index.max()])

    # Set legend and its appearance
    ax.legend(loc='upper right', fontsize=10)

    # Adjust the plot layout
    plt.tight_layout()

    # Show the plot
    plt.show()

uniswap_v3_eth-1-usdc-weth-spot
e0554a476a092703abdb3ef35c80e0d76d32939f
USDC: https://atlas.coinmetrics.io/address-details?asset=usdc&address=e0554a476a092703abdb3ef35c80e0d76d32939f
WETH: https://atlas.coinmetrics.io/address-details?asset=weth&address=e0554a476a092703abdb3ef35c80e0d76d32939f

uniswap_v3_eth-2-usdc-weth-spot
88e6a0c2ddd26feeb64f039a2c41296fcb3f5640
USDC: https://atlas.coinmetrics.io/address-details?asset=usdc&address=88e6a0c2ddd26feeb64f039a2c41296fcb3f5640
WETH: https://atlas.coinmetrics.io/address-details?asset=weth&address=88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

uniswap_v3_eth-3-usdc-weth-spot
8ad599c3a0ff1de082011efddc58f1908eb6e6d8
USDC: https://atlas.coinmetrics.io/address-details?asset=usdc&address=8ad599c3a0ff1de082011efddc58f1908eb6e6d8
WETH: https://atlas.coinmetrics.io/address-details?asset=weth&address=8ad599c3a0ff1de082011efddc58f1908eb6e6d8

uniswap_v3_eth-4-usdc-weth-spot
7bea39867e4169dbe237d55c8242a8f2fcdcc387
USDC: https://atlas.coinmetrics.io/address-details?asset=usdc&address=7bea39867e4169dbe237d55c8242a8f2fcdcc387
WETH: https://atlas.coinmetrics.io/address-details?asset=weth&address=7bea39867e4169dbe237d55c8242a8f2fcdcc387