Automating your CompiledSanity Finance Sheet with UpBank

July 16, 2021

Compiled Insanity Finance spreedsheets allow you to track your ETFs, Stocks, Property, Assets and more. You can get the free version, however the Paid one which $8 AUD is worth it. Each month you update your CompiledSanity Personal Finance Template by updating your Cash Values and generally your Super.

Doing so produces the reports for the month. UpBank Recently released their API and i wanted to see how hard it would be to automate my Cash Values using GoogleSheets & The UpBank API.

Before you start

I created this for my own purposes, so the code is a bit of trash. also, I would recommend you make a backup of your CompiledSanity Personal Finance Template first in case anything goes horribly wrong during getting this setup.

I have not tested this with version migrations either.

UpBank

UpBank in my opinion is the best MobileBanking App in Australia right now, if you don’t have an account already you can do so by downloading thier app from the Google Play Store or Apple Store.

Automating your Monthly Cash Values

Google Sheets allows you to write Scripts which is how some magic in CompiledInsanities Personal Finance Templates works To Automate our own Spreadsheet we will add our own script which will allow us to automate updating our cash values with the click of a button.

Obtaining your UpBank Bearer Token

In order to automate your accounts, you will need to obtain a bearer token from UpBank. This can be done by visiting their getting started page Make sure you keep this safe, don’t commit it to github or any public pages otherwise people will have access to your Bank Account via the API key.

Adding updateBankAccounts script.

• Click Tools -> Script Editor this will open up a new Tab which is where we will create our script.
• Click the + Button next to files
• Name your Script, I have named mine updateBankAccounts.gs

Breaking down the script.

The first section adds a Menu Option to your Google Sheets, Now you can probably do this another way using the GoogleSheets Events such as onOpen(e) in this example I’ll be using the Menu Bar though.

Once you run the script you should expect to see a new Menu as below.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // The Name of the Menu Item.
  ui.createMenu('update cash')
  // The Item under the menu
      .addItem('up yeah','updateBankAccounts')
      .addToUi();
}

When you click on up yeah it calls the updateBankAccounts function which is responsible for retrieving our Accounts & updating our Spreadsheet.

Next we’ll add the function to call the UpBank Api.

function updateBankAccounts() {
   // Make sure you replace your_auth_token your bearerToken below
   var authToken = 'your_auth_token';
   var options = {
    contentType: 'application/json',
    headers: {
    'Authorization': `Bearer ${authToken}`
  }
};
   // Make the Request to upBank using your bearerToken
   var response = UrlFetchApp.fetch("https://api.up.com.au/api/v1/accounts", options);
   var responseBody = response.getContentText();
   var json = JSON.parse(responseBody);
   // Now there's probably better ways you can achieve this,
   // This worked fine for me though, was just a matter of cross
   // referencing the values you can also dump the request if you need to
   // and check what it correlates to.
   var daily_expenses = json.data[0].attributes.balance.value
   var savings = json.data[1].attributes.balance.value
   var bills = json.data[2].attributes.balance.value;
   var extinguisher = json.data[5].attributes.balance.value;
   var splurge = json.data[6].attributes.balance.value;
   var computer = json.data[4].attributes.balance.value;
   var cash = SpreadsheetApp.getActive().getSheetByName('Cash')
   // Update the Cash Values in your SpreadSheet as needed.
   cash.getRange('C2').setValue(daily_expenses);
   cash.getRange('C4').setValue(savings);
   cash.getRange('C3').setValue(splurge);
   cash.getRange('C5').setValue(bills);
   cash.getRange('C6').setValue(extinguisher);
   cash.getRange('C7').setValue(computer);
}

Testing

• Run The script inside the script editor, this will create the UI menu in your Sheets.
• Click on the newly created Menu Item.
• Wait for your cash values to update.

Try moving a $1 from one account to another via your UpBank Mobile application then go ahead and update your new menu item & see your cash values update.

Wrapping up

I enjoy Finance / Programming / FinTech, I’ll be definitely posting more on these topics in the future, follow me on twitter to keep up to date with anything I am working on.