Dune is probably the strongest blockchain data analytics tool currently available to the public, and what's best is: it's free! With Dune you get near real-time access to blockchain data through a public database that you can query through Dune's website using SQL.

That's a lot of power.

Dune decodes blockchain data before adding it to their database, meaning that you don't have to figure out bytecode communication on your own. Instead, you use Dune's dataset explorer to browse datasets, specific smart contracts, events, or calls!

With Dune's recent announcement of their V2 Engine, which increases performance by 10x, it's about time you learn how to use Dune.

In this guide, you will learn:

• Part 1: The Dune interface

• Part 2: Build your own queries and charts with SQL – starting from the very basics

• Part 3: Organizing it all into a dashboard

In this step-by-step guide we will build the queries of below dashboard for the Pooly NFT series:

Let's get started!

Contents

• Part 1: Overview of Dune and Functionality

  • Dashboards

  • Queries

  • Forking

  • Query Editor

  • Dataset Explorer & Data Categories

  • Saving Your Forked Query

• Part 2: Building Your First Query

  • Deciding Which Queries to Build

  • Finding the Right Information

  • Getting Ready to Build Your First Query in Dune

    • Query 1: Funds Raised in ETH

    • Query 2: Funds raised in USD

    • Query 2a: Funds raised in USD at Current ETH Value

    • Query 2b: Funds raised in USD at ETH Value at Time of Purchase

    • Query 3: Total Number of Supporters

    • Query 4a: Leaderboard using the erc721. Abstraction

    • Query 4b: Leaderboard using the poolysupporter. Decoded Table

    • Query 5: Max Supply and Remaining Supply of Each of the NFT sets

    • Query 6: Timeseries Chart of ETH Raised Over Time

• Part 3: Cleaning up the Dashboard

• Closing

Part 1: Overview of Dune and Functionality

When you first open the Dune website at Dune.com, you are greeted with below window. This window has a view changer at the top that lets you cycle through dashboards, queries and wizards, and then the detailed view area in which you can see the list of dashboards, queries and wizards (users) on the left as well as some search-related settings on the right.

Dashboards

Dashboards are a collection of queries that are arranged as a series of charts, counters, and other information to give the user context about a specific area of interest. Below, I have opened an Ethereum dashboard by the legendary @hildobby. Here we can see all sorts of data that is being pulled from Dune’s database, shown as sums and timeseries charts.

In Dune, every dashboard is public. That means everything you build or other people build can be viewed and forked (i.e., copied) by anybody! This severely decreases dashboard creation time and lets you learn from other users' queries.

Queries

If you recall, I mentioned that dashboards are a collection of queries. If you click on the title of any one of the dashboard elements, you'll be taken to that chart's SQL query:

Here in Here we can see two primary on-screen elements: the query (top; black box) and the output chart (bottom). That’s right: no matter which block or chart you click on, you can see how the user created that chart!

Forking

If you want to save an entire dashboard or only the chart’s query to your own account, you can click “Fork” on the top right, and everything on the forked screen will be duplicated to a new window, in which you can make edits before saving the views to your account.

Let's fork the Ethereum Price chart! Once you press "Fork" on a query, you'll be taken to the query editor with the previous code already copied in!

Query Editor

Let me introduce you to the various on-screen elements here:

1. Query location & name – name can be changed once you hit save!

2. Dataset explorer – search for a specific dataset

3. Query window – input your SQL queries here

4. Visualization selector – choose whether to see query results, the forked line chart or create a new visualization

5. Run – run the query that is in the query window

6. Results/Visualization – see query results or your visualization created with the query results

7. Save – save your (forked) query!

Dataset Explorer & Data Categories

Let's take a closer look at the dataset explorer. There are six functional areas within the dataset explorer:

1. Chain selection

2. Dataset search

3. Browse raw blockchain data

4. Browse data of decoded contracts

5. Browse abstractions

6. Browse community-supplied data

Dataset Selection

In the dataset selection you can choose which chain you want to parse. Selecting "Dune Engine V2 (Beta)" let's you use Dune's latest enhancements which include multichain queries and 10x performance increases.

If you choose another chain, you the category selection (items 3-6 in previous image) will disappear and instead you'll see a list of contract calls and events that you can interact with.

Search

In the search field you can input your search parameter, and Dune will search through all tables that include that keyword in some form.

Note: The Dune Engine V2 and the old search function return results in a different fashion. The old search returns a list of all results, while Dune Engine V2 returns a nested list of results. We will be using the V2 engine!

Raw Blockchain Data

If you click into raw blockchain data, you can easily find queries for the various blockchains that Dune supports in a nested data structure where you can first pick your raw table, and from there pick the specific table columns you want to investigate further. Within each level of nesting you are also given the option to filter for specific search results you are looking for.

This is a very quick and handy way to get high level blockchain data.

Decoded Projects

In here you will find projects that have been decoded by Dune. Decoded projects are projects that the Dune team took apart, labelled, and put into tables so that users have an easy and standardized reference for certain data.

You will notice that again, the search results are nested. At the highest level, we have projects that you can search through, at a lower level you can filter specific smart contracts within that project, and finally we get to the various tables generated from that smart contract. If you click on any of the tables, you will see a list of columns, just like in with the raw blockchain data.

Abstractions

Abstractions can be thought of as custom tables that connect and combine various queries and pieces of data to form a unique table. Abstractions help users to query specific data they are looking for more easily, without the trouble of manually combining various pieces of data.

Generally, abstractions can be split into two main categories:

• Sector abstractions: sector-specific data

• Project abstractions project-specific data

From the abstractions sub-menu, we can see a list of abstractions with labels that specify whether the abstraction is sector-specific or project specific.

Community

The community section can be thought of as an extension of the abstraction section, but with data aggregations being supplied by Dune community members.

You may wonder why there is only one entry in the community section ("flashbots") – well, that's because Dune Engine V2 was just released! Over time, we can expect to see more and more community-built datasets by trusted community members.

Dataset Explorer Labels

In below illustration you can see a summary of how data is summarized within Dune as of launch of Dune Engine V2: the four main data categories are raw blockchain data, decoded projects, abstractions, and community, which hold the data of various blockchains in table format that can hold various data types.

Saving Your Forked Query

Let's go ahead and save this query first. After you hit save, a few things will happen. First, you will be asked to give your query a name.

Once you've chose a name, you will notice (1) the query location and name have been updated to your chosen name and (2) your query is running. That means Dune is fetching the latest data from their database, which is periodically updated with the latest data from various blockchains.

Once the query is finished running, you'll see your query results (3).

From here, if you click any of (1) "Query results", "Line Chart" or "New visualization", the (2) result/visualization box will update together with (3) settings for your selection that appear beneath it. Here you also have an "Add to dashboard" button, to quickly add your query results or visualization to new or existing dashboards – just like @hildobby's Ethereum dashboard from earlier!

If you click on (1) the circle in the top right and then on (2) “My queries”, you will open the queries list of your account.

The queries list includes all queries you’ve ever saved on your account. In below screenshot at the top we can see the latest query created:

Congratulations, you've forked and saved your first query with a visualization!

Forking is a Dune superpower, and it can help you easily and quickly create new queries by building on what other wizards (yes, you're a wizard now too!) have built before you. You can combine multiple forked queries to build your own dashboard!

Let's get our hands dirty and build a dashboard – a collection of queries and visualization – from scratch without forking. This will teach you where to find the right blockchain details to look for your specific project as well as teach you SQL basics.

Part 2: Building Your First Query

The purpose of this section is to teach you:

1. How to find the right information you need for your specific project

2. Some basic SQL

But first, we need to decide what we want our dashboard to be about. Pooly NFTs by the Pool Together DeFi protocol are a great first step.

If we search for "Pooly" on Dune, sure enough we can find some Pooly NFT trackers created by the community.

We could click into one the Pooly dashboard created by @0xbills and click on "Fork" and work from there…

However, if we build it from scratch we get to learn how to be a blockchain detective and learn some SQL as well! So instead, we'll build our own from scratch.

Deciding Which Queries to Build

First, let's decide what charts we want on our dashboard. Let's rebuild the views that Pooly built on their homepage! Taking a closer look at below two screenshots, we can see a few indicators based on on-chain data.

We can see:

• Funds raised vs funding target denominated in ETH

• Funds raised vs funding target denominated in USD

• Total number of supporters (unique addresses that purchased a Pooly)

• Leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order

• Max supply and remaining supply of each of the three NFT types

Awesome! But those are only a snapshot in time. Let's give ourselves another challenge as well:

• Timeseries chart of ETH raised over time

As it stands now, we can't build the views in the same way as the Pooly website, but we can capture the same amount of data (and more!) to build our dashboard.

Finding the Right Information

Before we get started on Dune, we need to find the right information. From the website we can tell that PoolTogether is selling three sets of NFTs:

• Supporter – 1 of 9 random collectible for 0.1 ETH

• Lawyer – Only one artwork for 1 ETH

• Judge – Only one artwork for 75 ETH

Is Pooly selling all three NFTs through one contract, or through three different contracts?

Let's head to Etherscan and see if we can find a smart contract related to Pooly. Once you open Etherscan.io type "Pooly" to see if the owner of these smart contract registered them with Etherscan.

Indeed! There are three smart contracts, likely corresponding to each of the three NFT collections. Furthermore, we now know that each Pooly is an ERC721 token.

Open each of the three collections and copy the smart contract address by clicking on the copy icon that appears upon hovering over the address. At the bottom of the page we can see all recent transactions as well, which will be helpful for troubleshooting later.

We will need these smart contract addresses to pull the right data from Dune, and they form the foundation of all of our queries:

0.1 ETH Pooly Supporter:

1.0 ETH Pooly Lawyer:

75 ETH Pooly Judge:

Getting Ready to Build Your First Query in Dune

First, navigate to dune.com and click "New Query" on the top right of the screen.

This will open the query editor and we can start working on our query!

Query 1: Funds Raised in ETH

First, in the top left switch from "7. Dune Engine V2 (Beta)" to "1. Ethereum". Pooly lives on Ethereum, thus we only need Ethereum data for this query. Plus, the tables in "1. Ethereum" are more mature than than Dune Engine V2, which only just entered beta.

For our first query, we will build a counter that shows funds raised denominated in ETH. To do this, copy below code into the query field in Dune and press "run" (or CTRL+Enter):

select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'

The above code is an SQL query that parses through Dune's database to fetch specific data that we requested. You can imagine Dune's database to be a collection of various tables, each containing specific information you may want to pull. With SQL, you…

• specify what data you want (which column in a table)

• whether you want to transform that data

• from which table you want to get the data

• whether you want to filter the data

To illustrate the above, let us run the above code piece by piece. Copy the below code into Dune's query editor and run it:

select * from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'

You will get a big table with a lot of information:

Now let's walk through the SQL code:

What this code says is "select all columns from the transactions table within the ethereum category where the value in the to column is \x3545192b340F50d77403DC0A64cf2b32F03d00A9" or in plain English: show me a table with all smart contract interactions with the Pooly2 (1 ETH) smart contract.

You don't need to run a query to see the columns within a table. The data explorer let's you explore the various table headers through their nifty search function:

We could have removed line 3 entirely to remove the filter, however, this would have returned a massive table and the query would have taken a long time to complete. The more precise your queries, the faster they will run!

Because we only want to return funds raised, we don't need all columns. So let's adjust our code to only grab the "value" column:

select "value" from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'

Now we only have one “value” column instead of the many we saw before:

You may notice, however, that the values seem to be quite large. That's because they are denominated in Wei and not ETH! To fix that, we can simply apply an arithmetic operator to the "value" column:

select "value"/1e18 from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'

That looks better! 1e18 in SQL is the same as 10^18, and we are simply telling Dune to divide the number by 1,000,000,000,000,000,000 so that instead of Wei, we see the values denominated in ETH.

And since we only want the total value and not a list of value, we can wrap the "value"/1e18 in a SUM() statement:

select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'

Awesome, we can now see the total number of ETH spent on Pooly2! And since we want to get the total spent on all three Pooly NFT smart contracts, we need to add two more lines to include details about the other smart contracts:

select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'

The "or" command works in tandem with the "where" command and specifies that when filtering the values in the "to" column, that the row should be considered if the first value or the second value or the third value are found.

We now see that in total 773.7 ETH has been spent on all three Pooly contracts. Awesome! Let's go to the Pooly website to see if that's right:

On the Pooly Website we see 776.5 ETH - congrats on achieving the target! But, oh no, there's a 2.8 ETH difference! Well – that's nothing to worry about. Dune syncs blockchain data in regular intervals. And because the dataset they sync to their database is so massive, it takes some time. We can expect the data to be refreshed in the next hour or two.

Now that our query is complete, we need to set up a counter to display this on our dashboard later. Below the Query results box, click on new visualization, and in the drop down menu that appears click on "counter".

And finally, click on "Add visualization":

A counter will appear and if you scroll down, you'll see various settings show up. Simply adjust the settings to your liking.

Once done, click on (1) "Add to dashboard" and select (2) "New dashboard". Then (3) give your dashboard a name and (4) click "Save dashboard". Your new dashboard will appear in your dashboards list. From here click (5) "add" on the dashboard you wish to add your visualization to. Once added, the label will change from "add" to "added".

If you click on your dashboard's name in this sub-menu ("Pooly NFT by 0xPhillan"), you will be taken to a dashboard that has our tracker displayed.

Great!

We'll get back to editing our dashboard once we have completed setting up all queries.

Query 2: Funds raised in USD

There are two ways that we can approach this:

1. Use the current value of the funds in USD used to purchase the NFTs

2. Use the USD value of the funds at the time of purchase

If we look at the smart contracts on Etherscan, we can see that a large portion of the 776.5 ETH have already been moved out of the smart contracts, leaving 299.2 ETH in the Pooly NFT smart contracts as of time of writing.

If we look at the Pooly website screenshot from earlier 776.5 ETH is valued at $1,411,249 USD ($1,817 USD/ETH), hinting that possibly the Pooly smart contract owner is keeping the funds as ETH instead of trading for USD.

Ultimately, it's difficult to say which approach Pooly is taking, but both approaches to USD value are interesting:

1. The current value tells us what the funds are worth now

2. The value at time of purchase tells us the intended USD donation amount by purchasers

So… let's create both!

Query 2a: Funds raised in USD at Current ETH Value

For this one, we'll use our previous code as a base and slot in some additional lines to get the current USD value.

First, fork the query we just created:

Then adjust your code to look as follows:

select SUM("value"/1e18) * (
        SELECT "price" FROM prices.usd
        WHERE "symbol" = 'WETH' 
        AND "minute" < now() - interval '1 hours'
        ORDER BY "minute" DESC 
        LIMIT 1
    )

from ethereum.transactions

where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'

You'll notice that we have added a multiplication operator * and a large code block right after our SUM("value"/1e18) command.

In Dune, you can highlight specific portions of your query and only run that portion by clicking "Run selection". Let's (1) highlight only the lines within the parentheses and (2) run that selection:

In the query results you'll see the latest USD price of WETH! Our addition here multiplies the latest price of WETH with the amount of ETH raised to give us the USD value.

Let's break down this code block:

1. From the prices.usd table select the "price" column

2. Filter the symbol column for "WETH"

3. Only look at time entries of the past 1 hour (this will significantly speed up the query)

4. Order in descending order (newest first)

5. Limit the query to one result (the first result, i.e., latest price)

To better understand this block of code, let's make some small adjustments to our query. (1) replace "price" with * (which returns all columns) and (2) select only the code from lines 2 to 5, then (3) run the selection:

In the query results you will see the full table consisting of five columns. First, let's check the contract address in Etherscan.io:

This smart contract controls the WETH asset on the Ethereum blockchain. Great! Now we know the source of the table from our previous screenshot that shows the WETH price in USD.

Let's divert our attention back to the previous table:

In here we have a column called "minute" which tracks the ETH to USD value every minute. Since we limited the query to "interval – '1 hours'", we only get the latest hour of data available. Since for our purposes we actually only need the latest data entry, limiting this query to the last hour speeds up the query significantly. You could also change this to '1 days', '3days' or '1 weeks' for example, to get more historical data.

What's important here is that the columns name is "minute", hence our query refers to the "minute" column, which is not to be mistaken for a time-related command.

Let's revert our code back to what we changed it to at the beginning of this section and run the query:

The result is the current USD of the ETH that has been transferred to the Pooly1, Pooly2 and Pooly3 smart contracts in exchange for Pooly NFTs.

For this we will be using a counter again, so scroll down and (1) click on the counter forked from our previous query, (2) adjust the data source and (3) change the labels.

Once finished, remember to save and add to our dashboard:

After it's added, it will look like the below screenshot. Don't worry, at the end of this guide we'll clean it up. For now, don't worry about the looks!

Query 2b: Funds raised in USD at ETH Value at Time of Purchase

This query will be a little more complex, as we will have to query two tables and combine the results. Specifically, we will have to get the individual transactions and convert the ETH value of each transaction using the price of ETH at the time of the transaction.

Again, let's first fork our previous query in preparation for our next query:

From the forked code, we want to make the following:

with poolyTransactions as
(
select
    block_time,
    value/1e18 as value_eth

from ethereum.transactions

where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)

select 
sum(value_eth * price)
from poolyTransactions tx

left join 
    (select minute, price from prices.usd 
              where symbol = 'WETH' and minute > '2022-05-01') 
              as prices on date_trunc('minute', block_time) = minute

If we run this code here, we see that we receive a USD value of 1,4m USD. This is the result of

Let's break this code down into three sections:

Section 1

Here we build the first table that we will reference. What we do here is create an auxiliary table that we call "poolyTransactions" that will hold block_time and value_eth (value in wei divided by 10^18 to convert to ETH, which we give a custom name) from the ethereum.transactions table. For this table, we filter the three Pooly addresses that we know of.

Here a line by line explanation:

• Line 1: with poolyTransaction as – define auxiliary table called "poolyTransaction" as having below properties

• Line 3-11: select columns and filters to include on the ethereum.transcations table

• Line 5: value/1e18 as value_eth – here we rename the column as "value_eth" so that we can reference it directly in section 2 instead of doing another calculation

Section 2

This is where we create the output table. You'll notice that we are building a table from poolyTransactions, the auxiliary table we created in section 1, but we are also referencing a column called "price" that we didn't define yet. Price is in fact only defined later in line 19! This is made possible, because we are joining the poolyTransactions with certain outputs from the prices.usd table in section 3. So in essence, we are creating a table using our auxiliary table poolyTransactions as well as the table we built from prices.usd in the next section.

Section 3

This is where we define a table that we want to join with another table. The "left join" keyword allows us to do this:

• Line 18: left join – keyword used to indicate that we want to join our first table (left table) with another table (right table). That means, our first table defined in section 1 acts as the base table.

• Line 19-20: here we define the table we want to create from prices.usd. In line 20 we limit the duration to '2022-05-01', because the Pooly smart contracts were only deployed in the month of May, so we can significantly speed up the table query if we limit it to a smaller timeframe instead of parsing data all the way to Ethereum's genesis block.

• Line 21: as prices – this names our table in lines 19-20 "prices" for easier future reference

• Line 22: on data_trunc('minute', block_time) = minute – this is the line that combines our auxiliary table (section 1) with the prices table (section 3 lines 19-20). What it says here is to take column "block_time" from our auxiliary table, and truncate it to minutes only, i.e., remove all other data that is not minutes (e.g. seconds, milliseconds, etc.). The prices.usd table is already truncated to minutes, so there is no further conversion needed here. Then the minute column in prices.usd is matched to the minute column in our auxiliary table, and the correct price is thus allocated from the prices.usd to the corresponding minute timestamp in poolyTransactions.

To better visualize the third section, I have reorganized the parts to make it easier to understand:

(1) We create the poolyTransactions table, then (2) we tell SQL to join it with another table, which (3) we define as minute and price columns from the prices.usd table. This prices.usd table we created is then (4) joined onto the left table poolyTransactions using the time in minutes as a mapping variable. To join tables, both tables must have entries which are exactly the same and if we truncate the block_time variable to minutes, we create matching minutes between both tables. By doing this, the (5) poolyTransactions table is updated to include the price column, with price values being matched to the respective dates.

From here, we simply query the joined poolyTransactions table and sum the result of multiplying the value_eth and price of ETH for each row.

Now add a counter, save and add to dashboard!

Query 3: Total Number of Supporters

For our next query, we want to count the unique addresses that purchased a Pooly NFT (i.e., total number of supports). That means that even if an address purchased multiple Poolys across all three Pooly types, they should only be counted once.

For this, let's first open our very first query, fork it, and remember to save it too.

Here we simply change the first line:

select COUNT(DISTINCT "from") from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'

The COUNT variable counts all transactions, and the DISTINCT keyword makes sure that every unique entry is only counted once. The result we get is 4660 unique supporters. If we compare that to the unique supporters on the Pooly website, we see they are very close:

This shows that our query is correct, as there is a little lag between Dune's database refreshes and the latest blockchain state.

Finally, change the counter visualization and add to dashboard again.

Query 4a: Leaderboard using the erc721. Abstraction

Next, let's build the leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order.

Again, let's fork our previous query so that we avoid having to retype the filtered addresses. Remember to save this new query before proceeding.

Looking at the leaderboard, there are three pieces of information we need. First, the address of the purchaser, then the number of NFTs purchased, and finally, the amount of ETH spent on all NFTs purchased.

Here we are looking at the NFTs purchased not at the NFTs held. It is entirely possible that somebody purchased (minted) an NFT then moved it to a safe wallet or resold it at a later point. We are only interested in first-time purchases (mints).

We achieve this using below query:

with poolyTransactions as
(
select
    "from",
    hash,
    value/1e18 as value_eth

from ethereum.transactions

where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)

select 
"from", nfts_purchased, value_eth
from poolyTransactions


left join 
    (Select evt_tx_hash, COUNT("tokenId") as nfts_purchased
        From erc721."ERC721_evt_Transfer"
        Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
        or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
        or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
        and "from" = '\x0000000000000000000000000000000000000000'
        group by 1)
        as nfts
        on evt_tx_hash = hash
ORDER BY 3 desc

You will notice that this heavily resembles the query in "Funds raised in USD at ETH value at time of purchase", and that's because we are using the same methodology: we first gather the transaction data in our poolyTransactions table, then we left a second table on it with a common mapping value.

Here, for the second table we use the erc721."ERC721_evt_Transfer" table, which is an abstraction that Dune maintains that tracks all NFT transfers on Ethereum. If we use the dataset explorer, type "erc721." And scroll to "ERC721_evt_Transfer" we can see everything that is included in that specific table. We can also highlight only the commands for the second table and see what the output would be:

Since we only want NFTs that were newly minted by the smart contract, we must specify the "from" address to be the null address. On Ethereum, all NFTs are minted from the null address. By counting the amount of "tokenId" for each transaction, we can count the total number of NFTs minted for each transaction.

You will also notice something peculiar about how the filters are defined. The first three filters are now enclosed in brackets, while the last filter is outside of the brackets.

The brackets determine the order in which the and/or filter commands are evaluated, just like when executing arithmetic commands in SQL. If we did not enclose the first three statements, the and condition would only apply to the last filter setting.

Since we want the from null address filter to be applied to all results of the previous filters, we need to add the parentheses.

Finally, since we are using the "COUNT" command, we need to specify what column to count these on (i.e., what variable to roll-up the counts to). For this we use the "group by" command to say that we want to group the count of "tokenId" to the first column in our table, which is "evt_tx_hash".

Earlier I mentioned that we need a common mapping value to map this second table to the transactions table. Here we use the transaction hash to map the number of NFTs purchased per transactions, to our poolyTransactions table where this time we also requested the transaction hash. So ultimately, we are mapping the transaction hash of our erc721."ERC721_evt_Transfer" table (which we named "nfts") to the transaction hashes of our poolyTransactions table, which only include transactions that were used to purchase poolys.

The output is a table that includes the purchasers address, the number of NFTs that purchased purchased in total, as well as the total value of ETH spent.

Finally, we tell Dune to "ORDER BY 3 desc", which means that the third column of our output table should be ordered in descending order:

Awesome! Our leaderboard is complete. Let's compare it against the leaderboard on the Pooly NFT website:

Not all figures tie, but from this list we can see that a few addresses, NFTs purchased and total ETH spent figures do tie. This is once again a sync timing issue between Dune and real-time blockchain data and nothing to worry about.

Remember to save your query and add it to the dashboard.

Query 4b: Leaderboard using the poolysupporter. Decoded Table

Instead of using the erc721."ERC721_evt_Transfer" table, we can also use the poolysupporter."PoolyNFT_call_mintNFT" decoded table that the Dune team put together.

with poolyTransactions as
(
select
    "from",
    hash,
    value/1e18 as value_eth

from ethereum.transactions

where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)

select 
"from", nfts_purchased, value_eth
from poolyTransactions


left join 
    (Select call_tx_hash, "_numberOfTokens" as nfts_purchased
        From poolysupporters."PoolyNFT_call_mintNFT"
        where contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
        or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
        or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
        )
        as nfts
        on call_tx_hash = hash

ORDER BY 3 desc

The methodology is the same as above, except that with this table we can directly return all transaction hashes that called the mintNFT function, instead of using the null address to determine which transactions from the erc721. "ERC721_evt_Transfer" table are mint transactions.

The poolysupporter dataset allows us to do more specific and detailed queries, as we can refer to specific contract calls.

Let's compare the results of our two tables to ensure nothing is amiss:

Won't you look at that, the outputs are identical. Great!

Remember to save your query and add it to the dashboard.

Query 5: Max Supply and Remaining Supply of Each of the NFT sets

In the alternative version of query 4 we used the poolysupporter function. You may have seen that when you search for pooly in the dataset explorer, you also see a function called "PoolyNFT_call_maxNFT".

You may conclude that you can use this function call to directly retrieve the maximum minted NFTs.

Unfortunately, that is not possible: this function is a "read" function and as a result, there is no on-chain record when this function is called. See Etherscan below:

The maxNFT variable is set when the deployer contract deploys the Pooly Supporter smart contracts, but unfortunately at time of writing, the deployer smart contract has not been decoded, so we can't get the max mint numbers from on-chain data – at least not without significant effort.

Instead, we'll have to manually slot in the maxNFT figures for each smart contract:

with poolyContracts as
(
Select  contract_address,
        COUNT("tokenId") as nfts_purchased
            From erc721."ERC721_evt_Transfer"
            Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
            or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
            or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
            and "from" = '\x0000000000000000000000000000000000000000'
            group by 1
)

select 
    CASE contract_address
        WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 'Pooly_Supporter'
        WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 'Pooly_Lawyer'
        WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 'Pooly_Judge'
        END as NFT_name, 
    nfts_purchased, 
    CASE maxNFT_Supply
        WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 10000
        WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100
        WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 10
        END as NFT_Supply,
    CASE maxNFT_Supply
        WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 100-(nfts_purchased/10000.0*100)
        WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100-(nfts_purchased/1000.0*100)
        WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 100-(nfts_purchased/10.0*100)
        END as percent_supply_remaining
from poolyContracts

left join
    (
    Select  contract_address as maxNFT_Supply
            From erc721."ERC721_evt_Transfer"
            Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
            or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
            or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
            and "from" = '\x0000000000000000000000000000000000000000'
            group by 1
    )
    as maxNFT
    on maxNFT_Supply = contract_address
    
ORDER BY 3 desc

Here is where I had to be a little creative. Manually adding numbers to specific table entries in SQL is a difficult undertaking, and I had to apply some tricks to get this to leave a table that is easily readable.

Here again, we will use left join to combine two tables, but we will also used CASE WHEN statements on three of four columns to output specific information that we want to show. What we will do is first create our base table, then create a second table, and left join the second table with the first table and transform the table inputs to make them human-readable as well as do some simple arithmetic for us.

The reason for the second table is that within a single query SQL does not let you call a column twice. We, in fact, need to call one column multiple times and transform each column call separately. A joined table however, allows us to call columns from the second table multiple times, thus enabling us to create the outputs we need for the specific rows in the column that we need.

Let's break this query down into four sections for easier digestion.

TAKE NOTE OF THE SECTION ORDER! 1, 3, 2, 4!

Section 1

In this section we define a table called "poolyContracts" in which we count all individual tokenIds that originated from the null address from the three Pooly contract addresses, thus including only minted NFTs by using the erc721."ERC721_evt_Transfer" table. We then group these by the first column, thus returning minted NFTs per pooly smart contract.

Section 2

In this code block we force the query to only show one of each of the three contract addresses. We do this by using the "group by 1" command, i.e., group the results by unique entries of the first column.

Without the group by command the query would return all transfer events in relation to these contract addresses, but we only need each to appear once. You'll see why in the next section.

Furthermore, we rename the contract_address column to maxNFT_Supply so that we can define which column to join this table onto the poolyContracts table with.

Section 3

This is where the magic happens.

In this section we can now call columns from our joined table. We call:

1. contract_address

2. nfts_purchased

3. maxNFT_Supply

4. maxNFT_Supply

You'll notice that we are essentially retrieving the same data three times with columns 1, 3 and 4, and that columns 3 and 4 are even the same identical column! This is possible because we joined two tables. If you were to call contract_address twice before the tables were joined, the query editor would return an error message.

Next you will also notice that columns 1, 3 and 4 each have a CASE WHEN clause embedded. Because each of the two previous tables that we created only have one unique line for each smart contract, we cannot use a CASE WHEN statement to specify if a specific smart contract address appears (one of three options), return something else in its place.

You'll see here for the first column we tell the query editor to replace each smart contract address with the name of the respective NFT!

In the third column we replace it with the known max NFT quantities listed on the Pooly website.

And in the fourth column we use a formula to calculate percent of remaining NFT supply. In these statements at least one of the numbers used in the arithmetic operation needs to include one decimal. If this is not included, the SQL query will be interpreted as wanting to return integers, meaning that we will not get any decimals for these calculations. By including “.0” we signify to the server that we want this calculation to return a decimal number.

Section 4

And finally, we indicate that we want the output to be ordered in descending order (largest to smallest) by the third column.

This table is done as well. Save your query, make any changes you need to make to the table and add it to your dashboard.

Query 6: Timeseries Chart of ETH Raised Over Time

In our final query, we will create a timeseries chart of the amount of ETH raised through NFT sales over time.

select 
    block_time as time,
    sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
from ethereum.transactions

where ("to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and date_trunc('day', block_time) < '2022-06-25’

This is a shorter piece of code, however it includes the over command, which is an important command for aggregating cumulative values.

In this query we first select block_time, then we sum the ETH value (which is value/1e18) over the block_time in minute intervals, which we directly sort in ascending order, and name the column cumu_value_eth.

sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth

Furthermore, we've added another filter to the end, which states that for this query blocktime should not exceed 2022-06-25, which is roughly when the fundraising campaign ends according to the Pooly website. This way our area chart will only display data of the campaign, instead of adding a flat line that will drag into eternity as time goes by.

To create the area chart, (1) click on "New visualization", then in the (2) dropdown menu select "Area chart" and finally (3) click "Add visualization".

Your area chart should appear automatically with relevant settings pre-selected by Dune.

If they aren't pre-selected, you can play with the settings below the chart until it looks right.

Finally, save your query and press "Add to dashboard" once more.

Part 3: Cleaning up the Dashboard

We built a lot of queries, and directly added these to our dashboard. Well, let's take a look at how it looks like. Just click on the dashboard name after adding the last chart to your dashboard.

And, let's see…

Yikes! That definitely needs to be cleaned up before it's presentable.

In the top right of your dashboard screen, click "Edit" to start editing.

From here, you can drag and drop the individual elements on a grid that is displayed by a red box in the background while you move elements around, and you can resize each element by dragging the icon in the bottom left corner. Simple!

To add text and images to your dashboard, press "Add text widget" in the top right of the dashboard edit screen.

While cleaning up you might notice that these two queries look the same and having both doesn’t really provide any valuable info…:

Because we don't know when or how PoolTogether is withdrawing the ETH in the smart contract, we can just stick to how the Pooly website does it. We'll remove the right one and replace it with another query.

Here we go, the final dashboard:

That looks much nicer than before, and it also follows the same format of the Pooly website!

Closing

Dune Analytics is a strong platform that can provide deep blockchain data capabilities in the right hands. I hope with this piece I was able to teach you the basics. From here, it’s up to you to take on bigger challenges and make even better dashboards. Please share your dashboards with me - I’m excited to see what you build!

If you enjoyed this piece, please consider following me on Twitter @0xPhillan!

Don’t hesitate to reach out if you have any questions about Dune Analytics or ideas for future guides.

Special thanks to @superamscom for reviewing my code and informing me of a bug that has now been fixed!

In this piece I will teach you how to use Microsoft Excel to connect to APIs of blockchain data aggregation services to pull aggregate price, market cap, trade volume and TVL data. The skills learned here are transferable to any public API – be creative and show me what you make with it!

We will cover three APIs that will teach you the basics of using Excel Power Query for API calls:

• CoinGecko API*: basic Power Query API call*

• CoinMarketCap API*: basic Power Query API call with a personal API key*

• DeFi Llama API*: advanced Power Query API call with parameters and data transformation in Power Query*

Contents

• Part 1 Introduction

• Part 2: Getting API Access

• Part 3 CoinGecko – API & Querying in Excel

  • Part 3.1 CoinGecko – Finding the API Link

  • Part 3.2 Using Excel to Query the CoinGecko API

  • Part 3.3 Transforming the retrieved CoinGecko data

• Part 4: CoinMarketCap – API & Querying in Excel

  • Part 4.1 CoinMarketCap – Finding the API Link

  • Part 4.2 Using Excel to Query the CoinMarketCap API

  • Part 4.3 Transforming the retrieved CoinMarketCap data

• Part 5 DeFi Llama – API & Querying in Excel

  • Part 5.1 DeFi Llama – Finding the API Link

  • Part 5.2 Using Excel to Query the DeFi Llama API – /protocols

  • Part 5.3 Transforming the retrieved DeFi Llama data – /protocols

  • Part 5.4 Using Excel to Query the DeFi Llama API - /protocol/{protocol}

  • Part 5.5 Transforming the retrieved DeFi Llama data – /protocol/{protocol}

• Part 6 Other Notes

  • Part 6.1 Refreshing Data

  • Part 6.2 Important note on API call URLs

• Closing thoughts

Part 1 Introduction

When we look at the blockchain data that is available today, we can see massive amount of data in various forms from various sources at various levels of granularity. We can find data on prices, market caps, TVL, NFT sales and even aggregations of specific smart contract interactions – all the data is public, and the volume of data available is incomprehensible.

While having more granular data means you can do deeper analyses, you also face the challenge of having to transform the data into a human-readable format. This is an extremely challenging task, but thankfully we have data aggregation services that do the heavy lifting for us.

From above illustration you may feel that with aggregate data at token or smart contract level you will miss the big picture, but the truth is quite the opposite: with aggregate data you get to see the big picture and visualize trends without being distracted by every detail. If you start your data analysis journey with on-chain data, you'll miss the forest for the trees!

So for today, let's see how we can use the CoinGecko API, CoinMarketCap API and DeFi Llama API to fetch aggregate token and TVL data.

Part 2 Getting API Access

Before we get started, we need to make sure we have API access. Some APIs are accessible publicly, while others require sign-up and a personal API key. Today we will use below three public APIs:

• CoinGecko (free, no sign-up required | optional paid plans)

• CoinMarketCap (free, sign-up required | optional paid plans)

• DeFi Llama (free, no sign-up required)

CoinGecko and CoinMarketCap have mostly the same data available, however we will be using both APIs to demonstrate how to use Excel to pass your API key details when querying an API which requires you to register for an account.

Whenever working with any API, the API documentation is an extremely valuable document that explains what data can be accessed through which API calls. Below are the latest links to the API docs for our three data sources:

• https://www.coingecko.com/en/api/documentation

• https://coinmarketcap.com/api/documentation/v1/

• https://defillama.com/docs/api

Since CoinGecko and DeFi Llama APIs are free, we only need to sign up for CoinMarketCap. In below link click the "Get Your API Key Now" button, follow the steps on-screen to get access to your API key:

The World's Number 1 Cryptocurrency Market Data API

Use CoinMarketCap's crypto API to get the best, most accurate real-time, historical cryptocurrency, DEX and exchange trade data for Bitcoin, Ethereum and more.

https://coinmarketcap.com

Once you have completed the process, you can view and copy your API key by hovering over the "API Key" box in the CoinMarketCap developer backend. Your CoinMarketCap API key should look something like this:

8d441bdd-dccf-5a50-ac45-8a880cc52c1a

Note: This API key will not work and is for illustration purposes only. You must apply for a personal API key if you want to use the CoinMarketCap API.

Part 3 CoinGecko – API & Querying in Excel

We will start off with CoinGecko since it is the easiest API to use.

Every API will have a special link you need to use initiate an API query. In below section we will first look at how to read API documentations to find the right links, before moving on to Excel and Power Query to fetch and transform the data. Some APIs provide you with the API link directly, while others require you to look around a little bit.

Part 3.1 CoinGecko – Finding the API Link

First let's open the API documentation again:

https://www.coingecko.com/en/api/documentation

If you scroll down should see something like this:

CoinGecko does not directly share the API link with you, however we can easily find it by searching through their API commands. If you expand the GET ribbon under "ping", you will see an option to try out the ping command.

If you click "Try it out" and then "execute" you should see the following:

Here we can see the following important information:

• Request URL: https://api.coingecko.com/api/v3/ping – the link used to initiate the API call

• Server Response:

  • Response code: 200 – a code which signifies the server response was successful

  • Response body: "gecko_says": "(V3) To the Moon!" – the API query response

When you execute an API query call, all of the call parameters are included in the request URL. Or phrased differently: based on what is included in the link, the API knows which data or information to return. You can test this by pasting the request URL in your browser and you will receive the same response:

Let's do another API call, but this time using the /coins/markets call under the coins section. This will "List all supported coins price, market cap, volume, and market related data". Once you click "Try it out", you will see a long list of parameter names and input fields in which we can specify our query parameters.

Apart from the field marked "required" which we must fill out (I put "usd"), we can leave the other fields as per default. Click the "Execute" button and wait for the API call. Once completed, you will see the below:

In the response body we can now see a huge JSON response with a lot of data about the cryptocurrencies CoinGecko tracks. Awesome!

Next let's see how we can run this API call in Excel and pull the data into Excel.

Part 3.2 Using Excel to Query the CoinGecko API

From the server response from the previous step copy the request URL:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=100&page=1&sparkline=false

Then open a fresh instance of Excel and navigate to the data tab (1). Within the Data tab under “Get & Transform Data”, click on “From Web” (2):

Once you do this, the following pop-up window will appear. Paste the request URL into the box (1) and press "OK" (2):

If this is your first time connecting to the CoinGecko API, you will see a window like the below. Simply press connect.

Note: In below window I first connected to the ping API call, your link will be to /coins/markets.

You should see below screen as it connects.

Part 3.3 Transforming the retrieved CoinGecko data

Followed by the Excel Power Query editor automatically popping up, as seen below. The Power Query editor lets you further apply data transformations to your query.

The Power Query editor follows Excel's general design language with a top ribbon with commands, and settings to the side.

First, let us rename our Query to something more readable and then let us convert the JSON response we received from CoinGecko into a Table:

You will see below pop-up; press OK to proceed.

Once you press OK, the Power Query editor will refresh and you will see a few changes applied to it. First, the header ribbon becomes activated and the "List Tools & Transform" ribbon disappears (1). Then, your raw JSON data has now been transformed into a Power Query table. This is added to the "Applied Steps" list on the right side (2) and can be reviewed in the formula bar above the list entries (3). Finally, although the data has been turned into a table, it still doesn't give us any valuable information. Currently Excel is showing you the highest level of data from the JSON response, which is just a list of records (4). We need to go one level deeper to see the data, and to do this we need to expand the records of Column1.

To expand Column1, click on the two outward facing arrows (1) which will open a sub-menu with all the column headers of data available at the next level of depth. Simply press OK (2) to expand the column and refresh the table.

You will see another step has been added to your applied steps (1) and that the formula bar has also been updated with code reflecting the expand step (2). But most importantly, all the detailed data from our API call is now visible (3)!

From here you can decide to make more transformations to your data such as changing data types, removing columns you don't need or adding certain calculations. If you are happy with the data, as a next step press "Close & Load" (4). Since this is a new query, this will automatically load your Power Query table into a new Excel sheet as an Excel table.

The Power Query editor will close and you will be brought back to your Excel sheet. In the Queries and Connections panel and within the sheet itself you will see Excel attempting to fetch the data again. Simply wait up to a minute for the API call to fetch the data, and Power Query to apply the transformations.

Once complete, your data will appear in Excel and the Queries and Connections tab will let you know how many rows of data have been loaded.

That's it! From here on out you can use Excel to play with the data, transform the data, make charts, tables graphs or just browse the full extent of data you pulled from CoinGecko in table format. Congratulations!

Below is a simple charting example that only took a few seconds to produce with the data. Using a pivot table on the query result, we can easily create pivot charts that summarize the data in a visual manner.

You can follow these steps for other API links within CoinGecko's documentation to fetch other data.

Part 4: CoinMarketCap – API & Querying in Excel

While the process for CoinMarketCap will mostly follow that of CoinGecko, there is one key difference: CoinMarketCap requires an API key to access their API. If you followed the steps in Part 2, you should already have an API key at your disposal. KEEP YOUR API KEY PRIVATE AND SAFE. Your API key should not be shared with others, as in the future you may start paying for other API access, and anybody with your API key will be able to use your access.

Part 4.1 CoinMarketCap – Finding the API Link

Just like in part 3, let us open the API documentation for CoinMarketCap:

Here you can view all API calls. Let us, however, use an API call that requests similar data to the one we used earlier for CoinGecko, namely the "Listings Latest" call:

https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyListingsLatest

If you click the top right GET command box, you can see the API call request URL. Copy it:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest

The CoinMarketCap free API membership is the “Basic” API plan. Hence we can use all endpoints that support that plan.

Part 4.2 Using Excel to Query the CoinMarketCap API

This step will be slightly different to the previous step with CoinGecko, because we need to use an API key to access the data. If we look at the "Authentication" section of the CoinMarketCap API Documentation, we will find that CoinMarketCap explains that to connect to their services we must use a custom header and our API key. The custom header that CoinMarketCap recommends is:

X-CMC_PRO_API_KEY

With the custom header and API key at hand (log in to the CoinMarketCap Developers portal to find it, see Part 2 of this guide), let us navigate to the data tab (1) and under "Get & Transform Data", click on "From Web" (2) just like we did with CoinGecko:

This time when the “From Web“ pop-up appears, click on "Advanced" (1) and input the request URL in "URL parts" (2), then add the HTTP request header (3) as well as your API key (4). Once all is input, press OK (5).

URL:

https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest

HTTP request header:

X-CMC_PRO_API_KEY

API key:

Log in to your personal CoinMarketCap Developer account and fetch from the dashboard

If this is your first time connecting to the CoinMarketCap API, you will see a window like the below we saw before for CoinGecko. Simply press OK.

Note: The link at the top will show your request URL for CoinMarketCap instead of CoinGecko as below.

You should see below screen as it connects.

Part 4.3 Transforming the retrieved CoinMarketCap data

The PowerQuery editor will automatically pop-up.

You will notice that it looks different to the CoinGecko screen – instead of “List Tools”, we are given a “Record Tools” ribbon at the top. This is because the CoinMarketCap API returns both the data we requested, as well as a record of our API call request (“status”), which it uses to track how much data we have pulled. In the CoinMarketCap API, there is a daily limit of how much data can be requested and is measured in credits. If you look at the CoinMarketCap developer dashboard, you will see with every API call your credits used will increase.

The above API call used 5 credits:

To find the data we want from the Power Query editor, click on "List".

The result will be a table that looks similar to our previous table from CoinGecko. From here on out all steps are the same as with CoinGecko:

Press "To Table"

Then click OK on the pop-up:

In the resulting column, click the two outward facing arrows at the top of the column (1), then press OK (2).

As with CoinGecko, all CoinMarketCap data will be expanded. Again, click "Close & Load" to load this data as a table into Microsoft Excel.

You will see the Query running while Excel attempts to fetch the data.

Once the API query call is complete, you will see all data in table format in your Excel.

If you have been paying attention, you may have noticed that we did not rename our query this time. If you need to make any edits to your query, including any transformation steps within the query or the query name, simply double-click on the query on the right side to re-open the Power Query editor.

From here we can now change the name (1) and hit "Close & Load" (2) to save the changes to our query and reload it into our Excel.

The updated name will be reflected accordingly. If you make any other changes to your query, they will also be updated.

Part 5 DeFi Llama – API & Querying in Excel

Unlike the CoinMarketCap API, the DeFi Llama API is free. Hence, this process will much more closely resemble that of CoinGecko. However this time, we will run two Power Query API calls. We want to:

1. Get a full list of all protocols tracked by DeFi Llama

2. Get detailed timeseries data for a specific protocol tracked by DeFi Llama

Part 5.1 DeFi Llama – Finding the API Link

Again, let's open the API documentation. This time for DeFi Llama:

https://defillama.com/docs/api

In your browser you should see the below screenshot. Here we can see a list of various Total Value Locked (TVL) data retrieval API calls. If we use the first call command /protocols, we will get a table similar to that of CoinGecko and CoinMarketCap which lists all the protocols and related information. From there we can find which protocol we want more detailed information for, and then use the /protocol/{protocols} API call to retrieve that information.

In summary, the steps we must undertake are:

1. Get the protocol list using the /protocols API command

2. Within the data retrieved from the /protocols API command identify the "slug" (unique identifier) of the protocol that we want to retrieve our timeseries data for (this is a requirement listed in the /protocol/{protocols} API call)

3. Use the /protocol/{protocols} command to get the timeseries data for our selected protocol

Same as with CoinGecko, we can expand the /protocols API call command and try it out on the website directly. This will show us the request URL we need to use in Excel, as well as show us the format of the data.

The request URL we need is:

https://api.llama.fi/protocols

Just like with CoinGecko, we can observe the data returned. This can be helpful if you want to understand what data an API request will return. From the above screenshot, we can already see the slug for MakerDao: "makerdao". We could skip doing the first API call from Excel and go straight to the second API query. However, we want to have a full list of all query-able slugs so that we can change our query in the future if we want to. For this we must still do the first API call requesting the full protocol list.

Part 5.2 Using Excel to Query the DeFi Llama API – /protocols

Open Excel, and once again navigate to the data tab (1). Within the Data tab under "Get & Transform Data", click on "From Web" (2):

In the pop-up window, paste the retrieval URL https://api.llama.fi/protocols then press OK.

If you are asked to apply Access Web content settings, leave it with the default setting and press connect.

A connecting pop-up will appear indicating that Excel is attempting the API call.

Part 5.3 Transforming the retrieved DeFi Llama data – /protocols

Once the API call was successful, the Power Query editor will open automatically once again, resembling our first CoinGecko API call. Once again, let us click "To Table" to convert the retrieved data to a table format.

When the "To Table" pop-up appears, keep all settings at default and click OK.

Once complete, click the two outward facing arrows (1) then click OK (2) to expand the underlying columns.

The result will be the full expanded table of all protocols listed on Defi Llama. Click "Close & Load" to load the data as a table into a new sheet.

Excel will now work to retrieve the data from DeFi Llama.

Once retrieved, you will have a data table including information of protocols with key data that is listed on DeFi Llama.

From here we can navigate to the "slug" column to see all the slugs that DeFi Llama uses (press CTRL+F on Windows to search for the keyword "slug" to find it quickly).

Part 5.4 Using Excel to Query the DeFi Llama API - /protocol/{protocol}

Now that we have a full list of available slugs that we can refresh whenever we want, let us pick a slug. For this example I have picked "makerdao".

Let us navigate back to the API documentation https://defillama.com/docs/api and take a closer look at the /protocol/{protocol} command.

Again, we can try the API call directly on the DeFi Llama website first to get a feeling for the kind of data we will receive. Let us input "makerdao" into the protocol slug field and press execute.

In the resulting server response we can see a complex JSON data structure.

At the highest level we receive more details about the protocol, and if we scroll further down the server response, we can also see what other data is included in the API response.

From our previous API call https://api.llama.fi/protocols that used the /protocols command, we can guess that the API address for this call should now be

• API base URL: https://api.llama.fi

• API query: /protocol/

• Parameter: makerdao

Adding everything together the address we need to use is: https://api.llama.fi/protocol/makerdao

Again in Excel we click the "Data" (1) tab and select "From Web" (2).

In the pop-up, paste the URL we just created:

A connecting pop-up will appear indicating that Excel is attempting the API call.

Part 5.5 Transforming the retrieved DeFi Llama data – /protocol/{protocol}

Since I've worked with API before, I know to access "tvl" to get the total MakerDAO TVL over time. However, when you work with APIs you have not yet worked with before and the API documentation isn't very descriptive, you may need to click around the API responses to find what you are looking for.

In the result we can see all details of the MakerDAO protocol that DeFi Llama provides through their API as of this moment in time. From here we click on "record" next to "tvl" to expand that entry.

From here we transform these entries into a table by clicking on "To Table".

When the below pop-up appears, click OK.

We must once again click the outwards facing arrows (1) on our column of data to open the sub-menu, then click OK (2) to expand the data.

We can now see data and TVL data or phrased differently, a timeseries of Total Value Locked in MakerDAO. But you may have noticed that the dates currently do not resemble dates. That is because the dates have been returned as UNIX timestamps, which count seconds from January 1st, 1997 onwards. Excel does not recognize this time format. If we click the cell format dropdown (1) and convert this data to date format (2), then…

We receive an error. Excel is not capable of directly transforming from a UNIX timestamp to an Excel time format. To do this, we need to manually calculate what the Excel time equivalent would be.

First, undo the last action by clicking on the "X" next to "Changed Type" in the "Applied Steps" window on the right.

Now that we have returned to our previous view, select the "Add Column" tab and click "Custom Column".

This will open the Custom Column editor where we can input a custom formula. Power Query formulas are different to Excel formulas, so if you want to do complex calculations, please refer to the Power Query formula documentation by Microsoft: https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference

What we must do now is write a custom formula that converts the timestamp from UNIX to Excel format. UNIX time counts time in seconds from January 1st, 1970, while Excel counts time as days from January 1st, 1990. So to convert the time, we must take UNIX time and multiply by the amount of seconds within a day (86,400), then add the number of days from January 1st 1900 to January 1st 1970 (25,569).

Let's add the formula below and click OK.

A new column has been added with numbers, but these numbers still don't look like dates. The values are correct, but the format is not.

Hence as a next step, we need to change the format to "Date".

And there we go, now our data looks alright.

Now let's clean up our table by removing the first column which is now obsolete. To do this right click on the column header (1) and then click "Remove" (2).

And then let's move the new date column to the left (simply drag it to the left).

Now our data is ready to be loaded into an Excel sheet. Click "Close & Load".

Now the timeseries TVL data for MakerDAO has been loaded into Excel as a data table. Congratulations!

From here we could, for example, use the data to add a line chart to visualize MakerDAO's TVL over time.

Part 6 Other Notes

Here we will briefly cover how to refresh data after you have built a query, and share some other important notes on API call URLs.

Part 6.1 Refreshing Data

To refresh this data when you open your Excel file in the future, simply navigate to the Data tab and click "Refresh All" (1).

Another quick way is to just right click anywhere within your table (1) and click "Refresh" (2).

Alternatively, if you only want to refresh a specific query, you can click anywhere in the data table (1) then navigate to the data tab (2) and click the down arrow under the "Refresh All" button (3) and click "Refresh" (4).

Or within the data tab (1) you can open the Queries & Connections panel (2), right click on your specific query (3) and click "Refresh" (4).

Part 6.2 Important note on API call URL

In our first CoinGecko example we used the below API call link provided by the CoinGecko API documentation:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=100&page=1&sparkline=false

This link is actually comprised of two parts, namely the base URL and the API call parameters:

• Base URL: https://api.coingecko.com/api/v3/coins/markets

• Parameters:

  • ?vs_currency=usd

  • &order=market_cap_desc

  • &per_page=100

  • &page=1

  • &sparkline=false

The first parameter is prepended by a question mark (?), while every other parameter is prepended with an ampersand (&). Each parameter consists of the parameter name (left of the =) and the parameter setting (right of the =). You can change these parameters as needed for your specific API call. When manually building API call URLs, make sure that you include the required parameters as stated in the API documentation, otherwise your API call will return an error.

Error codes and their meanings are usually listed in API documentations as well to help you with troubleshooting.

7. Closing thoughts

From here on out you can use multiple API queries to fetch and update information at regular intervals and create entire dashboards of only the data that is important to you. Gone are the days where you have to click through multiple websites and multiple pages to see the data that you care about – instead you can build it all in Excel!

If you have previous experience with Excel, you can combine these data fetching techniques with other Excel data transformation techniques to combine queries from multiple APIs to get a strong view on the crypto market. For example, you could combine CoinGecko's or CoinMarketCap's AAVE token pricing, market cap and volume details with DeFi Llama's TVL data to get an immediate overview of how the AAVE token is performing.

If you found this helpful, please consider sharing this and following me on Twitter @0xPhillan. And if you have any questions, feel free to DM me on Twitter!

Thanks, and have a great day!