Some time ago it was possible to use Google Finance built-in methods to pull cryptocurrency prices into your Google Spreadsheets (at least was possible with Bitcoin), however this is no longer the case and the following was deprecated sometime around 2018:
=GOOGLEFINANCE("btcusd")
=GOOGLEFINANCE("CURRENCY:BTCUSD")I keep a Google Spreadsheet with my portfolio ( which includes some cryptocurrency ) and this change was a bit disruptive when I wanted to see my global positions, let’s fix this and keep everything in the same Spreadsheet again.
The idea here is pull data directly from any website API that keeps cryptocurrency prices up to date, in this example I will use cryptocompare.com API and I want to get Bitcoin (BTC) and Euro values, but we can use any other website by adapting the code. Then we’ll need to use 3 of Google Spreadsheets built-in methods:
- IMPORTDATA() imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format.
- REGEXEXTRACT() extracts matching substrings according to a regular expression.
- VALUE() converts a string in any of the date, time or number formats that Google Sheets understands into a number.
1) The first step will be to get the URL where we’ll be getting the data from, in this case I want to get EUR/BTC so will use https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR which will give me the BTC to Euro exchange rate. If you want to use a different cryptocurrency or fiat currency just change the parameters in the URL where: fsym=BTC&tsyms=EUR
=IMPORTDATA("https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR")2) Once we have this Bitcoin and Euro prices imported into Google Spreadsheets via IMPORTDATA() , we’ll see the following response: {"EUR":6073.11} . This is an object that we need to parse in order to get only the price, so we can work with this value in our spreadsheet. Here we’ll use REGEXEXTRACT() to extract any data that comes after the ":" via REGEX {.+:(.+)}
=REGEXEXTRACT(IMPORTDATA("https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR"), "{.+:(.+)}")3) Finally, as the price has been extracted from a JSON object, this is currently a string, and we need to transform it into a number in order to work with it, so we can use Google Spreadsheet VALUE() method to transform the data type:
=VALUE(REGEXEXTRACT(IMPORTDATA("https://min-api.cryptocompare.com/data/price?fsym=BTC&tsyms=EUR"), "{.+:(.+)}"))4) And that’s it!
Curious about crypto? You’ll receive 9.15€ if you register and trade in Coinbase using this link.
Awesome stuff Gabriel, thanks!
it worked for a little while then it started giving me N/A
I found this error code :
Message:”Please use an API key for your calls. To get an API key go to https://www.cryptocompare.com/cryptopian/api-keys register create a key and add it to your requests as either ? or &api_key=.” HasWarning:false Type:99 RateLimit:{} Data:{} Cooldown:299}
do you know how to include an happy to the formula ?
I made a new website to solve this problem, basically it will return the data in text format without any html. It requires no parsing and has big focus in simple and reliable and performance service.
Google sheets code works like
=IMPORTDATA(“https://cryptoprices.cc/ETC”)
this is amazing Pedro, thanks so much! I don’t find pair with eur there, any chance you could add them , please ?
How to get the change % of crypro currency like a ticker =GoogleFinance(C159,”changepct”)
This is answered here: https://tilcode.blog/2020/04/02/get-crypto-prices-using-google-finance-and-google-spreadsheets/comment-page-1/#comment-28
Google Finance only works for certain pairs. It doesn’t work for many altcoins like DOGE, AAVE, DOT, LUNA, WAVES, and so on.
Yes, Google Spreadsheet is limited to what Google Finances and their API offer, but there are alternative implementations where you can pull data from other APIs into Google Spreadsheets if needed. This is out of the scope of this post though.
Here’s a simple line to get Crypto quotes. For example, this is for Ethereum:
=VALUE(REGEXEXTRACT(IMPORTDATA(“https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD”), “{.+:(.+)}”))
This does not seem to work. I have tried it and I am getting an error using googlesheets
=VALUE(REGEXEXTRACT(IMPORTDATA(“https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD”), “{.+:(.+)}”))
Todd
There’s a new ( and simpler ) method: https://tilcode.blog/2021/03/28/how-to-get-crypto-prices-using-google-finance-and-spreadsheets-easy-updated-2021/
Good work, now how do I get the % rise/fall on yesterday’s price?
That would be a general variation formula: [(today’s price – yesterday price)/yesterday price]
Well, I sort of knew that. But as I have absolutely no idea how to program, I don’t know what the actual code is. Sorry if I didn’t make that clear.
I love this formula! Thank you very much! I use the standard Google Finance code for the crypto they cover, but I own a few that G-Fi doesn’t cover, like XRP. I do have a question, how often does the data get updated/refreshed when using your formula in G-sheets?
Is variable but potentially every 20-30 minutes.
Nice formula I used it for DASH. I just used =GOOGLEFINANCE(“BTCUSD”) today and it works at least for BTC / BCH / ETH
Thanks
I am regular reader, how are you everybody? This article posted at this site is actually good.