Stale balance query - where not closed

[Paul Walker]

I found a query to help remind me which accounts need attention from this conversation last year:

    SELECT account, max(date) as date FROM #balances GROUP BY account ORDER BY date

Is there any "WHERE not close"d option? I tried the postings-table close_date function, but...

    SELECT account, max(date) as date FROM #balances WHERE NOT close_date(account) GROUP BY account ORDER BY date
error:
...
AttributeError: 'Balance' object has no attribute 'open_close_map'

I wasn't sure if this "implement balances ... where syntax" issue was related. It's not terrible to script picking balances out of the directives list in python, but seems so close to a simple query-based solution.

Thanks,

Paul

[Red S]

This is not a direct answer to your question, but bean-downloads needs-update, available here (which your original thread finally nudged me to do) is what I use, and it solves several other annoyances that a BQL approach had for me, including easy configurability. It filters out closed accounts as well. See below for the man page:

Usage: bean-download needs-update [OPTIONS] BEANCOUNT_FILE Show 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). 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). Commodity leaf accounts are ascribed to their parent. The parent's last updated date is considered to be the latest date of a balance assertion on any child. 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. The BEANCOUNT_FILE environment variable can optionally be set instead of specifying the file on the command line. The (optional) configuration for this utility is to be supplied as a custom directive like the following example in your beancount file: 2010-01-01 custom "reds-importers" "needs-updates" "{ 'included_account_pats' : ['^Assets:Banks', '^Assets:Investments', '^Liabilities:Credit-Cards'], 'excluded_account_pats' : ['.*Inactive', '.*Closed'] }}" Default values for the configuration are: 2010-01-01 custom "reds-importers" "needs-updates" "{ 'included_account_pats' : ['^Assets:', '^Liabilities:'], 'excluded_account_pats' : [] }}" Options: --recency INTEGER How many days ago should the last balance assertion be to be considered old [default: 15] --sort-by-date Sort output by date (instead of account name) --all-accounts Show all account (ignore include/exclude in config) --help Show this message and exit.

​

[Daniele Nicolodi]

On 03/09/24 05:42, Paul Walker wrote:
> I found a query to help remind me which accounts need attention from
> this conversation

> <https://groups.google.com/g/beancount/c/MJYbx6Au7TM/m/yw-sr3PJAQAJ>

> last year:
>
>     SELECT account, max(date) as date FROM #balances GROUP BY account
> ORDER BY date
>
> Is there any "WHERE not close"d option? I tried the postings-table
> close_date function, but...
>
>     SELECT account, max(date) as date FROM #balances WHERE NOT
> close_date(account) GROUP BY account ORDER BY date
> error:
> ...
> AttributeError: 'Balance' object has no attribute 'open_close_map'

This should work in the unreleased version of beanquery from git master.

> I wasn't sure if this "implement balances ... where syntax" issue was related.

It is not.

Cheers,
Dan

[Paul Walker]

On Tuesday, September 3, 2024 at 2:35:38 PM UTC-4 dan...@grinta.net wrote:

On 03/09/24 05:42, Paul Walker wrote:
>     SELECT account, max(date) as date FROM #balances WHERE NOT
> close_date(account) GROUP BY account ORDER BY date
> error:
> ...
> AttributeError: 'Balance' object has no attribute 'open_close_map'

This should work in the unreleased version of beanquery from git master.

 

It sure does, thanks!

Red, thanks for the link. I was inspired by bean-download to dig into ofxget/ofxtools. It's unfortunate direct-ofx's being phased out. One of these days I hope to attempt a selenium-based fetcher.

I like that you also highlight accounts with no balance. I'm not sure how that would work in query without a union operator. I may leave that in python script.

Paul

[Red S]

Red, thanks for the link. I was inspired by bean-download to dig into ofxget/ofxtools. It's unfortunate direct-ofx's being phased out. One of these days I hope to attempt a selenium-based fetcher.

You're welcome, glad it helped. Switching to selenium has been on my list for a while. Please do share if you do so.

About half my banks have continued to support direct-downloads so far. If your bank does, it's trivial to setup ofxget/ofxtools, so you don't have much to lose if/when they stop supporting it.