Skip to main content

Analyzing DeFi Protocol Balance Sheets

Decentralized Finance (DeFi) is a rapidly emerging ecosystem of applications and protocols used for trading, lending, and various other financial services. Rather than relying on centralized intermediaries, these protocols utilize permissionless blockchains such as Ethereum to conduct the majority of their activities and transactions on-chain. While these protocols offer an unprecedented level of transparency, complex smart contract code and overlapping on-chain transactions can be difficult to interpret. Coin Metrics DeFi Balance Sheets endpoint attempts to distill these operations into a traditional accounting format, presenting protocol assets and liabilities in an intuitive, easy-to-understand schema.

Resources​

This notebook demonstrates basic functionality offered by the Coin Metrics Python API Client.

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

Download DEFI - Dex Data.ipynb

Download DEFI_dex_data (1).pdf

Notebook Setup​

from os import environ
import sys
import pandas as pd
import numpy as np
import seaborn as sns
import logging
from datetime import date, datetime, timedelta
from coinmetrics.api_client import CoinMetricsClient
import json
import logging
from pytz import timezone as timezone_conv
from datetime import timezone as timezone_info
import matplotlib.dates as mdates
from IPython.display import Markdown as md
import matplotlib.pyplot as plt
import requests
import locale
import ast
# %matplotlib inline
sns.set_theme()
sns.set(rc={'figure.figsize':(12,8)})
sns.set_style("whitegrid",{'axes.grid' : True,'grid.linestyle': '--', 'grid.color': 'gray','axes.edgecolor': 'white','font.family': ['arial']})
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-16 09:31:03 INFO     Using API key found in environment

Retrieve Balance Sheet​

aave_v2_eth = client.get_defi_balance_sheets(
    defi_protocols = "aave_v2_eth",
    start_time = '2023-10-15',
    end_time = '2024-10-15').to_dataframe()

# Creating Start and End Time Variables to Simplify Future Data Creation
start_time = pd.to_datetime('2023-10-15')
end_time = pd.to_datetime('2024-10-15')

number_of_days = (end_time - start_time).days

header_index = number_of_days

#Showing last few rows of our current DataFrame with time parameters
aave_v2_eth.tail()
defi_protocolblock_heighttimeassets_total_usdassets_total_countliabilities_total_usdliabilities_total_countloans_lent_total_usdtvl_total_usdnet_working_capital_usdassetsliabilitiesprotocol_utilization_ratioliquid_supply_ratiocurrent_ratiodebt_to_assets_ratio
362aave_v2_eth209386572024-10-11 00:00:00+00:001093532689.9828491093614333.609549351033529.7825742499160.2003-351115173.4092[{'asset': '1inch', 'total_units': '56475.6927...[{'asset': '1inch', 'total_units': '56085.2667...0.3209850.6789410.6789411.000075
363aave_v2_eth209458112024-10-12 00:00:00+00:001111063638.7613491111174709.049449354732076.2007756331562.5606-354843146.4888[{'asset': '1inch', 'total_units': '56476.3058...[{'asset': '1inch', 'total_units': '56085.2668...0.3192410.680660.680661.0001
364aave_v2_eth209529572024-10-13 00:00:00+00:001124501751.5171491124630410.084249357832576.4653766669175.0518-357961235.0324[{'asset': '1inch', 'total_units': '56476.9193...[{'asset': '1inch', 'total_units': '56085.2669...0.3181780.6817080.6817081.000115
365aave_v2_eth209600892024-10-14 00:00:00+00:001116858119.0506491117027224.205149356159715.5002760698403.5504-356328820.6547[{'asset': '1inch', 'total_units': '56477.5331...[{'asset': '1inch', 'total_units': '56085.2669...0.3188470.6810030.6810031.000152
366aave_v2_eth209672442024-10-15 00:00:00+00:001167546255.9893491167839749.205649365325202.3638802221053.6255-365618695.5801[{'asset': '1inch', 'total_units': '56478.1472...[{'asset': '1inch', 'total_units': '56085.2670...0.3128220.6869280.6869281.000252
last_row = pd.DataFrame(aave_v2_eth.sort_values(by='time',ascending=False).iloc[0])
last_row
366
defi_protocolaave_v2_eth
block_height20967244
time2024-10-15 00:00:00+00:00
assets_total_usd1167546255.9893
assets_total_count49
liabilities_total_usd1167839749.2056
liabilities_total_count49
loans_lent_total_usd365325202.3638
tvl_total_usd802221053.6255
net_working_capital_usd-365618695.5801
assets[{'asset': '1inch', 'total_units': '56478.1472...
liabilities[{'asset': '1inch', 'total_units': '56085.2670...
protocol_utilization_ratio0.312822
liquid_supply_ratio0.686928
current_ratio0.686928
debt_to_assets_ratio1.000252
assets = last_row.loc["assets"].astype("str")
assets = assets.apply(lambda x: ast.literal_eval(x))
assets = assets.apply(pd.Series)
assets = assets.transpose()
assets = pd.json_normalize(assets[header_index])
assets = assets.set_index('asset').astype(float).sort_values('tvl_usd', ascending=False)
assets
total_unitsloans_lent_unitstvl_unitstotal_usdloans_lent_usdtvl_usdtotal_shareloans_lent_sharetvl_share
asset
wbtc4.848282e+035.466126e+024.301669e+033.198977e+083.606640e+072.838313e+080.2739910.0987240.353807
steth_lido1.040132e+05NaN1.040132e+052.735189e+08NaN2.735189e+080.234268NaN0.340952
weth9.980952e+043.763314e+046.217638e+042.625917e+089.901010e+071.635816e+080.2249090.2710190.203911
dai4.809159e+072.683005e+072.126154e+074.808146e+072.682440e+072.125706e+070.0411820.0734260.026498
usdt_eth1.032573e+088.644445e+071.681281e+071.032206e+088.641379e+071.680685e+070.0884080.2365390.020950
link1.464885e+065.475866e+041.410127e+061.649758e+076.166937e+051.588089e+070.0141300.0016880.019796
usdc1.271867e+081.148692e+081.231756e+071.271589e+081.148440e+081.231486e+070.1089110.3143610.015351
aave4.722557e+04NaN4.722557e+047.681909e+06NaN7.681909e+060.006580NaN0.009576
mkr1.280425e+031.074842e+001.279350e+031.783592e+061.497221e+031.782095e+060.0015280.0000040.002221
crv2.539174e+067.895537e+032.531279e+066.821795e+052.121230e+036.800583e+050.0005840.0000060.000848
uni8.171202e+042.884203e+037.882782e+046.628549e+052.339690e+046.394580e+050.0005680.0000640.000797
snx4.090228e+052.623178e+034.063996e+056.343925e+054.068537e+036.303239e+050.0005430.0000110.000786
busd7.909986e+052.157098e+055.752888e+057.890753e+052.151853e+055.738900e+050.0006760.0005890.000715
frax5.869414e+058.315375e+045.037876e+055.850664e+058.288812e+045.021783e+050.0005010.0002270.000626
tusd5.113231e+051.365453e+053.747778e+055.101233e+051.362249e+053.738985e+050.0004370.0003730.000466
gusd5.928732e+052.691468e+053.237263e+055.923533e+052.689108e+053.234425e+050.0005070.0007360.000403
yfi4.882787e+015.700117e-024.877086e+012.460453e+052.872309e+022.457580e+050.0002110.0000010.000306
mana9.075228e+051.570493e+057.504735e+052.807515e+054.858480e+042.321667e+050.0002400.0001330.000289
rai7.827556e+041.651545e+046.176010e+042.383428e+055.028824e+041.880546e+050.0002040.0001380.000234
susd3.454224e+051.701397e+051.752826e+053.448540e+051.698598e+051.749942e+050.0002950.0004650.000218
ens9.499343e+033.318105e+019.466162e+031.728763e+056.038542e+021.722725e+050.0001480.0000020.000215
ust6.382869e+062.446807e+056.138189e+061.395150e+055.348164e+031.341669e+050.0001190.0000150.000167
pax2.320820e+051.059562e+051.261257e+052.318035e+051.058291e+051.259744e+050.0001990.0002900.000157
bal6.257505e+043.488278e+035.908677e+041.290957e+057.196507e+031.218992e+050.0001110.0000200.000152
zrx2.935068e+055.396759e+032.881101e+059.856175e+041.812271e+039.674948e+040.0000840.0000050.000121
lusd1.440150e+055.852579e+048.548924e+041.437232e+055.840718e+048.531598e+040.0001230.0001600.000106
ren1.739052e+066.816859e+031.732235e+066.742399e+042.642933e+026.715970e+040.0000580.0000010.000084
cvx3.273132e+041.463298e+023.258499e+046.226566e+042.783670e+026.198729e+040.0000530.0000010.000077
bat3.405090e+056.836504e+033.336725e+056.146868e+041.234126e+036.023455e+040.0000530.0000030.000075
enj2.973739e+059.736583e+042.000080e+054.734441e+041.550145e+043.184295e+040.0000410.0000420.000040
1inch5.647815e+041.121128e+035.535702e+041.533170e+043.043444e+021.502736e+040.0000130.0000010.000019
ampl2.545603e+052.457468e+058.813521e+033.521378e+053.399459e+051.219190e+040.0003020.0009310.000015
knc3.358496e+041.393234e+041.965262e+041.582542e+046.564995e+039.260421e+030.0000140.0000180.000012
fei_eth1.075374e+043.299947e+037.453792e+031.045299e+043.207657e+037.245332e+030.0000090.0000090.000009
BptBALWETH2.422182e-09NaN2.422182e-09NaNNaNNaNNaNNaNNaN
BptWBTCWETH3.185679e-03NaN3.185679e-03NaNNaNNaNNaNNaNNaN
GUniDAIUSDC3.743267e+00NaN3.743267e+00NaNNaNNaNNaNNaNNaN
GUniUSDCUSDT9.999975e-02NaN9.999975e-02NaNNaNNaNNaNNaNNaN
Univ2AAVEWETH1.346558e-01NaN1.346558e-01NaNNaNNaNNaNNaNNaN
Univ2CRVWETH4.136273e-05NaN4.136273e-05NaNNaNNaNNaNNaNNaN
Univ2DAIUSDC9.089043e-06NaN9.089043e-06NaNNaNNaNNaNNaNNaN
Univ2DAIWETH3.669936e-01NaN3.669936e-01NaNNaNNaNNaNNaNNaN
Univ2LINKWETH8.050459e+01NaN8.050459e+01NaNNaNNaNNaNNaNNaN
Univ2UNIWETH1.210293e+00NaN1.210293e+00NaNNaNNaNNaNNaNNaN
Univ2USDCWETH3.099794e-07NaN3.099794e-07NaNNaNNaNNaNNaNNaN
Univ2WBTCWETH1.887777e-07NaN1.887777e-07NaNNaNNaNNaNNaNNaN
dpi8.275005e+024.202049e+024.072956e+02NaNNaNNaNNaNNaNNaN
renfil6.387879e+036.162561e+032.253174e+02NaNNaNNaNNaNNaNNaN
xsushi4.261961e+053.357735e+024.258604e+05NaNNaNNaNNaNNaNNaN
assets_tvl_totals = assets['tvl_usd']
#the top 7
assets_tvl_top7 = assets_tvl_totals[:7].copy()
# other
assets_tvl_top7.loc['other'] = assets['tvl_usd'][7:].sum()
df = pd.DataFrame(assets_tvl_top7)
df['tvl_usd'] = df['tvl_usd'].apply(lambda x: "${:,.2f}".format((x)))
df
tvl_usd
asset
wbtc$283,831,271.42
steth_lido$273,518,949.72
weth$163,581,616.37
dai$21,257,057.15
usdt_eth$16,806,852.06
link$15,880,889.36
usdc$12,314,862.37
other$15,029,555.17
def my_autopct(pct):
    return ('%.2f' % pct + '%') if pct > 7 else ''

axes = assets_tvl_top7.plot(kind='pie', autopct=my_autopct, figsize=(16, 16), subplots=True, layout=(2, 2), legend=False, labeldistance=1.1)

for ax in axes.flat:
    yl = ax.get_ylabel()
    ax.set(ylabel='', title=yl)
    ax.set_title('Aave_v2_ETH \nEstimated TVL Share (USD) by Asset*',size=20, font='arial')

fig = axes[0, 0].get_figure()
fig.suptitle('\n*Assets with CM Reference Rate',size=11, font='arial')

fig.tight_layout()
plt.rcParams['font.size'] = 15.0
aave_v2_eth.index = (pd.to_datetime(aave_v2_eth.time)).dt.date
aave_v2_eth["Total Value Locked (USD)"] = aave_v2_eth.tvl_total_usd.astype(float)
aave_tvl = aave_v2_eth[['defi_protocol','Total Value Locked (USD)']]
aave_tvl
defi_protocolTotal Value Locked (USD)
time
2023-10-15aave_v2_eth1.989641e+09
2023-10-16aave_v2_eth1.995368e+09
2023-10-17aave_v2_eth2.032416e+09
2023-10-18aave_v2_eth1.987327e+09
2023-10-19aave_v2_eth1.970314e+09
.........
2024-10-11aave_v2_eth7.424992e+08
2024-10-12aave_v2_eth7.563316e+08
2024-10-13aave_v2_eth7.666692e+08
2024-10-14aave_v2_eth7.606984e+08
2024-10-15aave_v2_eth8.022211e+08

367 rows × 2 columns

ax = aave_tvl.plot.area()
plt.xlim([aave_tvl.index[0], aave_tvl.index[-1]])
plt.title('\n' + str(aave_tvl.defi_protocol.iloc[0]).upper() + '\n Total Value Locked \n',fontdict={'fontsize':19.5})
ax.set_xlabel("")
ax.set_ylabel("Total Value Locked (USD) \n",fontdict={'fontsize':13.5})
ax.yaxis.set_ticks(plt.gca().get_yticks())
ax.get_legend().remove()
plt.gca().set_yticklabels(['${:,.2f}B'.format(x/1000000000) for x in plt.gca().get_yticks()]);