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.
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.
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
Now go to the sheet named list,