How to set the opening balance in a foreign currency account?

[J. B. Rainsberger]

Hi, folks. I had hoped this would be straightforward, but evidently not enough for me so far. I need your help.

My base currency is CAD and I have a bank account (call it "USD Bank") denominated in USD. My last year-end balance sheet has a CAD balance of "USD Bank" as CAD 815.57 and the bank statement has the USD balance of "USD Bank" as USD 835.44. Our convention is to use the monthly average foreign currency exchange rate from Bank of Canada when converting foreign currency transactions to CAD. Naturally, the CAD equivalent "balance" on this account has drifted from the amount you would get by converting the current USD balance into CAD.

Now I'm trying to start tracking this account with hledger. Here's what I have:

- P directives for the monthly average forex rates for each month of 2023 between USD and CAD

- an opening balance entry for "USD Bank" as 835.44 USD @@ 815.57 CAD

- I'm using --value="then,CAD" when I run the hledger tools

When I do this, the opening balance of "USD Bank" shows as 1144.55 CAD, which I infer is the correct conversion using the "--value=then,CAD" switch, but I need this opening balance to be 835.44 USD valued at 815.57 CAD in order to match the balance sheet moving forward.

What am I missing? What am I getting wrong? Do I have a fundamental problem in my mental model wanting to do this and use "--value=then,CAD"? How would I make it work to be able to use "--value=then" (which is convenient for other reasons) with an opening balance whose amounts don't match the then-in-use forex rate?

Many thanks,

--

@jbrains

[Simon Michael]

Hi J.B. I for one have trouble visualising that, perhaps you can share a minimal example we can tinker with ?

--
You received this message because you are subscribed to the Google Groups "hledger" group.
To unsubscribe from this group and stop receiving emails from it, send an email to hledger+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/hledger/173db6ed-fc56-47dd-b245-335310f87b89n%40googlegroups.com.

[J. B. Rainsberger]

Hello! I am finally back to this. Here is a journal file with some explanatory comments.

----- begin journal -----

# Base currency is CAD; USD is a foreign currency
commodity 1,000,000.00 CAD
commodity 1,000,000.00 USD

# Monthly average exchange rates, which is our accounting standard
P 2023-12-01  USD  1.20 CAD
P 2024-01-01  USD  1.30 CAD
P 2024-02-01  USD  1.40 CAD
P 2024-03-01  USD  1.50 CAD

account  Assets:Bank:USD Chequing
account  Revenue:Uncategorized Revenue

# The balance for USD Chequing on the accountants' balance sheet
# reflects our accounting standard of converting each transaction
# into CAD (base currency) at the monthly average exchange rate
# in effect. This causes the CAD balance to drift out of control
# away from the value computed by converting the USD balance to
# CAD at the monthly average exchange rate in effect during the
# month.
2023-12-31  Opening Balances as taken from accountants' balance sheet
    Assets:Bank:USD Chequing    500.00 USD @@ 538.71 CAD    ; "would" be 600.00 CAD if we used the spot rate in effect on this date
    Equity

2024-01-02  Received USD
    Assets:Bank:USD Chequing    1000.00 USD    ; converted to 1300.00 CAD
    Revenue:Uncategorized Revenue

# Our accountants' methods puts the balance of USD Chequing at
# 1838.71 CAD, whereas hledger with "--value=then,CAD" computes
# the balance as 1900.00 CAD = (500.00 USD * 1.20 CAD + 1300.00 CAD).

----- end journal -----

In short, how can I achieve what I'm trying to achieve with hledger? 

I've been using the switch `--value="then,CAD"` to apply the currency conversion rules that my accountants and the tax authority demand (recommend? suggest? I'm not sure.). Although this matches my accountants' methods for Income Statement accounts, it reports CAD equivalent balances differently (based on spot conversion) that my accountants' methods (sum of individual converted amounts).

Many thanks,

@jbrains

[Simon Michael]

Hi.. it seems like there's some disagreement in your conversion methods, ie whether to value things at cost or at market value (on some date).

And, I randomly tried a report that with both -B and --value and it seems to produce the right number ?

$ hledger -f 2024-02-03.j bal USD -B --value=then

        1,838.71 CAD  Assets:Bank:USD Chequing

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

        1,838.71 CAD

To view this discussion on the web visit https://groups.google.com/d/msgid/hledger/6c391082-3cd3-468e-ad57-960a42dc6bf9n%40googlegroups.com.

[J. B. Rainsberger]

Thank you. I figured it was something like that. I didn't know what I was doing, so I was merely groping for something that might threaten to work. Now I begin to understand one more helpful element: "-B".

--

J. B. (Joe) Rainsberger :: tdd.training :: jbrains.ca ::
blog.thecodewhisperer.com

Replies from this account routinely take a few days, which allows me to reply thoughtfully. I reply more quickly to messages that clearly require answers urgently. If you need something from me and are on a deadline, then let me know how soon you need a reply so that I can better help you to get what you need. Thank you for your consideration.