Full listing

Create a new sheet, name it cryptodata, then on cell A1 type the formula:

=CRYPTOFINANCE("COINMARKETCAP")

This will create as many rows as there are coins tracked on CoinMarketCap, over 1,600 as of now.
From there you can just reference these data from your portfolio sheet like this (example showing Bitcoin price):

=cryptodata!E2

Note that the order of coins may change at each refresh, as they are sorted by marketcap. Here is how to make your cell references sticky:

=INDEX(cryptodata!A1:Z, MATCH("bitcoin",cryptodata!A1:A,0), 5)

Here is how it works, in order:

  1. MATCH will search for bitcoin in the sheet cryptodata on the column A and return the matching row number.
  2. INDEX will look at the cryptodata sheet and return the value that is at the row where bitcoin is, and at the column 5. Column 5 is where the price is, you can change it to 8 for marketcap info, etc.

Show prices in another currency

You can show the prices in a 3rd currency (in addition to USD and BTC), examples:

  • With Ether: =CRYPTOFINANCE("COINMARKETCAP", "ETH")
  • With South African Rand: =CRYPTOFINANCE("COINMARKETCAP", "ZAR")

Show only top N coins by marketcap

You can show only the top N coins by marketcap with the 3rd argument:

  • Top 10: =CRYPTOFINANCE("COINMARKETCAP", "", 10)
  • Top 100 with Ether as 3rd currency: =CRYPTOFINANCE("COINMARKETCAP", "ETH", 100)

Specific market data

Get exchange rates

Call the function with =CRYPTOFINANCE("XXX/YYY") where XXX is the origin currency symbol (eg. BTC , ETH, DASH , etc.) and YYY is the destination currency (eg. USD , EUR , GBP , etc.)

Examples:

  • =CRYPTOFINANCE("BTC/USD") returns the current rate of Bitcoin in US Dollar, same thing as calling =CRYPTOFINANCE("BTC/USD", "price").
  • =CRYPTOFINANCE("ETC/EUR") returns the current rate of Ethereum Classic in Euro, same thing as calling =CRYPTOFINANCE("ETC/EUR", "price").
  • =CRYPTOFINANCE("DASH/CAD") returns the current rate of Dash in Canadian Dollar, same thing as calling =CRYPTOFINANCE("DASH/CAD", "price").
  • =CRYPTOFINANCE("BTC/BTC") is useless and returns its input, 1 BTC = 1 BTC.

Get marketcap

Call the function =CRYPTOFINANCE("XXX/YYY", "marketcap") to get the marketcap of the XXX currency in the YYY currency. By default if YYY is not provided USD will be used.

Examples:

  • =CRYPTOFINANCE("BTC", "marketcap") will return the Bitcoin marketcap in US Dollar. This is similar to calling =CRYPTOFINANCE("BTC/USD", "marketcap") .
  • =CRYPTOFINANCE("ETH/EUR", "marketcap") will return the Etherum marketcap in Euro.

Get 24h volume

Call the function =CRYPTOFINANCE("XXX/YYY", "volume") to get the volume in the last 24 hours for the currency XXX in the currency YYY . By default if YYY is not provided USD will be used.

Examples:

  • =CRYPTOFINANCE("LTCEUR", "volume") will return the LiteCoin 24h volume in Euro.
  • =CRYPTOFINANCE("BTC/USD", "volume") will return the Bitcoin 24h volume in US Dollar. Similar to calling =CRYPTOFINANCE("BTC", "volume") .

Get total supply

Call the function =CRYPTOFINANCE("XXX/YYY", "total_supply") to get the total supply for the currency XXX in the currency YYY . By default if YYY is not provided USD will be used.

Examples:

  • =CRYPTOFINANCE("BTC", "total_supply") will return the total supply of Bitcoin in US Dollar. Similar to calling =CRYPTOFINANCE("BTC/USD", "total_supply") .
  • =CRYPTOFINANCE("BTC/EUR", "total_supply") will return the total supply of Bitcoin in Euro.

Get available supply

Call the function =CRYPTOFINANCE("XXX/YYY", "available_supply") to get the available supply for the currency XXX in the currency YYY . By default if YYY is not provided USD will be used.

Examples:

=CRYPTOFINANCE("BTC", "available_supply") will return the total supply of Bitcoin in US Dollar. Similar to calling =CRYPTOFINANCE("BTCUSD", "available_supply") . * =CRYPTOFINANCE("BTC/EUR", "available_supply") will return the total supply of Bitcoin in Euro.

Get maximum supply

Call the function =CRYPTOFINANCE("XXX", "max_supply") to get the maximum supply for the currency XXX.

Example:

  • =CRYPTOFINANCE("BTC", "max_supply") will return the maximum supply of Bitcoin, which is 21000000.0.

Get change percentage

Call the function=CRYPTOFINANCE("XXX", "change", "PERIOD") to get the percentage change over the period PERIOD in USD. PERIOD can take 3 values: 1h (1 hour), 24h (24 hours) and 7d (7 days). If no PERIOD argument is given, the default is 24h .

Examples:

  • =CRYPTOFINANCE("BTC", "change") will return the change percentage of Bitcoin over the last 24 hours in USD. Similar to calling =CRYPTOFINANCE("BTC", "change", "24h")
  • =CRYPTOFINANCE("ETH", "change", "1h") will return the change percentage of Ethereum over the last hour in USD.
  • =CRYPTOFINANCE("DASH", "change", "7d") will return the change percentage of Dash over the last 7 days in USD.

Get currency rank

Call the function =CRYPTOFINANCE("XXX", "rank") to get the currency XXX rank, based on its marketcap, as returned by Coinmarketcap.

Examples:

=CRYPTOFINANCE("BTC", "rank") will return 1 (as of May 2017). This means Bitcoin has the biggest marketcap among all crypto-currencies (according to Coinmarketcap).

Get currency full name

Call the function =CRYPTOFINANCE("XXX/USD", "name") to get the currency XXX full name, as returned by Coinmarketcap.

Examples:

  • =CRYPTOFINANCE("BTC/USD", "name") will return Bitcoin.
  • =CRYPTOFINANCE("BCC/USD", "name") will return BitConnect.
  • =CRYPTOFINANCE("BCH/USD", "name") will return Bitcoin Cash.

Get global statistics

Use the special symbol GLOBAL to get aggregated statistics across all crypto-currencies. Available statistics and how to call CRYPTOFINANCE is as follow:

  • Total coin marketcap: =CRYPTOFINANCE("GLOBAL", "total_marketcap") (returned in US Dollar)
  • Total 24h volume: =CRYPTOFINANCE("GLOBAL", "total_24h_volume") (returned in US Dollar)
  • Bitcoin percentage of marketcap: =CRYPTOFINANCE("GLOBAL", "bitcoin_percentage_of_marketcap")
  • Number of active crypto-currencies: =CRYPTOFINANCE("GLOBAL", "active_currencies")
  • Number of active markets: =CRYPTOFINANCE("GLOBAL", "active_markets")