CSV imported transactions were rounded down on import

English Import/export
1

Hi all,

I just did my monthly import of my crowdlending transactions and noticed something very odd.

Every month I follow the same procedure: I update a CSV file in LibreOffice with relevant transactions for each project, then import the CSV file in PP. So far this has been going very well.

Only this month, checking out my crowdlending dashboard in PP, something looked odd… monthly revenue was much lower than expected. Expecting to have missed transactions, I go and check the imported transactions and notice that they were all rounded down upon import!

I deleted all affected transactions and tried again: in the first screen of CSV import, everything is OK. In the second screen where you confirm the imported transactions, all numbers are indeed rounded down.

Does this ring a bell to anyone? Can someone try to reproduce?

My CSV file uses commas for separation, numbers use a dot for decimal, and the file is UTF8 encoded.

I’m using PP Version: 0.61.4 (Mar 2023), Platform: macosx, aarch64, Java: 17.0.5+8-LTS, Azul Systems, Inc.

Edited sample from the file:

Date,Type,Gross Amount,Taxes,Value,Shares,Note,Cash Account,Securities Account,Security Name
2023-02-04,Dividend,2.71,0.81,1.9,,ProjectNameAsNote,AccountName,SecuritiesAccount,SecurityName
2023-02-04,Dividend,2.92,0.88,2.04,,ProjectNameAsNote,AccountName,SecuritiesAccount,SecurityName
2023-02-04,Dividend,2.08,0.62,1.46,,ProjectNameAsNote,AccountName,SecuritiesAccount,SecurityName
2023-02-15,Dividend,2.5,0.75,1.75,,ProjectNameAsNote,AccountName,SecuritiesAccount,SecurityName

Thanks all for your help.

2

What do you mean by “rounded down”? Everything after the . cut off, or something else? Does it affect all numbers, or only some of them?

Can you show what happens when you try to import your sample?

And you did correctly specify that?

3

What do you mean by “rounded down”? Everything after the . cut off, or something else? Does it affect all numbers, or only some of them?

That’s right, every numeric value’s decimal part cut off.

And you did correctly specify that?

Just wanted to signal I’m aware of potential issues with that. CSV settings look fine as far as I can tell, and I’ve never run into any issue. Manual inspection in a text editor doesn’t look suspicious.

Can you show what happens when you try to import your sample?

Here are screenshots of the two CSV import screens. It shows numbers rounded down from the second screen. I don’t remember if the second screen should show more columns, or if showing just the “Amount” column is already a symptom of the issue.

Screenshot 2023-03-25 at 21.02.29
Screenshot 2023-03-25 at 21.02.291026×412 78.5 KB
Screenshot 2023-03-25 at 21.02.50
Screenshot 2023-03-25 at 21.02.501026×481 72.8 KB

If I go through with the import, below screenshots show two instances of same transaction, the one which imported fine in January, and the faulty one in February :

Screenshot 2023-03-25 at 21.18.21 Screenshot 2023-03-25 at 21.17.57

So it looks like it discarded the taxes and gross value information, and just took the rounded down net value.

4

I can’t reproduce the issue here.

5

Which platform are you on? I just tried both aarch64 and x86_64 on my Macbook Air M1 and in both cases the issue is there.

Since it’s been working fine previously, I tried downloading again some older releases:

  • 0.61.4 aarch64 Not OK
  • 0.61.3 aarch64 OK
  • 0.61.2 aarch64 (no binaries)
  • 0.61.1 aarch64 OK
  • 0.61.0 aarch64 OK
  • 0.60.2 aarch64 OK
  • 0.60.1 aarch64 OK
  • 0.60.0 aarch64 OK

I’ll try my luck and browse through the changes from 0.61.3 to 0.61.4.

6

Linux.

Are you in France?

I would reiterate my suggestion to verify that the right number format is selected (the default has changed for French locales).

7

Ok so I think so issue was introduced with [6417702] Adding a new money and date format in the CSV importer.

My locale is en_FR and it appears after this change the default format for numbers is "0 000.00" (space for thousands separator). With previous version I had "0,000.00" (by default).

Thus two questions here:

  • Since my numbers are small and do not even have thousands, I’m not sure why this results in an issue, so this looks like an actual bug with processing this format. In both cases, the decimal separator is a dot so it should be fine.
  • I see that all options listed to me for numbers formatting include some thousands separator. I’m not sure how these format specifiers work: is the thousands separator always optional?

Edit: You were faster, and correct. Changing back to the previous format does the trick. But I don’t see why it fails to import with this format specifier.

8

Yes, this will need investigating.

It should be.

1 Like
9

Thanks @chirlu, appreciate your late Saturday night support. Please go to bed now. :grinning:

1 Like
10

So, the definition is here:

The first parameter is a string with a . (dot). The second parameter is a localized (translatable) string, which in every language is the same and has a , (comma). I’m not sure what its use is, since obviously the first (non-localized) string gets displayed in the UI. The third parameter is the format that is actually used for parsing; it is taken from the French locale and probably expects , (comma) as the decimal separator. It appears that is indeed the common usage in France.

So, a bit of a mess. Probably the first string should be changed to have a , (comma), then the UI display would be correct instead of confusing. However, I don’t understand why we even have three independent (and, in this case, contradictory) definitions for one format.

1 Like
11

All that is needed to parse numeric input is 0-9, -, and the appropriate decimal separator, everything else should be stripped. The errors are due to PP overcomplicating things.

Similar issue for dates.

12

Well, the nunbers are stripped somehow before parsing the values:

13

So in the end the issue is the discrepancy between the displayed hardcoded format string and the NumberFormat parser used. And indeed there’s the localised strings which don’t appear to be used, or at least not in this context.

Too bad it’s not using the actual NumberFormat’s format function to show a meaningful example instead, like “123456.78” → “123 456,78” - that would enforce the consistency (this could make sense for other formats as well, like dates).

Yes but this only strips leading and trailing non-number characters.

Indeed a solution would be to offer the user just two formats “123,45” and “123.45” for the purpose of identifying the decimal separator from the user (default based on locale). Then remove anything not in [0-9, +, -, E, decimal separator]. Replace decimal separator with a dot, then use parseDouble or BigDecimal.

14

Got the new version and saw that the selector now offers “0 000,00” rather than “0 000.00” which I was seeing before. But I don’t know how that would be from browsing the changes between 0.61.4 and 0.62.0.

In doubt, I tried again 0.61.4 and can’t get the “0 000.00” option anymore, so I don’t know what to think.

If I’ve been hallucinating and PP actually displays the localised strings (which are correct: “0 000,00”) rather than the incorrect “0 000.00” which is in the code, then I apologise for the faulty report.

15

Hello @financialPineapple
if everything is OK, this bug should be fixed with the next release.

Regards
Alex

16

I am a little late to this thread, but let me clarify the mess a little bit.

About the parameter:

  • The first is the identifier of the pattern in the JSON config file (as you know, one can save, export, and import CSV configurations. It was added later and I needed and identifier that was stable across languages and can uniquely identify this pattern)
  • The second parameter is the translated label - the idea being that one can translate the identifier for years, etc. (again, maybe not so useful for the numbers as there is no translatable text there)
  • The third parameter is the NumberFormat that is actually used
17

I agree. The current implementation is needlessly exposing the complexities of NumberFormat.

I have played around with the code and that should simplify the options.

Bildschirmfoto 2024-02-04 um 11.39.23
Bildschirmfoto 2024-02-04 um 11.39.231308×538 113 KB

The only thing is that I cannot detect picking the wrong format. So “1.23” will be parsed as 123 if one selects the comma as decimal separator. But that is also today’s behavior as the number format behaves identical.

I’ll merge into the master branch and would appreciate feedback if you test before I create a new release.

18

Uh, I don’t think I’d like a “number” such as h 123,12 being accepted without any indication that there might be something wrong. At the very least, it should be marked yellow.

1 Like
19

Will be marked yellow (typically it is strings like “EUR 42” or “+5,00”)

Bildschirmfoto 2024-02-04 um 22.42.08

1 Like