can one reduce FIFO lots purchased in one currency and sold in another?

[jke...@gmail.com]

Hello fellow beancount users,

I've got a bunch of bitcoin purchases in USD that I then sold for EUR, and beancount isn't matching the EUR sales with the USD purchases of bitcoin. Running bean-check against the transactions results in a "No position matches" and a list of the current non-reduced lots--all denominated in USD. I have daily EUR - USD and BTC - USD price histories per beancount's syntax in my ledger file. All my sales of bitcoin to USD reduced just fine... its just the sales to EUR that aren't working out.

Originally I had my reducing legs of the transactions with empty curly braces {} but then I tried the following:

option "title" "My Personal Ledger"
option "operating_currency" "USD"
option "booking_method" "FIFO"

2013-01-01 open Equity:Opening-Balances
2013-01-01 open Assets:Crypto:Bitcoin           BTC
2013-01-01 open Assets:Checking:CapitalOne      USD
2013-01-01 open Expenses:Crypto:Fees:Coinbase   USD
2013-01-01 open Assets:Checking:BNP             EUR
2013-01-01 open Income:CapitalGains

2016-05-25  price  BTC    449.24 USD
2016-05-25  price  EUR    1.12 USD
2016-05-27  price  BTC    474.38 USD
2016-05-27  price  EUR    1.11 USD

2016-05-25 * "Coinbase" "Bitcoin purchase"
    Expenses:Crypto:Fees:Coinbase    4.60 USD
    Assets:Crypto:Bitcoin            1.02140000 BTC {450.07 USD}
    Assets:Checking:CapitalOne    -464.30 USD

2016-05-27 * "(s9d8fukf0)" "Sell BTC via bitsquare"
    Assets:Crypto:Bitcoin           -0.50000000 BTC {USD} @ 416.75 EUR
    Assets:Checking:BNP            208.38 EUR
    Income:CapitalGains

2016-05-27 * "(l2l3jfp5d)" "Sell BTC via bitsquare"
    Assets:Crypto:Bitcoin           -0.50000000 BTC {USD} @ 412.00 EUR
    Assets:Checking:BNP            206.00 EUR
    Income:CapitalGains

That seems to work, except that that bean-report balances now shows me EUR and USD on separate capital gains lines:

Income:CapitalGains             -414.38       EUR
Income:CapitalGains              450.07       USD

So, I've got two questions:

1. Since my file includes prices, is there a way I can have beancount know the USD equivalent for reducing lots in EUR that were purchased with USD?

2. If I explicitly put "USD" in all my empty {}, how does will FIFO booking option handle the occasional cases where I've purchased bitcoin with euros? Will it always reduce the oldest bitcoin lots first, regardless of the currency I'm reducing with, or will explicitly specifying the reducing currency mean I have to manually check the order lots are cleared to make sure that the total of the two currencies reduces correctly per FIFO?

I guess I'm looking to understand more specifically what the FIFO booking is doing or not doing when a commodity (BTC) is purchased in one currency (USD) and sold in another (EUR).

[Martin Blais]

This is not legal Beancount syntax, you're not supposed to be able to use a different cost currency as a price currency.

This should raise an error (it doesn't, verified).

(This is excellent, you have an exceptional skill at finding bugs. I was dead certain I had a unit test for this type of error and the fact it doesn't raise an error really surprises me. I will fix and make this raise an error.)

Furthermore, when a cost basis and a price annotation are both present, the price is unused. It's only there as a convenience to fill in the price database (the implicit_prices plugin does that, it looks for those and converts them into Price directives). The total cost basis of that posting is always the amount used to balance with the rest of the postings.

Here, this will work:

2016-05-27 * "(s9d8fukf0)" "Sell BTC via bitsquare"

    Assets:Crypto:Bitcoin           -0.50000000 BTC {USD}

    Assets:Checking:BNP            208.38 EUR @ 1/416.75 USD

    Income:CapitalGains

2016-05-27 * "(l2l3jfp5d)" "Sell BTC via bitsquare"

    Assets:Crypto:Bitcoin           -0.50000000 BTC {USD}

    Assets:Checking:BNP            206.00 EUR @ 1/412.00 USD

    Income:CapitalGains

Here's how to debug: You can view what Beancount expands to like this:

bean-query /home/blais/r/q/beancount-data/user/jkepler/20170819.beancount print

This gives you the whole file, interpolated, expanded, matched. 

This is what actually gets aggregated.

If you're interested in the context of a particular transaction you can use this command:

bean-doctor context 20170819.beancount 25

Hash:ff03de6e58cb3b0b4355684d0e00be61

Location: /home/blais/r/q/beancount-data/user/jkepler/20170819.beancount:22

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

  Assets:Crypto:Bitcoin            1.02140000 BTC {450.07 USD, 2016-05-25}

  Assets:Checking:BNP

  Income:CapitalGains

------------ Transaction

2016-05-27 * "(s9d8fukf0)" "Sell BTC via bitsquare"

  Assets:Crypto:Bitcoin  -0.50000000 BTC {450.07 USD, 2016-05-25}                ;                -225.0350000000 USD

  Assets:Checking:BNP         208.38 EUR @ 0.002399520095980803839232153569 USD  ; 0.5000119976004799040191961607 USD

  Income:CapitalGains         224.53 USD                                         ;  224.5349880023995200959808038 USD

Tolerances: BTC=5E-9, EUR=0.005

Basis: (-225.0350000000 USD)

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

* Assets:Crypto:Bitcoin            0.52140000 BTC {450.07 USD, 2016-05-25}

* Assets:Checking:BNP                                           208.38 EUR

* Income:CapitalGains                    224.5349880023995200959808038 USD

"25" is the line number, and the Emacs mode has support for automatically bringing up this.

This is useful to debug issues with matching lots.

 

So, I've got two questions:

1. Since my file includes prices, is there a way I can have beancount know the USD equivalent for reducing lots in EUR that were purchased with USD?

You could convert the amounts to a single currency (though the rate used for the conversion will be the latest/current one, not that at the time of each transaction).

bean-query ...  "select account, convert(sum(position), 'USD') ..."

 

2. If I explicitly put "USD" in all my empty {}, how does will FIFO booking option handle the occasional cases where I've purchased bitcoin with euros?

I don't understand the question. If you try to reduce and provide USD in the description, it will only match against lots in USD. Can you provide an example pair of transactions?

 

Will it always reduce the oldest bitcoin lots first, regardless of the currency I'm reducing with, or will explicitly specifying the reducing currency mean I have to manually check the order lots are cleared to make sure that the total of the two currencies reduces correctly per FIFO?

I'm still not sure I understand, it will never convert currencies when reducing.

 

I guess I'm looking to understand more specifically what the FIFO booking is doing or not doing when a commodity (BTC) is purchased in one currency (USD) and sold in another (EUR).

The booking method looks at the contents of {...}, treats it like a specification to winnow down the set of available lots to a subset of ones matching what you put in there (currency, cost basis, purchase date, label, and any combination of those). If after filtering there is more than a single lot, the "booking method" is invoked. If that is STRICT (the default), an error is raised. If it is FIFO, the lots are eaten up until there is a total amount matching the balancing number of units.

I hope this helps,

 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+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/77653225-48df-4b1d-8e36-939a4ae6b138%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[jke...@gmail.com]

Thanks for your response.

2. If I explicitly put "USD" in all my empty {}, how does will FIFO booking option handle the occasional cases where I've purchased bitcoin with euros?

I don't understand the question. If you try to reduce and provide USD in the description, it will only match against lots in USD. Can you provide an example pair of transactions?

 

Will it always reduce the oldest bitcoin lots first, regardless of the currency I'm reducing with, or will explicitly specifying the reducing currency mean I have to manually check the order lots are cleared to make sure that the total of the two currencies reduces correctly per FIFO?

I'm still not sure I understand, it will never convert currencies when reducing.

 Here's an example, from a fictitious episode in which I rode Amtrak back and forth a few times between New York City and Montreal to sell apples:

 

option "title" "My Personal Ledger"
option "operating_currency" "USD"

option "operating_currency" "CAD"
option "booking_method" "FIFO"

2016-01-01 open Equity:Opening-Balances
2016-01-01 open Assets:US:Cash                    USD                    
2016-01-01 open Assets:CA:Cash                    CAD
2016-01-01 open Assets:MyBackpack:Stuff        APPLES
2016-01-01 open Income:CapitalGains

2016-05-26  price  CAD   0.77046 USD
2016-05-28  price  CAD   0.76761 USD

2016-05-24 * "Opening Balances"
  Assets:US:Cash            40.00 USD
  Assets:CA:Cash             0.00 CAD
  Equity:Opening-Balances

2016-05-25 * "Bought some apples in New York"
  Assets:MyBackpack:Stuff   10 APPLES {1.00 USD}
  Assets:US:Cash

2016-05-26 * "Convert USD to CAD"
  Assets:CA:Cash                
  Assets:US:Cash           -15.03 USD @ 1.29792 CAD

2016-05-26 * "Bought some more apples in Montreal"
  Assets:MyBackpack:Stuff   13 APPLES {1.50 CAD}
  Assets:CA:Cash

2016-05-27 * "Bought some apples in New York"
  Assets:MyBackpack:Stuff   11 APPLES {1.20 USD}
  Assets:US:Cash

2016-05-28 * "Sold some more apples in Montreal"
  Assets:MyBackpack:Stuff  -14 APPLES {USD} @ 1.70 CAD   ; should sell 10 APPLES {1.00 USD} & 4 APPLES {1.50 CAD}, a cost basis of 14.605 USD
  Assets:CA:Cash            23.80 CAD                    ; converts to 18.269 USD
  Income:CapitalGains                                    ; revenue (18.269 USD) - cost basis (14.605 USD) = 3.665 USD

; CapitalGains = 3.47 USD per beancount when the {1.50 CAD} lot doesn't get reduced.

I don't think beancount is reducing my apples by FIFO correctly, because when I run the bean-report sample holdings when selling 14 apples, its reducing from my {1.00 USD, 2016-05-25} and {1.20 USD, 2016-05-27} lots, but not reducing any apples from my {1.50 CAD, 2016-05-26} lot. Since apples are apples, shouldn't it reduce the lots by date order?

If I replace the {USD} with an empty {} beancount tries to reduce all 14 sold apples form the {1.50 CAD, 2016-05-26} lot, but fails because there's only 13 apples in that lot. The only was I can find to have beancount reduce my apple inventory selling the oldest lots first is manually, like this:

2016-05-28 * "Sold some more apples in Montreal"
  Assets:MyBackpack:Stuff  -10 APPLES {USD} @ 1.70 CAD
  Assets:MyBackpack:Stuff   -4 APPLES {CAD} @ 1.70 CAD
  Assets:CA:Cash            23.80 CAD                    
  Income:CapitalGains    

So, I'm not sure FIFO is reducing the oldest lots first. It depends on the lot currency.

[jke...@gmail.com]

One clarification: over lunch I was thinking about this, and realized that beancount does FIFO booking reductions correctly for securities like stocks under the usually valid assumption  a security is sold/ reduced in the same currency it was purchased in, since each security is denominated in the currency of its exchange.  This is reasonable: if one buys shares in Nokia (NOK) listed on the New York Stock Exchange, they're always listed in USD. Shares listed in EUR are available on the Helsinki Stock Exchange, but their symbol is NOKIA, hence a different security.

Where thinks currently seem not to work is if one is tracking a real commodity -- apples, bitcoin, or coffee -- anything that doesn't have an intrinsic currency for valuing it.

Does that make sense, or have I misunderstood something?

Thanks for all you work on beancount. It's a great tool!

Joel

[Martin Blais]

On Sat, Aug 19, 2017 at 7:54 AM, <jke...@gmail.com> wrote:

One clarification: over lunch I was thinking about this, and realized that beancount does FIFO booking reductions correctly for securities like stocks under the usually valid assumption  a security is sold/ reduced in the same currency it was purchased in, since each security is denominated in the currency of its exchange.  

That is correct.

The main issue is that there are two processes going on, and it's not clear which one could come first:

- Interpolation of missing values

- Booking against existing inventories of lots

Resolving this is hard - one can come up with useful cases where one would be preferred to run before the other - and part of it involves inferring the currency of a lot.

Once the currencies are resolved, then the booking takes place.

So the currency is fixed.

 

This is reasonable: if one buys shares in Nokia (NOK) listed on the New York Stock Exchange, they're always listed in USD. Shares listed in EUR are available on the Helsinki Stock Exchange, but their symbol is NOKIA, hence a different security.

Where thinks currently seem not to work is if one is tracking a real commodity -- apples, bitcoin, or coffee -- anything that doesn't have an intrinsic currency for valuing it.

What you call a "real commodity" here is generally a currency. Beancount differs from Ledger in this regard, you can treat currency as they are.

 

Does that make sense, or have I misunderstood something?

No, I think you get it.

However, I think there may be a better resolution here.

I think you may be able to treat Bitcoin as a currency, disregard its cost basis, yet still be able to account for gain/loss for it.

Let me explain.

When a currency conversion is accepted by Beancount, no cost basis is stored.

However, if the exchange rate takes place, a gain or loss will be incurred.

In other words, the sum of all postings will not be an empty inventory.

In order to deal with this, Beancount inserts an ugly conversion transaction which makes us for exactly this. 

This transaction is inserted by this code:

http://www.mscs.dal.ca/~selinger/accounting/tutorial.html

Its purpose is to exactly counterbalance this difference, across all currencies.

One problem is that its contents must depend on the set of transactions being summarized.

That's why it's inserted when you open or close on the set of directives, e.g.., before producing a report.

Now, please take a few minutes to dive in and go read this:

http://www.mscs.dal.ca/~selinger/accounting/tutorial.html

This method resolves the problem elegantly.

Each of the transactions posts the currency differences to a dedicated set of currency accounts.

Unfortunately, it would be a pain to enter your transactions in this way.

However, it would be simple to do this automatically with Beancount, even as a plugin.

I plan to do this eventually.

(You're welcome to get ahead about that, I'm working on other things these days.)

Now, how does this relate to your question?

Well, with this "trading accounts" method, you could later (in time) unwind some of these currency account at their current rate, and post the difference as a gain/loss.

I know I've said previously it would be easier to use the cost basis. 

What you get from that is specific lot reduction methods, e.g. FIFO, LIFO.

On the other hand, if you'd like not to have a particular cost currency, really you are dealing with currencies.

In this case, you won't have any booking, but you could still have total gain/loss.

(I hope this makes sense.)

 

Thanks for all you work on beancount. It's a great tool!

Thanks, and please keep on finding problems, that's the way software gets stronger.

 

--

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+unsubscribe@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/dc892eed-3ce2-4372-92c3-6decc85ccab0%40googlegroups.com.

[jke...@gmail.com]

Thanks for your thorough and helpful response. I think I'd seen Selinger's currency trading accounts post referenced before, but hadn't taken time to read and understand it. Wow. His method does resolve the problem "elegantly," as you say.

Unfortunately for me, I believe that the IRS requires using FIFO to calculate capital gains and losses with bitcoin. So as much as I would like to simply treat it like another currency and use Selinger's "trading account(s)" method, I don't think I'm free to do that.

So, I'm currently trying (A.) to wrap my mind around how to write a plugin as you suggested or (B.) to re-work my source data converting the EUR amounts to USD equivalents, thereby avoiding the problem of trying to reduce lots in a different currency from that which I used to purchase them.

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.

[Martin Blais]

On Thu, Jul 5, 2018 at 5:37 PM <den...@uncommon.co> wrote:

Hi,

I've just read the Selinger's excellent post. I have a few questions:

1. Martin said "This transaction is inserted by this code:", but then it's followed by the duplicate link to the Selinger's post (probably mistakenly). What's the correct link?

My bad, I meant to insert this link: 

https://bitbucket.org/blais/beancount/src/44ee4feed2fc1fb405aefd97c5b7d3340613286a/beancount/ops/summarize.py#lines-459

2. Using Selinger's terminology, what method of multiple currency balancing is used in beancount? I'm coming from Gnucash, so I'm familiar with the Trading Accounts method. 

I don't think he describes what Beancount does.

 

3. Is there beancount documentation devoted to the multiple currency question? I think I've read most of the docs, but don't recall anything.

Not particularly, there's a little bit all over.

Ultimately, I think the best way will be to implement the trading accounts method, but it has to be fully automated, that is, none of the input will need to change (it shouldn't have to).

 

Thank you!

Dennis Golomazov

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e1b43304-3ea7-407b-a03c-45d76e7accf8%40googlegroups.com.

[Metin Akat]

Hi,

I have been thinking about this and it seems that regular syntax doesn't contain enough information (if you don't use the position syntax with {}) for it to be possible (or at least feasible) to implement it.

The information gets lost when you do transformations like  CurrencyA => CurrencyB => CurrencyC => CurrencyA 

In this case there has to be some hint as to which lot/position you are trading or the software has to hold a "full-path" reference to the positions (the whole history of trading) which will explode in complexity. I presume GnuCash generates positions/lots upon every transformation between currencies.

Of course, I might be wrong in my understanding of the whole process, please correct me if so.

Best Regards,

Metin

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e1b43304-3ea7-407b-a03c-45d76e7accf8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhP8WT0GCXxz73Vv7PrJviqYftC8zsduAsBxwjbG9Ogk%3Dg%40mail.gmail.com.

[Dennis Golomazov]

This is what I think too. Please consider this example: https://www.mscs.dal.ca/~selinger/accounting/tutorial.html#2.2 (
Double entry foreign currency accounting, the wrong way). Doesn't it describe the way beancount works?

I also created a dedicated topic for multiple currencies, we may continue there: https://groups.google.com/forum/?utm_medium=email&utm_source=footer#!msg/beancount/ByOdqXAGANA/Pv50y4uqAgAJ

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e1b43304-3ea7-407b-a03c-45d76e7accf8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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 post to this group, send email to bean...@googlegroups.com.

[Martin Blais]

On Fri, Jul 6, 2018 at 2:38 AM Metin Akat <akat....@gmail.com> wrote:

Hi,

I have been thinking about this and it seems that regular syntax doesn't contain enough information (if you don't use the position syntax with {}) for it to be possible (or at least feasible) to implement it.

You should not use the {} syntax when doing price conversions. We do want to lose the conversion rate once the currencies have been converted.

 

The information gets lost when you do transformations like  CurrencyA => CurrencyB => CurrencyC => CurrencyA 

In this case there has to be some hint as to which lot/position you are trading or the software has to hold a "full-path" reference to the positions (the whole history of trading) which will explode in complexity. I presume GnuCash generates positions/lots upon every transformation between currencies.

Which information gets lost?

Of course, I might be wrong in my understanding of the whole process, please correct me if so.

I don't understand. Perhaps provide a more specific example (transactions in Beancount format).

If we automatically inserted postings to reverse the currency conversions on each transaction (i.e., implement a plugin that applies the "trading accounts" method automatically), lot information would still be lost. All you'd be left with are the same balances + non-zero balances in the trading accounts (which you could eventually pull balances from to account for profits/losses due to currency conversions).

 

Best Regards,

Metin

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e1b43304-3ea7-407b-a03c-45d76e7accf8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
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 post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhP8WT0GCXxz73Vv7PrJviqYftC8zsduAsBxwjbG9Ogk%3Dg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

--
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 post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAEk80bmtGDeSKT%2B%2BPsx-x-DCozOdspXTaTWZQ05axVyEwMtDaw%40mail.gmail.com.

[Metin Akat]

I saw your replies in the other thread. Thanks. I'll think about it and reply there if I have what to say.

Best Regards,

Metin

To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscribe@googlegroups.com.

To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/e1b43304-3ea7-407b-a03c-45d76e7accf8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhP8WT0GCXxz73Vv7PrJviqYftC8zsduAsBxwjbG9Ogk%3Dg%40mail.gmail.com.

For more options, visit https://groups.google.com/d/optout.

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAEk80bmtGDeSKT%2B%2BPsx-x-DCozOdspXTaTWZQ05axVyEwMtDaw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

--

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

To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/CAK21%2BhNqFLdtWn-A7CbJgHZqJ3aG0ZOC3aQjFD91OBPcFFYERA%40mail.gmail.com.