xl2qif Excel macro


Excel to QIF data conversion for Money & Quicken

XL2QIF | CALC2QIF | Links | Download | FAQ | Français 



New

References


Links



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:

Xl2qif Wizard

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:

Xl2qif Wizard

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.

  • XL2QIF V1.11: (April 10, 2005)
    • [1.11] Load from QIF: Support for end of line with LF only in addition to CR+LF for QIF files
    • [1.1] Save to QIF: New option to import automatically the .qif output file into Money or Quicken
    • [1.1] Save to QIF: New "Sign" et "Address" fields
    • [1.1] Save to QIF: Fix of an issue with the comma in the investments commissions
    • [1.1] New "Uninstall" button in the install.xls file
    • [1.1] New xl2qif.ini file to save user settings
  • XL2QIF V1.0: (June 9, 2004) - For Excel 97
    • Load from QIF: Fix a day/month inversion issue in the dates occuring in some cases with V0.98
    • Save to QIF: new Specific processings for Société Générale (bank & Visa Premier statements)
    • New "Support" item in XL2QIF menu
  • XL2QIF documentation: (July 02, 2004)
    • Documentation in Acrobat format

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:



XL2QIF | CALC2QIF | Links | Download | FAQ | Français 


© 2002-2004 Last updated: Monday July 5th, 2004