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).
On Mon, Nov 25, 2019 at 5:01 PM John Rakestraw <john.a.rakest...@gmail.com> wrote: > > 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 > > On Sunday, November 24, 2019 at 9:14:38 PM UTC-5, Martin Blais wrote: >> >> On Sun, Nov 24, 2019 at 10:01 AM John Rakestraw >> <john.a....@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 bean...@googlegroups.com. >> > 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 beancount+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/362d7e1a-abbd-4a26-89e4-3dd10d2581c7%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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhPDu6TEzwNQzTD9PrBefCNmxc0Q-%3DF%3DrQ_R8SEWspjqbA%40mail.gmail.com.
