If there are things you want QuickBooks to do but can’t find a way to do it, you need to know about the QODBC driver for QuickBooks from QODBC.com. ODBC in an acronym for “open database connectivity” and with this driver you can read data from QuickBooks to create custom reports or manipulate it and post new transactions back into QuickBooks. Below is an example of a cash flow manager I created in EXCEL using the QODBC driver.
First, let talk about how we get exactly the information we want from QuickBooks through the ODBC interface. Below is a example of a query which I created from within Excel. It uses standard SQL to establish the specific information to be retrieved and the selection criteria. Once created, this query is stored for future retrieval into Excel so the information can be updated with a simple “refresh” command.
In this example, I have set up queries to retrieve bank balances, open invoices, and open vendor bills from QuickBooks, created a separate worksheet for “Other Budgeted Items” such as recurring items or anticipated but unrecorded items, and used the VBA programming that comes with Excel to automate the process of creating and manipulating the cash flow worksheet depicted below.
The idea is that we have substantial control over our disbursements but we usually have to predict when we will get money owed to us. For example, we have payroll due the end of next week and we are considering paying some vendor bills today. However, if we pay our vendor bills and don’t get paid an outstanding invoice due next week, we won’t be able to cover payroll. The question is, “how confident are we that we will receive that payment?” Let’s postpone some anticipated cash receipts for a week and see what that does to our cash balance.
As you can see, our cash balance and net cash flow for each week are automatically recalculated.
This is just one example of how you can use the QODBC interface between Excel and QuickBooks. I showed an example in my last post of the calculation for a management fee based on rents received and creation of a detailed investor check for rents received less the management fee and expenses. In that case, the user have to import the check into QuickBooks through a QuickBooks utility. With the QODBC interface, I can automatically write the check into QuickBooks from Excel using the VBA programming tool.
The QODBC driver even works with QuickBooks Online. You can check out the QODBC driver at www.qodbc.com
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