Re: on-line banking & qif import

[Bill Gribble]

Linas Vepstas <[EMAIL PROTECTED]> writes:
> Sooo (sound of lightbulb turning on) isn't the right way to import
> QIF files is to run them through a reconcile-like dialogue?

That's a great idea for the "last step" of *some* QIF imports.  I think
the most common usage of the QIF importer is still a mass import from
old Quicken records, which doesn't need such a reconciliation step.  

However, as you point out, downloading short snippets of QIF from the
bank is a pretty common operation, and for this case we need a way of
comparing the transactions that are being imported with
already-existing transactions so as to match up QIF imported ones with
ones you entered from your checkbook.

> Anyone out there using gnucash and also looking at thier bank-staements
> on-line?  What do y'all think?

I'd be interested to hear a more detailed description of how that
might work.  It seems like you would still need the step of mapping
QIF accounts to GNUcash accounts, but would just add a "pruning"
dialog which would let you match a QIF transaction with an existing
GNUcash transaction or just import it if you hadn't entered that
transaction by hand.

Bill Gribble

Re: on-line banking & qif import

[linas]

It's been rumoured that Bill Gribble said:
> 
> Linas Vepstas <[EMAIL PROTECTED]> writes:
> > Sooo (sound of lightbulb turning on) isn't the right way to import
> > QIF files is to run them through a reconcile-like dialogue?
> 
> However, as you point out, downloading short snippets of QIF from the
> bank is a pretty common operation, and for this case we need a way of
> comparing the transactions that are being imported with
> already-existing transactions so as to match up QIF imported ones with
> ones you entered from your checkbook.

Its not just 'matching them up', its also marking them as 'tentatively 
cleared' in the reconcile dialog.   Basically, since the qif is 'just
like' a paper statement one got from the bank, it should be treated the
same way (but automated).  New transactions should be 'tentatively
added' and old transactions 'tentatively cleared', until the user can
review the contents of the 'reconcile' window, and make the changes
permanent.

---
More generically, I was looking at the reconcile window as 'a place to
review changes before they are made permanent', which one might even be
interested in having when doing mass-imports of raw data.  

--linas

Re: on-line banking & qif import

[Bill Gribble]

[EMAIL PROTECTED] writes:
> Its not just 'matching them up', its also marking them as 'tentatively 
> cleared' in the reconcile dialog.   

I think this should be a separate step from the import.  It makes
sense to store information with a transaction indicating "we've seen
this in a bank download", but I don't think that should actually have
an effect on the "cleared" status of the transaction until the REAL
reconcile window comes up.

The problem is that opening/closing balances for reconciliation
periods should be "sacred"; if you are reconciling once a month from
your bank statement, nothing you do in between should affect the
"Cleared Balance" that results from doing the monthly reconcile;
otherwise, your statement "opening balance" won't match up with
gnucash's "cleared balance".

> Basically, since the qif is 'just like' a paper statement one got
> from the bank, it should be treated the same way (but automated).

I don't think it's that clear.  Yes, we should take whatever
information we have from imported QIF files, but I'm not sure it
neatly fits into the existing framework for reconciliation, which is a
matter of making your gnucash records match up with the bank's on a
periodic and complete basis, rather than an intermittent and
fragmentary basis.

> More generically, I was looking at the reconcile window as 'a place to
> review changes before they are made permanent', which one might even be
> interested in having when doing mass-imports of raw data.  

I guess this is what I disagree with... reconciling is a
clearly-defined concept, and it just muddies the water to mix it up
with generic integration of information from the bank.

IMO the dialog you need to meaningfully integrate QIF snippets is very
different from the account reconcile window.  In the reconcile window,
you are just checking off transactions that you have entered; in the
"snippet incorporate" window, you are confirming and modifying the
results of some VERY fuzzy matching rules which look at your account
and try to find transactions matching the ones you are importing.

b.g.

Re: on-line banking & qif import

[Randolph Fritz]

On Tue, May 09, 2000 at 12:50:09AM -0500, Linas Vepstas wrote:
> 
> I was talking to someone about on-line banking & gnucash.  I hadn't
> thought about ti much, but a large part of on-line banking is
> reconciling statements against what the bank has.  Now, many 'online
> banks' use QIF export as a way of sending statements to users.  Sooo 
> (sound of lightbulb turning on) isn't the right way to import QIF files
> is to run them through a reconcile-like dialogue?  
> 
> Anyone out there using gnucash and also looking at thier bank-staements
> on-line?  What do y'all think?
> 

That's exactly what I am currently doing manually, once a week.  My
Credit Union can provide transaction records in QIF format, but there
doesn't seem to be a good way to use them with GnuCash.  One of the
things I like about this combination of services is the amount of work
it saves; errors are caught early, and there is no need to laboriously
drag through a month or more of transactions to unsnarl mistakes.

-- 
Randolph Fritz
Eugene, Oregon, USA

Re: on-line banking & qif import

[linas]

Bill, see the note below.

I take all of the points you made in an earlier message; you're right,
reconcileing with QIF files is a potentially ill-defined, dangerous
process.  And yet, we still have the note below.  

It's been rumoured that Randolph Fritz said:
> 
> On Tue, May 09, 2000 at 12:50:09AM -0500, Linas Vepstas wrote:
> > 
> > I was talking to someone about on-line banking & gnucash.  I hadn't
> > thought about ti much, but a large part of on-line banking is
> > reconciling statements against what the bank has.  Now, many 'online
> > banks' use QIF export as a way of sending statements to users.  Sooo 
> > (sound of lightbulb turning on) isn't the right way to import QIF files
> > is to run them through a reconcile-like dialogue?  
> > 
> > Anyone out there using gnucash and also looking at thier bank-staements
> > on-line?  What do y'all think?
> > 
> 
> That's exactly what I am currently doing manually, once a week.  My
> Credit Union can provide transaction records in QIF format, but there
> doesn't seem to be a good way to use them with GnuCash.  One of the
> things I like about this combination of services is the amount of work
> it saves; errors are caught early, and there is no need to laboriously
> drag through a month or more of transactions to unsnarl mistakes.

If I understand this correctly, and we did qif-based reconciliation,
it would work as follows:

-- randolph goes to bank web site, makes note of the banks current balance,
   and downloads a qif. 
-- he powers up gnucash, and picks 'reconcile-qif' from the menu.
-- we suck in the qif file, and try to find any transactions in it that
   are not already recorded in the register.  we pop up a window, saying
   'do you want to add these transactions to your account?' and allow
   user to add them one by one, or regject them one by one, till they're
   done.  We may want to help them idntify potential duplicates (e.g.
   dates, amounts which are identical, but payee feild is different, or
   dates that are same, payee field slightly misspeled, and amounts
   slightly off ...) 
-- when they're done with above, then we pop up the reconmcile window,
   and from there, things proceed as normal reconciles do ...
   with one 'minor' difference: instead of having 'y/n' in the reconcile
   column, we might have a yellow 'm' for 'maybe' or 'c' for
   candidate/confirm', and the 'c's' got marked that way because 
   we marked them when the QIF came in.  randolph has only to click on
   yellow c's to turn them into green y's to get full reconciled.
   (and there would be a yellow subtotal, showing hopefully yellow $0.0
   which turns green at the end ... )


Linas.

Re: on-line banking & qif import

[Richard Wackerbarth]

On Thu, 11 May 2000, Linas wrote:
> If I understand this correctly, and we did qif-based reconciliation,
> it would work as follows:
>
> -- randolph goes to bank web site, makes note of the banks current balance,
>and downloads a qif.
> -- he powers up gnucash, and picks 'reconcile-qif' from the menu.
> -- we suck in the qif file, and try to find any transactions in it that
>are not already recorded in the register.  we pop up a window, saying
>'do you want to add these transactions to your account?' and allow
>user to add them one by one, or regject them one by one, till they're
>done.  We may want to help them idntify potential duplicates (e.g.
>dates, amounts which are identical, but payee feild is different, or
>dates that are same, payee field slightly misspeled, and amounts
>slightly off ...)
> -- when they're done with above, then we pop up the reconmcile window,
>and from there, things proceed as normal reconciles do ...
>with one 'minor' difference: instead of having 'y/n' in the reconcile
>column, we might have a yellow 'm' for 'maybe' or 'c' for
>candidate/confirm', and the 'c's' got marked that way because
>we marked them when the QIF came in.  randolph has only to click on
>yellow c's to turn them into green y's to get full reconciled.
>(and there would be a yellow subtotal, showing hopefully yellow $0.0
>which turns green at the end ... )

Yes, that is generally how I see it working. However, we should recognize 
that we may not want to actually finish the reconciliation. That might need 
to wait for the printed statement to arrive.

Re: on-line banking & qif import

[Gerald Champagne]

[EMAIL PROTECTED] wrote:
> 
> Bill, see the note below.
> 
> I take all of the points you made in an earlier message; you're right,
> reconcileing with QIF files is a potentially ill-defined, dangerous
> process.  And yet, we still have the note below.
> 
> It's been rumoured that Randolph Fritz said:
> >
> > On Tue, May 09, 2000 at 12:50:09AM -0500, Linas Vepstas wrote:
> > >
> > > I was talking to someone about on-line banking & gnucash.  I hadn't
> > > thought about ti much, but a large part of on-line banking is
> > > reconciling statements against what the bank has.  Now, many 'online
> > > banks' use QIF export as a way of sending statements to users.  Sooo
> > > (sound of lightbulb turning on) isn't the right way to import QIF files
> > > is to run them through a reconcile-like dialogue?
> > >
> > > Anyone out there using gnucash and also looking at thier bank-staements
> > > on-line?  What do y'all think?
> > >
> >
> > That's exactly what I am currently doing manually, once a week.  My
> > Credit Union can provide transaction records in QIF format, but there
> > doesn't seem to be a good way to use them with GnuCash.  One of the
> > things I like about this combination of services is the amount of work
> > it saves; errors are caught early, and there is no need to laboriously
> > drag through a month or more of transactions to unsnarl mistakes.
> 
> If I understand this correctly, and we did qif-based reconciliation,
> it would work as follows:
> 
> -- randolph goes to bank web site, makes note of the banks current balance,
>and downloads a qif.
> -- he powers up gnucash, and picks 'reconcile-qif' from the menu.
> -- we suck in the qif file, and try to find any transactions in it that
>are not already recorded in the register.  we pop up a window, saying
>'do you want to add these transactions to your account?' and allow
>user to add them one by one, or regject them one by one, till they're
>done.  We may want to help them idntify potential duplicates (e.g.
>dates, amounts which are identical, but payee feild is different, or
>dates that are same, payee field slightly misspeled, and amounts
>slightly off ...)
> -- when they're done with above, then we pop up the reconmcile window,
>and from there, things proceed as normal reconciles do ...
>with one 'minor' difference: instead of having 'y/n' in the reconcile
>column, we might have a yellow 'm' for 'maybe' or 'c' for
>candidate/confirm', and the 'c's' got marked that way because
>we marked them when the QIF came in.  randolph has only to click on
>yellow c's to turn them into green y's to get full reconciled.
>(and there would be a yellow subtotal, showing hopefully yellow $0.0
>which turns green at the end ... )
> 
> Linas.


Linas,

When I read your E-mails about this I thought it was a great idea (I also 
agree with Bill's concerns).  After thinking about your idea for a while,
the concept of using a piece of paper for a statement sounded a hundred years
old, yet we're still doing it.  Can we just get rid of the paper statement,
just like we replaced the paper register?  

Your method is almost exactly the same as replacing a paper statement with
an electronic one, but a quick overview of the process sounds like you're
trying to change the methods used to import and reconcile.  Is there a way 
to present your method to the user so it's more obvious that you're just 
replacing the paper statement with an electronic version, and then adding a
few obvious features?

For example:

- Download the qif data into a "bank statement" display.

- Display this next to the real register in a "diff" format where Gnucash
  tries to match transactions as best as possible. 

- Now the user can manually reconcile everything from here and it's just 
  like the process we use now, and no one feels like anything has changed.

- Now with our existing reconciling methods still obviously unchanged, what's
  the first feature a user would want?  They would want a way to just copy
  a transaction from the statement into the register, with a few tweaks to
  change the account names, payee, and memo fields before the copy.  This
  is useful for those transactions where you know they are valid, but you 
  forgot to enter them into the register.  This is still just like what we
  do manually.

- After a while we realize that if we're lazy and don't enter a transaction
  into the register before reconciling, then we just enter them while we're
  reconciling the account.  Sometimes we do this now as well, except with your 
  idea, typing is reduced.  For every transaction I enter after downloading
  the statement, I have less typing to do.

After a while using this feature, I would change the way I work.  Instead
of entering transactions on a regular basis and reconciling once a month,
I would "reconcile" every time I use the program.  I would download the 
statement _before_ each time I sit down to enter trans

Re: on-line banking & qif import

[Hendrik Boom]

> On Tue, May 09, 2000 at 12:50:09AM -0500, Linas Vepstas wrote:
> > 
> > I was talking to someone about on-line banking & gnucash.  I hadn't
> > thought about ti much, but a large part of on-line banking is
> > reconciling statements against what the bank has.  Now, many 'online
> > banks' use QIF export as a way of sending statements to users.  Sooo 
> > (sound of lightbulb turning on) isn't the right way to import QIF files
> > is to run them through a reconcile-like dialogue?  
> > 
> > Anyone out there using gnucash and also looking at thier bank-staements
> > on-line?  What do y'all think?
> > 

Last time I looked, my bank's qif imports give me all the transactions
since the last statement, not since the last qif import.

If gnucash were to remember the complete text of the qif-version of the
transactions, it could easily and reliably eliminate duplicate
qif-transactions.  Matching them with the manually entered ones
should not be automatic -- except, of course if gnucash recognizes
that a new qif transaction is identical to a previously matched
qif-transaction.

Reconciling with monthly bank statements is a separate issue, because
monthly bank statements (a) do not repeat transactions, and (b) are
a separate sequence of consistent data from the downloads.

Maybe some users do download consistent, non-verlapping batches of
qif transactions.  They should then be reconcilable independently from
paper statements.

> 
> That's exactly what I am currently doing manually, once a week.  My
> Credit Union can provide transaction records in QIF format, but there
> doesn't seem to be a good way to use them with GnuCash.  One of the
> things I like about this combination of services is the amount of work
> it saves; errors are caught early, and there is no need to laboriously
> drag through a month or more of transactions to unsnarl mistakes.

> 
> -- 
> Randolph Fritz
> Eugene, Oregon, USA
> 

Re: on-line banking & qif import

[Richard Wackerbarth]

On Thu, 11 May 2000, Hendrik Boom wrote:

> Last time I looked, my bank's qif imports give me all the transactions
> since the last statement, not since the last qif import.
You should also be able to get the bank to export other periods.
In general, I think that we must assume no correlation between importing data 
and reconciliation. All that we know is that each entry imported from the 
bank must appear in the ledger and that it has cleared the bank.
A JE must progress through the following "reconciliation states"
1) Entered
2) Cleared
3) Candidate
4) Reconciled

The act of reconciliation is that of selecting candidates so that they match 
the statement to which they are being reconciled. When it is complete, the 
"commit" operation moves all the candidates to the reconciled state.
>From a user's POV, it is convenient to be able to invoke a function which 
moves all "Cleared" entries to the "Candidate" state if they are on or before 
the closing date of the statement. The user can then manually select 
exceptions.


> If gnucash were to remember the complete text of the qif-version of the
> transactions, it could easily and reliably eliminate duplicate
> qif-transactions.  Matching them with the manually entered ones
> should not be automatic -- except, of course if gnucash recognizes
> that a new qif transaction is identical to a previously matched
> qif-transaction.
I don't think that it is necessary to attempt to remember the complete text.
Transaction(cheque) identifier(number) and amount should be adequate to 
identify most transactions once they have been matched the first time.

> Reconciling with monthly bank statements is a separate issue
Agreed. However, downloading from the bank and then selecting all "cleared" 
items is likely to produce something very close to the correct set.

Re: on-line banking & qif import

[Hendrik Boom]

> In general, I think that we must assume no correlation between importing data 
> and reconciliation. All that we know is that each entry imported from the 
> bank must appear in the ledger and that it has cleared the bank.
> A JE must progress through the following "reconciliation states"
> 1) Entered
> 2) Cleared
> 3) Candidate
> 4) Reconciled

That's right.  Only I like to add a note to each candidate and reconciled
transaction to pair it off with a specific line on the bank statement.
My credit card statements contain line numbers, and i use them
in the ledger to establish a one-to-one correspondence.

> 
> The act of reconciliation is that of selecting candidates so that they match 
> the statement to which they are being reconciled. When it is complete, the 
> "commit" operation moves all the candidates to the reconciled state.
> >From a user's POV, it is convenient to be able to invoke a function which 
> moves all "Cleared" entries to the "Candidate" state if they are on or before 
> the closing date of the statement. The user can then manually select 
> exceptions.

Except that when I reconcile, I match up the transactiuons with the statement
one at a time and check them off on the paper statement.  If I Converted
I would lose track of which lines on the paper statement had been matched.

> I don't think that it is necessary to attempt to remember the complete text.
> Transaction(cheque) identifier(number) and amount should be adequate to 
> identify most transactions once they have been matched the first time.

Except a lot of the transactions I get don't have meaningful numbers on
the bank statement -- whether .qif or paper.

-- hendrik.

Re: on-line banking & qif import

[Richard Wackerbarth]

On Thu, 11 May 2000, Hendrik Boom wrote:
> > In general, I think that we must assume no correlation between importing
> > data and reconciliation. All that we know is that each entry imported
> > from the bank must appear in the ledger and that it has cleared the bank.
> > A JE must progress through the following "reconciliation states" 1)
> > Entered
> > 2) Cleared
> > 3) Candidate
> > 4) Reconciled
>
> That's right.  Only I like to add a note to each candidate and reconciled
> transaction to pair it
I'm not sure that I would allow you to alter the entry while it is 
"reconciled". However, I would assume that you are happy to do it in the 
"candidate" state.

> > >From a user's POV, it is convenient to be able ...

> Except that when I reconcile, I match up the transactiuons with the
> statement one at a time and check them off on the paper statement.  If I
> Converted I would lose track of which lines on the paper statement had been
> matched.
Then you should not use the "shortcut". In my case, my bank statement has 
cheque numbers in sorted order. I can quickly scan the list and locate any 
exceptions. As a result, assuming that all cleared items are candidates is 
much quicker for me.

>
> > I don't think that it is necessary to attempt to remember the complete
> > text. Transaction(cheque) identifier(number) and amount should be
> > adequate to identify most transactions once they have been matched the
> > first time.
>
> Except a lot of the transactions I get don't have meaningful numbers on
> the bank statement -- whether .qif or paper.
OTOH, I'll bet that the "new" items are "appended" to the previous list.
Therefore, as long as I can find a "Cleared" item of the correct amount, I can
declare that it is not new and skip it. That may not be perfect, but I'll bet 
it is very close.

Re: on-line banking & qif import

[Hendrik Boom]

> On Thu, 11 May 2000, Hendrik Boom wrote:
> > > In general, I think that we must assume no correlation between importing
> > > data and reconciliation. All that we know is that each entry imported
> > > from the bank must appear in the ledger and that it has cleared the bank.
> > > A JE must progress through the following "reconciliation states" 1)
> > > Entered
> > > 2) Cleared
> > > 3) Candidate
> > > 4) Reconciled
> >
> > That's right.  Only I like to add a note to each candidate and reconciled
> > transaction to pair it
> I'm not sure that I would allow you to alter the entry while it is 
> "reconciled". However, I would assume that you are happy to do it in the 
> "candidate" state.

With Quicken, I often change reconciled entries.
Wjat I never do is change the amounts.  It's not umusual for me
to find out more information about an entry after it is cleared
and reconciled (for example, who it was really paid to, when I finally
find my chequebook at the back of the kitchen utensil drawer.  I wonder how
it gets there??)

Re: on-line banking & qif import

[Richard Wackerbarth]

On Sun, 14 May 2000, Hendrik Boom wrote:
My question was
> > "With respect to reconciliation, when is the 'payee' field considered to
> > be reconciled?"
>
> I guess I missed your point because I don't understand what it means
> to reconcile a 'payee' field.

One aspect of reconciliation is the immutability of the entry. When do we 
declare that we have verified that the Payee is correct and prohibit ordinary 
alterations to it.

Re: on-line banking & qif import

[Hendrik Boom]

> 
> As for changing "reconciled" transactions, it is unclear to me what 
> relationship exists between the "transaction" and the "JEs".

It is the JEs that get reconciled.

> 
> Each JE would get reconciled separately. Therefore you could have a 
> transaction transferring funds from one account to another with one entry 
> reconciled and the other still pending. Changing the "Payee" would not affect 
> the JEs. However, changing the date could.

I occasionally do change the date when I find the date on which I actually
wrote the cheque (the bank only gives cleared dates).  However, in Gnucash,
this would probably not be necessary -- I would merely be providing extra
information when I find the chequebook.

Re: on-line banking & qif import

[Richard Wackerbarth]

On Sat, 13 May 2000, Hendrik Boom wrote:
> > On Fri, 12 May 2000, Hendrik Boom wrote:
> > > > As for changing "reconciled" transactions, it is unclear to me what
> > > > relationship exists between the "transaction" and the "JEs".
> > >
> > > It is the JEs that get reconciled.
> >
> > Right. But to what does the "Payee" belong?
>
> I presume whoever you wrote the cheque out to.

I think that you missed by point. My question was
"With respect to reconciliation, when is the 'payee' field considered to be 
reconciled?"
Remember that a "transaction" has to be reconciled multiple times, once for 
each of the JE splits that make up the transaction.

Re: on-line banking & qif import

[Richard Wackerbarth]

On Fri, 12 May 2000, Hendrik Boom wrote:
> > As for changing "reconciled" transactions, it is unclear to me what
> > relationship exists between the "transaction" and the "JEs".
>
> It is the JEs that get reconciled.

Right. But to what does the "Payee" belong?

> I occasionally do change the date when I find the date on which I actually
> wrote the cheque (the bank only gives cleared dates).

Which brings us to a slight problem. For tax purposes, I must use the date 
written. That can make it difficult to match up with the file from the bank.

Do we need to store both a transaction date and the posted date?

Re: on-line banking & qif import

[Hendrik Boom]

> On Fri, 12 May 2000, Hendrik Boom wrote:
> > > As for changing "reconciled" transactions, it is unclear to me what
> > > relationship exists between the "transaction" and the "JEs".
> >
> > It is the JEs that get reconciled.
> 
> Right. But to what does the "Payee" belong?

I presume whoever you wrote the cheque out to.
Payees are not necessarily in one-to-one correspondence with the
accounts at the other ends of transactions.
(a) The payee might be the name of an agent for whomever you are paying.
(b) the other account might be more like a Quicken category than a payee,
perhaps "Automobile expense" instead of "Jim's gas station".

> 
> > I occasionally do change the date when I find the date on which I actually
> > wrote the cheque (the bank only gives cleared dates).
> 
> Which brings us to a slight problem. For tax purposes, I must use the date 
> written. That can make it difficult to match up with the file from the bank.
> 
> Do we need to store both a transaction date and the posted date?
> 

I thought we already did store both dates.

-- hendrik.

Re: on-line banking & qif import

[Glen Ditchfield]

I'd like the looser rules for matching imported entries with existing entries. 

The .qif files I get from my bank contain descriptions like 
"CHQ#00452-0041093240" for a cheque.  I suppose the digits after the dash are
some sort of tracking number; at any rate, when I type the cheque into
Gnucash I can't predict what they will turn out to be. 

ATM withdrawal descriptions look like "GM W/D000915".  In this case I
do know what the trailing digits will be, but I don't want to type them or the
spaces, because I'm lazy.

Of course, for cheques and even some ATM withdrawals, the date in the .qif file
won't be the date on which I wrote the cheque or made the withdrawal.

For my purposes, I think I would like an existing entry in an account to match
an imported entry if 
- the amounts match,
- the existing entry's description is a prefix of the imported entry's
description, and
- the existing record's date is on or before the imported record's date.

Re: on-line banking & qif import

[Christopher Browne]

On Mon, 15 May 2000 21:18:16 CDT, the world broke into rejoicing as
Glen Ditchfield <[EMAIL PROTECTED]>  said:
> I'd like the looser rules for matching imported entries with existing
>entries. 

Ouch.  I'm starting to get a tad concerned about how "fuzzy" this matching
is starting to get.

> The .qif files I get from my bank contain descriptions like 
> "CHQ#00452-0041093240" for a cheque.  I suppose the digits after the dash are
> some sort of tracking number; at any rate, when I type the cheque into
> Gnucash I can't predict what they will turn out to be. 
> 
> ATM withdrawal descriptions look like "GM W/D000915".  In this case I
> do know what the trailing digits will be, but I don't want to type
> them or the spaces, because I'm lazy.
> 
> Of course, for cheques and even some ATM withdrawals, the date in the
> .qif file won't be the date on which I wrote the cheque or made the
> withdrawal.

Indeed.

> For my purposes, I think I would like an existing entry in an account
> to match an imported entry if 
> - the amounts match,
> - the existing entry's description is a prefix of the imported entry's
> description, and
> - the existing record's date is on or before the imported record's date.

The problem is that this starts leading us towards the possibility of the
software deciding to throw out transactions because they _appear_ similar.
THAT would be a bad thing.

This sort of suggests that the system shouldn't quietly drop the
transactions, but rather list them in parallel [e.g. - side by side] and
provide the option of doing some combination of:
  a) Drop the one on the books in favor of the one being loaded,
  b) Drop the one being loaded,
  c) Keep both,
  d) [This starts getting questionable...] Merge data for the transactions
 together, say pulling all the non-blank fields from the input file
 in to replace what's in GnuCash.
--
If you  ever drop your  keys into a  river of molten lava,  let'em go,
because, man, they're gone.
[EMAIL PROTECTED] - 

Re: on-line banking & qif import

[Bill Gribble]

Christopher Browne <[EMAIL PROTECTED]> writes:
> Ouch.  I'm starting to get a tad concerned about how "fuzzy" this matching
> is starting to get.

Well, as long as the user has to put a "stamp of approval" on the
matches, I think it's OK.  I'm currently working on an overhaul of the
QIF code that will support this as an optional step in the import.  I
don't think the heuristics will be that hairy... really the amount
plus a date window is a fairly unique identifier.

> This sort of suggests that the system shouldn't quietly drop the
> transactions, but rather list them in parallel [e.g. - side by side] and
> provide the option of doing some combination of:
>   a) Drop the one on the books in favor of the one being loaded,
>   b) Drop the one being loaded,
>   c) Keep both,
>   d) [This starts getting questionable...] Merge data for the transactions
>  together, say pulling all the non-blank fields from the input file
>  in to replace what's in GnuCash.

I think (d) is an important option.  Often the bank has some
information (tracking numbers, etc) that you don't have when you enter
the transaction, and you have memos and such that the bank doesn't
have.

Bill Gribble

Re: on-line banking & qif import

[Gary Oberbrunner]

As a lurker on this list, I just wanted to say this "fuzzy QIF import"
with duplicate recognition would be the most *wonderful* feature.
Better even than OFX (ok, about equal to it in importance for me).

I enter all my checks manually, and anything large (so I have a
general idea how I'm doing), but I tend to let the tons of little $20
ATM and EFT-type transactions at the grocery store slide until the
monthly reconcile, then I enter them all from my bank statement
(semi-painful).  It would be *terrific* to be able to import them and
not duplicate all the stuff I've hand-entered.  I would be very happy
clicking something like "ignore (dup)", "import", "merge with..." or
"edit then import" for each txn.

I would use it weekly (more or less), with Richard's method of not
completing the reconciliation, saving that for the monthly paper
statement; presumably that would be a trivial process since I'd
already have all the right transactions in gnucash!

My bank can create a download qif of an arbitrary transaction period
(up to 3 months) with any amount of overlap from previous downloads.
You specify begin and end date.

(Wow, maybe I could even write guile code to go to my bank's web site,
get the last week's QIF data, and start the import process -- TOO
COOL!)

Fuzzy matching (asking me questions when match is not obvious) will be
important for me.  Ideally I could also have a hook to write
transformation rules (e.g. all ATM withdrawals go into "Cash
Withdrawal" subaccount or something like that).  The rules should run
before the matcher gets a crack at it, I'd think.

Again, you folks are the greatest!  As soon as I get my new machine up
and dual-booting 98/Linux, gnucash is going to be my first install!


> "RW" == Richard Wackerbarth <[EMAIL PROTECTED]> writes:

  RW> On Thu, 11 May 2000, Linas wrote:
  >> If I understand this correctly, and we did qif-based reconciliation,
  >> it would work as follows:
  >> 
  >> -- randolph goes to bank web site, makes note of the banks current 
  >> balance, and downloads a qif.
  >> -- he powers up gnucash, and picks 'reconcile-qif' from the menu.
  >> -- we suck in the qif file, and try to find any transactions in it that
  >> are not already recorded in the register.  we pop up a window, saying
  >> 'do you want to add these transactions to your account?' and allow
  >> user to add them one by one, or regject them one by one, till they're
  >> done.  We may want to help them idntify potential duplicates (e.g.
  >> dates, amounts which are identical, but payee feild is different, or
  >> dates that are same, payee field slightly misspeled, and amounts
  >> slightly off ...)
  >> -- when they're done with above, then we pop up the reconmcile window,
  >> and from there, things proceed as normal reconciles do ...
  >> with one 'minor' difference: instead of having 'y/n' in the reconcile
  >> column, we might have a yellow 'm' for 'maybe' or 'c' for
  >> candidate/confirm', and the 'c's' got marked that way because
  >> we marked them when the QIF came in.  randolph has only to click on
  >> yellow c's to turn them into green y's to get full reconciled.
  >> (and there would be a yellow subtotal, showing hopefully yellow $0.0
  >> which turns green at the end ... )

  RW> Yes, that is generally how I see it working. However, we should
  RW> recognize that we may not want to actually finish the
  RW> reconciliation. That might need to wait for the printed
  RW> statement to arrive.

-- 
. . . . . . . . . . . . . . . . . . . . . . . . .
Gary Oberbrunner[EMAIL PROTECTED]
GenArts, Inc.   Tel: 617-492-2888
8 Clinton StreetFax: 617-492-2852
Cambridge, MA 02139 USA http://web.genarts.com

Re: on-line banking & qif import

[Richard Wackerbarth]

On Fri, 12 May 2000, Hendrik Boom wrote:

> > I'm not sure that I would allow you to alter the entry while it is
> > "reconciled". However, I would assume that you are happy to do it in the
> > "candidate" state.
>
> With Quicken, I often change reconciled entries.
> What I never do is change the amounts.  It's not umusual for me
> to find out more information about an entry after it is cleared
> and reconciled (for example, who it was really paid to, when I finally
> find my chequebook at the back of the kitchen utensil drawer.  I wonder how
> it gets there??)
The pack rats must have put it away. Do you have any mouse traps? :-)

As for changing "reconciled" transactions, it is unclear to me what 
relationship exists between the "transaction" and the "JEs".

Each JE would get reconciled separately. Therefore you could have a 
transaction transferring funds from one account to another with one entry 
reconciled and the other still pending. Changing the "Payee" would not affect 
the JEs. However, changing the date could.

Perhaps we should require the user to "unreconcile" a period if he needs to 
change something that might affect the numbers.

Re: on-line banking & qif import

[Hendrik Boom]

> On Sat, 13 May 2000, Hendrik Boom wrote:
> > > On Fri, 12 May 2000, Hendrik Boom wrote:
> > > > > As for changing "reconciled" transactions, it is unclear to me what
> > > > > relationship exists between the "transaction" and the "JEs".
> > > >
> > > > It is the JEs that get reconciled.
> > >
> > > Right. But to what does the "Payee" belong?
> >
> > I presume whoever you wrote the cheque out to.
> 
> I think that you missed by point. My question was
> "With respect to reconciliation, when is the 'payee' field considered to be 
> reconciled?"

I guess I missed your point because I don't understand what it means
to reconcile a 'payee' field.

> Remember that a "transaction" has to be reconciled multiple times, once for 
> each of the JE splits that make up the transaction.
>