This tutorial shows how to convert CSV/Excel files to QFX (Web Connect) format and import into Quicken 2017. We will use CSV2QFX utility.
We will use a sample CSV file, you can find it on the product page. You can use this file as an example to try the product or to get an idea: how a CSV file should look like, so the converter processed it without problems. The converter knows many other formats, but this is more the simplest format, that you can follow.
Open a sample.csv file.
We don’t use any manual Mapping here, the converter understands the file itself. It’s great, if your CSV file has the first line, as column names, like Date, Amount, Payee, Memo, Check number, Type. All these columns the converter understands and uses them for specific fields on a QFX file or to Map your CSV file.
The QFX format does not have categories here, so Quicken offers the Renaming rules or categorization feature during QFX import. There isn’t for that, because QFX format by its history or by its definition does not the categories. Because categories are something, that you defined, not your Bank defines when it provides a QFX file. And QFX file originally downloaded from your bank. This is the whole issue with categories. You may also use CSV2QIF converter with Quicken 2017, which imports QIF files fine. There is another article with step-by-step details for CSV to QIF conversion, that shows how to convert to QIF file and import into Quicken 2017 (please visit: https://www.propersoft.net/howto/qif-import-quicken-2017). There is a difference between QFX and QIF formats. There is no good format. Some users may find QFX useful, some users find QIF useful.
You can remap your CSV file if needed. You can click ‘Review Mapping’ and reassign columns here if needed. Like if your file has Debit and Credit columns instead of one Amount column, then you would say: “Do not use” and assign the Debit and Credit columns.
If your CSV file has a separate column with words like Debit or Credit, you can also Map it as the Record Type and say: the Amount is always positive, but there is the column, that says: Debit or Credit. Then you can Map Record Type column and use also Map Amount column.
This CSV file is simple, it has one Amount column with amounts as positive and negative numbers, so the converter understands it. There is an important thing, that for some Credit Cards they supply expenses as positive numbers and the deposits of payments to Credit Card are negative numbers. You have to click ‘Change +/-’ to reverse that. If your Credit Card comes like in this example: expenses are positive and payments to the Account are negative, then you have to click the ‘Change +/-’ button once per file to have expenses negative and payments positive.
Also, don’t forget to set your Account Type on the right side.
Another setting is Target. Set Target as Quicken 2017. INTU.BID is a number of bank label. You can set it for your Bank and change it.
Search, for example, “Capital One Card Services”, which supports only Credit Card, that means when you convert the CSV to QFX you have to set the Account Type to Credit Card. Quicken will not import if you set INTU.BID to “Capital One” and Checking Account.
The default value ‘3000’ is “Wells Fargo”. You can also use “Wells Fargo”, which supports both Banks, Checking, Saving, Credit Card Account Types. You can also find your bank. Let’s use “Capital Credit Union”, for example.
You have to enter the actual Account number, that you have or you can keep the default number. If you are using multiple Accounts, please use different Account numbers, when you convert for different accounts, because Quicken remembers the account you choose for the first time, it links the Account number on the QFX file to the account you choose in Quicken. And the next time you import another QFX file with the same Account number, Quicken has the link to the chosen previous account, it will not ask you to choose the account again, it will just import into the previously chosen account.
You can keep Bank ID and Branch ID empty unless you need to set it, you can leave these value blanks. Encoding: unless you have a specific encoding, you can leave it as default “Western”. You can also set ‘Open after conversion’ when it is saved, the converter will open Quicken and ask it to import that QFX file. But ProperSoft’s suggestion is to try import manually for the first time.
Then click the ‘Convert’ button.
And create a QFX file.
Then go to Quicken. Let’s say, you have already “Checking at Wells Fargo Bank” account in Quicken. And you want to import it into this account. Click ‘Tools’ — ‘Account List’.
You see, that this account has a Transaction Download — ‘Yes’, it means that it has already the established link to import QFX files or import directly from your Bank. If you need to import into this account, you must first to disconnect. Click ‘Edit’ the account.
Click ‘Online Services’ and ‘Deactivate’.
You see, that Quicken can not edit “Checking at Wells Bank”. Downloaded transactions must be accepted into the Account register or Transaction List. What happens?
Click ‘Accept all’ transactions. Then click ‘Tools’ — ‘Account List’ — ‘Edit’ — ‘Online Services’ — ‘Deactivate’ and ‘Ok’.
You can import into a new account or if you want to import it into an existing account, it has to be disconnected first, at least for the first time. For the next QFX file the link you establish for the specific INTU.BID number, for specific Account ID or Account number, what was mentioned earlier, that’s why it is important to use the same Account number for a specific account or use different Account numbers for different accounts.
Let’s try to import that QFX file we just created. Click ‘File’ — ‘File Import’ — ‘Web Connect (.QFX) File’, not (.QXF).
Select a sample.qfx file.
We can choose ‘Create a new account’ or we can choose an existing account, it’s available here. If that account would be linked, then “Checking at Wells Fargo Bank” account wouldn’t be available here, because it already linked.
We select “Checking at Capital Credit Union”, which is fine. For this case, let’s create a new account. Click the ‘Import’ button.
So, we have a new account created, you can see transactions, you can review them, you can use Renaming Rules, that was mentioned before, you have to ‘Accept All’ of them, but you can also check Payees if you want to rename it or do any other edits you need to edit.
What also you can do?
If you have ‘’Checking at Wells Fargo Bank” account, for example, and it directly linked to your Bank, you don’t want to unlink anything, but you want to import transactions for the previous dates. You can choose any Bank in the converter, import into a new account “Checking at Capital Credit Union” in our case. Then you can select transactions — right-click — cut transactions.
Then go to ‘’Checking at Wells Fargo Bank” and right-click — paste transactions.
So, in this way, we moved transactions from the new account to ‘’Checking at Wells Fargo Bank”, and they will here.
You can delete “Checking at Capital Credit Union” later. If you have already the established account and you don’t want to change anything, you just want to import transactions for the previous dates. That’s what you do to a proxy account, you convert, you import into a proxy account, and then you cut and paste transactions under another account, and then you delete this proxy account.
Originally published at https://www.propersoft.net.