How to Import Crypto Prices into Excel with CoinGecko API

https://www.coingecko.com/learn/import-crypto-prices-excel

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 URL.

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

💡 Pro-tip: Along with /coins/list, these are navigational endpoints in the API, which helps you to discover and navigate the data retrieval process.

  • /exchanges/list
  • /derivatives/exchanges/list
  • /nfts/list
  • /search
  • /search/trending
  • /coins/list/new

Click on ‘Try it out’, set the ‘include_platform’ parameter to ‘false’ as this isn’t required for the purpose of this demo, and hit ‘Execute’.

Two responses will appear, cURL and a Request URL. Copy the Request URL.

Coingecko API Documentation playground get /coins/list request URL

On your new Excel workbook, navigate to Data > Data from Web.

Microsoft Excel 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. 

Data from web basic toggle connect API request URL

💡 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:

    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.

    Enter Pro API Key in Excel dialog box

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.

Excel power query editor

Next, expand the table.

expand table in excel power query editor example

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.

close and load power query editor with tooltip

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.

create a excel list of cryptocurrency price data by coin id symbols


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 /search/trending 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.

Navigate to the API documentation once again, and find the Request URL for this endpoint.

Search 7d trending coins request URL coingecko api

Copy the Request URL and follow the steps in the previous section to connect the endpoint to Excel’s Power Query Editor.

find out which cryptocurrencies are trending

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

Import trending crypto data into Microsoft Excel

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

fetch trending cryptocurrency price data into excel workbook


How to Get Data on Cryptocurrency Trading Pairs in Excel

Get all trading pairs (tickers) for a specified cryptocurrency on CoinGecko, with the /coins/{id}/tickers endpoint.

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

Ethereum coin id on coins list

From the API documentation playground, fill in the following parameters and hit ‘Execute’ to retrieve the Request URL, which should look like this: 

https://api.coingecko.com/api/v3/coins/ethereum/tickers?include_exchange_logo=true&order=trust_score_desc

api parameters

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.

power editor

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.

power editor list of records example

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

expand columns select values

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. nested columns

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!

example of cryptocurrency trading pairs CoinGecko api

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 CoinGecko API endpoint /coins/{id}/market_chart. This will derive historical market data through April 2013, and include price, market cap and 24 hour volume.

In this example, we will be pulling out the maximum historical data for Bitcoin (BTC), based on what has been tracked on CoinGecko. 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

how to download historical crypto price data CoinGecko api

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.

extract values in Microsoft power query editor

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

pop-up

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.

concatenated values split by comma delimiter

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.

split column by delimiter example data set

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

split and rename columns

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)’.

convert epoch unix timestamp to human readable date using excel formula

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.

pivot table of historical crypto data in excel sheet

💡 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

Use the CoinGecko API endpoint /global to fetch real-time total crypto market cap data into Excel. For historical global market cap and volume data, use the /global/market_cap_chart endpoint, exclusively available for Paid API subscribers.

In this demo, we’ll be using the second endpoint /global/market_cap_chart.

Head over to our Paid API docs, which outlines that the data granularity for the ‘days’ parameter is automatically set based on the number of days indicated in the parameter.

  • 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’.

Advanced data from web

💡 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:

market cap on power editor

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

extract 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.

values are concatenated in excel power query editor

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.

Split columns by delimiter in excel power query editor

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

rename table

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.

download total crypto market cap into excel with crypto api

💡 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.

Change number of days in query excel power editor

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.

import trending cryptocurrency coin categories into ms excel

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

which crypto categories are trending excel workbook CoinGecko api


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 to Data > Refresh All (orange) in your toolbar. To manually refresh specific queries, go to Data > Queries & Connections (blue).

Refresh All queries and connections in excel power query editor

A right panel will appear, click on the Refresh 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.

Refresh queries & connections properties

This prompts the following dialog 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.

Refresh properties settings dialog box

View Last Updated Status

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

Last refreshed


Troubleshooting Tips

API Key Error

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

api error

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.

troubleshooting data from web api key error

Getting Rate Limited

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

rate limited api queries CoinGecko api

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 our Analyst API plan. The CoinGecko API currently has 50+ endpoints, tracks 10,000+ coins across 800+ exchanges and 3,000+ NFT collections serving billions of API calls each month. If you require a custom solution, fill in the form below to get in touch with our API sales team:

How to Import Crypto Prices into Google Sheets with CoinGecko API

https://www.coingecko.com/learn/import-crypto-prices-google-sheets

Google Sheets doesn’t natively support JSON data, which means it may be slightly tricky to fetch real-time crypto price data into your spreadsheet. Thankfully, no-code API connectors by Mixed Analytics and Apipheny make it extremely easy to help you pull crypto data from our API, in just a few clicks!

In this guide, learn how to:

  1. Connect Google Sheets to CoinGecko API with an API Connector
  2. Import a list of coin ids and symbols
  3. Get live crypto price data for specific cryptocurrencies
  4. Retrieve historical crypto prices for specific coins
  5. Fetch total crypto market cap data
  6. Pull live coin price data for trending categories

We will also briefly cover how you can refresh the data in your spreadsheet, and address potential rate limitations and what you can do about it.

Let’s jump in!


Connect Google Sheets to CoinGecko API using an API Connector

For this guide, we’ll be using the API Connector by Mixed Analytics (their free plan allows for 3 saved requests and 250 requests/month and provides a 30 day free-trial of its Pro plan). Let’s kick things off by installing the add-on from Google’s Marketplace.

API Connector by Mixed Analytics | Fetch Crypto Prices with CoinGecko Crypto API

Once the add-on has been successfully installed, create a new Google Sheet. In the top navigation bar, select Extensions and click ‘Open’.

Import crypto prices google sheets Extensions navigation bar

A panel will slide out from the right. Click on the ‘Create’ tab and you’ll find a few fields that will configure your API request. The commonly used ones in this guide will be:

  • Request URL
  • Output settings > Destination sheet and cell
  • Output options
  • Naming the request

API Connector - configure API request

With that, your spreadsheet is now fully set up and ready to import some crypto price data!

How to Import a List of Coin IDs & Symbols

First, head over to CoinGecko’s Crypto API documentation and navigate to the /coins/list endpoint. Select ‘Try It Out’.

In the include_platform parameter dropdown, you may select ‘true’ if you want to retrieve platform contract addresses, else set it to ‘false’. Hit the Execute button and copy the Request URL (https://api.coingecko.com/api/v3/coins/list?include_platform=true).

How to fetch live crypto price data on google sheets | CoinGecko API endpoint

Go back to your spreadsheet with the active API Connector panel and paste the copied URL into the ‘Request URL’ field.

If you’re a Paid API subscriber, change the root URL to https://pro-api.coingecko.com/api/v3/ and append your API key at the end to avoid getting rate limited by Google Sheets (more on this below).

Your API Request on the connector add-on will look similar to what’s in the following screenshot. Name it accordingly so that you can revisit this easily in future, including scheduling automatic data refreshes. In this example, we’ve named this request Coin List since we’ll be pulling out a list of coins and respective ids. Save and click Run.

Configure API request - mixed analytics API connector

An extensive list of coins data will populate your destination sheet – with that, we now have a comprehensive coin list directory to reference ids and symbols, to fetch all sorts of crypto price data.

CoinGecko Token API List

Tip: The CoinGecko team also ensures this list of coin ids and symbols is publicly accessible, so you don’t necessarily have to generate your own.

Now that we have a comprehensive list of coin IDs and symbols, we can reference the coins’ ids and get prices for specific cryptocurrencies in the next section. 

How to Import Live Crypto Price Data into Google Sheets

The easiest way to import live crypto price data into Google Sheets is using the popular CoinGecko API endpoint ‘/simple/price’. This endpoint allows you to fetch real-time crypto prices for multiple coins with just one API call, and is a publicly accessible API endpoint.

However, due to limitations with the API Connector add-on, we’ll require more than one API call in this demo, in order to get bitcoin, ethereum, dogecoin, apecoin and matic-network price data.

Head over to the API documentation once again and fill in respective parameters.

Get real-time cryptocurrency prices with the /simple/price endpoint | CoinGecko API

Hit Execute and copy the Request URL:

https://api.coingecko.com/api/v3/simple/price?ids=bitcoin&vs_currencies=usd&include_market_cap=true&include_24hr_vol=true&include_24hr_change=true&include_last_updated_at=true&precision=full

Paste it into the API Connector add-on and select a new destination sheet for this data. 

Tip: Remember to create a new request or duplicate the previous one. Essentially, avoid overwriting your previous request or you will experience stale data when your sheet auto-refreshes saved queries.

You may want to structure your worksheet as such:

Format your google sheets to pull specific crypto price data

Check ‘remove header row’ and uncheck ‘clear sheet data’. Ensure your destination cell is set to B2. Run the query and you’ll find that bitcoin data will now populate row 2.

Do the same with the rest of the cryptocurrencies you’d like to track. A more efficient way of setting up these numerous requests is to duplicate existing requests and change the coin id in the Request URL directly.

Get crypto prices with CoinGecko API - market cap, volume, price data with crypto API

And done! Now we have real-time cryptocurrency data imported straight into Google Sheets.

Real-time crypto price data, bitcoin ethereal dogecoin prices

Fetching Live Crypto Prices for Coins in Trending Categories

In order to identify all categories on CoinGecko, we’ll use the endpoint /coins/categories.

Input the following Request URL in the API Connector accordingly and execute the query.

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

The full list of categories on CoinGecko with market cap data will now populate in the spreadsheet:

How to import live coin and token prices for trending cryptocurrency categories

You may format values in column F, G and H with the =IMAGE([CELL],1) function to visualize the token logos, since the original values are hosted logo image links of each token. The ‘1’ at the end of the function simply refers to resizing the image to fit inside the cell, maintaining aspect ratio.

Google Sheets function =IMAGE()

Now that we have a list of all categories, we can either sort it by 24 hour market cap change on the spreadsheet, or reference what’s trending in the last 7 days on our Top Crypto Categories by Market Cap page. A quick sort shows us that TRY Stablecoin, Kommunitas Launchpad tokens and Discord Bots are trending in the last 7 days.

Top cryptocurrency categories by market cap on CoinGecko - TRY Stablecoin, Kommunitas Launchpad tokens, Discord Bots, Telegram Bots

For this example, we’ll pull out price data for all 15 coins in the Discord Bots category. We will first identify the category id, based on the earlier called data.

Discord bots cryptocurrency category - Trending Coins | CoinGecko

Navigate to the API documentation and input ‘discord-bots’ in the category parameter.

get live cryptocurrency price market data with crypto api | CoinGecko API

Upon running the query, the Request URL will be:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=discord-bots&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=full

If you’re a Paid API user, your Request URL will appear as:

https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=discord-bots&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=full&x_cg_pro_api_key=YOUR-API-KEY

Copy this and head over to API Connector. As above, do not overwrite previous requests and instead Create a New Request. Paste this into the Request URL field and call the API. All cryptocurrencies listed in this category now show up in this sheet, and you can schedule a time to automatically refresh the data in this sheet or manually refresh it through the add-on.

Discord Bots cryptocurrency category token price data | CoinGecko API

Let’s move on now to importing historical crypto prices and the total crypto market cap into Google Sheets.

How to Pull Historical Crypto Price Data into Google Sheets

To import historical cryptocurrency prices into Google Sheets, use CoinGecko API’s /coins/{id}/market_chart endpoint and access up to 10 years worth of historical crypto data, from April 2013 to date.

Traders often leverage spreadsheets to build up a database of historical prices and other information, which can help with analysis and backtesting crypto trading strategies. These endpoints make pulling out historical crypto prices for specific coins like Bitcoin, Ethereum and so on, an extremely straightforward process.

Once again, navigate to the API documentation and find the endpoint /coins/{id}/market_chart. In this example, we’ll query 14 days of historical Bitcoin (BTC) price data, with a daily granularity.

Pull historical Bitcoin price data - historical BTC prices with CoinGecko API

Fill in the parameters based on your desired data output and your Request URL should appear along the lines of:

https://api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=14&interval=daily&precision=full

If you’re a Paid API user, your Request URL will appear as:

https://pro-api.coingecko.com/api/v3/coins/bitcoin/market_chart?vs_currency=usd&days=14&interval=daily&precision=full&x_cg_pro_api_key=YOUR-API-KEY

Copy this and head over to the API Connector, pasting this into the Request URL field.

This time, expand ‘Output options’ by clicking on it, then select ‘grid’ under Report style. This will help to arrange your data in a grid-like table.

API Connector MixedAnalytics Crypto Price, Market Cap and Total Volume | CoinGecko API

Run the request and the data should populate accordingly in your destination cell and sheet.

Import cryptocurrency price data into Google Sheets spreadsheet - Live and auto-refresh | CoinGecko

In each cell, two values are returned:

Cryptocurrency API price and market data

The first value is the returned timestamp data in the UNIX milliseconds format. You may use tools like UNIX Epoch Converters to convert the UNIX timestamp data into a human readable date. Alternatively, follow the subsequent steps to format your sheet.

Use the following formula to remove the ‘[] and ‘]’ brackets, and split the values by the comma separator. 

=SPLIT(SUBSTITUTE(SUBSTITUTE($A3,"[",""),"]",""), ",")

Apply the formula to the rest of the rows accordingly.

Using the split and substitute Google Sheets function

Price values are now split into its separate columns (F and G).

Get crypto price data using the best cryptocurrency API | CoinGecko API

To convert UNIX timestamps to a human readable date and time, apply this formula: =EPOCHTODATE(F3,2)

How to use EPOCHTODATE function google sheets example

Now that we have a clean date and time column, let’s move on to extract the data for Market Cap and Volume. Since the UNIX timestamp is repeated, we’ll now use the LEFT function to find the position of the first comma and remove data up to that point, effectively removing the first value from the string.

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B3,"[",""),"]",""), LEFT(SUBSTITUTE($B3,"[",""), FIND(",", SUBSTITUTE($B3,"[",""))), ""))

How to Import Cryptocurrency Prices in Google Sheets

Copy the formula across the rest of the rows in column H, and use the same function on cell I3, mapped to the data on cell C3, for Volume.

Import historical cryptocurrency prices for backtesting trading strategy | CoinGecko API

This can be quickly replicated across other cryptocurrencies – simply reference the Coin List you’ve generated earlier, find the coin id and replace it in the Request URL accordingly.

How far back does the historical crypto data go?

You can fetch up to 10 years worth of historical cryptocurrency prices since April 2013 with the /coins/{id}/market_chart endpoint, by setting the days parameter as ‘max’. Also, it’s worth noting that depending on your inputs for the ‘days‘ parameter, data retrieved will be of various granularities:

  • Within 1 day from now = data granularity is at 5 minute intervals
  • Between 1-90 days from now = data granularity is at hourly intervals
  • More than 90 days from now = data granularity is at daily intervals, at 00:00 UTC

Historical crypto price data days parameter | CoinGecko API

Additional Tips:

  • Use the /coins/{id}/market_chart/range endpoint to get a list of historical price and market data for a specific coin, for a specified date range in UNIX Timestamp between ‘from’ and ‘to’.
  • You may even choose to pull out historical price data by contract addresses with these endpoints:
    • /coins/{id}/contract/{contract_address}/market_chart
    • /coins/{id}/contract/{contract_address}/market_chart/range

 

How to Import Total Crypto Market Cap Data

The /global/market_cap_chart endpoint returns the historical global market cap and volume data, and is an exclusive endpoint for Paid API subscribers. This is another popular endpoint and its equivalent page on CoinGecko is this total crypto market cap chart.

Similar to the previous /coins/{id}/market_chart endpoint, data granularity of this endpoint is automatically set based on the number of days indicated in the parameter.

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

In this example, we’ll retrieve the request URL from the Paid API documentation and import 14 days of historical price data (from today). As such, the ‘days’ parameter is set to ‘14’.

https://pro-api.coingecko.com/api/v3/global/market_cap_chart?days=14&x_cg_pro_api_key={YOUR-API-KEY}

Create a new request in the API Connector and paste the Request URL into the field. Before saving and executing the request, expand Output options and select ‘grid’ under Report Style.

Output settings for API Connector

After running the query, two columns of data now populated: Market Cap and Volume.

Total crypto market cap and volume data on Google Sheets | CoinGecko API | API Connector

Use the spreadsheet functions above to remove the brackets, split the data and convert the UNIX timestamp to a human readable date. We now have daily 00:00 UTC data of the total crypto market cap, for the last 14 days.

Convert UNIX Timestamp to human readable date

Refreshing the Data: Manually & Automatically

There are two ways to refresh the data pull with this API Connector – manually and automatically.

Manual Refresh

This simply means triggering the refresh manually, when you want to refresh the sheet. Click on Extensions > API Connector > Refresh All Now in the Google Sheets top navigation bar.

API Connector Refresh All Now

Automatic Refresh

The second way is to automatically refresh the data by creating a trigger schedule via the API Connector. Click on the Schedule tab in the add-on panel, and you’ll see various customizable trigger settings. This provides the flexibility in running data refreshes for specific API requests at specific intervals (every hour, 3 hours, 6 hours, 12 hours, daily, weekly, monthly).

API Connector Google Sheets Add-on Trigger Refresh Settings | CoinGecko API

You may also consider using a mix of both methods to reduce the number of API calls, like manually refreshing coin lists or historical crypto prices, where real-time data is not as crucial, and automatically refreshing coin price data for trending categories.

Troubleshooting: Google Sheets API Rate Limits

An error you may encounter is error code 429, where you have exceeded the API rate limit. This is due to rate limits on Google Sheets, which restricts how much data you’re able to import with each API call.

Getting rate limited on Google Sheets | Crypto API rate limits

Google Sheets rely on shared hosting – this means that one Google server hosts multiple spreadsheets and caters to multiple users sharing the same limit of API calls per minute. As such, you may get rate limited even when using only a few API calls.

Subscribing to paid CoinGecko API plans can help you overcome rate limits, as CoinGecko’s server will host and supply all data. 

Crypto API for developers set up call consumption alerts | CoinGecko API

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

Are you a project founder, developer or builder looking for a custom solution? Get in touch with our API sales team for a custom solution:


Looking for similar guides? Check out this tutorial that covers importing real-time crypto data for the top 500 coins, using an ImportJSON AppsScript.

Creating an Ethereum dApp with Crypto Price Data

https://www.coingecko.com/learn/ethereum-dapp-crypto-api

Digital applications that run on a blockchain network of computers, otherwise known as decentralized applications (dApps), provide users with innovative solutions that prioritize transparency, security and user control. According to DappRadar’s 2022 Industry Report, the dApp industry experienced a 50% increase in daily unique active wallets (dUAW), rising from 1.58 million dUAW to 2.37 million in 2022.

A key component of dApps is the ability to display real-time token price data, which empowers users to make informed decisions and engage with dynamic features seamlessly. In this guide, we will explore:

  • Common challenges developers face when integrating crypto data into dApps
  • Things to look out for when integrating crypto data
  • Popular CoinGecko API endpoints that developers use

Let’s dive in!

This guide caters to Ethereum-based web applications and ERC-20 tokens.


Common Challenges with Crypto Data Integrations

From navigating poorly documented APIs to facing data discrepancies, the developer community may face the following challenges when it comes to finding a crypto API provider.

  1. Unorganized Data that Lacks Comprehensive Token Coverage
    In terms of token data available, some crypto API providers may lack comprehensive price data covering a wide range of ERC-20 tokens. Beyond prices, dApps often require a comprehensive set of data, including historical price trends, trading volumes, market capitalization, and more. Sourcing and organizing this data from various providers and across multiple exchanges can be time-consuming and challenging, especially for developers who want to focus on innovation.

    Having poorly organized data where symbols clash can also mean that token ID mapping becomes a hassle for developers. For example, the BAT token can refer to Basic Attention Token or Batcoin, and WorldCoin (WDC) can be easily mixed up with Sam Altman’s Worldcoin (WLD). When symbols clash, developers end up spending time maintaining a list of tokens with the API provider ID or identifier, ultimately resulting in extra work or maintenance.

  2. Data Inconsistencies
    Developers may sometimes encounter inconsistent data when pulling crypto prices across various sources. Price disparities can lead to inaccurate token prices, which may then compromise dApp functionality. Another potential scenario that results in data discrepancies occur when the API data provider does not actively maintain its database or keep up with the evolving crypto market.

  3. Getting Rate Limited by Free or Public APIs
    Getting rate limited can hinder a dApp’s performance, leading to delays and potential disruptions in user experience. Developers may struggle to manage these especially if free, public APIs with a low rate limit are used to power dApps, and instead of reaping cost savings, this may instead result in a more resource-intensive processes, or a dApp that displays inconsistent or inaccurate crypto price data.

  4. Poorly Documented APIs and/or a Lack of Client Support
    Finally, a significant challenge developers encounter when integrating crypto data into dApps is the quality of API documentation and the availability of client support. Inadequate or confusing documentation can slow down the development process, leading to wasted time deciphering endpoints, parameters, and authentication methods. Further, a lack of responsive client support can leave developers stranded when they encounter issues or have questions about specific endpoints, their API usage and plan, and even accidental overages.

Selecting the Right Crypto API: Considerations to Note

Integrating real-time crypto price data into your dApp offers immense value – yet, as the above illustrates, it’s not without its challenges. Here are some key considerations to keep in mind, when assessing and selecting the right crypto API to leverage for your dApp.

  1. Data Coverage
    The crypto market evolves rapidly, which means that having comprehensive data coverage is essential when assessing a data provider for your dApp. Select a data provider that offers extensive coverage across a wide range of tokens (including ERC-20 tokens), and that continuously updates with new tokens listed, to ensure your dApp always remains relevant.

  2. Data Consistency and Accuracy
    Cryptocurrency markets can be highly volatile, and even slight discrepancies in prices across exchanges can lead to misinformation within your dApp. When selecting a crypto data provider, prioritize those with a proven track record of accurate data aggregation from multiple exchanges, and one that supplies high quality price data, such as CoinGecko API. Always thoroughly test and validate the data before incorporating it into your dApp, to ensure data integrity.

  3. Reliable API Uptime & Maintenance Logs
    Uninterrupted access to real-time price data is crucial for the smooth functioning of your dApp. Check the API provider’s historical uptime and downtime records. A reliable data provider will be transparent about its API status, and provide a comprehensive log of incidents and maintenance history. Ensuring that you onboard a robust and reliable API will minimize disruptions to your dApp’s user experience, and ensure that your users always have access to the latest price information.

  4. API Rate Limits and Scaling
    As your dApp gains in traction, the demand for real-time data may increase significantly. Be mindful of rate limits imposed by respective API providers. Exceeding these limits can result in temporary data unavailability or overage charges. Implementing caching mechanisms—temporarily storing data that your dApp frequently uses for quick retrieval (without having to refresh from the original source every single time)—for specific endpoints can help you stay within rate limits. Additionally, it’s important to plan ahead when it comes to scaling your dApp’s infrastructure to accommodate increased usage.

  5. User Privacy and Security
    Integrating external APIs, including crypto price data providers, may introduce potential security vulnerabilities. Ensure that the data provider follows industry best practices for data encryption, authentication, and privacy. Safeguard user data by adhering to data protection regulations and implementing proper security measures within your dApp.

  6. API Documentation and Support
    Thoroughly review the API documentation before integration – clear, comprehensive documentation will streamline the integration process and reduce development time. Look for well-documented code examples and use cases that match your dApp’s requirements. Additionally, assess the availability of client support. Prompt and knowledgeable support can be invaluable to developers, especially when faced with technical challenges during the integration process. A well-documented API combined with a responsive client support service are positive signals that the API is well-maintained.

  7. API Cost and Sustainability
    Lastly, consider the cost implications of integrating real-time price data into your dApp. Some API providers offer free access up to a certain limit, while others may require payment for higher usage tiers. Evaluate your dApp’s projected usage and growth to determine a sustainable pricing model. Factor in costs associated with potential upgrades, scaling, ongoing maintenance, as well as overages should rate limits exceed.

Popular CoinGecko API Endpoints for Ethereum dApps

To date, CoinGecko API has 50+ endpoints, tracks 10,000+ coins across 700+ exchanges and 3,000+ NFT collections – serving billions of API calls each month. We’ve listed the top 5 most popular CoinGecko API endpoints commonly used by developers and crypto projects, plus two bonus ones below, that you may want to consider for your next dApp:

Retrieve Live Token Prices with /coins/{id}

The /coins/{id} endpoint is especially useful to pull coin price and market data, exchange tickers, and metadata like links, categories, contract address, community, description, developer data and more, for specific coins. For example, the following Request URL can be used to retrieve live Bitcoin (BTC) price and metadata.

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

 

Retrieve Historical Token Prices with /coins/{id}/history

/coins/{id}/history is a commonly used endpoint to get historical data (price, market cap, 24 hour volume etc.) at any given date, for a specific cryptocurrency. For example, the following Request URL can be used to retrieve historical Bitcoin (BTC) price and metadata on November 9, 2021.

https://api.coingecko.com/api/v3/coins/bitcoin/history?date=09-11-2021

 

Retrieve Latest Coins Listed with /coins/list/new

 /coins/list/new is the most efficient endpoint that returns the latest 200 coins’ ids and activated time as listed on CoinGecko. With a data update frequency or cache of 30s, this is an exclusive endpoint for Paid API subscribers. Its equivalent page on CoinGecko is New Cryptocurrencies. The Request URL is:

https://pro-api.coingecko.com/api/v3/coins/list/new?&x_cg_pro_api_key={YOUR-API-KEY}

 

Identify Trading Pairs (Tickers) with /coins/{id}/tickers

This endpoint identifies all trading pairs, also known as tickers, for a coin. Should you want to narrow this down to trading pairs on a specific exchange, centralized or decentralized, you may use coins/{id}/tickers or /exchanges/{id}/tickers and insert the right parameters to filter accordingly. For instance:

This Request URL returns all trading pairs for Ripple (XRP):

https://api.coingecko.com/api/v3/coins/ripple/tickers

This Request URL returns all trading pairs for Ripple on Binance:

https://api.coingecko.com/api/v3/coins/ripple/tickers?exchange_ids=binance

Alternatively, this Request URL also returns all trading pairs for Ripple on Binance:

https://api.coingecko.com/api/v3/exchanges/binance/tickers?coin_ids=ripple

 

Retrieve a List of Coins in a Specific Category with /coins/markets

Crypto market rallies are often led by trending narratives, and these tokens are actively curated into Top Crypto Categories by the CoinGecko team. Updating the ‘category’ params for the /coins/markets endpoint will enable you to pull out all coins data within specific categories.

For example, this Request URL can be used to retrieve all token data that is classified under the ‘Layer 2’ category:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=layer-2&order=market_cap_desc&per_page=100&page=1&sparkline=false

Similarly, the following Request URL returns all tokens under the ‘Telegram Bots’ category:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=telegram-bots&order=market_cap_desc&per_page=100&page=1&sparkline=false

 

Bonus: Looking Up Coins by Contract Address

If you’re building an Ethereum wallet web interface and have a list of tokens with contract addresses, you can look up tokens’ contract addresses to display price data in your preferred currency – with the endpoint /coins/{id}/contract/{contract_address}.

For example, the Request URL to return coin details from Chainlink (LINK) token with the contract address of ‘0x514910771af9ca656af840dff83e8264ecf986ca’, would be:

https://api.coingecko.com/api/v3/coins/ethereum/contract/0x514910771af9ca656af840dff83e8264ecf986ca

 

Bonus: Retrieve Coins Data by Symbol or Contract Address

Last but not least, you may have a list of tokens and want to retrieve the market price for each token in the list. You can easily use the /search endpoint to look up coins via a symbol OR the /simple/token_price/{id} endpoint to get the current price of tokens for a given platform, in any supported currency.

The Request URL using the /search endpoint, for Bitcoin (BTC) is:

https://api.coingecko.com/api/v3/search?query=btc

 

Here, we use the /simple/token_price/{id} endpoint where contract address is ‘0x514910771af9ca656af840dff83e8264ecf986ca’ (LINK) and currency is USD:

https://api.coingecko.com/api/v3/simple/token_price/ethereum?contract_addresses=0x514910771af9ca656af840dff83e8264ecf986ca&vs_currencies=usd

 


CoinGecko’s Paid API Plans

If you’re a developer or founder in the midst of building your dApp, you may want to consider getting in touch with our API sales team to enquire about the best API plan that would suit your needs. Fill in the form below to get in touch:

How to Pull Live Crypto & Stocks Prices into Excel

https://www.coingecko.com/learn/crypto-stocks-prices-excel

Having access to real-time price data is crucial for making informed decisions. Unsurprisingly, many financial analysts and traders use Microsoft Excel to stay on top of their portfolio, track stock and crypto performance, asset details, profit and loss, return on investment (ROI), among other metrics. In order to fetch live price data conveniently, we’ll be utilizing Excel’s native Stocks integration feature and CoinGecko’s crypto API.

This step-by-step guide will walk through:

  1. How to import real-time stocks data into Excel
  2. How to import live crypto data into Excel (including coins in trending categories)
  3. Bonus: Setting up a P&L tracker for your investment portfolio

Let’s jump in!

Disclaimer: This guide is prepared for Microsoft Excel users operating on Windows, and Microsoft’s Stocks data is an Office 365 subscription-only feature.


Fetching Real-time Stocks Data in Excel

First, create a new spreadsheet on Microsoft Excel, labeling it ‘Stocks’. On cell A1, make a list of stocks you’re currently holding or that’s on your watchlist. This is the tab where you’ll import all stocks-related data using Excel’s native Data integration.

Once you’re done, select ‘Data’ in the top navigation bar. Highlight the row of stock tickers, and click on ‘Stocks’, which will activate the integration with Microsoft Bing’s database.

Fetch live stocks data in Microsoft Excel

Next, click on the top right icon (‘Insert Data’), and a drop-down menu will appear:

Select all fields you’d like information on – for each selection, a new column will appear and populate accordingly. In this guide, we’ve kept it simple and included just four columns: Ticker, Price, Change, Price Change % and Market Cap.

Importing Live Crypto Data into Excel with CoinGecko API

Moving on to the more exciting part of pulling in live crypto data into Excel – head over to CoinGecko’s API documentation to retrieve the /coins/markets endpoint URL.

CoinGecko Crypto API - /coins/markets coin price data endpoint

/coins/markets‘ is one of the most commonly used crypto API endpoints that traders, developers, projects and analysts rely on to fetch real-time crypto price data. With only two API calls on this endpoint, you can retrieve data of the top 500 cryptocurrencies listed on CoinGecko, which is typically enough for most. To import data for coins beyond the top 500 or smaller market cap cryptocurrencies, simply adjust the ‘Page’ parameters accordingly (each page displays 250 coins data).

Expanding the toggle on /coins/markets, click on ‘Try it out’ and fill in the parameters following the API playground prompts.

Leave the ‘ids’ parameter blank, and add the following inputs:

  • Per_page: 250
  • Page: 1

Customize the other fields according to your preferences. In this example, we’ve included:

  • Vs_currency: usd
  • Order: market_cap_desc (sorts market cap in descending order)
  • Sparkline: true
  • Price_change_percentage: 1h,24h
  • Locale: en
  • Precision: 3

Tip: If you’d like to import coins data for specific crypto categories rather than pulling in a whole list of top 250 or 500 cryptocurrencies, input the respective category id in the ‘category’ parameter field.

In the example below, we’ll look at the ‘telegram-bots’ category.

Click on the ‘Execute’ button to generate the Request URL.

In our example, the Request URL for the top 250 cryptocurrencies is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3

The Request URL for the next 250 cryptocurrencies (top 251 to 500) is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3

As highlighted in yellow above, the key difference between the two Request URLs generated is the page number.

Finally, the Request URL for the Telegram Bots category is:

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&category=telegram-bots&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3
 
Let’s head back to our Excel workbook and navigate to ‘Data’. This time, select ‘From Web’.

Excel Data From Web feature with tool tip

If ‘From Web’ is not available in your navigation bar, you can find it under Data > From Other Sources > From Web. If you’re a Mac OS user on Excel, you can instead rely on CryptoSheets, as this feature is applicable for Windows OS users only.

Upon clicking ‘From Web’, a pop-up window appears. Ensure the toggle is set to ‘Basic’, copy and paste the first Request URL into the input field and select ‘OK’. 

It may take a while to establish a connection to API servers on the Public API plan.

Paid API users may tweak the Request URL to call from the root URL ‘https://pro-api.coingecko.com/api/v3/coins/markets’ and append in their Pro API key at the end of the URL. The URL structure will appear as:

https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=false&price_change_percentage=1h%2C24h&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY
 
Once it’s loaded, Excel’s Power Query Editor appears in a new window. To avoid random query strings, you may choose to rename the query – in this example, we’ve renamed it to ‘coins/markets1-250’.

Click on ‘To Table’ at the top right corner and select ‘OK’. You won’t need to select or enter a delimiter.

Select the expand icon beside Column1 and a drop-down menu will appear. As a default, all columns will be selected. You may also uncheck the box below that states ‘Use original column name as prefix’ so that columns will be cleanly labeled.

Select ‘OK’ and the data will be expanded into various columns. Next, click on ‘Close & Load’ and the data will populate into a new worksheet, which we’ll label as ‘Top 500 Crypto + Categories’ (we’ll be adding in more cryptocurrencies in subsequent steps).

Repeat this process, but this time using the second Request URL with the ‘Page’ parameter set to ‘2’. Instead of closing and loading this query immediately, we’ll import specific crypto categories data in the same manner as well. In this example, we’ve imported Telegram Bots, Real World Assets and Animal Racing categories given their recent rally!

Now toggle back to the first query ‘/coins/markets1-250’ in the Power Query Editor and select ‘Append Queries’.

A dialogue box appears and you can toggle to ‘Three or more tables’ and select the tables to append accordingly. This essentially combines the top 500 crypto data with specific categories (Telegram Bots, Real World Assets and Animal Racing) from the /coins/markets/ API endpoint. Click on ‘OK’ and ‘Close & Load’.

In just a few clicks and under 5 minutes, you now have a real-time, functional Excel workbook importing stocks and crypto price data! Specifically for crypto, you have a consolidated list of top 500 cryptocurrencies data and coins from trending categories you’re watching.

Refreshing the Data

On Excel, you can toggle your preferred refresh frequency by navigating to Data > Refresh All > Connection Properties. Deselect ‘Enable background refresh’ and ‘Refresh every 30 minutes’ if you’d like to manually refresh and conserve the number of API calls made, or opt for a 10-min auto-refresh like in the screenshot below.

Finally, we’ll move on to how you can customize your stocks and crypto portfolio dashboard.

Bonus: Profit & Loss Tracker for Your Investment Portfolio

With real-time stocks and crypto price data being pulled into your Excel spreadsheet, you can easily customize your portfolio tracker based on trading preferences. 

The example below shows a simple and straightforward tracker using the VLOOKUP formula, to extract data from the raw databases Stocks and Top 500 + Categories.

Tip: Use the =IMAGE(“url”) formula to bring the crypto coins images to life!

Add formulas in the gray columns accordingly:

  • Holdings Value – Multiply Holdings # by Current Price. This depicts how much value your stocks and crypto holdings are worth, in fiat currency.
  • P&L – The difference between Holdings Value and Total Invested, essentially your unrealized profit or loss, if the position was closed at that time.
  • ROI % – Divide P&L by Total Invested to derive your Return on Investment, which determines how profitable your investment is. The higher your ROI, the more profitable your investment.

Finally, set up chart visualizations by selecting the relevant cells and navigating to Insert > Recommended Charts. You’re set!

For Day Traders: Recommended Analyst API Endpoints

Advanced traders who want access to more API endpoints, historical prices and avoid getting rate limited, may consider subscribing to our Analyst API plan. The CoinGecko API currently has 50+ endpoints, tracks 10,000+ coins across 700+ exchanges and 3,000+ NFT collections serving billions of API calls each month.

Advanced tracers might also find these useful API endpoints particularly useful:

  • /coins/top_gainers_losers – get the top 30 coins with the largest price gains and losses based on specific time frames
  • /global/market_cap_chart – get historical global market cap and volume data, by no. of days away from now
  • /nfts/markets – track NFT floor prices, market cap and volume

If you require a custom solution, fill in the form below to get in touch with our API sales team:

Create a Crypto Portfolio Tracker on Google Sheets

https://www.coingecko.com/learn/crypto-portfolio-tracker-google-sheets

Especially in a fast-moving space like crypto, it can be overwhelming to stay on top of your investments 24/7. In this article, we’ll be sharing how to build your own real-time portfolio tracker using Google Sheets, so you can manage and track your crypto investments easily. Creating your own custom portfolio will allow you to record and calculate your crypto holdings, analyze crypto price and volume changes, and tailor it to your trading preferences. Investors that trade stocks and other assets may even combine this with existing stocks portfolio trackers.

Regardless of whether you’re a beginner or advanced trader, this detailed guide will walk through:

  • How to set your Google Sheet up for auto-refreshes
  • How to import live crypto data via CoinGecko API (for both Public & Paid API users)
  • How to customize your spreadsheet to calculate crypto holdings, holdings value, and more.
  • The benefits of creating a portfolio tracker on Google Sheets

Let’s get started!


Create a Live Crypto Portfolio Tracker on Google Sheets in 4 Steps

First, create a new spreadsheet on Google Sheets and name it accordingly. This will be your workspace where you’ll input and analyze cryptocurrency data.

Step 1: Import Live Data with App Scripts

Navigate to ‘Extensions’ and select ‘App Script’, where a new tab will appear.

Import Live Crypto Data using Apps Script

On the left panel, select ‘ Editor’ and add a new script using the ‘+’ button. Copy and paste the following importJSON script, and save the script as ‘ImportJSON’. This importJSON script is a versatile one that will allow you to import data in many different ways.

Create a second Apps Script by clicking on the ‘+’ button. Copy the code below and paste it into the script editor, saving it as ‘autoRefresh’ – this will allow your sheet to automatically refresh at fixed intervals.

Your Apps Script editor will now look like this:

auto refresh script on apps editor

Step 2: Automate Data Refreshes

Now that the scripts have been created, select the clock icon on the left to navigate to ‘Triggers’.

Apps Script Triggers

Clicking on ‘+ Add Trigger’ will cause this pop-up to appear. Select the respective dropdowns accordingly:

  • Choose which function to run: triggerAutoRefresh
  • Choose which deployment should run: Head
  • Select event source: Time-driven
  • Select type of time based trigger: Minutes timer
  • Select minute interval: Every 5 or 10 minutes (note: anything less than this may not be useful, as results are cached)

Depending on your preferred frequency, you may also toggle between Hour timer, Day timer, Week timer, and 15 or 30 minute interval triggers.

Trigger autoRefresh Script

Step 3: Import Top 500 Crypto Data with CoinGecko API

CoinGecko tracks over 10,000 cryptocurrencies across 700 exchanges, and is the go-to source for millions of investors globally. Coin rankings are based on market capitalization, so pulling in top 500 cryptocurrencies data would typically be more than sufficient for most investors. Do adjust parameters accordingly if you trade smaller market cap coins!

Head over to our Crypto API documentation, and find the endpoint /coins/markets.

CoinGecko Crypto API - /coins/markets endpoint

Select ‘Try it out’ and fill in the parameters according to respective prompts in the API playground.

As each ‘Page’ tracks 250 coins, we will be importing two pages of coins data via two API calls, to derive the top 500 cryptocurrencies. Leave the ‘ids’ parameter blank, and add the following inputs:

  • Per_page: 250
  • Page: 1

CoinGecko API documentation /coins/markets params

To pull data for smaller cap coins, change the page number accordingly – for instance, you will be importing coins data for coins ranked between #2500 to #3000, with the ‘Page’ parameter inputs of 11 and 12. This will consume two API calls as well.

Obtaining Data for Specific Cryptocurrencies

In the scenario where you want to only retrieve data for a specific list of coins, you can fill in the ‘ids’ parameter with the respective coins’ API IDs – this Token API list, created by the CoinGecko team is particularly helpful. Alternatively, you may search for the specific coin on CoinGecko and copy the API id from individual coin pages. For example, XRP’s API id is ‘ripple’.

Ripple XRP API ID coin id on CoinGecko

Fill in the parameters and finally hit the ‘Execute’ button. The server response and Request URL will be generated accordingly.

Crypto API Request URL

In our example, the Request URL is: 

https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3

Copy this and head back to your worksheet.

Label a new worksheet ‘Top 500 Coins’, as this will serve as your raw database and not your actual crypto portfolio dashboard.

In cell A1, use the following and replace the Request URL accordingly.

=IMPORTJSON(“Request URL”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

The following script will appear:

=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=100&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate”,doNotDelete!$A$1)

 

Why Am I Getting Rate Limited?

Due to Google Sheets rate limits, you may run into an ‘#ERROR’ or are only able to import a small range of data. Google Sheets rely on shared hosting, which means one Google server hosts multiple Google Sheets, and multiple users making requests on the same server share the same API calls per minute limit. This explains why you may get rate limited even when you use only a few API calls.

You can avoid getting rate limited by subscribing to the CoinGecko API Analyst plan. If you’re an existing subscriber and have an API key, use the Pro API root URL (https://pro-api.coingecko.com/api/v3/) and include your API key at the end. This is how the URL structure will appear:

=IMPORTJSON(“https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=1&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate”,doNotDelete!$A$1)

Once the script loads, a list of top 250 cryptocurrencies and its respective price, market cap data will now stream into your spreadsheet up through row 251.

  • /name – coin name
  • /current_price – coin price
  • /market_cap – all market cap details
  • /price_change – 24hr price change
  • /total_volume – 24hr trading volume
  • /high_24h and /low24 – 24hr high and low prices

Crypto Portfolio Tracker - Live market data using Crypto API

To pull the next 250 cryptocurrencies, apply the same formula on cell A252 with a few tweaks, or simply copy and paste the formulas below!

  • Change page number to ‘2’, since we’re now moving on to Page 2 for the top 251-500 cryptocurrencies
  • Add in ‘,noHeaders’ after “noTruncate” – this prevents duplicating headers (as seen in row 1) on row 252.

Public API users:

=IMPORTJSON(“https://api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

Similarly, due to Google Sheets and Public API rate limits, you may only be able to import a limited range of data.

Paid API users:

=IMPORTJSON(“https://pro-api.coingecko.com/api/v3/coins/markets?vs_currency=usd&order=market_cap_desc&per_page=250&page=2&sparkline=true&price_change_percentage=1h%2C24h%2C7d&locale=en&precision=3?&x_cg_pro_api_key=YOUR_API_KEY”,“/name,/current_price,/market_cap,/price_change,/total_volume,/high_24h,/low_24h”,“noTruncate,noHeaders”,doNotDelete!$A$1)

This robust crypto API integration on Google Sheets allows you to easily fetch real-time prices for the top 500 cryptocurrencies on CoinGecko, and the data automatically refreshes every 10 minutes.

We’ll move on to the final step where you can customize your crypto portfolio tracker and dashboard.

Step 4: Configure Your Portfolio Tracker

Now that you have an auto-updating database of the top 500 cryptocurrencies, you can customize your portfolio tracker based on your trading preferences.

Using VLOOKUP, search for the price, market cap, trading volume and % change, based on the Coin Name. In this example, we’ve done a VLOOKUP search of ‘Bitcoin’ in cell B19, cross referencing its price in the Top 500 Coins worksheet.

How to VLOOKUP Crypto Prices on Google Sheets

Since coin price data is indexed on column 2 of our Top 500 Coins database, we enter ‘2’ in the VLOOKUP formula.

How to vlookup based on indexed columns

This method is applied to the rest of the table, returning responding values accordingly.

Finally, create a Portfolio section at the end to track your holdings, calculate holding value and profit and loss (P&L) based on real-time cryptocurrency prices.

Auto Updating Crypto Portfolio Tracker

Create the following row headers:

  • Current Holdings – How much of each coin you currently hold.
  • Current Holding Value (USD) – How much value your crypto holdings is worth, in fiat currency, derived by multiplying Current Holdings by Current Price.
  • Total Invested (USD) – Cost of purchase in fiat currency, for each entry.
  • Unrealized P&L (USD) – The profit or loss that could be realized, if the position were closed at that time.
  • Realized P&L (USD) – The actual profit or loss that has been realized, based on closing positions.
  • ROI % – Return on investment, which evaluates how efficient or profitable your investment is. The higher your ROI, the more profitable your investment is.

Finally, you may want to add data visualizations to your crypto portfolio tracker. Adding a chart and a summary can help to organize and present your crypto investments in an easily digestible way, especially if you have a wide range of crypto assets in your basket.

Create a Crypto Portfolio Tracker using CoinGecko API

As you continue to invest in crypto, you’ll need to update your portfolio tracker with new purchase records and remove outdated ones. While calculating trading profits and losses hasn’t been fully covered in this article, we’ll be creating a step-by-step guide soon on how to automate P&L – both unrealized, realized, ROI and more.

Here’s the final Crypto Portfolio Tracker on Google Sheets:

Crypto Portfolio Dashboard Tracker Example

What are the Benefits of Tracking Your Crypto Portfolio on Google Sheets?

Tracking your crypto portfolio on Google Sheets allows you to easily analyze data with charts, pivot tables and formulas across any device, any time. Having auto-refreshing crypto price data also ensures  you have an accurate view of all your investments at all times. Investors who diversify their portfolios across crypto, stocks and other assets and are working off Google Sheets, will find it extremely convenient to consolidate and customize all asset holdings in a single, master dashboard.

For Advanced Traders: Useful CoinGecko API Endpoints

Here are some useful API endpoints that advanced traders might find particularly useful:

  • /coins/top_gainers_losers – get the top 30 coins with the largest price gains and losses based on specific time frames
  • /global/market_cap_chart – get historical global market cap and volume data, by no. of days away from now
  • /nfts/markets – track NFT floor prices, market cap and volume

If you’re an advanced trader and want access to more comprehensive data, historical prices and bypass rate limits, you may want to consider subscribing to an Analyst API plan. Alternatively, if you require a custom solution, fill in the form below to get in touch with our API sales team:

 


Credits & Acknowledgements

  • importJSON script by Brad Jasper and Trevor (Github)
  • triggerAutoRefresh script by Andrea Borruso (Github)