Re: on-line banking qif import
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
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
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
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.
Re: on-line banking qif import
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
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
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
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
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
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
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
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
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
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
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
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
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
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
