Note: Since Dec 4th 2018, CoinMarketcap shut down their free API and updated their API terms of usage. CRYPTOFINANCE had to buy a commercial license to keep distributing their data.
CoinMarketcap data remain available for free, but a quota of 50 calls per day per user has been set. A plan for unlimited data is available and helps fund the CRYPTOFINANCE project.
Full Listing
Note: This CoinMarketcap full listing is only available under the Data Availability Plan.
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:
MATCH
will search for bitcoin in the sheetcryptodata
on the columnA
and return the matching row number.INDEX
will look at thecryptodata
sheet and return the value that is at the row where bitcoin is, and at the column5
. Column5
is where the price is, you can change it to8
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")
Get Market Specific Data
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.
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.
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")
.
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.
Circulating Supply
Call the function =CRYPTOFINANCE("XXX/YYY", "circulating_supply")
to get the circulating supply for the currency XXX
in the currency YYY
. By default if YYY
is not provided USD
will be used.
Examples:
=CRYPTOFINANCE("BTC", "circulating_supply")
will return the total supply of Bitcoin in US Dollar. Similar to calling =CRYPTOFINANCE("BTCUSD", "circulating_supply")
.
* =CRYPTOFINANCE("BTC/EUR", "circulating_supply")
will return the total supply of Bitcoin in Euro.
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.
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.
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).
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.
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 total marketcap:
=CRYPTOFINANCE("GLOBAL", "btc_dominance")