How to track cryptocurrency prices (Bitcoin, Ethereum, etc) using Google Sheets?

How to track cryptocurrency prices (Bitcoin, Ethereum, etc) using Google Sheets?

By Ci2fi | ci2fi | 9 May 2021


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 row2nd 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

 

How do you rate this article?


22

0

Ci2fi
Ci2fi

Normal salaried man exploring the ways towards financial independence


ci2fi
ci2fi

Cryptocurrency portfolio tracking, farming, stablecoins, binance, google sheets, and what's out there

Send a $0.01 microtip in crypto to the author, and earn yourself as you read!

20% to author / 80% to me.
We pay the tips from our rewards pool.