Get Crypto prices using Google Finance and Google Spreadsheets

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!

Once you have the price you can calculate other multiple financial indicators

Curious about crypto? You’ll receive 9.15€ if you register and trade in Coinbase using this link.

Comments

20 responses to “Get Crypto prices using Google Finance and Google Spreadsheets”

  1. Bryan Avatar

    St. Kitts and Nevis Marine Resources director Marc Williams authenticated that the victim and her female friend were swimming and kayaking near Booby Island when the episode happened.

  2. […] a follow-up from last week’s post about getting cryptocurrency prices using Google Spreadsheets, we can use Python as well to get prices via API endpoints. And this will give us much more […]

  3. Arnaud CZ Avatar

    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 ?

  4. Pedro Avatar

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

    1. Arnaud CZ Avatar

      this is amazing Pedro, thanks so much! I don’t find pair with eur there, any chance you could add them , please ?

  5. Arul Avatar
    Arul

    How to get the change % of crypro currency like a ticker =GoogleFinance(C159,”changepct”)

      1. D. Groll Avatar
        D. Groll

        Google Finance only works for certain pairs. It doesn’t work for many altcoins like DOGE, AAVE, DOT, LUNA, WAVES, and so on.

      2. Gabriel Maldonado Avatar
        Gabriel Maldonado

        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.

  6. […] year I published a post explaining a way to get cryptocurrency prices like Bitcoin or Ethereum into Google Spreadsheets via […]

  7. Clifford Baynon Avatar
    Clifford Baynon

    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”), “{.+:(.+)}”))

    1. Todd S Avatar
      Todd S

      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

  8. John P Avatar
    John P

    Good work, now how do I get the % rise/fall on yesterday’s price?

    1. Gabriel Maldonado Avatar
      Gabriel Maldonado

      That would be a general variation formula: [(today’s price – yesterday price)/yesterday price]

      1. John Avatar
        John

        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.

  9. David Avatar
    David

    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?

    1. Gabriel Maldonado Avatar
      Gabriel Maldonado

      Is variable but potentially every 20-30 minutes.

  10. Joe Moraca Avatar
    Joe Moraca

    Nice formula I used it for DASH. I just used =GOOGLEFINANCE(“BTCUSD”) today and it works at least for BTC / BCH / ETH

    Thanks

  11. Rose Martine Avatar

    I am regular reader, how are you everybody? This article posted at this site is actually good.

Leave a Reply

%d bloggers like this: