Best practice for removing Closed account from Balance sheet but not losing Historical data

I checked the community a bit but couldn’t get a general sense what best practice was for this. I have some newly Closed accounts that are no longer able to connect (since they are Closed), however, the last pulled balance and the account is still showing up in the Balances sheet. This is causing a skewed Net Worth and Asset amount, since it’s counting the balance of an account that’s closed. So, what’s the best practice for removing a closed account in the Balances sheet while retaining the historical net worth data (up to the date of closure, ideally)? If I set the Closed account to Hide, it removes it from Balances but it also removes all historical data from Net Worth and other important historical sheets. If I Remove the account from the Tiller Console, I’m concerned it’s going to delete it from Balances and also remove the Historical data (which I won’t be able to recover, unlike the current “Hide” path). Not sure if there are any other options built-in to Tiller to make this work as intended. In Mint, it was as easy as going to the account management page, selecting “Inactive” under the status, and setting the date of Inactivity. Historical data was preserved, but the account was removed from the Balances section.


UPDATE: Here is my current workaround which requires just a few one time edits. Please let me know if anyone knows if this will cause any issues with other sheets. It seems to work as intended for Balances and Net Worth as described above.

  1. On the Accounts sheet, I chose Data Validation and added another value called “Closed” under the “Hide” option for Column D. I then added a “Closed” value in column “D” (“Hide”) for each of the account rows I wanted to hide from Balances but retain all historical sheets like Net Worth.

  2. On the Balances sheet, cell K3, I adjusted the formula as follows: =iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Asset",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true)) //// All I did with this formula was add ,indirect(Y18)<>"Closed" after indirect(Y18)<>"Hide"
    This will just filter out any Accounts with “Closed” in column D (the “Hide” column).

  3. Finally, since this Closed account now has a 0 balance, I went into the Balance History sheet and made sure to insert a new row, copying and pasting a previous row with the now Closed account. I updated the Date/Time of that new row to be the date of account closure and set the balance to 0.

My only issue with this solution is that the Institution that the account was connected with no longer connects so I’m seeing that “1 Feed needs refreshing” message on the Tiller extension in Google Sheets. Since I don’t want to close these accounts (for fear of losing all historical data), and I don’t want to delete that Institutional connection (for the same fear), I’m not sure what to do about this. Any ideas would be welcomed. Would have to always have that annoying message there every time I use the extension.

Just want to be clear here. The account I’m referring to is an account from an Institution that merged to a new company and so the old Institution is no longer able to be connected to. So all connection attempts in Tiller to said website will fail. The accounts there are obviously closed, too. I do not see a need to retain any Historical data in future Tiller sheets as this is my Master sheet which I backup regularly, so I have the data I need. Would “Removing” said Closed accounts from the old Institution that I do not need data from retain all previous historical data in my connected Tiller sheet? Net Worth, Transactions, Balance History, etc? If so, then I think Removing it will work fine, assuming that when I remove all the accounts associated with old Institution, that Institution will no longer attempt to connect in Tiller and I will no longer see that connection message in the Tiller extension/Console. So, will that be the best solution for me here? My solution above works well and accomplishes what I need, but I just am going to see this connection error in the extension/console until the end of time. Would rather not have that if possible. Just don’t want to Remove an account and then see that data disappear in my current sheet, which is what I am trying to make sure is 100% not going to happen in any way.

Thanks!

Nope, even if I Remove the accounts from the Tiller Console, I still see the Removed account appear on the Balances page. I decided to keep my solution from the initial post in this thread plus remove the Closed accounts from the Tiller console as a solution to remove the Institutional connection error in Tiller extension/console.

Try changing the last entry in Balance History to 0.

1 Like

I like the idea to mark something “inactive” but keep all of the data visible in some sheets. We don’t have a solution for this but your use case makes sense.

I’ve handled this in the past by manually recording a transfer out on the old account that reduced the balance to 0 (and then manually adjusting the balance), with a matching transfer in on the new account at the new institution for the starting balance. This is in keeping with double-entry account principles, so you’re not “inventing” money out of thin air. This way it won’t skew your net worth and asset amounts.

Admittedly, this really only solves half of your problem. An “Inactive” option would be nice for closed accounts where we want to retain the data.