How to Retrieve Crypto Ticker Prices in Google Sheets for Bitcoin and Other ALT Cryptocurrencies
This tutorial will guide you on how to monitor the prices of Bitcoin and various alternative cryptocurrencies in Google Sheets.
Firstly, you’ll need a website that provides real-time and up-to-date prices. We found an excellent site, https://cryptoprices.cc/, which not only displays current prices but also offers guidance on setting up a basic Google Sheet to showcase these prices. However, in this tutorial, we have included additional elements to make tracking your investments more convenient.
STEP 1.
Commence by utilizing Cell A3 to exhibit the coin symbol. For instance, Bitcoin is represented by BTC. Ensure that the symbol is in all capital letters. Here is a list of crypto coins and their symbols.
STEP 2.
Now, retrieve the price for the given symbol by pulling the current price from the Crypto Prices website. Insert the following formula into cell B3: =IMPORTDATA(“https://cryptoprices.cc/”&A3&””). The URL path ends with /SYMBOL, pulling information from A3.
That covers the basics of obtaining the current live prices of any coin. If you wish to enhance your Google Sheet further, follow the steps below.
STEP 3.
Display the All-Time High (ATH) price of the coin in Cell C3. Insert this formula: =IMPORTDATA(“https://cryptoprices.cc/”&A3&”/ATH”).
STEP 4.
Track your percentage returns and losses. Input the purchase price of the coin in Cell E3. Then, in cell F3, insert this formula: =IF(E3 <> 0, (B3 – E3) / E3, “”), calculating the current percentage profit or loss.
BONUS FUN STEP
To visually distinguish between profit and loss:
- Select cell F3.
- Navigate to “Format” in the top menu.
- Choose “Conditional formatting.”
- Set up two rules:
- For positive percentage change (green):
- Under “Format cells if,” choose “Custom formula is.”
- Enter the formula: =F3 > 0
- Set the formatting style for positive values (e.g., green).
- For negative percentage change (red):
- Under “Format cells if,” choose “Custom formula is.”
- Enter the formula: =F3 < 0
- Set the formatting style for negative values (e.g., red).
- For positive percentage change (green):
This way, the background color of cell F3 will change to green when the percentage change is positive and red when it’s negative. Adjust the formatting options as needed.
Download our example Google Sheet here (the example coins we used on this sheet are: BTC, ETH, LINK, SOV, SOL, and SUSHI)