The Capital
Published in

The Capital

Tracking Crypto Gains/Losses through a Spreadsheet Walkthrough

By Kolin Lukas on The Capital

Connect CoinMarketCap crypto coin daily pricing with an auto-updating Google Sheets. Track your coins, and mark if they rise or fall a certain percentage.

I wouldn’t say I’m in love with spreadsheets, but I do appreciate the way they bring clarity to tracking, and help to comprehend numbers that change over time.

Bringing in Bitcoin pricing is just the basics. You can make cells and fonts change color based on a percentage rise or fall, alerting you to major and minor trends, which you can then act upon. You can make pretty charts that place the trend front and center. It’s what they call actionable intelligence.

We’ll go over the basics, and add some additional ways in which you can manipulate the information to help you see your price trends better, and create what the intelligentsia may call actionable intelligence.

The Formula

In Column A of your Google Spreadsheet, place the Coinmarketcap URL for Bitcoin:

2. In Column B, paste the following:


The result should look like this:

Congratulations, you’ve just pulled in the current price of Bitcoin!

Now, let’s give you the ability to put any crypto name in the first column, so you can create a list of crypto and their pricing.

Adding more cryptocurrencies

Click cell A1, and then Insert a Column to the left. In the new A1 add the name of the coin you want to track, in this case, “bitcoin.”

In B1, Change the contents to this:


You should see this:

Can you see how the formula grabs the name of the coin in A1 and appends it to the formula in B1?

C1 remains the same — pulling in the price of bitcoin from the URL created in B1. Now add another crypto, such as ethereum to A2, dragging down formulas B1 and C1 to the next row:

Notice I used “ethereum” and not its coin name of “ETH.” The name of the coin has to correspond to how CoinMarketCap creates its URL. If I used “eth” this would not work.

You can compare previous pricing with current pricing, coloring the cells if they rise or fall.

Let’s do that.

Conditional Formatting

Click on Column “C,” and add a column to the left.

In the new “C” column type in the following prices for bitcoin and ethereum: $7,748.802, $210. It should look like this:

3. The new “C” column is the previous day’s price, and “D” column is the current price. Let’s add headers:

4. Shift>Select both cells D2 and D3, and under the “Format” Google Sheets tab dropdown select “Conditional Formatting”:

5. A Dialog box should open:

6. Under “Format Rules” scroll down and “Format Cells If” select “Custom Formula is” and add the following formula:


It should look like this:

What this does is color the background of the cell green if the price when compared with the previous day goes up by 10%.

Now, let’s add a 2nd formula.

Adding a 2nd formula for a 10% Decrease

Click “Done.

With the Conditional Format Rules box still open, Click “Add another rule”:

3. Keep everything else the same as above, just change the custom formula to:


4. Change the background color to red.

The sheet should look like this:

The Conditional Formatting rules will now color the cells red or green depending on whether the price rose or fell by more than 10%. You can play around with this, such as just changing the font colors rather than the backgrounds, or apply different formulas based on exactly what you want actionable intelligence on.


Using just simple price action you can set formulas to format cells depending on what actions you’d like to take. You could, for example, set a sell point when a crypto drops more than 35%. Or, alternatively, this could also be a signal to buy more if you are a long term HODLer or the coin. Or perhaps when a crypto rises 10x, color the text bright green as a signal to sell 50%.

Your options are endless.

Kolin Lukas

Social Media :




A publishing platform for professionals in business, finance, and tech

Recommended from Medium

TRON Independence Day — A Letter to the Community

Trading Strategy: Still Bored of the 9,000-10,000 Range?

Top 10 Cryptocurrencies In July 2021Other Noteworthy Cryptocurrencies Tied for 10th place in terms…

dHEDGE Open Call for Managers

Lazy Lions NFT Collections: A Comprehensive Review

Brick Strategy

Obsidium is the ecosystem serverless cross-chain layer-2 solution that fuel the crypto space with…

Cathie Wood Suffers a Major Blow

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Kolin Lukas

Kolin Lukas

More from Medium

DanteHQ vs DeFi Llama: The top two crypto dashboards for fundamental data

What are Crypto Rug Pulls and How to Avoid Them?


Bitcoin Macro Analysis — bet you nobody told you this! Halvings, Support . Eth & Alts ready to