How to get last day of month in BQL

[Andreas Gerstmayr]

Hi!

I'm trying to get a report of the market value of my assets at the end
of each month:

SELECT year, month,
CONVERT(LAST(balance), 'USD', DATE_ADD(DATE(year, month+1,
1), -1)) AS market_value,
CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(year, month+1,
1), -1)) AS book_value
WHERE account ~ '^Assets:'
GROUP BY year, month

This fails with

Syntax error near '1'

Replacing "month+1" with "month" (as an experiment, it won't produce
correct results), I'm getting

ERROR: Mixed aggregates and non-aggregates are not allowed.

I think technically this should work, as year and month are in the GROUP
BY clause.


I came up with the following sketchy workaround:

SELECT year, month,
CONVERT(LAST(balance), 'USD',
DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS market_value,
CONVERT(COST(LAST(balance)), 'USD',
DATE_ADD(YMONTH(DATE_ADD(YMONTH(FIRST(date)), 31)), -1)) AS book_value
WHERE account ~ '^Assets:'
GROUP BY year, month

It uses YMONTH() to get the 1st of the given month and year, adds 31
days to get the next month, runs YMONTH() again to get the 1st of the
next month, and subtracts one day.
It won't work in all cases however, for example if the first transaction
of January is on January 31, because adding 31 days "skips over" February.

Is there a way to get the last day of a month with the current
beancount.query or the new beanquery module, and if not, can I add one? :)
MySQL for example has a LAST_DAY() function.


Cheers,
Andreas

[Andreas Gerstmayr]

> Hi!
>
> I'm trying to get a report of the market value of my assets at the end
> of each month:
>
>     SELECT year, month,
>     CONVERT(LAST(balance),       'USD', DATE_ADD(DATE(year, month+1,
> 1), -1)) AS market_value,
>     CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(year, month+1,
> 1), -1)) AS book_value
>     WHERE account ~ '^Assets:'
>     GROUP BY year, month

It does work with the new beanquery when using FIRST():

SELECT year, month,
CONVERT(LAST(balance), 'USD', DATE_ADD(DATE(FIRST(year),
FIRST(month)+1, 1), -1)) AS market_value,
CONVERT(COST(LAST(balance)), 'USD', DATE_ADD(DATE(FIRST(year),
FIRST(month)+1, 1), -1)) AS book_value

WHERE account ~ '^Assets:'
GROUP BY year, month

However there's an issue in December, as month+1 doesn't work if month
is December :|

I think a new BQL function is required to support this use case (happy
to provide a PR).

[francocalvo]

I know this won't solve your issue. What I've done in the past was to read the data to a Pandas DataFrame, and aggregate and query it using DuckDB

[Chary Chary]

Looks like you need to ask for a new function to be added to the beanquery, something like MONTH_END_DATE(year, month). In addition also QUATER_END_DATE  (year, month) would be useful.

Looking at the code of the similar functions, that shall be quite easy.

I personally solve this in the following way:

1) Generate the dates I am interested in in python (e.g. every week/month/quarter/year end)

2) Generate the market value for these dates using the following query:

    query = f"""

    SELECT account, convert(SUM(position),'{currency}',{date_iso}) as amount

    where date <= {date_iso} AND account ~ 'Assets|Liabilities'

    """

3) But I use beancount from jupyter notebook, not from command line

[Daniele Nicolodi]

On 29/05/24 04:10, 'francocalvo' via Beancount wrote:
> I know this won't solve your issue. What I've done in the past was to
> read the data to a Pandas DataFrame, and aggregate and query it using DuckDB

How do you make Pandas and DuckDB work with fixed decimals and with
amounts in different currencies?

Cheers,
Dan

[Daniele Nicolodi]

On 29/05/24 10:59, Chary Chary wrote:
> Looks like you need to ask for a new function to be added to the
> beanquery, something like MONTH_END_DATE(year, month). In addition also
> QUATER_END_DATE  (year, month) would be useful.

Andreas contributed support for date_truc() and interval() and I merged
it into beanquery last week:

https://github.com/beancount/beanquery/pull/169

Cheers,
Dan

[Chary Chary]

Dan, 

this is great, thanks!