One of the most compelling reasons to use QuickBooks Pro, Premier, or Enterprise is the amount of built-in reports and how powerful customization of such reports can be. However, where QuickBooks ends, an add-on utility called QQUBE. I will describe this utility as an “Advanced QuickBooks Report Generator” inside Excel, but with QuickBooks data.
QQUBE is a Data Warehousing utility that mirrors your entire QuickBooks Database into a very fast PivotTable-ready dataset. If you do not know what a PivotTable is, I recommend you read this first. However, it is also important to understand that this tool is designed for QuickBooks Desktop only (Pro, Premier, Enterprise) and it is an investment of $425 at least (pricing varies on number of companies.
Now, on to my tutorial on how to use this program. First, once the software is installed You will see a Utility called the QQUBE Configutation Tool, that basically keeps track of which company files are synced and ready to be used, plus some other utilities to to allow the user to choose the TYPE od data available in Excel to be used to create reports.
After the file is synced up, then you can launch the tool from within Excel, in the QQUBE tab:
Once the tool is loaded, a regular Pivot Table loads, however another tool loads to the left of it which is the entire QuickBooks dataset organized into logical subjects which contain the transaction details in one folder, and list and date folders to organize and filter the transaction details in the pivot table. There are over 1750 fields available to use to create your report or analysis.
After choosing which datafield from the QQUBE Add-in dataset, then onto doing Pivot Tables as normal:
In this particular example, you see sales by item, then by customer with the total Sales Price, COGS amount, and Profit Margin. Something that you cannot build within QuickBooks. In QuickBooks you may see your profitabiltity per item (items sold vs bought) on a report, but not possible to see at the customer level a well. Furthermore, I could add additional data from the customer table, such as customer CITY, and filter it at the City level.
If you are interested in discovering QQUBE, there are two additional resources available:
- 1.25 Hour video in YouTube with a complete overview of the tool and some very good examples
- Request a fully functional Trial http://www.clearify.com/shopcusttrialrequest.asp
Where QQUBE really outshines is in Job Costing applications, I assume that the developers of QQUBE were trying to resolve the limited Job Costing Reporting capabilities in QuickBooks; please no dot misinterpret, QuickBooks is very powerful in reporting certain job costing elements, but it is not “deep” in its reach so say the least, most predominately in the areas of WORK IN PROCESS (WIP).
Please feel free to email me hector@garciacpa.com with any special reporting requirements you have and I’ll gladly work with QQUBE to find the right solution for you.
If you like this article, I also recommend:
https://qbkaccounting.com/quick-pivot-tables-excel-quickbooks-data/