QIF-Import into 1.6.5 buggy
Matthias Hessler
gnucash@mhessler.de
Mon, 9 Sep 2002 23:11:59 +0200
Hi,
I want to migrate from my old MS Money (German version 3.0) to gnucash
(my Suse distribution comes along with version 1.6.5).
Sorry that I'm refering to this old version and post my mail to the mailing
list, but before filing a bug report I wanted to check whether anyone knows
more about these problems - when looking through the mailing list archives
since Jan 2002 I did not find anyone talking about these things.
My MS Money runs on a computer with english comma/dot settings, therefore
here a snippet of the original qif written by MS Money:
!Type:Invst
D17/1/00
CX
MKurs 13.10 EUR
T73,149.02
L[AdvanceBank]
$73,149.02
NBuyX
YFidelity World Funds
I25.621373
Q2,855
^
The import functionaly of gnucash gives me loads of problems importing this
properly.
The original qif as above will actually cause an almost dead-sure crash
with my gnucash version - interestingly enough, before the crash it says it is
not sure about the date format, and asks me to select the proper format, out
of an EMPTY combo box. The only way to avoid the crash is to cancel the
import immediately then.
Here my conclusions after several experiments (see below for the details of
the experiments):
* gnucash always reads the T-value properly, regardless whether dots or commas
were used, or whether thousand separators were present or not.
=> good, but why doesn't do it as greatly for the other values?
* gnucash has real problems reading the price properly:
- gnucash should never come up with negative values for anything where there
is no '-' involved => clearly a bug to me.
- gnucash has a rounding issue with the price, it looks as if it assumes that
there is a comma or some separater where instead there is a 3
(it read 25,621373 as 25.6217) => also clearly a bug to me.
- It rounds the price to 4 digits: why on earth? If the price is 6 digits,
keep it 6 digits (in this case it is 6 digits because the fund was traded
with 13.10 EUR, which I converted into 25.621373 DEM because my account
is still in DEM - my version of MS Money can only handle one currency)
=> I would call it a bug, but maybe it is just an 'undesired feature'
* gnucash has big problems reading the quantity properly:
- gnucash crashes if a comma was used as a thousand separator, without
a dot (Q2,855)
- gnucash can not handle the case where no separators are present for a
quantity (Q2855)
=> it comes up with negative values, messes up quantity, price and total
quantity (regardless of whether a comma or dot was given)
- a comma is required at the end of a quantity for gnucash to read the value
properly
=> only 'Q2.855,' was properly read as 2855
- gnucash assumes 'Q2.855' to be 2.855 - even so beforehand it correctly
identified the dot as thousand separator in "T73.149,02"
* gnucash seems to ignore the $-value, and rather calculates this value from
price and quantity
=> in my opinion gnucash could easily use the additional data to guess
the proper settings (autodetect), then use it for verification when
loading the qif-file. Who knows to select 'verify all accounts' to create
and see extra correction bookings?
Here the details of some of my experiments:
Crash on data import:
!Type:Invst
D17/1/00
CX
MKurs 13.10 EUR
T73,149.02
L[AdvanceBank]
$73,149.02
NBuyX
YFidelity World Funds
I25.621373
Q2,855
^
When correcting to:
Q2855
it imports into the German version, but has strange results:
quantity: -285.500 (means -285500 = negative, and too high by a factor of 100)
price: -25,6214 (means -25.6214 = should not be negative)
Total: 7.314.901,99 (means 7314901.99 = too high by a factor of 100)
A total of 7.314.901,99 (means 7314901.99 = wrong) is booked onto the
brokerage account, but 73.149,02 (means 73149.02 = correct) onto 'AdvanceBank'.
As a result, when checking all accounts, it will do a reconciliation booking
for 7.241.752,97 (means 7241752.97).
The same happens if I remove the commas:
!Type:Invst
D17/1/00
CX
MKurs 13.10 EUR
T73149.02
L[AdvanceBank]
$73149.02
NBuyX
YFidelity World Funds
I25.621373
Q2855
^
The same happens if I replace the dots by commas:
!Type:Invst
D17/1/00
CX
MKurs 13.10 EUR
T73149,02
L[AdvanceBank]
$73149,02
NBuyX
YFidelity World Funds
I25,621373
Q2855
^
Now I add thousands positions with dot:
!Type:Invst
D17/1/00
CX
MKurs 13.10 EUR
T73.149,02
L[AdvanceBank]
$73.149,02
NBuyX
YFidelity World Funds
I25,621373
Q2.855
^
Result changes:
quantity: 2,855 (means 2.855 = too low by a factor of 1000)
price: 25,6217 (means 25.6217 = please notice the rounding error!)
Total: 73,15 (means 73.15 = too low by a factor of 1000)
A total of 73,15 (means 73.15 = wrong) is booked onto the
brokerage account, but 73.149,02 (means 73149.02 = correct) onto 'AdvanceBank'.
As a result, when checking all accounts, it will do a reconciliation booking
for 73.075,87 (means 73075.87).
Since only the quantity seems to be off, let's try:
Q2855
again => yields the wrong results from our first attempts, with negative
quantity and price:
quantity: -285.500 (means -285500 = negative, and too high by a factor of 100)
price: -25,6214 (means -25.6214 = should not be negative)
Total: 7.314.901,99 (means 7314901.99 = too high by a factor of 100)
A total of 7.314.901,99 (means 7314901.99 = wrong) is booked onto the
brokerage account, but 73.149,02 (means 73149.02 = correct) onto 'AdvanceBank'.
As a result, when checking all accounts, it will do a reconciliation booking
for 7.241.752,97 (means 7241752.97).
Now trying to add a comma to our previous attempt:
Q.855,
=> finally, things are as they should be:
quantity: 2.855 (means 2855 = correct)
price: 25,6214 (means 25.6214 = correct)
Total: 73.149,02 (means 73149.02 = correct)
A total of 73.149,02 (means 73149.02 = correct) is booked onto the
brokerage account, as well as onto 'AdvanceBank'.
As a result, when checking all accounts, it will not do any reconciliation
booking (correct).
Trying with 'Q2855,' yields the same correct results.
Maybe someone can shed some light on these things...
---------------------------------------------------------------
Matthias Hessler