How did you get your balance history from Mint into Tiller?

Ask Anything
9

For anyone in the same situation as me, I created a few scripts to help with this process. I had 89 accounts that I needed to import the balance history for, so after downloading them from Mint using the tools mentioned, I still needed to format them and copy them into the balance history sheet. Here are the steps I followed

  1. Create the manual account with the Tiller extension if needed
  2. Import the balance history into a new sheet
  3. Run the “PrepareMintAccountBalance” automation while on the new sheet
  4. Copy a row from the “Balance History” sheet for the account into row 2 of the new sheet
  5. Make sure everything on row 2 is the way you want the rest of the rows to be
  6. Run the “Autofill” automation to copy from row 2 to the rest of the rows
  7. Make sure everything looks the way you want it to
  8. Run the “MoveToBalanceHistory” automation, which will copy everything over to the “Balance History” sheet.
  9. Make sure that the balance history sheet is still sorted with the most recent at the top

This series of automations may not be perfect, but it sure helped me to import the balances for my 89 accounts much quicker, so I thought I’d share

/** @OnlyCurrentDoc */

function PrepareMintAccountBalance() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();

  // Move Account to Column D
  sheet.getRange('C:C').moveTo(sheet.getRange('D:D'));

  // Move Amount to Column I
  sheet.getRange('B:B').moveTo(sheet.getRange('I:I'));

  // Move Date to Column B
  sheet.getRange('A:A').moveTo(sheet.getRange('B:B'));

  // Copy the header from Balance History
  spreadsheet.getRange('\'Balance History\'!1:1').copyTo(sheet.getRange('A1'), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

  // Insert a new row below the header that will be used for updating in the next Macro
  sheet.insertRowsBefore(sheet.getRange('2:2').getRow(), 1);

  // Put focus on A2 to make it easy to paste the row
  sheet.getRange('A2').activate();
};

function Autofill() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();

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

  // Set Time Column
  sheet.getRange(3, 3, maxRow - 1, 1).setValue('12:00 AM')

  // Autofill D through F
  sheet.getRange('D2:F2').autoFill(sheet.getRange(2, 4, maxRow, 3), SpreadsheetApp.AutoFillSeries.ALTERNATE_SERIES);

  // Autofill H
  sheet.getRange('H2').autoFill(sheet.getRange(2, 8, maxRow, 1), SpreadsheetApp.AutoFillSeries.ALTERNATE_SERIES);

  // Set Month Column
  sheet.getRange('J2').clearContent();
  sheet.getRange('J1').setFormula('=arrayformula(if(row(B1:B)=1,"Month",if(B1:B<>"",date(year(B1:B),month(B1:B),1),"")))');

  // Set Week Column
  sheet.getRange('K2').clearContent();
  sheet.getRange('K1').setFormula('=arrayformula(if(row(B1:B)=1,"Week",if(B1:B<>"",B1:B-WEEKDAY(B1:B)+1,"")))');

  // Autofill L through O
  sheet.getRange('L2:O2').autoFill(sheet.getRange(2, 12, maxRow, 4), SpreadsheetApp.AutoFillSeries.ALTERNATE_SERIES);
  
  // Format all rows based on row 2
  sheet.getRange('B2:O2').copyTo(sheet.getRange(2, 2, maxRow, 14), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
};

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