QIF Importer

Thomas Baumgart

QIF format considered harmful

Generally speaking, the QIF format should be avoided wherever possible. It is a poor choice for transporting financial data. Among other things, QIF suffers from these problems:

  • Lack of standardized format: Different versions of the same program will impart different meanings to the same element.

  • Lack of transaction identifier: Because there is no ID number associated with each transaction, matching duplicate transactions is haphazard at best.

  • Lack of expressiveness: The grammar is really simple, and cannot portray the depth of financial information found in today's financial environment.

This is generally why Intuit stopped supporting QIF input at all with Quicken 2005. If you have the option of getting data some other way, like OFX, always choose that option.

How to import a QIF file

To import a QIF file, first ensure you have a valid KMyMoney file open. Then select ImportQIF... from the File menu.



The resulting dialog prompts for the QIF filename allowing you to locate the file by clicking on the Browse button.

Also, KMyMoney differentiates between the import of a bank statement file and historic data exported from another application. The default is to import a bank statement file. In case you are importing data from your previous personal finance manager application select the appropriate option.

In general the default QIF profile should work with your QIF data. In some cases it might become necessary to use a modified QIF profile. See the next section for more details on that subject.

Click on Import to import the QIF file.

KMyMoney will start scanning the file to determine the formats used to represent dates and numbers. In case it cannot determine a date format unambiguously, KMyMoney will ask the user to select one from the list of possible date formats.

Next, KMyMoney imports the data and creates all necessary objects, such as payee information, accounts and category records, and stock price information. Wherever possible, existing transactions will be matched against the imported information. A progress bar is shown and updated during the import process.

In case KMyMoney could not detect the name of the account to be imported, the user will be asked to select the account into which the data should be imported. If the account does not already exist in your file, a new account can be created by clicking on Create.

At the end of the import, KMyMoney shows a statement import statistics window.


Statement statistics

After importing, all of the imported transactions will be shown with a yellow background in the ledger view. In case KMyMoney was able to match an imported transaction with an already existing transaction, the background is shown in light green.

The next step is to verify the imported data and accept it. This is a general process and also applies to imports from other sources. It is outlined in a separate section of this document.

Note

The colors used to mark imported and matched transactions are customizable and may be different in your environment.

Setting up a QIF profile

Because there is no universally standard format for a QIF file, different vendors have taken liberties with the format, and introduced their own nuances. The QIF Profile allows KMyMoney to know about the peculiarities of your file. To edit an existing QIF Profile, or to create a new one, press the New button on the QIF Import dialog, near the profile selector.


QIF Profile Editor

Note

Previous versions of KMyMoney used to have a tab for date and amount specifications. KMyMoney now determines those settings by scanning the file. If it cannot figure out all settings, it will interrogate the user during import.

Transaction matching

As noted previously, one of the major drawbacks of the QIF format is the lack of a unique identifier for each transaction. Thus, if you import a QIF file and some of the transactions are already in your ledger, you may get duplicates. KMyMoney attempts to get around this by looking for transactions that look similar to those you already have. If it finds something that looks like the same transaction, it will match the apparent duplicate.

This can be a problem if you have transactions that look too similar but are actually different. In this case, you can unmatch those transactions later in the ledger view.

Writing an import filter

Sometimes you may have data in a custom format, like comma-separated-values (CSV) or something else unique to your situation. As of version 4.6, KMyMoney includes a CSV Importer Plugin, but you can still import other types of files into KMyMoney using a QIF Import Filter. A filter is a custom program you write which takes your special file as input, and produces a QIF file as output. This can be a shell script, a perl script, a compiled program written in C/C++, or anything else you can dream of, as long as the system can run it.

To use it, edit your favorite QIF Profile, and select the Filter tab. Enter the location of your filter program where prompted. Then, whenever you do a QIF import using this profile, the file you select for importing will be run through your filter first.

A common problem is to convert a list of comma-separated-values into a QIF file. This is a textbook case for the awk tool. Create a script called csv2qif.awk, with the following two lines as contents:

	BEGIN { FS=","; print "!Type:Bank" }

	{ print "D"$1; print "T"$2; print "N"$3; print "P"$4; print "M"$5; print "^" }

Then, change the QIF keys (D,T,N,P,M) to match the order of your csv data. Set the input filter to awk -f csv2qif.awk.

Another problem sometimes arises in the encoding of QIF files. KMyMoney expects files to be UTF8 encoded. If your file is encoded in something else, it can be useful to convert it to UTF8. For example to convert it from iso-8859-1, you would set the input filter to recode iso-8859-1..utf-8.

Special KMyMoney QIF extensions

As already mentioned, one of the major drawbacks of the QIF format is the lack of a unique identifier for each transaction. If you are writing your own QIF file creator (or filter, as described above), you can overcome this problem. KMyMoney supports the '#' field. The importer will interpret this as a unique transaction ID, and disregard the record if the transaction is already in the system.