Crypto/Fiat Net Worth Tracking

Crypto/Fiat Net Worth Tracking

Updated regarding Google and privacy: I think privacy and convenience are generally a trade off. I think Google Sheets is a better alternative to Blockfolio or similar apps that phone home and probably do data analysis of your holdings to inform their investment decisions (or sell that data on to other parties). The ultimate solution would be a completely open source system that you can access on both your desktop and phone, although that would require a centralized server to sync your data, and could be exploited. I could make an OSS desktop-only or mobile-only solution, but again, it’s that trade off of time investment vs privacy. I think for the vast majority that have holdings of less than $250,000, Google Sheets is one of the best options available at the minute. I’m more than happy to promote any better alternatives if you have suggestions :-).

Setup

Click here and copy the spreadsheet by going to File -> Make a Copy.

There are two tabs: one is for your current net worth and the other is for your net worth over time. First you need to fill in your current holdings in the Net Worth tab (this is private to your Google Drive unless you explicitly choose to share it). Simply identify the row of the currency you hold on the left (in purple), then move over to the column which has the matching currency name, and put the amount you hold in that cell. Don’t worry about putting in new currencies that you hold yet - let’s just get it all working with a few rough numbers in there. I’ve included directions on how to add other currencies below.

Click Tools -> Script Editor. This should open a new tab in your browser. Give the script a name by clicking on Untitled project on the top left and putting in something like Net Worth Over Time. Then paste following code in the code editor:

function recordDailyNetworth() {

  var netWorthSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Net Worth");
  var nwOverTimeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Net Worth Over Time");
  
  var totalUsdValue = netWorthSheet.getRange("totalUsdValue").getValue();
  var fiatUsdValue = netWorthSheet.getRange("fiatUsdValue").getValue();
  var cryptoUsdValue = netWorthSheet.getRange("cryptoUsdValue").getValue();
  
  var percentCryptoBtc = netWorthSheet.getRange("percentCryptoBtc").getValue();
  var percentCryptoAlt = netWorthSheet.getRange("percentCryptoAlt").getValue();
  
  var todaysRowNumber   = nwOverTimeSheet.getRange("N1").getValue();
  
  nwOverTimeSheet.getRange(todaysRowNumber, 3).setValue(fiatUsdValue);
  nwOverTimeSheet.getRange(todaysRowNumber, 4).setValue(cryptoUsdValue);
  nwOverTimeSheet.getRange(todaysRowNumber, 5).setValue(totalUsdValue);
  
  nwOverTimeSheet.getRange(todaysRowNumber, 8).setValue(percentCryptoBtc);
  nwOverTimeSheet.getRange(todaysRowNumber, 9).setValue(percentCryptoAlt);
}
  
  
function updateRates() {
  
  var netWorthSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Net Worth");
  
  var btcUsdRate = getRateForCurrencyId('bitcoin');   
  var xrpUsdRate = getRateForCurrencyId('ripple'); 
  var bchUsdRate = getRateForCurrencyId('bitcoin-cash'); 
  var ethUsdRate = getRateForCurrencyId('ethereum');
  var lskUsdRate = getRateForCurrencyId('lisk');
  var neoUsdRate = getRateForCurrencyId('neo');
  var snglsUsdRate = getRateForCurrencyId('singulardtv');
  var vtcUsdRate = getRateForCurrencyId('vertcoin');
  
  netWorthSheet.getRange("btcUsdRate").setValue(btcUsdRate);
  netWorthSheet.getRange("bchUsdRate").setValue(bchUsdRate);
  netWorthSheet.getRange("xrpUsdRate").setValue(xrpUsdRate);
  netWorthSheet.getRange("ethUsdRate").setValue(ethUsdRate);
  netWorthSheet.getRange("lskUsdRate").setValue(lskUsdRate);
  netWorthSheet.getRange("neoUsdRate").setValue(neoUsdRate);
  netWorthSheet.getRange("snglsUsdRate").setValue(snglsUsdRate);
  netWorthSheet.getRange("vtcUsdRate").setValue(vtcUsdRate);
}
  
  
function getRateForCurrencyId(currencyId) {
  
  var url = 'https://api.coinmarketcap.com/v1/ticker/' + currencyId + '/';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  return parseFloat(data[0]['price_usd']);
}

Click Run -> updateRates and then Run -> recordDailyNetworth. Go back to the Sheet, open the Net Worth Over Time tab and make sure a row has been entered for todays date. If all is well, go back to the script editor and Click Edit -> Current Project's Triggers. Then click No triggers set up. Click here to add one now., and make triggers look similar to this:
Triggers Example

Adding a Currency

Fiat

Simple - Adding One Currency

The simplest way to add a fiat currency is to convert the THB (Thai Baht) row/column that is already there to the currency you want. For example if you don’t care about THB and do care about AUD, then anywhere you see THB in a cell in Row 11 or Column D, replace with AUD.

Complex - Adding Multiple

Insert a new row above the BTC row, and fix the ranges used for the Total Fiat USD Value and Total Crypto USD Value cells. Insert a column to the left of the BTC column and enter the value in terms of the currency that you hold. In the USD column, you’ll need to add something like =GoogleFinance("CURRENCY:GBPUSD")*E12, but replace GBP with the 3-digit code for the currency you added, and replace E12 with the cell that has the local currency’s value in it. You’ll need to copy and paste the other conversions as well. They all reference Named Ranges, so it should just be a simple copy/paste.

Cryptocurrency

If you’d like to add another cryptocurrency, insert a new row below the BTC row and edit the ranges for Total Fiat USD Value and Total Crypto USD Value as above. Add a column to the right of BTC, and enter the value in terms of the new currency. Then, in N6 put your currency’s name (for example:XMR). Click on cell N7, then click Data -> Named Ranges, and name the cell something like xmrUsdRate. Go to https://api.coinmarketcap.com/v1/ticker/ and find the id field for the currency you added (in this case it would be monero). In the script editor (if you closed it you can open it with Tools -> Script Editor) add a line below var vtcUsdRate = ... with your new rate. In the XMR example, it would be var xmrUsdRate = getRateForCurrencyId('monero');. The value in getRateForCurrencyId must match the id you got from the Coin Market Cap API. Add a line below netWorthSheet.getRange("vtcUsdRate")... that sets the rate for your newly-added coin. Continuing the example, that would be netWorthSheet.getRange("xmrUsdRate").setValue(xmrUsdRate);. Note that the value in getRange must match the “Named Range” you set for the currency previously in the sheet and the value in setValue must match the name of the variable you just created. Be sure to put the newly-added line inside of the }. Run the script to make sure it works by clicking Run -> updateRates. Check in your sheet that the price has been populated. Then in the USD value for your newly added cryptocurrency, put in ={a} * {b} where {a} is the column of the newly added currency along with the current row number (something like I17), and {b} is the Named Range of your rate (something like xmrUsdRate).

Updates

  • 9 October 2017 - mcr55 posted a much easier way to get/add crypto conversion rates on Reddit: mcr55's Reddit Comment
  • 9 October 2017 - Added a table of contents and a simple way of adding 1 new fiat currency.
  • 12 October 2017 - The delimiter in mcr55’s suggestion should be a ‘;’ instead of a ‘,’. This only affects certain locales, as ‘,’s are used in place of ‘.’s in some locales. This was discovered by saviongl0ver on Reddit: saviongl0ver's Reddit Comment

Contact Me

Is there something in this post that’s incorrect, unclear, or could be improved? Tweet or DM me on Twitter. If you liked this post, feel free to follow me :-).


Jonathan Sterling

Written by

In March 2017, I quit my job as a software engineer and began travelling around the world. This unmonetized, open-source blog exists to document my journey, share my thoughts, and let my friends and family know that I'm not dead.

Updated