1. round off problem

I have imported general ledger data from a working system in another OS 
into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
ledger  software, I've run into serious  round  off issues. Here's a 
couple of examples to show the problem.

A typical account balance:

Mysql shows  -1143.46  which is  correct
EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
manual states.

Totalling the credits and debits over the entire file of transactions 
gives the following (printing with a mask of  %12.2f.)

Debits = 4642782.63
Credits= 4642782.56

Off by 4 cents. The input data from the other OS is balanced. It looks 
like there's about 8 places of accuracy not 15.
The data records are always read into a sequence, then arithmetic is 
done with atoms.

Any Ideas?

george

new topic     » topic index » view message » categorize

2. Re: round off problem

> I have imported general ledger data from a working system in another OS
> into mysql using Matt's ODBC tools. Trying to finish the  EU  general
> ledger  software, I've run into serious  round  off issues. Here's a
> couple of examples to show the problem.
>
> A typical account balance:
>
> Mysql shows  -1143.46  which is  correct
> EU trace shows -1143.459961 which is not accurate to 15 places as the EU
> manual states.
>
> Totalling the credits and debits over the entire file of transactions
> gives the following (printing with a mask of  %12.2f.)
>
> Debits = 4642782.63
> Credits= 4642782.56
>
> Off by 4 cents. The input data from the other OS is balanced. It looks
> like there's about 8 places of accuracy not 15.
> The data records are always read into a sequence, then arithmetic is
> done with atoms.
>

 Euphoria atoms are IEEE double precision floating points. They should never
be used for fiannical calculations, as many common decimal values cannot be
accuratrly represented in this type of value. For example, .01 has no finite
binary rerpesentation and therefor can't be exactly represented in an atom.
No doubt your source uses Binary Coded Decimal internally, which can
represent .01 exactly. (By the way BCD calculations are horribly slow vs
binary floating point and for this reason are never used except in finacial
calcualtions.)

Euphoria can't handle BCD directly but there are workarounds. If your totals
are constitently under 10,000,000 you can express all values in cents and
store them as Eu integers. If you are dealing with larger values, you will
need a libray for big numbers--check the contributions page.

-- Mike Nelson

new topic     » goto parent     » topic index » view message » categorize

3. Re: round off problem

George Walters wrote:
> 
> I have imported general ledger data from a working system in another OS 
> into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
> ledger  software, I've run into serious  round  off issues. Here's a 
> couple of examples to show the problem.
> 
> A typical account balance:
> 
> Mysql shows  -1143.46  which is  correct
> EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
> manual states.
>
> Totalling the credits and debits over the entire file of transactions 
> gives the following (printing with a mask of  %12.2f.)
> 
> Debits = 4642782.63
> Credits= 4642782.56
> 
> Off by 4 cents. The input data from the other OS is balanced. It looks 
> like there's about 8 places of accuracy not 15.
> The data records are always read into a sequence, then arithmetic is 
> done with atoms.
> 
> Any Ideas?

I think you may be having trouble with a 32-bit floating point number vs
a 64-bit number.  Try:

? float64_to_atom( atom_to_float64( 1143.46 ) )
? float32_to_atom( atom_to_float32( 1143.46 ) )

I get:

1143.46
1143.459961

What are the actual datatypes in the MySQL database?  And how are you
checking the value from MySQL?  You can see how odbc.ew converts data
by looking at the convertData() function.

Matt Lewis

new topic     » goto parent     » topic index » view message » categorize

4. Re: round off problem

Are you using any multiplications or divisions?
If not, you might have to move to using a special
adder for cents and dollars...do them separate...
that's the best way anyway because you can always use
integers and you get more range.


Take care,
Al

And, good luck with your Euphoria programming!

My bumper sticker: "I brake for LED's"

new topic     » goto parent     » topic index » view message » categorize

5. Re: round off problem

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Mike, If EU stores atoms with double precision (i.e 15 places per the
manual) then any round off would be negligible over a fairly large
number of additions. The only problem comes on comparisons which I
would have to&nbsp; do something like this<br>
<br>
If abs(number1 - number2) &gt; .001 <br>
<br>
for an equality test. If the difference is less than a penny then the
figures are equal , at least financially.<br>
<br>
george<br>
<br>
<br>
Mike Nelson wrote:<br>
<blockquote cite="mid928259027-1463792382-1088438917 at boing.topica.com"
 type="cite">



  </pre>
  <blockquote type="cite">
<pre wrap="">I have imported general ledger data from a working system in
    another OS
into mysql using Matt's ODBC tools. Trying to finish the  EU  general
ledger  software, I've run into serious  round  off issues. Here's a
couple of examples to show the problem.

A typical account balance:

Mysql shows  -1143.46  which is  correct
EU trace shows -1143.459961 which is not accurate to 15 places as the EU
manual states.

Totalling the credits and debits over the entire file of transactions
gives the following (printing with a mask of  %12.2f.)

Debits = 4642782.63
Credits= 4642782.56

Off by 4 cents. The input data from the other OS is balanced. It looks
like there's about 8 places of accuracy not 15.
The data records are always read into a sequence, then arithmetic is
done with atoms.

    </pre>
  </blockquote>
  <pre wrap=""><!---->
 Euphoria atoms are IEEE double precision floating points. They should never
be used for fiannical calculations, as many common decimal values cannot be
accuratrly represented in this type of value. For example, .01 has no finite
binary rerpesentation and therefor can't be exactly represented in an atom.
No doubt your source uses Binary Coded Decimal internally, which can
represent .01 exactly. (By the way BCD calculations are horribly slow vs
binary floating point and for this reason are never used except in finacial
calcualtions.)

Euphoria can't handle BCD directly but there are workarounds. If your totals
are constitently under 10,000,000 you can express all values in cents and
store them as Eu integers. If you are dealing with larger values, you will
need a libray for big numbers--check the contributions page.

-- Mike Nelson








  </pre>
</blockquote>
</body>
</html>

new topic     » goto parent     » topic index » view message » categorize

6. Re: round off problem

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Matt, I'll look at convertData. The fields in mysql are setup as
float(7,2)&nbsp; and mysql shows all the figures correctly when I retreive
them with a direct sql statement.<br>
<br>
thanks<br>
george<br>
<br>
Matt Lewis wrote:<br>
<blockquote cite="mid1493394626-1463792382-1088439042 at boing.topica.com"
 type="cite">


George Walters wrote:
  </pre>
  <blockquote type="cite">
<pre wrap="">I have imported general ledger data from a working system in
    another OS
into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
ledger  software, I've run into serious  round  off issues. Here's a 
couple of examples to show the problem.

A typical account balance:

Mysql shows  -1143.46  which is  correct
EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
manual states.

Totalling the credits and debits over the entire file of transactions 
gives the following (printing with a mask of  %12.2f.)

Debits = 4642782.63
Credits= 4642782.56

Off by 4 cents. The input data from the other OS is balanced. It looks 
like there's about 8 places of accuracy not 15.
The data records are always read into a sequence, then arithmetic is 
done with atoms.

Any Ideas?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
I think you may be having trouble with a 32-bit floating point number vs
a 64-bit number.  Try:

? float64_to_atom( atom_to_float64( 1143.46 ) )
? float32_to_atom( atom_to_float32( 1143.46 ) )

I get:

1143.46
1143.459961

What are the actual datatypes in the MySQL database?  And how are you
checking the value from MySQL?  You can see how odbc.ew converts data
by looking at the convertData() function.

Matt Lewis





  </pre>
</blockquote>
</body>
</html>

new topic     » goto parent     » topic index » view message » categorize

7. Re: round off problem

On 28 Jun 2004, at 11:14, George Walters wrote:

> 
> 
> I have imported general ledger data from a working system in another OS 
> into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
> ledger  software, I've run into serious  round  off issues. Here's a 
> couple of examples to show the problem.
> 
> A typical account balance:
> 
> Mysql shows  -1143.46  which is  correct
> EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
> manual states.
> 
> Totalling the credits and debits over the entire file of transactions 
> gives the following (printing with a mask of  %12.2f.)
> 
> Debits = 4642782.63
> Credits= 4642782.56
> 
> Off by 4 cents. The input data from the other OS is balanced. It looks 
> like there's about 8 places of accuracy not 15.
> The data records are always read into a sequence, then arithmetic is 
> done with atoms.
> 
> Any Ideas?

Use only string math libs. That way you are also free from the famous 
pentium math bug, or any future bugs. Humans play with money in decimal 
system, and additions is fine in binary when translated to decimal, but 
division isn't. Subtraction can get you into trouble too. Only other cure, if
you
don't mind being tied to a certain cpu family, is to use machine code to flip it
into bcd mode, do the math, and return it to previous state. But i would avoid 
binary like the plague when your rear counts on accuracy in decimal matters.

Or, round up/down to the penny after *each and every* basic math operation, 
which is still not trustworthy if your job is on the line. The order of the 
operations across the db will change the results, which is why it's not 
trustworthy.

Kat

new topic     » goto parent     » topic index » view message » categorize

8. Re: round off problem

Hello again,

I've used eu integers in the past for this kind of thing
using one for cents and one for dollars and creating a
function for addition and one for subtraction.  This 
guarantees success for addition and subtraction 
for dollar amounts up to about a billion dollars.


function add(sequence a1, sequence a2)
  sequence s

  s=a1+a2
  if s[2]>100 then
    --reduce s[2] and generate carry:
    s[2]=s[2]-100
    s[1]=s[1]+1
  end if
  return s
end function

function sub(sequence a1, sequence a2)
  sequence s

  s=a1-a2
  if s[2]<0 then
    --augment s[2] and generate borrow:
    s[2]=s[2]+100
    s[1]=s[1]-1
  end if
  return s
end function

procedure Print(sequence a)
  --typical printing
  printf(1,"%d.%d\n",{a[1],a[2]})
  --possibly: printf(1,"%d.%d\n",a)
end procedure


integer d,c  --dollars, cents
sequence a1, a2

-- $123.45 using all integers:
a1={123,45}

-- $123.46 using all integers:
a2={123,46}

Print(add(a1,a2))
Print(sub(a2,a1))




Note that with the above functions you dont have to 
worry about round errors and such.

You may wish to double check the functions for accuracy
before using.

If the amount goes over some number just over a billion,
you wont get a Eu error -- you can get up higher but you
may wish to add testing so the number wont turn into a float
(Somewhere over 10 billion dollars).

Note the sign is carried by the first sequence element.


Take care,
Al

And, good luck with your Euphoria programming!

My bumper sticker: "I brake for LED's"

new topic     » goto parent     » topic index » view message » categorize

9. Re: round off problem

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Matt, out of curosity I converted the data base numeric fields in mysql
to double(9,2) and then convertData begins to blow up...<br>
<br>
C:\EUPHORIA\include\machine.e:267 in function float64_to_atom() <br>
type_check failure, ieee64 is {0,0,0,0} <br>
&nbsp;&nbsp;&nbsp; ieee64 = {0,0,0,0}<br>
<br>
... called from C:\EUPHORIA\include\odbc.ew:1415 in function
convertData()&nbsp; <br>
&nbsp;&nbsp;&nbsp; str = {0,0,0,0}<br>
&nbsp;&nbsp;&nbsp; dt = 7<br>
&nbsp;&nbsp;&nbsp; a = &lt;no value&gt;<br>
&nbsp;&nbsp;&nbsp; b = &lt;no value&gt;<br>
<br>
Any idea what's going on here??<br>
<br>
george<br>
<br>
<br>
<br>
Matt Lewis wrote:<br>
<blockquote cite="mid1493394626-1463792382-1088439042 at boing.topica.com"
 type="cite">


posted by: Matt Lewis &lt;matthewwalkerlewis at yahoo.com&gt;

George Walters wrote:
  </pre>
  <blockquote type="cite">
<pre wrap="">I have imported general ledger data from a working system in
    another OS
into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
ledger  software, I've run into serious  round  off issues. Here's a 
couple of examples to show the problem.

A typical account balance:

Mysql shows  -1143.46  which is  correct
EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
manual states.

Totalling the credits and debits over the entire file of transactions 
gives the following (printing with a mask of  %12.2f.)

Debits = 4642782.63
Credits= 4642782.56

Off by 4 cents. The input data from the other OS is balanced. It looks 
like there's about 8 places of accuracy not 15.
The data records are always read into a sequence, then arithmetic is 
done with atoms.

Any Ideas?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
I think you may be having trouble with a 32-bit floating point number vs
a 64-bit number.  Try:

? float64_to_atom( atom_to_float64( 1143.46 ) )
? float32_to_atom( atom_to_float32( 1143.46 ) )

I get:

1143.46
1143.459961

What are the actual datatypes in the MySQL database?  And how are you
checking the value from MySQL?  You can see how odbc.ew converts data
by looking at the convertData() function.

Matt Lewis





  </pre>
</blockquote>
</body>
</html>

new topic     » goto parent     » topic index » view message » categorize

10. Re: round off problem




procedure Print(sequence a)
  --typical printing
  printf(1,"%d.%02d\n",{a[1],a[2]})
  --possibly: printf(1,"%d.%02d\n",a)
end procedure




 



Take care,
Al

And, good luck with your Euphoria programming!

My bumper sticker: "I brake for LED's"




		

		

		


	

		

							new topic
										    »
				goto parent
			 			     »
			 topic index
			 » view message
			 » categorize
		

	


	

		
		
11. Re: round off problem

		

		



<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Matt, you're right about the 32 vs 64 conversion. Forget the last
message on the convertData blow up....my typo caused it. I changed the
mysql specifications to double(xx,yy) as needed and the numeric
significance is now considerably more. I'm having no round off issue
reading at least 2000 financial records. I'll need to do some more
checking however on larger datasets.<br>
<br>
Thanks for everybody's help and suggestions... very enlightening on how
each one solves the same problem.<br>
<br>
Matt Lewis wrote:<br>
<blockquote cite="mid1493394626-1463792382-1088439042 at boing.topica.com"
 type="cite">


George Walters wrote:
  </pre>
  <blockquote type="cite">
<pre wrap="">I have imported general ledger data from a working system in
    another OS
into mysql using Matt's ODBC tools. Trying to finish the  EU  general 
ledger  software, I've run into serious  round  off issues. Here's a 
couple of examples to show the problem.

A typical account balance:

Mysql shows  -1143.46  which is  correct
EU trace shows -1143.459961 which is not accurate to 15 places as the EU 
manual states.

Totalling the credits and debits over the entire file of transactions 
gives the following (printing with a mask of  %12.2f.)

Debits = 4642782.63
Credits= 4642782.56

Off by 4 cents. The input data from the other OS is balanced. It looks 
like there's about 8 places of accuracy not 15.
The data records are always read into a sequence, then arithmetic is 
done with atoms.

Any Ideas?
    </pre>
  </blockquote>
  <pre wrap=""><!---->
I think you may be having trouble with a 32-bit floating point number vs
a 64-bit number.  Try:

? float64_to_atom( atom_to_float64( 1143.46 ) )
? float32_to_atom( atom_to_float32( 1143.46 ) )

I get:

1143.46
1143.459961

What are the actual datatypes in the MySQL database?  And how are you
checking the value from MySQL?  You can see how odbc.ew converts data
by looking at the convertData() function.

Matt Lewis





  </pre>
</blockquote>
</body>
</html>




		

		

		


	

		

							new topic
										    »
				goto parent
			 			     »
			 topic index
			 » view message
			 » categorize
		

	




			


		


		


			


				
Search


				

					

						

						
						
						

					

					
				


				
Quick Links

				

				
					
User menu

                                        Not signed in.
					

				
				
				
Misc Menu