Skip to main content

Analyzing on-chain data using Covalent API + Python

TL;DR

This cookbook will go through how to extract and analyze on-chain data of Astar Network using Python and Covalent API. This cookbook is especially useful for non-devs who are not familiar with setting up indexers to query on-chain data. All steps can be done totally free without having to use a terminal or setting up a local development environment.

What is Covalent

Covalent leverages big-data technologies to create meaning from hundreds of billions of data points, delivering actionable insights to investors and allowing developers to allocate resources to higher-utility goals within their organization. Instead of pain-stakingly sourcing data from a small handful of chains, Covalent aggregates information from across dozens of sources including nodes, chains and data feeds. The Covalent API then sources end users with individualized data by wallet, including current and historical investment performance across all types of digital assets. Most importantly, Covalent returns this data in a rapid and consistent manner, incorporating all relevant data within one API interface.

Analyzing ArthSwap pool balance

As an example in this cookbook, we will analyze the change in the balance of ceUSDC/ceUSDT pool on ArthSwap. We will be using Python in this cookbook. For non-devs who are not familiar to setting up local environment to run Python, we recommend using Jupyter Notebook.

Make sure to sign up for Covalent to get the API key needed to run the code. (You can register here)

Step1: Extract data

Before we do any data transformation and analytics, we need a list of historical portfolio data of ceUSDC/ceUSDT pool contract as our first step. To get the information, we need to send the following request (see the reference section in this cookbook for more info on API format):

GET /v1/{chain_id}/address/{address}/portfolio_v2/

In this request, parameter chain_id is the chain ID of the Blockchain being queried. In this cookbook, we will use chain_id = 593 (Astar Network) and contract address of ceUSDC/ceUSDT pool = 0xD72A602C714ae36D990dc835eA5F96Ef87657D5e as example. The following code uses Python to extract the data.

import requests

API_KEY = [YOUR_API_KEY]
base_url = 'https://api.covalenthq.com/v1'
blockchain_chain_id = '592'
address = "0xD72A602C714ae36D990dc835eA5F96Ef87657D5e"

def get_wallet_portfolio(chain_id, address):
endpoint = f'/{chain_id}/address/{address}/portfolio_v2/?key={API_KEY}'
url = base_url + endpoint
result = requests.get(url).json()
return(result)

portfolio_data = get_wallet_portfolio(blockchain_chain_id, address)
print(portfolio_data)

Below is a sample output:

{'data': {'address': '0xd72a602c714ae36d990dc835ea5f96ef87657d5e', 'updated_at': '2022-09-20T07:17:27.930341337Z', 'next_update_at': '2022-09-20T07:22:27.930341567Z', 'quote_currency': 'USD', 'chain_id': 592, 'items': [{'contract_decimals': 6, 'contract_name': 'USD Coin', 'contract_ticker_symbol': 'USDC', 'contract_address': '0x6a2d262d56735dba19dd70682b39f6be9a931d98', 'supports_erc': None, 'logo_url': '[https://logos.covalenthq.com/tokens/592/0x6a2d262d56735dba19dd70682b39f6be9a931d98.png](https://logos.covalenthq.com/tokens/592/0x6a2d262d56735dba19dd70682b39f6be9a931d98.png)', 'holdings': [{'timestamp': '2022-09-20T00:00:00Z', 'quote_rate': 0.9932833, 'open': {'balance': '391683183282', 'quote': 389052.34}, 'high': {'balance': '392123445379', 'quote': 389489.66}, 'low': {'balance': '316424219770', 'quote': 314298.88}, 'close': {'balance': '317469504720', 'quote': 315337.16}}, {'timestamp': '2022-09-19T00:00:00Z', 'quote_rate': 1.0022721, 'open': {'balance': '391991979278', 'quote': 392882.62}, 'high': {'balance': '392739045673', 'quote': 393631.4}, 'low': {'balance': '389667428685', 'quote': 390552.8}, 'close': {'balance': '391683183282', 'quote': 392573.16}}, ...

Step2: Transform the data into lists

After data extraction is done in step 1, we will transform that data into a few lists so it can be easily handed when using Pandas which is a data analytics library for Python. A few functions are created in the code below to transform the data into lists.

import requests
import json

API_KEY = 'ckey_76799bb987a14e179ea6031d15c'
base_url = 'https://api.covalenthq.com/v1'
blockchain_chain_id = '592'
address = "0xD72A602C714ae36D990dc835eA5F96Ef87657D5e"

def get_wallet_portfolio(chain_id, address):
endpoint = f'/{chain_id}/address/{address}/portfolio_v2/?key={API_KEY}'
url = base_url + endpoint
result = requests.get(url).json()
return(result)

def get_timestamp_list(sample_data):
timestamp = []
for tmp in reversed(sample_data):
timestamp.append(tmp["timestamp"][5:10])
return (timestamp)

def get_token_balance_list(data):
token_balance_list = []
for tmp_data in reversed(data):
balance = tmp_data["open"]["balance"]
token_balance_list.append(int(balance) // 1000000)
return (token_balance_list)

portfolio_data = get_wallet_portfolio(blockchain_chain_id, address)
timestamp_list = get_timestamp_list(portfolio_data["data"]["items"][0]["holdings"])
usdc_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][0]["holdings"])
usdt_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][1]["holdings"])
print(timestamp_list)
print(usdc_token_balance_list)
print(usdt_token_balance_list)

The output will look as follows. The first list is a list of timestamps, second is liquidity of USDC (in USD), and the third is liquidity of USDT (in USD) in each day.

['08-21', '08-22', '08-23', '08-24', '08-25', '08-26', '08-27', '08-28', '08-29', '08-30', '08-31', '09-01', '09-02', '09-03', '09-04', '09-05', '09-06', '09-07', '09-08', '09-09', '09-10', '09-11', '09-12', '09-13', '09-14', '09-15', '09-16', '09-17', '09-18', '09-19', '09-20']
[317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469, 317469]
[317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368, 317368]

Step3: Transform the data to Pandas **Dataframe**

Using the lists created in step2, we will transform them into Pandas Dataframe so that they can be turned into a graph in the next step.

import pandas as pd
import requests
import json

API_KEY = 'ckey_76799bb987a14e179ea6031d15c'
base_url = 'https://api.covalenthq.com/v1'
blockchain_chain_id = '592'
address = "0xD72A602C714ae36D990dc835eA5F96Ef87657D5e"

def get_wallet_portfolio(chain_id, address):
endpoint = f'/{chain_id}/address/{address}/portfolio_v2/?key={API_KEY}'
url = base_url + endpoint
result = requests.get(url).json()
return(result)

def get_timestamp_list(sample_data):
timestamp = []
for tmp in reversed(sample_data):
timestamp.append(tmp["timestamp"][5:10])
return (timestamp)

def get_token_balance_list(data):
token_balance_list = []
for tmp_data in reversed(data):
balance = tmp_data["open"]["balance"]
token_balance_list.append(int(balance) // 1000000)
return (token_balance_list)

portfolio_data = get_wallet_portfolio(blockchain_chain_id, address)
timestamp_list = get_timestamp_list(portfolio_data["data"]["items"][0]["holdings"])
usdc_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][0]["holdings"])
usdt_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][1]["holdings"])

lp_df = pd.DataFrame(data = [usdc_token_balance_list, usdt_token_balance_list], index = ["USDC", "USDT"], columns = timestamp_list)
print(lp_df.T)

The output will look as follows and you can see that the lists have turned into a dataframe.

       USDC    USDT
08-21 446081 451625
08-22 453840 459288
08-23 455964 461331
08-24 455846 461451
08-25 456262 461089
08-26 455285 461550
08-27 457687 463863
08-28 456071 462506
08-29 460596 465996
08-30 449226 454343
08-31 429668 435999
09-01 430336 435230
09-02 331040 335945
09-03 321951 327345
09-04 221460 227266
09-05 226810 231804
09-06 237230 242222
09-07 302571 308771
09-08 293992 299795
09-09 292354 297289
09-10 292838 297973
09-11 296315 301463
09-12 296068 301855
09-13 296641 301435
09-14 408155 413254
09-15 289567 294152
09-16 393641 398622
09-17 391511 395897
09-18 392412 396156
09-19 391991 396653
09-20 391683 392573

Step4: Visualizing the data

As the final step, we will visualize the liquidity of USDC and USDT of the pool for each day using the dataframe from the previous step.

%matplotlib inline
import pandas as pd
import matplotlib as mpl
import matplotlib.pyplot as plt
import requests
import json

API_KEY = 'ckey_76799bb987a14e179ea6031d15c'
base_url = 'https://api.covalenthq.com/v1'
blockchain_chain_id = '592'
address = "0xD72A602C714ae36D990dc835eA5F96Ef87657D5e"

def get_wallet_portfolio(chain_id, address):
endpoint = f'/{chain_id}/address/{address}/portfolio_v2/?key={API_KEY}'
url = base_url + endpoint
result = requests.get(url).json()
return(result)

def get_timestamp_list(sample_data):
timestamp = []
for tmp in reversed(sample_data):
timestamp.append(tmp["timestamp"][5:10])
return (timestamp)

def get_token_balance_list(data):
token_balance_list = []
for tmp_data in reversed(data):
balance = tmp_data["open"]["balance"]
token_balance_list.append(int(balance) // 1000000)
return (token_balance_list)

portfolio_data = get_wallet_portfolio(blockchain_chain_id, address)
timestamp_list = get_timestamp_list(portfolio_data["data"]["items"][0]["holdings"])
usdc_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][0]["holdings"])
usdt_token_balance_list = get_token_balance_list(portfolio_data["data"]["items"][1]["holdings"])

lp_df = pd.DataFrame(data = [usdc_token_balance_list, usdt_token_balance_list], index = ["USDC", "USDT"], columns = timestamp_list)
lp_df.T.plot()

The output will look as follows:

1

After all the steps, we were able to visualize the historical balance of ceUSDC/ceUSDT pool on ArthSwap using Covalent and Python. From the graph you can see the liquidity for both USDT and USDC on 9/20 is $400K.

This is just a simple example, but there is a lot of API opened by Covalent and endless ways to utilize those data to come out with useful insight.

Reference