How to import the current cryptocurrency rate into Google Sheets

Cryptocurrency rates change almost every minute, and some fluctuate by more than a dozen or even a hundred percent. Sometimes you need to track the current rate of a certain cryptocurrency right in Google Sheets in order to perform the necessary calculations, calculate profits or losses.

In this article, I’ll show you how to handle this task in two different ways.

Method 1: Using the GOOGLEFINANCE function

The use of the GOOGLEFINANCE function has already been described in my other article  – it talks about converting non-cryptocurrency monetary units. You can also use it for bitcoin or other tokens that Google knows about. To do this, you can enter a query in the search engine, for example,  “SHIB to RUB” . If a form from Google appears, then this method can be used for a specific cryptocurrency.

It will be enough for you to find out the abbreviation and indicate, for example, BTCRUB in the formula, to get the corresponding rate, which will be updated automatically from time to time, displaying the current state of quotes.

Otherwise, when there is no quote form, you will need to refer to the following XML import method, which I will cover in the next instruction.

Method 2: Using the IMPORTXML function

This method is more flexible, since you can choose the site or exchange from which you will transfer cryptocurrency quotes to Google Spreadsheet with automatic updates. The second advantage of this option over the previous one is that there are no restrictions in terms of available tokens, because not all of them are represented by Google. I took the CoinMarketCup website as an example, so I will show you how to get the required value for further use in the table.

1. Open this site or any other, find the required token in the list and go to its page.
2. The site offers to choose the currency with which you want to display the rate. Find a suitable option and wait for the page to load.
3. On the Russian version of the CoinMarketCup website, the price is automatically displayed in rubles. Select the inscription, right-click on it and use the context menu to view the element’s code.
4. You need to know which class this value belongs to. If you use this site, you can skip this step because you can then copy my formula and paste it into your spreadsheet. When working with other sites, you will need to independently find out the class and already upgrade the formula for yourself.

Now go to the table, select an empty cell for the formula. Paste the formula there if you’re going to use the same site I’m talking about:

=IMPORTXML("https://coinmarketcap.com/ru/currencies/bitcoin/";"//div[contains(@class,'priceValue')]")

Accordingly, “bitcoin” must be replaced with the name of the token whose rate you want to track. Please note that when working with other sites, the class name will need to be replaced with the current one in order for the data to be read correctly.

You can remove the “ru/” prefix from this formula to get a display of the selected cryptocurrency’s exchange rate against the dollar.

Additionally, I note that you can use the link of the format https://coinmarketcap.com/currencies/bitcoin/btc/eur/ , replacing the necessary currency names. This is a more flexible option, not tied to the language of the page and your geo-location. 

The developers in the documentation describe the IMPORTXML function in more detail , which is the main one when obtaining the necessary information. You can familiarize yourself with its syntax and edit it according to your personal needs.

If the site you use as a cryptocurrency tracking tool provides its own API, you can import it into Google Sheets and get about the same result. I do not consider this option, because for ordinary users it is not a priority, and besides, it is much more difficult to implement than the two methods discussed above.