Displaying the cost date in beanquery, when displaying inventory

[Chary Ev2geny]

Dear all,

I am just wondering whether it is possible to configure beanquery so, that it would display a date of the position cost, when displaying inventory.

Example:

suppose, this is the ledger:

2023-01-01 open Assets:Bank

2023-01-01 open Income:Salary

2023-01-01 open Assets:Investment

2023-01-01 * "Salary"

  Income:Salary   -100000 USD

  Assets:Bank      100000 USD

2023-01-11 * "Investment 1"

  Assets:Investment   1  IVV {10 USD}

  Assets:Bank        -10 USD

2023-01-12 * "Investment 2"

  Assets:Investment   10 IVV {20 USD}

  Assets:Bank        -200 USD

2023-01-13 * "Investment 3. The same cost as Investment 2, but diff date"

  Assets:Investment   100 IVV {20 USD}

  Assets:Bank        -2000 USD

2023-01-14 * "Investment 4. Not at cost"

  Assets:Investment   1000  IVV @ 40 USD

  Assets:Bank        -40000 USD

If I run the following query

SELECT sum(position)
WHERE account = "Assets:Investment"

Then the result is as follows:

                              sum(position)                              
--------------------------------------------------------------------------
1000 IVV            100 IVV {20 USD}    10 IVV {20 USD}     1 IVV {10 USD}

This is a bit confusing, as this is not very clear, what is the difference between  100 IVV {20 USD}  and  10 IVV {20 USD}     This is because  the result hides the cost date

So, the question is whether it possible to configure it somehow to be like this:

                              sum(position)                              
-----------------------------------------------------------------------------------------
1000 IVV            100 IVV {20 USD, 2023-01-13}    10 IVV {20 USD, 2023-01-12}     1 IVV {10 USD, 2023-01-11}

[Chary Ev2geny]

As a follow up to my above question.

I myself do not use the  Inventory and at cost tracking features of beancount, as the financial legislation of the country I am based in does not  seem to require this. But I find, that the ability to debug and dive in the inventory status ( e.g. an inventory state of a specific account at a specific date) is a bit limited (usage of the SUM function as mentioned above the only thing I could find, and it is limited as well).

The only access to an inventory status one seems to get when one gets an "No position matches" or "Not enough lots to reduce" error

E.g.

No position matches "Posting(account='Assets:Investments', units=-10 IVV, cost=CostSpec(number_per=Decimal('10.00'), number_total=None, currency='USD', date=datetime.date(2024, 1, 2), label=None, merge=False), price=None, flag=None, meta={'filename': 'C:\\_code\\pytest\\beantst\\tst.bean', 'lineno': 19})" against balance (10 IVV {10.00 USD, 2024-01-01}, 10 IVV {15.00 USD, 2024-01-02})

   2024-01-03 * "Selling Invest 1"
     Assets:Investments     -10 IVV {10.00 USD, 2024-01-02}
     Assets:Bank         120.00 USD
     Income:Investments

Yet beancount internally must be calculating these, otherwise it would no be able to generate the above error

Would be nice to have some kind of beanquery command like:

PRINT account, inventory WHERE account = <account-name> AND date = <specific-date>

I wonder how those, who heavily rely on the inventory functionality debug it?  

Or am I missing something?

[Justus Pendleton]

I don't think there is anyway to configure bean-query to display things like you suggest. I think in practice people use something like the holdings listing in Fava which AFAIK just uses a query like this:

SELECT account,
    units(sum(position)) as units,
    cost_number as cost,
    first(getprice(currency, cost_currency)) as price,
    cost(sum(position)) as book_value,
    value(sum(position)) as market_value,
    cost_date as acquisition_date
  WHERE account_sortkey(account) ~ "^[01]"
  GROUP BY account, cost_date, currency, cost_currency, cost_number, account_sortkey(account)
  ORDER BY account_sortkey(account), currency, cost_date

You are right that debugging inventory issues is more painful than it feels like it should be in beancount. I haven't used it in a while but occasionally bean-doctor (on the offending line) helps. In practice, I usually just visually/manually look at the holdings list and try to figure out what the problem is. I guess I put up with it because, at least for me, I'm not selling inventories very often so the problem only comes up once a year or so.

I'm not sure what people with tons of transactions (crypto, daytraders) do to manage it. Perhaps they are downloading everything automatically from brokers and, in practice, for them, they never run into inventory issues to debug?

[Martin Blais]

Are you using bean-doctor?

--
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+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/beancount/cb74f04d-5051-4b0e-ac8a-2e7f29944bfcn%40googlegroups.com.

[Chary Ev2geny]

On Wednesday, April 15, 2026 at 4:06:32 AM UTC+2 bl...@furius.ca wrote:

Are you using bean-doctor?

No, I didn't!!! What a shame after so many years! Thank you for pointing out!

Now, I will be.

Note: just for the record for the above case the bean-doctor produces the following output!

PS C:\_code\bean> bean-doctor context test.bean 21
** Transaction Id --------------------------------

Hash:9847d4c5060cbee6bbddb5a3c4bda180
Location: C:\_code\bean\test.bean:21


** Balances before transaction --------------------------------

  Assets:Investment                         1 IVV {10 USD, 2023-01-11}
  Assets:Investment                        10 IVV {20 USD, 2023-01-12}
  Assets:Investment                       100 IVV {20 USD, 2023-01-13}

  Assets:Bank                                                97790 USD


** Average Costs --------------------------------

  Assets:Investment  111 IVV {19.90990990990990990990990991 USD, 2023-01-11}


** Unbooked Transaction --------------------------------

2023-01-14 * "Investment 4. Not at cost"

  Assets:Investment    1000 IVV @ 40 USD  ;  40000 USD
  Assets:Bank        -40000 USD           ; -40000 USD


** Transaction --------------------------------

2023-01-14 * "Investment 4. Not at cost"

  Assets:Investment    1000 IVV @ 40 USD  ;  40000 USD
  Assets:Bank        -40000 USD           ; -40000 USD


** Residual and Tolerances --------------------------------



** Balances after transaction --------------------------------

  Assets:Investment                         1 IVV {10 USD, 2023-01-11}
  Assets:Investment                        10 IVV {20 USD, 2023-01-12}
  Assets:Investment                       100 IVV {20 USD, 2023-01-13}
* Assets:Investment                                           1000 IVV

* Assets:Bank                                                57790 USD

PS C:\_code\bean> 

 

[Simon Guest]

Sorry for the late reply.  I check in here from time to time to validate what I am doing with limabean against what people need.  I am pursuing a new user experience, which may help here.

With your cost/date example:

> limabean --beanfile examples/beancount/cost-date.beancount

[Rebel readline] Type :repl/help for online help info

[limabean] 8 directives loaded from examples/beancount/cost-date.beancount

[limabean] 8 directives resulting from booking and running plugins

user=> (show (inventory))

Assets:Bank          57790 USD

Assets:Investment     1000 IVV

                         1 IVV  2023-01-11 USD 10

                        10 IVV  2023-01-12 USD 20

                       100 IVV  2023-01-13 USD 20

Income:Salary      -100000 USD

:ok

Also, since the data structures in limabean are simply Clojure maps and vectors, you can drill into it as much as you like.  Here's the underlying data:

user=> (pprint (inventory))

{"Assets:Bank" [{:units 57790, :cur "USD", :cell/type :position}],

 "Assets:Investment"

 [{:units 1000, :cur "IVV", :cell/type :position}

  {:units 1,

   :cur "IVV",

   :cost

   {:date #time/date "2023-01-11",

    :per-unit 10,

    :total 10,

    :cur "USD"},

   :cell/type :position}

  {:units 10,

   :cur "IVV",

   :cost

   {:date #time/date "2023-01-12",

    :per-unit 20,

    :total 200,

    :cur "USD"},

   :cell/type :position}

  {:units 100,

   :cur "IVV",

   :cost

   {:date #time/date "2023-01-13",

    :per-unit 20,

    :total 2000,

    :cur "USD"},

   :cell/type :position}],

 "Income:Salary" [{:units -100000, :cur "USD", :cell/type :position}]}

Hopefully at some stage this will start to become interesting for others besides me!

cheers,

Simon

--

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+...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/beancount/a5956ad8-4f9a-4dee-8ac7-8a0f88609039n%40googlegroups.com.