How to upload crypto prices to Google Sheets

Google Sheets is Google’s alternative to Microsoft’s Excel, and among other things it allows importing crypto prices.

Indeed, Google Sheets is used online, as SaaS (Software-as-a-Service), so it is by definition connected to the network. Excel, on the other hand, is stand-alone software that runs on the user’s machine even offline.

Being online, Google Sheets can connect to other data sources, even updated in real time.

How to import crypto prices into Google Sheets

Although it is not straightforward, data from external sources can be imported into Google Sheets, and among the data that can be imported in this way are up-to-date prices from crypto markets.

It is necessary to use a workaround that takes advantage of the IMPORTXML feature, and there is also a video tutorial that shows how to proceed.

This tutorial is short, but not complete, so much so that there is also another one that is more complete but also longer.

The data source

The source of the data is the CoinMarketCap (CMC) website, from which XML data can be extracted that can be imported into Google Sheets.

In order to proceed, it is necessary to open one by one the individual CMC sheets of all the cryptocurrencies whose prices you want to import.

Of these you need to copy the URL, which for Bitcoin, for example, is https://coinmarketcap.com/en/currencies/bitcoin/.

Using the URL of the CMC card, it is possible to import the data contained in the card into any Google Sheets document, using the IMPORTXML function.

This function has two arguments, separated by commas, that allow you to specify the URL of the data source, and the data you intend to import.

To import the updated price, for example, you would use the argument “//div[contains(@class,’priceValue’)]”.

In other words, to display in a specific cell within a Google Sheets document the updated price of Bitcoin you have to write the following code in that cell:

=IMPORTXML(“https://coinmarketcap.com/en/currencies/bitcoin/”, “//div[contains(@class,’priceValue’)]”)

This is a workaround because the data source is actually an HTML page, and not an XML file as the IMPORTXML function would like. However, that function can also read an HTML document.

Indeed, the argument that is used to retrieve the updated price is nothing more than telling the IMPORTXML function to fetch the contents of a specific DIV element of the page’s HTML code, and in particular the one marked with the priceValue class.

In this way you could theoretically tell the IMPORTXML function to display in the cell in which it is inserted, the contents of any HTML element contained in the page that is found online at the indicated url.

This workaround actually allows any content on that page to be imported, as long as the container can be uniquely identified. HTML elements called DIVs are containers, and to view the HTML code of any web page you typically only need to type ctrl+u.

In the specific case of CMC, HTML code is created dynamically from JavaScript code, so with ctrl+u you see the JavaScript code, and not the HTML. However, usually by right-clicking over any element on the page you can choose the “Inspect Element” option to see the HTML code for that element in the sidebar.

How to automatically upload updated crypto data to Google Sheets

After using this workaround, every time the document in Google Sheets is opened, the updated data will be imported.

However, by clicking on File/Settings, and then selecting the Calculation tab you can set an update timer of one minute or one hour to have the data update even without having to close and reopen the file.

In this way it is possible to have data updated every minute regarding all the prices of all the cryptocurrencies on CMC, assuming you include the specific url of its tab for each one.

The problem is that sometimes CMC changes the HTML code of its pages, and should it do so, the workaround would stop working and the data would no longer show up.

Since it is quite likely that this may happen sooner or later, in the event the data that was imported in this way disappears on the Google Sheets document, it will be necessary to update all the IMPORTXML functions used.

In particular there may be a need to update the url, or to update the class name of the DIV whose content you want to retrieve.

Alternative data sources

This technique also works with any other updated HTML file that can be found online, as long as it is public.

The tricky thing is to go and find a way to be able to uniquely tell the IMPORTXML function which HTML element to go and retrieve, but thanks to the “Inspect Element” option, it is not that difficult to go looking for a name, ID, or class that allows you to accurately identify an element.

However, there remains the problem of whether the HTML code has been changed, because it is not all that uncommon for HTML code to be updated, or even distorted.