Merge account and sum balances

I used to have two brokerage accounts for a while. I recently closed one of them and transferred the balance to another borkerage.

I’d like to retroactively merge these accounts and sum the balances. Since these two accounts were concurrently open and had independent balances, I need to sum the balances while merging for the balances to be accurate. Any suggestions on how to do this?

My first thought is to copy the past history of both accounts into a new sheet, and then merge them by (1) selecting one transaction per account per month to represent the balance, and (2) sum balances of those two transactions to create on transaction per month, (3) copy back to my balance tracking sheet (after deleting old transactions for both accounts).

Would love to hear any simpler ideas!

Not sure why you would want to alter the past balances and transactions, since the current should be accurate, but if there’s a reason, here’s what I would do:

  • On the Balance History sheet, you need to make the following columns match for each account:
    • Account, Account #, Account ID, Institution
  • On the Transactions sheet you need to make the following columns match for each account:
    • Account, Institution, Account #, Account ID

You can do a simple ‘Replace’ to update everything on the sheet, one value at a time. If it doesn’t do what you want, you can always undo or at worst, use Version History to go back to before the changes.

I have a chart that tracks net worth. If I simply replaced the old account name and # with the new account, the old balance wouldn’t be accurate and the net worth calculation would be inaccurate. That’s why I want to sum the past account balances together to create the new merged account. Essentially, I want it to be as if the accounts had never been separate.

Like @jpfieber, I also have some reservations about doing something lossy with the data, but, if it were me, I would:

  • Make a copy of the Balance History sheet in a new spreadsheet
  • Use filters to delete everything but the accounts I wanted to “merge”
  • Use the Trim Balance History workflow in the Tiller Community Solutions Add-on to reduce the data down to one balance record per month
  • Depending on number of rows remaining, either manually combine balances month by month leaving all the other fields for one of the two accounts… or do something clever
  • Delete the merged accounts out of your original spreadsheet
  • Paste the merged data back into your original spreadsheet
  • Sort your updated Balance History sheet
1 Like

There is no harm in either suggested approach @virajmahesh because the accounts will still be separate in our database so if you create a new spreadsheet and link both accounts you’ll get all the historical data we have as separate accounts to start fresh with.

Keep in mind that the Transactions sheet is distinct from the Balance History sheet here and you should be summing the entries from the Balance History sheet. Balance History is hidden by default.

Thanks all! I used @randy 's solution. The only issue was that I couldn’t figure out how to use the trim tool on the copy of Balance History, so I just edited the main sheet directly :sweat_smile: Looks like it all worked out thought. If I hadn’t merged the accounts, the chart would have had a separate line for the closed account forever, which added clutter to the chart.

1 Like

Glad to hear you solved it, @virajmahesh. FYI if you simply want to hide an account on the Net Worth template you can do that with an override on the (hidden) Accounts sheet.