How to track cryptocurrency prices (Bitcoin, Ethereum, etc.) using Google Sheets? – Financial independence through passive income

After my earlier post in January about tracking Singapore stock prices with Google Sheets, I thought it would be nice to also be able to track cryptocurrency prices with Google Sheets as I would most likely be investing in some cryptocurrencies this year.

In this post, I will explain how to track cryptocurrency prices on Coinmarketcap.com using Google Sheets.

Find out the URL

First you need to determine the URL to use. Luckily, the cryptocurrency URL for Coinmarketcap.com is pretty simple. It is like that for Bitcoin

https://coinmarketcap.com/currencies/bitcoin/

It is like that for Ethereum

https://coinmarketcap.com/currencies/ethereum/

Use IMPORTHTML to find the data table you want

The next step is to use IMPORTHTML to find the cryptocurrency price information table. I tried the following formula through some experimentation and found the table I need to use.

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

Image: Table of bitcoin price information

Extract the price with REGEXREPLACE

You can see the price and other information like trading volume and price change (24 hours) in the table above. The price of bitcoin is at 1st row, 2nd column the table. After entering the following formula to refer to the first row and the second column, you can extract the bitcoin price in 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

Assuming you’re trading for short-term profits, you might want to watch the price change (24 hours) to see if it’s time to turn a profit.

You can track the price change (24 hours) by entering the following formulas.

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

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

Example: Using Regexextract to extract the second row of information from a cell with multiple rows of information

Conditional formatting (profit/loss, target price setting)

You can also use conditional formatting to highlight your win/loss. If the win/loss column (I2 to I5) is below zero on losses, the cell will be highlighted in red.

Example: Use conditional formatting to highlight losses in RED

You can also use conditional formatting when the market price hits your target price. For example, if you want to cash out a BTC when it reaches 40,000 or more, you can set conditional formatting as below.

Example: Sample cryptocurrency tracking portfolio using Google Sheets

The above Google sheet on easy crypto tracking portfolio can be found here. I hope you find it useful.

Message me if you have any questions. I try my best to answer them.

Like this:

How Loading…

Related

Learn Crypto Trading, Yield Farms, Income strategies and more at CrytoAnswers
https://nov.link/cryptoanswers