# Get Real Time Crypto Prices Inside Google Sheets Crypto Portfolio

April 21, 2018October 3rd, 2020

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!

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 2: Insert Code For Getting Crypto Price

#### Get your CryptoCompare API key from here (free for 100k calls per month): https://min-api.cryptocompare.com/

```function CryptoPrice(ticker) {
var url = 'https://min-api.cryptocompare.com/data/price?fsym=' + ticker + '&tsyms=USD&api_key=API_KEY_GOES_HERE';
var response = JSON.parse(UrlFetchApp.fetch(url).getContentText());

return response.USD;
}```

This is what it should look like:

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 + '&api_key=API_KEY_GOES_HERE';
return JSON.parse(UrlFetchApp.fetch(url).getContentText()).USD;
} else {
url = 'https://min-api.cryptocompare.com/data/price?fsym=' + ticker + '&tsyms=USD&api_key=API_KEY_GOES_HERE';
return JSON.parse(UrlFetchApp.fetch(url).getContentText()).USD;
}

}```

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

And a full example of our Crypto Google Sheet:

### Join the discussion 7 Comments

• J.R. says:

Script isn’t returning values when I tried it this morning? Did they close/stop the API?

• Mike Doubintchik says:

The API is still working. The free API allows 100k calls per month.

• GV says:

It doesn’t return values for me either.

• MJS says:

I tried it today but it did not return any values. Does that mean the 100k calls per month have already been used up for October?

• Mike Doubintchik says:

Should be good now. I’ve updated the article as well with instructions to implement the API Key from cryptocompare.

• Gary says:

Thank you very helpful, can someone advise how to set it in Google Sheets so it only refreshes manually so I don’t use up all my calls

• Mike Doubintchik says:

There’s different triggers you can setup inside the script editor. If you don’t setup any triggers, it will only refresh when you first open up the google sheet.

I have a google drawing of an update button that I inserted into my google sheet. Then you can assign an action to the button to run a script called updateData.

My helpers file (which contains the method updatedata) looks something like this:

```/**
* Get all sheet names from active spreadsheet
*/
function sheetNames() {
var sheetNames = [];
for (var i = 0; i < sheets.length; i++) sheetNames.push( sheets[i].getName() );
return sheetNames;
}

/**
* Update all formula data in cell range
*/
function updateData() {
activeSheetName = activeSheet.getSheetName(),
activeRange     = activeSheet.getActiveRange(),
sheets          = sheetNames(),
range           = '';

// loop through all sheets
sheets.forEach(function(sheet) {
var originalFormulas = [],
range            = activeRange,
defaultRanges    = {
'Alts': 'J2:J64',
'Ledger': 'D2:D10'
};

// set range to active range, otherwise fall back to defined cell range

originalFormulas = range.getFormulas();

// modify formulas
var modified = originalFormulas.map(function (row) {
return row.map(function (col) {
return col.replace('=', '?');
});
});

// update formulas
range.setFormulas(modified);