Trouble getting QFX File to work correctly.

jjkeely

I downloaded a QFX file from my bank that should have contained all transactions from 05/2018 when I opened the account until today. I tried both importing the file and opening the file and the PAYEE field is cut off short, the MEMO field is a duplicate of the PAYEE but, not truncated and no categories were auto assigned by Quicken. This was about 4900 records. I wanted to have all transactions that occurred in that account in Quicken. If I set up my bank account with a new blank file and SYNCH it to my bank account only a couple of months at best are down loaded and categorized. Does anyone have a work around for this so I can get all the transactions into Quicken?

Chris_QPW

I'm not exactly sure what to tell you because my test results are different from your, but the first thing to check is if these preferences that I have highlighted are selected.


When you are starting out these are the ones that make "guesses" for you for the payee and categories.

In a brand new file I tried this with importing an old QFX file and it guessing both the payee name and category. I even changed one to push the payee name to the memo to test that part, and it still worked (my financial institution doesn't muck up the payee names like yours does, I underlined the downloaded/QFX payee and memo, you can right click on the image below and open it in a new tab to see it at full size).


About the only other thing I can think of is that there is for whatever reason Quicken feels that the data in the name is better than what can suggest.

Might be in interesting experiment to change one of the names to something  like I did "test here" and import that into a new account and see if will translate what is in the memo.

studpup

Sorry you're having difficulty! 

Congrats on years of data, that's more than most banks can download. :-)

First, maybe you're only seeing part of the Payee field & use the mouse to make the field wider to see the entire field. Or maybe the bank truncates it and that's all there is.

Second, you have to categorize a category the first time and then either manually "Memorize the transaction", or have preferences set to "automatically memorize new payees". it doesn't have a secret idea what every payee is, it just works off your previous history. https://help.quicken.com/display/WIN/About+the+way+Quicken+suggests+categories+and+payees

The memo field was also part of the download, so maybe the bank just sends the same information as the payee field again.

jjkeely

No the PAYEE filed is actually truncated. I had already widened that column. Although I can take an empty file and synch it to my bank the information shows up perfectly except that it is only 90 days worth of data but, does actually categorize most all the entries. Very frustrating problem.

I agree the bank likely copies the PAYEE data to MEMO data but, MEMO data is not truncated.

I appreciate your input.

Chris_QPW

By the OFX standard the payee name can't be more than 32 characters.  The financial institution should put the most important parts first, but they don't always do that.  On the on other hand the OFX standard allows for 256 characters in the memo.

You are going to need a list of the payees that you need to fix.  Run a report on the payees and save the report to a file.

You then need to do is get to a state before you did that import.
Restore from a backup.  If you don't have such a backup there are other methods, but that is the easiest.   You can't download just delete the transactions and reimport because Quicken records the unique Ids for the transactions imported, and will ignore the transactions again if they are the same unique Ids.

Then what you need to do is create renaming rules to fix up the payees.  Renaming rules look at both the downloaded payee and the downloaded memo to decide what to change the payee name to.  After that is done you can import the transactions and the renaming rules will fix them up.

The other possible way to deal with this is just go to the the register and type Ctrl+H that will allow you to search and replace for a given payee name.  But note that you will still need the renaming rules to fix the payees coming in, in the future.

jjkeely

Chris , many thanks for your enlightening comments. Yes that explains why PAYEE is truncated. Luckily I am a new Quicken user so I have the luxury of trying different imports and then if they don't work deleting all the data files and starting over. I would really love to be able to download all the data for the account which goes back to 5/2018. There are about 4900 lines involved.

I had also downloaded all my bank data for this account into a Excel csv file and through text manipulation and formulas got the Payee field boiled down to just text. I have not tried importing the csv version of this file.

My bank was kind enough to contact their vendor and see what they could do. I am told they made some change but, I do not see it reflected in the results.... same issues.

If I go in and set up my bank to synch to Quicken - no issues at all except it only goes back a few months (maybe 6).

Think I am just screwed here or is the csv worth a try?

I am kinda surprised others have not had this issue.

Many thanks again on your input and help!!

Chris_QPW

jjkeely said:

I had also downloaded all my bank data for this account into a Excel csv file and through text manipulation and formulas got the Payee field boiled down to just text. I have not tried importing the csv version of this file.

Quicken will not import transaction in CSV format, but you can use this program I wrote to convert them to QFX.  There is an extra step to this after you setup the column mapping.  The default is QIF but when importing with QIF it just puts the transactions into the register without going through the memorized payees to setup the categories.
http://www.quicknperlwiz.com/

Another approach might be using a good text editor to change the QFX file directly.
But since you have the Excel done that would probably be the best approach.

jjkeely said:

My bank was kind enough to contact their vendor and see what they could do. I am told they made some change but, I do not see it reflected in the results.... same issues.

It might be that the changes will only affect new transactions.

jjkeely said:

I am kinda surprised others have not had this issue.

Many thanks again on your input and help!!

I suspect they either have a financial institution that did mess up the payee names in the first place or they deal with the problem by using Quicken's search and replace for the payee names.

jjkeely

That is very kind of you Chris.

I am blessed with a bank that seems to really trying to work this out for me. I am enough of a geek to get myself into trouble but, like I said I am dealing fresh so if it doesn't work I toss it and try a different idea.

I'll try your suggestions tomorrow and see what happens.

I wondered if I should just synch with the bank for the 90 or so days they go back and then import the rest of the transactions kind of going backward until I get back to 05/2018.

The nice part is that I am oblivious of what I can and cannot do and so I try everything no matter how unlikely to work and sometimes karma comes down takes pity on me and makes it work chuckle.

Thank you for your work on your program and making it available to all for free. That is very generous.

Chris_QPW

The typical pattern is to connect and download whatever it gives and then use the QFX to back fill (making sure to update the opening balance transaction).

But in your case I would probably use the QFX file to create the Quicken account and import the transactions.  Just because you have already done what you want to change the transactions that include the last 90 days.  And then after that is taken care of select the gear icon in the upper right of the register and select Edit account details -> Online Services.  There should be a link to "improve connection" from Web Connect (importing a QFX) to either Direct Connect (preferred if you can get it) or Express Web Connect (The Intuit servers trying to download the transaction from the financial institution's website for you.  Quicken Inc pays Intuit for this service).

Chris_QPW

P.S. Each transaction in the QFX file has an unique Id.  Quicken uses them to decide if they are new or duplicates.  Provided they don't change between the QFX file and Direct Connect/Express Web Connect then you won't have to deal with duplicate transactions downloading when you make the "better connection".

In the QFX files that is the FITID field.

jjkeely

You are a rock star! Thank you again for your time. It is very much appreciated and valued.

Chris_QPW

Oh one more thought.  If you are going to do CSV to QFX then the information that Quicken uses to connect between the online account and the Quicken account might not line up and instead of being able to use "improve connection" what you might have to do is deactivate the account for downloading (on the Online Service tab), then go to the General tab and clear out the financial institution name and account number, and then go back to the Online Service tab and activate it.  And be sure to "link to the account" instead creating a new one when it gives you a dialog that asks you about it.

jjkeely

Hey Chris, would it be alright if I sent you a screen shot of what's happening on this. Unfortunately I am one of those guys who hates to give up when faced with a problem.

Chris_QPW

jjkeely said:

Hey Chris, would it be alright if I sent you a screen shot of what's happening on this. Unfortunately I am one of those guys who hates to give up when faced with a problem.

Sure you can post a screenshot here.  If there anything private be sure to mask it.

jjkeely

Chris, here is what's happening.
I imported the info in screen shot using the QFX using the web connect. There is another selection on the Quicken file import menu that says "Quicken Transfer format QXF. You are correct that the PAYEE field is being truncated.

Now if I simply go on and establish a connection to my bank account and synch it with Quicken it works perfectly but, Quicken only shows a few months. That may be bank issues I'm guessing.

Can you walk me through step by step. I'll send you a bottle or two of the best BBQ sauce you'll ever taste. Gates BBQ out of Kansas City.

Chris_QPW

Are you saying when you setup the account for downloading the payees are corrected in the download, but now if you import the QFX file?
(BTW the the QXF file is for a completely different use case, data file to data file).

What is the connection type when setting up?
It is either Direct Connect or Express Web Connect and is displayed at the top of the register.

Also it would help to look at the Downloaded Payee and Memo, which you can do by selecting this icon.

jjkeely

Okay, in this example I started fresh - no previous entries, linked to my bank account. It pulled in from 11/3/2020 to today. Perfect PAYEE names and categories mostly filled in. I believe this was a express web connect connection. This what I want but, getting the data from farther back by importing a file is the hiccup.

Chris_QPW

I'm not exactly sure what to tell you because my test results are different from your, but the first thing to check is if these preferences that I have highlighted are selected.


When you are starting out these are the ones that make "guesses" for you for the payee and categories.

In a brand new file I tried this with importing an old QFX file and it guessing both the payee name and category. I even changed one to push the payee name to the memo to test that part, and it still worked (my financial institution doesn't muck up the payee names like yours does, I underlined the downloaded/QFX payee and memo, you can right click on the image below and open it in a new tab to see it at full size).


About the only other thing I can think of is that there is for whatever reason Quicken feels that the data in the name is better than what can suggest.

Might be in interesting experiment to change one of the names to something  like I did "test here" and import that into a new account and see if will translate what is in the memo.