пятница, 1 сентября 2017 г.

How to get crypto-currencies rates and more in Google Sheets

How to get crypto-currencies rates and more in Google Sheets
The CRYPTOFINANCE function on Google Sheets.

Join CRYPTOFINANCE Telegram group. A place to ask questions, share usage tips and propose cool features.


Raison d’être


Keeping track of your crypto-currency portfolio can be challenging. There are a few services out there, but nothing beats the flexibility of a spreadsheet.


Google Sheets is a very convenient spreadsheet tool. But while we can get the current Bitcoin exchange rate with theGOOGLEFINANCE("BTCUSD") function, other crypto-currencies and information (market cap, volume, etc) are not available.


The CRYPTOFINANCE() function, available as a Google Sheets Add-On, uses the coinmarketcap API to returns the rates, market cap, volume, change, total supply and rank.


I announced it on Twitter and people started to like it and asking for more features.


The initial announcement Tweet.

1. Go to the “Add-ons” menu, and click on “Get add-ons”.


2. On the Add-ons panel, search for “cryptofinance”, click on “+ FREE” to install it.


3. Choose under which account you want to install the Add-on.


4. CRYPTOFINANCE needs to connect to an external API and customize your sheet menu, click on “Allow”.


5. Make sure the add-on is activated in your sheet:


  1. Go to Add-on > CRYPTOFINANCE > Help
  2. Click on View in store , then click on Manage and check Use in this document:

And that’s it! From there the =CRYPTOFINANCE() function is available on any new spreadsheet you create.


Get all of CoinMarketCap data in a single sheet:


Created 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, about 1,300 as of now. From there you can just reference these data from your portfolio sheet like this:


=cryptodata!E2 (this will show BTC price)


Note that the order of coins may change at each refresh, as they are sorted by marketcap. Here is how to make your references so that they are 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.

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")

You can show only the top N coin (by marketcap) with the 3rd argument:


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

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


  • All origin currencies are available (over 1,200), as listed on CoinMarketCap Currency listing.
  • 31 fiat destination currencies are available, along with Bitcoin and Ethereum, as a destination currency: AUD, BRL, CAD, CHF, CLP, CNY, CZK, DKK, EUR, GBP, HKD, HUF, IDR, ILS, INR, JPY, KRW, MXN, MYR, NOK, NZD, PHP, PKR, PLN, RUB, SEK, SGD, THB, TRY, TWD, USD, ZAR.

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 market cap


Call the function =CRYPTOFINANCE("XXX/YYY", "marketcap") to get the market cap 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 market cap in US Dollar. This is similar to calling =CRYPTOFINANCE("BTC/USD", "marketcap") .
  • =CRYPTOFINANCE("ETH/EUR", "marketcap") will return the Etherum market cap 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 market cap, as returned by Coinmarketcap.


Examples:


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

Get currency full name


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


Examples:


  • =CRYPTOFINANCE("BTC", "name") will return Bitcoin.
  • =CRYPTOFINANCE("BCC", "name") will return BitConnect.
  • =CRYPTOFINANCE("BCH", "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 market cap: =CRYPTOFINANCE("GLOBAL", "total_marketcap") (returned in US Dollar)
  • Total 24h volume: =CRYPTOFINANCE("GLOBAL", "total_24h_volume") (returned in US Dollar)
  • Bitcoin percentage of market cap: =CRYPTOFINANCE("GLOBAL", "bitcoin_percentage_of_marketcap")
  • Number of active crypto-currencies: =CRYPTOFINANCE("GLOBAL", "active_currencies")
  • Number of active assets: =CRYPTOFINANCE("GLOBAL", "active_assets")
  • Number of active markets: =CRYPTOFINANCE("GLOBAL", "active_markets")

How to refresh rates


Your sheet should auto-refresh each time you open it. But this is rather uncertain and will not refresh if the time interval between each open is too short. Once open your sheet should refresh (again, not 100% sure) about each 2min for about 10min. These behaviours are Google Sheets defaults and CRYPTOFINANCE has no control over them.


Here is how to setup a manual refresh trigger for your sheet:


  1. Input the number 0 into cell A1
  2. Make the 4th argument to all your CRYPTOFINANCE function call a locked reference to cell A1. If you don’t have 2nd or 3rd arguments, just make them an empty string "" .
  3. Change the content of the cell A1 to refresh the rates. This works because Google Sheets will see the function CRYPTOFINANCE has an argument (the 4th) that changed and will force the refresh.

For example:


If your function call was:

=CRYPTOFINANCE("KRAKEN:BTC/EUR")

your function call becomes:

=CRYPTOFINANCE("KRAKEN:BTC/EUR", "", "", $A$1)


If your function call was:

=CRYPTOFINANCE("BTC/USD", "change")

your function call becomes:

=CRYPTOFINANCE("BTC/USD", "change", "", $A$1)


Tips: Make the content of A1 a function that update itself reasonably regularly, like =GOOGLEFINANCE("USDEUR"), to have the sheet always up to date.


Get historical price data


Extending from price syntax, you can get a pair the market average price at a historical date with the following syntax:


=CRYPTOFINANCE("BTC/USD", "price", "2014-12-25")


The last argument, the date, must be a 10 characters long string following the format YYYY-MM-DD. Where the year is written in 4 digits, then the month in 2 digits (add a padding zero for months January to September), then the day in 2 digits (add a padding zero for 1st to 9th). Each group being separated by a single hyphen -.


If no price data is available for the date, 0 is returned. The prices returned are the close prices at end of day GMT, or end of hour if specified.


You can prepend an exchange name, to return not the market average but this exchange price. 77 exchanges are supported, you can find the list here, along with their supported pairs.


Example getting Kraken BTC/USD price for November 2nd 2015:


=CRYPTOFINANCE("KRAKEN:BTC/USD", "price", "2015-11-02")


Using Google Sheets TODAY() function:


=CRYPTOFINANCE("KRAKEN:BTC/USD", "price", TEXT(TODAY(), "yyyy-mm-dd"))


With a specific time:


=CRYPTOFINANCE("KRAKEN:BTC/USD", "price", "2015-11-02@15:00")


Only rounded hour timestamp will work. Specifying a minute other than 00 will almost always return the same as 00. The time is set on GMT timezone.


Referencing cells to build the timestamp:


Say A1 contains BTC , A2 contains 2016–10–22 and A3 contains 15:00.


First make sure to format A2 and A3 in plain text, go to Format > Number > Plain text to do this. Then, call:


=CRYPTOFINANCE(A1 & "/USD", "price", A2 & "@" & A3)


Other data are available: open, high, low, close and volume info:


=CRYPTOFINANCE("KRAKEN:BTC/USD", "open", "2015-11-02@19:00")


=CRYPTOFINANCE("KRAKEN:BTC/USD", "high", "2015-11-02@19:00")


=CRYPTOFINANCE("KRAKEN:BTC/USD", "low", "2015-11-02@19:00")


=CRYPTOFINANCE("KRAKEN:BTC/USD", "close", "2015-11-02@19:00"), same as =CRYPTOFINANCE("KRAKEN:BTC/USD", "price", "2015-11-02@19:00").


=CRYPTOFINANCE("KRAKEN:BTC/USD", "volume_from", "2015-11-02@19:00")


=CRYPTOFINANCE("KRAKEN:BTC/USD", "volume_to", "2015-11-02@19:00")


Get blockchain info for Bitcoin


Get recommended transaction fees


All returned values are in satoshis per byte. Data are from the Earn.com Bitcoinfees API.


Fastest: =CRYPTOFINANCE("BLOCKCHAIN:BTC", "fee") , same as calling =CRYPTOFINANCE("BLOCKCHAIN:BTC", "fee", "fastest").


Half-hour: =CRYPTOFINANCE("BLOCKCHAIN:BTC", "fee", "half_hour") . The fee that will confirm a transaction within half an hour (with 90% probability).


Hour: =CRYPTOFINANCE("BLOCKCHAIN:BTC", "fee", "hour"). The fee that will confirm a transaction within an hour (with 90% probability).


Get current data from a specific exchange


Note: See above “Get historical price data” for exchange specific historical data


In a nutshell, call =CRYPTOFINANCE("KRAKEN:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the Kraken public API. All ticker information available can be retrieved.


In the following examples, XXX is the origin currency symbol (eg. BTC , ETH , DASH , etc.) and YYY is the destination currency (eg. USD , EUR , GBP , etc.).


Only currencies traded on Kraken are available. Both XBT and BTC symbols can be used.


  • Get a currency pair exchange rates

Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "ask") to get the ask price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "bid") to get bid price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "open") to get today’s opening price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "closed") to get the last trade closed price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "low", "today") to get today’s lowest price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "low", "last_24h") to get the lowest price over the last 24 hours.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "high", "today") to get today’s height price.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "high", "last_24h") to get the highest price over the last 24 hours.


  • Get a currency pair volume data

Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "ask", "whole_lot_volume") to get the current ask price whole lot volume.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "ask", "lot_volume") to get the current ask price lot volume.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "bid", "whole_lot_volume") to get the current bid price whole lot volume.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "bid", "lot_volume") to get the current ask price lot volume.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "volume", "today") to get the volume since the day started (midnight past 1 second).


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "volume", "last_24h") to get the volume over the last 24 hours.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "weighted_volume", "today") to get the volume since the day started (midnight past 1 second).


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "weighted_volume", "last_24h") to get the volume over the last 24 hours.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "closed", "lot_volume") to get the last trade closed lot volume.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "nb_trade", "today") to get the number of trades today.


Call =CRYPTOFINANCE("KRAKEN:XXX/YYY", "nb_trade", "last_24h") to get the number of trades over the last 24 hours.


In a nutshell, call =CRYPTOFINANCE("BITTREX:DASH/BTC") to get the current Dash price in Bitcoin.

All information are returned from the Bittrex public API. All 9 tickers information can be retrieved: ask (default), bid, last, high, low, volume, base_volume, open_buy_orders and open_sell_orders.


Examples:


  • =CRYPTOFINANCE("BITTREX:DASH/BTC") will return Dash price (ask price) in Bitcoin. Same thing as calling =CRYPTOFINANCE("BITTREX:DASH/BTC", "ask")
  • =CRYPTOFINANCE("BITTREX:ARK/BTC", "bid") will return Ark bid price in Bitcoin.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "last") will return Monero last price in Bitcoin.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "high") will return Monero highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "low") will return Monero lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "volume") will return Monero volume (in XMR) over the last 24 hours.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "base_volume") will return Monero volume (in BTC) over the last 24 hours.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "open_sell_orders") will return the number of open sell orders for the market BTC-XMR.
  • =CRYPTOFINANCE("BITTREX:XMR/BTC", "open_buy_orders") will return the number of open buy orders for the market BTC-XMR.

Note: Bittrex market names are a reverse of the pair name. The market labeled BTC-DASH on Bittrex website will use the DASH/BTC ticker in CRYPTOFINANCE .


In a nutshell, call =CRYPTOFINANCE("LIQUI:ETH/BTC") to get the current Ethereum price in Bitcoin.

All information are returned from the Liqui.io ticker public API. All 8 ticker information can be retrieved: ask (sell, default), bid (buy), last, avg, high, low, volume, volume_in_currency.


Examples:


  • =CRYPTOFINANCE("LIQUI:ETH/BTC") will return Ethereum price (ask, sell price) in Bitcoin. Same thing as calling =CRYPTOFINANCE("LIQUI:ETH/BTC", "ask")
  • =CRYPTOFINANCE("LIQUI:STX/ETH", "bid") will return Stox bid price in Ethereum.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "last") will return Stox last price in Bitcoin.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "avg") will return Stox average price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "high") will return Stox highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "low") will return Stox lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "volume") will return Stox volume in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("LIQUI:STX/BTC", "volume_in_currency") will return Stox volume in Stox over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("BINANCE:NEO/BTC") to get the current NEO price in Bitcoin.

All information are returned from the Binance ticker public API. All 9 ticker information can be retrieved: ask (sell, default), bid (buy), open, high, low, last,volume, change.


Examples:


  • =CRYPTOFINANCE("BINANCE:NEO/BTC") will return NEO price (ask, sell price) in Bitcoin. Same thing as calling =CRYPTOFINANCE("BINANCE:NEO/BTC", "ask")
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "bid") will return NEO bid price in Bitcoin.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "open") will return NEO open price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "high") will return NEO highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "low") will return NEO lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "last") will return NEO last price in Bitcoin.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "quote_volume") will return NEO volume in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "volume") will return NEO volume in Neo over the last 24 hours.
  • =CRYPTOFINANCE("BINANCE:NEO/BTC", "change") will return NEO change percentage over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("KUCOIN:KCS/BTC") to get the current KCS price in Bitcoin.

All information are returned from the Kucoin ticker public API, 6 ticker information can be retrieved: ask (sell, default), bid (buy), high, low, last andvolume.


Examples:


  • =CRYPTOFINANCE("KUCOIN:KCS/BTC") will return KCS price (ask, sell price) in Bitcoin. Same thing as calling =CRYPTOFINANCE("KUCOIN:KCS/BTC", "ask")
  • =CRYPTOFINANCE("KUCOIN:KCS/BTC", "bid") will return KCS bid price in Bitcoin.
  • =CRYPTOFINANCE("KUCOIN:KCS/BTC", "high") will return KCS highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("KUCOIN:KCS/BTC", "low") will return KCS lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("KUCOIN:KCS/BTC", "last") will return KCS last price in Bitcoin.
  • =CRYPTOFINANCE("KUCOIN:KCS/BTC", "volume") will return KCS volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("LUNO:XBT/ZAR") to get the current Bitcoin price in South African Rand.

All information are returned from the Luno ticker public API. All 4 ticker information can be retrieved: ask (sell, default), bid (buy), last and volume.


All markets supported, make sure to denote Bitcoin with XBT, not BTC.


Examples:


  • =CRYPTOFINANCE("LUNO:XBT/ZAR") will return Bitcoin price (ask, sell price) in South African Rand, similar to=CRYPTOFINANCE("LUNO:XBT/ZAR", "ask").
  • =CRYPTOFINANCE("LUNO:XBT/ZAR", "bid") will return Bitcoin bid price in South African Rand.
  • =CRYPTOFINANCE("LUNO:XBT/ZAR", "last") will return Bitcoin last trade price in South African Rand.
  • =CRYPTOFINANCE("LUNO:XBT/ZAR", "volume") will return Bitcoin volume in South African Rand over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("GEMINI:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the Gemini ticker public API. All 5 ticker information can be retrieved: ask (sell, default), bid (buy), last, volumeand base_volume.


All 3 trading symbols are available: BTC/USD, ETH/USD, ETH/BTC.


Examples:


  • =CRYPTOFINANCE("GEMINI:BTC/USD") will return Bitcoin price (ask, sell price) in US Dollar, similar to=CRYPTOFINANCE("GEMINI:BTC/USD", "ask").
  • =CRYPTOFINANCE("GEMINI:BTC/USD", "bid") will return Bitcoin bid price in US Dollar.
  • =CRYPTOFINANCE("GEMINI:BTC/USD", "last") will return Bitcoin last trade price in US Dollar.
  • =CRYPTOFINANCE("GEMINI:BTC/USD", "volume") will return Bitcoin volume in US Dollar.
  • =CRYPTOFINANCE("GEMINI:BTC/USD", "base_volume") will return Bitcoin volume in Bitcoin.

In a nutshell, call =CRYPTOFINANCE("BITHUMB:BTC") to get the current Bitcoin price in Korean Republic Won (KRW).

All information are returned from the Bithumb ticker public API. All 9 ticker information can be retrieved: ask (sell, default), bid (buy), avg, high, low, open, close, volume over 24h and 7d periods.


There is no need to specify the destination currency, as Bithumb only supports KRW. If provided it will be ignored and the result will still be denominated in KRW.


Examples:


  • =CRYPTOFINANCE("BITHUMB:BTC") will return Bitcoin price (ask, sell price) in KRW. Same thing as calling =CRYPTOFINANCE("BITHUMB:BTC", "ask").
  • =CRYPTOFINANCE("BITHUMB:BTC", "bid") will return Bitcoin bid price in KRW.
  • =CRYPTOFINANCE("BITHUMB:BTC", "avg") will return Bitcoin average price in KRW over the last 24 hours.
  • =CRYPTOFINANCE("BITHUMB:BTC", "high") will return Bitcoin highest price in KRW over the last 24 hours.
  • =CRYPTOFINANCE("BITHUMB:BTC", "low") will return Bitcoin lowest price in KRW over the last 24 hours.
  • =CRYPTOFINANCE("BITHUMB:BTC", "open") will return Bitcoin open price in KRW over the last 24 hours.
  • =CRYPTOFINANCE("BITHUMB:BTC", "close") will return Bitcoin close price in KRW over the last 24 hours.
  • =CRYPTOFINANCE("BITHUMB:BTC", "volume") will return Bitcoin volume over the last 24 hours, same thing as calling =CRYPTOFINANCE("BITHUMB:BTC", "volume", "24h").
  • =CRYPTOFINANCE("BITHUMB:BTC", "volume", "7d") will return Bitcoin volume over the last 7 days.

In a nutshell, call =CRYPTOFINANCE("HUOBI:ETH/BTC") to get the current Ethereum price in Bitcoin.

All information are returned from the Huobi ticker public API. All 7 ticker information can be retrieved: ask (sell, default), bid (buy), high, low, open, close, volume.


Only 3 pairs are supported at the moment ETH/BTC, ETC/BTC and BCC/BTC.


Examples:


  • =CRYPTOFINANCE("HUOBI:ETH/BTC") will return Bitcoin price (ask, sell price) in Bitcoin. Same thing as calling =CRYPTOFINANCE("HUOBI:ETH/BTC", "ask").
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "bid") will return Ethereum bid price in Bitcoin.
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "high") will return Ethereum highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "low") will return Ethereum lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "open") will return Ethereum open price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "close") will return Ethereum close price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("HUOBI:ETH/BTC", "volume") will return Ethereum volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("HITBTC:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the HitBTC ticker public API. All 7 ticker information can be retrieved: ask (sell, default), bid (buy), last, high, low, open, volume.


Examples:


  • =CRYPTOFINANCE("HITBTC:BTC/USD") will return Bitcoin price (ask, sell price) in US Dollar. It is the same thing as calling =CRYPTOFINANCE("HITBTC:BTC/USD", "ask").
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "bid") will return Bitcoin bid price in US Dollar.
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "last") will return Bitcoin last price in US Dollar.
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "high") will return Bitcoin highest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "low") will return Bitcoin lowest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "open") will return Bitcoin open price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("HITBTC:BTC/USD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("BITSTAMP:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the Bitstamp ticker public API. All 7 ticker information can be retrieved: ask (sell, default), bid (buy), last, high, low, open, volume.


Examples:


  • =CRYPTOFINANCE("BITSTAMP:BTC/USD") will return Bitcoin price (ask, sell price) in US Dollar. It is the same thing as calling =CRYPTOFINANCE("BITSTAMP:BTC/USD", "ask").
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "bid") will return Bitcoin bid price in US Dollar.
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "last") will return Bitcoin last price in US Dollar.
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "high") will return Bitcoin highest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "low") will return Bitcoin lowest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "open") will return Bitcoin open price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("BITSTAMP:BTC/USD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("GDAX:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the GDAX ticker public API. 3 ticker information can be retrieved: ask (sell, default), bid (buy) and volume.


Examples:


  • =CRYPTOFINANCE("GDAX:BTC/USD") will return Bitcoin price (ask, sell price) in US Dollar. It is the same thing as calling =CRYPTOFINANCE("GDAX:BTC/USD", "ask").
  • =CRYPTOFINANCE("GDAX:BTC/USD", "bid") will return Bitcoin bid price in US Dollar.
  • =CRYPTOFINANCE("GDAX:BTC/USD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("BTCMARKETS:BTC/AUD") to get the current Bitcoin price in Australian Dollar.

All information are returned from the BTCMarkets public API. All 4 ticker information can be retrieved: ask (default), bid, last, volume.


Examples:


  • =CRYPTOFINANCE("BTCMARKETS:BTC/AUD") will return Bitcoin price (ask) in Australian Dollar, same as calling =CRYPTOFINANCE("BTCMARKETS:BTC/AUD", "ask")
  • =CRYPTOFINANCE("BTCMARKETS:BTC/AUD", "bid") will return Bitcoin bid price in Australian Dollar.
  • =CRYPTOFINANCE("BTCMARKETS:BTC/AUD", "last") will return Bitcoin last price in Australian Dollar.
  • =CRYPTOFINANCE("BTCMARKETS:BTC/AUD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD") to get the current Bitcoin price in Australian Dollar.

All information are returned from the Independent Reserve Public API, 7 ticker information can be retrieved: ask (default), bid, last, avg, low, high, volume.


Examples:


  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD") will return Bitcoin price (ask) in Australian Dollar, similar to =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "ask").
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "bid") will return Bitcoin bid price in Australian Dollar.
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "last") will return Bitcoin last price in Australian Dollar.
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "avg") will return Bitcoin average price in Australian Dollar over the last 24 hours.
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "low") will return Bitcoin lowest price in Australian Dollar over the last 24 hours.
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "high") will return Bitcoin highest price in Australian Dollar over the last 24 hours.
  • =CRYPTOFINANCE("INDEPENDENTRESERVE:XBT/AUD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("BITFINEX:BTC/USD") to get the current Bitcoin price in US Dollar.

All information are returned from the Bitfinex public API. All 6 ticker information can be retrieved: ask (default), bid, last, high, low, volume.


Examples:


  • =CRYPTOFINANCE("BITFINEX:BTC/USD") will return Bitcoin price (ask) in US Dollar, same as calling =CRYPTOFINANCE("BITFINEX:BTC/USD", "ask")
  • =CRYPTOFINANCE("BITFINEX:BTC/USD", "bid") will return Bitcoin bid price in US Dollar.
  • =CRYPTOFINANCE("BITFINEX:BTC/USD", "last") will return Bitcoin last price in US Dollar.
  • =CRYPTOFINANCE("BITFINEX:BTC/USD", "low") will return Bitcoin lowest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("BITFINEX:BTC/USD", "high") will return Bitcoin highest price in US Dollar over the last 24 hours.
  • =CRYPTOFINANCE("BITFINEX:BTC/USD", "volume") will return Bitcoin volume over the last 24 hours.

In a nutshell, call =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC") to get the current Dotcoin price in Bitcoin.

All information are returned from the Cryptopia Public API. All 14 tickers information can be retrieved: ask (default), bid, last, high, low, open, close, change, volume, buy_volume, sell_volume, base_volume, buy_base_volume, sell_base_volume.


Examples:


  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC") will return Dotcoin price (ask price) in Bitcoin, similar to calling =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "ask")
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "bid") will return Dotcoin bid price in Bitcoin.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "last") will return Dotcoin last price in Bitcoin.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "high") will return Dotcoin highest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "low") will return Dotcoin lowest price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "open") will return Dotcoin open price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "close") will return Dotcoin close price in Bitcoin over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "change") will return the change percentage for the market DOT-BTC.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "volume") will return Dotcoin volume (in DOT) over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "buy_volume") will return Dotcoin buy volume (in DOT) over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "sell_volume") will return Dotcoin sell volume (in DOT) over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "base_volume") will return Dotcoin volume (in BTC) over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "buy_base_volume") will return Dotcoin buy volume (in BTC) over the last 24 hours.
  • =CRYPTOFINANCE("CRYPTOPIA:DOT/BTC", "sell_base_volume") will return Dotcoin sell volume (in BTC) over the last 24 hours.

Get social media data on cryptocurrencies


Solume Website

Provided by Solume, you can get Twitter and Reddit mention count and change over the last 24 hours.


Over 100 coins are supported and new one are added constantly. You can search for supported coins on their site.


Get volume and change data across both Twitter and Reddit:


  • Call =CRYPTOFINANCE("SOLUME:LTC") to get Litcoin mention count over the last 24 hours on both Twitter and Reddit. This is similar to calling =CRYPTOFINANCE("SOLUME:LTC", "volume").
  • Call =CRYPTOFINANCE("SOLUME:LTC", "change") to get Litcoin mention count change over the last 24 hours on both Twitter and Reddit.

Get volume and change data on Twitter only:


  • Call =CRYPTOFINANCE("SOLUME:NEO", "twitter_volume") to get Neo mention count over the last 24 hours on Twitter only.
  • Call =CRYPTOFINANCE("SOLUME:NEO", "twitter_change") to get Neo mention count change over the last 24 hours on Twitter only.

Get volume and change data on Reddit only:


  • Call =CRYPTOFINANCE("SOLUME:LTC", "reddit_volume") to get Litecoin mention count over the last 24 hours on Reddit only.
  • Call =CRYPTOFINANCE("SOLUME:LTC", "reddit_change") to get Litecoin mention count change over the last 24 hours on Reddit only.

Original article and pictures take cdn-images-1.medium.com site