Quicken is one of the best-selling programs in the history of the Universe. Pretty much everyone that has owned a PC or Mac since the late 80's has had a copy of it lying around somewhere, and lots of people actually use it to keep track of their finances. Why? Because it works pretty well and Intuit has (to their credit) done a good job of keeping up with what people want the program to do.
They've done such a good job, in fact, that lots of Linux folks keep a Windows partition on their machine just so they can run Quicken and the latest shoot-em-up games. So of course we want to give you a way to suck all your Quicken data into GnuCash and remove one more barrier to putting a nice ext2 filesystem on that Windows partition.
The problem is that GnuCash is a real double-entry accounting system and Quicken has a pretty simplistic view about what an account is, what a transaction is, and what to save in data files. In short, QIF files just don't contain enough information to completely and accurately reconstruct your Quicken account hierarchy in the GnuCash double entry system without some guessing by the import code and some handholding by you. QIF files omit small things that can be easily guessed (for instance, are numbers in decimal-radix [1,000.00 == 1000] form or European comma-radix form [1.000,00 == 1000]? Are dates m/d/y or y/m/d?) and big things that can't be easily guessed, like, for example, what currency the file is denominated in, or what account the file describes.
For the most part, GnuCash's QIF importer does a good job of figuring this stuff out, but you do have to keep an eye on it. The system is designed so that you can correct problems BEFORE you make changes to your GnuCash accounts; nothing is done to your GnuCash accounts until you click the final "OK" button.
In the next section, I'll give an overview of the QIF file and its "features". This may seem unnecessarily technical, but if you will at least glance through it you will be much better able to understand what's going on if you are having to jump through hoops to make things work right, and how you might be able to jump right in and edit the QIF file to fix really tough problems.
There are two major "paths" for using the GnuCash QIF importer. One is the "I am a Quicken user just migrating to GnuCash" path; the other is the "I am downloading some updates from my bank as a QIF file" path. This document mainly focuses on the former case, since new users are likely to need the most help and you can't get started using GnuCash until you can get your old records in.
Table of Contents
Introduction to the QIF file
QIF files are plain text files formatted as "tag-value" pairs. At the beginning of each line there is a single character "tag" followed immedately by the "value", which extends to the end of the line. Don't be afraid to pop up a QIF file in "less" or the text editor of your choice if you are having problems getting some Quicken data imported correctly; chances are a simple search-and-replace will fix just about any problem you might have with a QIF file. And a regexp search-and-replace will get the rest.
Collections of tag-value pairs form records of various types. There are records to store the names and descriptions of your accounts and of expense and income categories that you have defined in Quicken. There are records to define Quicken "classes" (sort of like sub-accounts, sort of like categories, but not exactly like either). And there are records to describe transactions.
Here's a typical Quicken transaction record:
!Type:Bank D6/20/97 T-500 N1012 C* M P L[Visa] ^
The ! tag denotes the start of a section of records of a certain type. In this case, Bank transactions. Type:Cat means a section of Category descriptions, Account means account descriptions, and so on.
The D tag denotes the date. Note y2k compliance "issue". Here's a lovely "feature" of some version of Quicken and dates in 2000:
D1/ 1' 0 T-640.00 CX N511 PJoe Bob LRent:Apartment ^
Ouch! Fortunately the GnuCash QIF importer can handle all of the wacky date formats that the gnucash-devel list can find.
The T field is the "Total" amount of the transaction. If there are splits, the sum of all the split amounts is in a T field. Money going out of the account is negative.
The N field is a "Number", which is usually a check number or some other identifying number for the transaction.
The C field represents the clearing/reconciliation state of the transaction. An x or X in this field means the transaction is "Cleared", a * means the transaction is Reconciled.
The M field is the transaction memo.
The P field is the Payee.
The L field is the Category/Account line. If the value in this field is enclosed in square brackets, like [Visa], this transaction is a transfer to the Quicken account named Visa. If there are no square brackets, the transaction is in the named Category (like Rent:Apartment).
The ^ tag means End of Record.
Quicken users taking advantage of Classes will see a slash (/) character followed by the class name appended on the Category line (like [Visa]/Project)
If a transaction has "splits", meaning that it is a single transaction with "this" account but is "split" into multiple source/destination accounts, the splits are described with S fields for the category/account/class of each split, an $ field for the amount of the split, and an E field for a per-split memo. The total of all the $ fields in a transaction record should equal the T field.
Note that nowhere in the transaction record, nor anywhere else in the file, does Quicken store the name of the account that the file describes. Don't ask me, I don't know why either. Microsoft Money (which also can save QIF files) started doing a "trick" to get the information in the file. If the very first Bank transaction in the file has a payee of "Opening Balance", the L line contains the name of the account that the file describes:
!Type:Bank D12/03/95 T4,706.57 CX POpening Balance L[New Bank] ^
Opening Balance records are handled specially, since they don't mean what they appear to mean (if you interpret the record literally, as a transfer of $4706.57 from [New Bank] to [New Bank], your new balance is a whopping $0.00). In the Accounts Tab section there's a discussion of what we do with them.
Table of Contents
How to use the QIF Import dialog
QIF files describe only one account, and try to be "complete" in representing all the transactions involving that account. This is fine if you only have one account, but if you have multiple Quicken accounts and transfers between them, transactions will show up in multiple files. This means that if you aren't smart about catching duplicate transactions you will end up with wrong balances in GnuCash. Definitely a bad thing.
In order to get the best possible replication of your Quicken account tree, export everything you can from Quicken and then import it all in one session. The importer's Files tab will allow you to load as many QIF files as you want, and to make sure that the currency, Quicken account name, and so on are right for each one. Then the importer can do a really good job of catching cross-references and marking them.
The importer is written mostly in Guile, and it can be a little slow on large QIF files. Load File takes 5-6 seconds for a QIF file with 1000 or so transactions on my machine.
Once you have loaded all the files into the importer, go to the Accounts tab, and then to the Categories tab, and check that the importer is going to put your Quicken transactions in the right place. You can click to pop up a dialog and change the GnuCash destination account name/type for any QIF account. Don't be afraid to change these destination accounts; they are only guesses by the importer based on the name and type of the QIF account. Mappings of Quicken account to GnuCash account are written to a preferences file when you click "OK", so if you import other Quicken files describing these same accounts you won't have to correct the importer again.
Make sure (especially in the Accounts tab) that the QIF account names and transaction counts make sense to you. If you see that one QIF account is mentioned by two different names, make sure that the "QIF Account" for every file in the Files tab is what you meant it to be. If the QIF Account for a file is wrong, the importer won't be able to match up transfers correctly and your balances will be wrong. If a QIF Account for a file is wrong, select the file in the Files tab, unmark the "Auto" checkbox, and edit the text box to contain the right name, then click "Load File" again. You will be asked to confirm a reload of the file and then it will be done. Flip back to the Accounts tab, see if that fixed the problem, repeat as necessary.
When you are happy with the account mappings (double check them, and make sure to save your GnuCash session first just to be sure), then and only then click OK. If you click Cancel at any time, your accounts will not be touched.
Again, the importer is written mostly in Guile, and it can be a little slow on large QIF files. It takes 3-4 seconds to stuff 1000 transactions into GnuCash on a Celeron 433, proportionately longer on slower CPUs. You only have to do a large import like that a few times, fortunately, so I'm not too worried about it.
Table of Contents
The first thing to do is load all your files. Click "Select
File", pick your file, then set the account, currency, radix, and date
fields, then click "Load File". The Currency field defaults to the
GnuCash default currency (set in the International tab of the
Preferences dialog). Try autodetecting radix, date format, and
account name first. The radix and date formats will stay on
"Autodetect" if the autodetector is not 100 percent sure of the right
answer; in that case, you will have to make a manual selection. You
probably know what the correct radix format is; if you're in the US or
the UK, it's definitely "decimal". Almost every QIF file I have seen
in the US is m/d/y for the date format, so try that if autodetect
doesn't work.
To go back to a file that you have previously loaded, select
its name in the file list on the left. If you change settings for a
previously-loaded file, click "Load File" again to reload it with new
settings. Don't forget to turn off "Auto" on the QIF Account entry if
you manually enter it.
If there's no Opening Balance record in the file, the account
name is guessed from the file name: any .qif extension is removed, and
all dashes and underscores are changed to spaces. If you want to save
yourself manually re-entering the name, save the file with a name that
will get guessed correctly (i.e. save the account "My Bank Account" as
My-Bank-Account.qif or My_Bank_Account.qif).
GnuCash makes a hearty effort to interpret any QIF file that
you throw at it, but you need to make sure that it's a normal DOS or
Unix text file before trying to import. The Mac version of Quicken
saves files with Macintosh newline conventions, which really confuses
the Guile reader. Macintoshes use the carriage-return only (which
usually prints as ^M), and the Unix convention requires a line feed
(usually prints as ^J). You can use "tr" to fix this problem, or a
search and replace in your favorite text editor. With tr,
the command might look like
Table of Contents
Each line in the Accounts tab display represents a mapping from
a Quicken account to a GnuCash account. Similarly, the Categories tab
display shows mappings from Quicken categories to GnuCash
accounts. Only QIF accounts referenced by one or more transaction
records are displayed. The name of the GnuCash account is displayed
in "full name" format, including the names of all parent accounts
separated by your default separator character (generally ":").
The first thing to check is the column of Quicken account
names. Make sure there are no duplicates with slightly-different
names. If a QIF transaction makes a transfer to [My Checking], and
you imported a file called my-checking.qif, you might have one account
entry for "my checking" and one for "My Checking". If these are the
same account, you need to go back to the Files tab and reload
my-checking.qif with the correct Quicken account name, My Checking.
Once you have all the Quicken accounts making sense, check the
GnuCash account column. The default GnuCash account for a given
Quicken account is determined by a fallback procedure which makes the
best guess it can given the available information. The guesses that
are tried are (in order of preference):
Check both the name of the GnuCash account for each QIF account
and the type. If you are unhappy with either, click on the row in the
display containing the offensive mapping. You will see the
Account Picker dialog which will allow you
to change it.
Table of Contents
This account picker is sort of broken. The idea is that you
can select an existing account from the tree display, or enter
information for a new account in the boxes below. However, right now
it's possible to do Very Bad things like specify a subaccount of an
existing account with a type that's not compatible with the parent.
As soon as I figure out how I want this dialog to work I'll fix it. I
have tested out the worst things that you can do and nothing terrible
happens, except your account tree might be in a state that you could
never have created through the GUI (a Credit Card account as a child
of a Bank account, for example). Don't do that. I'll fix it Real
Soon.
Table of Contents
Everything really happens when you hit the "OK" button, so it
gets a section to itself.
Table of Contents
If your Quicken files have "Opening Balance" records, you will
see an account called "Opening Balance" in the Accounts tab.
Accounting for the source of opening balances is sort of a hassle,
when you think about it, because they come from accounts that are
outside the scope of the GnuCash universe. The suggestion I've seen
on the gnucash-devel list is to make Opening Balances point to a
GnuCash account called "Retained Earnings", of type Equity. I don't
exactly understand this but it seems reasonable, and it's the default
for accounts called "Opening Balance".
In the Categories display, you may notice a blank QIF Category
entry. Quicken transactions are not required to have a Category, but
GnuCash transactions are required to have a source and a destination.
The blank category lets you select which GnuCash account all
uncategorized transactions go to. This will generally be
miscellaneous checks you have written, cash withdrawals, and so on, so
you probably want to put these in a "Misc Expenses" account or
something similar. It may make sense to put this in an equity
account; let me know if there's a good explanation for how it should
be.
Quicken stock transactions have a recognizable pattern for
dividend payments. If the importer can definitely tell that a
transaction is a transfer from dividends then it will default to
creating a "Dividend" income account. This category is usually not
present in the Quicken file, so it's being manufactured out of
nowhere.
Quicken has the abstraction of a single account representing a
"fund family" for the purpose of allowing smooth transfers between the
various accounts administered within the family. The GnuCash Importer
will ALWAYS get this wrong the first time, because Quicken explicitly
puts the wrong information in the file. The "blanket" account
representing the fund family as a whole should probably be a Bank
account, since the transfers to and from it in the Quicken file are
denominated in currency, not shares. The balance of such an account
is supposed to always be 0 since you just use it as an intermediary
between two accounts in the family. Hopefully I'll fix this at some
point if someone tells me how it's supposed to work.
Brokerage accounts are really confusing to me. Basically, my
thinking is that the brokerage account itself should probably be a
Bank account. The only wierdness is in stuff like dividends paid from
securities to the brokerage account. If you're using a Dividend
account, you can lose the information about where the dividend came
from. The importer tries to save this information by putting the
security name in the Payee slot (which shows up in the GnuCash
Description field for the transaction). If you have a better idea,
let me know.
The "Files" Tab
cat macfile | tr 015 012 > unixfile
The "Accounts" and "Categories" Tabs
The Account Picker
The "OK" Button
A few hints
Opening Balance
Empty category
Dividend category
Fund families
Brokerage accounts