Importing .csv Files (Or Converting Them to .qfx) (into Q Mac)

nello

I’m still working on getting credit card accounts set up (as discussed in: https://community.quicken.com/discussion/7925665/set-up-and-uses-for-credit-card-account)

Unfortunately, some credit card companies provide transaction data in .csv files and .qfx files are NOT an option.

How do I import (or convert) a .csv file?

jacobs

Try those rules using "If statement name contains" instead of "If Quicken name is". 

Statement name is exactly what the financial institution sends to Quicken; Quicken name is what Quicken has changed it to based on its server-based renaming rules. 

Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)

Jon

Renaming rules don't apply to manually entered transactions, only to downloaded transactions. 

jacobs

You can't.

Unless you want to pay for a third-party utility which accomplishes it by spoofing the ID number of a financial institution which is authorized to work with Quicken. We can't discuss those products, since they cheat Quicken's systems, but if you Google converting csv to qfx, you'll see what's out there.

nello

Can transaction files in any format other than qfx be imported by Quicken for Mac?

For example, Quicken for Mac apparently supports Quicken Interchange Format (.qif) files for converting from other applications, including Banktivity or Moneydance. (See: https://www.quicken.com/support/converting-your-data-quicken-mac)

Can transactions formatted as .qif be imported to an existing Quicken for Mac file/database? Or, alternatively, can .qif be used only to create a new Quicken for Mac file/database?

Jon

You can only import a QIF as part of creating a new file.

Look at the File -> Import menu to get an idea of what you can import to your current file.

nello

Apparently Quicken for Mac can import transactions from a Mint CSV.

Can you give any idea how Quicken for Mac validates that a CSV is from Mint?

jacobs

If you're trying to spoof a Mint CSV file, I'd suggest you create a Mint account, enter two dummy accounts, enter a few dummy transactions, and then do an export to CSV to see the format Mint uses. (You might want to try to import that CSV file before you go much further, to see if the import of the Mint CSV file still works. This is the type of feature which is easily broken if Mint has changed anything in their export and Quicken hasn't made a corresponding update to their import filter.)

Jon

This thread shows the Mint CSV formatting.

jacobs

Jon said:

This thread shows the Mint CSV formatting.

There's a good chance that will work; I didn't cite that thread because it's 6+ years old, with an update that's 4+ years old, and I don't know if Mint's exports are still identical. 

Jon

I tried it just now using the sample file from that thread, it still works.

nello

@Jon @jacobs

Here is a November 2022 example that also confirms the Mint CSV format:

https://help.monarchmoney.com/hc/en-us/articles/9163024504468-Exporting-data-from-mint

jacobs

@Jon Thanks for verifying that it works.

@nello So it seems you have the solution you were looking for. 😀 Have you tried importing some data from your credit card download?

nello

@jacobs
No, not yet. I’m fishing for help to automate transforming my credit card data (ccCSV) into a Mint CSV (mintCSV) and I’m getting some nibbles.

If I do develop an AppleScript to transform ccCSV → mintCSV, is it appropriate to post it somewhere in a Quicken forum?

Thank you for all your help!

jacobs

First, I’d note that every credit card company may use a slightly different format in their CSV downloads. (The lack of any standards is part of the reason Quicken mostly does not allow CSV imports). So I suspect that your AppleScript may not work for others.

Second, I think Quicken wouldn’t want to encourage this, because some financial institutions pay a fee to Intuit to be a registered participant, and they don’t want to make it easier to bypass that infrastructure. They created the Mint-only CSV import because it could help them sell Quicken to people who wanted to move off Mint; I think if this little opening were widely exploited, they might just remove it.

Finally: AppleScript? Apple has been slowly phasing it out in favor of newer automation technologies, so who knows how long it will remain part of macOS. 😉

Chris_QPW

For what it's worth, there are converters out there (on the Internet) that do convert from CSV to QFX.  Try searching for "CSV to QFX".

jacobs

Yup, I mentioned converter software in the second post. 😀

Chris_QPW

jacobs said:

Yup, I mentioned converter software in the second post. 😀

Oops.  

nello

The format of data exported from Mint as a CSV file is (apparently):
Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes

I have questions about how Quicken validates and uses the data in these fields during an import:

Date
- Can it be empty?
- Are leading zeros required? In other words, is ‘4/1/2022’ valid or must it be ’04/01/2022’?
- Must the century portion be four digits?

Description,Original Description
- Can either or both be empty?
- Which one does Quicken actually use for the value of Quicken’s Payee field?
- Are they both required or may one be empty? In other words, is there any reason to have duplicate values in them (as I see in examples)?

Transaction Type
- Is there an assumed value if it is empty?
- Is any value valid other than ‘credit’ or ‘debit’?

Amount
- Can it be empty?
- Are any signs, e.g., ‘+’, ‘-‘, or ‘$’, permitted or required?
- Must it always contain a decimal point followed by two digits? In other words, is any of these valid: ‘1’, ‘1.’, or ‘1.1’?

Category
- Can it be empty?
- What happens if it is empty or does not match any existing Quicken Category? For example, does Quicken ask the user to select or create a valid Category and then continue with the import?
- If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Category fields?
- During the import, does Quicken apply the Payee Quickfill Rule, if any, based on the value of Description (or Original Description)?- If so, is a Rule applied only when Category is empty, or does the Rule overlay whatever is already in Category?

Account Name
- Can it be empty?
- What happens if it is empty or does not match any existing Quicken Account? For example, does Quicken ask the user to select a valid Account and then continue with the import?
- If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Account fields?

Labels
- Can it be empty?
- What does does Quicken do with this data? In other words, which Quicken field is the data put into?

Notes
- Can it be empty?
- Is this value used for the Quicken Memo/Notes field?


Thank you for your patience with my tedious questions.

Jon

(this post was in reply to nello's questions which somehow got moved after this post)

Account Name, Labels & Notes can all be empty - the sample file in the thread I linked to above shows a transaction like that. Account name is probably only used in case of a transfer, they would need to know where the money is going to or coming from.

Why on earth would the date or amount fields be empty? Seems to me like those two would be the bare minimum required.

It seems like most of these questions could be easily answered by simply trying them out for yourself. 

nello

UPDATE:

I am experimenting with this and will post my findings.


ORIGINAL POST


The format of data exported from Mint as a CSV file is (apparently):
Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes

I have questions about how Quicken validates and uses the data in these fields during an import:

Date
- Can it be empty?
- Are leading zeros required? In other words, is ‘4/1/2022’ valid or must it be ’04/01/2022’?
- Must the century portion be four digits?

Description,Original Description
- Can either or both be empty?
- Which one does Quicken actually use for the value of Quicken’s Payee field?
- Are they both required or may one be empty? In other words, is there any reason to have duplicate values in them (as I see in examples)?

Transaction Type
- Is there an assumed value if it is empty?
- Is any value valid other than ‘credit’ or ‘debit’?

Amount
- Can it be empty?
- Are any signs, e.g., ‘+’, ‘-‘, or ‘$’, permitted or required?
- Must it always contain a decimal point followed by two digits? In other words, is any of these valid: ‘1’, ‘1.’, or ‘1.1’?

Category
- Can it be empty?
- What happens if it is empty or does not match any existing Quicken Category? For example, does Quicken ask the user to select or create a valid Category and then continue with the import?
- If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Category fields?
- During the import, does Quicken apply the Payee Quickfill Rule, if any, based on the value of Description (or Original Description)?- If so, is a Rule applied only when Category is empty, or does the Rule overlay whatever is already in Category?

Account Name
- Can it be empty?
- What happens if it is empty or does not match any existing Quicken Account? For example, does Quicken ask the user to select a valid Account and then continue with the import?
- If Quicken prompts for a value upon encountering an empty value, then will it use the same response automatically for all subsequent empty Account fields?

Labels
- Can it be empty?
- What does does Quicken do with this data? In other words, which Quicken field is the data put into?

Notes
- Can it be empty?
- Is this value used for the Quicken Memo/Notes field?


Thank you for your patience with my tedious questions.

nello

Whenever I File → Import a mint csv file, Quicken creates a new Account with the name of the file (including the extension).

How do I import a mint csv file into an existing Account?

Jon

nello said:

How do I import a mint csv file into an existing Account?

That might not be possible. Since importing a Mint file would be something you do only once (when switching from Mint to Quicken) it might just be hardwired to create a new account for each csv file. Even if you give the file the same name as an existing account it creates a new account anyway.

nello

> @Jon said:
> Even if you give the file the same name as an existing account it creates a new account anyway.

Yes, that’s what I found too.

I guess that I misunderstood the comments from @jacobs I thought he was saying that I could import Mint CSV data just like a QFX file; it would add the transactions to an existing Quicken Account, not create a new one.

Is there any way to merge two accounts, the imported “account” and an existing real account?

Jon

Just drag & drop the transactions from the imported account into the existing account.

nello

> @Jon said:
> Just drag & drop the transactions from the imported account into the existing account.

Excellent; that works fine.

Still one more question …

I see my credit card statements have long names that I’d like to create a rule that renames them to shorter Payees that I have already following the instructions on this page:

https://www.quicken.com/support/renaming-rules-past-and-future-transactions-quicken-mac

Attached are screenshots of both my rename Rule as well as a register in which it is NOT firing.

What am I doing wrong? How can I get the rule to fire?

Thank you.

Jon

Are those transactions that were part of the Mint import? I'm not sure those rules would apply to that.

jacobs

Try those rules using "If statement name contains" instead of "If Quicken name is". 

Statement name is exactly what the financial institution sends to Quicken; Quicken name is what Quicken has changed it to based on its server-based renaming rules. 

Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)

nello

> @Jon said:
> Are those transactions that were part of the Mint import? I'm not sure those rules would apply to that.

Yes, but I created a new Rule (companyName2 → CompanyName3) and it doesn’t fire when manually entered in either a normal credit card or normal checking account.

jacobs

Renaming rules are for downloaded transactions; they don't work on manually-entered transactions. I think Quicken assumes that if you're entering the transaction manually, you'll give the Payee the name you want. Renaming is for changing the Payee name upon download of transactions from an online source.

And I agree with @Jon that it's unlikely they would have built the Mint import feature to use the rules. For one thing, the Mint import code was written before the addition of Renaming Rules, and I doubt they went back and re-wrote the Mint import code. Also, I'd guess they figured that whatever you had in Mint is what you'd want to have in Quicken; they wouldn't want to go changing your Payee names. Keep in mind that the Mint import was built for the specific purpose of being used one time for users migrating their data from Mint to Quicken. 

From your ongoing posts here, I think you'd likely be better served by one of the commercial converters which do CSV to QFX.

Jon

Renaming rules don't apply to manually entered transactions, only to downloaded transactions. 

nello

> @jacobs said:
>
> Also, you may be aware, but these renaming rules apply to new transactions as they are downloaded; they won't affect existing transactions in your database. (If the latter is what you're trying to do, post back and we can let you know how to tackle that.)

What I’m trying to do is change the names when importing a mint.csv. Unfortunately, I don’t see any way to map imported CSV data to Quicken’s “Statement Payee” column. No, data in the mint.csv column “Original Description” maps to nowhere (that I can find).

But my testing in the post to which you replied was for a Rule with manually entered data, so I guess my testing (as opposed to my intended use) falls in to “the latter” as you called it.

jacobs

nello said:

What I’m trying to do is change the names when importing a mint.csv. Unfortunately, I don’t see any way to map imported CSV data to Quicken’s “Statement Payee” column. No, data in the mint.csv column “Original Description” maps to nowhere (that I can find).

Think about the intended use of the Mint import. If I had been using Mint for 5 years, and now decided to switch to Quicken, I wouldn't;t want Quicken mucking up my 5 years of historical transactions; I'd want it to import it as is. And that's what I expect the import it doing.

You're trying to use the Mint import for ongoing transaction downloading, something it wasn't built to do. (Remember, Quicken doesn't want to support CSV for transaction imports.) So while you can get it to work by getting a CSV file in the right format, it's not going to behave like a regular Quicken transaction download/import. Thus the suggestion to look into a file conversion program. Or lobby your financial institution to provide QFX downloads.