I recently created a google sheet script which tracks every coin listed on CoinMarketCap. I use the sheet to track my personal portfolio so I can run calculations on them that are not provided by most portfolio trackers. I just wanted to explain how the sheet works as well as how you can use it to your own advantage.

Before you read further and look a the sheet: certain functionality will not work until you make a copy of the sheet to you own drive and open it and REFRESH THE ENTIRE SHEET. Now all that is left is the click Portfolio Tracker Functions->Refresh Coin Data on the menu at the top af the page. You will have to permit the sheet to run a script which accesses an external service (Coinmarketcap).

Layout

The script that runs the Sheets can be found by going to Tools->Script Editor in the menu at the top. The script populates mainly the Data sheet but you are free to adjust it to do more than that.

There are 3 sheets, Data, Portfolio and Graphs. The names are pretty obvious to what they stand for. :)

In the data sheet, all the data for all the coins are being displayed. In the portfolio sheet, you fill in which and how many coins you have, and at what price you have bought them. Lastly, in the graphs sheet, you can run all kind of graphs on your data and portfolio. Let's take a look at the data sheet first.

Data

The data sheet looks like this:



On this sheet you can find the raw data provided by CoinMarketCap's API. The script for this sheet is mainly one function that is run each time the spreadsheet is openend (or dependent on personal triggers you want to configure).

function getAllCoinData() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var ssAllCoins = ss.getSheetByName('Data'); var url = 'https://api.coinmarketcap.com/v1/ticker/?limit=0'; var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true}); var json = response.getContentText(); var data = JSON.parse(json); var maxRangeNr = 0; var values = []; var row = []; for (var i = 0; i < data.length; i++) { row = [data[i]['id'],data[i]['name'],data[i]['symbol'],data[i]['rank'],data[i]['price_usd'],data[i]['price_btc'],data[i] ['24h_volume_usd'],data[i]['market_cap_usd'],data[i]['available_supply'],data[i]['total_supply'],data[i] ['max_supply'],data[i]['percent_change_1h'],data[i]['percent_change_24h'],data[i]['percent_change_7d'],data[i] ['last_updated']] values[i] = row; } maxRangeNr = i+1; var range = ssAllCoins.getRange("A2:O" + maxRangeNr); range.setValues(values); }

This function can also be run by accessing the custom menu item at the top of the sheet that can be found under Portfolio Tracker Functions->Refresh Coin Data. So you can always refresh the data without refreshing the entire sheet.

Portfolio

The Portfolio Tab is where you track how much of a certain crypto currency you have in your possession. All you need to do is fill in the amount which of crypto you bought Column A, the price each at which you bought it Column D and choose which coin you bought from the dropdown menu in Column B. Everything else will be taken care of for you. You can also fill in the amount of money you initially invested to build the portfolio with in cell E1 to track your overall profits. An example would look like this:

For ease of use I will create 2 separate links so you can take either the Euro Sheet or the Dollar Sheet.

Graphs

On the Graphs tab you can basically run any calculation you desire that is within the possibilities of Google Sheets. :) 2 examples would be the profit you have made per crypto, as well as your total portfolio allocation.

The Sheets

Euro

Dollar

I hope I can at least help a few of the people that want the analytical ability of google sheets at their disposal when managing their portfolio.