10.9 C
Karachi
Monday, January 30, 2023

CoinGecko API in Microsoft Excel

In this article, we will demonstrate how to use CoinGecko REST API in Microsoft Excel to build Crypto Portfolio Dashboard. We will use the built-in PowerQuery feature of Microsoft Excel to extract data from API.

Step 1: Select GET endpoint to fetch coin’s data

Go to CoinGecko API documentation, we will see a list of GET queries to fetch coin’s data in a different manner. We are interested in fetching the data of all coins, so we will choose the “GET /coins/list” endpoint.

Open the dropdown and click the “Try it out” button

Now we will execute the query and it will return the below URL that will be used in Excel PowerQuery to fetch coin data in Excel.

https://api.coingecko.com/api/v3/coins/list

Step 2: Working with Excel PowerQuery

Now open excel and go to Data > From Web tab and provide URL returned from API in Step 1. Click OK to continue.

We will see PowerQuery windows as shown below. Some Raw Data named as “Record” is loaded to PowerQuery window, we need to convert these records to table format.

Click the “To Table” button to convert Raw Records to “Table” format.

You will be prompted to the dialogue box as shown below, keep default values and click OK to continue.

Now we will expand the table to display data as shown below.

Once done with the last step, you see the windows shown below; the table is expanded and all columns show up. At this point, your work related to PowerQuery is finished, just click the yellow highlighted button “Close & Load” at the top left corner of the window to continue.

Now you should expect to see the excel sheet as shown below. We see that the three fields – id, symbol, and name of 9950+ cryptocurrencies have been pulled from the CoikGecko API.

Step 3: Creating a Crypto Portfolio using CoinGecko API

At this point, we have a better understanding of how to use API in excel. To create the portfolio of cryptocurrency, we need more fields like current price, market cap, rank, volume, etc. So we need a different endpoint to fetch more detailed data from API.

We will use the “GET /coins/markets” endpoint to fetch data to make a portfolio.

Provide the input as shown below.

Click the Execute button and copy the returned URL.

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

Now repeat the same steps as shown earlier in Step 2 to fetch the API data into excel i.e converting records into tables, expanding tables into columns, and load&close. You will end up seeing the windows below: 100 top cryptocurrencies loaded orders by market cap descending.

If you want to fetch more than 100 cryptocurrencies, you can either change the input fields or change the URL as below

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

Now go to the sheet named list,

Gulraeez Gulshan
Gulraeez Gulshan
I am an engineer, programmer, tech-savvy professional, and very passionate about the latest technologies for the modern web, mobile, cloud-native, machine learning, and network automation. I have a bachelor's degree in Electronics Engineering and a Master's degree in Computer Science and Information Technology from a renowned university in Pakistan. I have not limited myself to a certain set of skills in this era where technology is in a state of flux; I have experience working with an extensive range of technologies and learning daily to update my skills and adapt to the latest technologies

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Latest Articles