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. On Wed, Aug 3, 2011 at 3:53 PM, thierry <[email protected]> wrote: > 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 >
