For several years I have successfully used the QuickBooks IIF format to import bills, checks, and employee hours. I have been less successful with invoices and I was unable to import payments. Several times I considered writing a C# or VB program to import the data but I backed off because it looked like it would take too much time. I looked at AutoIt but felt it would have a steep learning curve, too. Yesterday I decided to give AutoIt a try and after a couple of hours I was able import both invoices and payments.
Since I am the treasurer for a Habitat for Humanity affiliate I have a strong need to make sure our mortgage tracking system is kept in sync with QuickBooks accounts. I use an Excel spreadsheet to reconcile mortgage payments we record in the specialized system with the actual bank deposits. This is where I find the errors since we use one person to record the payments and another person to record the deposits. For each mortgage payment I must allocate it to principal, escrow, and late fee. If everything works fine, then both systems have the correct values for each individual home owner’s mortgage and escrow accounts. I set up an invoice for the principal owed when the home owner closes on the house. Every month I enter invoices for the escrow and late fees due. Habitat does not charge interest. On an average month I need to enter 30 to 40 invoices and 30 to 40 payments. As you can see, I have a vested interest in seeing that this project be quick and accurate.
The present script is a work in progress. I feed the data into the script via a VBA macro I created in my spreadsheet. The macro writes out the payment values as AutoIt code onto a new worksheet. Then I paste this code into my AutoIt script, save the script, and run it. Crude but effective. The code for the invoice screen was pretty straightforward and I am letting it run automatically. The code for the payment requires human interaction since I must allocate the payment to the different invoices. It pre-fills the payment screen and then asks me to allocate the payment and save the payment before proceeding. I click on a popup to process the next payment.
Before next month I will have the macro writing out the code to a text file that I can “include” in the script and include a little bit more error checking.