![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
Many online banks and credit card companies allow you to download recent transactions (checks, deposits, credit card charges, etc.) into a file in the Quicken Interchange Format (QIF), so that you can import these transactions into your account. The problem with this approach is that typically the bank has no way of assigning meaningful categories to the transactions. So, if you care about categorizing your transactions, you must go through each transaction you have just imported into your financial software and add categories. QIF Wizard automates this process. By default, QIF Wizard adds categories based on the description (e.g. payee) of each transaction. To convert a QIF file, just drop it onto QIF Wizard. You can configure QIF Wizard as a browser helper application for files of type application/QIF to have it run automatically on downloaded QIF files. QIF Wizard converts each transaction in the QIF file by:
If you do not want categories in your output QIF, you
can specify in the preferences
that the categories in your output are those present in
the input, else blank. The conversion is controlled by instructions that you provide. You are prompted for instructions whenever QIF Wizard does not know how to convert a transaction. You can also edit any instructions you have previously given. You can use the Control dialog to specify which transactions in the input file are to be processed, and how the output should be stored. Click on the Control tab above or click here for more information. If your bank or credit card company does not allow you to download QIF, you can still import transactions into your financial software by getting from your bank or credit card companies transaction history website display. For more information see Extracting Bank or Credit Card Transactions From Text |
By default, the conversions that QIF Wizard performs are based on the description in each transaction. When QIF Wizard encounters a description for which it does not have instructions, it prompts you for instructions with a dialog like the following.
This dialog identifies the Date, Amount, Description, and Memo (if any) of the transaction being converted, and allows you to enter a category to be associated with the description in the future. You can shorten the description to match more transactions in the future. In the above example, the charge was to Staples store #123. If you made a future charge to a different Staples, these instructions would not apply unless you shortened the description to just "Staples". You cannot edit the description other than shortening it, because it must be an initial match for the transaction being converted.
You can also enter a category to be associated with this description in the future. While entering the category, the dialog acts like Quicken itself does when you enter a category, autocompleting categories you start typing from a list of categories. You can also "scroll" through the list of categories by using the up and down cursor keys. or click on the down arrow to select a category from a list.
It is recommended that you export your categories from your financial software into a QIF file and drop that file on the QIF Wizard icon or open it from within QIF Wizard. Thereafter, QIF Wizard will maintain the category information. If, in the future, you add more categories in your financial software, you will need to export your categories and drop them on QIF Wizard again.
If you create a new category, the dialog will warn you, as shown below.
In this case, if you import the converted QIF file into Quicken,
Quicken will update its list of categories. Other financial
software may behave differently.
You have the option to specify a new description and memo for the transaction by checking the appropriate boxes.
You have the option (Allow me to confirm) to specify that each time this instruction is applied, you should be shown the transaction and given the opportunity to change the description or category. If you leave this unchecked, the category will be automatically and silently changed whenever a transaction description matches the description in the instruction. See below for other ways to cause transactions to be confirmed.
You have the option of specifying a new description, category and memo for the transaction based on the description and the amount. This option does not make sense for this transaction, and is left unchecked. See below for an example of when you might want to check this option.
Because the transaction description contains a number, the dialogs above give you the option of extracting a check number from the description. Since the number in the description is a store number and not a check number, this option would not make sense in this case, and is left unchecked. See below for an example of when you might want to check this option.
If you click Swap Description and Memo the description and memo of the transaction are swapped.
The Back button normally allows you to go back one transaction if you have specified in the preferences that all transactions should be confirmed. The Back button is dimmed in this example because this is the first transaction.
You can also add splits to the instruction, replacing the category entry in the display.
Finally you can specify, using the "Amount Match Options" tab,
instructions to be followed for transactions that match the amount
as well as the description. See below
for an example of why you might want to do this.
When you click Save, a new instruction is created, and the transaction is converted into the output file. If you click Skip Transaction, the transaction is not put in the output file. If you click Cancel, processing of the QIF file is is stopped, with no change to the output file.
Instructions also allow you to specify that you want to change the description of a matched transaction. In the above example, you may want to change the description from "Staples #123" to just "Staples". To do so, you would check the Use description button and enter the new description, as shown below.
While entering the description, the dialog acts like Quicken itself does when you enter a description, autocompleting descriptions you start typing from a list of previously-entered descriptions. You can also "scroll" through the list of descriptions by using the up and down cursor keys. or click on the down arrow to select a description from a list.
Instructions also allow you to specify that you want to change the memo of a matched transaction. In the above example, you may want to change the memo from "Staples #123" to "Supplies". To do so, you would check the Use memo button and enter the new memo, as shown below.
In the default mode, when matching a description in a transaction with instructions, the longest description (from the instructions) that matches the start of the transaction description is used. For example, given the description "Transfer to savings" in a transaction, the instructions description "Transfer to savings" would be used, instead of the instructions description "Transfer".
This "starts with" matching works well in most cases, but does not work when descriptions start with reference numbers. For example, if your QIF contains two Staples credit card purchases, the descriptions could be something like "104498720587 Staples #123" and "104498793817 Staples #456". Though you would likely want to associate the same category with each of these transactions, the instruction you defined when you encountered the first transaction would not match the second. In this case, an instruction that matches descriptions that contain "Staples" makes more sense. To achieve this type of matching you can turn on the "Match description with CONTAINS instead of STARTS WITH" preference. With this preference turned on, when QIF Wizard encounters a description for which it does not have instructions, it prompts you for instructions with a dialog like the following.
This dialog is identical to the case when the preference is turned off, except in the way that the description match string can be edited. The dialog reminds that "you can edit the string above as long as it matches the description". If you edit the string such that it is no longer contained in the description, an error message appears in the dialog as shown below, where the "p" was deleted from "Staples #123".
Furthermore, the instruction cannot be save unless the string is contained in the description.
QIF Wizard has several features designed to make the handling of Check transactions easier. Check transactions are usually downloaded from banks without specifying the payee in the description. Also, check transaction descriptions often contain the check number. Consider the transaction shown in the dialog below.
If you want your financial software to store the check number as a QIF check number, then turn on the Extract number from description option. When you do so, the display changes as shown below. Note that the extracted check number now appears at the top of the dialog, indicating that it will be stored as a check number.
Now, let's say you write a check each month or each week to the same payee for the same amount. For example, If you write a check each week to XYZ for $67.89, this might download from your bank as shown in the above dialog. In this case, you might want to edit the description to match to be just "Check Number", go to the Amount Match Options tab, and check the When the description starts as above AND has amount shown above, use the following description and category instead box. The result would be as shown below.
After checking the box, you enter the new payee for the transaction, and the new category. Allow me to confirm was checked just in case you sometimes write a check to some payee other than XYZ for $67.89.
While entering the description, the dialog acts like Quicken itself does when you enter a description, autocompleting descriptions you start typing from a list of previously-entered descriptions. You can also "scroll" through the list of descriptions by using the up and down cursor keys. or click on the down arrow to select a description from a list.
Here is a summary of the meaning of the above instructions:
You could optionally specify a new memo of the transaction by
checking the Memo checkbox, or add splits to the
instruction instead of a category.
The "normal" mode of operations is for QIF Wizard to silently and automatically convert transactions based on the instructions you have provided. There are two cases however, in which you are allowed to confirm a transaction:
You are asked to confirm a transaction with a dialog as follows.
This dialog allows you to:
While entering the description, the dialog acts like Quicken itself does when you enter a description, autocompleting descriptions you start typing from a list of previously-entered descriptions. You can also "scroll" through the list of descriptions by using the up and down cursor keys. or click on the down arrow to select a description from a list.
You can adjust the date by clicking the control in front of Date. If you do adjust the date, the dialog changes to indicate both the original date and the adjusted date, as shown below.
If you choose to edit the instruction by pulling down Instruction options..., you will get a dialog as shown below.
In this dialog you can change any part of the instruction and press the Save Changes To This Instruction button, delete the entire instruction with the Delete Entire Instruction button, or delete just the portion of the instruction that applies to this amount with the Delete This Amount button.
If you are confirming all transactions you can go back to previous transactions by clicking the Back button. This button appears only if you are confirming all transactions, and is dimmed for the first transaction because there is nothing to go back to. The Back button also appears in the Create Instruction dialog.
If you select the "Categories in the output QIF should be based on the category in the QIF if present, else on the description" preference, the conversions that QIF Wizard performs can be based on the category in each transaction if one is present. This is the case for some credit card companies that associate categories with transactions in their downloaded QIF. With this preference set, when QIF Wizard encounters a category for which it does not have instructions, it prompts you for instructions with a dialog like the following.
This dialog differs from the one for creating an instruction based on the description in that it shows the category in the input QIF at the top and defaults the new category to that value. You do not have the option to shorten the category in the input to match future transactions, because category matching, unlike description matching, is based on equality. In this particular example, note that the category in the input QIF would be a new one. In this case, you would want to specify your existing category for internet access, so you would change the category accordingly, as shown below, and then save the instruction.
You can edit instructions you have previously given QIF Wizard as shown above (when they are being used for a transaction), or by selecting the Bank/Credit Card Instructions... menu item under the Edit menu. When you use the menu, you will get the dialog that follows.
To delete instructions, you can select one or more instructions
and click Delete. To edit an instruction, you must select
a single instruction and clock Edit. This dialog shows
editing an instruction based on a description.
This dialog is similar to the one that appears when you edit an instruction being used for a transaction. The only difference is that you can change the string to match to use this instruction. In the example above you might want to edit the string so that it matches any description that starts with "Staples". The result would be as follows.
You can edit descriptions you have previously entered in QIF Wizard by selecting the Descriptions... menu item under the Edit menu. When you use the menu, you will get the dialog that follows.
To delete descriptions, you can select one or more descriptions and click the minus sign.
You can add, delete, or edit categories using the dialog shown below. If possible, you should export your categories from your financial software into a QIF file, and open that file with QIF Wizard to populate the list of categories known to QIF Wizard. If you cannot do that, or just want to make a minor change to the categories, then you can use this function.
The QIF format handles splits, which assign
multiple categories to a transaction, with a different amount for
each category. A few financial institutions provide QIF that
contains splits (such as PayPal, which uses splits to break out
the net amount received from a sale from the fee).
QIF Wizard allows you to process QIF that contains splits, and allows you to add splits to or remove them from transactions being output in QIF format. Instructions are not followed for splits. When a transaction containing splits is confirmed, a dialog similar to the one at the right appears: You can change the category associated with each split as well as enter a memo for each split. If there are more than 3 splits, you can scroll the list to see/change them all.
If one or more of the categories in the split input are not in the list of categories, the warning shown in red at the right will appear. If you do not know which category is new, you can find out by pulling down the menu for each category. New categories will not be selected in the list of categories that appears.
You can use the + button to add another split, or the - button to delete the selected split(s), but you cannot delete all of the splits. Use the Remove Splits button instead.
You can edit the Category, Memo, or Amount for each split. As you edit amounts, QIF Wizard will insure that the sum of all of the split amounts equals the amount in the transaction. Otherwise, your financial software might silently change the amounts in your splits to make them add up correctly.
Note that only the QIF format allows for splits; QFX/OFX does
not, and custom output formats you define (e,g, to produce CSV)
cannot contain split information. Thus, if you produce output in a
format other than QIF any splits in the input are completely
ignored, and you cannot add splits.
If your bank or credit card company does not allow you to download QIF for your transactions, an alternative approach is to use QIF Wizard to extract QIF from your bank or credit card company's transaction history web page and import that QIF into your financial software.
To use QIF Wizard in this manner, you copy the transactions from the web site into the clipboard and then have QIF Wizard extract QIF from them. If you prefer, you can put the contents of the clipboard into a file and process that file. Or, you might be able export you transactions in CSV format in a file, and process that file with QIF Wizard. To be able to accomplish this extraction, QIF Wizard needs to learn the format your investment website uses for its transaction history display.
QIF Wizard has the ability to learn about a wide range of websites and formats. However, no guarantee can be made that any particular website can be learned. Since different browsers encode web page information copied into the clipboard in different ways, the specific web browser used is a factor in determining whether QIF Wizard can learn a particular website. QIF Wizard works with Internet Explorer. It also works with Netscape 4.x and very recent versions of Mozilla. However, there is a bug in early versions of Mozilla/Chimera/Camino/Netscape 6 or 7 that prevents them from working with QIF Wizard in many cases. QIF Wizard will not work with OmniWeb.
QIF Wizard cannot learn or extract from websites that:
Click here for more information on defining the format of bank or credit card transactions. Click here for more information on extracting QIF from a bank or credit card website transaction history once its format has been defined. Click here for information on how to edit a format once you have defined it.
To be able to extract transactions from text, you must teach QIF Wizard about the format of the transactions in the text. The format depends on 1) the way the website displays the transactions and 2) the way the web browser puts selected information into the clipboard or file, or the format of CSV produced. Therefore, if you use the same web site with two different browsers, you will need to define two formats. In general, you need to define a format for each website/browser combination.
To define a format:
Here is an example Control dialog after a format name has been entered.
The name entered above suggests that you should name your format with the bank name and account type. This is because some banks display different transaction fields for checking accounts (e.g. check number) than they do for savings accounts. You probably don't need to put the account type in the name for credit card accounts. When you hit return, the following dialog appears.
This dialog contains example text from the clipboard for a hypothetical bank. You specify four items in this dialog. The first is the type of transactions, in this case Bank. Second, you specify some text from the input that uniquely identifies this text format. In the example above the account number was selected. The account number was chosen because it denotes the type of account: checking or savings. This is important because you might have to define a different formats for these two types of accounts, because they do not have the same kind of information in them. For example, a checking account might have a check number, whereas a savings account would not. QIF Wizard will use this text to guess which format applies to the text in the clipboard when you specify Process Clipboard. This guess does not have to be perfect because you always have the opportunity to select the proper format. The third item you specify is whether the first line in the text contains a transaction. The fourth item you specify is the date in the first transaction in the clipboard. In this case, the input contains dates with the month followed by the day. If however, the dates in the input have day before month (such that 12-03-2001 means March 12, 2001), then you would check the "Interpret dates with day before month" option. Note that this option specifies only how dates in the input are interpreted, it does not control how dates are output by QIF Wizard. Dates are always output in the format mm/dd/yyyy. When you press NEXT in this dialog, the following one appears.
From this point forward in the format definition process, you can
use the "Back" button to go back one screen in case you answered
on of the questions wrong.
This is a very important dialog. You need to first make sure that each row in the table contains a single transaction. You should experiment with the setting of the Assume each transaction is on a separate line checkbox to make sure there is one transaction per line. In this example you leave the box unchecked. Then you need to choose a method to separate fields that replicates the fields displayed in your web browser. It is important that all transactions (rows) be divided into fields in the same and correct manner. If the field division does not look right when this dialog appears, try the other methods and click NEXT when you have chosen the best method. Don't be surprised of multiple methods yield the same results. In that case, choose any with the correct result. Sometimes non-US characters can confuse the splitting into fields, so you can check the Text contains non-US characters box to see if that corrects the columns. If no method divides the fields properly, then QIF Wizard will not be able to extract from the web site with the browser you used. Try another browser. When you click NEXT, the following dialog appears.
This dialog merely contains instructions. If the column widths don't allow you to see all of each column, you can adjust the widths were, or in the dialogs that follow. When you click NEXT, the following dialog appears.
Starting with the above dialog, you are asked a series of questions for each bank or credit card transaction field: description, debit, credit, check number, and memo. The specific questions you are asked depends on the answer to your questions. Your transactions do not have to contain all of these types of fields. Had you checked the Assume each transaction is on a separate line box you would be asked which column contains the date. Otherwise QIF Wizard assumes that the first column contains the date.
In the case above you would click on the column header above the third column. When you click there, the following dialog appears.
Note that the header you clicked has now been labeled with "Description". Here you are asked whether this bank puts its credits and debits in the same column. Usually different columns are used, as is the case here. So you would click on No and the following dialog would appear.
Here you would click on the column header above the fifth column. When you click there, the following dialog appears.
Note that the header you clicked has now been labeled with "Debit". Here you would click on the column header above the fourth column. When you click there, the following dialog appears.
Note that the header you clicked has now been labeled with "Credit". Here you are asked to indicate which column contains a check number. You also given the option to not select a column, since check numbers do not appear in all cases. In this case you would click on the second header and the following dialog would appear.
Note that the header you clicked has now been labeled with "Check". Here you are asked to indicate which column contains a memo. You also given the option to not select a column, since memos do not appear in all cases. In this case you would click on the last header and the following dialog would appear.
Note that the header you clicked has now been labeled with "Memo". Here you are asked to indicate which column contains a category. You also given the option to not select a column, since categories do not appear in all cases. In this case you would click on the NEXT button and the following dialog would appear.
You are now done defining the format. You are not asked about a balance, because hopefully your financial software will automatically compute this when the transactions are imported.