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

In this tutorial, learn how to leverage CoinGecko APIand 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 theCoinGecko API/coins/listendpoint. This is a fundamental step as it serves as a navigational guide to reference coin ids, forfuture API calls that require ids to be specifiedin its parameter.

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

If you are using our freeDemo plan, remember to toggle the documentation to v3.0.1. If you are onthe 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 URLand navigate toyour 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.

💡 Pro-tip: If you plan to use CoinGecko API extensively and have mission-critical analysis work, we recommend subscribing to our Analyst API Plan at only $103/month (annual subscription).

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:

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 Searchendpoint, 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:

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

Update: As of February 2024, access to historical data via the Demo API public planisrestricted to the past 365 days. To access the complete range of historical data, please subscribe to one of ourpaid plansto obtain a ProAPI key.

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. The first value in each cell is the timestamp in the UNIX epoch format. The second value is Bitcoin’s price in USD.

In order to have timestamp in one column and price in another, right click on the ‘Value’ header cell and select Split Column > By Delimiter.

Columns are now split. In this example we’ve renamed the columns accordingly for clarity.

Close and load the data. In your actual workbook, you’ll now see a table with the following rows: Name (prices, market cap and total volume), Unix TImestamp and Price USD.

Convert the UNIX epoch timestamp to a human-readable date with the formula ‘=(CELL/86400)+DATE(1970,1,1)’.

Since data for price, market cap and total volume are appended in the same table, create a pivot table using the current data set to show a clearer view of daily historical crypto price, market cap and volume data.

💡 Pro-tip: Depending on your needs, you can get historical data of cryptocurrencies with any of these endpoints:

• /coins/{id}/history: Get the coin price and market data on a specific day in the past (00:00 UTC).
• /coins/{id}/market_chart: Get the historical coin price and market data, X days before today.
• /coins/{id}/market_chart/range: Get historical coin price and market data for a specified date range.
• /coins/{id}/contract/{contract_address}/market_chart: Get historical coin price and market data with a contract address.
• /coins/{id}/contract/{contract_address}/market_chart: Get historical coin price and market data with a contract address for a specified date range.

How to Fetch Total Crypto Market Cap Data in Excel

You can call the Crypto Global Market Data endpoint in CoinGecko API to get real-time total crypto market cap data in Excel. For historical global market cap and volume data, use theGlobal Market Chart endpoint.

In this demo, we’ll be using the latter – note that this is accessible via the paid API plan.

As indicated in the documentation, data granularity for the ‘days’ parameter is automatically set based on the number of days.

• 1 day from now = data granularity is at hourly intervals
• 2 days and above = data granularity is at daily intervals, at 00:00 UTC

To pull the last 30 days of data, let’s specify the parameters in the Request URL where ‘days=30’.

💡 Pro-tip: The default currency for this query is USD. However, you can specify a currency of choice in the optional parameter ‘vs_currency=SGD’ for instance.

Convert the data to table and expand the values accordingly.

As we’ll only be looking at retrieving market cap data, let’s remove the volume column to avoid confusion. The Power Query Editor should appear as such:

Extract the values and select a comma delimiter for concatenating values.

Concatenated values are now shown in the ‘market_cap’ column. The first value is the timestamp in UNIX epoch format, and the second value is the total market cap on its corresponding day.

Next, split the ‘market_cap’ column by the comma delimiter. You will now have timestamp values in the middle column, and market cap values in the right-most column.

Rename the columns for clarity. We’ll also remove the first column ‘Name’.

Finally, save and close the Power Query Editor, and find your clean data in your Excel workbook.

Insert a new column to convert the UNIX epoch timestamp to a human-readable format, with the formula ‘=(CELL/86400)+DATE(1970,1,1)’. Format other columns accordingly.

💡 Pro-tip: To change the number of days of data being pulled, head to Data > Query & Connections and update the number of days within the Request URL.

How to Pull Price Data for Trending Crypto Categories

To get live data for trending crypto categories, use the CoinGecko API endpoint /categories. This will import market cap, volume and respective 24 hour percentage change, as well as the top 3 coins, for each category – this can be useful to see which category or ecosystem is gaining traction.

Similar to previous steps, run the Request URL in Excel’s Power Query Editor. Convert the data into a table and expand the columns accordingly. Note to extract the values for the nested List in the ‘top_3_coins’ column, and splitting its columns thereafter.

Format the cells accordingly. With that, you now have real-time data of trending crypto categories in your Excel workbook!

Refreshing the Data

As with most databases, you can choose to refresh the data manually or automatically.

Manual Refresh

To refresh all connected queries in the worksheet, go toData > Refresh All (orange)in your toolbar. Tomanually refresh specific queries, go to Data > Queries & Connections (blue).

A right panel will appear, click on theRefresh icon to refresh the corresponding query.Doing this can conserve the number of API calls made, especially if you are on the Public or lower-tiered API plan.

Automatic Refresh

To set up automatic refresh, navigate to Data > Queries & Connections. Right click and select Properties.

This prompts the followingdialog box, where you can toggle your preferred refresh frequency:

• Enable background refresh
• Refresh every X minute
• Refresh data when opening the file
• Refresh this connection on Refresh All

This will need to be set up for each query individually, as there is no other way to adjust the settings globally.

View Last Updated Status

You can view when the query was last updated by hovering on each query.

Troubleshooting Tips

API Key Error

You may run into the following error when using the Advanced Data from Web feature.

Perform relevant checks to verify that your Paid API key and header names are in order. If the error persists, there may simply be a clash among the multiple queries in the worksheet. To solve this, ensure that your API key is applicable on the main root URL https://pro-api.coingecko.com/api/v3, instead of https://pro-api.coingecko.com/api/v3/search/trending for instance.

Getting Rate Limited

Another issue you may encounter is getting rate limited. While there areno error prompts, you may experience timeout or a perpetually connecting dialog box.

Signing up for our Paid CoinGecko API can solve this issue – and enable you to focus on analyzing the data rather than troubleshooting sheet errors.

If a consideration for subscribing is running into unexpected overages, setting up a call consumption alert via your developer dashboard can help avoid overages.

Advanced traders who want access to more API endpoints, historical prices and avoid getting rate limited, may consider subscribing to ourAnalyst API plan. The CoinGecko API currently has 70+ endpoints, tracks 2M+ coins across 1,000+ exchanges on 145 networks, and serves billions of API calls each month.

Looking for further API resources for Excel? Check out this tutorial that covers how to import crypto and stock prices into Excel.