How to list all lots with their buying and selling history

[Peter]

Hey there,

Is there a way to list a history of all lots for a given commodity? I am using FiFo and I would like to create some sort of spreadsheet that lists all lots with their buy date and sell date (if there is one).

So, these transactions

2023-06-01 * "Buy shares"
  Assets:Investments:Stocks  50 AAPL {300.00 USD}
  Assets:Bank:Checking

2023-07-01 * "Buy shares"
  Assets:Investments:Stocks  50 AAPL {400.00 USD}
  Assets:Bank:Checking

2023-08-01 * "Buy shares"
  Assets:Investments:Stocks  50 AAPL {500.00 USD}
  Assets:Bank:Checking

2023-12-01 * "Sell shares"
    Assets:Investments:Stocks -75 AAPL {500.00 USD}
    Assets:Bank:Checking 37,500 USD
    Income:Capital:Gains

2024-01-01 * "Sell shares"
    Assets:Investments:Stocks -25 AAPL {500.00 USD}
    Assets:Bank:Checking 12,500 USD
    Income:Capital:Gains

Should become something like this:

+-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+ | Commodity | Bought | Qty | Price | Sold | Qty | Price | PnL | Duration (days) | Held more than 145 days? | +-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+ | AAPL | 2023-06-01 | 50 | 300 | 2023-12-01 | 50 | 500 | 10,000 | 150 | Y | +-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+ | AAPL | 2023-07-01 | 50 | 400 | 2023-12-01 | 25 | 500 | 5000 | 120 | N | +-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+ | | | | | 2024-01-01 | 25 | 500 | 5000 | 150 | Y | +-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+ | AAPL | 2023-08-01 | 50 | 500 | | | | | | | +-----------+------------+-----+-------+------------+-----+-------+--------+-----------------+--------------------------+
Is this possible? If so, how?

Regards

[Chary Chary]

It looks like you will need to group (or to link) postings by lot, but I also couldn't find how to extract lot information in beanquery

[Eric Altendorf]

Not sure about beanquery, but I did lot tracking and reporting in 

https://github.com/ericaltendorf/magicbeans if the code there is useful for ideas.  

--
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 on the web visit https://groups.google.com/d/msgid/beancount/64a9defc-421b-4cd1-b48f-4bd3ec1ec3d9n%40googlegroups.com.

[Martin Blais]

In order to generate this you need to produce this as a side-effect of the booking process (which involves matching the incomplete posting spec to the inventory contents).

I think I've wanted to make this part of the core - an important part I should say - but at the time I switched to building Johnny (https://github.com/edgebips/johnny) for more sophisticated trading (options with adjustments to multiple legs) and never built it in. I documented this for the rewrite, as I think it's important: https://docs.google.com/document/d/1qPdNXaz5zuDQ8M9uoZFyyFis7hA0G55BEfhWhrVBsfc/edit#heading=h.1on91pqrb473

So right now, unfortunately no.

--

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 on the web visit https://groups.google.com/d/msgid/beancount/2b279e0f-255e-4783-951c-6b53042fcd5dn%40googlegroups.com.

[Martin Blais]

BTW I think the best way to implement this is by modifying the booking process to insert metadata IDs on its output, so that one could call a function to easily extract all the trades from the same set of directives after they've been booked and interpolated, by just matching these IDs.

[fin]

i had to write a similar program when i was importing a lot
of transactions, but it effectively worked in the opposite
direction of what you require so it isn't very useful (i was
scanning a list of my own making to turn it into beancount
transactions). i wrote it in Python. it takes csv format
input.

to adapt it to your purposes you'd need a way to dump the
transactions to a csv format, but from those you could run my
code and that would give you a python list that you could
then list the lots in whatever format you'd like.

alas, i'm short on time right now until Monday or Tuesday
but if you want to explore this send me an e-mail and we
can go from there.


fin

[Chary Chary]

This seems to be the most logical thing, as beancount internally already must be using this information.

Anyone who attempts to write a plugin to implement this will have to simulate beancount logic inside the plugin, and then somehow verify that plugin's logic was equivalent to the one of beancount

[Martin Blais]

Yeah, that's why it wasn't done.

I'll see if I can just insert the metadata for it, should be pretty easy.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/fd36d738-0d4a-4051-aad3-bf78de5a9a10n%40googlegroups.com.

[Martin Blais]

It looks like I had a quick go at this in 2020 and concluded I'd never more than an afternoon:

https://github.com/beancount/beancount/tree/trade_quick_experiment