Multicurrency and reg problem

[jleija]

Hi,

I noticed a weird behaviour when doing a 'reg' on a multi-currency
file (Mexican peso, Guatemalan quetzal and usd). I'm using ledger v.
2.6.2 in Ubuntu linux, 64 bit. Here is my command:

ledger -w -f multicurrency.ledger -V reg payable:jlopez

Here is my output:

2008/05/29 jlopez airfare to Guatemala
co:liabilities:payable:jlopez:cash
$-133.82 $-133.82
2008/05/30 jlopez dinner in Churrasco Centro..
co:liabilities:payable:jlopez:cash
$-50.43 $-184.25
2008/05/31 jlopez dinner in Churrasco Centro..
co:liabilities:payable:jlopez:cash
$-138.44 $-322.69
2008/06/09 Check 1004 - travel expenses reim..
co:liabilities:payable:jlopez:cash
$335.00 $12.31
2008/07/03 jlopez airfare to Guadalajara, Me..
co:liabilities:payable:jlopez:cash
$-347.26 $-334.95
2008/07/06 jlopez meal with prospective clie..
co:liabilities:payable:jlopez:cash
$-112.55 $-447.49
2009/07/06 Check 1012
co:liabilities:payable:jlopez:cash
$614.99 $167.50
2009/07/13 jlopez meal at Bistro Cheney, Mex..
co:liabilities:payable:jlopez:cash
$-27.90 $130.40
2009/07/15 Car Gas
co:liabilities:payable:jlopez:cash
$-30.00 $100.40
2009/07/20 Car Gas
co:liabilities:payable:jlopez:cash
$-8.22 $225.57
2009/07/20 jlopez parking in Mexico, DF
co:liabilities:payable:jlopez:cash
$-1.49 $224.07

The problem is in the second-to-the-last line where the balance goes
from 100.40 to 225.57 with an expense of 8.22 (225.57 instead of
92.18).

Here is my simplified multicurrency.ledger file (105 lines):

;begin of file
----------------------------------------------------------------
;jlopez business trip to Guatemala to visit Grupo Azteca and Dr. Lara
{{{
;with 7.0 qzl per 1 dollar:
P 2008/05/29 qzl $0.142857142857143

2008/05/29 * jlopez airfare to Guatemala
jlopez:assets:reimbursables:plankor
$133.82
jlopez:assets:checking
$-133.82
company:expenses:travel:airfare:jlopez
$133.82
company:liabilities:payable:jlopez:cash
$-133.82
; receipts available
2008/05/30 * jlopez dinner in Churrasco Centroamericano, SA.,
Guatemala
jlopez:assets:reimbursables:plankor
353.00 qzl
jlopez:assets:checking
-353.00 qzl
company:expenses:travel:meals:jlopez
353.00 qzl
company:liabilities:payable:jlopez:cash
-353.00 qzl
; receipts available
2008/05/31 * jlopez dinner in Churrasco Centroamericano, SA.,
Guatemala
jlopez:assets:reimbursables:plankor
969.10 qzl
jlopez:assets:checking
-969.10 qzl
company:expenses:travel:meals:jlopez
969.10 qzl
company:liabilities:payable:jlopez:cash
-969.10 qzl
; receipts available
2008/06/09 * Check 1004 - travel expenses reimbursement to jlopez
jlopez:assets:checking
$335.00
jlopez:assets:reimbursables:plankor
$-335.00
company:liabilities:payable:jlopez:cash
$335.00
company:assets:accounts:checking:bofa
$-335.00
; receipts available
;; bofa-check transaction type: withdraw
;; bofa-check md5: 84ee2140132d30a1567f09fc20a29a0b
;}}}

;July

;jlopez trip to Guadalajara to present product to CIAJ and Confitexpo
{{{
; 10.20 mxn = $1
P 2008/07/03 mxn $0.0980392156862745

2008/07/03 * jlopez airfare to Guadalajara, Mexico (round trip)
jlopez:assets:reimbursables:plankor
3542.01 mxn
jlopez:assets:checking
-3542.01 mxn
company:expenses:travel:airfare:jlopez
3542.01 mxn
company:liabilities:payable:jlopez:cash
-3542.01 mxn
; receipts available
2008/07/06 * jlopez meal with prospective clients at "La Campana Roja,
S de RL de CV", Guadalajara, Mexico
jlopez:assets:reimbursables:plankor
1148.00 mxn
jlopez:assets:checking
-1148.00 mxn
company:expenses:travel:meals:jlopez
1148.00 mxn
company:liabilities:payable:jlopez:cash
-1148.00 mxn
; receipts available

;}}}

;==============================================================================
;2009

;jlopez trip to Mexico City {{{
; $1 usd = 13.5758, from www.sat.gob.mx
2009/07/06 * Check 1012
jlopez:assets:checking
$614.99
jlopez:assets:reimbursables:plankor
$-614.99
company:liabilities:payable:jlopez:cash
$614.99
company:assets:accounts:checking:bofa
$-614.99
;; bofa-check transaction type: withdraw
;; bofa-check md5: bfba1cb366010b18d96ce0bec736559e

; $1 usd = 13.2724, from www.sat.gob.mx
; changed during testing
P 2009/07/08 mxn $0.1

2009/07/13 * jlopez meal at Bistro Cheney, Mexico, DF
jlopez:assets:reimbursables:plankor
279.00 mxn
jlopez:assets:checking
-279.00 mxn
company:expenses:travel:meals:jlopez
279.00 mxn
company:liabilities:payable:jlopez:cash
-279.00 mxn
; receipt available
2009/07/14 * STARBUCKS TLACOQUEMEC
company:expenses:travel:meals:jlopez
$7.25
company:liabilities:debt:creditcard:bofa:jlopez
;; bofa-visa post date: 2009/07/16
;; bofa-visa transaction type: withdraw
;; bofa-visa md5: 54bdac1bf829ce86d9188e8a22c87388
2009/07/15 * Car Gas
jlopez:assets:reimbursables:plankor
300.00 mxn
jlopez:assets:checking
-300.00 mxn
company:expenses:travel:car:gas:jlopez
300.00 mxn
company:liabilities:payable:jlopez:cash
-300.00 mxn
;receipt available

; $1 usd = 13.3895, from www.sat.gob.mx

P 2009/07/20 mxn $0.074685387

2009/07/20 * Car Gas
jlopez:assets:reimbursables:plankor
110.00 mxn
jlopez:assets:checking
-110.00 mxn
company:expenses:travel:car:gas:jlopez
110.00 mxn
company:liabilities:payable:jlopez:cash
-110.00 mxn
;receipt available
2009/07/20 * jlopez parking in Mexico, DF
jlopez:assets:reimbursables:plankor
20.00 mxn
jlopez:assets:checking
-20.00 mxn
company:expenses:travel:parking:jlopez
20.00 mxn
company:liabilities:payable:jlopez:cash
-20.00 mxn
; receipt available

;end of file ------------------------------------------------------

Thanks,

Javier

[John Wiegley]

On Nov 23, 2009, at 4:56 PM, jleija wrote:

> I noticed a weird behaviour when doing a 'reg' on a multi-currency
> file (Mexican peso, Guatemalan quetzal and usd). I'm using ledger v.
> 2.6.2 in Ubuntu linux, 64 bit. Here is my command:

Ledger 2.6.2 does not alert you when commodities have been revalued behind the scenes. If you did this with Ledger 3 (current 'master'), you'd see a posting with the account "<Revalued>":

08-May-29 jlopez airfare t.. co:li:pa:jl:cash $-133.82 $-133.82
08-May-30 jlopez dinner in.. co:li:pa:jl:cash $-50.43 $-184.25
08-May-31 jlopez dinner in.. co:li:pa:jl:cash $-138.44 $-322.69
08-Jun-09 Check 1004 - tra.. co:li:pa:jl:cash $335.00 $12.31
08-Jul-03 jlopez airfare t.. co:li:pa:jl:cash $-347.26 $-334.95
08-Jul-06 jlopez meal with.. co:li:pa:jl:cash $-112.55 $-447.50
09-Jul-06 Check 1012 co:li:pa:jl:cash $614.99 $167.49
09-Jul-13 Commodity rounding <Rounding> $0.01 $167.50
09-Jul-13 Commodities reva.. <Revalued> $-9.20 $158.30
09-Jul-13 jlopez meal at B.. co:li:pa:jl:cash $-27.90 $130.40
09-Jul-15 Car Gas co:li:pa:jl:cash $-30.00 $100.40
09-Jul-20 Commodities reva.. <Revalued> $133.38 $233.78
09-Jul-20 Commodity rounding <Rounding> $0.01 $233.79
09-Jul-20 Car Gas co:li:pa:jl:cash $-8.22 $225.57
09-Jul-20 Commodity rounding <Rounding> $-0.01 $225.56
09-Jul-20 jlopez parking i.. co:li:pa:jl:cash $-1.49 $224.07

You can see the $133.38 there, which happened because of this line:

P 2009/07/20 mxn $0.074685387

You see, if you run the report without -V, you see this on 7/15:

09-Jul-15 Car Gas co:li:pa:jl:cash -300.00 mxn $816.17
-5269.01 mxn
-1322.10 qzl

That's a pretty large negative balance of mxn. So, even small price fluctuations are going to cause noticeable changes in the overall value. In this case, because mxn gets a fair bit cheaper on 7/20 ($0.30 per), it means your loss is much less, hence the increase in overall dollar value of the account.

John

[jleija]

Thanks John. Thanks for the quick answer.

I misunderstood the tool, well I actually misunderstood commodities
for that matter. :) I'm a noob in finances/accounting.

In our case (my little company) we have many cash expenses in foreign
currencies that we reimburse in dollars. The amount of reimbursement
should use the exchange rate at the time of the expenditure, and not
be re-adjusted, as commodities are, to calculate present value. This
page describes the "official" process we try to follow:
http://www.mscs.dal.ca/~selinger/accounting/.

Through the IRC I learned that I can use @ with each foreign amount to
immediately convert it to $ (usd) amount. If I do this for all my
transactions and then I report with '-B reg', I get what I wanted. So
this works but I have many cash transactions and now they are more
verbose:

2009/07/20 * Car Gas
jlopez:assets:reimbursables:plankor

110.00 mxn @ $0.074685387
jlopez:assets:checking
-110.00 mxn @ $0.074685387

company:expenses:travel:car:gas:jlopez

110.00 mxn @ $0.074685387

company:liabilities:payable:jlopez:cash

-110.00 mxn @ $0.074685387
;receipt available

With the P directive I wanted to group a bunch of transactions from
the same day, or even multiple days (with very similar exchange
rates). Also, when I use the @ I loose the balance in actual
currencies and I get it all in $ (I don't really know how valuable
those reports would be with their original currencies, it just feels
bad that I'm loosing information that is otherwise available.)

We spent some time discussing options at the IRC and these possible
solutions came up (in random order):

* one-currency accounts (or one-commodity accounts): where the account
itself converts all amounts to its declared amount, say $. ie:
"declare payabe:jlopez:cash $" would force conversion from mxn into $
using the latest P rate. So this account is always in dollars
calculated at the time of the transaction. (sm idea)
* Fixed-and-dynamic commodities. For example, if a commodity
conversion is given as 'F date $ 14 mxn' then mxn is a fixed commodity
and is to be treated as an immediate-conversion commodity (at
transaction time, using the F table, similar to P), as opposed to
being calculated at present value for a gain/loss. A dynamic commodity
would be the same as current -V behaviour. (Demosthenex idea)
* after-datefield conversion rate. Same as @ but written only once,
after the date in the transaction (for example), instead of writing it
for every amount in the transaction (4 times in my case). This assumes
all amounts are in same currency, of course. With this option I still
need to repeat the rate for each transaction but at least I type it
once, instead of 4 times. Example:
2009/07/20 {mxn @ $0.074685387} * Car Gas

jlopez:assets:reimbursables:plankor
110.00

jlopez:assets:checking
-110.00

company:expenses:travel:car:gas:jlopez
110.00

company:liabilities:payable:jlopez:cash
-110.00

;receipt available

I think the need/requirement has merit but I'm just learning these
finance/accounting things. So I really can't tell. I wonder how other
people report international expenses.

I'm an experienced C++ programmer and might be able to put some time
into this if you need my help.

Please consider these thoughts and advise.

Thanks,

Javier Leija

[John Wiegley]

On Nov 24, 2009, at 7:22 PM, jleija wrote:

> With the P directive I wanted to group a bunch of transactions from
> the same day, or even multiple days (with very similar exchange
> rates). Also, when I use the @ I loose the balance in actual
> currencies and I get it all in $ (I don't really know how valuable
> those reports would be with their original currencies, it just feels
> bad that I'm loosing information that is otherwise available.)

I actually discussed a similar problem on IRC at least with Demosthenes, and we came up with a fairly neat solution: a "fixedrate" scoped directive, which lets you fix the cost of a certain commodity within a given span of your data file. Then, when you use -V (or -X), it displays the value of those commodities based on the Ledger data, and not based on the current market value.

This is a fairly easy change to make, I expect it will be available to you sometime next week.

Btw:

100 mxn @ $0.075

This is slight different from saying:

100 mxn {=$0.075}

In the former case, it means you are buying/selling mxn on the given day for $0.075, but future value reports will value the currency based on current market value.

In the latter case, it means you are dealing in a fixed-rate commodity whose value is always reported to you in those terms, independent from current market value. This is what the fixedrate directive will apply within its range of effect:

fixedrate mxn $0.075

... 1000 mnx

end fixedrate

Then you needn't repeat the fixed-rate commodity pricing.

Would this solve your issue?

John

[jleija]

Yes, it does solve it.

I just tried the {} with -V and it works as I need it for expenses
reporting. The fixedrate directive syntactic-sugar is sweet because it
allows me to avoid all that duplicated typing (the source of my
pain).

I guess I need to be careful to place the regions around the correct
dates but this is just fine with me because I keep my ledger in
chronological order anyway. I also keep the P prices in the same file,
so this solution plays well with that too.

Thank you very much for considering this request and being responsive.
This is just the kind of stuff one can't get out of commercial
solutions, where GUI gizmos are more important than a good/correct
model of the problem.

Javier

[John Wiegley]

Hi Javier, the "fixed" directive is now in the 'next' branch.

John

[John Wiegley]

On Nov 25, 2009, at 9:51 AM, jleija wrote:

> I just tried the {} with -V and it works as I need it for expenses
> reporting. The fixedrate directive syntactic-sugar is sweet because it
> allows me to avoid all that duplicated typing (the source of my
> pain).

I forgot to mention how to use it:

fixed mxn $0.07

.... transactions involving any un-annotated mxn commodities will
be regarded as if {=$0.07} had been specified ....

end fixed

Also, this will appear on the master branch in just a moment, as it passed full tests last night.

John

[John Wiegley]

On Nov 30, 2009, at 10:45 AM, jleija wrote:

> Note that in the July 15th transaction the converted amount is -29.41,
> when it should had been -30.00 because its enclosing "fixed mxn"
> region sets mxn to $0.1. The -29.41 comes out of multiplying -300.00
> mxn by 0.0980392156862745, which is the rate for the previous fixed
> region.

This has finally been fixed. It was a bit involved, but quite important.

John

[Javier Leija]

ha! Thank you John for following up on this issue. This is going to make my life easier. :)

I am going to be trying this out this week with some more foreign expenses.

Regards,

Javier Leija