How to display lot dates in aggregated query

[Philippe Dumonet]

How do I create a query such that I can get my lots, sorted by date while having the positions being aggregated? Specifically I'm playing around with a small example ledger file and the FIFO booking method. However reading through the documentation and trying to make my own queries I keep arriving at one of two results:

1. I am able to get an aggregated inventory by including `SELECT sum(position)` but need to settle with an aggregated date (first / last):

query> select first(date), account, sum(position) where currency != 'EUR' AND date <= #'26-05-2021' GROUP BY account, currency

result>

first_date    account         sum_position       
---------- ------------- -----------------------
2021-01-02 Assets:Crypto 0.483 ETH {1200.00 EUR}
                         5.00  ETH {1387.21 EUR}
2021-04-30 Assets:Crypto 3.47  XMR {  87.23 EUR}

2. Or I am able to get a result with the list of positions and their corresponding dates but they're not aggregated. Specifically what annoys me is that reducing inventory i.e. negative positions are shown independently and are not aggregated together, so I have a list of both positive and negative commodity entries instead of just the aggregated inventory:

query> select date, account, sum(position) where currency != 'EUR' AND date <= #'26-05-2021'

result>

   date       account          sum_position       
---------- ------------- ------------------------
2021-01-02 Assets:Crypto  0.5   ETH {1200.00 EUR}
2021-01-12 Assets:Crypto -0.017 ETH {1200.00 EUR}
2021-02-13 Assets:Crypto  5.00  ETH {1387.21 EUR}
2021-04-30 Assets:Crypto  3.47  XMR {  87.23 EUR}

Combining the aggregate query with a date filter is already quite useful for my usecase as I can e.g. see when the next position becomes 1 year old but I'd still like to have an aggregated inventory breakdown with individual dates. 

Any help would be greatly appreciated! I've already played around with python library so if this simply not possible with the query language I can also resort to writing a script but I'd prefer the most straightforward approach. Loving beancount so far!

[Daniele Nicolodi]

On 26/05/2022 20:26, Philippe Dumonet wrote:
> How do I create a query such that I can get my lots, sorted by date
> while having the positions being aggregated?

I am not sure I understand what you would like to obtain, but does a
query like

SELECT
sum(position),
cost_date
WHERE
account = 'Assets:Crypto'
GROUP BY
currency,
cost_date

get close to what you would like to obtain?

Having an example ledger and and the output you would like to obtain
would help to better understand.

Cheers,
Dan

[Daniele Nicolodi]

On 26/05/2022 20:26, Philippe Dumonet wrote:

> query> select first(date), account, sum(position) where currency !=
> 'EUR' AND date <= #'26-05-2021' GROUP BY account, currency

By the way, I advise against using the #'26-05-2021' format to specify
dates, especially if you use a day-month-year date format: it does not
do what you expect if the day number is less than 13 as it defaults to
the month-day-year date format.

The #'...' date literals are so confusing that I'll most probably remove
support for them from beanquery in the near future.

bean-query understands date literals in the YYYY-MM-DD format, in this
example, you can simply write:

SELECT first(date), account, sum(position)
WHERE currency != 'EUR' AND date <= 2021-05-26
GROUP BY account, currency

Cheers,
Dan

[Philippe Dumonet]

That is exactly what I was looking for! I did not know about cost_date. Also thanks for the tip on #'dd-mm-yyyy' I'll keep that in mind.

Looking back at the docs and guide I don't think cost_date is really documented there, besides digging through the code is there a reference I can refer to in the future?

Thanks so much!

[Daniele Nicolodi]

On 26/05/2022 23:07, Philippe Dumonet wrote:
> That is exactly what I was looking for! I did not know about cost_date.
> Also thanks for the tip on #'dd-mm-yyyy' I'll keep that in mind.
>
> Looking back at the docs and guide I don't think cost_date is really
> documented there, besides digging through the code is there a reference
> I can refer to in the future?

The documentation does not cover everything, yet. In the bean-query
shell, the 'help targets' command lists all columns in the BQL data
model and all defined functions.

The 'help' command lists all the available help "topics".

Cheers,
Dan

[Philippe Dumonet]

Hey, another thing: how do I access inventories in the filter (WHERE) part of my queries? I've looked at the help provided from within the interactive query command line and there seems to be a bunch of simple functions that take an inventory as an input but I can't seem to find the relevant column. It keeps telling me "Invalid column name 'inventory' in WHERE clause context" and I can't access my own columns or use sum within the filter part.

You can find the example ledger file I'm experimenting with here. The query I'm running is "SELECT account, cost_date as date, sum(position) as position, cost(sum(position)) as cost, cost_number as num WHERE currency != 'EUR' GROUP BY account, currency, cost_date, cost_number". I'm trying to filter out rows which have no inventory.

Thanks again!

[Daniele Nicolodi]

On 27/05/2022 13:11, Philippe Dumonet wrote:
> Hey, another thing: how do I access inventories in the filter (WHERE)
> part of my queries? I've looked at the help provided from within the
> interactive query command line and there seems to be a bunch of simple
> functions that take an inventory as an input but I can't seem to find
> the relevant column. It keeps telling me "Invalid column name
> 'inventory' in WHERE clause context" and I can't access my own columns
> or use sum within the filter part.

Aggregates cannot be used in the WHERE clause as the WHERE clause is
executed to filter the rows before computing the aggregates. To filter
on aggregates you would need to specify an HAVING clause, but it is not
implemented in bean-query as included in Beancount v2.

Working toward Beancount v3, some parts of Beancount have moved to
separate projects. This is the case of bean-query, which has moved to
its own repository https://github.com/beancount/beanquery/

beanquery implements the HAVING clause and the empty() BQL function that
checks whether an inventory is empty, see
https://github.com/beancount/beanquery/issues/36

beanquery can be installed with Beancount v2 and will replace the
bean-query command with the newer version. A release has not been tagget
yet, thus you would need to install from git. beanquery is evolving
rapidly (at least in comparison to the time I have to dedicate to it)
and the test coverage is not perfect yet, thus some bugs may sneak in.
Please give it a try and report any issue you find.

> You can find the example ledger file I'm experimenting with here

> <https://pastebin.com/LXYZ1Z05>. The query I'm running is "SELECT

> account, cost_date as date, sum(position) as position,
> cost(sum(position)) as cost, cost_number as num WHERE currency != 'EUR'
> GROUP BY account, currency, cost_date, cost_number". I'm trying to
> filter out rows which have no inventory.

I would write this like this:

SELECT
cost_date AS date,
sum(position) AS position,
cost(sum(position)) AS cost

WHERE
account = 'Assets:Crypto'
GROUP BY
currency,
cost_date

HAVING
not empty(sum(position))
ORDER BY
date


Cheers,
Dan

[Philippe Dumonet]

Thanks!