EXCEL and QODBC

This is the first of two articles that I will devote to using Excel and QODBC to extract and report on QuickBooks data.  There are many Excel gems that users either don’t know about or think it is too difficult. In this first article, I will discuss the Excel Query Wizard and Using Excel Query to generate existing QuickBooks reports. Both of these operations should be within the capabilities of the novice user.  In next month’s article I will show some advanced operations including pivot tables.

Excel the report writer?  YES!

In many of the lectures and presentations I give regarding QODBC, I discuss the pros and cons of using tools like Excel, Access, Crystal Reports, Xcelsius or other report writing mediums. I must admit that I tend to give Excel less air time because of three things:

• Dynamic Ranges make it difficult for cell references
• The inability to relate more than a couple of tables using the data query Wizard
• Better tools exist that are specifically designed to write reports

Dynamic ranges cause problems with formatting and cell referencing formulas.  If you retrieve 40 rows one time and 42 the next, the cell you may have referenced in a formula will contain different data, and thus a different result.

When using the Microsoft Query Wizard, you can only tie together two tables using an outer join.  You CAN however create a manual SQL statement that uses more than two tables, but most people are not SQL experts.

A product like Crystal Reports:

• Has been around for 15 years
• Has a community of 14 million users
• Is taught and showcased at Intuit conferences
• Has been – and still is – packaged with most other accounting packages.

But, there is another side of the story that is waiting to be told! There are many business cases and upsides to using Excel that we tend to overlook.

• A user may not want to pay for, or learn how to use, a report writer
• Most people are familiar with Excel
• You can query virtually any existing QuickBooks report without needing to have an advanced knowledge of  SQL
• Pivot tables are rich in data analysis features; Excel includes a Wizard for this operation
• The file you create on one machine is generally portable to another

Excel Query Wizard

Microsoft Query is a free tool that allows you to query against any ODBC compliant database, such as SQL server, or Access, - or QuickBooks.

This invokes the Microsoft Query Wizard which allows you to choose tables and fields for your query:

Using Excel Query to generate an existing QuickBooks report

For many small businesses with smaller QuickBooks files, there is a mechanism in the QODBC driver that allows us to pull existing QuickBooks reports without having to export cut and paste.

If you go to http://doc.qodbc.com/qodbc/20/reports/sp_report_detail.html, you will see a list of reports that are available using the QODBC driver, and you will notice that each row has a link to a query example that you can use to copy and paste right into Excel.

For example, if you click on the “Example” link for A/P Aging Detail (http://doc.qodbc.com/qodbc/20/reports/sp_report_sql6313.html?sp_report_name_id=45) you will see the following:

Notice that the query begins with “sp_report”.  This stands for “Stored Procedure Report” which is a simple way of telling you that you don’t have to construct a long SQL statement to get what you want – it does that FOR you.  You simply provide:

• Sp_report - starts the command
• Name of the report - APAgingDetail
• Show - instructs the query that you are getting ready to list the fields you want
• Date, RefNumber - field names you want to show
• Parameters - tells the query you want to provide a filter
• AgingAsOF = ‘Today’ - tells the query you want to filter the report by telling it to calculate aging as of ‘Today’

You can paste this into Excel’s Microsoft Query and get the exact A/P Aging Detail report that would show in QuickBooks (for this example I removed the “Title” fields from the query for better readability):

There are advanced features of this “sp_report” query that we will cover in a future article, but there are some simple things you should know.  You can:

• Save this query and spreadsheet
• Edit this query to make changes
• Refresh this query to get real time data changes
• Have multiple queries in one spreadsheet
• Return ONLY data rows (i.e. without subtitles, subtotals, etc.)
• Send this spreadsheet to somebody so they can use it on THEIR QuickBooks file

Easier than you might think

I am not going to pontificate about why ODBC technology should be used by more people.  Let’s face it, writing SQL queries, or figuring out how QuickBooks data tables tie together to get a job cost report, is not the easiest thing in the world – even for experts.

But there are some simple things that you can learn that may save time for you or your client by not having to export, cut, paste and re-format every time you want to analyze QuickBooks data.