Get Real Time Crypto Prices Inside Google Sheets Crypto Portfolio

If you’re serious about investing, you know that price tracking is critical to success. I’m always looking to minimize costs, so I don’t purchase any paid portfolio tracking apps. I use Google Sheets for tracking my crypto portfolio.

In this article, I will show you how to use the free crypto currency price API from CryptoCompare inside of your Google Sheets crypto portfolio. I will provide you with the Google Script you need to use and instructions on how to implement it, so no coding knowledge is necessary.

To start off with I’ll assume you have a simple crypto portfolio in Google Sheets. For example, something that looks like this:

What is this spreadsheet missing? Why, the real time price surely!

How To Add Real Time Crypto Prices To Google Sheet

Here’s comes the fun part of creating the Google Script that will pull prices and create a custom function that we can use anywhere in our spreadsheet.

Step 1: Open The Script Editor

Step 2: Insert Code For Getting Crypto Price

function CryptoPrice(ticker) {
  var url = 'https://min-api.cryptocompare.com/data/price?fsym=' + ticker + '&tsyms=USD';

  var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());

  SpreadsheetApp.flush();

  return response.USD;
}

This is what it should look like:

Complete Crypto Price Google Script

Final step here is to save the code: File > Save and close the tab.

Step 3: Using Our New CryptoPrice Function

Now we can use our new function to pull in prices for any coin ticker. For example, to get the Bitcoin price we would enter =CryptoPrice("BTC")  into a spreadsheet cell. For Stellar Lumens it would be =CryptoPrice("XLM")

Step 4: Update Our Spreadsheet With A Price Column

To make everything nice and dynamic, we will use a formula to get current price based on the coin ticker in column A and quantity in column C. For example, the Bitcoin (row 2) formula will look like this =CryptoPrice(A2)*C2. We can then copy that formula down the entire column.

This is how it will look in the end:

That is all. It really is that simple.

Bonus: How To Get Historical Prices To Calculate Profit / Loss

We update our script with this:

function CryptoPrice(ticker, date) {
  var url,
      response,
      timeStamp = date ? Date.parse(date).toString().slice(0,-3): false;

  if (timeStamp) {
    url = 'https://min-api.cryptocompare.com/data/pricehistorical?fsym=' + ticker + '&tsyms=USD&ts=' + timeStamp;
    return JSON.parse(UrlFetchApp.fetch(url).getContentText())[ticker].USD;
  } else {
    url = 'https://min-api.cryptocompare.com/data/price?fsym=' + ticker + '&tsyms=USD';
    return JSON.parse(UrlFetchApp.fetch(url).getContentText()).USD;
  }
  
  SpreadsheetApp.flush();
}

Usage in an excel formula for this would be =CryptoPrice("BTC", "4/21/2018").

And a full example of our Crypto Google Sheet: