Selinger's multiple currency accounting & ledger

[thierry]

Hello,

I played with multiple currency accounting. I had already read a long
time ago the article from Peter Selinger [1], but this time I
exercised with ledger. I used the Table 4.4 [2] as my example.

[1] http://www.mscs.dal.ca/~selinger/accounting/tutorial.html
[2] http://www.mscs.dal.ca/~selinger/accounting/tutorial.html#Table-4.4

I entered the table 4.4 using all ledger possible syntaxes I could
think about. You will find the full ledger text file at the end of
this post. The files differs mainly on one transaction, and just below
this transaction extracted from full text file. Please note that some
other lines makes the files differs.


### selinger_table_4_4-naive
; Naive, is actually my current way of doing it.
2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food

### selinger_table_4_4-balanced
; Balanced, from original USD into other currency
2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 52 CAD

### selinger_table_4_4-currency_trading_account
; Currency trading account, is method taught by Selinger.
2011/01/03 Buy food
Assets:US_Cash -40 USD
Income:Currency:USD 40 USD
Income:Currency:CAD -52 CAD
Expenses:Food 52 CAD

### selinger_table_4_4-fixated_commodity_price
; Fixated commodity price, is a ledger specific syntax.
2011/01/03 Buy food
Assets:US_Cash -40 USD {=1.3 CAD}
Expenses:Food

### selinger_table_4_4-pricing@
; pricing@, using one @ for actual currency rate
2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @ 1.3 CAD
Income:Currency 40 USD
Income:Currency -40 USD @ 1.3 CAD

### selinger_table_4_4-pricing@@
; pricing@@, @@ is useful when multiple posts
2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @@ 52 CAD
Income:Currency 40 USD
Income:Currency -40 USD @@ 52 CAD

### selinger_table_4_4-income_currency
; income currency, using empty Currency:Income, and @ rate.
2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @ 1.3 CAD
Income:Currency


My ranking would be:

1. "income_currency" looks to be the smartest implementation of
Selinger method using ledger. Pros are : it uses the currency trading
account philosophy, it balances to zero when --basis or when
--exchange=CAD, it is smart/lazy enough to avoid entering computations
(you have to enter the currency rate, but nothing to compute by hand),
it uses ledger ability to auto-balance in a multi-currency account
(the use of account Income:Currency) and here also nothing to compute
by hand, it allows computation of unrealized gain when
"--end=2011/01/04 --exchange=CAD balance" (taking into account the
line "P 2011/01/03 USD 1.30 CAD"). I found only one Cons : it does not
balance to zero when no option to bal, but when --basis or
--exchange=CAD is added then it balances to zero.

2. I would rank second "pricing@" and "pricing@@" as they are less
smart than first method. "pricing@@" is useful when multiple posts in
one transaction.

3. I would rank third "currency_trading_account". The cons of this
method is when using command "register Expenses:Food" the amount is in
CAD, so you lose the original amount in USD, and it is more difficult
to reconciliate.


Other findings/thoughts are:

4. All syntaxes balance to zero when --basis (this is expected)

5. Only "currency_trading_account", "pricing@", "pricing@@" balance to
zero (when no option is given to "ledger bal")

6. When "--exchange=CAD balance", "naive" and
"fixated_commodity_price" gives incorrect result (balance to 5 CAD
instead of expected 7 CAD)

7. When "--basis --exchange=CAD balance", "balanced" gives incorrect
result (Assets are 128 CAD and 135 CAD is expected)

8. I have not understood the way of working of
"fixated_commodity_price". I thought is was designed to solve some
issue of multi currency accounting by making the amount as final, but
I think I did not get the point...


What I learned when (re)reading Selinger article:

9. "The official solution: translation to a single currency" [3] "In
effect, this means that foreign currencies should be treated precisely
in the same way as non-monetary assets, i.e., they should be accounted
for in the local currency." My understanding is that I have to choose
one main currency (in my case my local currency). This means to me
that: 1) I use only my local currency for Expenses and Income, 2) for
Assets and Liabilities accounts I will use a single currency,
generally my local, but could be a foreign, 3) the account
"Income:Currency" is the only account that will be multi-currency.
This are the rules that I will impose to me. As as remark, this is
what imposes GnuCash to you. Support for multi-currency accounting, as
described by Selinger, was added in GnuCash 2.3.9.

[3] http://www.mscs.dal.ca/~selinger/accounting/tutorial.html#3.2

10. "The transaction is balanced in each currency separately". When
multiple currencies in one transaction, I have to balance each
currency separately. This is what is doing the Peter Selinger
methodology by using "Income:Currency:USD" and "Income:Currency:USD"
with an amount. This can be shortened with ledger by using
"Income:Currency" without amount.


Constructive feedback will be appreciated. I may have been mislead
somewhere, and I would like to know. Sorry for this long post, but I
wanted to share as I did not find something like that in mailing list
archive. I also wanted to choose and describe my solution for multiple
currency accounting and be able to find it back at a future point.


Thierry


Below are the files that I used:

### selinger_table_4_4-naive
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-balanced
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 52 CAD

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-currency_trading_account
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Income:Currency:CAD 120 CAD
Income:Currency:USD -100 USD
Assets:US_Cash 100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Income:Currency:USD 40 USD
Income:Currency:CAD -52 CAD
Expenses:Food 52 CAD

2011/01/05 Exchange
Assets:US_Cash -60 USD
Income:Currency:USD 60 USD
Income:Currency:CAD -75 CAD
Assets:Canadian_Cash 75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-fixated_commodity_price
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD {=1.3 CAD}
Expenses:Food

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-pricing@
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD
Income:Currency 120 CAD
Income:Currency -100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @ 1.3 CAD
Income:Currency 40 USD
Income:Currency -40 USD @ 1.3 CAD

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD
Income:Currency 60 USD
Income:Currency -75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-pricing@@
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD
Income:Currency 120 CAD
Income:Currency -100 USD

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @@ 52 CAD
Income:Currency 40 USD
Income:Currency -40 USD @@ 52 CAD

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD
Income:Currency 60 USD
Income:Currency -75 CAD

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

### selinger_table_4_4-income_currency
2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD
Income:Currency

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @ 1.3 CAD
Income:Currency

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD
Income:Currency

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

[Peter Keen]

Oh a recent trip out of the country I kept a secondary ledger in CAD. When I returned, I wrote a small perl script that went over the file line by line and did the currency conversion based on the date of the transaction. 

This was problematic mostly because some transactions posted to my checking account immediately (ATM transactions), whereas most everything else posted a few days later and used the later date's conversion rate. Also, some transactions didn't post at all (cash transactions). I decided to just manually fix up transactions that didn't match the corresponding bank statement entry. My ledger is thus still completely in USD.

This, of course, would never work in on a longer timeline. One week's worth of vacation expenses is still less than 50 transactions, all told, so it was easy to hand-verify.

[thierry]

Well, after modifying two thousands transactions in my personal ledger
file, I realized that I got it wrong and that I had not fully
understood the way ledger handles multiple currency accounting.

I used the following syntax, that I mistakenly believed the smartest.

2011/01/03 Buy food
Assets:US_Cash -40 USD
Expenses:Food 40 USD @ 1.3 CAD
Income:Currency

With 2000 transactions like that in my personal ledger, the problem is
that I accumulated a lot of "XX USD @ YY CAD". When accumulating
several thousands of USD, a very slight change in USD/CAD currency
rate make a huge change in CAD total.

As an example, let's add the following line in my test files named
"selinger_table_4_4-*".

P 2011/01/08 USD 1.45 CAD

When doing "ledger bal", only 3 files give a zero balance:
- selinger_table_4_4-currency_trading_account
- selinger_table_4_4-pricing@
- selinger_table_4_4-pricing@@

When doing "ledger -X CAD bal", only 2 give a correct result:
- selinger_table_4_4-balanced
- selinger_table_4_4-currency_trading_account

Only "selinger_table_4_4-currency_trading_account" looks to be the
solution. I remind the syntax used:

2011/01/03 Buy food
Assets:US_Cash -40 USD
Income:Currency:USD 40 USD
Income:Currency:CAD -52 CAD
Expenses:Food 52 CAD

While correct, I do not like this syntax, because I have to manually
compute the CAD amount (52 CAD equals 40 USD @ 1.3 CAD), and also when
"ledger reg Food", I lost the fact that it was a 40 USD spending.

After some thinking, I finally found the trick, by using the following
syntax:

2011/01/03 Buy food
Assets:US_Cash -40 USD

Expenses:Food 40 USD {= 1.3 CAD}
Income:Currency

This gives me satisfaction: "bal", "bal --basis", "bal -X CAD" all
gives correct results, even when using --end with dates 2011/01/04,
2011/01/07, and 2011/01/08.

In my previous post, I disregarded "fixated commodity price" because
it gives weird results. Actually, it gives correct result when
balanced with an empty "Income:Currency". But if used without an empty
"Income:Currency", it gives strange results.

As a summary, here is file syntax:

-----------8<-----------8<-----------8<-----------

2011/01/01 Opening balance
Assets:Canadian_Cash 200 CAD
Equity:Initial_Capital

2011/01/02 Exchange
Assets:Canadian_Cash -120 CAD
Assets:US_Cash 100 USD
Income:Currency

P 2011/01/03 USD 1.30 CAD

2011/01/03 Buy food
Assets:US_Cash -40 USD

Expenses:Food 40 USD {= 1.3 CAD}

Income:Currency

2011/01/05 Exchange
Assets:US_Cash -60 USD
Assets:Canadian_Cash 75 CAD
Income:Currency

2011/01/07 Buy food
Assets:Canadian_Cash -20 CAD
Expenses:Food

P 2011/01/08 USD 1.45 CAD
-----------8<-----------8<-----------8<-----------