How To Create A Qif File From Excel

XL2QIF is an Excel Add-In that converts data to QIF format, the format used by Money and Quicken to import transactions.

XL2QIF is optimized for frequent conversions, it supports also different date formats (US, Europe, pre/post 2000).

When you install this macro, there is a new XL2QIF menu added to Excel with an item to create a QIF file (Save to QIF) and an item to import a QIF file (Load from QIF).

It's also possible to choose the language at the first installation. You can choose French or English. After that the macro will never ask again for the language.

The figure below shows the XL2QIF command windows and the parameters that can be changed:

The XL2QIF interface is inspired by Txttoqif. Furthermore all XL2QIF parameters are saved automatically and restored when you start again the macro to optimize repetitive conversions.

You can select the range of data to convert before launching the macro or from the command window.

Parameter	Description
QIF Output File	Select the XL2QIF output file.
Input data	Select the data cells range. By default this field is set to the selected cells on the active sheet. Note: You should select only the data to convert, not the column titles, for example in the figure below the Input data are from cell A3 to cell G6.
Auto select	Select the table around the active cell. By default the first line of the table, considered as a title line, is not selected. To select also the first line click a second time on Auto select
Fields in Input Data	To describe the organization of the input data and the order of the corresponding columns.
Columns order in Input Data	Shows the order of selection of the data fields. This should represent the order from left to right of the data columns.
Account Type	To select the account type to be put at the beginning of the QIF file. (By default Bank is translated to !Type=Bank)
Date Format	To select the european (Day/Month/Year) ou US (Month/Day/Year) date format. Dates before or after 2000 are automatically translated to the right Money format, e.g.: xx/yy/99 or xx/yy'00. The dates in the QIF output are generated according to this table:
Special processings	To select a special processing of the data before writing to the QIF file, if necessary. extract transaction date and payee from memo field for BNP Net VISA statements extract transaction payee and check number from memo field for Soci�t� G�n�rale statements use of the comma as the decimal separator in the QIF output file QIF format compatible with Quicken version 6 or before (without U tag).
Separate Debit/Credit	To select the way amounts are organized: - either a single Amount column (unckecked), - either two separated Debit and Credit columns (checked).
Invert amounts	To invert the amount sign. This is useful for some credit card statements where payments are positive (the amount you owe to the bank) instead of negative as expected by Money (payments are debits). This option is invalid when you choose to have separate debit and credit column since the sign is explicit in this case: debits are treated as negative and credits are treated as positive by XL2QIF.
Auto import	To import automatically the .qif output file in the software associated to the .qif files, as for downloads.

Excel data organisation

Excel data should be arranged in columns sorted by type of field. As in the example below:

Note the selection area that contains only valid data cells.

Download

It is recommended to do a backup of your Money or Quicken data before importing a .qif file generated by XL2QIF.

XL2QIF is an Excel macro written initially to solve personnal needs of conversion between Excel and Money.
On the Money & Quicken newsgroups one of the most frequently asked questions is �How to import Excel data into Money or Quicken�, so I've decided to share my work and to provide this macro as a freeware on this web site. However since it's not a commercial product it's not guaranteed bug free. I'm doing my best to test XL2QIF but if you find any issue please report it to me.

The current XL2QIF version is V1.11 .
Note: this version is not compatible with Excel 97, please use V1.0 for Excel 97.

Known limitations:

None

Feedbacks

All your feedbacks are welcome to improve XL2QIF.

XL2QIF is freeware and will remain free, however if you find XL2QIF useful, you may want to support it's development. You can do so with a donation, using the button below.

Visitors since February 19th, 2003: