UPDATE: The formula previously used for pulling in crypto pricing no longer works. The article, and sample Google sheet has been updated with the correct formula, which is:
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.
- 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 crypto currencies
- 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.
- 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 of the coin. Or perhaps when a crypto rises 10x, color the text bright green as a signal to sell 50%.
Your options are endless.
Here’s a link to a working spreadsheet:
Crypto Tracking by Cryptozoa.com
Sheet1 Crypto Name,URL,Previous Price,Current Price bitcoin, https://coinmarketcap.com/currencies/bitcoin…
File>Make a copy