How Up-to-Date are my Accounts?

Introduction

It is helpful to know what accounts you haven’t updated in a while in your journal. bean-download is a downloader that ships with beancount-reds-importers. It now has a new feature: bean-download needs-update shows a list of accounts needing updates, and the date of the last update (which is defined as the date of the last balance assertion on the account).

Filtering via Configuration

It is helpful to filter out uninteresting accounts, which is done via configuration:

  • Include/exclude list of account regexs: Only accounts in the included list which are not in the excluded list are considered. Both lists are specified as regular expressions. These can be used to include only accounts existing in the real world, and filter out those that are not interesting (eg: accounts known to not be used often). Configuration looks like this:

     2010-01-01 custom "reds-importers" "needs-updates" "{
   'included_account_pats' : ['^Assets:Banks', '^Assets:Investments', '^Liabilities:Credit-Cards'],
   'excluded_account_pats' : ['.*Inactive', '.*Frozen']
 }}"

    Configuration is completely optional

  • Closed accounts are filtered out

  • Accounts matching the criteria above with zero balance entries are also printed out, since by definition, they don’t have a (recent) balance assertion

Per-Account Configuration

If the metadata needs_update_days exists for an account, that will be used instead of the global configuration above. For example:

2024-01-01 open Assets:MyBank USD
  needs_update_days: 90

2025-01-01 open Assets:AnotherBankAccount USD
  needs_update_days: 15

Commodity leaf accounts

A recommended way to hold investment accounts is in commodity-leaf accounts, which look like this:

Assets:Investments:Fidelity-Brokerage:AAPL
Assets:Investments:Fidelity-Brokerage:MSFT
...

You can skip this section if you do not use commodity-leaf accounts.

We detect commodity leaf accounts by matching the leaf against this regular expression: '^[A-Z0-9]+$'. How should we handle commodity leaf accounts? Note that:

  • There is no physical equivalent of these accounts at your bank. The bank only has a single account in the example above, represented by the parent, Assets:Investments:Fidelity-Brokerage
  • So, we are interested only in the recency status of that parent account.
  • Beancount currently offers no way to assert a comprehensive balance across all children of a parent account (no way to assert that Assets:Investments:Fidelity-Brokerage should contain exactly 8 AAPL and 7 MSFT, and nothing more or less; each assert statement can only assert a single commodity)

  • This leaves us with several reasonable ways to handle this situation. Here, we ascribe commodity leaf accounts to their parent:

    The parent’s last updated date is considered to be the latest date of a balance assertion on any child.

    This means it’s possible that there is no balance assertion on MSFT, but the parent is considered to be up to date. This is an unlikely scenario if you use automated importers to import the parent account, since it would have updated all commodities. However, it is possible, but we still choose practicality over strictness since our goal here is a “good-enough” indication rather than an assertion

Can’t I do this using bean-query?

Yes, you can, but it’s not ideal if you use commodity-leaf accounts:

; For non-commodity-leaf accounts:
SELECT account, max(date) FROM #balances WHERE account ~ "^Assets:.*Bank" GROUP BY account

; For commodity-leaf accounts at level 3:
SELECT root(account, 3), max(date) FROM #balances WHERE account ~ "^Assets:.*Brokerage" GROUP BY root(account, 3)

So you will have to:

  • use separate queries for commodity-leaf and non-commodity-leaf accounts
  • run multiple queries for commodity-leaf accounts if you have the at multiple levels of the account hierarchy (eg: Assets:Broker:AAPL and Assets:Work:AnotherBroker:ORNC)
  • use a separate query to determine accounts with no balance assertions

Of course, if none of these apply to you, the bean-query approach is simpler and therefore better. For an often used operation, for me, a small dedicated utility beats the clunkiness of having to remember to execute multiple queries and mentally processing their output.

Conclusion

Example output:

$ bean-download needs-update
Last Updated    Account
--------------  ---------------------------------------------------------
2023-08-01      Assets:Banks:MainOne
2023-02-05      Assets:Gift-Cards-and-Credits:Amazon-com-Gift-Cards
2022-06-01      Liabilities:Credit-Cards:Amex
2017-02-15      Liabilities:Credit-Cards:Discover

Accounts without balance entries:                                                                                                       ------------------------------------------------------------------------
------------------------------------------------------------------------
Assets:Due-From-Tenant:Rent

Most of my regularly used accounts take only a few seconds to update. I run two commands to update 10-12 accounts, and the whole thing takes only one or two minutes for all those accounts together. If you are not at this point yet, needs-update will be very helpful to you. For me, needs-update is primarily useful for me to figure out when I last updated less often used accounts. These include airline miles, Amazon shopping and gift card accounts, credit cards I don’t use often, and anything else that’s escaped my attention.

Notes mentioning this note

Putting it All Together into a Workflow
This is how my workflow looks:
Tools and Utilities
Index of Personal Finance Tools (Written by me)