2qfx Convert Pro | Help

2QFX Convert Pro

This Help file covers 2QFX Convert Pro, 2QFX Convert Pro+, 2QFX Convert Pro+ Express, and the PDF+ AddOn for working with scanned documents.

Getting Started

2QFX Convert Pro is an integrated financial data translator to extract financial transactions from CSV, Excel, QIF, QFX, and downloaded PDF statements and convert them into QFX format suitable for Quicken® software. 2QFX Convert Pro+ adds PDF+, MoneyThumb’s integrated text recognition module to handle scanned PDF statements. See the section on Working with Scanned Documents and PDF+ for information regarding PDF+.

Use 2QFX Convert Pro to import transaction data into Quicken when you downloaded statements from your financial institution or created the data in a spreadsheet program such as Microsoft Excel®.

To get started first set your Quicken account information and date formats with the Settings button. It is important to select an FID. If your bank is not listed, then it is not supported by Quicken, and you need to select the FID of some other bank as a workaround. See more about FID’s below. You can also select options for data handling and PDF options – see the section on Settings below.

Then select the Convert button to choose a file to convert. This will bring up a standard file chooser, and allow the selection of .csv, .pdf, .qif, ..qfx, xls, and .xlsx files. There are three action buttons, plus the cancel button, at the bottom of the file chooser. One of more of them may be grayed out depending on which type of file is selected. Use the Preview button to preview how .csv and .pdf files will convert, and to assign and verify which column is which before doing the import. Then select Create qfx to create the output file.

Once you have converted a file with Preview mode, use the Convert to qfx button to do one-step conversion of similar .pdf , .csv, .xls, or .xlsx files directly to QFX format, suitable for input into Quicken. You can also use the Convert to csv button to convert .pdf .qif, or even other .qfx files into CSV format for intermediate editing.

Whenever opening a file from a different bank or that has a different style, first use Preview Mode to verify the column setup. When in Preview Mode, use the pull-down list at the bottom of each column to select the correct type of information in that column. Be sure to select one Date column, one Payee column, and either one Amount column or both Credits and Debits columns. If you have a balance column, the column selection should be blank (to ignore it). You can also choose which transactions to convert. See more about Preview Mode below.

If the 2QFX Convert Pro log has an entry that the conversion did not find separate credit/debit sections, then check the plus/minus sign of the entries in the Preview window. If they need to be flipped, then select the checkbox for Switch signs of amounts on output and the amounts will be correctly output to your QFX format file.

Installation

  • Microsoft Windows® full install
    • Download 2QFXPro.exe for Windows, save the file to your computer, and run the installation program by double clicking the file.
    • If you do not have have Java installed it will be automatically downloaded during the installation.

 

  • Mac OS X® full install
    • Download 2QFXPro.dmg for Mac OS X, save the file to your computer. Locate the file in the download area, open it by double clicking, then and run the installer.app by double clicking it.
    • If you do not have have Java installed it will be automatically downloaded during the installation.

 

  • Portable Installation
    • Download 2QFXPro.zip and save the file to your computer.
      • If running on Mac OS X the unzip will be done automatically as part of the download, and 2QFXProPortable.jar should be in your user download folder.
      • If running on other operation systems run zip or winzip on 2QFXPro.zip and extract 2QFXProPortable.jar to a suitable folder such as C:\Program Files\MoneyThumb.
    • Make sure you have Java installed on your computer. If you do not have Java already installed, download it for free at www.java.com.

Entering License Information

On Microsoft Windows, the easiest way to enter the license is to copy the license file 2QFXPro.lic from the product confirmation e-mail to the same folder where you installed 2QFX Convert Pro – i.e. C:\Program Files\MoneyThumb\2QFXPro.

Otherwise enter the license by copying the license string (CTRL-C) from the confirmation e-mail and pasting it (CTRL-V) into the license dialog. To enter the license string manually from within the program select the License button, and paste (or type) the full license code into the dialog.

After you enter your license, your license email will be shown in the program title bar, and in About.

Preparation

There are two things to do before running 2QFX Convert Pro:

  1. Prepare your input file by downloading a PDF statement or a csv file from your bank or brokerage web site, or by creating your data in a spreadsheet program. PDF files are often identified with the red PDF or Get Adobe Acrobat logos. If you are creating data in Microsoft Excel, you can save the file as either a .csv file, a .xls file, or a .xlsx file. The advantage of .xls or .xlsx is that each worksheet can contain a set of transactions. The first row of the worksheet needs to have the name of the data contained in that column – see Appendix A for a list of recognized names.
  2. Get the account number of the Quicken account into which you want to import transactions. If you are creating a new account, then any number will suffice. If you wish to import transactions into an existing account, then Quicken will match up the account numbers, and you will want to import into the correct account.

Running 2QFX Convert Pro

On Windows or Mac OS X, double click the 2QFX Convert Pro icon on your desktop.

You may also run 2QFX Convert Pro from the Windows Start Menu, or run 2QFXPro.exe on Windows or 2QFX Convert Pro.app on Mac OS X.

If you are running the portable version, run 2QFXProPortable.jar by double clicking it, or starting it as a Java program.

Settings

Use the Settings button to bring up the Settings dialog:

2qfx Convert Pro Settings


Setting Quicken Account Info

First use the Account Type pull-down menu to select the correct type for the output file -Bank, Credit Card, or Brokerage. You can output either QFX or QIF format files for Quicken. All current versions of Quicken accept QFX files. Windows versions of Quicken also accept QIF files, which can be useful to import categories or tags, and the ability to import QIF format does not get ‘expired’ by Quicken.

For QFX there are four pieces of account information that may be inserted into the QFX file when it is created. The first of these – the Quicken FID – is absolutely critical. The FID is a four or five digit number that Quicken uses to identify the financial institution. Quicken will validate the FID over the Internet when reading in the QFX file, and it must be correct and valid, or Quicken will abort the file import.

You can access the Quicken Account Info from either the main Settings dialog, or the Preview Mode Screen. Set the Quicken account information with the Settings button. This will bring up the dialog below. First, select the Account Type as Bank or Credit Card in the drop down list.

Select “Lookup…” in the dialog and it will bring up a window with all the Quicken Financial Institutions that are listed for that type of account. Type some or all of the name of your financial institution into the search box at the top to search within the list. Scroll down the list to find your bank and select it. The bank URL is also included to help resolve ambiguity if your bank has a name that is similar to other banks.

If your bank is not listed, then Quicken will not import QFX files (Web Connect files) that are identified as being from that financial institution. The only workaround is to use an FID from some other bank. You can still name the account to include the name of your financial institution. So long as you as not also doing Quicken Direct Connect downloads into the same account, it will work just fine. There are some Financial Institutions that provide Direct Connect, but do not support Web Connect, and if you use one of these, you will have to create two accounts and move transactions from one to the other.

Just remember that the FID is critical, and although 2QFX Convert Pro will run without one, it will give a warning, and Quicken will give an error when reading the QFX file.

QFX files are required to have account information. All files require an account number and bank accounts also require a bank routing number. If you don’t want to save your accounts numbers for security reasons, then you can skip entering this this information. If you do provide your account number to be inserted into the QFX file, then Quicken uses that number to automatically determine which account to import into. If you are always importing into the same account, then 2QFX Convert Pro will save the information from session to session, so you do not have to re-enter it. For PayPal users, your account number is the email address you use to sign into PayPal. Note that 2QFX Convert Pro does not access the Internet at all, so any information entered is only saved on your computer, and is not sent over other web or to any other computers.

To determine the account number to use in the QFX file, 2QFX Convert Pro will look in the following locations, in order.

  1. Values from the Settings dialog window (see above). Once again, if you are concerned about entering your account number, then don’t, and either manually edit the QFX file after it is created, or match up accounts when doing the Quicken import.
  2. The input file name, if it is a number without any letters.
  3. As a last resort, 2QFX Convert Pro will use an arbitrary default number for the routing number and account number. You will then have to manually match accounts when reading the QFX file into Quicken.

The bank account routing number is required by QFX for bank accounts (but not for credit card accounts). However, it is not actually used by Quicken, so if you don’t specify one, 2QFX Convert Pro will insert a default value and Quicken will accept it.

Lastly, the currency needs to be specified. US Dollars are the initial setting, use the drop down to select a different currency.

For creating QIF files, things are a lot simpler – just specify the name of the account in Quicken where the import will be done. This needs to be an exact match, although it is not case sensitive.

PDF Settings

PDF Password

If your PDF statements have a password that you need to enter in order to view them, then use the setting for Set PDF Password. The password is not saved for security reasons, so you need to enter a password each time you start 2QFX Convert Pro. However, if you are converting multiple statements that require the same password, the password will be applied to multiple conversions in the same session.

PDF Page Range

If your PDF statement has multiple accounts (such as a checking and a saving account) you can restrict the pages converted so that only a single account is processed. Enter values into the dialog for Only Convert Pages from … to to specify a page range. The first value is the page number of the first page to convert, the second value is the page number of the last page to convert. All other pages will be ignored.

Year

Many banks simply use the month and date for individual transactions, with the year being elsewhere on the statement. 2QFX Convert Pro
determines the calendar year from other dates in the statement. If for some reason the year is not found correctly, this setting allows you to override the year which is used. For statements that appear to cross a year-end boundary, this value is the earlier year.

The Spacing Factor is for PDF Statements that have extremely wide or narrow text. 2QFX Convert Pro will automatically determine a good value, but if your initial conversion has extra spaces where they shouldn’t be, or no spaces where they should be, this is a way to override the calculated value.

Page Width is used when the PDF Statement has two columns for transactions but has extra text outside the viewable area that is causing the second column to be unrecognized. Use this value to override the page width manually, for example to 8.5. The value is always in inches.

Transaction descriptions need alphabetic characters is normally on. Turn this off if the converter is not recognizing transactions that only have a number as the transaction description or payee.

Allow dates without any separator can be used for banks that use a month-day format of ‘mmdd’, without any space, dash, or slash between the month and day. Setting this option may cause non-date values such as check numbers to be interpreted as dates, so use with caution.

Process statement as a single currency column

This option is used very rarely, but is for the case where your PDF statement has different columns of currency values, but the different columns do not identify debits versus credits. This would be evident when converting the statement in Preview mode. Most statements with multiple columns have one column for credits, one for debits, and perhaps another for balances. This is what the converter expects. However, if your bank created statements with different columns that do not identify debits vs credits then you would need to turn on this option. The only known bank that does this is PNC, where one column is used for checking withdrawals and another for check card debits. When this option is on, all the currency values will be assumed to be in a single column and the converter will rely on the section names or plus/minus values to distinguish credits versus debits. If your bank only has one column of currency values, then this option makes no difference.

Always run PDF+ text recognition (OCR)

Use this option when your statement may already have text from a previous OCR process. See the section on PDF+ for additional information.

Advanced PDF Options

These options provide a great deal of control over the PDF conversion, but must be used with care. They are designed to guide the PDF conversion, improve accuracy, and make clean Payee names for Quicken.

Most of the options use text strings to specify types of transactions or sections in the PDF Statement. These text strings all use a concept called ‘regular expressions’, which is extremely powerful, but admittedly hard to understand if you have never done them before. If one is trying to match a line like “Checking Account Transactions”, then the regular expression is straightforward – it would just be “Checking Account Transactions”. To match the remaining characters in a string use “.*” so that another way to match the previous line would be “Checking Account.*”. A web site that has a reference, and enables you to enter the text from your PDF Statement and test regular expressions to ensure they do what you expect is at http://regexpal.com/.

The Advanced PDF Options dialog has many tool-tips, and hints for the type of entry for each field. Since 2Qbo Convert Pro already recognizes a large vocabulary of terms for the type of text found in most PDF Statements, it would be unusual to have to create custom entries for section names in the statement. However, if you have a statement that is not in English, then it will be very useful.

PDF Section Identification Strings are used to identify sections in the statements and thereby decide whether a transaction is a credit or debit, whether a section contains a list of checks, or whether a section has balances and should be ignored. Multi-account Identification Strings are used if your PDF Statement has multiple accounts in a single statement, and you wish to limit the conversion to the transactions for one account. Page numbers are one way to limit which data is converted, but sometimes the accounts are not on page boundaries. Entries that should be Skipped strings are for entries that could be taken for a transaction, but should be skipped – things such as totals and balances. Once again, 2QFX Connect already has a large vocabulary of terms, but if your bank uses with something different, this is a way to quickly handle that.

The Spacing Factor is for PDF Statements that have extremely wide or narrow text. 2QFX Convert Pro will automatically determine a good value, but if your initial conversion has extra spaces where they shouldn’t be, or no spaces where they should be, this is a way to override the calculated value.

Page Width is used when the PDF Statement has two columns for transactions but has extra text outside the viewable area that is causing the second column to be unrecognized. Use this value to override the page width manually, for example to 8.5. The value is always in inches.

Transaction descriptions need alphabetic characters is normally on. Turn this off if the converter is not recognizing transactions that only have a number as the transaction description or payee.

Allow dates without any separator can be used for banks that use a month-day format of ‘mmdd’, without any space, dash, or slash between the month and day. Setting this option may cause non-date values such as check numbers to be interpreted as dates, so use with caution.

Process statement as a single currency column

This option is used very rarely, but is for the case where your PDF statement has different columns of currency values, but the different columns do not identify debits versus credits. This would be evident when converting the statement in Preview mode. Most statements with multiple columns have one column for credits, one for debits, and perhaps another for balances. This is what the converter expects. However, if your bank created statements with different columns that do not identify debits vs credits then you would need to turn on this option. The only known bank that does this is PNC, where one column is used for checking withdrawals and another for check card debits. When this option is on, all the currency values will be assumed to be in a single column and the converter will rely on the section names or plus/minus values to distinguish credits versus debits. If your bank only has one column of currency values, then this option makes no difference.

The Text to remove from transaction Payees section is the one you are most likely to use – it enables you to remove extra text at the beginning of a Payee name and turn transaction payees like “Check paid to John Smith” into “John Smith” and turn “Check Card 0125 Staples” into “Staples”. This greatly improves the ease of importing transactions into Quicken. Some examples:

To remove ‘Check number” or “check #’ use the text “Check number” or “check#’ respectively.

To remove “Paid on ” followed by a 4 digit number use “Paid on \d\d\d\d”

To remove “Paid on” followed by a 3-4 digit number use “Paid on \d\d\d\d?”

Some banks have lots of this text as a prefix, some have none. This option provides ten different strings that you can use to improve usability.


Date Formats

2QFX Convert Pro can read dates either in US format (month-day-year) or European format (day-month-year). Use the Settings dialog to select the date format that is used in your .pdf or .csv file. If your dates have the month name or abbreviation rather than a number, then this setting is not applicable. Note that the read date option is not necessary when reading .xls or .qfx files

In addition, you can select whether the output file should be in US format (month-day-year) or European format (day-month-year). This is only used when converting from PDF or QIF format to CSV format. The dates in .qfx files are format independent.

Positive and Negative Charges

Normally bank statements will have charges as negative numbers and payments as positive numbers. That is what Quicken expects. Many credit card companies switch things so that charges are positive – showing an increase in your balance – and payments are negative. Use the Settings dialog to select Charges are positive, Payments are negative (Switch signs) if this is how your CSV or PDF statement is formatted.

Assigned Column Names

Column names can be preassigned to your conversions. Whenever you use Preview Mode when running a PDF or CSV conversion, the column names are saved and automatically assigned to subsequent conversions. See the description of Preview Mode, in the following section. The current column names are displayed in the text box in this section.

You can also read predefined column setting from a CSV format file. 2QFX Convert Pro is shipped with a number of presets that facilitate conversion from popular CSV download sites, such as PayPal. To read presets select the Load Preset button, and use the open file dialog to open a csv file with the column names. A preset CSV file is just a single line of text with the column names separated by commas. You can also save the current displayed column names to your own preset file with Save Preset

Select the checkbox for Use column names to enable or disable the column settings.

Splitting transactions for Quicken

When reading transactions from payment systems such as PayPal, the transaction is often composed of a gross amount, a transaction fee, resulting in a net amount. The Separate Splits option specifies whether split transactions such as these create one transaction in the QFX file, or whether each split generates a new transaction in the QFX file. When transactions are split, the Quicken import will create one Quicken transaction that is the received payment amount, and another transaction that is the transaction fee. This enables you to match up payments with invoices, and to track transactions fees in a separate account. If the transaction is not split, such a transaction will be imported as a single transaction with the net amount. See more about specifying split amounts in the description of Preview Mode, in the following section.

CSV Fixed Column Layout

Normally saving a csv file will use a column setup that mimics that found in the PDF file. Select Create CSV file with fixed column layout to use a fixed column layout which is Date, Payee, Amount, Memo, Number. This makes csv files more consistent to work with when editing in Excel or other spreadsheet programs. Note that if the columns during conversion are not recognized, the converter will enter Preview mode so that you can define the what type of date is in each column.

Saving and Restoring Settings

2QFX Convert Pro will always save all the settings from one session to the next. To save the settings for converting files for a particular account use the Save Settings button. Then to restore setting for that account use Load Settings before running the conversion. The saved settings include the Quicken account information, column information, PDF options, and Payee Cleanup options. Settings are saved in a file with a .mthumb extension and may be saved in any folder.

Converting a File

2QFX Convert Pro can be run in two modes – Preview Mode and Express Mode. If you are just getting started, then use Preview Mode for reading .csv or .pdf files. If you have run 2QFX Convert Pro previously on a similar file and are sure that the columns are correct then it’s faster to use Express Mode. Express mode can be used for all formats. To run either mode, start with the Convert button.

Preview Mode (for .csv and .pdf formats)

Preview mode is applicable when creating QFX format files from either PDF Statements or .csv files. Select the Convert button and this will bring up the file chooser dialog. Navigate to the folder containing the .pdf or .csv file, select the file, and then select Preview at the bottom of the dialog.

This will extract the transactions from your input file and bring up a Preview window that displays the transactions which were found. At the bottom of each column is a selector that contains the name of the data in that column. It may have already been set correctly by 2QFX Convert Pro based on column headings in the input file. If it’s not correct, use the pull-down to select the correct type of data. Each type can only be used in one column, so types that have already been used will be grayed out. If you have many columns, you can increase the width of the columns of interest by going to the header row and dragging the column separator to increase the width of the column, and of course drag a corner of the window to enlarge it as well. Use the Clear button to empty the column selectors and start over.

2qfx Convert Pro Preview

At the top of the Preview screen the account type is shown. If the account type is incorrect, then use the Set Quicken Account Info open up a subset of the Settings dialog where the Quicken account information may be modified.

Most credit card statements have the the signs of amounts reversed so that credits are a minus amount, and charges are positive. If separate credits and debits sections were not found, then the preview screen may show credits as negative. In this case the checkbox for Switch signs of amounts should be selected. This will ensure that debits and credits are correctly labeled in the QFX file, and imported correctly into Quicken. If you have more than ten columns, the checkbox for Hide unmapped columns will hide all the columns that do not currently have a name assigned. This is very useful for files that come from PayPal or Stripe that have too many columns for them all to be readable.

The only required columns are the Date, and depending on whether the transaction amounts are in one column or two, either Amount or both Credits and Debits. If your statement has an Amount column that has positive amounts for both credits and debits then you also need to select a Type column that has the type – typically Credit/Debit or CR/DB. If your input file has a column for ‘Balance’, it should not be used – the entry in the pull down list should be blank. Quicken will calculate the balance using all the transactions in the account.

The Payee column is the downloaded payee name for Quicken. You can also have the payee name duplicated at the start of the memo field by using the Payee & Memo selection. This is useful if you want to retain the downloaded payee name after the Quicken payee rules have been applied, or you need to see more than the QFX limitation of 32 characters for the payee name.

The Number column is used for Check Numbers, if those are present in your input file. The Transaction ID column should only be used for credit card statements that have a unique reference number for each transaction.

When the Separate Splits option is set under Settings, you can specify which columns contain amounts that are to be split. Additional column names will be displayed for Gross Amount, Transaction Fee, and Refund Amount. For these columns, if there is an amount present, it will generate its own transaction, with a payee of the column name. You can also define your own split names by using the Split<> column name. This will prompt you for the name to used for amounts in this column. Additionally, you can force the amounts in a column to be either positive or negative amounts by specifying a plus or minus sign before the split name.

To create a Memo that combines two different columns choose Memo for the the first part of the memo, and Memo Add-on for the second part. The text from the two columns will be combined into a single Memo, with a space between the two text strings.

The column called Use determines whether the transaction will be processed into the QFX file. If you have some transactions that you wish to ignore, deselect the checkbox in the Use column. Use the checkbox at the bottom to deselect or select all transactions.

When the column names are correct, select Create qfx at the bottom of the Preview window. The conversion will proceed, giving some statistics on how many lines were processed and create a .qfx file with the same name as the input file. If a .qfx file with that name already exists you will be prompted to overwrite it.

Preview Mode column settings will be automatically remembered, and will apply to subsequent Express Mode conversions. To clear Preview Mode, either run a different file in Preview mode or use the Settings button and then uncheck the checkbox for Assigned Column Names – Use column names.

Payee Cleanup

When downloading bank transactions, very often payee names will have extra information such as dates, phone numbers, store numbers, transaction type descriptions, and so forth. Use Payee Cleanup Settings to automatically remove that extraneous information. Payee Cleanup Options are normally accessed from the Preview dialog. The Cleanup dialog is side by side with the Preview dialog so that you can specify changes and instantly see the cleaned-up payee names. The cleanup filters will only affect the column selected as Payee or Payee & Memo at the bottom of the dialog.

Payee Cleanup Settings

Most of the filter options remove a specific type of text and should be self explanatory. You can also specify up to 100 phrases to be removed. Phrases can include wild cards. Use a question mark (?) to match a single character or an asterisk (*) to match multiple characters at the beginning or end of a word. Some frequently used phrases are in the hints for the first few entries. The best way to specify phrases is to start typing and see how each character that you type affects the payee names in the Preview transactions. If you have many payees you might want to expand the size of the Preview dialog and you can also scroll through the list of transactions while the Cleanup dialog is visible.
To remove phrases such as ‘PayPal *’ or ‘SQ *’ that prefix the actual payee you can either specify a string such as “Paypal ?” or “SQ ?”, or you can remove all special characters and specify a single word removal string such as Paypal or SQ. All phrase removal is case insensitive – for example PayPal, paypal, or Paypal will all remove the word paypal, regardless of how it is capitalized.

If you are a devote of regular expressions, you can also use regular expressions as a removal option. Start and end the removal string with a slash (/) to have it recognized as a regular expression.

Note that filters are applied in the same top to bottom order displayed in the dialog, so that for example a store number such as ‘#2032’ would be removed before special characters, which includes the #.

Express Mode

Express mode can be used for all conversions, although it is highly recommended that whenever converting a .csv or .pdf file from a new source, you should first use Preview mode to make sure the column setup and payee cleanup settings are correct. Select the Convert button and this will bring up the file chooser dialog. Navigate to the folder containing your input file, and select the appropriate action – Convert to csv (for PDF, QIF, or QFX files ) , or Convert to qfx (for all file types except QFX) at the bottom of the dialog. 2QFX Convert Pro will run, giving some statistics on how many lines were processed and create a the output file with the same name. If a file with that name already exists you will be prompted to overwrite it. When running Convert to qfx, if the converter detects the the current Preview settings will not work with the file being converted, it will automatically enter Preview mode so that the column can be configured correctly.

You can also convert multiple files in a single step. This should only be used when all the input files are similar, for example a years worth of statements for the same account. Hold down the Ctrl key (Command key on a Mac) while clicking on file names to select multiple files one by one. Hold down the Shift key while clicking on file names to select all files between the first click and the next click. When you convert multiple file, 2QFX Convert Pro will also create a concatenated output file (either CSV or QFX) that contains all the transactions from all the conversions. This file will have the word “Concatenated” appended to the file name of the first file converted.

If you converted a PDF Statement to .csv format, the .csv file can now be read directly into Excel or other spreadsheet program to view or edit the data. If you created a .qfx file, it is now ready to be read in Quicken – see below.

If you are reading a .xls or .xlsx file that contains more than one worksheet you will be prompted to select the worksheet to import.

Automation

To run 2QFX Convert Pro from the command line or a script simply invoke it on Windows as:

2QFXPro CONVTYPE inputfile

Or for the portable version

2QFXProPortable.jar CONVTYPE inputfile

where CONVTYPE is one of the following:

PDF2QFX, PDF2CSV, QIF2QFX, QIF2CSV, XLS2QFX, CSV2QFX

There is no need to specify an output file name, 2QFX Convert Pro will use the same name and an QFX extension. The log will be written to a file with the same name and a .log extension, or ERROR.log if the input file name is invalid.

Note that if the output file already exists, it will be overwritten. And if your input file name has any spaces in it, remember to use quotes – for example:

2QFXPro PDF2QFX “input file.pdf”

Processing payment systems downloads (PayPal, Square, Stripe…) with Presets

Payment systems such as PayPal, Square, Stripe create .csv files which contain transactions which contain additional information beyond the net amount of the sale. Most commonly the downloaded .csv file will contain columns with the gross amount of the sale, the transaction fee, and the net amount of the sale. It may also contain a column with the refund amount, if any. When importing this information into Quicken, it is often desirable to have this information as multiple transactions. For example the gross amount of the sale would become one transaction, and the transaction fee would be a second transaction. This allows the two amounts to go to different Quicken accounts, and also allows the the gross amount to be matched up with a Quicken customer invoice.

Presets in 2QFX Convert Pro make this conversion very simple.

1. Select Settings and read in the Preset file for the payment system processor with Load Preset. 2QFX Convert Pro is shipped with a number of standard preset files for common payment systems and credit cards.

2. You will also likely want to select the checkbox for Separate Splits so that individual transactions are created for each amount.

3. Select Convert and run the conversion with Convert to qfx. You will automatically have the correct columns mapped, and create a .qfx file. If you wish to verify that the column names are correct, you can instead Preview the conversion from the Open File dialog before creating the .qfx file.

You can also create your own Preset files if you wish to have different names for the transaction fee, refund, or have a payment system that has additional fields. You can save the column settings that are displayed in the dialog with Save Preset. You can also create preset files with a spreadsheet program such as Microsoft Excel that creates .csv files. The file format is just a single line with the column names, separated by commas.

Working with Scanned Documents and PDF+

This section is only applicable if you purchased 2QFX Convert Pro+ or the PDF+ AddOn. Running with PDF+ is virtually identical to running the normal version. In most cases the converter will recognize that the PDF statement does not contain readable text, and will automatically invoke the text recognition module. The main noticeable difference is that the text recognition takes much longer than files that don’t need text recognition.

When scanning documents to be processed by 2QFX Convert Pro+ it is best to scan at a resolution of 300 dpi (dots per inch). Most scanners should have this as an optional setting. And obviously the cleaner and crisper the document scans, the better the recognition will be. A speck of dirt in the wrong place, such as making a keyword like ‘Debits’ unintelligible can throw off the entire conversion.

After text recognition and conversion, the converter will also automatically refine any transaction date or amount values that appear to be incorrect with Pin Point recognition, and redo the conversion. The number of values refined will be shown in the converter log. If there are still values that appear to be incorrect, the converter log will list the number of lines that should be manually corrected. If you are creating a CSV file, they will be identified in the CSV file with an extra column containing a double question mark – “??”. Otherwise the converter will identify those lines in Preview mode. Those transactions will be highlighted in yellow, and will have the Use box unchecked.

PinPoint Correction

You can then review the text in those lines, and edit them to make any necessary corrections. Edit like you would a spreadsheet – select the cell and then edit the text in that cell. Sometimes the highlighted lines may be extraneous text that is not a transaction, simply ignore those. If you do edit the lines to create valid values, check the Use box so that they will in included in the output. You can also edit any other text, and if something is missing, even insert a new transaction using the Add Transaction button. If you don’t want to include a line, uncheck the Use box.

In the example above, there were a few transactions where the ‘g’ in ‘Aug’ was fuzzy and was recognized as ‘a’. In this instance one should change those dates to 8/25/2012 and 8/27/2012, and then the statement will be completely correct.

If you are converting scanned documents that had text recognition done by other Optical Character Recognition (OCR) software then you can choose to either use the text from the previous OCR software or use the MoneyThumb’s integrated PDF+ with Pin Point text recognition. The Settings option under Advanced PDF Options, Always run PDF+ text recognition (OCR) tells the converter to always use PDF+ text recognition. If that box is unchecked 2QFX Convert Pro+ will process those files using the searchable text created by your previous OCR. Always running with text recognition will take substantially longer, but will also generally get more accurate results than using the results from other OCR software. That is especially true when compared to free OCR software that may have come with your scanner.

Lastly, there are also a few banks that create PDF statements from images and a very few who create PDF statements with an internal encryption. Bank statements created from images should automatically be processed by PDF+, since no readable text will be found. Statements with an internal encryption will generate unusable text, so , they can only be correctly processed by using the Settings option above. You can recognize these statements by copying and pasting text from your PDF reader to any editing program, and seeing random text characters rather than the text you copied.

Importing QFX files into Quicken

The easiest way to import the .qfx file into Quicken is simply to double-click it from Windows Explorer or Max OS X Finder. You can also read the QFX file while running Quicken. Simply select File, then from the pull-down menu select Utilities, then Import, and then Web Connect File... This will bring up the standard File Open dialog, select the file, and then select Open. The transactions should be read into Quicken.

If you are using Quicken Online, then open the account into which you want to import the .qfx file. There is an Update menu in the upper right. Select the down-arrow next to Update, and then select File Upload.  Select Browse, navigate to the .qfx file on your computer, select it, and select Open, and Next. The transactions should be read into Quicken Online.

One of the advantages of using QFX file format is that Quicken ‘remembers’ which transactions it previously read, and will not read the same transaction again. If your PDF file has a transaction ID from your financial institution, then make sure that the column header is correct, and 2QFX Convert Pro will use that transaction ID. 2QFX Convert Pro will generate transaction Id’s if they are not available, but they will not match up with direct downloads from your financial institution.

Troubleshooting


Your bank is not present in the list of FID’s.

The list of FID’s (Financial Institution ID’s) actually comes from your Quicken install, and is the list of banks that have paid to be included as a Quicken supported bank. A bank may be supported for checking accounts or credit cards, or both. Similarly a bank may be supported for Direct or WebConnect downloads, or both. And the list is country specific. If your bank chose not to be included, then it will not be in the FID list and Quicken will not import transactions using that bank name. It has nothing to do with 2QFX Convert Pro. However, there is a very simple workaround which is to use the FID of some other Financial Institution. You can use pretty much any other FID you want. You should probably choose one that is not a bank you have other accounts at, is a large bank that is unlikely to drop Quicken support, and has a name that is similar to or that you can associate with your bank name.


Error Message: “No text was found in the PDF file”

This error generally means that the PDF file is a image file, not a text based (or searchable) PDF file. Image PDF’s are created when scanning, or a small minority of banks create a PDF statement with a few images rather than text. You can verify this by trying to select a line of text while viewing the PDF file in Adobe Acrobat. Depending on the type of PDF file, the selection are will either snap to a line of text, or just be a rectangle following the cursor.

Text Versus Image PDF Comparison

It is also possible that the PDF is encrypted; try to copy/past text from Acrobat to any editor and if the text does not paste correctly, then the PDF is encrypted. In either case you will need 2QFX Convert Pro+ with text recognition in order to process this file. You could also use other OCR software, although MoneyThumb’s PDF+ is unique in being optimized for recognizing financial transactions.

Error Message: “No transactions found in the PDF file”

This error can be caused by anything from the converter not working correctly on your bank’s PDF file to a PDF file that has internal encryption or images that makes it impossible to convert.

A quick test is to verify whether the text in the statement is extractable. Open the statement with Adobe Acrobat, select the text for a transaction, and use Edit, Copy to copy the text to the clipboard. Open any kind of text or document editor (i.e. Notepad, Word, TextEdit, Pages) and paste the text into the program.
If the text does not paste correctly, then it’s an image, or somehow encrypted, and the statement can only be processed with 2QFX Convert Pro+. You may need to turn on the Settings option for Always run text recognition (OCR).

If text was processed, there should be a line in the log like “Found 100 lines with a date, 90 lines with a currency value, 80 lines with both.” If this line is missing or the number of lines found is much lower than expected, then the statement has spacing, date, or currency formats that are not being recognized. One thing you can try, especially for scanned documents, is to adjust the Spacing Factor in the Settings menu to a number such as 1.5 or 2.0.

If date and currency values were found, then the formatting of the statement is likely causing a problem. If your statement has multiple sections for different accounts, that can sometimes cause confusion. It can often be corrected by only processing pages for one section of the file at a time. Enter values for Only convert PDF pages from .. to .. in the lower right of the Settings menu. You can also use the Advanced PDF Options under Settings to enter the header line for each section and specify start and end of the conversion process more exactly.

If transactions are still not being found, you would need to send a test file to MoneyThumb for further investigation. We can send you a procedure to remove personal information from the PDF statement.

Warning Message: “No separate credit/debit sections found. Verify plus/minus sign of amounts”

If the 2QFX Convert Pro log ends with this message, then 2QFX Convert Pro was unable to find distinct sections for credits and debits in the PDF statement. If your PDF statement has plus and minus signs, then you should check that they are correct and if not, use the Switch sign of amounts setting in the top right of the Preview dialog to switch all credits and debits. If you are running multiple statements from the same bank, use Settings to set the switch signs option for all conversions. If all your transactions are positive numbers, then 2QFX Convert Pro was unable to recognize the sections correctly. If your statement does have distinct credit/debit sections, then you should use use Advanced PDF options to specific the text which identifies the start of the different statement sections.

Another workaround is to run 2QFX Convert Pro twice; select all the credits the first time, and then the debits, switching the Switch sign of amounts checkbox so that the transactions are positive on the first run and negative on the second. Select the checkbox under the Use column to choose which transactions will be processed. The checkbox at the bottom of that column will select/deselect all transactions.

Lastly, PNC customers should check the option under Settings for Process statement as a single currency column.

Warning Message: “Credit/debit columns not identified. Verify plus/minus sign of amounts”

This warning is similar to the warning above regarding credit/debit sections, but 2QFX Convert Pro did find separate columns for credits and debits, just could not determine which is which. Therefore, you should simply ensure that the debits and credits columns are identified correctly in Preview mode. If the columns in your statement do not distinguish credits and debits, then you should use the Settings option for Process statement as a single currency column and use Advanced PDF Options to specify the credit/debit sections as above.


No transactions visible in Quicken when you import a QIF file

The most common reason for this is that you need to accept the transactions into the Quicken account register. In Quicken, go to the account register, select the Downloaded Transactions tab at the bottom of the register, and accept the transactions.

If there are transactions showing in the account list, but not at the bottom of the account register, you may need to modify your Quicken preferences. Under the preferences for Downloaded transactions uncheck the top checkbox on the right hand side for Automatically add to banking registers.

Transactions have an incorrect year

Most bank statements don’t have the year on individual transactions but have the year in the statement date. This is normally picked up by 2QFX Convert Pro. However if the statement date is not present or there are other dates found in the statement, sometimes all the transactions will have an incorrect year. To override the year value found in the statement, specify a year in the Settings menu, using the Year value on the lower right.


Quicken Error: “Quicken is currently unable to verify the financial institution for this download. Please try again later.

When you import the .qfx file into Quicken, if you receive an error “Quicken is currently unable to verify the financial institution for this download. Please try again later.” then the FID is incorrect. Quicken is very picky about this. The financial institution must be currently in good standing with Quicken for Web Connect download, and the account must be of the correct type. Quicken distinguishes between financial institution support of bank versus credit card transactions.

2QFX Convert Pro will only display those FID’s that are listed as being able to import Web Connect files, so if you don’t see your bank or brokerage, Quicken will not accept Web Connect files from that financial institution. Also, even though a bank may be listed, Quicken may still give this error. That problem seems mostly to occur with small banks, who perhaps are not current with Quicken.

If you are running into a brick wall with Quicken FID’s, the workaround is to use an FID for some other financial institution other than the one you actually use. However, if you already are downloading from your financial institution, then you would have to have two accounts and copy transactions from one to the other. Therefore, this is practical only if you don’t already download directly from that financial institutional, or you import transactions very infrequently.

If Quicken is refusing to link a .qfx import to an existing account, then make sure that the account type was set correctly – see the section above on Setting Quicken Account Info. Quicken will only allow you to import credit card .qfx files to credit cards accounts, and bank account .qfx files to bank accounts. If the account number was not specified accurately in the Quicken Account Info, then Quicken will generally allow you to do a manual match, but it will also change the account number in Quicken.


No transactions created when creating the QFX or QIF file

Review the log in the 2QFX Convert Pro log window. Often the cause is a missing header. Use Preview Mode to correct the column description using the pull-down menus at the bottom of each column. To enter Preview Mode, run the conversion by selecting the Preview button rather than running a one-step conversion.

Switched Information in Quicken

If you are importing the QFX file into Quicken and information is switched (i.e. the Payee is what you expected for some other field) then the headers in the PDF file may be mislabeled. Use Preview Mode to correct the column description using the pull-down menus at the bottom of each column. To enter Preview Mode, run the conversion by selecting the Preview button rather than the Convert to qfx button.

If your bank statement has one column for credits and another column for debits, then make sure that those columns are correctly labeled.

If your banks statement has all the amounts in a single column and the amounts are switched (i.e. credit card charges are showing up as positive rather than negative) then use checkbox Switch signs of amounts (in the upper right corner of the Preview Mode screen.

Payee name is being truncated

The QFX file format does not permit payee names longer than 32 characters. That can cause problems with longer payee names being truncated on import into Quicken. It is not possible to change that limitation, but 2QFX Convert Pro does have a workaround. In Preview Mode, select the column definition for the payee to be Payee & Memo. This will put the payee into both the Payee field (truncated to 32 characters), and the start of the Memo field. This will make the full payee name visible in your Quicken transactions.

In addition, if many of the payee names in your statement start with a prefix like “Checkcard payment” you can use Advanced PDF Options to remove that type of text from the payee names and thus have more meaningful payee names. This is fully described under Settings.

Multiple Accounts in a singe file

Because a .qfx file can only contain transactions from a single financial institution, 2QFX Convert Pro will only process the first account found in a PDF Statement when creating a .qfx file. To process other accounts in the PDF Statement, use either the setting for Only convert PDF pages from .. to .. or the Advanced PDF options to restrict the portion of the statement which is processed.

Saving the 2QFX Convert Pro Log

After 2QFX Convert Pro has run, you may wish to save the log information to a file. Select the Save Log button. This will bring up a File Save dialog. Simply specify a file name and select Save.

To clear the log information select the Clear Log button.


Appendix A

Spreadsheet conventions

If you want to create or edit the CSV file, this is a complete description of the conventions used by 2QFX Convert Pro.

By way of example, a simple bank account might look like this:

!Type:Bank;My bank account
Date Number Payee Cleared Amount Category Memo
5/1/2016 Opening Balance X 0 [My bank account]
5/2/2016 Deposit * 100 Gifts From David
5/10/2016 ATM ATM Withdrawal * 50 Cash
5/18/2016 INT Interest Paid This Period * 0.01 _IntInc

First Line

As an alternative to Settings dialog, the first line for account can also specify the type of account and the account name in the format:

!Type:accounttype;accountname

where accountname is the account name in Quicken, and accounttype is one of the following:

    • Bank – checking and savings accounts
    • CCard – credit card accounts
    • Invst – brokerage and investment accounts
    • Prices – lists of stock prices
    • Cat – Quicken category list
    • Security – Quicken security list
    • Memorized – Quicken memorized payee list
    • Account – Quicken account list
    • Oth L – Quicken Liability account
    • Oth A – Quicken Asset account

If the first line does not have this type of information, then CSV2QIF Convert will look at the column headers to determine the account type, and determine the account name from the user specified name, or otherwise the CSV file name.

Columns

All data must be in columns, and the columns should have the column name at the top of the column. Normally the column headers are in the second line of the spreadsheet, but CSV2QIF Convert will search until a header row is found. The order of the columns is not important, and only the columns in bold are required. Many alternate column names are used by various financial institutions, and most of them are recognized by CSV2QIF Convert. If your CSV file does not have column headers, or uses different names, then running under Preview Mode is the easiest way to assign the column names for your CSV file.

The following is the list of ‘standard’ column names and their meanings for different account types.

Bank and Credit Card Accounts

    • Date – transaction date
    • Number – check number (for bank accounts only)
    • Payee – payee or merchant
    • Payee & Memo – payee or merchant, also copied to the start of the memo field
    • Cleared – cleared status
    • Amount – $ amount of the transaction. Alternatively a credit and a debit column can be used
    • Credit – credit amounts
    • Debit – debit amounts
    • Type – transaction type, such as credit or debit
    • Category – Quicken category
    • Tag – Quicken tag
    • Memo – memo or description of the transaction
    • Memo Add-on – addition description to be added to the memo
    • Gross Amount – when splitting transactions, gross amount of transaction
    • Transaction Fee – when splitting transactions, the transaction fee charged
    • Refund Amount – when splitting transactions, amount of refund

Split transactions will be automatically created with a column header of

Split<QuickenCategory>

For example the following can be used to define a paycheck made up of a gross amount with taxes taken out.

Date Number Payee Amount Split<Gross> Split<Tax>
4/30/2016 DEP Acme Inc. 4100.00 5000.00 -900.00

The category name can be prefaced by a + or – to ignore the sign of the value in that column, and either add or subtract the value. It is also possible to add a Quicken tag with a column header syntax such as Split<QuickenCategory/QuickenTag>.

Alternatively, splits for any individual transaction will be read in columns to the right of the last column as

    • Split 1 Category
    • Split 1 Memo
    • Split 1 Amount
    • Split 2 Category
    • Split 2 Memo
    • Split 2 Amount
    • …. and so forth.

Note that this is the format created by QIF2CSV Convert. With this method, split columns do not require a header, CSV2QIF Convert will automatically check every transaction for possible splits.

For example, either of the following can be used to define a paycheck with a gross salary and taxes taken out; the split headers are optional.

Date Number Payee Amount Category Memo Split1C Split1M Split1 Split2C Split2M Split2
4/2/2016 DEP Acme 4100.00 Salary Salary:Gross Memo_1 5000.00 Salary:Taxes Memo_2 -900.00

 

Date Number Payee Amount Category Memo
4/2/2016 DEP Acme 4100.00 Salary Salary:Gross Memo_1 5000.00 Salary:Taxes Memo_2 -900.00

Up to 50 splits may be present for a transaction. If you have columns that you do not want to be considered as possible splits, use a header of ‘Ignore’ and they will be ignored.

Investment Accounts

    • Date – transaction date
    • Action – investment transaction. Supported actions are:
      • Buy, Sell, Div, CGShort, CGMid, CGLong, IntInc, ReinvDiv, ReinvSh, ReinvMd, ReinvLg, ReinvInt,
      • ShrsIn, ShrsOut, XIn, XOut, Cash, Contrib, MiscExp, MiscInc, MargInt, RtrnCap, ShtSell, CvrShrt,
      • Exercise, Expire, StkSplit, Grant, Vest, Reminder, AdjShBal, Deposit, OnlPmt
    • Security – stock name, mutual fund name, or other security name
    • Symbol – stock or mutual fund symbol
    • Cleared – cleared status
    • Quantity – number of shares in the transaction
    • Price – price per share
    • Commission – commission
    • Total – total amount (for a buy or sell should be quantity * price + commission)
    • Transfer – Quicken account name to transfer funds to/from
    • Memo – memo or description of the transaction
    • Memo Add-on – addition description to be added to the memo
    Depending on the transaction type some or all of

quantity, price

    , and

total

    may be required for any particular transaction. For example all three are needed for a Buy or Sell, only

quantity

    is needed to transfer stock in or out of an account, and only the

total

    is needed for a cash transfer.

In addition, there are many different ways that brokerages create CSV files, and sometimes required information is found the memo column. CSV2QIF Convert uses the contents of the action, the security, and the memo to determine the complete transaction. If insufficient information is found, then that transaction will be skipped, and the log will describe why.

Memorized Payees

    • Type – (P) Payment or (D) Deposit
    • Total – total $ amount
    • Payee – payee or merchant
    • Category – Quicken category
    • Tag – Quicken tag
    • Memo – any description
    • Memo Add-on – addition description to be added to the memo
    Memorized transactions may also have splits, using the same method as described above for bank accounts

Category List

    • Category Name – name of the category
    • Type – (I) Income or (E) Expense
    • Tax Related – Tax flag
    • Tax Line – Quicken tax line number
    • Description – Tax line description

Security List

    • Security Name – Name of the security
    • Symbol – Stock symbol
    • Type – valid types are: Bond, CD, Emp. Stock Opt, ESPP, Market Index, Money Market, Mutual Fund, Option, Other, Stock, US Savings Bond

Security Prices

    • Symbol – stock symbol
    • Price – the price
    • Date – date of that price

Account List

    • Account Name – name of the account
    • Account Type – Quicken account types: Bank, CCard, Port, 401(k)/403(b), Oth L, Oth A, Tax
    • Line of Credit – line of credit for credit card accounts