Exclude empty amounts with GROUP BY

[Martin Michlmayr]

I want to see a list of outstanding invoices.  Given the example
below, I can use:

beancount> SELECT ENTRY_META('invoice'), SUM(position) WHERE account ~ 
'^Assets:Receivable' GROUP BY ENTRY_META('invoice')
ent sum_posit
--- -
A01  
B01 30.00 USD

Is there a way to exclude positions that are 0, i.e. that have been
paid?

Example:

2020-01-01 open Assets:Bank
2020-01-01 open Assets:Receivable
2020-01-01 open Income:Sponsorship

2020-03-01 * "Sponsorship from A"
invoice: "A01"
Assets:Receivable  100.00 USD
Income:Sponsorship-100.00 USD

2020-03-01 * "Sponsorship from B"
invoice: "B01"
Assets:Receivable   30.00 USD
Income:Sponsorship -30.00 USD

2020-03-10 * "Payment from A"
invoice: "A01"
Assets:Bank100.00 USD
Assets:Receivable -100.00 USD

-- 
Martin Michlmayr
https://www.cyrius.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/20200316074928.GV25911%40jirafa.cyrius.com.

GROUP by financial year

[Martin Michlmayr]

I can use:

SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER BY YEAR

to see all income per year.

Unfortunately, the financial year is not the calendar year.  Is there
a way to use bean-query to do a GROUP BY on a fiancial year or do I
have to write a script?

-- 
Martin Michlmayr
https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.com.

Re: GROUP by financial year

[Martin Blais]

I don't think there's a concert of financial year that's != calendar year.

On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:

> I can use:
>
> SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER
> BY YEAR
>
> to see all income per year.
>
> Unfortunately, the financial year is not the calendar year.  Is there
> a way to use bean-query to do a GROUP BY on a fiancial year or do I
> have to write a script?
>
> --
> Martin Michlmayr
> https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.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%2BhNDs0kbdkH-%3DJi%3Df7Lqmd9Ecw3hKbEu_7ab60%2BtL%2BWXuQ%40mail.gmail.com.

Re: Exclude empty amounts with GROUP BY

[Martin Blais]

This would need a filtering expression of the aggregate, it's not
implemented. SQL defines a syntax thought.

On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:

> I want to see a list of outstanding invoices.  Given the example
> below, I can use:
>
> beancount> SELECT ENTRY_META('invoice'), SUM(position) WHERE account ~
> '^Assets:Receivable' GROUP BY ENTRY_META('invoice')
> ent sum_posit
> --- -
> A01
> B01 30.00 USD
>
> Is there a way to exclude positions that are 0, i.e. that have been
> paid?
>
> Example:
>
> 2020-01-01 open Assets:Bank
> 2020-01-01 open Assets:Receivable
> 2020-01-01 open Income:Sponsorship
>
> 2020-03-01 * "Sponsorship from A"
> invoice: "A01"
> Assets:Receivable  100.00 USD
> Income:Sponsorship-100.00 USD
>
> 2020-03-01 * "Sponsorship from B"
> invoice: "B01"
> Assets:Receivable   30.00 USD
> Income:Sponsorship -30.00 USD
>
> 2020-03-10 * "Payment from A"
> invoice: "A01"
> Assets:Bank100.00 USD
> Assets:Receivable -100.00 USD
>
> --
> Martin Michlmayr
> https://www.cyrius.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/20200316074928.GV25911%40jirafa.cyrius.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%2BhOAvTrTwxsXC2TZNLjOp3pwZpmkSUO_XC7CcrSZQONfYA%40mail.gmail.com.

Re: GROUP by financial year

[Martin Michlmayr]

s/concert/concept/?

* Martin Blais  [2020-03-16 08:07]:
> I don't think there's a concert of financial year that's != calendar year.
> 
> On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:
> 
> > I can use:
> >
> > SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER
> > BY YEAR
> >
> > to see all income per year.
> >
> > Unfortunately, the financial year is not the calendar year.  Is there
> > a way to use bean-query to do a GROUP BY on a fiancial year or do I
> > have to write a script?
> >
> > --
> > Martin Michlmayr
> > https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.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%2BhNDs0kbdkH-%3DJi%3Df7Lqmd9Ecw3hKbEu_7ab60%2BtL%2BWXuQ%40mail.gmail.com.

-- 
Martin Michlmayr
https://www.cyrius.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/20200316121736.GE25911%40jirafa.cyrius.com.

Re: GROUP by financial year

[Martin Blais]

Yep
Cell phone finger fart

On Mon, Mar 16, 2020, 08:17 Martin Michlmayr  wrote:

> s/concert/concept/?
>
> * Martin Blais  [2020-03-16 08:07]:
> > I don't think there's a concert of financial year that's != calendar
> year.
> >
> > On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:
> >
> > > I can use:
> > >
> > > SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR
> ORDER
> > > BY YEAR
> > >
> > > to see all income per year.
> > >
> > > Unfortunately, the financial year is not the calendar year.  Is there
> > > a way to use bean-query to do a GROUP BY on a fiancial year or do I
> > > have to write a script?
> > >
> > > --
> > > Martin Michlmayr
> > > https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.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%2BhNDs0kbdkH-%3DJi%3Df7Lqmd9Ecw3hKbEu_7ab60%2BtL%2BWXuQ%40mail.gmail.com
> .
>
> --
> Martin Michlmayr
> https://www.cyrius.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/20200316121736.GE25911%40jirafa.cyrius.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%2BhNuppmZOXqiL8j%2BH7ogSReqQ2ijbcvjrfYXUH%3DHEWUhBw%40mail.gmail.com.

Re: GROUP by financial year

[Oon-Ee Ng]

Fairly common for some organizations to have financial years starting on
(say) 1st August or something similar. Sometimes to sync up with regulatory
requirements, sometimes so that bonuses/promotions hit a particular period
of the year, sometimes due to expected peak dates for a particular industry.

On Mon, Mar 16, 2020 at 8:08 PM Martin Blais  wrote:

> I don't think there's a concert of financial year that's != calendar year.
>
> On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:
>
>> I can use:
>>
>> SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER
>> BY YEAR
>>
>> to see all income per year.
>>
>> Unfortunately, the financial year is not the calendar year.  Is there
>> a way to use bean-query to do a GROUP BY on a fiancial year or do I
>> have to write a script?
>>
>> --
>> Martin Michlmayr
>> https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.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%2BhNDs0kbdkH-%3DJi%3Df7Lqmd9Ecw3hKbEu_7ab60%2BtL%2BWXuQ%40mail.gmail.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/CAGQ70euz0fJfv3rT%2BBZWCr5G89bjLsEG5MfNp2KgPUdSzc10HQ%40mail.gmail.com.

Re: GROUP by financial year

[Martin Michlmayr]

I wasn't sure how to interpret Martin's reply.  At first, I thought he
was saying that he's not aware of circumstances where the FY !=
calendar year.  But then I realized that he he was just stating that
beancount doesn't have a concept of FY.

As you say (and I'm sure as Martin is full aware), the FY often
differs.  The tax year is also different in some countries, e.g. in
the UK it starts on April 6.

What would the best way be to implement FY in beancount?

* Oon-Ee Ng  [2020-03-17 14:23]:
> Fairly common for some organizations to have financial years starting on
> (say) 1st August or something similar. Sometimes to sync up with regulatory
> requirements, sometimes so that bonuses/promotions hit a particular period
> of the year, sometimes due to expected peak dates for a particular industry.
> 
> On Mon, Mar 16, 2020 at 8:08 PM Martin Blais  wrote:
> 
> > I don't think there's a concert of financial year that's != calendar year.
> >
> > On Mon, Mar 16, 2020, 04:19 Martin Michlmayr  wrote:
> >
> >> I can use:
> >>
> >> SELECT YEAR, SUM(position) WHERE account ~ '^Income' GROUP BY YEAR ORDER
> >> BY YEAR
> >>
> >> to see all income per year.
> >>
> >> Unfortunately, the financial year is not the calendar year.  Is there
> >> a way to use bean-query to do a GROUP BY on a fiancial year or do I
> >> have to write a script?
> >>
> >> --
> >> Martin Michlmayr
> >> https://www.cyrius.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/20200316075211.GW25911%40jirafa.cyrius.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%2BhNDs0kbdkH-%3DJi%3Df7Lqmd9Ecw3hKbEu_7ab60%2BtL%2BWXuQ%40mail.gmail.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/CAGQ70euz0fJfv3rT%2BBZWCr5G89bjLsEG5MfNp2KgPUdSzc10HQ%40mail.gmail.com.

-- 
Martin Michlmayr
https://www.cyrius.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/20200317062618.GA5242%40jirafa.cyrius.com.

Re: New Fava extension: Tax loss harvester

[Red S]

Updates to the TLH extension in these interesting times we live in:

- the command line client has now been folded in to the fava client and 
uses the same code (refactored out to support both fava and bean-query)
- a --brief option to the command line client displays the summary only
- unit tests now included

On Friday, March 13, 2020 at 1:16:46 AM UTC-7, Red S wrote:
>
> Summary on top grouped by commodity (thanks Justus Pendleton for the idea).
>
> Also, washes have been merged into a single table.
>
>

-- 
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/e23aa163-3a1d-45e3-917e-1786fff2e182%40googlegroups.com.