How to Import Live Crypto Prices into Excel Spreadsheets | CoinGecko API

In this tutorial, learn how to leverage CoinGecko API and Microsoft Excel to:

1. Set up a directory of coin ids and symbols
2. Import and track live crypto prices
3. Get real-time crypto prices for trading pairs
4. Get historical prices for specific cryptocurrencies
5. Fetch total crypto market cap data
6. Pull price data for trending crypto categories

Additionally, we’ll cover how to refresh the data in your Excel workbook, address common errors and how you can overcome rate limits.

Let’s dive in!

Note: This guide is prepared using the Microsoft 365 version of Excel, for Excel users operating on Windows instead of Mac OS. Power Query is included in Excel versions higher than 2010, however only 2016 versions onwards and Microsoft 365 are actively updated by Microsoft.  In newer versions, Power Query capabilities are integrated within the Data tab. If this feature is not available in your version of Excel, download it for free on Microsoft.

How to Import a List of Coin IDs & Symbols into Excel

You can import a list of coin ids and symbols into Excel with the CoinGecko API /coins/list endpoint. This is a fundamental step as it serves as a navigational guide to reference coin ids, for future API calls that require ids to be specified in its parameter.

To start, head over to the CoinGecko API documentation and find the /coins/list endpoint.

If you are using our free Demo plan, remember to toggle the documentation to v3.0.1. If you are on the Pro (Paid) API plans, be sure to reference v3.1.1.

This endpoint will return a full list of active coins on CoinGecko and its respective ‘id’, which identifies all crypto assets listed.

Add in your API key and click ‘Try It‘.

Copy the URL and navigate to your new Excel workbook – Data Data from Web.

Upon clicking Data from Web, a ‘From Web’ dialog box will appear.

If you’re on our Demo API plan, stay on the Basic toggle and paste the URL accordingly. Depending on the current global call usage, it may take a while to establish a connection. 

If you are a paid API subscriber and have a pro API key, you can connect the API in two ways:

1. In the same Basic dialog box, paste the Request URL with the root URL updated to the pro API version, and append your API key at the end. Essentially, it should appear as:

   https://pro-api.coingecko.com/api/v3/coins/list?include_platform=false&x_cg_pro_api_key=YOUR-API-KEY-HERE

2. Toggle to the ‘Advanced’ option, and paste the Request URL with the updated pro API root URL in the ‘URL parts’ field. Additionally, in the HTTP Request Header Parameters section, fill in ‘x-cg-pro-api-key’ and input your pro API key accordingly.

Both ways work well, and is just a matter of personal preference. Once the connection is established, a new Power Query Editor window appears with a column of records. 

Select ‘To Table’ at the top left corner of the window. You may encounter a pop-up asking for a delimiter to split the table.

Next, expand the table.

The Records are now expanded into their respective columns. You may choose to rename the columns accordingly (in our example, we’ve renamed columns to ‘id’, ‘symbol’ and ‘name’ respectively. Finally click on ‘Close & Load’ on the top left.

That’s it! Now you have a list of active cryptocurrencies on CoinGecko, with its respective coin id, symbol and names – this will come in handy for future API calls.

How to Import Trending Crypto Price Data into Excel

The quickest way to fetch trending crypto price data into Excel is using CoinGecko API’s Trending Search endpoint, through Excel’s Data from Web feature. This will import price data on the top 7 trending coins on CoinGecko, as searched by users in the last 24 hours.

Similarly, copy the Request URL and follow the steps in the previous section to connect the endpoint to Excel’s Power Query Editor. In this example, the Request URL is: https://api.coingecko.com/api/v3/search/trending.

Upon closing and loading the table, you’ll find the top 7 trending cryptocurrencies and its respective data in the spreadsheet.

Format the data based on your preferences. Now you can easily reference the top 7 trending cryptocurrencies on CoinGecko, directly on Excel!

How to Get Data on Cryptocurrency Trading Pairs in Excel

Get all trading pairs (tickers) for a specified cryptocurrency on CoinGecko, with the Coin Tickers by ID endpoint.

Referencing our coins list in the first section of this guide, we identify that the id for Ethereum is ‘ethereum’.

On the API documentation playground, fill in the following parameters:

Retrieve the Request URL once again, which should be: https://api.coingecko.com/api/v3/coins/ethereum/tickers?include_exchange_logo=true&order=trust_score_desc

Connect it using Excel’s Data from Web feature and you’ll see a table with just one set of data. Double click on ‘List’ to expand it into a list of records.

As every record contains a nested data set for each trading pair on Ethereum, you will need to convert this into a table. Click on the ‘List’ column header, and then select the ‘To Table’ button at the top left corner.

Expand the columns accordingly, checking the data points you’d like to retrieve.

After expanding the columns in the main table, you will find that some columns (like market, converted_last and converted_volume etc.) may additionally contain nested data. Depending on preference, you can expand this accordingly or keep it as is. 

When expanding converted_last and converted_volume, uncheck ‘eth’ since this is already our base currency.

After expanding all data points, close and load the Power Query Editor – you have successfully imported all ETH cryptocurrency pairs into your spreadsheet!

What Are Cryptocurrency Trading Pairs?

Trading pairs, also known as cryptocurrency pairs, are assets that can be traded for each other or swapped, on an exchange. A trading pair is used to compare the value of one cryptocurrency against another – essentially, how much of the base currency is needed to buy one unit of the quoted cryptocurrency.

How to Get Historical Crypto Prices into Excel

To download historical cryptocurrency prices into Excel, use the Coin Historical Chart Data by ID endpoint on CoinGecko API. This will derive historical market data through April 2013, and include price, market cap and 24 hour volume for a specific coin.

In this example, we will be pulling out the maximum historical data for Bitcoin (BTC). Navigate to the API documentation playground, find the  /coins/{id}/market_chart endpoint, fill in the following parameters and execute the query.

• id: bitcoin
• vs_currency: usd
• days: max
• interval: daily
• precision: full

Once again, copy and paste the Request URL into Excel’s Power Query Editor. Similar to previous steps, convert the source data into a table. However, this time instead of expanding the list of values to new rows, select ‘Extract Values’ instead.

A dialog box appears, select ‘Comma’ as the delimiter and click on the ‘OK’ button.

Two strings of values are now extracted within the Value column, separated by the comma delimiter….