Bits #6: Cryptocurrency Prices in Google Sheets
Google Sheets is far better than MS-Excel (IMO) and its
googlefinance() function is among one of most used features in my financial spreadsheets. Although very powerful, allowing you to get quotes in a streamlined way, it lacks support for many cryptocurrencies --and I'm not talking about obscure, unknown coins.
The standard way of getting quotes, specially for Stocks, works for the most popular cryptocurrencies, like Bitcoin, Ethereum, and Cardano. Just use the
googlefinance() function and pass the cryptocurrency code as parameter concatenated with the currency code (fiat money), like USD or BRL. Examples:
=googlefinance("BTCBRL") # gets the Bitcoin (BTC) price in Brazilian Real (BRL) =googlefinance("ETHUSD") # gets the Ethereum (ETH) price in US Dollar (USD)
The common workaround for this type of situation is to find a website that brings the updated coin prices and use the
Other services allow us to get prices programmatically through APIs but they require authentication and it would require writing functions in Google Sheets, what would require a more complex approach to solve the problem. Fortunately, the Crypto Prices site provides an easy way to get Cryptocurrency prices by particular URLs. This service is like an abstraction for CoinGecko API, that uses the coin tickers instead the coin names to get data (which makes more sense to me), but limits to price data --which is enough for this goal.
Crypto Prices’ homepage itself shows usage examples for Google Sheets with the help of
importdata() function: just append coin code to the base URL for Crypto Prices and pass it to
And that's all: the cryptocurrency price in US Dollar will be fetched and can be used in the spreadsheet. But what if you're using a different location in the spreadsheet (different decimal separator) or simply not using US Dollar?
This method works well with
en_US locations, but if you're using another location with
a better another number format, it'll bring incorrect values although the function won't break --like bringing
801457 instead of
0.801457. To fix it, make sure to pass the correct location to
importdata() in the third parameter, like this:
In this case, the locale will always be
en_US because we can't change that in the source (as a parameter in the URL, for instance). Furthermore, the result will always come in US Dollar (USD), so if you want to change it, just multiply the output by the price of your desired currency in comparison to USD. The
googlefinance() function can do that conversion easily just by concatenating the currency codes, like
USDBRL (US Dollar to Brazilian Real) or
JPYEUR (Japanese Yen to Euro). So, to get the Chainlink price in Brazilian Real in a spreadsheet with Brazilian locale, the formula would be:
=importdata("https://cryptoprices.cc/LINK/";;"en_US") * googlefinance("USDBRL")
Translation: get the Chainlink price in US Dollar from Crypto Prices (remember it'll come in en_US locale), and multiply it by the Brazilian Real value in front of US Dollar, so I'll get the Chainlink quotation in BRL in my Brazilian spreadsheet.