Stock purchase combined with foreign currency conversion

Martin Blais

unread,

Nov 2, 2014, 4:58:42 PM11/2/14

to ledger-cli

Hi,

I'm trying to complete my Beancount to Ledger syntax converter. There's only one issue left.

I have some transactions like this in my Beancount file:

2014-11-02 open Expenses:Commissions
2014-11-02 open Assets:CA:Investment:GOOG
2014-11-02 open Assets:CA:Investment:Cash

2014-11-02 * "Buy some stock with foreign currency funds (CAD -> USD)"
Assets:CA:Investment:GOOG 5 GOOG {520.0 USD}
Expenses:Commissions 9.95 USD
Assets:CA:Investment:Cash -2939.46 CAD @ 0.8879 USD

Specifically, what I mean is that I have postings taking cash in a foreign currency (the third) and other postings held at cost (the first). This is not a theoretical use case BTW, in my past account history I have an account in a foreign currency that is used to buy stocks on the US market and the conversion occurs this way, automatically and atomically (I'm trying to convert my real ledger file to Ledger, for comparison purposes).

My converter script translates this to a Ledger equivalent:

account Expenses:Commissions
account Assets:CA:Investment:GOOG
account Assets:CA:Investment:Cash

2014/11/02 * Buy some stock with foreign currency funds (CAD -> USD)
Assets:CA:Investment:GOOG 5.00 GOOG {520.00 USD}
Expenses:Commissions 9.95 USD
Assets:CA:Investment:Cash -2,939.46 CAD @ 0.887900000000 USD

P 2014/11/02 00:00:00 GOOG 520.00 USD
P 2014/11/02 00:00:00 CAD 0.88790 USD

This does not work, however, Ledger appears not to grok it:

ledger -f /Users/blais/p/beancount-data/foreign-paid-stock-purchase.beancount bal
While parsing file "/Users/blais/p/beancount-data/foreign-paid-stock-purchase.beancount", line 8:
While balancing transaction from "/Users/blais/p/beancount-data/foreign-paid-stock-purchase.beancount", lines 5-8:
> 2014-11-02 * "Buy some stock with foreign currency funds (CAD -> USD)"
> Assets:CA:Investment:GOOG 5 GOOG {520.0 USD}
> Expenses:Commissions 9.95 USD
> Assets:CA:Investment:Cash -2939.46 CAD @ 0.8879 USD
Unbalanced remainder is:
5 GOOG {520.00 USD}
-2600.00 USD
Amount to balance against:
5 GOOG {520.00 USD}
9.95 USD
Error: Transaction does not balance

Changing the price conversion to a {} stock conversion also fails:

2014/11/07 * Buy some stock with foreign currency funds (CAD -> USD)
Assets:CA:Investment:GOOG 5.00 GOOG {520.00 USD}
Expenses:Commissions 9.95 USD
Assets:CA:Investment:Cash -2,939.46 CAD {0.887900000000 USD}

I get similar error messages.

So I tried simplifying some more by removing the commissions leg, adjusting the price:

2014/11/06 * Buy some stock with foreign currency funds (CAD -> USD)
Assets:CA:Investment:GOOG 5.00 GOOG {520.00 USD}
Assets:CA:Investment:Cash -2,928.2577 CAD @ 0.887900000000 USD

Ledger isn't happy about this either. But changing the currency conversion to a cost-style conversion (which intuitively appears to me wrong, this is not a lot to be tracked, but whatever, at this point I'm just trying to get syntax that works), it seems to accept it:

2014/11/06 * Buy some stock with foreign currency funds (CAD -> USD)
Assets:CA:Investment:GOOG 5.00 GOOG {520.00 USD}
Assets:CA:Investment:Cash -2,928.2577 CAD {0.887900000000 USD}

So based on the above, Ledger _does_ treat @ price vs. {price} rates at least somewhat differently.

If I add the commissions leg back to the previous transaction (adjusting the numbers correctly), it stops accepting it.

I'm confused.

I have two questions:

1. Can somebody explain how the treatment of "@ price" vs. "{price}" differs? Other members of this list have previously pointed out to the docs that say that they are entirely equivalent. Based on the previous change, they appear not to be. This needs clarification IMO.

2. What is the correct way to convert my original transaction to Ledger syntax?

3. More generally, what is the rule for what combinations of price/cost conversions it should be able to accept or not?

Thank you,

Martin Michlmayr

unread,

Nov 2, 2014, 5:10:35 PM11/2/14

to ledge...@googlegroups.com

* Martin Blais <bl...@furius.ca> [2014-11-02 16:58]:

> 1. Can somebody explain how the treatment of "@ price" vs. "{price}"
> differs? Other members of this list have previously pointed out to the docs
> that say that they are entirely equivalent. Based on the previous change,
> they appear not to be. This needs clarification IMO.

{price} is the lot price, i.e. the original cost. @ price is what
it's worth now. e.g. you could have 1 AAA {5.00 GBP} @ 6.00 GBP - you
bought it for 5 and now you're selling it for 6.

Why are {price} and @ price handled differenly? I think this is a
bug. See http://bugs.ledger-cli.org/show_bug.cgi?id=630

> 2. What is the correct way to convert my original transaction to Ledger
> syntax?

I think for now:

Assets:CA:Investment:GOOG 5 GOOG {520.0 USD} @ 520.0 USD

Expenses:Commissions 9.95 USD
Assets:CA:Investment:Cash -2939.46 CAD @ 0.8879 USD

--
Martin Michlmayr
http://www.cyrius.com/

Martin Blais

unread,

Nov 2, 2014, 5:37:00 PM11/2/14

to ledger-cli

On Sun, Nov 2, 2014 at 5:10 PM, Martin Michlmayr <t...@cyrius.com> wrote:

* Martin Blais <bl...@furius.ca> [2014-11-02 16:58]:
> 1. Can somebody explain how the treatment of "@ price" vs. "{price}"
> differs? Other members of this list have previously pointed out to the docs
> that say that they are entirely equivalent. Based on the previous change,
> they appear not to be. This needs clarification IMO.

{price} is the lot price, i.e. the original cost. @ price is what
it's worth now. e.g. you could have 1 AAA {5.00 GBP} @ 6.00 GBP - you
bought it for 5 and now you're selling it for 6.

Why are {price} and @ price handled differenly? I think this is a
bug. See http://bugs.ledger-cli.org/show_bug.cgi?id=630

This makes sense, thanks for the link.

I think as you explore issues related to fixing that bug, eventually you might end up considering the same model I've using for currency conversions, it avoids the problem with well-defined and simply understood meaning:

https://bitbucket.org/blais/beancount/src/2376dd522c03977955e6915153a3a1cfd8a47724/src/python/beancount/core/complete.py?at=default#cl-28

Another option that might be considered as a fix is the implementation of GnuCash's "Trading Accounts" method. I'm considering writing a plugin to automatically do that in Beancount, so the user could choose to use Trading Accounts (with no effort required, would happen automatically) or the conversions patch-up method I currently use.

> 2. What is the correct way to convert my original transaction to Ledger
> syntax?

I think for now:

Assets:CA:Investment:GOOG 5 GOOG {520.0 USD} @ 520.0 USD

Expenses:Commissions 9.95 USD
Assets:CA:Investment:Cash -2939.46 CAD @ 0.8879 USD

This works indeed. Adding the price seems to have no other effect on the balances report than to make it accept the input, it's odd (and perhaps insert that as a price entry in the price db?). This will work for my conversion. (I was halfway through splitting the entries in two, a conversion entry and a stock purchase entry. Thanks for the quick reply.)

Thanks Martin!

P.S. I think it might be worthwhile for someone to spell out the balancing algorithm in detail in the docs.

--
Martin Michlmayr
http://www.cyrius.com/

--

---
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 ledger-cli+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Reply all

Reply to author

Forward