Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Total Price rounding errors


Guest

Recommended Posts

I have noticed some rounding errors in my invoices when using different currencies (OSC2.2 MS2). Has anyone else experienced this and is there a fix?

 

I have entered two products in admin, one at ?49.99 including tax and one at ?50.00. This automatically calculated the pre-tax price and stored this into the database to 4 decimal places.

 

My tax rate is set at 21%

My exchange rate is 0.67000002 from Euro to Pounds

 

I then created a new order containing the following

2 x product at ?49.99

1 x product at ?50.00

 

before submitting this I change the currency from ? to ?

 

When I go the invoice for the order this is what I get

 

Products Tax Unit(ex) Unit(inc) Total (ex) Total (inc)

2 x test 49.99 21.00% ?27.68 ?33.49 ?55.36 ?66.99

1 x Test 50 21.00% ?27.69 ?33.50 ?27.69 ?33.50

 

Sub-Total (ex TAX): ?83.04

Delivery (ex TAX): ?8.30

TAX (21%): ?19.18

Total: ?110.52

 

As you can see the total for the products (ex TAX) comes to ?83.05 whereas the sub-total that is automatically calculated is a penny less

 

Thanks for any help

 

Rob

Link to comment
Share on other sites

I have found a fix to the above problem by changing the rounding for the net value to two decimal places rather than four.

 

simply change the Javascript function in catalog/admin/categories.php to:

function updateNet() {
 var taxRate = getTaxRate();
 var netValue = document.forms["new_product"].products_price_gross.value;

 if (taxRate > 0) {
   netValue = netValue / ((taxRate / 100) + 1);
 }

 document.forms["new_product"].products_price.value = doRound(netValue, 2);
}

 

Does anyone know if there was another reason why the rounding was set to 4 decimal places?

 

Thanks

 

Rob

Link to comment
Share on other sites

Hi,

I've been toying with this problem too lately...

Does anyone know if there was another reason why the rounding was set to 4 decimal places?

Because the number of decimal places ist limited to 4 in the database as well: in table products it says

products_price decimal(15,4)

 

To me it seems like a coincidence that reducing the number of decimal places solved your problem. Actually increasing it helps, products_price decimal(15,4) should become products_price decimal(15,6) (or more), and

document.forms["new_product"].products_price.value = doRound(netValue, 4);

should become

document.forms["new_product"].products_price.value = doRound(netValue, 7);

or more.

Disclaimer: I don not know however if these numbers are high enough for all practical purposes. Besides, net prices occur in other DB tables too, I haven't checked yet if the problem applies to those as well.

Link to comment
Share on other sites

Yes, I was beginning to notice that reducing the accuracy to two decimal places fixed the rounding problem using different currencies but was causing more rounding errors on the default currency.

 

Since there doesn?t seem to be an easy fix to this problem I have taken the decision to only run the store in one currency.

 

I have also increased the number of decimal places the store runs on to 8.

 

The SQL to perform this is given below in case anyone else has experienced rounding problems

 

 

ALTER TABLE `specials` CHANGE `specials_new_products_price` `specials_new_products_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `products_attributes` CHANGE `options_values_price` `options_values_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `products` CHANGE `products_price` `products_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `orders_total` CHANGE `value` `value` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `orders_products_attributes` CHANGE `options_values_price` `options_values_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `orders_products` CHANGE `products_price` `products_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `orders_products` CHANGE `final_price` `final_price` DECIMAL( 15, 8 ) DEFAULT '0.00000000' NOT NULL;
ALTER TABLE `customers_basket` CHANGE `final_price` `final_price` DECIMAL( 15, 8 ) DEFAULT '0.0000' NOT NULL;
ALTER TABLE `orders` CHANGE `currency_value` `currency_value` DECIMAL( 14, 8 ) DEFAULT NULL;

 

You should also increase the rounding to 8 in catalog/admin/categories.php as discussed earlier.

Link to comment
Share on other sites

  • 2 years later...

BUMP :(

 

Any solutions, except for running in one currency only? I can't find any answers in the forum or bug reports, except for this un-answered thread:

http://www.oscommerce.com/forums/index.php?showtopic=92448

and also a few comments in the Official PayPal IPN thread, which also seemed unanswered.

 

To repeat the problem:

 

There are rounding errors in the invoice totals when using different currencies.

 

Osc converts each part of an invoice into the new currency, rather than doing its adding in the converted item prices.

EG: My customer bought two CDs.

In GBP (default currency) they appear as ?5.99 each, and he used US$ so he saw them as $11.07 each.

The sub-total Osc calculated was $22.15 !!!

This is because it actually calculated ?11.98 (doing its adding in the default currency) and then converted that figure into US$. The rounding difference between converting ?5.99 and ?11.98 gave the extra cent.

He didn't complain... so I'm off the hook for a while.

 

But - is there a fix?

favicon.gif I'm Maynard, and I'm happily running, maintaining, tweaking osC on Maynard's Groovy Bible Tunes (google it!)
Link to comment
Share on other sites

OK, following the deafening silence of the community, here's a temporary fix:

 

Keep all your prices* and currency rates rounded to ONE decimal place.

That way all multiplications work out to at most TWO decimal places, and there is no rounding, so rounding errors cannot occur.

 

*prices meaning prices including tax.

 

ie.

= Sell things for ?5.00 and not ?4.99

= if you charge VAT at 17.5%, sell things for amounts which, when VAT is added, are very close to 1dp figures, eg ?4.34, which gives ?5.0995, ie ?5.10 when rounded, with only a small rounding error.

= have currency rates of (e.g.) 1.9 instead of 1.87445633

 

Obviously this would get complicated/impossible if for some reason the tax charged can vary on one item. Sorry.

 

{rant}

Is no-one else bothered by this rounding problem? I've visited other multi-currency sites, and it's an issue on them as well. Does no-one notice that OsCommerce can't add up?!!

{/rant}

favicon.gif I'm Maynard, and I'm happily running, maintaining, tweaking osC on Maynard's Groovy Bible Tunes (google it!)
Link to comment
Share on other sites

Unfortunately when man created those !*$x* 1 cent pennies .. he threw the whole economy for a loop.

 

OSC is not the only thing that can't keep track of tenths, hundreds, or thousandths of a cent ... our Governments themselves can't even figure it out.

 

Unfortunately it would involve rewriting a a lot of the code in osc. I don't even think the next release MS3 with its new core components will handle it.

 

I wouldn't worry about it anyway. You as a business owner are not liable for any minute accounting discrepencies ... unless you show hundreds or thousands of dollars missing come tax time. No business is 100% accurate.

 

Just put a small clause in your sales policies like large companies do ... that reads something like. "Prices are subject to change without notice due to market fluctuations" ... and you should be ok.

Link to comment
Share on other sites

I wouldn't worry about it anyway. You as a business owner are not liable for any minute accounting discrepencies ... unless you show hundreds or thousands of dollars missing come tax time. No business is 100% accurate.

It's not so much 'minute accounting discrepancies' as regularly 'overcharging' customers by 1 cent (or more - if a customer ordered 10 items where a currency rounding error is likely, they could be 'overcharged' by 4 cents).

 

Our customers do expect our invoices to them to be 100% accurate - and that's why we're getting away with this error: they assume they're accurate and so nobody notices!

 

Just put a small clause in your sales policies like large companies do ... that reads something like. "Prices are subject to change without notice due to market fluctuations" ... and you should be ok.

 

The clause needs to be a comment re how the multiple currency conversion works:

"When shopping in a currency other than [default currency], all amounts are shown as direct conversions. This may result in minor discrepancies in invoice additions, however the invoice total is guaranteed to be a direct conversion of what the invoice would cost in [default currency]."

favicon.gif I'm Maynard, and I'm happily running, maintaining, tweaking osC on Maynard's Groovy Bible Tunes (google it!)
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...