Data Encyclopedia
HomeCharts
  • Welcome & Product Overview
  • Getting Started
  • Tutorials and Examples
    • Tutorials
      • Python API Client Walkthrough
      • Getting Started With Market Data
      • Getting Started With Futures Data
      • Aggregating Options Data
      • Examining Orderbook Depth
      • Aggregating Orderbook Depth to Create Liquidity Metrics
      • Comparing Stablecoin Prices Using Different Pricing Methods
      • Comparing Volumes of Exchanges and Assets
      • Creating Custom Network Data Metrics Using ATLAS
      • Applying Different Types of Marketcap Metrics
      • Comparing the Dominance of Mining Pools Using ATLAS
      • Using Staking Metrics to Get Yield and Staked Supply
      • Granular Insights On Chain Using Hourly Network Data Metrics
      • Exploring Options, Open Interest, and Volatility Data
      • Calculating Total Value Locked in Liquidity Pools using DEX Data
      • Calculating DEX Liquidity Pool Fees and Volumes
      • Analyzing DeFi Protocol Balance Sheets
    • How To Guides
      • How To Export Data
      • How To Migrate From Catalog to Catalog V2 and Reference Data
      • How To Use the Coin Metrics API Efficiently
    • Dashboard Examples
  • Packages
    • CM Labs
    • Coin Metrics Community Data
  • Access Our Data
    • API Reference
    • API Conventions
      • Catalog V1 to Catalog V2 Migration
    • Python API Client
    • R API Client
    • Coverage
    • Status Page
  • Data Visualization
    • Charting Tool
      • Formula Builder
      • Correlation Tool
      • Embedded Charts
      • Troubleshooting
    • Dashboard
      • Troubleshooting
    • CMTV Charts (Labs)
      • Troubleshooting
    • Atlas Explorer
  • Network Data
    • Network Data Pro Overview
      • Availability
        • Asset Completion Time
      • Addresses
        • Active Addresses
        • Address Balances
        • New Addresses
      • Economics
        • Mining
        • Valuation
      • Exchange
        • Deposits
        • Exchange Supply
        • Net Flows
        • Transaction Count
        • Withdrawals
      • Fees and Revenue
        • Fees
        • Revenue
      • Market
        • Market Capitalization
        • Price
        • Profitability
        • Returns
        • Volatility
      • Key Risk Indicator (KRI) Feed
        • Blocks
        • Block Attributes
        • Block Size
        • Block Times
        • Empty Blocks
        • Fees
        • Outputs
        • Rewards
        • Feerates
        • Hashrate
        • Transaction Feerates
        • Transaction Fees
        • Transaction Sizes
        • Transactions
      • Mining
        • Balances
        • Difficulty
        • Exchange Flows
        • Flows
        • Hardware Hash Rate
        • Hash Rate
      • Network Usage
        • Blocks
        • Contracts
        • Profitability
        • UTXOs
        • Blobs
      • Staking
        • Consensus Health
        • Flows
        • Penalty Metrics
        • Slashing Metrics
        • Validator Supply
        • Stakers
        • Yield
      • Supply
        • Active Supply
        • Addresses with Balance
        • Burnt Supply
        • Current Supply
        • Free Float Supply
        • Future Expected Supply
        • Miner Revenue
        • Profitability
        • Revived Supply
        • Shielded Supply
        • Supply Issuance
        • Staking Supply
      • Transactions
        • Blobs
        • Contracts
        • Token Transactions
        • Transactions
        • Transfer Value
        • Transfers
        • Velocity
      • Wallets
        • Active Wallets
        • Wallet Balances
    • Atlas Overview
      • Accounts
      • Account Balance
      • Blocks
        • Full Block
      • Transactions
        • Full Transaction
          • Full Transaction Info for Block
      • Balance Updates
    • Methodologies
      • Normalizing Block Times
    • DeFi Overview
      • Decentralized Exchange Data
      • DeFi Balance Sheets
      • DeFi FAQs
    • Tagging Meta Data
    • Transaction Tracker
    • CM Labs
      • Mining Pool Monitor Overview
        • Mining Pool Monitor API Fields
      • Reorg & Fork Tracker Overview
        • Reorg & Fork Tracker Tracker API Fields
    • Deprecated
      • Mempool Monitor
      • WatchTower Alerts Overview - DEPRECATED
        • WatchTower Alerts - DEPRECATED
          • Ethereum Proof-of-Stake Alerts - DEPRECATED
            • Missed Slot Alert - DEPRECATED
            • Fast Increase in Transaction Count Alert - DEPRECATED
            • Fast Decrease in Transaction Count Alert - DEPRECATED
            • Fast Decrease in Base Fees - DEPRECATED
            • Fast Increase in Base Fees - DEPRECATED
            • Fast Decrease in Priority Fees (Tips) Alert - DEPRECATED
            • Fast Increase in Priority Fees (Tips) Alert - DEPRECATED
            • Decrease in Active Addresses Alert - DEPRECATED
            • Increase in Active Addresses Alert - DEPRECATED
            • Decrease in Total Block Fees Alert - DEPRECATED
            • Increase in Total Block Fees Alert - DEPRECATED
          • DeFi Alerts - DEPRECATED
            • Smart Contract Admin Change Alert - DEPRECATED
            • Admin Change with Issuance Event Alert - DEPRECATED
            • Admin Change with Large Issuance Event Alert - DEPRECATED
          • Mining Alerts - DEPRECATED
            • Unknown Miner Predominance Alert - DEPRECATED
            • Mining Pool Conflict Alert - DEPRECATED
            • Persistent Mining Pool Conflict Alert - DEPRECATED
            • Hashrate Decrease Alert - DEPRECATED
            • 1-Block Difficulty Decrease - DEPRECATED
          • Blockchain Alerts - DEPRECATED
            • 1 Block Reorg Alert - DEPRECATED
            • 2 Block Reorg Alert - DEPRECATED
            • 3 Block Reorg Alert - DEPRECATED
            • Satoshi Coins Spent - DEPRECATED
            • Vintage Coins Spent - DEPRECATED
            • Slow Block Alert - DEPRECATED
            • 1 Consecutive Empty Block Alert - DEPRECATED
            • 2 Consecutive Empty Blocks Alert - DEPRECATED
            • 3 Consecutive Empty Blocks Alert - DEPRECATED
            • 6 Consecutive Empty Blocks Alert - DEPRECATED
          • Mempool Alerts - DEPRECATED
            • Mempool Disruption Alert - DEPRECATED
            • Mempool Size 90% Alert - DEPRECATED
            • Mempool Size 95% Alert - DEPRECATED
            • Mempool Size 99% Alert - DEPRECATED
            • Mempool Size 100% Alert - DEPRECATED
            • Mempool Congestion Alert - DEPRECATED
        • WatchTower API Fields - DEPRECATED
    • Network Data Glossary
    • Network Data FAQs
  • Market Data
    • Market Data Overview
      • Basis
      • Candles
      • Contract Prices
      • Funding Rates
        • Funding Rates
        • Predicted Funding Rates
        • Aggregated Futures Funding Rate
        • Cumulative Futures Funding Rate
      • Greeks
      • Institution Metrics
        • Grayscale
          • Shares Outstanding
          • Market Price
          • Net Asset Value
          • Coin Per Share
          • Total Assets
      • Liquidations
        • Market Level Liquidations
        • Liquidation Metrics
      • Liquidity
        • Bid-Ask Spread Percent
        • Order Book Depth
        • Slippage
      • Market Metadata
      • Open Interest
        • Market Level Open Interest
        • Reported Open Interest
      • Orderbooks
      • Quotes
      • Trades
      • Volatility
        • Market Implied Volatility
        • Implied Volatility
        • Realized Volatility
      • Volume
        • Trusted Volume
        • Reported Volume
    • CM Prices
      • Reference Rate
      • Principal Market Price (USD)
      • Principal Market (USD)
    • Methodologies
      • Coin Metrics Prices Policies
      • Coin Metrics Prices Methodology
      • Trusted Exchange Framework
    • Market Data FAQs
      • CM Prices FAQs
      • Trusted Exchange Framework FAQs
  • Index Data
    • Index Overview
      • Index Timeseries
        • Index Levels
        • Index Candles
        • Index Constituents
    • Policies & Charters
      • CMBI Index Policies
      • Governance Committees
    • Methodologies
      • Fork Legitimacy Framework
      • Adjusted Free Float Supply Methodology
      • Candidate Market Guidelines
    • Fact Sheets
      • CMBI Single Asset Series Fact Sheet
      • CMBI Multi Asset Series Fact Sheet
      • CMBI Total Market Series Fact Sheet
      • CMBI Mining Series Fact Sheet
    • Indexes Glossary
    • Index FAQs
  • Reference Data
    • datonomy Overview
      • Taxonomy for Assets
      • Taxonomy Metadata for Assets
      • datonomy FAQs
    • Profiles Overview
      • Asset Profiles
      • Network Profiles
    • Security Master Overview
      • Assets
      • Markets
    • Methodologies
      • Guiding Principles and Methodology for datonomy
  • BITTENSOR
    • Precog Methodology
      • Point Forecast Ranking
      • Interval Forecast Ranking
      • Interval Score Examples
      • Miner Weight from Rank
Powered by GitBook
On this page
  • Resources
  • File Download
  • Notebook Setup
  • DEX Market Reference Data
  • Fetch contract balances over time with ATLAS
  • Retrieve Reference Rates to calculate the equivalent USD value for TVL
  • Plot USD-denominated TVL for target liquidity pools

Was this helpful?

  1. Tutorials and Examples
  2. Tutorials

Calculating Total Value Locked in Liquidity Pools using DEX Data

PreviousExploring Options, Open Interest, and Volatility DataNextCalculating DEX Liquidity Pool Fees and Volumes

Last updated 6 months ago

Was this helpful?

Automated Market Makers (AMMs) are an innovative new class of smart contracts introduced in decentralized exchange protocols like Uniswap, allowing users to permissionlessly provision liquidity for digital asset trading activity without needing a traditional central order book. However, an important aspect of providing users of these pools is understanding how the total USD value of the assets allocated to the contract can fluctuate over time, leading to impermanent loss for liquidity providers. In this notebook, we explore how Coin Metrics DEX market metadata can be combined with Reference Rates and ATLAS search engine capabilities to construct a timeseries representation pool TVL, allowing market participants to make more informed decisions about DEX market making and trading.

Resources

This notebook demonstrates basic functionality offered by the Coin Metrics Python API Client, ATLAS blockchain search engine, and DEX Market Data.

Coin Metrics offers a vast assortment of data for hundreds of cryptoassets. The Python API Client allows for easy access to this data using Python without needing to create your own wrappers using requests and other such libraries.

To understand the data that Coin Metrics offers, feel free to peruse the resources below.

File Download

Download the entire notebook as either a jupyter notebook to run yourself or as a pdf from the two links below

Notebook Setup

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

DEX Market Reference Data

The reference-data/markets endpoint returns a list of available markets meeting specified criteria. Users can pass in a list of markets, exchanges, or market types (spot, futures, options). For DEX markets, the endpoint also returns key liquidity pool metadata, such as fee tier and pool contract address.

uni_v3_markets = client.reference_data_markets(
    exchange = 'uniswap_v3_eth'
).to_dataframe()
uni_v3_markets
market
exchange
base
quote
pair
symbol
type
size_asset
margin_asset
strike
...
order_price_increment
order_price_min
order_price_max
order_size_min
order_taker_fee
order_maker_fee
margin_trading_enabled
experimental
base_native
quote_native

0

uniswap_v3_eth-1-1inch-dai-spot

uniswap_v3_eth

1inch

dai

1inch-dai

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1

uniswap_v3_eth-1-1inch-usdc-spot

uniswap_v3_eth

1inch

usdc

1inch-usdc

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

2

uniswap_v3_eth-1-1inch-weth-spot

uniswap_v3_eth

1inch

weth

1inch-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

3

uniswap_v3_eth-1-ageur_eth-eurcv_eth-spot

uniswap_v3_eth

ageur_eth

eurcv_eth

ageur_eth-eurcv_eth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

4

uniswap_v3_eth-1-ageur_eth-usdc-spot

uniswap_v3_eth

ageur_eth

usdc

ageur_eth-usdc

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

1638

uniswap_v3_eth-agg-yfi-cvx-spot

uniswap_v3_eth

yfi

cvx

yfi-cvx

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1639

uniswap_v3_eth-agg-yfi-link-spot

uniswap_v3_eth

yfi

link

yfi-link

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1640

uniswap_v3_eth-agg-yfi-usdc-spot

uniswap_v3_eth

yfi

usdc

yfi-usdc

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1641

uniswap_v3_eth-agg-yfi-wbtc-spot

uniswap_v3_eth

yfi

wbtc

yfi-wbtc

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1642

uniswap_v3_eth-agg-yfi-weth-spot

uniswap_v3_eth

yfi

weth

yfi-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1643 rows × 39 columns

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
market
exchange
base
quote
pair
symbol
type
size_asset
margin_asset
strike
...
order_price_increment
order_price_min
order_price_max
order_size_min
order_taker_fee
order_maker_fee
margin_trading_enabled
experimental
base_native
quote_native

72

uniswap_v3_eth-1-usdc-weth-spot

uniswap_v3_eth

usdc

weth

usdc-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

281

uniswap_v3_eth-2-usdc-weth-spot

uniswap_v3_eth

usdc

weth

usdc-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

650

uniswap_v3_eth-3-usdc-weth-spot

uniswap_v3_eth

usdc

weth

usdc-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

1045

uniswap_v3_eth-4-usdc-weth-spot

uniswap_v3_eth

usdc

weth

usdc-weth

<NA>

spot

<NA>

<NA>

<NA>

...

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

<NA>

True

<NA>

<NA>

4 rows × 39 columns

Fetch contract balances over time with ATLAS

Now that we have a list of target liquidity pool contracts, we can use ATLAS blockchain search engine to query for balance updates in the pool for each asset.

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
chain_sequence_number
account
account_creation_height
change
previous_balance
new_balance
transaction_sequence_number
n_debits
n_credits
block_hash
...
consensus_time
credit
total_received
total_sent
previous_debit_height
previous_credit_height
previous_chain_sequence_number
txid
asset
market

0

88964646899286016

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-60426.466497

72674047.982769

72613621.516272

0

4019999

3619510

96131a3e3702feb38e1ce25c71773962cdf6ed8beb5ee2...

...

2024-09-09 14:37:23+00:00

False

738732040663.426392

738659427041.910034

20713695

20713694

88964642604318722

a8f5e44bb9ae9f93a3d1b11b5fce58fe2bcad8ba73561f...

usdc

uniswap_v3_eth-2-usdc-weth-spot

1

88964646899286022

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-551.745218

72613621.516272

72613069.771054

0

4020000

3619510

96131a3e3702feb38e1ce25c71773962cdf6ed8beb5ee2...

...

2024-09-09 14:37:23+00:00

False

738732040663.426392

738659427593.655396

20713696

20713694

88964646899286016

3072cd48442d1e61b7232590812737dabdd52d60ed2181...

usdc

uniswap_v3_eth-2-usdc-weth-spot

2

88964651194253318

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-1189.382003

72613069.771054

72611880.389051

0

4020001

3619510

d1b93582ac0242d6c4d78a2e15fa7a14cb8f23cdd315a7...

...

2024-09-09 14:37:35+00:00

False

738732040663.426392

738659428783.037231

20713696

20713694

88964646899286022

785845bb01a0c4c95a4fce1a5b684b607eff59db5e35a0...

usdc

uniswap_v3_eth-2-usdc-weth-spot

3

88964651194253320

e0554a476a092703abdb3ef35c80e0d76d32939f

13616454

-1185.605465

601863.20449

600677.599025

1

231967

225267

d1b93582ac0242d6c4d78a2e15fa7a14cb8f23cdd315a7...

...

2024-09-09 14:37:35+00:00

False

5991391248.046802

5990790570.447777

20713695

20713695

88964642604318733

785845bb01a0c4c95a4fce1a5b684b607eff59db5e35a0...

usdc

uniswap_v3_eth-1-usdc-weth-spot

4

88964651194253338

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-1178.099514

72611880.389051

72610702.289537

0

4020002

3619510

d1b93582ac0242d6c4d78a2e15fa7a14cb8f23cdd315a7...

...

2024-09-09 14:37:35+00:00

False

738732040663.426392

738659429961.136841

20713697

20713694

88964651194253318

e8c9bf688ee4d41f22d95f34936a425edb4f61244318b7...

usdc

uniswap_v3_eth-2-usdc-weth-spot

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

273787

89893038440054825

e0554a476a092703abdb3ef35c80e0d76d32939f

13616454

11.45984

2397.232798

2408.692638

1

292522

301912

de4360d0ae6f824e4ca7525da2268497de62906fec744c...

...

2024-10-09 18:33:11+00:00

True

4128265.590843

4125856.898204

20929853

20929850

89893034145087672

aefc38b1fa84766bb2e50b8565d7e6b23031d98a28ccd8...

weth

uniswap_v3_eth-1-usdc-weth-spot

273788

89893038440054892

e0554a476a092703abdb3ef35c80e0d76d32939f

13616454

-0.015523

2408.692638

2408.677115

0

292523

301912

de4360d0ae6f824e4ca7525da2268497de62906fec744c...

...

2024-10-09 18:33:11+00:00

False

4128265.590843

4125856.913727

20929853

20929854

89893038440054825

9b83a0daab76cd6db6aedbafc5f1e61adc0f5d31e72e47...

weth

uniswap_v3_eth-1-usdc-weth-spot

273789

89893038440054968

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-2.123209

27357.193086

27355.069877

0

3685872

4078043

de4360d0ae6f824e4ca7525da2268497de62906fec744c...

...

2024-10-09 18:33:11+00:00

False

378530260.402821

378502905.332944

20929852

20929854

89893038440054823

e79fe9981166fccb9fb15b872da4ee5f8ea4f581a86e0d...

weth

uniswap_v3_eth-2-usdc-weth-spot

273790

89893051324956808

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-0.119392

27355.069877

27354.950485

0

3685873

4078043

2823efc897ab6e43f4382e7670f1cfa4aa621571e79d9d...

...

2024-10-09 18:33:47+00:00

False

378530260.402821

378502905.452336

20929854

20929854

89893038440054968

a46cab246dea451a42d4b26c79994c5fc06ce1a10a53a7...

weth

uniswap_v3_eth-2-usdc-weth-spot

273791

89893051324956894

88e6a0c2ddd26feeb64f039a2c41296fcb3f5640

12376729

-0.825174

27354.950485

27354.125311

0

3685874

4078043

2823efc897ab6e43f4382e7670f1cfa4aa621571e79d9d...

...

2024-10-09 18:33:47+00:00

False

378530260.402821

378502906.27751

20929857

20929854

89893051324956808

3004b50008190b1b36f5cdf602b63b9f9792267d89f2fe...

weth

uniswap_v3_eth-2-usdc-weth-spot

547648 rows × 21 columns

# 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
market
consensus_time
asset
new_balance

1

uniswap_v3_eth-2-usdc-weth-spot

2024-09-09 14:37:23+00:00

weth

-3.126659e+04

1

uniswap_v3_eth-2-usdc-weth-spot

2024-09-09 14:37:23+00:00

usdc

7.261307e+07

3

uniswap_v3_eth-1-usdc-weth-spot

2024-09-09 14:37:35+00:00

weth

-4.664425e+02

3

uniswap_v3_eth-1-usdc-weth-spot

2024-09-09 14:37:35+00:00

usdc

6.006776e+05

4

uniswap_v3_eth-2-usdc-weth-spot

2024-09-09 14:37:35+00:00

weth

-3.126763e+04

...

...

...

...

...

273850

uniswap_v3_eth-2-usdc-weth-spot

2024-10-09 18:36:11+00:00

usdc

8.006068e+07

273853

uniswap_v3_eth-1-usdc-weth-spot

2024-10-09 18:36:23+00:00

usdc

2.503766e+06

273851

uniswap_v3_eth-2-usdc-weth-spot

2024-10-09 18:36:23+00:00

usdc

8.005964e+07

273854

uniswap_v3_eth-2-usdc-weth-spot

2024-10-09 18:36:47+00:00

usdc

8.005916e+07

273855

uniswap_v3_eth-1-usdc-weth-spot

2024-10-09 18:36:59+00:00

usdc

2.493272e+06

382679 rows × 4 columns

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]}

Retrieve Reference Rates to calculate the equivalent USD value for TVL

To normalize pool TVL into USD-denominated terms, we'll leverage the Coin Metrics Reference Rate, which represents a volume-weighted median price across a subset of the asset's most highly-liquid markets.

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
asset
usdc
weth

time

2024-09-09 14:38:00+00:00

1.000228

2281.628477

2024-09-09 14:39:00+00:00

1.000066

2278.047686

2024-09-09 14:40:00+00:00

0.99975

2279.877181

2024-09-09 14:41:00+00:00

0.999917

2278.211943

2024-09-09 14:42:00+00:00

0.999867

2279.118291

...

...

...

2024-10-09 18:34:00+00:00

0.999862

2422.199211

2024-10-09 18:35:00+00:00

0.999822

2418.294121

2024-10-09 18:36:00+00:00

0.999827

2420.816853

2024-10-09 18:37:00+00:00

0.999789

2420.182517

2024-10-09 18:38:00+00:00

0.999873

2421.764058

43441 rows × 2 columns

# 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
asset
usdc
weth

time

2024-09-09 14:38:00+00:00

580007.558512

-1085063.725878

2024-09-09 14:39:00+00:00

591547.333378

-1071736.155646

2024-09-09 14:40:00+00:00

591664.869298

-1072292.759274

2024-09-09 14:41:00+00:00

584444.264977

-1078824.970611

2024-09-09 14:42:00+00:00

604433.557236

-1059255.13815

...

...

...

2024-10-09 18:34:00+00:00

2480138.279715

-5834295.808101

2024-10-09 18:35:00+00:00

2506710.799699

-5824889.708489

2024-10-09 18:36:00+00:00

2492840.090089

-5830966.153299

2024-10-09 18:37:00+00:00

2492746.887517

-5829438.242635

2024-10-09 18:38:00+00:00

2492954.27474

-5833247.665283

43441 rows × 2 columns

Plot USD-denominated TVL for target liquidity pools

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


The website contains the full set of endpoints and data offered by Coin Metrics.

The gives detailed, conceptual explanations of the data that Coin Metrics offers.

The contains a full list of functions.

Coin Metrics API v4
Coin Metrics Product Documentation
API Spec
453KB
DEFI_liquidity_pool_tvl.ipynb
840KB
DEFI_liquidity_pool_tvl.pdf
pdf