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 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.
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:
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:
|