can bean-query report include accounts with zero amounts?

[John Rakestraw]

Hi, all --

New user here -- apologies if information I'm seeking is in the docs -- I've searched both the docs and this list and can't find the answer.

I'm attempting to construct a report of monthly spending in a set of accounts, but I'd like the report to include an account in a particular month even if there is no spending in that account. I've found that an account is included (with the spending amount of zero) if there happened to be actual transactions that total to zero (when I spent $40 that was refunded in the same month). But if there's no activity, the account is not included in the report.

Is this possible?

Thanks --

John

[Martin Blais]

How do you produce your reports?
bean-report?
or bean-query?

> --
> 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/1962423c-41a8-40f4-a5f5-9854006440d1%40googlegroups.com.

[John Rakestraw]

Hi, Martin --

I'm using bean-query. Here's one example:

 bean-query /path/to/beancountfile "Select root(account, 3) as Account, SUM(Convert(position, 'USD', date)) AS balance, currency as Total FROM OPEN ON 2019-01-01 CLOSE ON 2019-11-01 WHERE account ~ 'account1|account2|account3|account4'"

This gives me exactly what I want, unless the report is limited to a time period in which there are no transactions in one of the accounts. If there's no transaction involving account 2, for example, then the report lists only accounts 1, 3, and 4. I'd like all of them to be listed, with account 2 showing a position of zero.

Is that possible?  bean-query -f csv -q ~/beancountfiles/accounts/accounts.beancount "Select root(account, 3) as Account, SUM(Convert(position, 'USD', date)) AS balance, currency as Total  Should I try bean-report?

(Great software, by the way.)

Thanks --

John

On Saturday, November 23, 2019 at 10:46:36 PM UTC-5, Martin Blais wrote:

How do you produce your reports?
bean-report?
or bean-query?

On Tue, Nov 19, 2019 at 11:44 AM John Rakestraw
<john.a....@gmail.com> wrote:
>
> Hi, all --
>
> New user here -- apologies if information I'm seeking is in the docs -- I've searched both the docs and this list and can't find the answer.
>
> I'm attempting to construct a report of monthly spending in a set of accounts, but I'd like the report to include an account in a particular month even if there is no spending in that account. I've found that an account is included (with the spending amount of zero) if there happened to be actual transactions that total to zero (when I spent $40 that was refunded in the same month). But if there's no activity, the account is not included in the report.
>
> Is this possible?
>
> Thanks --
>
> John
>
> --
> 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 bean...@googlegroups.com.

[Martin Blais]

On Sun, Nov 24, 2019 at 10:01 AM John Rakestraw
<john.a.r...@gmail.com> wrote:
>
> Hi, Martin --
>
> I'm using bean-query. Here's one example:
>
> bean-query /path/to/beancountfile "Select root(account, 3) as Account, SUM(Convert(position, 'USD', date)) AS balance, currency as Total FROM OPEN ON 2019-01-01 CLOSE ON 2019-11-01 WHERE account ~ 'account1|account2|account3|account4'"
>
> This gives me exactly what I want, unless the report is limited to a time period in which there are no transactions in one of the accounts. If there's no transaction involving account 2, for example, then the report lists only accounts 1, 3, and 4. I'd like all of them to be listed, with account 2 showing a position of zero.

Hmm.
Let me ask you a question: If you did not have a where clause to
filter for accounts 1, 2, 3 and 4, would you have liked to have seen a
0 for account 5 if there were no transaction for it?
I'm not sure I have a good fix for that.
The SQL interpreter is pretty generic; I'd have to somehow design a
special case to cover accounts with no activity.
I'm not sure how. I don't have a great answer right now.

> Is that possible? bean-query -f csv -q ~/beancountfiles/accounts/accounts.beancount "Select root(account, 3) as Account, SUM(Convert(position, 'USD', date)) AS balance, currency as Total Should I try bean-report?

Nah, I'd love to remove those codes eventually, I don't like them,
it's ugly code.
I want to move everything to the query language as much as possible.
I wouldn't rely on it. They'll likely disappear in the next major revision.

Your use case is a legitimate one that I need to think about a bit more.

> (Great software, by the way.)
>
> Thanks --
>
> John
>
> On Saturday, November 23, 2019 at 10:46:36 PM UTC-5, Martin Blais wrote:
>>
>> How do you produce your reports?
>> bean-report?
>> or bean-query?
>>
>> On Tue, Nov 19, 2019 at 11:44 AM John Rakestraw
>> <john.a....@gmail.com> wrote:
>> >
>> > Hi, all --
>> >
>> > New user here -- apologies if information I'm seeking is in the docs -- I've searched both the docs and this list and can't find the answer.
>> >
>> > I'm attempting to construct a report of monthly spending in a set of accounts, but I'd like the report to include an account in a particular month even if there is no spending in that account. I've found that an account is included (with the spending amount of zero) if there happened to be actual transactions that total to zero (when I spent $40 that was refunded in the same month). But if there's no activity, the account is not included in the report.
>> >
>> > Is this possible?
>> >
>> > Thanks --
>> >
>> > John
>> >
>> > --
>> > 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 bean...@googlegroups.com.
>> > To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/1962423c-41a8-40f4-a5f5-9854006440d1%40googlegroups.com.
>
> --
> 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/ce2a539c-b430-40dc-9f9a-ca704eea7fa4%40googlegroups.com.

[John Rakestraw]

Thanks Martin. My current use case involves a report from a limited set of accounts -- no need to see accounts with zero position if I'm running the report without the WHERE restriction. If nothing else, then perhaps I could export to csv and write macros in a spreadsheet to build the report.

--John

> To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/ce2a539c-b430-40dc-9f9a-ca704eea7fa4%40googlegroups.com.

[Martin Blais]

What I'm thinking is that something else could be done if the accounts
are open during the period to ensure they appear with a zero sum.
I'm not sure how to do that generically yet though (I'm trying to do
this very generically because the query tool will eventually move out
of the context of Beancount).

> 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/362d7e1a-abbd-4a26-89e4-3dd10d2581c7%40googlegroups.com.