Saturday, 23 February 2008

Using QIF files to record Quicken transactions

I'm trying to use Quicken Personal Plus 2008 to record all my financial transactions this year, but not having a huge amount of success so far. Entering stock and mutual fund transaction details is easy enough, as I can enter them once a month from the statements. The trouble I have is keeping a record of all the miscellaneous daily transactions I make, such as buying lunch or a snack, or picking up some groceries during lunch hour.

Although I keep receipts for these transactions, the receipts tend to just accumulate in my wallet for a couple of weeks until I get around to entering them all into Quicken at home. Some cash transactions don't have a receipt either, so I end up writing these items down on scraps of paper which I take home. Often these odd notes get lost before I can enter it into Quicken, making my cash reconciliation rely on the dreaded "miscellaneous adjustment" item.

What I'd like to do is record these daily transactions on my PC at work, since I usually have some spare time during lunch or tea breaks which I could use to keep these small transactions up to date. But there doesn't seem to be any small utility provided by Quicken for doing this, and I sure don't want to buy and install a second copy of Quicken on my work PC just for these few transactions!

Using an excel spreadsheet would be OK, except that Quicken doesn't import excel files, and I don't want to have to reenter transactions into Quicken at home if I've already done the data entry once. Luckily the old QIF file format Quicken originally developed for sending transaction data to technical support can provide a simple solution.

Older versions of Quicken used QIF to import transaction data from banks, credit providers and the like. But the more recent versions don't support QIF import except for Cash accounts. (It's been replaced by the more comprehensive 'Web Connect' or QFX format, which is based on the open format OFX). However, the QIF format is fine for recording simple transactions, and the way around this limitation is to save transactions in a QIF file and import it into a dummy Cash account setup in Quicken for this purpose. Once the transactions are imported they can then be simply moved to the appropriate account within Quicken, without having to reenter the transaction details.

As QIF files are plain text, you can use a simple text editor such as Notepad to enter the transactions. I just email the file to myself from work, and can import the file into Quicken at home with a couple of keystrokes.

The QIF format is fully explained in wikipedia, but for simple transaction data you just need a header line and a couple of data lines for each individual transaction. For example:

!Type:Cash
D12/02/2008
T-77.43
PSupermarket
^^
D13/02/2008
T500.00
PATM withdrawal
^^
D13/02/2008
T-10.96
PTakeaway
^^

Imported into Quicken this data appears as:


and can be moved to the relevant accounts by highlighting a transaction and using the Edit/Transaction/Move Transaction command to move them to the relevant account.

If I used a PDA I could even enter these transactions as I made them in the shops, eliminating the need to even take the receipts back to the office to do the data entry.

Copyright Enough Wealth 2007

6 comments:

Alex - My Trader's Journal said...

Why don't you like using the QFX format? I use my credit card for 95% of my purchases and download everything fine. Places you frequently visit are automatically entered with the correct category. Only new stores require any data entry.

Anonymous said...

I agree with the previous comment. I use MS Money, but I try to use my credit card statement for 95% of my purchases. I download all my statements from my banks, including mutual fund activity, 401K balances, IRA balances, and all credit card activity.

Anonymous said...

QFX is great if your bank supports it. My credit union only has the option to down load QIF. They also will not tell me why they do not support QFX. Great idea taking it to a dummy account first!!

enoughwealth@yahoo.com said...

I mainly use this method to enter details for minor cash transactions I make during the day, so I don't have to enter them each night at home (if I leave it to the end of the week I forget what I've spent all my cash on). So there's no bank file available for these transactions.

However, I do use a CC for 95% of my purchases, but I enter them once a week from the cash register receipts and then reconcile them to my monthly CC statement each month. I would love to import a QFX file from my CC provider -- except that they don't offer that format. To quote my bank's FAQ:
"You can export your account and transaction information into any software package that accepts Comma Separated Values (CSV) and Quicken® Interface Format (QIF) files."


Most Australian banks and credit unions do NOT offer QFX format, probably because quicken charges them a fee to use it.

Grover said...

Fantastic idea, as I was stuck with the precise same issue.
BTW, I've just discovered that you can import QIF files directly into the normal accounts in Quicken Personal Plus 2008.
File > Import > Import QIF File
I've just imported all my transactions from my (Australian backwards) bank. Yes, they're charged by Quicken to get the format in Australia according to what I've been told.
Neil

Anonymous said...

Thx peoples for posting your comments as you've been helping me get educated on Quicken. I'm ex MYOB (v.old version) and am running a trial Quicken - the attraction being to run a budget and analyse against it really simply, which is my highest priority presently - then I'll go to bigger and better usage from there. I don't want to use it to tell me when to pay a bill (argh!) So (since it's a trial I can only import QIF from my bank and not via connect so I don't know about that method). I also use my CC for at least 95% of transactions so 3 wks ago I imported data since Jan01 and categorised each transaction. Now, when I do an update of import I still have to categorise repeat types of transactions (e.g. supermarket). Can this be resolved in Personal Plus to categorise automatically? I see there is a "QIF Master" product for MAC but I'm on Windows. Do I have to buy a version that support QFX (and convince my bank - CBA - yeah, right! to provide the export format?). I'm gathering all this from your comments but can someone confirm for me?