I got an email recently from someone who was trying to import QuickBooks IIF transactions. Although I could not help this person with the problem, I did get motivated to write an Excel macro to import IIF transactions. One of the more tedious tasks I do as Treasurer for our Habitat affiliate is inputting payment data for our home owners. Each payment is split into three parts, principal, escrow, and late fee. The actual breakout of the payment is determined by our mortgage committee when they enter the data into their mortgage tracking program. This is important financial data so I keep track of the data in QuickBooks, too. Every month I use a spreadsheet to reconcile the deposit journal with the payments recorded into the mortgage system to make sure we do not miss anything. Then I get to the tedious part. I have to enter the payment data for each homeowner into QuickBooks before I can calculate how much to transfer into the escrow checking account. The good news is that we “only” have 37 home owners. The bad news is that I have to make two or more transactions per homeowner payment. This is the most time consuming portion of my Treasurer’s tasks.
My partial solution to this problem is to create about half of the transactions, escrow and late fees, directly from the spreadsheet. In my case I enter QuickBooks invoices for every escrow and late fee payment. The problem is that the escrow and late fees payments change every month. Another problem is that you need to create three rows in an IIF for every payment. In the spreadsheet I use to reconcile the deposits, I use one row per payment. So here is how I solved the problem.
I select the rows I want to generate invoices for and then run an Excel macro that creates a new worksheet in the IIF format with the data from the payments. Then I save the new worksheet as a Tab delimited file with an iif extension and import it into QuickBooks. The logic is pretty simple.
- You create a new Export worksheet. You delete the old Export worksheet if it exists.
- For every row in the selection you check to see if was part of a deposit. My worksheet has all of the home owners listed. If a home owner does not make their monthly payment, this cell is empty. If a home owner makes more than one payment in a month I will have two or more rows for them.
- If it was part of a deposit and the escrow portion is greater than zero, you make an escrow invoice.
- If it was part of a deposit and the late payment is greater than zero, you make an late fee invoice.
So there it is. Since I track escrow liabilities by home owner, each home owner has their own QuickBooks “Item” that points to their escrow account. I used the “vlookup” command to fill out the invoice with the correct QuickBooks “Item” for each home owner.
The other half of the transactions is the data entered on the Customer Payment screen. Although there is an IIF transaction for customer payment it does not help me. The Bill Payment transaction is not helpful, too. It would be nice to automate this but I will settle for my small improvement in the process.