Using Excel to Do Calculations and Create QuickBooks Entries

When you can’t do it in QuickBooks, move it into Excel to produce the result you want and, then, move it back into QuickBooks. Here’s an example I worked out for a real estate investor and property manager who wanted to automate rent distributions less his fee to an investor. On occasion, tenants did not pay their full rent. Therefore, the distribution check needed to be based on received rents less the management fee, which is a percentage of received rents.

The first step was to set up automatic entry of the rent invoices using memorized transactions as shown below. This eliminated the time wasted creating the invoices one at a time each month.


Next, I created a custom report for rent receipts, for which I created a shortcut on the icon bar.


The next step is to export the report to a worksheet in an Excel template.


With the Excel workbook now open, a single mouse click runs a visual basic program that calculates the management fee and creates the check to the property owner for rent receipts less the management fee. This transaction is created in a file that can be imported into QuickBooks as a check to be printed.


After closing Excel, we return to QuickBooks and import the check transaction we just created. From the QuickBooks menu select “File/Utilities/Import/IIF Files”. I programmed the file name for this transaction to be RE.IIF. Open this file and the import is accomplished in a second.


We now have a distribution check ready to print.


This process can be further automated by using the ODBC interface from QuickBooks. This interface is included with Enterprise versions of QuickBooks and can be purchased separately for the Pro and Premier versions.

Robert Guild is Advanced Certified QuickBooks ProAdvisor in Austin, TX who conducts CPE courses for CPAs and individual training and group classes to QuickBooks users. His company at www.QBCoach.biz, maintains a sixteen-station QuickBooks lab, providing hands-on training. You can contact him directly at rguild@QBCoach.biz or follow him on twitter at QBPro