CSV import rounding errors

[Jindrich Makovicka]

Sometimes, the last digits in the amounts imported from CSV files are off by one. For example, the following CSV file imports as two transactions with 1196.10 and 1151.10 CZK, instead of the values specified in CSV.

date,time,account,amount,currency,category,parent,payee,location,project,note
2011-06-30,12:00:00,Bank,1196.11,CZK,,,"",,No project,
2011-01-31,12:00:00,Bank,1151.11,CZK,,,"",,No project,

[Piotr Myszka (pmyszka)]

The bug still exists in version 1.5.8u1.

Sample data:
date;time;account;amount;currency;category;parent;payee;location;project;note
2011-04-07;11:00:00;visa;-80.46;PLN;;;foo;Current location;No project;foo
2011-04-08;11:00:00;visa;-79.99;PLN;;;bar;Current location;No project;bar

These amounts are imported as -80.45 and -79.98. If changed to -80.47 however, the amount is imported properly.

[Piotr Myszka (pmyszka)]

/src/ru/orangesoftware/financisto/export/csv/CsvImport.java (revision 365):

else if (transactionField.equals("amount")) {
   double fromAmount = Double.parseDouble(fieldValue);
   Double fromAmountDouble = fromAmount * 100.0;
   transaction.fromAmount = fromAmountDouble.longValue();

The correct value (80.46) is lost in the 3rd line, when multiplied by 100.0 - it becomes 8045.999999999 and after conversion to long it becomes 8045.
For a quick hack, the fieldValue should be always right-padded with "1" to avoid this situation (80.461). For a more decent solution, the amount shouldn't probably be even kept as a long value.

[Jindrich Makovicka (makovick-gmail)]

Or better, Math.round() should be used instead of longValue.

[Wasa Pleshakov (wasa-j)]

Bug still exist in v.1.6.2.

Mine csv data is:
"2012-02-29","00:00:00","Альфа цели","2.26","RUB","Проценты","Доход","Альфа банк","","",""
It imports as 2.25 instead of 2.26.

to piotr-myszka:
why not just round value of fromAmountDouble before getting longValue of it?

[Piotr Myszka (pmyszka)]

@wasa-j: it was easier for me to parse my CSV file and right-pad all the amount values with "1" and re-import the file rather than apply a patch, recompile this app, install the app on the phone etc. So the right-pad workaround is for people like me, who want to save time ;)

[Wasa Pleshakov (wasa-j)]

@piotr-myszka: yeah, I got that and I'll follow your rigth-pad workaround soon. But still I'm wondering why this happens in _standard java libs_ and how to fix this bug in Financisto code.
Oh, didn't see your comment #3 when I asked about round function.

BTW, QIF import work with BigDecimal instead of Double , and does it very oddly (but I'm not specialist in java). And as i can see it works fine.

[Jindrich Makovicka (makovick-gmail)]

It is obvious why it happens. Some decimal numbers cannot be represented precisely in Double, so when parsed, their FP representation is either slightly smaller or lower. Because the conversion to Long always rounds down, sometimes the last digit changes.

The solution is either 1) using a rounding to nearest integer as proposed in #3, or 2) parse the parts before and after the decimal point as integers and avoid using floating point completely.

[Jindrich Makovicka (makovick-gmail)]

Created a pull request at Github:

https://github.com/jmakovicka/financisto/commit/1c4df11689d4dff61007155c142e9dad5fa0ad17