Follow me on Twitter: @0xPhillan

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
• 1. 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:

https://coinmarketcap.com/api/

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:

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

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!