How to Upload Crypto Prices to Google Sheets

[ad_1]

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

Indeed, Google Sheets is used online, in SaaS (Software-as-a-Service) mode, so it is by definition connected to the network. Excel, on the other hand, is standalone 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 not straightforward, data from external sources can be imported into Google Sheets, and among the data that can be imported this way are up-to-date crypto market prices.

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

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

The data source

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

To continue, 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 CMC map URL, it is possible to import the data contained in the map into any Google Sheets document, using the IMPORTXML function.

This function has two arguments, separated by commas, which allow you to specify the data source URL 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 of a Google Sheets document the updated Bitcoin price, you would 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 expect. However, this function can also read an HTML document.

Indeed, the argument used to retrieve the updated price is nothing more than telling the IMPORTXML function to retrieve the content of a specific DIV element from the HTML of the page, 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 content of any HTML element contained in the page which is in line at the indicated url.

This workaround actually allows any content from 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 of any web page, you usually only need to type ctrl+u.

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

How to Automatically Upload Updated Crypto Data to Google Sheets

After using this workaround, each 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 so that the data is updated even without having to close and reopen the file.

This way, it is possible to have minutely updated data regarding all prices of all cryptocurrencies on CMC, assuming you include the specific tab url for each one.

The problem is that sometimes CMC changes the HTML of its pages, and if it did, the workaround would stop working and the data would no longer be displayed.

As it is very likely that this will happen sooner or later, in the event that the data thus imported disappears on the Google Sheets document, it will be necessary to update all the IMPORTXML functions used.

In particular, it may be necessary 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 that can be found online, as long as it is public.

The tricky part is figuring out a way to be able to uniquely tell the IMPORTXML function which HTML element to fetch, but thanks to the “Inspect Element” option, it’s not that hard to fetch a name, ID , or class that allows you to precisely identify an element.

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

[ad_2]