Which are the correct separators for QIF-Import?

Matthias Hessler gnucash@mhessler.de
Sun, 22 Sep 2002 22:22:25 +0200


Hi,

some time ago I reported lots of problems with QIF import of a brokerage
account into Gnucash.
Unfortunately, despite giving all the details, nobody seemed to care.
I had checked the mailing list archives for several months back, so I'm
sure it is not a FAQ.

Please, anybody, let me know:
* how does the QIF import work, which separators for thousands and dot
are expected?
* They seem to be somehow internationalized, as I provide english ones,
but it seems to expect some weird German standard - why?

This is really bothersome.
Please don't let me down - I've also seen loads of other people having
similar trouble with QIF Import crashes and negative multi-dollar numbers
after the import, so these may be important issues for many people.
I've already invested many weekends to get the QIF import working, and
would love to make gnucash a success.

Matthias Hessler

----- Forwarded message from Matthias Hessler <gnucash@mhessler.de> -----

Delivered-To: gnucash-user@localhost.gnucash.org
From: Matthias Hessler <gnucash@mhessler.de>
To: gnucash-user@lists.gnucash.org
Subject: QIF-Import into 1.6.5 buggy
User-Agent: Mutt/1.3.27i
X-Sender: 310049880874-0001@t-dialin.net
X-Sanitizer: This message has been sanitized!
X-Sanitizer-URL: http://mailtools.anomy.net/
X-Sanitizer-Rev: $Id: Sanitizer.pm,v 1.54 2002/02/15 16:59:07 bre Exp $
Errors-To: gnucash-user-admin@lists.gnucash.org
X-BeenThere: gnucash-user@lists.gnucash.org
X-Mailman-Version: 2.0.11
Precedence: bulk
List-Help: <mailto:gnucash-user-request@lists.gnucash.org?subject=help>
List-Post: <mailto:gnucash-user@lists.gnucash.org>
List-Subscribe: <https://lists.gnucash.org/mailman/listinfo/gnucash-user>,
	<mailto:gnucash-user-request@lists.gnucash.org?subject=subscribe>
List-Id: General Accounting Discussions <gnucash-user.lists.gnucash.org>
List-Unsubscribe: <https://lists.gnucash.org/mailman/listinfo/gnucash-user>,
	<mailto:gnucash-user-request@lists.gnucash.org?subject=unsubscribe>
List-Archive: <http://lists.gnucash.org/pipermail/gnucash-user/>
Date: Mon, 9 Sep 2002 23:11:59 +0200
X-Sanitizer: This message has been sanitized!
X-Sanitizer-URL: http://mailtools.anomy.net/
X-Sanitizer-Rev: $Id: Sanitizer.pm,v 1.54 2002/02/15 16:59:07 bre Exp $
X-Spam-Status: No, hits=1.1 required=5.0
	tests=DOUBLE_CAPSWORD
	version=2.31
X-Spam-Level: *
X-UIDL: PWO!!,JR"!dJd"!,"j"!


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
_______________________________________________
gnucash-user mailing list
gnucash-user@lists.gnucash.org
https://lists.gnucash.org/mailman/listinfo/gnucash-user


- ----- End forwarded message -----

- -- 

- ---------------------------------------------------------------
Matthias Hessler