Your QuickBooks company file contains a large part (if not all) of your important company data, but our understanding of that data is limited by the views that QuickBooks provides to us. They have a lot of “canned” reports that are very useful, but every business has its own unique situation that isn’t covered by those standard reports. Today I’m going to talk about a resource that a lot of people don’t know about, the QuickBooks Library Reports. This is a wonderful source for reports as well as another way to export your data, and it is free!
Reports, Reports, We All Like Reports
Follow this link to the QuickBooks Reports Library. There is a long list of reports that you can download. Most of these are in the “QBR” format. To use one:
- Save it to a folder in your computer
- In QuickBooks, select Reports, then Memorized Reports, then Memorized Report List.
- Click the Memorized Report button and select Import Template.
- Locate the report and “open” it. It will be saved to the memorized report list.
MAKE SURE that you save the “QBR” file that you downloaded, because QuickBooks sometimes has a problem where it loses memorized reports.
Take a look at the list of reports – it is very extensive. I found a number of reports that would have solved problems that people have been asking about in the QuickBooksGroup user forums.
Some interesting examples:
- Unapplied Customer Payments and Credits: listing all payments from customers that are unapplied.
- Unbilled Mileage by Job: Unbilled mileage marked as billable, subtotaled by customer job.
- Contacts for Pending Invoices: A list of your pending sales for each customer, showing the customer’s contact name, phone and email address.
- Customer email list: Customer email address and current balance for each customer.
This is just a sample. The list is very extensive, and worth time exploring.
One way to get a look at your data in your own way is to export it into another format that you can manipulate with Excel, Access, or some other database/reporting tool. QuickBooks by itself doesn’t do a great job in letting you export data – you can get list information (item list, customer list, etc.) but it is harder to get transaction information (invoices, checks, etc.). Normally when I talk to people about exporting transactions I tell them to use one of the following methods:
- Purchase the ODBC driver (www.qodbc.com) and use a tool like Access, Excel or Crystal Reports. Note that if you have QuickBooks Enterprise you already have this. It takes a bit of work to use this tool.
- Purchase another export/synchronization tool from the QuickBooks Solutions Marketplace – there are several that work with various database/reporting tools.
- Get the Programming SDK and write your own program (very complicated).
For the most part these approaches cost money (the SDK is free, but complicated to work with and you may need a programming tool). The QuickBooks Report Library provides another alternative if you have Excel. If you scroll down the page a ways there is an Excel section that contains a QuickBooks Workbook with Wizard and a QuickBooks Workbook. These tools provide you with a way to extract transaction information from QuickBooks into Excel. You can use them as they come, or you can play with them to modify what information you can extract. This is still a bit complicated, particularly if you want to modify what you can get. If you are comfortable with playing with VBA (Visual Basic for Applications) then you can get a lot out of this. The nice thing is, it’s free (if you have Excel).
Download the worksheet and open it with Excel. Most likely you will run into some security issues – Excel will not let the program run fully unless you take care of some permissions. There is a Security Guide worksheet that will explain what to do.
Right off the shelf – enter some dates in the Update worksheet and click the button (have your QuickBooks company file open first):
When it is done, look at the Data worksheet, and you will see your transactions:
As you can see, this pulled out inventory adjustments, invoices, and assembly builds (this is just a sample).
If you are trying to get transactions out of QuickBooks this can be a simple and cheap way to start.
Let Us Know What You Try
If you try this spreadsheet, or if you use any of the reports from the library, post a comment here about what you found that you like (or don’t like)!