TITLE:Talking to QuickBooks ISSUE:Spectrum AUTHOR:Nathan Rector COMPANY:Natec Systems EMAIL:nater@northcoast.com HTTP:www.northcoast.com/~nater/ I originally started looking for the Quicken Interchange Format (.QIF) out of more curiosity until I found out how many small business are starting to use Quickbooks or Microsoft Money for some of thier accounting functions. When I asked why they decided to use QuickBooks, may responded "because it was up-to-date". Others responded by stating that that is what their CPA or Accountant requires them to use in order to use their services. After looking deeper into QIF, I found that there are a lot of accounting software in existants that also understands it. As the world becomes more automated, more and more banks are places their statements online in QIF or OFC. OFC is Microsoft's try at creating a common interchange format for finances. OFC has more functionality and was designed to work with built in encription as well as other tools needed in electronic Business-to-Business transction, but as with most things like this that Microsoft developed, it is a bit more complicated than it needs to be. They also got a late start and more programs understand QIF currently than OFC. Even if you are not using QuickBooks, or have a program that requires it, you will find it of use to your business as a time saving tool when balancing to your bank statement. Like I said above, most banks now provide your bank statements in a QIF format. Instead of paying someone to enter your bank statement, read the QIF file and update it automaticly. The QIF format is a simiple ASCII file who's content start with one of the following headers: !Type:Bank Bank account transaction !Type:Cash Cash account transaction !Type:CCard Credit Card account transactions !Type:Invst Investment account transactions !Type:Oth A Asset account transactions !Type:Oth L Liability account transactions !Account Account list or which account follows !Type:Cat Category list !Type:Class Class List !Type:Memorized Memorized transaction list Each Item in a !Type:Bank, !Type:Cash, !Type:CCard, !Type:Oth A, or !Type:Oth L must begin with a letter to indicate the type of field information. The following are the fields indicators: D Date T Amount C Cleared Status N Number (Check or reference number) P Payee M Memo A Address (can have this repeated 5 times) L Category:Subcatagory S The split Category:subcatagory $ dollar amount for the split E Memo for the split ^ End of entry Now that I've covered the general format here is an example of what you'd get from a bank: !Type:BankD06/01/2000PPAYROLL MAY31 NT100.00L^D05/25/2000PATM WITHDRAWAL NT-20.00L^D05/24/2000PATM TXN FEE NT-1.00L^D05/24/2000PPAYROLL MAY24 NT300.00L^D05/19/2000PSHARE DRAFT 1457 Trace # 80354 19N1457T-190.26L^ As you can see, it's quite simiple and easy to work with. Now, let's say you want to import check register informatin into QuickBooks or Quicken. The format is almost exactly the same except there is likely more information included with the informaiton. For example, an address the check was sent to, or the catagory that the information is to be assigned to. !Type:Bank D06/01/2000 T-1,000.00 N1005 PBank of Mortgage LPlant SPlant $-253.64 SMort Int $-746.36 ^ D06/02/2000 T75.00 PDeposit ^ D06/03/2000 T-10.00 PJoBob Biggs MJ.B. gets bucks LEntertain A1010 Rodeo Dr. AWaco, Tx A8050 ^ TITLE:Talking to QuickBooks ISSUE:Spectrum AUTHOR:Nathan Rector COMPANY:Natec Systems EMAIL:nater@northcoast.com HTTP:www.northcoast.com/~nater/ I originally started looking for the Quicken Interchange Format (.QIF) out of more curiosity until I found out how many small business are starting to use Quickbooks or Microsoft Money for some of thier accounting functions. When I asked why they decided to use QuickBooks, may responded "because it was up-to-date". Others responded by stating that that is what their CPA or Accountant requires them to use in order to use their services. After looking deeper into QIF, I found that there are a lot of accounting software in existants that also understands it. As the world becomes more automated, more and more banks are places their statements online in QIF or OFC. OFC is Microsoft's try at creating a common interchange format for finances. OFC has more functionality and was designed to work with built in encription as well as other tools needed in electronic Business-to-Business transction, but as with most things like this that Microsoft developed, it is a bit more complicated than it needs to be. They also got a late start and more programs understand QIF currently than OFC. Even if you are not using QuickBooks, or have a program that requires it, you will find it of use to your business as a time saving tool when balancing to your bank statement. Like I said above, most banks now provide your bank statements in a QIF format. Instead of paying someone to enter your bank statement, read the QIF file and update it automaticly. The QIF format is a simiple ASCII file who's content start with one of the following headers: !Type:Bank Bank account transaction !Type:Cash Cash account transaction !Type:CCard Credit Card account transactions !Type:Invst Investment account transactions !Type:Oth A Asset account transactions !Type:Oth L Liability account transactions !Account Account list or which account follows !Type:Cat Category list !Type:Class Class List !Type:Memorized Memorized transaction list Each Item in a !Type:Bank, !Type:Cash, !Type:CCard, !Type:Oth A, or !Type:Oth L must begin with a letter to indicate the type of field information. The following are the fields indicators: D Date T Amount C Cleared Status N Number (Check or reference number) P Payee M Memo A Address (can have this repeated 5 times) L Category:Subcatagory S The split Category:subcatagory $ dollar amount for the split E Memo for the split ^ End of entry Now that I've covered the general format here is an example of what you'd get from a bank: !Type:BankD06/01/2000PPAYROLL MAY31 NT100.00L^D05/25/2000PATM WITHDRAWAL NT-20.00L^D05/24/2000PATM TXN FEE NT-1.00L^D05/24/2000PPAYROLL MAY24 NT300.00L^D05/19/2000PSHARE DRAFT 1457 Trace # 80354 19N1457T-190.26L^ As you can see, it's quite simiple and easy to work with. Now, let's say you want to import check register informatin into QuickBooks or Quicken. The format is almost exactly the same except there is likely more information included with the informaiton. For example, an address the check was sent to, or the catagory that the information is to be assigned to. !Type:Bank D06/01/2000 T-1,000.00 N1005 PBank of Mortgage LPlant SPlant $-253.64 SMort Int $-746.36 ^ D06/02/2000 T75.00 PDeposit ^ D06/03/2000 T-10.00 PJoBob Biggs MJ.B. gets bucks LEntertain A1010 Rodeo Dr. AWaco, Tx A8050 ^