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
<https://beancount.github.io/docs/beancount_query_language.html#making-queries>
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!
--
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/2ae72dae-49ba-415c-ae8b-fd0f2c5554adn%40googlegroups.com.
