In this post, I will be sharing how to track the cryptocurrency prices at Coinmarketcap.com using Google Sheets.

Figuring out the URL

First thing to note is to figure out the URL to be used. Fortunately, for coinmarketcap.com, the URL for the cryptocurrency is pretty straightforward.

For bitcoin, it is https://coinmarketcap.com/currencies/bitcoin/

For ethereum, it is https://coinmarketcap.com/currencies/ethereum/

Using IMPORTHTML to find the desired table of data

The next step is to use IMPORTHTML to locate the table of information containing the price of the cryptocurrency. I tried the below formula, through some trial and error, and located the table i need to use.

=IMPORTHTML(“https://coinmarketcap.com/currencies/bitcoin/”,”table”,1)

Pic: Table containing the information of Bitcoin Price

Extracting the Price using REGEXREPLACE

You can see the price and other information such as trading volume, price change (24 hr) listed in the table above. The price of bitcoin is located at the 1st row, 2nd column of the table. After entering the below formula to reference the 1st row, 2nd column, you would be able to extract the bitcoin price into Google Sheets.

=REGEXREPLACE(index(IMPORTHTML(“https://coinmarketcap.com/currencies/bitcoin/”,”table”,1),1,2),”[^\d]”,””)/100

Example: Tracking cryptocurrency prices using IMPORTHTML

Tracking Other Information for Trading Purposes

Suppose you are trading for short term profits, you may want to look at the price change (24hr) to see if it is time to realise some profits.

By entering the below formulae, you can track the price change (24 hr)

=(regexextract(index(IMPORTHTML(“https://coinmarketcap.com/currencies/bitcoin”,”table”,1),2,2),”(\n.*){1}”))

The regexextract function is to extract the 2nd line of information (see red highlighted box) in C41.

Example: Using Regexextract to extract 2nd line of information from cell with multi-line of information

Conditional Formatting (Profit/Loss, Target Price setting)

You can also use Conditional Formatting to highlight your Profit/Loss. For losses, if Profit/Loss column (I2 to I5) is below zero, the cell will be highlighted red.

Example: Using conditional formatting to highlight losses in RED

You can also use conditional formatting if the market price hits your target price. For example, if you want to cash out some BTC when it hits 40000 or higher, you can set the conditional formatting like below.

Example: Sample cryptocurrency tracking portfolio using Google Sheets

You can find the above simple Crypto tracking portfolio Google sheet here. I hope you find it useful.

Do drop me a message if you have any questions. I try my best to answer them.

Note: This post was first published on my blog at ci2fi.wordpress.com