I think a lot of financial institutions used BCD (Binary Coded Decimal) because it is accurate without round off like floating point.
Christopher Schene Field Engineer Fraud & Identity Client Implementations 16260 N. 71st Street, Suite 400 | Scottsdale, Arizona 85254 www.experian.com<http://www.experian.com/> experian From: David Law <[email protected]> Reply-To: POI Users List <[email protected]> Date: Friday, October 18, 2019 at 1:47 PM To: "[email protected]" <[email protected]> Subject: Re: Floating Point Arithmetics, reading 0.1066913 gives 0.10669129999999999 Hi Andreas, I tried to create a Spreadsheet to reproduce this but failed. (it returned double = 0.1066913) Could you share an example.xslsx with us to demonstrate the behaviour? All the best, DaveLaw On 18/10/2019 04:34, Andreas Reichel wrote: Dear All, compliments of the day. We face some challenges with reading values from Spread Sheets. Example: the numeric cell has the value = 0.1066913 and when reading that value with POI we receive the double = 0.10669129999999999. This turns into a problem, when writing these figures into a database, when the Precision/Scale of the field can't hold that double value, e.g. JdbcSQLDataException: Value too long for column """BASE_RATE"" VARCHAR(12)": "'0.10669129999999999' (19)"; SQL statement: insert /*+PARALLEL APPEND_VALUES*/ into CFE.INTEREST_PERIOD (ID_INSTRUMENT ,ID_FIXINGMODE ,FIXING_DATE ,CHANGE_DATE ,BASE_RATE ,BASE_MARGIN_RATE ,PAR_RATE ,PAR_MARGIN_RATE ,ID_PAYMENT_CONVENTION ,ID_DAY_COUNT_CONVENTION ,ID_DAY_INCL_CONVENTION ,FIX_AMOUNT ,ID_CURRENCY_FIX_AMOUNT ,ID_SCRIPT) VALUES (? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,?) [22001-199]+ Excel itself does not seem to have a problem, because multiplying the value = 0.1066913 with a large multiplicant 1E15 gives the correct amount: 0.1066913 x 1E15 = 106691300000000 Furthermore, "similar" values like 0.0813613 or 0.0716913 work well and will fit. My question is: How does Excel know the correct value, but POI does not? And would it not be very very usefull to have a method returning cell values as BigDecimals instead of Doubles. At the moment, we work around by advising the users to provide numbers as Text/String. Although that is counter intuitive and always yields in having egg on the face. Thank you already for advise and best regards Andreas --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected]<mailto:[email protected]> For additional commands, e-mail: [email protected]<mailto:[email protected]>
