Foreign exchange, "print", and rounding problems

[J. B. Rainsberger]

Hi, folks. I'm delving into preparing EU VAT returns using hledger and I've run into a defect. Please suggest how to handle this situation.

Summary: When I use the "print" command to extract EU VAT-related transactions from the journal _and_ convert values to EUR _then_ it's possible for the resulting transaction to be unbalanced due to a rounding error.

For now, I'm hand-editing the extracted journal in order to remove the offending amount and let the transaction balance itself; however, I'd like to avoid human intervention _and_ I'd prefer to solve this problem with hledger and not rely on brute-force regex matching, since the latter will tend to be more brittle.

For my purposes, it really only matters to be able to calculate the total Sales in EUR of all the transactions that have a non-zero EU VAT amount. I know that I can do that in a variety of ways, but perhaps the wisdom of the crowd can work in my favor here. :)

Any suggestion is welcome. Thank you.

@jbrains

---- begin sample ledger -----

D  1,000,000,000.00 CAD    
commodity  1,000,000,000.00 CAD
commodity  1,000,000,000.00 EUR

P 2023-07-01  EUR  1.4227 CAD

2023-09-08  This transaction leads to a rounding error when converted to EUR at the effective rate.
    Revenue:Sales  -199.0 CAD
    Liabilities:EU VAT  -41.79 CAD
    Assets:Bank

---- end sample ledger ----

This command produces a defective journal:

$ hledger --file forex-defect.journal --value=then,EUR print

---- begin defective output ----

2023-09-08 This transaction leads to a rounding error when converted to EUR at the effective rate.
    Revenue:Sales          -139.87 EUR
    Liabilities:EU VAT      -29.37 EUR
    Assets:Bank             169.25 EUR

---- end defective output ----

[J. B. Rainsberger]

So far, to work around this, I've processed the interim/extracted journal using a regex to remove the amount from the Assets line. I don't hate it, but I don't love it. I would prefer a solution that tells hledger not to output an amount for that line.

[Simon Michael]

I see what you mean. https://hledger.org/dev/hledger.html#print alludes to this (sort of.. "There are some situations where print's output can become unparseable..").

By default, print preserves the implicitness of the last amount; but with any cost or valuation flag, it makes all amounts explicit. This had some advantage I have forgotten.

An extra amountless "expenses:rounding adjustment" posting would allow such transactions to balance. There's an open PR for a bucket directive which could possibly automate that.

But may I ask, do you truly needi to re-parse valued print output ? Rather than running a valued register or balance report ?

-- 
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/258dca88-d0c5-4efa-a245-27706f1cb6a8n%40googlegroups.com.

[J. B. Rainsberger]

On Thursday, 5 October 2023 at 01:19:22 UTC-7 Simon Michael (sm) wrote:

I see what you mean. https://hledger.org/dev/hledger.html#print alludes to this (sort of.. "There are some situations where print's output can become unparseable..").

Thank you for the pointer.

 

By default, print preserves the implicitness of the last amount; but with any cost or valuation flag, it makes all amounts explicit. This had some advantage I have forgotten.

I presume it ensures correctness more generally: when converting all those amounts, it's probably safer and better to alert us to the rounding errors so that we can decide what to do about them, rather than silently decide for us and perhaps create downstream problems.

 

An extra amountless "expenses:rounding adjustment" posting would allow such transactions to balance. There's an open PR for a bucket directive which could possibly automate that.

I suppose that is even safer than what I'm doing now, so at worst I can automate that. The spurious EUR 0.00 transactions wouldn't bother me for this purpose.

But may I ask, do you truly needi to re-parse valued print output ? Rather than running a valued register or balance report ?

I'm not sure. Maybe I will learn that I could do this another way. Here's what I'm doing: I can easily get a balance report to show the EU VAT Payable amount, but then I need to compute the total sales on all the transactions that had a non-zero EU VAT Payable amount. I couldn't figure out how to compute that with some kind of filter on a balance report. How would you approach this?

Thank you!

@jbrains

[Simon Michael]

On Oct 5, 2023, at 15:50, J. B. Rainsberger <m...@jbrains.ca> wrote:

Here's what I'm doing: I can easily get a balance report to show the EU VAT Payable amount, but then I need to compute the total sales on all the transactions that had a non-zero EU VAT Payable amount. I couldn't figure out how to compute that with some kind of filter on a balance report. How would you approach this?

That's a good question. We have added the more expressive "expr:" queries recently, but they don't give as much extra power as you'd think; eg they don't let us select transactions by the amount posted to a particular account (I think). I came up with this:

hledger print "EU VAT" | hledger -f- -I bal revenue:sales

which works if every transaction posting to EU VAT is a sale with non-zero VAT. And then to see this in EUR, you can do the conversion in the second command, but since print doesn't print P directives we must provide them. Eg:

... --value=then,EUR -f prices.journal   # or -f <(echo "P 2023-07-01  EUR  1.4227 CAD")

[Simon Michael]

A full command line, and another way to pass along the prices:

hledger print "EU VAT" | hledger -f <(grep ^P $LEDGER_FILE) -f- -I bal revenue:sales --value=then,EUR

[J. B. Rainsberger]

On Thu, Oct 5, 2023 at 8:27 AM Simon Michael <si...@joyful.com> wrote:

 

That's a good question. We have added the more expressive "expr:" queries recently, but they don't give as much extra power as you'd think; eg they don't let us select transactions by the amount posted to a particular account (I think). I came up with this:

hledger print "EU VAT" | hledger -f- -I bal revenue:sales

which works if every transaction posting to EU VAT is a sale with non-zero VAT. And then to see this in EUR, you can do the conversion in the second command, but since print doesn't print P directives we must provide them. Eg:

... --value=then,EUR -f prices.journal   # or -f <(echo "P 2023-07-01  EUR  1.4227 CAD")

This is perhaps where I got stuck: I insisted on converting currency in the first step, when I could do it at the last step. Sadly, this makes the penny rounding problem a silent failure, which maybe I don't mind and maybe I do. I'm not sure yet whether I need to worry about it.

The next step is to do the same thing for each EU VAT country. I will figure out how to use output from `hledger accounts` to do that.

As for the forex prices, I have already isolated them to separate files, since EU VAT, UK VAT, and Canadian income tax all use different rules. :P I already have the habit of including the forex rules I need with explicit `--file` options, in order to make it clear which rules I'm using and that they're the appropriate ones for my needs at the moment.

Very nice! Thank you for the help.

--

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.

[Simon Michael]

Excellent! Thanks for the update.