QIF import problem ANZ vs Westpac

[Gregmcki]

I'm about to move my accounts from ANZ to Westpac.

The ANZ QIF export format is:

D11/06/03 [date]
T-32.19 [amount]
PMOBIL [payee]

The Westpac QIF export format (they seem to have a V6 and V7 which is a
different date format, either provides)

D17/09/03 [date]
MDEPOSIT - [payee]
T1.00 [amount]
LDEP [category?]

I'm using V9, has the QIF format changed since V7 or is Westpac export
funtion incorrect. The result is that in all transactions that
Payee/Category is mixed up!

Any Advice ?

ott

[John Pollard]

Gregmcki wrote: I'm about to move my accounts from ANZ to Westpac. The ANZ QIF export format is: D11/06/03 [date] T-32.19 [amount] PMOBIL [payee] The Westpac QIF export format (they seem to have a V6 and V7
which is a different date format, either provides) D17/09/03 [date] MDEPOSIT - [payee] T1.00 [amount] LDEP [category?] I'm using V9, has the QIF format changed since V7 or is
Westpac export funtion incorrect. The result is that in all
transactions that Payee/Category is mixed up!

There is only one QIF specification and it has not changed;
different financial institutions (and different software) may
use that specification differently. Some uses of the
specification are more user friendly than others; if you want
the more user friendly form of the specification, you must
either convince the fi to change their software or change
financial institutions.

The only significant difference between the two formats you
posted is that Westpac is putting the payee in the memo field
(record) - they are not the first, or the only, fi to do that.
I would definitely contact them about this, as it should be a
relatively trivial matter to either make the "M" record into a
"P" record, or leave the "M" record as is and add a "P" record
with the same payee data. Many downloaded transactions will
never get the payee name correct; the bank, for example, does
not record the payee on your checks, so they can not download
it. And even when the payee name is "correct" (typically in a
download for your credit card, for example, where they do record
the payee name), it may not be spelled or capitalized the way
you want it, or it may contain extraneous data; so downloaded
payee names are a crapshoot anyway.

Many, if not most, downloaded transactions do not supply a
category since it is next to impossible for the fi to know what
category you would use for a given transaction. You can ask the
fi to leave the category blank, though that will only help if
you have a memorized transaction in Quicken with the correct
category - otherwise you will still have to manually categorize
the transaction, and I see little difference between changing a
blank category to the correct category and changing an incorrect
category to the correct category.

Of course, QIF is a very old spec and it is the most ineffecient
way to download data to Quicken. Intuit is attempting to reduce
the use of QIF files, or eliminate them altogether, so I would
be looking for a financial institution that offerred direct
downloads.


--
John Pollard
j underscore pollard at mindspring dot com
Please reply to newsgroup

[Mark McDonough]

For the Westpac banks download qif file

Reading your example below - I gave this a try and it still put the payee in
the memo field. Does anyone have any other suggestions how to fix this
within the file itself. I gave the MDEPOSIT change to PDEPOSIT and it still
puts the payee info in the memo field and not the payee field




"John Pollard" <invalid@hotmail.com> wrote in message
news:OZZab.147$vS.117@newsread3.news.pas.earthlink.net... Gregmcki wrote: I'm about to move my accounts from ANZ to Westpac. The ANZ QIF export format is: D11/06/03 [date] T-32.19 [amount] PMOBIL [payee] The Westpac QIF export format (they seem to have a V6 and V7 which is a different date format, either provides) D17/09/03 [date] MDEPOSIT - [payee] T1.00 [amount] LDEP [category?] I'm using V9, has the QIF format changed since V7 or is Westpac export funtion incorrect. The result is that in all transactions that Payee/Category is mixed up! There is only one QIF specification and it has not changed; different financial institutions (and different software) may use that specification differently. Some uses of the specification are more user friendly than others; if you want the more user friendly form of the specification, you must either convince the fi to change their software or change financial institutions. The only significant difference between the two formats you posted is that Westpac is putting the payee in the memo field (record) - they are not the first, or the only, fi to do that. I would definitely contact them about this, as it should be a relatively trivial matter to either make the "M" record into a "P" record, or leave the "M" record as is and add a "P" record with the same payee data. Many downloaded transactions will never get the payee name correct; the bank, for example, does not record the payee on your checks, so they can not download it. And even when the payee name is "correct" (typically in a download for your credit card, for example, where they do record the payee name), it may not be spelled or capitalized the way you want it, or it may contain extraneous data; so downloaded payee names are a crapshoot anyway. Many, if not most, downloaded transactions do not supply a category since it is next to impossible for the fi to know what category you would use for a given transaction. You can ask the fi to leave the category blank, though that will only help if you have a memorized transaction in Quicken with the correct category - otherwise you will still have to manually categorize the transaction, and I see little difference between changing a blank category to the correct category and changing an incorrect category to the correct category. Of course, QIF is a very old spec and it is the most ineffecient way to download data to Quicken. Intuit is attempting to reduce the use of QIF files, or eliminate them altogether, so I would be looking for a financial institution that offerred direct downloads. -- John Pollard j underscore pollard at mindspring dot com Please reply to newsgroup

[John Pollard]

Mark McDonough wrote: For the Westpac banks download qif file Reading your example below - I gave this a try and it still put the payee in the memo field. Does anyone have any other suggestions how to fix this within the file itself. I gave the MDEPOSIT change to PDEPOSIT and it still puts the payee info in the memo field and not the payee field

It is not clear to me what you did. Can you supply an example from your
file?

[John Pollard]

Mark McDonough wrote: See the attached files. What I did was download the qif file from Westpac (attached) and saved it as a csv file for viewing in Excel. All the payees were preceded by the letter "m" for example: mamazon.com for amazon.com Assuming the m stood for "memo" I ran a formula in Excel that would change the m's to 'p's Once done, I resaved the file as a QIF file and imported it into Quicken. This change had no affect.The payees continued to be put in the memo field instead of the payee field. In both instances (before and after), the payee field contained the category "other"

You are correct that a QIF record with an "M" in the first position contains
the data for the Quicken "Memo" field. And a QIF record with a "P" in
column one, contains the data for the Quicken "Payee" field. (And records
with an "L" in postion one contain the data for the Quicken "Category"
field).

I looked at your attached file, "26102003wv.QIF" and I see that the memo
field does appear to contain the names of payees, and there is no field for
payee. The "category" field (an "L" in position one), contains the word
"OTHER" for every transaction. (I do not understand all the commas in every
record?)

I also looked at your attached file "Test File.txt"; I am not sure what this
file represents, but it does not have *any* payee records in it either; the
payee names are still in memo records. If this is the output of your macro,
your macro did not work.

(Just a note: the date formats in your QIF file are not correct for Q2002
for Windows, US; but I do not think that is involved in your problem. And
they may be perfectly correct for the Australian version).

I have to say that the QIF specs are ancient, Quicken has been successfully
importing data in that format forever, and I have never heard of a failure
such as the one you are describing. Granted, it appears, you are using a
different country version than I am and it is certainly possible that the
version you are using could have such a bug in it; but I would certainly
have thought that others would have reported it here too, if it really were
doing as you claim.

I would take your downloaded file, make a copy of it, delete all but one
transaction, manually modify the "M" record to have a "P" in column one, get
rid of all the commas, and import that into Quicken. If that puts the QIF
category field into the Quicken payee field, then you have me stumped
(except see my last comment).

I did something similar to the above with the file you sent me.
1.) made a copy
2.) deleted all but the first transaction
3.) left the commas in the remaining transaction (to see what effect they
had)
4.) imported into test Quicken file

This produced a "valid" transaction in Quicken with a blank payee field and
with the memo field containing, what appears to be, the payee name ... just
as one would expect.

I then modified the "M" in position one to a "P" in the QIF file; imported
again, and got the payee name in the Quicken payee name field. Again, just
as I would have expected.

One thing you might want to lookout for is if you have memorized a
transaction with a blank payee or with whatever Quicken is comparing to in
the QIF file; if you have Quicken automatically filling in fields based on
memorized transactions, it is possible that this is interfering with your
import.

[Mark McDonough]

Thanks for going to the trouble to work out what was going on. It's late so
I will put your advice into action tomorrow.

You are right about the date - stupid really but Australia puts dd/mm/yy
format.

Not good for looking up dates in a hurry.

I'll let you know how I get on. Could be the excel formula.


"John Pollard" <willnotworkatall@hotmail.com> wrote in message
news:tLSnb.91720$5n.59744@bignews5.bellsouth.net... Mark McDonough wrote: See the attached files. What I did was download the qif file from Westpac (attached) and saved it as a csv file for viewing in Excel. All the payees were preceded by the letter "m" for example: mamazon.com for amazon.com Assuming the m stood for "memo" I ran a formula in Excel that would change the m's to 'p's Once done, I resaved the file as a QIF file and imported it into Quicken. This change had no affect.The payees continued to be put in the memo field instead of the payee field. In both instances (before and after), the payee field contained the category "other" You are correct that a QIF record with an "M" in the first position
contains the data for the Quicken "Memo" field. And a QIF record with a "P" in column one, contains the data for the Quicken "Payee" field. (And records with an "L" in postion one contain the data for the Quicken "Category" field). I looked at your attached file, "26102003wv.QIF" and I see that the memo field does appear to contain the names of payees, and there is no field
for payee. The "category" field (an "L" in position one), contains the word "OTHER" for every transaction. (I do not understand all the commas in
every record?) I also looked at your attached file "Test File.txt"; I am not sure what
this file represents, but it does not have *any* payee records in it either;
the payee names are still in memo records. If this is the output of your
macro, your macro did not work. (Just a note: the date formats in your QIF file are not correct for Q2002 for Windows, US; but I do not think that is involved in your problem. And they may be perfectly correct for the Australian version). I have to say that the QIF specs are ancient, Quicken has been
successfully importing data in that format forever, and I have never heard of a failure such as the one you are describing. Granted, it appears, you are using a different country version than I am and it is certainly possible that the version you are using could have such a bug in it; but I would certainly have thought that others would have reported it here too, if it really
were doing as you claim. I would take your downloaded file, make a copy of it, delete all but one transaction, manually modify the "M" record to have a "P" in column one,
get rid of all the commas, and import that into Quicken. If that puts the QIF category field into the Quicken payee field, then you have me stumped (except see my last comment). I did something similar to the above with the file you sent me. 1.) made a copy 2.) deleted all but the first transaction 3.) left the commas in the remaining transaction (to see what effect they had) 4.) imported into test Quicken file This produced a "valid" transaction in Quicken with a blank payee field
and with the memo field containing, what appears to be, the payee name ...
just as one would expect. I then modified the "M" in position one to a "P" in the QIF file; imported again, and got the payee name in the Quicken payee name field. Again,
just as I would have expected. One thing you might want to lookout for is if you have memorized a transaction with a blank payee or with whatever Quicken is comparing to in the QIF file; if you have Quicken automatically filling in fields based on memorized transactions, it is possible that this is interfering with your import.

[Mark McDonough]

Don't know what I did wrong the first time but it WORKED .............doing
a test with one transaction. I am only now setting my Quicken account up now
and not doing retrospective expenses via download so we'll see what happens
from here.

Thanks heaps for your help


"Mark McDonough" <mjmcdono@bigpond.net.au> wrote in message
news:Hltob.107046$Of.15260@news.easynews.com... Thanks for going to the trouble to work out what was going on. It's late
so I will put your advice into action tomorrow. You are right about the date - stupid really but Australia puts dd/mm/yy format. Not good for looking up dates in a hurry. I'll let you know how I get on. Could be the excel formula. "John Pollard" <willnotworkatall@hotmail.com> wrote in message news:tLSnb.91720$5n.59744@bignews5.bellsouth.net... Mark McDonough wrote: See the attached files. What I did was download the qif file from Westpac (attached) and saved it as a csv file for viewing in Excel. All the payees were preceded by the letter "m" for example: mamazon.com for amazon.com Assuming the m stood for "memo" I ran a formula in Excel that would change the m's to 'p's Once done, I resaved the file as a QIF file and imported it into Quicken. This change had no affect.The payees continued to be put in the memo field instead of the payee field. In both instances (before and after), the payee field contained the category "other" You are correct that a QIF record with an "M" in the first position contains the data for the Quicken "Memo" field. And a QIF record with a "P" in column one, contains the data for the Quicken "Payee" field. (And
records with an "L" in postion one contain the data for the Quicken "Category" field). I looked at your attached file, "26102003wv.QIF" and I see that the memo field does appear to contain the names of payees, and there is no field for payee. The "category" field (an "L" in position one), contains the word "OTHER" for every transaction. (I do not understand all the commas in every record?) I also looked at your attached file "Test File.txt"; I am not sure what this file represents, but it does not have *any* payee records in it either; the payee names are still in memo records. If this is the output of your macro, your macro did not work. (Just a note: the date formats in your QIF file are not correct for
Q2002 for Windows, US; but I do not think that is involved in your problem.
And they may be perfectly correct for the Australian version). I have to say that the QIF specs are ancient, Quicken has been successfully importing data in that format forever, and I have never heard of a
failure such as the one you are describing. Granted, it appears, you are using
a different country version than I am and it is certainly possible that
the version you are using could have such a bug in it; but I would certainly have thought that others would have reported it here too, if it really were doing as you claim. I would take your downloaded file, make a copy of it, delete all but one transaction, manually modify the "M" record to have a "P" in column one, get rid of all the commas, and import that into Quicken. If that puts the
QIF category field into the Quicken payee field, then you have me stumped (except see my last comment). I did something similar to the above with the file you sent me. 1.) made a copy 2.) deleted all but the first transaction 3.) left the commas in the remaining transaction (to see what effect
they had) 4.) imported into test Quicken file This produced a "valid" transaction in Quicken with a blank payee field and with the memo field containing, what appears to be, the payee name ... just as one would expect. I then modified the "M" in position one to a "P" in the QIF file;
imported again, and got the payee name in the Quicken payee name field. Again, just as I would have expected. One thing you might want to lookout for is if you have memorized a transaction with a blank payee or with whatever Quicken is comparing to
in the QIF file; if you have Quicken automatically filling in fields based
on memorized transactions, it is possible that this is interfering with
your import.