After problems with base currency conversions in reports, I've been running parameterized, preprepared SQL queries from the BASH command line very successfully but they require parsing the fractional value of "kmmSplits"."sharesFormatted" each time the quantity of shares is required. It seems that formatting commas etc have been removed from the comparable "kmmSplits"."valueFormatted" field so it can be used without parsing but "kmmSplits"."sharesFormatted" bears no relation to the quantity of shares at all (seems to be an integer rounding of the "kmmSplits"."priceFormatted" field). It would make life much easier if "kmmSplits"."sharesFormatted" held the decimal value of "kmmSplits"."shares" (albeit as TEXT). Apologies if "kmmSplits"."sharesFormatted" is being used for some other purpose and this is not a bug at all.
Ouch. You are right, sharesFormatted does appear to be the price, formatted to a precision dependent on the account of the split. I'll have to dig into the code to see what's going on.
Git commit 6641cad7c940a0867fd106e14bebba00d51bdf4c by Jack Ostroff. Committed on 13/08/2023 at 19:25. Pushed by ostroffjh into branch 'master'. Fix sql sharesFormatted to use shares instead of price M +1 -3 kmymoney/plugins/sql/mymoneystoragesql_p.h https://invent.kde.org/office/kmymoney/-/commit/6641cad7c940a0867fd106e14bebba00d51bdf4c
Git commit 854ec1ac9a8b8d8cff082d1a51e9978a4126090b by Jack Ostroff. Committed on 13/08/2023 at 19:41. Pushed by ostroffjh into branch '5.1'. Fix sql sharesFormatted to use shares instead of price FIXEDIN: 5.1.4 M +1 -3 kmymoney/plugins/sql/mymoneystoragesql_p.h https://invent.kde.org/office/kmymoney/-/commit/854ec1ac9a8b8d8cff082d1a51e9978a4126090b
Thanks so much for fixing this issue, it will make writing SQL queries so much easier! BTW am I right in thinking these ."valueFormatted" fields will no longer include comma thousand separators (which previously had to be parsed out for calculations)? Or perhaps that was a setting I had made in error in a previous version installation?
The ONLY change I made was in the name of the variable being formatted. I don't believe thousand separators (commas) should ever be used in such variables. I'm not aware of any KMM setting that should have made them be used. What are you using to process this data? In terms of stability of the current status, I don't foresee any short term work on the SQL storage plugin other than to fix bugs.
Thanks for your kind reply (and work on kmymoney). I use the sqlite3 command to process preprepared .sql queries that produce .csv reports with calculated ratios etc. I noticed that prices (over 1000) stored in the "kmmPrices"."priceFormatted" field for example, include a comma as the thousand separator, which has to be parsed out for sql calculations. These ."xxxxFormatted" would be much easier & neater to use in sql calculations but can't be relied upon "raw" if some include commas etc - hence my add on question. (The alternative of parsing fractions for substrings does require some very very long expressions in some instances.) I had not previously reported it as a bug because I inferred from the manual that this formatting was part of the "xxxxxFormatted" fields purpose - to improve the readability of large amounts on screen (but hoped there might be a settings option to turn it off - that I had missed) From the handbook: "Monetary amounts and share values are shown both in numerator/denominator format, and, with a field name suffixed with 'Formatted', in the form as shown on your screens." Perhaps in light of your last comment I should have reported it earlier as a bug?
No, given that description, I think the presence of the commas is intentional. I just didn't notice it. I'll see if it's reasonable to add a checkbox to toggle the use of the commas, but I'm not sure at this point. In the meantime, some questions: Are you sure there is no function in sql to parse a number from a string that does have thousands separators? If you are just writing out those values as part of a csv file, is it possible tor whatever you are using to process that file to read numbers with the thousands separator? I haven't tried, but I would think most languages (including Excel/Libre Office) should have a way to do it. This might mean less processing in sql and more in subsequent steps. Are you depending on the number of decimal places output, or just depending that there are enough to not lose information? As a possible alternative, have you thought about using the unformatted version? It is always (I think) in the form of "x/y" where x and y are integers. Just do the division.
>>"I'll see if it's reasonable to add a checkbox to toggle the use of the commas" - That would be great if possible because as you say, I don't believe they have any place in a numerical field. Surely it's more properly the program's job to the formatting >>Are you sure there is no function in sql to parse a number from a string that does have thousands separators? They can indeed be parsed out but it makes for unnecessarily messy code. >>...process that file to read numbers with the thousands separator? Yes but I'm not just reading & copying data, I need to perform calculations which is where the commas interfere. >>Are you depending on the number of decimal places output, or just depending that there are enough to not lose information? Enough to be accurate to 2 decimal in the result but that sometimes requires more places in the price ... have you thought about using the unformatted version? .... in the form of "x/y" That is my current choice but here is an example of the in (sqlite) sql code to parse one value stored as a fraction: ((CAST(substr ("kmmSplits"."shares", 1, (instr ("kmmSplits"."shares", '/' ) - 1 ) ) AS REAL ) ) / (CAST( substr( "kmmSplits"."shares", ( instr( "kmmSplits"."shares", '/' ) + 1 ), LENGTH( "kmmSplits"."shares" )) AS REAL ))) I sometimes want to multiply several values whilst adding and subtracting others . Thanks to your fix, in future it can be replaced with "kmmSplits"."valueFormatted" but that will have to be REPLACE("kmmSplits"."valueFormatted",',','')) if it contains commas. Hence my request.
The problem is that Formatted is not a numeric field, it is explicitly text, and formatting of currency amounts can reasonably include decimal and thousands separators. It also turns out it will likely be significantly more difficult to make that change than I first thought - I was thinking about a different part of the program. I won't rule it out, but it probably won't happen soon. I have opened a separate wishlit so it doesn't get forgotten. See https://bugs.kde.org/show_bug.cgi?id=473381 I wasn't asking if you could explicitly parse out the commas, I was asking if there was an existing function to convert text to number, recognizing the commas, just so you don't need to parse them out. In my reading (I don't commonly use sqlite, so I'm not really familiar with it) it seems you should relatively easily be able to write a function in some other language (where the parsing and/or conversion would be easier) that you can then call directly from sqlite.
Well I'm very grateful for your consideration and raising the wish list entry. Sorry I misunderstood your point about the function. As a complete amateur I did try an extensive search for a solution / function but only came up with the explicit parsing which though messy does work. Thanks again John
Not a problem. It's always hard to tell how much programming experience anyone has, but I figure sql is not a trivial thing to learn, so if you've gotten that far, you can probably figure out more :-) I'll leave it to you to search for how to write functions in Python (or whatever) that you can call in sqlite, but you can also define functions totally within sqlite, which at least can make your code look cleaner. Another option would be to write everything in some other language (Perl, Python, ...) and use it's interface to read the data from sqlite. It really boils down to personal preference.