Multiple currencies in beancount

[Dennis Golomazov]

Hi,

I've just discovered the plain text accounting a few days ago and now I'm switching from Gnucash (with 10 years of transactions in it). 

I've chosen beancount because I like the design philosophy (stricter validation, "one best way" to do things) and the fact that it's Python, my primary language (and it also has fava).

I've just read the Selinger's excellent post on multiple currency accounting (https://www.mscs.dal.ca/~selinger/accounting/tutorial.html),

and I have a few questions:

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

2. Is there beancount documentation devoted to the multiple currency question? I think I've read most of the docs, but don't recall anything. I'd be glad to have a detailed post with examples and everything, just like the rest of the docs.

Thank you!

Dennis Golomazov

[Justus Pendleton]

Read "How Inventories Work". The first example talks about using multiple currencies.

[Dennis Golomazov]

I did. I don't think there is sufficient information there. Please consider this paragraph "Double entry foreign currency accounting, the wrong way":

https://www.mscs.dal.ca/~selinger/accounting/tutorial.html#2.2

Doesn't it describe the beancount way of handling multiple currencies (when no cost is specified)?

[Martin Blais]

On Fri, Jul 6, 2018 at 12:22 PM Dennis Golomazov <denis.g...@gmail.com> wrote:

I did. I don't think there is sufficient information there. Please consider this paragraph "Double entry foreign currency accounting, the wrong way":

https://www.mscs.dal.ca/~selinger/accounting/tutorial.html#2.2

I think he means "wrong" in the context of attempting to keep correct balances in his system.

We can reason about these things ourselves, it's really not that complicated.

 

Doesn't it describe the beancount way of handling multiple currencies (when no cost is specified)?

Yes and no.

If you don't produce a summarization statement (and thus apply the conversions() transaction), that's what Beancount does.

However, the special conversions transaction specifically corrects for that and produces the same result.

Now, what's "wrong" about this way of doing things is that the conversion transaction that must be applied is a function of the subset of transactions being added up.

So if you change the journal you're filtering / looking at (e.g. the year, tags, whatever filter), the conversion transaction must be updated.

I don't like this.

Doing the currency conversions per transaction (as PS does) allows one to simply filter some subset of transactions and sum them up and voila, the correct trading account values appear. Each transaction is a pure zero sum. Using a simple SUM() operation in the shell should work as expected.

I like the simplicity of that, which is why I've been wanting to implement this method for a while.

Now, on the other hand, the way we do this currently is "correct" in the sense that the input the user should provide looks exactly like what he calls the "wrong" way.

This IS the most natural way to input things, and that which mirrors how we think about these transactions in the real world.

I definitely don't want to impose the burden of thinking about these "trading accounts" when inputting data.

My point is that the user should keep thinking in those natural terms (and not change the input format), but we should hide and completely automate the insertion of trading account postings on those transactions that involve a price conversion (automatically transform the transaction objects in the stream), and eliminate the special conversion transaction insertion (actually if there are no balances it can be a noop, so no code change is necessary). This could be prototyped in a rather simple plugin TBH.

On Friday, July 6, 2018 at 6:29:06 AM UTC-7, Justus Pendleton wrote:

Read "How Inventories Work". The first example talks about using multiple currencies.

--
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/3426155a-fdae-41d8-838d-90babf47efc2%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

Here's an example from the multiple currency conversion email on the other thread.

This conversion from USD -> EUR -> CAD:

plugin "beancount.plugins.auto"

2018-07-06 * "Initial deposit"

  Income:US:Salary -2000.00 USD

  Assets:US:Checking

2018-07-06 * "Move to France"

  Assets:US:Checking     -1000.00 USD @ EUR

  Assets:FR:Checking     847.46 EUR

2018-07-06 * "Move to France"

  Assets:FR:Checking     -847.46 EUR @ CAD

  Assets:CA:Checking     1,303.90 CAD

2018-09-01 query "bal" "select account, sum(position)"

Produces:

bean-query currencies.beancount run bal

     account       sum_position

------------------ ------------

Income:US:Salary   -2000.00 USD

Assets:US:Checking  1000.00 USD

Assets:FR:Checking

Assets:CA:Checking  1303.90 CAD

Now, if we implemented the "trading accounts" method, the input would be implicitly converted to something almost like this (I'll do it manually here):

plugin "beancount.plugins.auto"

2018-07-06 * "Initial deposit"

  Income:US:Salary -2000.00 USD

  Assets:US:Checking

2018-07-06 * "Move to France"

  Assets:US:Checking      -1000.00 USD

  Assets:FR:Checking        847.46 EUR

  Equity:Conversions:USD   1000.00 USD

  Equity:Conversions:EUR   -847.46 EUR

2018-07-06 * "Move to France"

  Assets:FR:Checking        -847.46 EUR

  Assets:CA:Checking       1,303.90 CAD

  Equity:Conversions:EUR     847.46 EUR

  Equity:Conversions:CAD  -1,303.90 CAD

Now the balances perfectly sum to zero:

bean-query currencies.beancount run bal

       account         sum_position

---------------------- ------------

Income:US:Salary       -2000.00 USD

Assets:US:Checking      1000.00 USD

Equity:Conversions:USD  1000.00 USD

Assets:FR:Checking

Equity:Conversions:EUR

Assets:CA:Checking      1303.90 CAD

Equity:Conversions:CAD -1303.90 CAD

bean-query currencies.beancount "select sum(position)"

-

In fact, any subset of the transactions would also sum to zero.

I hope this explains what I mean really clearly.

I should just code the silly plugin, frankly, it would take less than an hour.

Would I get a few beta testers?

It seems the PS method is compelling enough to stimulate a discussion.

[Martin Blais]

Here's yet another example, this one with a conversion loss and manually modified transactions with the trading accounts method:

plugin "beancount.plugins.auto"

2018-01-06 * "Initial deposit"

  Income:US:Salary -2000.00 USD

  Assets:US:Checking

2018-02-06 * "Convert to CAD"

  Assets:US:Checking      -1000.00 USD

  Assets:CA:Checking       1200.00 CAD

  Equity:Conversions:USD   1000.00 USD

  Equity:Conversions:CAD  -1200.00 CAD

2018-02-06 * "Convert later to USD with loss"

  Assets:CA:Checking      -1200.00 CAD

  Assets:US:Checking        950.00 USD

  Equity:Conversions:CAD   1200.00 CAD

  Equity:Conversions:USD   -950.00 USD

2018-09-01 query "bal" "select account, sum(position) order by account"

Which yields:

bean-query currency-profit.beancount run bal

       account         sum_position

---------------------- ------------

Assets:CA:Checking

Assets:US:Checking      1950.00 USD

Equity:Conversions:CAD

Equity:Conversions:USD    50.00 USD

Income:US:Salary       -2000.00 USD

Now at this stage I could "book" / extract some of that loss from the trading accounts to an income account to account for it as a loss (and perhaps declare it on my taxes and keep track of how much was declared in this way):

plugin "beancount.plugins.auto"

2018-01-06 * "Initial deposit"

  Income:US:Salary -2000.00 USD

  Assets:US:Checking

2018-02-06 * "Convert to CAD"

  Assets:US:Checking      -1000.00 USD

  Assets:CA:Checking       1200.00 CAD

  Equity:Conversions:USD   1000.00 USD

  Equity:Conversions:CAD  -1200.00 CAD

2018-02-06 * "Convert later to USD with loss"

  Assets:CA:Checking      -1200.00 CAD

  Assets:US:Checking        950.00 USD

  Equity:Conversions:CAD   1200.00 CAD

  Equity:Conversions:USD   -950.00 USD

2018-04-01 * "Realize currency conversion loss"

  Equity:Conversions:USD    -50.00 USD

  Income:US:CurrencyLosses   50.00 USD

2018-09-01 query "bal" "select account, sum(position) order by account"

(Please note that a positive number on an Income account is a loss; see signs for accounts / debits & credits. That's why 50.00 on the Income account, i.e, a loss of $50)

        account          sum_position

------------------------ ------------

Assets:CA:Checking

Assets:US:Checking        1950.00 USD

Equity:Conversions:CAD

Equity:Conversions:USD

Income:US:CurrencyLosses    50.00 USD

Income:US:Salary         -2000.00 USD

Note that I'm doing this with just USD and CAD here, but it would work with 3 or more currencies, doesn't matter. The trading accounts will record the sum total of currency conversions made over the filtered transactions.

[Metin Akat]

Hi,

This would solve one of the problems of multi-currency accounting and is probably a good idea, but does not try to address some of the "real" problems when doing personal accounting.

Here I'll try to explain what I had in mind in the other thread and also, point to some of the problems I experience often.

So consider this realistic situation that I deal with on quite a regular basis (the numbers are wrong, but illustrative):

2012-04-27 * "Exchange"

  Assets:Wallet   300 TRY @ 0.844 BGN

  Assets:Wallet  -253.20 BGN

2013-05-15 * "Exchange"

  Assets:Wallet    200 TRY @ 0.813 BGN

  Assets:Wallet   -162.6 BGN

Then spend some time in Turkey and have many transactions like this, during which time the price fluctuates:

2013-05-20 * "Dinner"

  Expenses:Food:Restaurants  50 TRY

  Assets:Wallet                         

Then go back, forget to convert the remainder for a year, then do, after I realize that the TRY continues to lose its value

2014-08-20 * "Exchange"

  Assets:Wallet    -163 TRY @ 0.601 BGN

  Assets:Wallet      97.96 BGN

Now, if we only want to track the loss of the remaining TRY, with the trading accounts, we can.

But we also want to plot our expenses (in our home currency) and here we have several problems:

1. For everyday expenses we want the report in BGN and the only correct way is to have it in "book value". I don't care what was the price of TRY at the moment of spending. I only care about how much it really cost me (which is the book value in BGN) to dine there. Currently, we can't do that (unless I use positions, but I don't want to go into that trouble). What I do is to make sure to at least have one price point per month so that it's relatively close to market value at time of spending (which is still wrong, but less wrong in my case)

2. During all this time that I hold the TRY, I want to see it in my asset reports both by book value and market value and of course, finally I will "realize" the gain/loss on conversion. Currently I don't have that as I don't use positions. And I don't think we can do this with trading accounts only. We need positions.

3. What happens when we move countries and now we want our reports in our new home currency (say EUR) ? Positions would be able to solve this again I think, while just trading accounts will not.

4. Of course ideal would be to be able to chose between FIFO, LIFO and Average cost basis.

I think the only way to do all of these is some form of automatic positions/lots generation on currency conversions. And of course, pointers to older transactions that generated these lots, so that we can walk backwards as far as needed, in order to get to the correct book value for reports.

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/3426155a-fdae-41d8-838d-90babf47efc2%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%2BhP7OTXDuQ%3Diw%3Da2FpyBhGn6TCRvURpc5nmVDF2tVnzerQ%40mail.gmail.com.

[Martin Blais]

On Sat, Jul 7, 2018 at 4:57 AM Metin Akat <akat....@gmail.com> wrote:

Hi,

This would solve one of the problems of multi-currency accounting and is probably a good idea, but does not try to address some of the "real" problems when doing personal accounting.

Here I'll try to explain what I had in mind in the other thread and also, point to some of the problems I experience often.

So consider this realistic situation that I deal with on quite a regular basis (the numbers are wrong, but illustrative):

2012-04-27 * "Exchange"

  Assets:Wallet   300 TRY @ 0.844 BGN

  Assets:Wallet  -253.20 BGN

2013-05-15 * "Exchange"

  Assets:Wallet    200 TRY @ 0.813 BGN

  Assets:Wallet   -162.6 BGN

Then spend some time in Turkey and have many transactions like this, during which time the price fluctuates:

2013-05-20 * "Dinner"

  Expenses:Food:Restaurants  50 TRY

  Assets:Wallet                         

Then go back, forget to convert the remainder for a year, then do, after I realize that the TRY continues to lose its value

2014-08-20 * "Exchange"

  Assets:Wallet    -163 TRY @ 0.601 BGN

  Assets:Wallet      97.96 BGN

Now, if we only want to track the loss of the remaining TRY, with the trading accounts, we can.

But we also want to plot our expenses (in our home currency) and here we have several problems:

1. For everyday expenses we want the report in BGN and the only correct way is to have it in "book value".

Don't call it "book value", that's only going to get more confusing. 

I think what you mean is you'd like to convert in the original rate of the particular units of BGN you exchange.

 

I don't care what was the price of TRY at the moment of spending. I only care about how much it really cost me (which is the book value in BGN) to dine there. Currently, we can't do that (unless I use positions, but I don't want to go into that trouble). What I do is to make sure to at least have one price point per month so that it's relatively close to market value at time of spending (which is still wrong, but less wrong in my case)

To fully automate this, you'd have to decide which of the TRY you're spending, e.g. from previous exchanges. 

That's way overkill.

A better way is to convert, as you do.

You could write a script to extract and insert exchange rates at every date you are spending TRY to get a more accurate conversion, and then report in BGN using CONVERT().

2. During all this time that I hold the TRY, I want to see it in my asset reports both by book value and market value and of course, finally I will "realize" the gain/loss on conversion. Currently I don't have that as I don't use positions. And I don't think we can do this with trading accounts only. We need positions.

It seems to me for the different values you can just use CONVERT() using the appropriate rate, either the latest rate (what you call "market value") or a rate at or close to that which was in effect at the time of the transaction (as in the previous paragraph above).  If the shell is missing functionality to do that, I'm happy to add it.

3. What happens when we move countries and now we want our reports in our new home currency (say EUR) ? Positions would be able to solve this again I think, while just trading accounts will not.

You use CONVERT() with a new currency, again, you don't need to track cost basis.

If you don't have the price points, you rerun your script to pull additional historical exchange rates for EUR.

4. Of course ideal would be to be able to chose between FIFO, LIFO and Average cost basis.

I don't think you need to track cost basis (what you call "positions"). You would only need that if you wanted to spend your converted currencies in a particular order, e.g. FIFO. This is overkill, nobody thinks of that in the real world ("oh, now I'm using those coins from that pocket I converted at X rate from my second exchange of currencies"). I understand the theoretical point you're making, but IMHO it's a contrived example and I'm not so keen in adding complexity to solve problems which aren't pragmatic, i.e., they don't solve a common problem most people care about.

If you really want that, you can use the {} syntax and the built-in FIFO booking to track the cost basis of your converted currencies. You could also write a plugin which does this for you automatically (it's possible to convert price conversions Postings to insert cost basis).

(Moreover, there's another problem: what if you made conversion FROM two different currencies? e.g. you have some TRY converted from BGN and some TRY converted from EUR. Now you spend TRY.... which ones do you use? It's not just a matter of ordering.)

I hope this helps,

 

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/3426155a-fdae-41d8-838d-90babf47efc2%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.

[Metin Akat]

Exactly. Your points are valid and please do not consider any of these as feature requests. I just wanted to explain what I called "impossible or at least infeasible to implement" in the other thread. Even with trading accounts, it won't be exact enough, so I don't even see the need for implementing trading accounts.

 

The current functionality is OK for personal use. Probably simplicity trumps "exactness" (sorry, not a native English speaker) in the field of personal accounting.

What I currently do (having one price point per month) is quite good IMO and serves my needs pretty well, even when I know that I'm off by several BGN each month in my reports.

Best,

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/3426155a-fdae-41d8-838d-90babf47efc2%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.

[Martin Blais]

On Sat, Jul 7, 2018 at 4:05 PM Metin Akat <akat....@gmail.com> wrote:

Exactly. Your points are valid and please do not consider any of these as feature requests. I just wanted to explain what I called "impossible or at least infeasible to implement" in the other thread. Even with trading accounts, it won't be exact enough, so I don't even see the need for implementing trading accounts.

 

The current functionality is OK for personal use. Probably simplicity trumps "exactness" (sorry, not a native English speaker) in the field of personal accounting.

Do you know of any commercial software that would support this level of precision for multi-currency?

If so, how do they solve the problem?

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/3426155a-fdae-41d8-838d-90babf47efc2%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.

[Metin Akat]

I have no idea, I have only ever done personal accounting. Initially with GnuCash, then Ledger, now Beancount.

But it's an interesting topic to think about :)

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/3426155a-fdae-41d8-838d-90babf47efc2%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.

[Dennis Golomazov]

Martin,

Thank you for great examples! Yes, they are very clear.

> I should just code the silly plugin, frankly, it would take less than an hour.

> Would I get a few beta testers?

Absolutely! Thank you!

One feature request for the plugin (I could help with implementing that too): ability to automatically move gains/losses from trading accounts to Income accounts periodically (maybe using a setting: daily/weekly/monthly/quarterly/yearly).

[Martin Blais]

Track this ticket for this issue:

https://bitbucket.org/blais/beancount/issues/318/implement-trading-accounts-as-a-plugin-if

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/4819b9ce-a322-44c7-b012-f67572e9fb39%40googlegroups.com.

[yangzh...@gmail.com]

The trading account method exactly solves my problem. May I ask the progress or plan about the plugin. That would be super useful.

[the.so...@gmail.com]

On Sunday, 8 July 2018 00:54:24 UTC+5:30, Martin Blais wrote:

To fully automate this, you'd have to decide which of the TRY you're spending, e.g. from previous exchanges. 

That's way overkill.

A better way is to convert, as you do.

You could write a script to extract and insert exchange rates at every date you are spending TRY to get a more accurate conversion, and then report in BGN using CONVERT().

This is why I see non-speculative currency transactions as the same as speculative currency transactions, with the main difference being that the former is, in practice, "average cost basis", rather than FIFO or LIFO.  If you exchange money twice and 2 different rates, and then spend that money, your mental model is that of average cost between the two exchange rates, and not FIFO/LIFO.

The justification given by Martin for having different notations for non-speculative currency conversions — @ — and for speculative investments (including for currency) — {} — is that the "mental model" for the former doesn't involve lots while the mental model for the latter does.

I beg to disagree. As this thread shows, people might not be thinking about "lots" while they are spending the converted currency, but they do want to know the cost in the base currency, which requires lots. So, the main difference, in my opinion, is that the lots involved in (non-speculative) currency conversions are average-cost basis in our mental models, while FIFO/ACB/LIFO is determined primary by securities/tax laws for other kinds of investments.

[Martin Blais]

The approach I used in designing Beancount's schema is to mirror the accounting which the institutions you're using do. And they clearly don't keep track of that.

A better method, if you want to keep all the basis, is to use currency trading accounts. Search this mailing list for info about a plug-in (slightly incomplete iirc) that does this for you. It's almost there.

If you really want to track all the lots for your currency conversions you could in theory make the booking method "NONE" on all your accounts but I'm pretty sure it's going to get annoying eventually and you're kind of on your own. Average booking isn't implemented. You should probably use Ledger (but then you don't have any lot matching and your file is order dependent).

-- Martin, on the cell phone with a bunch of suitcases while standing at Shinagawa station somewhere really really filled with people

--
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/f538987b-bc31-4b26-aa65-8bd85cc68dbd%40googlegroups.com.

[jonnyc...@gmail.com]

Dennis would you take a new role at present in San Fran?