How did you get it to import all your historical net worth into the balance history from Mint? Mine didn’t import anything other than transactions, so I’m looking a a net worth that’s now only 2 months of actionable data, but I’d like to see all 12 years somehow (possible if you select years as the unit of time on the net worth sheet)
I didn’t quite figure it out - I have all of the Mint data but it looks like the Tiller sheet limits it to the last 12 time periods (weeks, months, years) anyway so I would need something else for my 13+ years. I will likely make my own view at some point and just manually enter from this sheet on the first of the month.
Let me know if you’re able to figure something out!
Yeah, that’ll be interesting to me if you do figure that out. But, what I really want to know, is how you got Tiller to import your balance history into the balance history sheet for that entire time period? I used the import tool provided by Tiller, but it didn’t import the balance history into that sheet, so I’m having to manually enter it based on my transactions, and that’s a lot of work for that many years of transactions
You’d just need to manually add the lines to the Balance History sheet in bulk. Here is a page that has details on the balance history sheets components.
I did it one account at a time. I’d make a new blank sheet for an account based on the dates/balances I got from mint. Then I’d copy the Account Num/Institution/Account ID/Type/Class balances for that account from an existing entry in the Balance History sheet. Then I used the formulas from these docs for the month and week values. Then copied/pasted the values from the new sheet into Tiller’s balance history sheet.
Just make a backup/named version first. Also note that the balance history sheet needs to be sorted by date (descending) for Net Worth to work.
Did you actually get a balance history from Mint (all I got was transactions)? Or did you just use the transactions and some sort of formula to work it backwards from the most recent balance? That page you referenced is helpful for understanding what the balance history sheet is and what to add to the columns, but not HOW to generate them based on Mint transactions
Yes. I did it using this Chrome plugin. There is probably another way to do it but the plugin works well. It will generate a folder with one CSV file per account, with as much balance history as Mint has, going back years.
Edit I found instructions on how to get this directly if you don’t want to use a plugin
- Log in to Mint on the browser.
- Navigate to “Trends”.
- Expand either “Assets” or “Debts”, and select “Over time”.
- Select one account (Monarch only supports adding history for one account at a time).
- Select “All Time” or whatever range you want to transfer to Monarch.
- Scroll to the bottom of the screen, click “Export to CSV”. This will download trends.csv.
2 Likes
Oh, I LOVE YOU!! I was racking my brain trying to figure out how best to do this, but your links to that plugin have been a LIFESAVER!!! You just gave me nights and weekends back perpetually. That plugin was SLICK!!
2 Likes
Glad you found @RedNell’s helpful 
that’s super handy to have those steps. I’ve seen this come up in webinar Q&A and in support.
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
- Create the manual account with the Tiller extension if needed
- Import the balance history into a new sheet
- Run the “PrepareMintAccountBalance” automation while on the new sheet
- Copy a row from the “Balance History” sheet for the account into row 2 of the new sheet
- Make sure everything on row 2 is the way you want the rest of the rows to be
- Run the “Autofill” automation to copy from row 2 to the rest of the rows
- Make sure everything looks the way you want it to
- Run the “MoveToBalanceHistory” automation, which will copy everything over to the “Balance History” sheet.
- 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
I gave your scripts a try and the first two seemed to work, but I get an Error running MoveToBalanceHistory:
Error
Exception: Invalid argument
MoveToBalanceHistory @ Code.gs:68
Where code lines:
58:function MoveToBalanceHistory() {
59: var spreadsheet = SpreadsheetApp.getActive();
60: var sheet = spreadsheet.getActiveSheet();
61: var balanceHistorySheet = spreadsheet.getSheetByName('Balance History');
62:
63: // get number of rows with a valid date
64: var dates = sheet.getRange('B3:B').getValues();
65: var numRows = dates.findIndex((row) => !row[0]);
66:
67: // Insert numRows at the top of Balance History sheet
68: balanceHistorySheet.insertRowsBefore(balanceHistorySheet.getRange('2:2').getRow(), numRows);
Here’s what the Date and Time columns look like on my imported balance history sheet:
I did try logging numRows after line 65 and the result was 0.
console.log('numRows: ' + numRows);
Any ideas?
Ah, so I resolved the issue by closing the Apps Script project browser tab, selecting my Tiller spreadsheet with the import tab selected and re-opening Apps Script - then running the script worked fine.
My working sessions were two different days, where the second day was a new PC reboot with Chrome browser tabs restored to previous. I’m not sure how that caused a disconnect, since the App Script project is apart of the Tiller spreadsheet.
Anyhow, know that your scripts have at least helped one other person update Balance History more easily. Thanks for sharing
1 Like
Do you have to go through these steps with every account (csv) that is exported from the monarch tool?
Unfortunately yes. I had 89 accounts, and after doing a couple of them manually, that’s when i decided to write these scripts to speed up the process
Yeah I just exported 100+ plus accounts from mint. Doesn’t sound fun. I saw on Reddit someone just manually entered historic net worth quarterly. Might have to go that route.
Yeah, to be honest though, the most time consuming part was actually creating all the accounts with the manual account tool. After I did that, it was easier to use these scripts I made than I think it would be to manually enter quarterly balance info. Of course, it depends on how many quarters you’d have to enter for each of those 100+ accounts.