Google Sheets is Google’s alternative to Microsoft’s Excel, and among other things it allows the import of crypto prices.
In fact, 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 a stand-alone software that runs on the user’s machine even offline.
Being online, Google Sheets can connect to other data sources, even if 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 the latest prices from the markets of crypto.
Need to use a workaround that takes advantage of the IMPORTXML feature, and there is also a video tutorial showing how to proceed.
This tutorial is short, but not complete, so there is another one that is more complete but also longer.
The data source
The source of the data is the CoinMarketCap (CMC) website, where the data is obtained in XML that can be imported into Google Sheets.
To continue, it is necessary to open the respective CMC sheets of all cryptocurrencies whose prices you want to import.
In it you need to copy the URL, which for Bitcoin, for example, is https://coinmarketcap.com/en/currencies/bitcoin/.
Using the CMC card URL, 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 want 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 must 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 that the IMPORTXML function wants. However, that function can also read an HTML document.
In fact, the argument used to get the updated price is nothing more than telling the IMPORTXML function to get the content of a specific DIV element in the HTML code of the page, and specifically the one marked with priceValue class.
In this way you can theoretically tell the IMPORTXML function to display in the cell where it is inserted, the contents of any HTML element on the page that can be found online at the indicated url.
This workaround essentially allows any content on that page to be imported, as long as the content can be uniquely identified. HTML elements called DIVs are containers, and to view the HTML code on any web page you usually just need to type ctrl+u.
In the specific case of CMC, the HTML code is generated dynamically from the JavaScript code, so with ctrl+u you see the JavaScript code, and not the HTML. However, usually by right-clicking on any page element you can select the “Inspect Element” option to view 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 Google Sheets document 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 update the data even if it is not required to close and reopen the file.
In this way it is possible to update the data every minute about all the prices of all cryptocurrencies in CMC, if you include the specific url in its tab for each one.
The problem is that sometimes CMC changes the HTML code of its pages, and when it does, the workaround stops working and the data doesn’t show anymore.
Since it is likely to happen sooner or later, if the data imported in this way disappears from the Google Sheets document, it is necessary to update all the used IMPORTXML functions.
In particular you need to update the url, or 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 found online, as long as it is public.
The difficult thing is to go and find a way to make the unique speech of the IMPORTXML function HTML element to go and get, but thanks to the “Inspect Element” option, it is not difficult to look for a name, ID , or class that allows you to precisely identify an element.
However, there remains a problem when the HTML code is changed, because it is not at all unusual for the HTML code to be updated, or even distorted.