BQL: Exclude zero sum accounts

[Maayaas]

Hi,

I am using the query below to get a list of outstanding payments:

SELECT
    last(date), leaf(account), sum(position) as outstanding
WHERE
    account ~ "AccountsReceivable"
GROUP BY leaf(account)
ORDER BY outstanding DESC

It works as intended but it also lists the zero balance accounts. Is there a way to exclude them?

Thanks.

[redst...@gmail.com]

No way to currently do it AFAIK. A HAVING clause is on the wish list. See this comment.

[Maayaas]

Thank you. It looks like I need to write a script to do this instead of searching for a BQL recipe.

[Daniele Nicolodi]

On 21/01/2022 16:05, Maayaas wrote:
> Thank you. It looks like I need to write a script to do this instead of
> searching for a BQL recipe.

You can easily write a script that post-processes the data returned by a
BQL query. I often use Petl in combination with BQL for this. A simple
example solving your issue:

import click
import petl
from beancount import loader
from beancount.query import query

@click.command()
@click.argument('ledger')
def main(ledger):
entries, errors, options = loader.load_file(ledger)

rtypes, rows = query.run_query(entries, options, """
SELECT
last(date) as date,
leaf(account) as account,
sum(position) as outstanding
WHERE
root(account, 2) = "Assets:Receivable"

GROUP BY leaf(account)
ORDER BY outstanding DESC

""")

table = petl.wrap(rows) \
.pushheader([name for name, rtype in rtypes]) \
.select('outstanding', lambda x: not x.is_empty())

print(table.lookallstr(style='simple'))

if __name__ == '__main__':
main()

Cheers,
Dan

> On Friday, January 21, 2022 at 1:38:10 AM UTC-8 redst...@gmail.com wrote:
>
> No way to currently do it AFAIK. A HAVING clause

> <https://github.com/beancount/beancount/issues/114> is on the wish
> list. See this comment
> <https://github.com/beancount/beancount/issues/114#issuecomment-632815268>.

>
> On Thursday, January 20, 2022 at 9:51:06 PM UTC-8 Maayaas wrote:
>
> Hi,
>
> I am using the query below to get a list of outstanding payments:
>
> SELECT
>     last(date), leaf(account), sum(position) as outstanding
> WHERE
>     account ~ "AccountsReceivable"
> GROUP BY leaf(account)
> ORDER BY outstanding DESC
>
> It works as intended but it also lists the zero balance
> accounts. Is there a way to exclude them?
>
> Thanks.
>

> --
> You received this message because you are subscribed to the Google
> Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to beancount+...@googlegroups.com
> <mailto:beancount+...@googlegroups.com>.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/beancount/dfbab351-b922-4959-b487-865e9b52fdden%40googlegroups.com
> <https://groups.google.com/d/msgid/beancount/dfbab351-b922-4959-b487-865e9b52fdden%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Daniele Nicolodi]

On 21/01/2022 10:38, redst...@gmail.com wrote:
> No way to currently do it AFAIK. A HAVING clause

> <https://github.com/beancount/beancount/issues/114> is on the wish list.
> See this comment
> <https://github.com/beancount/beancount/issues/114#issuecomment-632815268>.

Turns out that a naive implementation of HAVING was not very difficult
to put together https://github.com/beancount/beanquery/pull/22

This implementation is naive in the sense that the expression in the
HAVING clause is computed from scratch instead than trying to reuse
aggregate expression computed as part of the SELECT. However, this is
not an optimization that is done in other parts of BQL either.

Given an implementation of an empty() function that checks whether an
inventory is empty https://github.com/beancount/beanquery/pull/21 you

can write:

SELECT
last(date) as date,
leaf(account) as account,
sum(position) as outstanding
WHERE
root(account, 2) = "Assets:Receivable"
GROUP BY leaf(account)

HAVING not empty(sum(position))
ORDER BY outstanding DESC

Cheers,
Dan

> On Thursday, January 20, 2022 at 9:51:06 PM UTC-8 Maayaas wrote:
>
> Hi,
>
> I am using the query below to get a list of outstanding payments:
>
> SELECT
>     last(date), leaf(account), sum(position) as outstanding
> WHERE
>     account ~ "AccountsReceivable"
> GROUP BY leaf(account)
> ORDER BY outstanding DESC
>
> It works as intended but it also lists the zero balance accounts. Is
> there a way to exclude them?
>
> Thanks.
>

> --
> You received this message because you are subscribed to the Google
> Groups "Beancount" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to beancount+...@googlegroups.com
> <mailto:beancount+...@googlegroups.com>.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/beancount/4c37598c-cd96-4121-9554-86a531711d01n%40googlegroups.com
> <https://groups.google.com/d/msgid/beancount/4c37598c-cd96-4121-9554-86a531711d01n%40googlegroups.com?utm_medium=email&utm_source=footer>.

[Martin Michlmayr]

* Daniele Nicolodi <dan...@grinta.net> [2022-01-21 22:51]:

> Turns out that a naive implementation of HAVING was not very difficult to
> put together https://github.com/beancount/beanquery/pull/22

Thank you, Daniele! I also asked about this feature in the past.

--
Martin Michlmayr
https://www.cyrius.com/

[Martin Blais]

That's great.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/Yes2/ZrEu5/7u7q6%40jirafa.cyrius.com.

[Maayaas]

Thanks Dan. Your script worked pretty well.

[Maayaas]

That's quite a feat, thanks!

I am currently using beancount2 and fava (python3 -m pip install beancount fava). Is it possible to integrate this unreleased beanquery into this flow?

[Daniele Nicolodi]

On 22/01/2022 06:32, Maayaas wrote:
> That's quite a feat, thanks!
>
> I am currently using beancount2 and fava (python3 -m pip install
> beancount fava). Is it possible to integrate this unreleased beanquery
> into this flow?

beangulp should work fine with Beancount v2 thus you should be able to
install it just fine. However, if you want to run queries through the
Fava web interface, you would need to hack Fava to use beanquery
instread of beancount.query (at this point a mechanical rewrite of the
import statements is sufficient). I don't plan to backport changes like
these to Beancount v2.

Cheers,
Dan

[Stefano Zacchiroli]

On Sat, Jan 22, 2022 at 11:00:18PM +0100, Daniele Nicolodi wrote:
> > I am currently using beancount2 and fava (python3 -m pip install
> > beancount fava). Is it possible to integrate this unreleased beanquery
> > into this flow?
>
> beangulp should work fine with Beancount v2 thus you should be able to
> install it just fine.

(You meant beanquery above, right? I'm assuming you did.)

I've tried this in a fresh virtualenv and it doesn't work out of the
box. beanquery depends on "beancount>=3.0.dev0", so it cannot be
installed side by side with beancount at least as shipped from pypi.
What's the recommended way to achieve that with the current split code
base? I'm interested too.

Thanks!
--
Stefano Zacchiroli . za...@upsilon.cc . upsilon.cc/zack _. ^ ._
Full professor of Computer Science o o o \/|V|\/
Télécom Paris, Polytechnic Institute of Paris o o o </> <\>
Co-founder & CTO Software Heritage o o o o /\|^|/\
Former Debian Project Leader & OSI Board Director '" V "'

[Daniele Nicolodi]

On 23/01/2022 09:19, Stefano Zacchiroli wrote:
> On Sat, Jan 22, 2022 at 11:00:18PM +0100, Daniele Nicolodi wrote:
>>> I am currently using beancount2 and fava (python3 -m pip install
>>> beancount fava). Is it possible to integrate this unreleased beanquery
>>> into this flow?
>>
>> beangulp should work fine with Beancount v2 thus you should be able to
>> install it just fine.
>
> (You meant beanquery above, right? I'm assuming you did.)

Ops. Sure, I meant beanquery in this context. Although, the statement
holds true for beangulp as well (with the same caveats, see below).

> I've tried this in a fresh virtualenv and it doesn't work out of the
> box. beanquery depends on "beancount>=3.0.dev0", so it cannot be
> installed side by side with beancount at least as shipped from pypi.
> What's the recommended way to achieve that with the current split code
> base? I'm interested too.

I should have checked this. Indeed the package metadata is written to
require the v3 development version of Beancount.

I tried to relax the version requirement for beanquery and have our CI
to run the beanquery tests against Beancount from git master and from
the latest PyPI release. However, the beangulp testsuite in a few places
assumes Beancount v3 syntax [1] thus the test suite fails with
Beancoiunt v2. I don't think it is worth the effort and the complication
to make the test suite work with both versions (although, I am open to
review and merge patches, if someone wants to work on this).

I don't feel comfortable relaxing the Beancount version required by
beanquery if there isn't CI in place preventing regressions.

As of now, my recommendation is to install beanquery along Beancount v3.
Except for the backward compatibility break noted above, v3 is expected
to work on v2 ledgers. Wanting to keep using v2 for other things, I
would install this combination in a dedicated venv from which to run the
bean-query command.

[1] the only backward compatibility break in Beancount v3 so far is the
syntax for transactions and posting flags using capital letters, which
must be prefixed with ' in the v3 syntax. [A-Z] flags are used to mark
automatic transactions, and beangulp has a few tests for the handling of
these.

Cheers,
Dan

[Stefano Zacchiroli]

On Sun, Jan 23, 2022 at 12:11:24PM +0100, Daniele Nicolodi wrote:
> As of now, my recommendation is to install beanquery along Beancount v3.
> Except for the backward compatibility break noted above, v3 is expected to
> work on v2 ledgers. Wanting to keep using v2 for other things, I would
> install this combination in a dedicated venv from which to run the
> bean-query command.

Thanks for this background and explanation. Installing beanquery
together with beancount v3 worked for me as well.

(Unfortunately I cannot actually use bean-query on my own v2 beancount
ledger, because I use as extension Fava's auto_commit, and Fava is
incompatible with beancount v3, as discussed early on in this thread,
but that's an unrelated problem.)

Cheers