Thanks for that awesome writeup Martin!
On Sun, 27 Sep 2015 at 18:24 Martin Blais <[email protected]> wrote:
> Hi Michael,
>
> I don't think you're going in the right direction to achieve your goal of
> calculating your capital gains on Bitcoins using the FIFO method. Having
> transactions available from Ledger is not what you're after, because
> finding the
> postings corresponding to the postings not in Bitcoins would only help you
> back
> out the price of the associated conversions, which you need, but it is not
> enough. It would also force you to make assumptions about the other legs
> in your
> transactions.
>
> Rather, what you're looking for is a way to connect two postings together
> when a
> posting reduces a position, i.e., in your case, when you sell or use some
> Bitcoins. More precisely, in order to calculate your gains, you need to
> connect
> the specific posting that reduces bitcoins to the particular posting that
> augmented your inventory of Bitcoins, for just the amount being reduced.
>
> (Quick note: I use these terms "augment" and "reduce" to qualify what
> happens to
> your inventory of Bitcoins. I could use "buy" and "sell" instead, but that
> would
> not generalize to short positions, and the term "reduce" emphasizes well
> that a
> matching lot must be associated with it.)
>
> Even then, it's a bit more complicated than just matching the lots:
> Because of
> differing quantities, one particular sale of bitcoins may match multiple
> purchases of bitcoins, e.g. (using Beancount syntax),
>
> ;; (1)
>
> 2015-09-04 *
> Assets:Bank -500.00 USD
> Assets:Bitcoin 2.000000 BTC @ 250.00 USD ;; A
>
> 2015-09-05 *
> Assets:Bank -520.00 USD
> Assets:Bitcoin 2.000000 BTC @ 260.00 USD ;; B
>
> 2015-09-20 *
> Assets:Bitcoin -3.000000 BTC @ 280.00 USD ;; D
> Expenses:Something
>
> In this example, the sale at D matches against inventory bought at A and
> partially B (there remains some B in the end).
>
> Conversely, multiple sales may match a single lot purchased previously:
>
> ;; (2)
>
> 2015-09-04 *
> Assets:Bank -1000.00 USD
> Assets:Bitcoin 4.347826 BTC @ 230.00 USD ;; A
>
> 2015-09-20 *
> Assets:Bitcoin -2.000000 BTC @ 231.00 USD ;; B
> Expenses:Something
>
> 2015-09-21 *
> Assets:Bitcoin -2.000000 BTC @ 232.00 USD ;; C
> Expenses:Something
>
> In that example, the purchase at A is reduced by B and C (and there
> remains some
> of A in the end).
>
> One way to express this well is to split the postings where necessary, to
> account for just the quantity being reduced. For instance, the first
> example
> would be booked like this in Beancount, inserting the cost bases:
>
> ;; (3)
>
> 2015-09-04 *
> Assets:Bitcoin 2.000000 BTC {250.00 USD}
> Assets:Bank -500.00 USD
>
> 2015-09-05 *
> Assets:Bitcoin 2.000000 BTC {260.00 USD}
> Assets:Bank -520.00 USD
>
> 2015-09-20 *
> Assets:Bitcoin -2.000000 BTC {250.00 USD} @ 280.00 USD
> Assets:Bitcoin -1.000000 BTC {260.00 USD} @ 280.00 USD
> Income:Bitcoin -80.00000000 USD
> Expenses:Something 840.00000000 USD
>
> Note here how the reducing -3.000000 BTC posting has been split into two
> different postings in the reducing transaction in order to associate the
> correct
> cost basis for those units.
>
> The second example should look like this:
>
> ;; (4)
>
> 2015-09-04 *
> Assets:Bitcoin 4.347826 BTC {230.00 USD}
> Assets:Bank -1000.00 USD
>
> 2015-09-20 *
> Assets:Bitcoin -2.000000 BTC {230.00 USD} @ 231.00 USD
> Income:Bitcoin -2.00000000 USD
> Expenses:Something 462.00000000 USD
>
> 2015-09-21 *
> Assets:Bitcoin -2.000000 BTC {230.00 USD} @ 232.00 USD
> Income:Bitcoin -4.00000000 USD
> Expenses:Something 464.00000000 USD
>
> Here, what's special is that the two reducing postings are using the same
> cost
> basis, because they match against the same lot.
>
> Okay, so given this, I mentioned that Beancount will eventually support
> this
> natively. Just for the sake of harvesting the context I've established
> here,
> here's how the first example will be input when that's done:
>
> ;; (5)
>
> 2010-01-01 open Assets:Bitcoin "FIFO"
>
> ...
>
> 2015-09-04 *
> Assets:Bitcoin 2.000000 BTC {250.00 USD}
> Assets:Bank -500.00 USD
>
> 2015-09-05 *
> Assets:Bitcoin 2.000000 BTC {260.00 USD}
> Assets:Bank -520.00 USD
>
> 2015-09-20 *
> Assets:Bitcoin -3.000000 BTC {}
> Income:Bitcoin
> Expenses:Something 840.00000000 USD
>
> That would translate into the same output as code block (3). Just to be
> clear,
> that's not supported yet, I'm still working out the details, though I'm
> already
> done through the hardest part of the job (isolating the booking code while
> keeping everything else working).
>
> The reasons behind this syntax are:
>
> - Distinguishing between cost basis and price conversions is sensible. One
> normally does not need to track the cost basis of currencies. I've made
> that
> point in other emails on this list. Your specific example is an
> exception,
> though a useful and legitimate one IMO (and I offer a solution below,
> please
> read on).
>
> - A user should be _able_ to tell the software explicitly which of the
> specific
> lots are to be matched against. A deterministic, fixed method like using
> FIFO
> is not always possible. A prime example is that in the USA, a taxpayer
> has the
> option to decide to choose which specific lots are to be sold, and AFAIK
> (s)he
> can use this to minimize capital gains impact for taxes. My point is, it
> has
> to support this model.
>
> Now, when I mentioned earlier in this thread that it would be possible to
> write
> a Beancount plugin to automatically book the trades what I meant is this:
> From
> input such as code blocks (1) or (3) above, that is, transactions treating
> Bitcoins as simple price conversions, it should be possible to transform
> the
> transactions into (2) and (4) respectively, entirely automatically, and
> without
> changing the Beancount code itself.
>
> After thinking about the suggestion I made during my vacation, I realized
> that
> this could be a very useful thing to perform in general, so I implemented
> that
> plugin in Beancount. It's a great example of the power of the plugin
> system and
> it also solves your problem entirely.
>
>
> You can find it under "beancount.plugins.book_conversions". It does
> exactly what
> I describe above. You use it like this: first you load the plugin:
>
> plugin "beancount.plugins.book_conversions"
> "Assets:Bitcoin,Income:Bitcoin"
>
> The configuration requires you provide two accounts:
>
> 1. An assets account to process, whereby all price conversions (in any
> currency)
> occurring in that account will have their cost basis automatically
> inserted,
> and,
>
> 2. An Income account that is inserted on reducing transactions to
> absorb the
> profit or loss of the sale.
>
> Then you book your transactions as simple price conversions, without
> worrying
> about cost basis, for example, this input:
>
> 2015-09-04 *
> Assets:Bank -750.00 USD
> Assets:Bitcoin 3.000000 BTC @ 250.00 USD
>
> 2015-09-05 *
> Assets:Bank -780.00 USD
> Assets:Bitcoin 3.000000 BTC @ 260.00 USD
>
> 2015-09-20 *
> Assets:Bitcoin -2.000000 BTC @ 300.00 USD
> Expenses:Something
>
> 2015-09-21 *
> Assets:Bitcoin -2.000000 BTC @ 310.00 USD
> Expenses:Something
>
> 2015-09-22 *
> Assets:Bitcoin -2.000000 BTC @ 330.00 USD
> Expenses:Something
>
> Would get translated by the plugin into the equivalent of this:
>
> 2015-09-04 *
> Assets:Bitcoin 3.000000 BTC {250.00 USD} @ 250.00 USD
> trades: "trade-ff4c3e592147,trade-6b9c8689ed50"
> Assets:Bank -750.00 USD
>
> 2015-09-05 *
> Assets:Bitcoin 3.000000 BTC {260.00 USD} @ 260.00 USD
> trades: "trade-91e2245de593,trade-d622a89c614d"
> Assets:Bank -780.00 USD
>
> 2015-09-20 *
> Assets:Bitcoin -2.000000 BTC {250.00 USD} @ 300.00 USD
> trades: "trade-ff4c3e592147"
> Income:Bitcoin -100.00000000 USD
> Expenses:Something 600.00000000 USD
>
> 2015-09-21 *
> Assets:Bitcoin -1.000000 BTC {250.00 USD} @ 310.00 USD
> trades: "trade-6b9c8689ed50"
> Assets:Bitcoin -1.000000 BTC {260.00 USD} @ 310.00 USD
> trades: "trade-91e2245de593"
> Income:Bitcoin -110.00000000 USD
> Expenses:Something 620.00000000 USD
>
> 2015-09-22 *
> Assets:Bitcoin -2.000000 BTC {260.00 USD} @ 330.00 USD
> trades: "trade-d622a89c614d"
> Income:Bitcoin -140.00000000 USD
> Expenses:Something 660.00000000 USD
>
>
> Note that the price annotations are kept as in the input file. The plugin
> only
> inserts the cost basis and splits postings where required.
>
> Secondly, a unique trade identifier is allocated for each matching pair of
> postings that were booked and is added to the metadata of those postings.
> Its
> value is a comma-separated list (this is the 'trades: "trade-..."' bit
> above if
> you're not familiar with Beancount syntax). This metadata is useful
> because it
> allows you to load the entries and from it, to build a list of trades to
> produce
> a suitable report for reporting.
>
> So the plugin also contains code to do this. You can invoke it as a main
> program, like this:
>
> python3 -m beancount.plugins.book_conversions <filename.beancount>
>
> On the file above, it should generate the following output, which I
> believe is
> the report you're really after for reporting purposes:
>
> Units Currency Cost Currency Buy Date Buy Price Sell Date
> Sell Price P/L
> -------- -------- ------------- ---------- --------- ----------
> ---------- ------
> 2.000000 BTC USD 2015-09-04 250.00 2015-09-20
> 300.00 100.00
> 1.000000 BTC USD 2015-09-04 250.00 2015-09-21
> 310.00 60.00
> 1.000000 BTC USD 2015-09-05 260.00 2015-09-21
> 310.00 50.00
> 2.000000 BTC USD 2015-09-05 260.00 2015-09-22
> 330.00 140.00
> -------- -------- ------------- ---------- --------- ----------
> ---------- ------
>
> Use the '--format=csv' option if you want to produce output suitable for
> including in a spreadsheet. If you need to customize the output further,
> see the
> source code (file:book_conversions.py). It is not possible to generate
> this list
> of trades using the SQL client yet, to change the output columns you would
> have
> to create a standalone script; you can copy just the main() function in the
> plugin if you need to do that and import the rest. (Note: The function that
> extracts the list of matches provides both the posting and its parent
> Transaction object, so you can output data from the transaction if you
> want,
> e.g. the narration of the selling transaction.)
>
> Finally, as I show here, the right solution to this problem is not to
> export
> your data outside the software and to try to do this manually. The right
> thing
> to do is to have the booking be done right inside the software itself.
> This has
> multiple benefits:
>
> - The Income legs of the transactions naturally accumulate the P/L
> incurred for
> that transaction. This means that filtering any subset of transactions a
> custom SQL query could produce would automatically compute the correct
> P/L
> balance for this subset, e.g. if you produce "income statements" for each
> quarter that include the P/L from those transactions, you would just
> have the
> right amount.
>
> - Errors in quantities will be flagged automatically. For instance, should
> you
> make a mistake in the input that sells more units than are accumulated,
> an
> error would be generated.
>
> - Finally, it allows the software to compute and report the cost basis of
> the
> inventory lots that remain, and that should allow you to do a little bit
> of
> tax planning, avoiding to incur excessive capital gains if there are
> great
> movements in the price of Bitcoins.
>
> I hope this is helpful, if anything, to write your own script.
>
>
> {c395de691449}
>
>
>
>
> On Thu, Sep 24, 2015 at 2:32 AM, Michael Toomim <[email protected]> wrote:
> >> On Sep 23, 2015, at 10:20 AM, John Wiegley <[email protected]>
> wrote:
> >>
> >>>>>>> Michael Toomim <[email protected]> writes:
> >>
> >>> Sure, I gave examples at the beginning of this thread. I want to get a
> >>> transaction out, but the csv and register commands will only give me
> >>> postings.
> >>
> >>> Here's a transaction:
> >>
> >>> 2014-1-1 sold bitcoin
> >>> Assets:bitcoin 4 btc @ $250
> >>> Assets:cash -$1,000
> >>
> >>> CSV export will currently give you:
> >>
> >>> "2014/01/01","","sold bitcoin","Assets:bitcoin","btc","4","",""
> >>> "2014/01/01","","sold bitcoin","Assets:cash","$","-1000","",""
> >>
> >> So, you can do this:
> >>
> >> ledger --prepend-format '"%(xact.id)",' csv
> >>
> >> And now you'll be able to determine related postings by grouping
> according to
> >> those with the same transaction id. This should be trivial in whatever
> >> scripting language you prefer.
> >
> > Thank you John! This feature is xact-ly what I need! I'm saved!
> >
> > We should get xact.id into the documentation, so that other users can
> learn that it exists.
> >
> >> At the beginning of this thread I gave details on everything I tried to
> get
> >>> this information out of Ledger.
> >>
> >> Sorry, I'm a bit swamped at the moment to address this problem in the
> detail
> >> it deserves. Hopefully we can get you on the right place without too
> much
> >> difficulty.
> >
> > I'm on the right path now, thank you. And I'm sorry that my original
> report was hard to understand, I was swamped at that moment too. I'll be
> able to work on this with a clearer mind soon.
> >
> > --
> >
> > ---
> > You received this message because you are subscribed to the Google
> Groups "Ledger" group.
> > To unsubscribe from this group and stop receiving emails from it, send
> an email to [email protected].
> > For more options, visit https://groups.google.com/d/optout.
>
> --
>
> ---
> You received this message because you are subscribed to the Google Groups
> "Ledger" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
---
You received this message because you are subscribed to the Google Groups
"Ledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
