Remove a Closed account from Balances sheet without losing historical data

Google Sheets Show & Tell
, ,
1

I checked the community a bit but couldn’t get a general sense what the best practice was for the following:

I have some newly Closed accounts that are no longer able to connect (since they are Closed and the Institution has no more active accounts), 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 or Liability amount, since it’s counting the balance of an account that’s closed. If I set the Closed account to Hide (Column D on the Accounts sheet), it removes it from Balances but it also removes all historical data from Net Worth and other important historical sheets, which is not the intention.

In Mint by Intuit, it was as easy as going to the account management page, selecting “Inactive” under the status, and setting the date of Inactivity. Historical (e.g. Net Worth) data was preserved, but the account was removed from the Balances section.

So, here is my solution to this issue, which requires just a handful of one time edits. It works 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, in the “Accounts” sheet) for Assets.

  1. Also, on the Balances sheets, cell R3, 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)="Liability",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, in the “Accounts” sheet) for Liabilities.

  1. 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.

  2. The only issue remaining is that the Institution that the closed account was connected with no longer connects (this will only happen if ALL accounts are closed at the Institution), so I’m seeing a “1 Feed needs refreshing” message on the Tiller extension in Google Sheets. So, the solution (for me) was to remove that Institution from the Tiller Console. Removing the Institution with no remaining active accounts from Tiller does NOT remove the data from your current sheet. It will, however, remove the data from the Tiller server so keep that in mind in case you need to redownload data into a new sheet at some point in the future. This is a non-issue for me so I removed the inactive account/Institutional connection from the Tiller Console and now no longer see the error trying to connect to an account that is inactive.

Now, I’m able to see all Closed accounts reflected in historical sheets (such as Net Worth), while also no longer seeing that account appear on the Balances sheet, and also no longer seeing a connection error in the Tiller Google extension.

4

Try changing the last entry in Balance History to 0.

1 Like
5

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.

6

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.

7

Thank you for sharing this, it’s just what I was looking for :+1:

I made the same change to cell R3, which is for Sorted Liabilities.
(Cell K3 is Sorted Assets)

2 Likes
8

Just want to add the formula for the Asset vs Liabilities.
Assets (K3)

=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))

Liabilities (R3)

=iferror(sort(filter({INDIRECT(Y11),INDIRECT(Y12),INDIRECT(Y13)&if(isblank(INDIRECT(Y14)),""," ("&INDIRECT(Y14)&")"),today()-INDIRECT(Y15),Indirect(Y16)},indirect(Y17)="Liability",indirect(Y18)<>"Hide",indirect(Y18)<>"Closed"),2,true, 4, true, 3, true))
1 Like
9

I edited the original post to include both formulas (Assets and Liabilities) and updated the content to remove my comments trying to troubleshoot the issue since it’s a working solution.

1 Like
10

Here is an extension of this solution for the Net Worth sheet.

People often want to Hide accounts from the Net Worth report for time periods after the account was closed, but still want them visible for when the account was open.

This uses the Accounts sheet Last Update date for the Closed date.
The Last Update date can be changed by changing the Balance History for the closed account.

Change Net Worth sheet cell AC7 to add the following to the QUERY’s WHERE clause:

AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"'))

Where:
AX17 = Last Update date (aka Closed date for closed account)
AI2 = Net Worth report starting date range

Here are the before/after Net Worth sheet cell AC7 formulas:
BEFORE:

=query(indirect(AX24),"SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&" WHERE "&AX13&" <> '' AND "&AX23&" <> 'Hide' ORDER BY "&AX21&", "&W4&", "&AX14,-1)

AFTER:

=QUERY(INDIRECT(AX24),
"SELECT "&AX21&", "&W4&", "&AX13&", "&AX14&", "&AX15&"
 WHERE "&AX13&" <> ''
   AND "&AX23&" <> 'Hide'
   AND ("&AX23&" <> 'Closed' OR ("&AX23&" = 'Closed' AND "&AX17&" >= date '"&TEXT($AI$2, "yyyy-mm-dd")&"'))
 ORDER BY "&AX21&", "&W4&", "&AX14
,-1)

Where I have changed the formula format for better readability.

3 Likes
11

Awesome. That will definitely help everyone. If you add the following 3 symbols `` ` (without the spaces) before and after the formulas, it will create a code block and make it easier for people to just copy and paste the correct formulas without them accidentally copying extra symbols or letters. Would be helpful for new users who aren’t as experienced. Thank you again.

1 Like
12

Will this affect the new Spending Trends solution that has replaced the Net Worth solution?

Also, I am unsure if this is related but I input this formula into the Net Worth sheet cell AC7. On one of my sheets where I haven’t updated the accounts and balance sheets, the formula pulls in all the data, and I do not see any errors. On my main sheet I have updated the accounts sheet, and I believe the Balance Sheet. All the sheets included in the new foundations template. On this template I receive the error “Did not find value ‘Hide’ in MATCH evaluation.” The formula that I copied was the last one titled “AFTER.” And nothing populates in the Net Worth sheet.

13

No, Spending Trends just reports today’s Net Worth.
The Net Worth sheet reports historical Net Worth.

Did you maybe accidentally change the right-most Hide column header name on the Accounts sheet? That would also break the Spending Trends Net Worth value.
image

1 Like
14

No my headers for my accounts sheet are labeled correctly

image

Edit: I went ahead and restored the Net Worth sheet and inserted the formula into the cell AC7. I must have messed something else because now I am no longer receiving any errors. Thank you.

2 Likes
15

I have finally gotten around to trying to do this in my sheet but it looks nothing like this. I am looking at the hidden columns and they do not correspond to this at all. Wondering if there is a version issue?

16

Note that this is for Google Sheets and not Excel, that could cause a difference.

Here is the version info I see in Tiller Community Solutions.
image

17

Oops , I am in Excel, it is totally different. I always thought I was pretty handy in excel but I have been looking at the formulas trying to see where I could insert something that does not show accounts that have zero balance for the selected range but sadly it seems beyond my capabilities.

18

I moved your original post into the Excel category.

I haven’t looked into doing this in Excel, yet, as Sheets is my primary usage. Sheets uses a really cool QUERY function for this in the Net Worth sheet that doesn’t exist in Excel.

I do have an Excel sample data spreadsheet linked to Tiller that I sometimes dabble with, so I might poke around in it, but not sure if/when I’ll get to it :confused:

19

thanks! Obviously, this is not a critical feature but it would make the report much cleaner.