Unfortunately, Google Finance’s data points and formula for cryptocurrency are quite limited. Crypto investors have to go to extra lengths if they want to build a crypto portfolio tracker for their Altcoins using Google Sheets.
In this blog post, I will take you through how you can capture many different data points such as the price, market cap, fully diluted market cap, circulating supply, volume, and many more from the Coinmarkecap.com website into your Google Sheets.
We already went one method of importing the crypto prices into google sheets on this blog, but this was limited to just crypto price data. Now we are taking that a step further by using an Import HTML formula to scrape much more data from the Coinmarketcap website.
Import HTML
Here’s an example formula that will import the market cap data for Bitcoin (BTC) from CoinMarketCap:
=IMPORTHTML("https://coinmarketcap.com/currencies/bitcoin/","table",2)
This formula uses the IMPORTHTML function to import the table data from the CoinMarketCap page for Bitcoin (BTC). The third data point within the brackets (2), specifies that the function should import the 10th table on the page, which contains the market cap data.
The 7th table on the Bitcoin page is the has market cap data table (as you can see from the above screenshot).
Below is what the executed formula looks like on Google sheets.
You can change this formula to capture many different data points such as market cap, fully diluted market cap, volume, and daily price change for any cryptocurrency listed on the CoinMarketCap website. All you have to do is change the “2” in the formula stated above to one of the following:
- 1 returns Bitcoin price, 24 hour price change, 24 hour low and high, and 24 hour volume.
- 2 returns market cap and fully diluted market cap.
- 3 returns yesterday’s high/low, yesterday’s open/close, yesterday’s % change, and yesterday’s volume.
- 4 returns 7 day high/low, 30 day high/low, 90 day high/low, 52 week high/low, and all-time high/low.
- 5 – returns total supply, circulating supply, and max supply.
Similarly, all you will need to do is replace the “bitcoin” in the URL with the name or symbol of the cryptocurrency you’re interested separate data on another crypto.
Note that the data imported by IMPORTHTML should update whenever the source website is updated. However, this method may not work in some cases if the website does not allow the data to be scraped.
An added step you need to work with the data
Your next question is going to be, but how can I build on that data coming from CoinMarketCap if it is not in the correct format. Well you can use the following formula to just extract the number from any cell, even if it includes both numeric and text characters.
=VALUE(REGEXEXTRACT(SUBSTITUTE(A1, ",", ""), "\d+\.?\d*"))
This formula uses the SUBSTITUTE function to remove any commas from the text string in cell A1 before extracting the numerical value.
The SUBSTITUTE function replaces any comma characters in the text string with an empty string, effectively removing them. The modified text string is then passed to the REGEXEXTRACT function to extract the numerical value using the same regular expression as before.
The VALUE function then converts the extracted numerical value into a number format that you can use in calculations.
Note that if the cell does not contain a numerical value, the formula will return a #VALUE! error.
Disclaimer: This blog post is for informational and educational purposes only and should not be construed as financial advice.
