Post by thinksync » Tue Feb 25, 2020 6:16 am

Hi there,

I help run a store shipping physical products from the UK to customers all over the world. We've been operating in some version of OpenCart since around 2012, and have recently migrated to v3.0.3.2. The base currency is GBP, but prices are displayed in the user's local currency and we take payments in the user's local currency - about 25 currencies in total. We also charge VAT and shipping where appropriate.

We've run into an odd problem since the migration, that I don't recall encountering before:

OC seems to be doing all its calculation for multiples of unit prices on line items in the base currency, then converting that to the foreign currency.

I've attached an example.

24.45 * 3 is 73.35, not 73.34
58.68 * 2 is 117.36 not 117.35

And then the subtotal should be 190.69 not 190.70

This only seems to happen on foreign currency transactions, so it looks to me like everything gets individually calculated in the base currency and then converted, rather than converted at the unit price, then calculated in the foreign currency - which seems a really strange way to go about it.

Has anyone figured out a way to address this?

Attachments

line-items.jpg

line-items.jpg (65.93 KiB) Viewed 747 times


Newbie

Posts

Joined
Fri Jul 26, 2013 5:05 am

Post by letxobnav » Tue Feb 25, 2020 8:38 am

OC does all calculations in the default/shop currency and does the conversion when formatting which causes rounding issues in some cases.
That is a problem but that is also the case in version 2 and probably 1.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by thinksync » Tue Feb 25, 2020 8:52 am

It's only really a problem because we sync our sales to Xero, and Xero spits back an error if the calcs are wrong.

We've had to introduce an adjustment line item to deal with it, which we didn't previously.

I suspect our previous developer who created a bespoke Xero integration re-did the calcs in the foreign currency before submitting.

So no plugins that fix this?

Newbie

Posts

Joined
Fri Jul 26, 2013 5:05 am

Post by IP_CAM » Tue Feb 25, 2020 9:10 am

Talking about single cents, what a World, we live in ... ::) :laugh:
https://www.opencart.com/index.php?rout ... arch=round

Please don't send me OC Forum Personal Messages, just contact: jti@jacob.ch
---
OC 1.5.6.5 LIGHT Test Site: http://www.bigmax.ch/shop/
OC 1.5.6.5 V-PRO Test Site: http://www.openshop.li/shop/
My Github OC Site: https://github.com/IP-CAM
2'600+ FREE OC Extensions on the World's largest Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by letxobnav » Tue Feb 25, 2020 9:19 am

well, no.
These mixing of conversions and formatting is everywhere so to fix it system-wide you would have to change all $this->currency->format statements along with doing the convert before any calculation on prices, things like in the cart:

instead of
$total = $this->currency->format($unit_price * $product['quantity'], $this->session->data['currency']);

you would have something like
// convert unit price
$conv_unit_price = $this->currency->convert($unit_price, YOUR_SHOP_CURRENCY, $this->session->data['currency']);
// format converted unit price * quantity without conversion
$total = $this->currency->format($conv_unit_price*$product['quantity'], $this->session->data['currency'],1);

but that is all over the place, even the fetching of prices from the database is done multiple times so it can be done but it's a lot of work and most of it would be in testing everything.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by thinksync » Tue Feb 25, 2020 9:24 am

IP_CAM wrote:
Tue Feb 25, 2020 9:10 am
Talking about single cents, what a World, we live in ... ::) :laugh:
https://www.opencart.com/index.php?rout ... arch=round
It's not about the single cents, it's about the opportunity cost lost in fixing it.

We're working on near-complete automaton of the business owner's processes. His strengths are in marketing and product development. Why should he waste his time sorting out dodgy account reconciliations for rounding that shouldn't occur in the first place?

Newbie

Posts

Joined
Fri Jul 26, 2013 5:05 am

Post by letxobnav » Tue Feb 25, 2020 9:38 am

or you could make sure that the Xero integration adopts/adheres/takes into account to the way OC does it.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by thinksync » Tue Feb 25, 2020 9:46 am

letxobnav wrote:
Tue Feb 25, 2020 9:38 am
or you could make sure that the Xero integration adopts/adheres/takes into account to the way OC does it.
Yeah, we've basically had to do that - we've got a line item just for rounding. It's a messy way to do it, and not great accounting practice.

Xero anticipates all sorts of possible rounding issues, but not one where the individual line items don't add up :)

Newbie

Posts

Joined
Fri Jul 26, 2013 5:05 am

Post by letxobnav » Tue Feb 25, 2020 11:15 am

Well, in core OC you could make it "easy" by changing the format function to only format without conversion (as it should, it is called format and not possibly_convert_and_format) and change all functions which fetch prices from the database to return those prices already converted, all prices, not just the product price.

That would mean that all calculations would be made in the current currency (the one in the session) and the formatting would do just that, format in the current currency. (you would have to check all queries though as there are controllers which run their own queries bypassing the model functions)

But, that introduces problems with extensions, any extension you have installed or will install in the future will expect the OC functions to return the prices in the default currency and do their own conversions which would make for some bad results.

So it is more a matter of convention and OC chose the wrong one.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

User avatar
Active Member

Posts

Joined
Fri Mar 16, 2012 10:18 am
Location - Canada, QC

Post by letxobnav » Wed Feb 26, 2020 1:40 am

not sure what those are for, I think this is exchange rate related, not price rounding, I experimented by rounding the exchange rate to (decimal places of the currency + 1) before doing the conversion.
Have not been able to check if that covers all instances though.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by ADD Creative » Fri Feb 28, 2020 2:30 am

Making the calculation after currency conversion was something I experimented on a while back.

My examples and comments on some of the issue I found are below, in case anyone is interested.
https://github.com/copona/copona/commit ... t-25190143
https://github.com/copona/copona/commit ... t-27435401
letxobnav wrote:
Tue Feb 25, 2020 11:15 am
Well, in core OC you could make it "easy" by changing the format function to only format without conversion (as it should, it is called format and not possibly_convert_and_format) and change all functions which fetch prices from the database to return those prices already converted, all prices, not just the product price.
Two different functions would be clearer. As it is, possibly_convert_and_possibly_format seems a more appropriate name.

www.add-creative.co.uk


Active Member

Posts

Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom
Who is online

Users browsing this forum: No registered users and 8 guests