On Sun, Nov 24, 2019 at 10:01 AM John Rakestraw <[email protected]> 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 >> <[email protected]> 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 [email protected]. >> > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/ce2a539c-b430-40dc-9f9a-ca704eea7fa4%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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhOC1796skx9KqGhsz85yHOUpWJbPETms8cuoLW%3DUajm8g%40mail.gmail.com.
