CoinGecko Prices, Volumes, Market Caps in Google Sheets and Excel

NEW COINGECKO_V2
Youtube video

For anyone looking to extract automatically data from CoinGecko.

No coding skills are required, made for non developers looking to retrieve cryptocurrency prices, volumes, market-caps… from any currency.

First step is to access the following template sheet to see how the formulas are used in Google Sheets.

TEST GOOGLE SHEET EXAMPLE HERE

Second step, I’ll describe the use of each function.

COINGECKO- DEX PRICES - READ ONLY - DUPLICATE FOR EDIT

Cryptocurrency Prices

If you are looking to retrieve the latest price for any cryptocurrency, you will need to use the GECKOPRICE() formula. It will get you the prices you see on the main CoinGecko page (see ref image).

https://www.coingecko.com/en

The formula will import CoinGecko’s cryptocurrency prices into Google spreadsheets. By default, data gets transformed into a number so it looks more like a normal price data import.

For example: =GECKOPRICE(“BTC”, “USD”)

Syntax: GECKOPRICE(ticker, currency,[optional refresh attribute])

1st parameter: the cryptocurrency ticker array you want the price from, example BTC. 2nd parameter: the fiat currency ex: USD, EUR, GPB etc… 3rd parameter an optional fixed cell for automatic refresh of the data, this parameter

Geckoprice for an array of ticker, default result in USD

Geckoprice for an array of ticker, PRICE in GBP specified as second element in formula

If you request a ticker in the following form:
TICKER/CURRENCY

Example: BTC/EUR, ETH/CHF, XTZ/GBP the algorithm won’t take into account the default currency and will get the Euro price for BTC, the CHF price for ETH, and the GBP price for XTZ.

Cryptocurrency Volumes

If you are looking to retrieve the latest volumes for any cryptocurrency, you will need to use the GECKOVOLUME() formula. It will get you the latest 24h volume (see ref image for BTC example).

https://www.coingecko.com/en/coins/bitcoin

Like the price formula, the volume formula will import CoinGecko’s 24h volume into your spreadsheet.

For example: =GECKOVOLUME(“BTC”, “USD”)

Syntax: GECKOVOLUME(ticker, currency,[optional refresh attribute])

1st parameter: the cryptocurrency ticker you want the 24h volume from, example BTC. 2nd parameter: the fiat currency ex: USD, EUR, GPB etc… 3rd parameter an optional fixed cell for automatic refresh of the data, this parameter

GECKOVOLUME for an array of ticker, VOLUME in EUR specified as second element in formula

If you request a ticker in the following form:
TICKER/CURRENCY

Example: BTC/EUR, ETH/CHF, XTZ/GBP the algorithm won’t take into account the ticker currency and will get the volumes in the currency you specified by the second parameter or the default currency USD.

Cryptocurrency Market Capitalizations

If you are looking to retrieve the latest market capitalizations for any cryptocurrency, you will need to use the GECKOCAP() formula. It will get you the latest market cap (see ref image for BTC example).

https://www.coingecko.com/en/coins/bitcoin

For example: =GECKOCAP(“BTC”, “USD”)

Syntax: GECKOCAP(ticker, currency,[optional DILUTED attribute],[optional refresh attribute])

1st parameter: the cryptocurrency ticker you want the market cap from, example BTC. 2nd parameter: the fiat currency ex: USD, EUR, GPB etc… 3rd parameter an optional boolean to get fully diluted valuation 4th parameter an optional fixed cell for automatic refresh of the data, this parameter

GECKOCAP for an array of tickers, market capitalization by default USD

For Diluted MarketCaps:

GECKOCAPDILUTED for an array of tickers, market capitalization by default USD

If the ticker has no diluted market capitalization it will return FALSE or Coin Not in List. This can be modified by request, please contact: https://t.me/TheCryptoCurious

If you request a ticker in the following form:
TICKER/CURRENCY

Example: BTC/EUR, ETH/CHF, XTZ/GBP the algorithm won’t take into account the ticker currency and will get the volumes in the currency you specified by the second parameter or the default currency USD.

Cryptocurrency % Change Price, Volume and Market Capitalization

If you are looking to retrieve the latest % change on price, volume or market capitalization for any cryptocurrency, you will need to use the GECKOCHANGE() formula. It will get you the latest percent change for any time period in terms of days.

Syntax: GECKOCHANGE(ticker, ticker2, type, nb_days, [optional refresh attribute])

24H Price Change

GECKO24HPRICECHANGE function gets you the 24h price % change. You can set as the second parameter other fiat currency like EUR.

10Day volume change on BTC/EUR

=GECKOCHANGE(“BTC”, “EUR”, “VOLUME”, 10)

7Day market cap change on BTC/LTC

=GECKOCHANGE(“BTC”, “LTC”, “MARKETCAP”, 7)

Cryptocurrency Chart Sparkline Price, Volume and Market Capitalization

If you are looking to retrieve the price, volume or market capitalization chart for any cryptocurrency, you will need to use the SPARKLINE(GECKOCHART()) formula. It will get you the chart in a cell for any time period in terms of days.

Syntax: SPARKLINE(GECKOCHART(ticker, ticker2, type, nb_days, [optional refresh attribute]))

=SPARLINE(GECKOCHART(“BTC”,”USD”,”volume”,7)) gets the volume chart of btc/usd for 7 days (cell C40)

Prices for cryptocurrencies with the same Ticker

In some cases cryptocurrencies have exactly the same ticker, for these case you can use the GECKOPRICEBYNAME() formula. It will get you the latest price for coins with the same ticker but different names (see ref image/gif for BTCV example).

Same ticker, two different coins BTCV -> Bitcoin Vault vs BitcoinV

Syntax: GECKOPRICEBYNAME(id_coin, currency,[optional refresh attribute])

1st parameter: the id name of cryptocurrency ticker found in web address of Coingecko 2nd parameter: the fiat currency ex: USD, EUR, GPB etc… 3rd parameter an optional fixed cell for automatic refresh of the data, this parameter

The id_coin of cryptocurrency ticker can be found in CoinGecko’s web address as shown on the above pictures. If the web address id is not functioning please refer to the id of your project found here.

Example for BitcoinV, Coingecko’s Web address is:
https://www.coingecko.com/en/coins/bitcoinv/usd

Example for Bitcoin Vault, Coingecko’s Web address is:
https://www.coingecko.com/en/coins/bitcoin-vault/usd

GIF below shows how you get the price of these two coins:

=GECKOPRICEBYNAME(“bitcoinv”, “USD”,”$A$1") OR =GECKOPRICEBYNAME(“bitcoin-vault”, “USD”,”$A$1")

Cryptocurrency Historical Price, Volume and Market Capitalization

If you are looking to retrieve the a historical close/open price, volume or market capitalization on a specified date for any cryptocurrency, you will need to use the GECKOHISTBYDAY() formula.

Syntax: GECKOHISTBYDAY(ticker, ticker2, type, date, [optional refresh attribute])

1st parameter: the cryptocurrency ticker 2nd parameter: the fiat currency ex: USD 3rd parameter the type of data : “price”, “volume” and “marketcap”cell 4th parameter the historical date format dd-mm-yyyy 5th parameter an optional fixed cell for automatic refresh of the data, this parameter

The format of the historical date has to be dd-mm-yyyy

Historical Open -Close price of BTC/USD on 31-12-2020

Left Image Open Price of BTCUSD on 31–12–2020 — Right image Close Price of BTCUSD on 31–12–2020

IMPORTANT: On Coingecko, the Close Price of any currency corresponds to the Open Price of the following day as shown on the below image:

CoinGecko Historical Data

Historical Volume & Market Cap of BTCUSD on 31–12–2020

Historical Volume & Market Cap of BTCUSD on 31–12–2020

For Specific data points like highs, lows, ranks…

If you are looking to retrieve the current data, for example 24h low, cryptocurrency ranking, scores, 7d price change, all time lows etc … for any cryptocurrency including exchange tickers, you will need to use the GECKO_ID_DATA() formula. It will get you the data you see CoinGecko API documentation page see ref image below.

https://www.coingecko.com/api/documentations/v3#/coins/get_coins__id_

I’ve created this function for users that need very specific data points. It works a little differently because it’s a bit more abstract in terms of parameters. I will show you different examples in order for you to get a grasp of it.

Syntax: GECKO_ID_DATA(ticker, parameter, optional by_ticker=true)

1st parameter: the cryptocurrency ticker 2nd parameter: the parameter separated by "/" ex: "market_data/ath/usd" 3rd parameter an optional true (data by ticker) false (data by id_name) 4th parameter an optional fixed cell for automatic refresh of the data, this parameter

If you are looking for a coin where the ticker doesn’t work you can directly specify the id_name of the coin and you would need to reference at the end of the formula false to signal to the function you are not using the ticker but the id_name of the coin.

7D PRICE % CHANGE

The second parameter refers to the data you are looking for, in case of 7 price change in USD -> “market_data/price_change_percentage_7d_in_currency/usd”, if you were looking for the EUR value then -> “market_data/price_change_percentage_7d_in_currency/eur” .

Left: =GECKO_ID_DATA(“LTC”,”market_data/price_change_percentage_7d_in_currency/usd”) — — — — Right: =GECKO_ID_DATA(“uniswap”,”market_data/price_change_percentage_7d_in_currency/usd”,false)

If you need help figuring the parameter you need to use, don’t hesitate to drop a message to https://t.me/TheCryptoCurious

24H Low and Highs

GECKO24HIGH and GECKO24LOW functions get you the 24h price low and high by default in USD but you can set as the second parameter other fiat currency like EUR.

All Time Highs and Lows

GECKO24ATH and GECKO24ATL functions get you the all time high and all time low prices, in this case in JPY

Rankings

GECKORANK function get you the ranking of any cryptocurrency

Logos

Exchange rates

All of these formulas can be valued in a multitude of fiat currencies, cryptocurrencies and commodities. Some of these options are shown below.

Example: get Bitcoin’s market capitalization in Euros

For example: =GECKOCAP(“BTC”, “EUR”)

Refresh All Data at once

All you need to do is add a 3rd parameter as an optional fixed cell for automatic refresh of the data as shown below.

3rd parameter cell $C$9

More available Formulas ?

There are plenty more functionalities that can be added through the CoinGecko API. Don’t hesitate to have a look at all available end points like:

• coins
• contracts
• exchanges
• history
• etc…

If you are interested in getting some help in integrating more personalized formulas, DM me.

https://www.coingecko.com/en/api

Github Open-Source Setup

The integration process is completely open sourced in Github, if you wish to set it up on your own and verify the code for yourself, please refer to the following Medium article. As for the Excel beta version, don’t hesitate to contact me in private DM in order to test it out.

Conclusion

Google Sheet CoinGecko formulas:

• GECKOPRICE()
• GECKOVOLUME()
• GECKOCAP()
• GECKOCHANGE()
• GECKOPRICEBYNAME()

ALL functions are available for use here!

COINGECKO- DEX PRICES - READ ONLY - DUPLICATE FOR EDIT

Available in Excel too, please contact me for more information.

This is ongoing work. If you find errors, please do not hesitate to let me know. Feedback is very welcomed. A telegram chat is also available for support. If this project adds any value to you and/or are looking for personalized coding on your Google Sheets, don’t hesitate to leave a message.

Thank you so much for investing your time in reading this article.

Take care of yourself & your family in these challenging times!