1. round off problem
- Posted by George Walters <gwalters at sc.rr.com> Jun 28, 2004
- 455 views
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
2. Re: round off problem
- Posted by "Mike Nelson" <MichaelANelson at worldnet.att.net> Jun 28, 2004
- 514 views
> 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
3. Re: round off problem
- Posted by Matt Lewis <matthewwalkerlewis at yahoo.com> Jun 28, 2004
- 454 views
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
4. Re: round off problem
- Posted by Al Getz <Xaxo at aol.com> Jun 28, 2004
- 445 views
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"
5. Re: round off problem
- Posted by George Walters <gwalters at sc.rr.com> Jun 28, 2004
- 454 views
<!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 do something like this<br> <br> If abs(number1 - number2) > .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>
6. Re: round off problem
- Posted by George Walters <gwalters at sc.rr.com> Jun 28, 2004
- 446 views
<!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) 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>
7. Re: round off problem
- Posted by "Kat" <gertie at visionsix.com> Jun 28, 2004
- 446 views
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
8. Re: round off problem
- Posted by Al Getz <Xaxo at aol.com> Jun 28, 2004
- 462 views
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"
9. Re: round off problem
- Posted by George Walters <gwalters at sc.rr.com> Jun 28, 2004
- 441 views
<!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> ieee64 = {0,0,0,0}<br> <br> ... called from C:\EUPHORIA\include\odbc.ew:1415 in function convertData() <br> str = {0,0,0,0}<br> dt = 7<br> a = <no value><br> b = <no value><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 <matthewwalkerlewis at yahoo.com> 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>
10. Re: round off problem
- Posted by Al Getz <Xaxo at aol.com> Jun 28, 2004
- 435 views
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"
11. Re: round off problem
- Posted by George Walters <gwalters at sc.rr.com> Jun 28, 2004
- 447 views
<!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>