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.
Leave a Reply