Is there an existing solution to easily manually update the Balance History for a number of accounts?

Conversation Ask Anything
1

Many of my accounts require manually uploading transactions as the bank feeds just don’t work. I typically do so at the end of the month. I also manually update the balance history for these accounts as the transactions downloaded from the bank feeds don’t always have the balances on them - and the current manual import functionality in the console doesn’t update balances.

I’m wondering if anyone has created a solution to more easily update the balance history other than inserting new rows in the Balance History sheet, copying and pasting the rows from the previous balances and then updating the balances and dates. It’s not a massive effort doing it this way - but just wondering if there’s a solution out there.

If not, I’ll likely create one and share it here. It’ll probably be something like having a sheet with the accounts that need to be manually updated listed, and a field to enter the balances along with a date field. I’d enter the date once - and the balances one by one - and select a button to populate all the various columns in the Balance History sheet. Happy to know if anyone has a better idea.

2

Consider leveraging this Apps Script for the button to populate Balance History.

function MoveToBalanceHistory() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  var balanceHistorySheet = spreadsheet.getSheetByName('Balance History');

  // get number of rows with a valid date
  var dates = sheet.getRange('B3:B').getValues();
  var numRows = dates.findIndex((row) => !row[0]);

  // Insert numRows at the top of Balance History sheet
  balanceHistorySheet.insertRowsBefore(balanceHistorySheet.getRange('2:2').getRow(), numRows);

  // Copy Account balances to Balance History sheet
  sheet.getRange(3, 1, numRows, 15).copyTo(balanceHistorySheet.getRange('A2:O2'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL);
  balanceHistorySheet.activate();
}
1 Like