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.
