Auto-categorization of downloaded transactions using the 'Memorized Payee List' and 'Memo' field

pkeegan

My problem:

I have many downloaded transactions from my banking account with Payee=WITHDRAW and Payee=DB DEBIT.

The 'Memo' field contains text that identifies the actual payee (e.g. "MARKET BASEKET77" or "USPS PO 24329804"). From this, the proper category can be determined.

I would like to auto-categorize these transactions based on text in the 'Memo' field.

I tried doing this using the 'Memorized Payee List'. This works for a single match, but it doesn't allow for multiple values in the 'Memo' field, to set the proper category.

Is there another solution?

Quicken September

Hello, @pkeegan! 

Thank you for reaching out to the Community with your question, though I am sorry for any trouble. There is no way to set a rule based on the memo field. Rules and automatic categorization are only able to use the Payee field as the subject of a rule. 

Given how you have described this system that you have, the most effective (albeit still manual) way to edit this information would be to navigate to the search bar in the top right of your register labeled Search all visible columns, search for the payee in the memo field - bringing up all of these transactions, and then highlighting all of the transactions and editing them all en masse. 

You may also be interested to know that there is a column dedicated to designating types of transactions. Whereas you are using the Payee field, if you navigate to the Columns option in the lower right of your register, you may select the Action column which is typically where users would denote what type of transaction they're dealing with (ATM, CASH, CHECK, CREDIT, DEBIT... etc). You may want to check out this Community post to read more about Actions. 

It may not be exactly the answer you were looking for, however, I hope you find this helpful nevertheless. If you have any other questions or require assistance, please reach out as we are always happy to help. Thank you and have a great day!

-Quicken September

jacobs

@pkeegan It sounds like your bank is actually encoding information incorrectly. In the OFX standard for transmitting banking data, the payee name and memo field are distinct, and they really should be putting the payee name in the payee name field rather than the memo field.

To make sure this is the bank's problem, click on one of these transactions and open View > Show Inspector. Look at the bottom section of the window that opens, and look at Statement Payee and Statement Memo; these show exactly what the bank is transmitting. If the Statement Payee is "Withdraw" or "debit" and the Statement Memo is the name of the actual Payee, then you have to take this complaint to the bank because their OFX output is coded incorrectly; there's no way to automatically fix this in Quicken. 

pkeegan

@Quicken September I found a 'Register columns' icon, but it has a checkbox for 'Action buttons', not 'Actions'. The former is already checked, but I see no column with that heading. If I uncheck it, nothing changes. Your suggestion for using Search->select transactions and editing the category may come in handy. Thanks.

@jacobs There is no View->Show Inspector choice when I select a transaction. Is there a debug mode to getting this enabled? Thanks.

pkeegan

@jacobs I searched Q support for 'inspector'. It seems to be a feature of the MAC version, but not Windows.

Quicken September

Hello, @pkeegan

Thank you for reaching out to us with your update. For clarity, are you a Quicken for Mac user or do you use Quicken for Windows? This post has been made in the Quicken for Mac category so these instructions have been Mac specific. That would explain some of the confusion, here. Thank you for your patience!

-Quicken September

jacobs

@pkeegan There is no such thing as a "debug mode" in Quicken Mac. Are you saying that if you click on a transaction, and you pull down the View menu, you don't see Show Inspector as the third menu choice? 

pkeegan

@Quicken September: I use Quicken for Windows. Sorry, I didn’t realize I was in the wrong forum category.

pkeegan

@jacobs: Sorry, I posted this in the wrong forum. I use Quicken for Windows, which does not have the ‘Show inspector’ tool. I will reach out to my bank to see if they can fix the payee problem.

Quicken September

Hello, @pkeegan.

Thank you for your clarifying update! I do see that the post has been moved to the appropriate category. This should help avoid future confusion. Thank you for your patience and have a great day!

-Quicken September

Chris_QPW

What is the financial institution and what is the connection type?

EDIT for Direct Connect you can look at this log: Help -> Log File -> OFX Log
For Express Web Connect it gets much more confusing, but in both cases, you should be able to turn on the Downloaded memo column to see at least what Quicken Windows thinks it got.


As for categorizing by what is in the memo.  If you select "If payee contains" in the Renaming rule it actually looks in the memo too.


Once you have it renamed to a unique Payee you can use that payee in the Memorized Pay List to categorize it.

pkeegan

@Chris_QPW:
The financial institution is: DCU (Digital Federal Credit Union)
The connection type is: Express Web Connect
There is nothing in the OFX log file for DCU.
I added 'Downloaded payee' and 'Downloaded memo' to the columns, and they are the same as the 'Payee' and 'Memo' columns.

Regarding the 'Renaming Rules', you said it will match the Memo field, too. Do you know if it will do a partial match? For example, if the rule has 'Market Basket', will it match 'MARKET BASKET77' in the Memo field?

Is the 'Renaming rule' processed before the 'Memorized Payee List' during downloading? I wish I could test this somehow before waiting for downloads, but this looks like a good solution to my problem.
Thanks very much.

pkeegan

@Quicken September: I didn't change the category to Windows, but I'm glad someone did (I wonder who?). Thanks.

Chris_QPW

pkeegan said:

Regarding the 'Renaming Rules', you said it will match the Memo field, too. Do you know if it will do a partial match? For example, if the rule has 'Market Basket', will it match 'MARKET BASKET77' in the Memo field?

Unfortunately, no, "contains" breaks up the test on words.  Market yes, Basket no.
EDIT:  And just to be inconsistent it seems; Quicken Name does match partials.

pkeegan said:

@Chris_QPW:
Is the 'Renaming rule' processed before the 'Memorized Payee List' during downloading? I wish I could test this somehow before waiting for downloads, but this looks like a good solution to my problem.

Yes, renaming rules are run as the transaction is download (before they go into either the Downloaded Transactions tab or into the register is automatic transaction entry mode is on).  It does make it a pain because you can only test renaming rules on a new download.

The way I test is get a QFX file and edit it.  One can keep changing the FITID to make it import the same transaction again.

Here is an example with just one transaction in it.

OFXHEADER:100

DATA:OFXSGML

VERSION:102

SECURITY:NONE

ENCODING:USASCII

CHARSET:1252

COMPRESSION:NONE

OLDFILEUID:NONE

NEWFILEUID:NONE

<OFX>

<SIGNONMSGSRSV1>

<SONRS>

<STATUS>

<CODE>0

<SEVERITY>INFO

</STATUS>

<DTSERVER>20110221120000[0:GMT]

<LANGUAGE>ENG

<FI>

<ORG>ISC

<FID>10898

</FI>

<INTU.BID>10898

</SONRS>

</SIGNONMSGSRSV1>

<CREDITCARDMSGSRSV1>

<CCSTMTTRNRS>

<TRNUID>1

<STATUS>

<CODE>0

<SEVERITY>INFO

<MESSAGE>Success

</STATUS>

<CCSTMTRS>

<CURDEF>USD

<CCACCTFROM>

<ACCTID>XXXXXXXXXXXXXXXX

</CCACCTFROM>

<BANKTRANLIST>

<DTSTART>20100821120000[0:GMT]

<DTEND>20110221120000[0:GMT]

<STMTTRN>

<TRNTYPE>DEBIT

<DTPOSTED>20210111120000[0:GMT]

<TRNAMT>-7.61

<FITID>2021011105410191010255900923945d

<NAME>Safeway

<MEMO>TEST PAYEE77

</STMTTRN>

</BANKTRANLIST>

<LEDGERBAL>

<BALAMT>-3091.73

<DTASOF>20110221120000[0:GMT]

</LEDGERBAL>

<AVAILBAL>

<BALAMT>2386.00

<DTASOF>20110221120000[0:GMT]

</AVAILBAL>

</CCSTMTRS>

</CCSTMTTRNRS>

</CREDITCARDMSGSRSV1>

</OFX>

pkeegan

@Chris_QPW: I don't see a way to create a QFX file export from my bank. Would editing your sample work if I changed the bolded values?

Chris_QPW

pkeegan said:

@Chris_QPW: I don't see a way to create a QFX file export from my bank. Would editing your sample work if I changed the bolded values?

Yes, it will work.  Note for testing create or link to a new account.

pkeegan

@Chris_QPW:
I'm testing with your QFX sample on a new Quicken file, and it imports successfully - thanks.
In the 'Renaming Rules', what is the difference between 'If Quicken name is' and 'If payee contains'?
I observed that if I use the former, it doesn't do the partial match on the memo field, but it does match using the latter rule, as in your screen shot.

Chris_QPW

I have never seen a definition of exactly what each means, so I can only go on what I have seen when testing.  It seems like Quicken name will do partial matching, but "payee contains" will not.  And it seems that Quicken name doesn't look in the memo, but "payee contains" does.

Chris_QPW

Actually, I should have looked in the Help, I did this before, but found it somewhat lacking, but it does fill in some parts:

pkeegan

@Chris_QPW
I was testing renaming rules using 'Quicken name'. Apparently, it does look in the Memo field. If Quicken finds a match in its database, I can change the payee's name, but not the category. Instead Quicken assigns its own category.

For example:
Renaming rule: if Quicken name = 'market basket', rename payee = 'MyStore'
Memorized payee list: Payee name = 'MyStore', Category = 'Food & Dining:Groceries'

When using this QFX data:
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20210112120000[0:GMT]
<TRNAMT>-7.62
<FITID>11116
<NAME>WITHDRAW
<MEMO>market basket
</STMTTRN>

The resulting transaction has Category = 'Food & Dining:Coffee Shops', which is different than the rules
. The Quicken help could use some clarification about matching text and interactions with memorized payees.

Thanks for showing me how to test downloads.

Chris_QPW

Thanks, it is good to know that Quicken Name looks in the memo field.

No matter what the renaming rule ends up for the payee, it should still be matched to what is in your Memorized Payee List to pick up the category.  Note the payee's name has to be exact, with the exception of the character case.

Note Automatically categorize transactions is a very important preference.
If Quicken gets through process and doesn't match a memorized payee it will then take a guess.  This is fine when you are first starting out, but after you have most everything setup I find it much more helpful for it to be left blank so that when I'm reviewing transactions it stands out, and I can set it and then memorize the new payee.


The Apply Quicken's suggested name generally works pretty well.  Basically, if you don't have a renaming rule that applies it will guess at the payee, and that usually to "clean it up" from things like Safeway #$#$# to just Safeway.  If the payee's name is close to a common one, but isn't that one, that is where you have to use a renaming rule to correct it.  Again, generally a good preference for starting out, but less used as you get everything setup.

pkeegan

@Chris_QPW
That's a good tip on setting the downloaded transactions preferences.

I just tested my original problem in this topic using a new Quicken file with no changes to renaming rules or memorized payees. In all cases, Quicken entered the correct payee and a reasonable category. In one case, it presented a 'Create Custom Renaming Rule' dialog that allowed me to select specific words from the downloaded payee name which was the text from the Memo field! This is exactly what I was hoping for. However, I've been using Quicken since 1993 and I have lots of renaming rules and memorized payees, so I'll have to think about the effects of removing these.

Chris: I'm not getting email notifications even with all notification preferences set for email. Do you know who I should contact about this?

Chris_QPW

I flagged your post for the moderators to talk to you about the email notification problem.  They are the ones that would work on fixing that.

Quicken Anja

Hello @pkeegan,

I have sent you a direct message, please navigate to the inbox in the top right-hand corner of the Community page and check your inbox.

Thank you!

pkeegan

@Chris_QPW
I deleted 450 memorized payees and 60 renaming rules that have been accumulating over the past few decades (most appear to have been created automatically). I set the 'download transaction' preferences as per your screenshot. I downloaded a bunch of new banking transactions, but the behavior is not the same as I hoped to see from my testing on a new Quicken file (see my Jan 3 post).

Quicken is not using its database to determine the payee and category, nor is it showing me the 'Create Custom Renaming Rule' that allows me to choose words from the Memo field, as in the attached screenshot.

Any suggestions on how to get Quicken to behave the same way on my real data file as the test file?

Chris_QPW

For the renaming dialog to come up the following is required.

1) Both of these preferences on:


2) No existing renaming rule that matches.
3) You change a payee's name in a transaction that has just been downloaded.

I tested this on my data file, and it worked fine.

For the renaming rules to apply the above "Use my existing renaming rules" has to be on.

As for it using the memorized payees, as long as the payee matches it should be using the category from that memorized payee entry.

If that isn't working for you, I'm not sure what is going on.  Is this only for this account or for the whole data file?  Renaming rules and memorized payees are global, so if testing another dummy account with a QFX file import works there might be something wrong with that account.  On the other hand, if it doesn't work at all in the data file, there might be something wrong with your settings or the data file.  For that case you should probably try Validate and Repair.

pkeegan

@Chris_QPW
I set the preferences for downloaded transactions as you show, except I left unchecked 'Automatically add to {banking, investments}...'. No memorized transactions or renaming rules exist. My bank account behaved as before. However, I downloaded the test QFX file, and that credit card account did work as expected in my real Quicken data file. So, maybe there's a problem with the bank account.

I restored from a backup created before downloading the new transactions and did a 'Validate and a Repair'. It fixed some transactions in my bank accounts dated from 1995-2015, and flagged some securities with prices dated in the future (hmm). Now, it appears that Quicken is generating a reasonable payee for some tranactions (but not categories, probably because my list differs from the one in my test data file). However, Quicken doesn't present the 'Create Custom Naming Rule' dialog as I saw in the test data file. I think I can live with this, though.

Thank you for your help. I really like having this QFX testing tool. I see that I can have Quicken generate a QFX file using Preferences/Web Connect. I enabled it, but it doesn't give me the option to save to file when I download transactions via Express Web Connect.

Chris_QPW

These options don't generate QFX files.


They are for what happens when you import a QFX file.
The first one is really more about where the QFX file is saved.  If I download a QFX file from the financial institution's website, it might go into the temp directory, "not be saved".  This just gives an option to save it somewhere else as part of the process.