Tracking Changes in Cryptocurrency Value

The Why

I, like many people, have purchased a bit of cryptocurrency. I have also dipped my toe into the world of personal finance, so, naturally I’ve wondered about the current value of cryptocurrency in my family’s investment portfolio.

Starting with a Manual Process

Answering this question initially involved a manual process in Google Sheets. I would visit CoinMarketCap to copy the USD spot price of a particular cryptocurrency and plug it into a spreadsheet. After using the spreadsheet to calculate our small slice of crypto, I would take all new calculated values and save them as a historical snapshot. Here’s what this looks like (with dummy data):

At the top, is a chart containing the original amount paid for a given cryptocurrency in addition to the quantity purchased. By multiplying the USD spot price by the quantity owned, I can find out the current value and calculate any gains or losses of the investment. The bottom portion of the sheet tracks the current value over time so that I can see how individual investments compare to one another.

I quickly learned two things. The result of this time-consuming process is worthwhile, but my time is better spent elsewhere.

Now, about the Automated Process

It turns out that CoinMarketCap has a well-documented API, so I spent time learning about it along with Google Apps Script to find out how these two can do all the legwork that I no longer felt like doing.

Fetching Crypto Data

First thing to do was to pull price data in via an API call to CoinMarketCap. That was done as follows:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


// API call for fetching data regarding the latest market quote for cryptocurrencies of interest
// More Information can be found at: https://coinmarketcap.com/api/documentation/v1/#operation/getV1CryptocurrencyQuotesLatest

var response = UrlFetchApp.fetch(
  `https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?symbol=${Object.keys(CRYPTO_ROWS).join(',')}`,
    {
    'method' : 'GET',
    'contentType': 'application/json',
    'headers' : {
      'X-CMC_PRO_API_KEY' : `${COINMARKETCAP_API_KEY}`,
    }
  }
);

var json = response.getContentText();
var data = JSON.parse(json);

Next was to figure out how to get the script to do two things:

Calculate the new value of each cryptocurrency investment
Take a snapshot of the value for that day so that it can be viewed over time

Calculating the Current Value of Crypto

The script uses a few constants in order to accurately update the table data at the top of the sheet. It then loops over each cryptocurrency, fetches its current price from the json data, and uses the updated price to calculate its updated value. Once that’s done, it updates these values for each cryptocurrency in the spreadsheet.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


QUANTITY_COLUMN = 'C';
PRICE_COLUMN = 'D';
VALUE_COLUMN = 'E';
CRYPTO_ROWS = {
  'ETH' : '2',
  'BTC' : '3',
  'FIL' : '4',
  'DOGE' : '5',
}

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crypto');

for (let [symbol, row] of Object.entries(CRYPTO_ROWS)) {
  // fetch USD spot price of cryptocurrency of interest from CoinMarketCap json data
  var currentPrice = data['data'][symbol]['quote']['USD']['price'];

  // pull value of quantity owned from spreadsheet
  // mutliple value of quantity by current USD spot price to calculate current value
  var currentValue = sheet.getRange(QUANTITY_COLUMN + row).getValue() * currentPrice;

  // updates top section of spreadsheet:
  // set current USD spot price
  sheet.getRange(PRICE_COLUMN + row).setValue(currentPrice);
  // set current value
  sheet.getRange(VALUE_COLUMN + row).setValue(currentValue);
}

Saving the Snapshot of Crypto Value

I’ve already done most of the legwork for the second ask of the script, as I already have the current value of each cryptocurrency. Now I just need to append these values to the right spot. The chart visually capturing historical data expects the data format to be as follows: date, cryptoVal1, cryptoVal2, etc… and that’s what the script delivers.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


// initialize your values array with the date
var values = [todaysDateAsString()];

for (let [symbol, row] of Object.entries(CRYPTO_ROWS)) {
  .
  .
  .
  // after calculating the value, push it to your list
  values.push(currentValue);
}

// tracks current values in historical chart
sheet.appendRow(values);

If you’re curious as to what todaysDateAsString looks like, it looks like the following:

1
2
3
4
5
6
7
8


function todaysDateAsString() {
  var today = new Date();
  var todayMonth = today.getMonth() + 1;
  var todayDay = today.getDate();
  var todayYear = today.getFullYear();

  return `${todayMonth}/${todayDay}/${todayYear}`;
}

Woot! Now, at the push of a button, the script can do both of the things that I was previously doing manually. Additionally, it can be automated so that it doesn’t even require a button push. Time saved to go dig into some other aspect of our finances.

Helpful Resources

If you’re interested, the full script can be found as a gist here: Tracking Changes in Cryptocurrency Value.

If you find yourself on a related journey, you may also find the following resources useful: