Advice for modifying Accounts sheet to better handle closed accounts?

Google Sheets
1

I’ve consolidated several accounts, and my retirement account has moved between custodians. As a result I’ve got a few closed accounts that are cluttering up reports.

I understand that the purpose of the Hide override is to hide these accounts from balanced based reports, but this isn’t precisely what I want in some cases.

For example, as implemented, if I Hide an account, it will disappear completely from the net work report. This means that if I had $X in old retirement account, and I hide it because that was moved to new retirement account, instead of a smooth graph it will be dropped from history, and there will be a spike on account transition date.

In my balances sheet, I did make an entry on the date of the transfer setting the balance of the old account to $0, and setting the status to CLOSED.

Modifying the Accounts sheet is a bit of a bear because of the hard-coded columns, but I added a column in the left hand section called Date Closed, and intend to update Net Worth so that the table at the bottom of the sheet doesn’t list an account if it was closed for the entire report period. (Effectively, this new column acts as a “hide after this date” column.)

My question is, is it possible to derive this information from the balance sheet by copying the Status column for the last entry into the Accounts sheet? How do I do this?

Absent a good solution for that, my manual solution is acceptable, but having denormalized data for derived values is always a source of potential inconsistencies.

2

OK, I figured out how to modify the Balance column to copy over the Account Status:

={
  "Account Status";
  LET(
    COLUMN_LETTERS,
    MAP({"Account Id";"Date";"Account Status"},LAMBDA(HEADER,REGEXEXTRACT(ADDRESS(1,MATCH(HEADER, indirect("'Balance History'!$1:$1"), 0)), "[A-Z]+"))),
    RANGES,
    MAP(COLUMN_LETTERS,LAMBDA(COLUMN_LETTER,"'Balance History'!$"&COLUMN_LETTER&"$2:$"&COLUMN_LETTER)),  
    BYROW(
      INDIRECT("G2:G"),
      LAMBDA(
        ACCOUNT_ID,
        IFERROR(IF(ISBLANK(ACCOUNT_ID),1/0,INDEX(SORT(FILTER({INDIRECT(INDEX(RANGES,2)),INDIRECT(INDEX(RANGES,3))},INDIRECT(INDEX(RANGES,1))=ACCOUNT_ID),1,FALSE),1,2)))
      )
    )
  )
}

(Updated to use column ranges from the stock Accounts sheet.)

2 Likes
3

Would applying the same Account ID to the consolidated accounts in your Balance History sheet help? That way you can keep the Net Worth history, as if it’s belonged to a single account all along. How to handle duplicated accounts in my spreadsheet | Tiller Help Center

4

Consolidating is more complicated than a simple rename of old account to new, because their periods of non-zero balance overlaps.

I have a solution that works for me now. We’ll have to see how much I regret the customizations I’ve made if Foundation Template or Net Worth Sheet is updated.

1 Like