As an accounting application designed for small businesses, QuickBooks and QuickBooks Online have relatively robust reporting options. In addition to numerous, standard reports, these tools offer customization options and the ability to save customized reports for recall. But what about situations where you might need more reporting and analytical tools than QuickBooks otherwise makes available? In settings such as these, you may consider linking QuickBooks data to Excel. When you do so, you can create customized reports, schedules, analyses, and dashboards in the familiar confines of a spreadsheet. Read on, and in this article, you will learn how you can link QuickBooks data into Excel.
Open Database Connectivity is the Key
QuickBooks natively supports exporting reports and list data to Excel and text files. But a one-time export is not what we need for our reporting purposes. Instead, we are looking to create a perpetual, live link between QuickBooks and our spreadsheet. This link is one that we can refresh at any time so that our reports, analyses, and dashboards are always up to date. To do this, we will need to take advantage of Open Database Connectivity (ODBC).
Without going into a great deal of technical detail, ODBC is a vendor-neutral technology that allows an application such as Excel to access data stored elsewhere, such as a QuickBooks database. ODBC is a proven technology; it’s been around since the mid-1990’s. Further, virtually all databases – including the databases that QuickBooks and QuickBooks Online use – are written so they can share data through ODBC.
Obtaining an ODBC Driver
Although QuickBooks and QuickBooks Online can share data through ODBC, you will not be able to take advantage of this feature until you obtain and install an ODBC driver onto your computer. Think of the ODBC driver as a “middleman” between the database that stores your data (QuickBooks and QuickBooks Online) and the application that is requesting the data (Excel).
Numerous options exist for obtaining an ODBC driver for QuickBooks and QuickBooks Online. For example, if you have access to QuickBooks Enterprise Solutions, Intuit makes a driver available with that application at no additional charge. To access and install that driver, go to File, Utilities, Configure ODBC and follow the instructions provided there and in the QuickBooks Help system.
If you are not using QuickBooks Enterprise Solutions, you will need to obtain a driver from a third-party. One of the more well-regarded companies that offers ODBC drivers is CData (www.cdata.com) and in this example, we will use their QuickBooks driver to link the data into Excel.
Accessing the Data through ODBC
After selecting, acquiring, and installing your ODBC driver, you are ready to link your accounting data into Excel. Begin the process by opening your QuickBooks data file and Excel. Then, from Excel’s Data tab of the Ribbon, choose Get Data, From Other Sources, From ODBC and select the name of your QuickBooks data source as shown in Figure 1.
After clicking OK, select the table(s) you want to link from QuickBooks into Excel; in this example, we will link only the BillExpenseItems table, as shown in Figure 2. However, it is noteworthy that you can select and link multiple tables simultaneously.
After selecting the data you wish to link, click the drop-down arrow next to Load near the lower, right corner of the window and choose the Load To option, followed by your desired type of output, such as table, PivotTable, etc. In this example, we will load the data to a Table in Excel and upon doing so, all the data in the selected data source(s) appears in Excel, as shown in Figure 3.
two critical points
It is important to note two items at this point. First, once the data appears in Excel, you can do anything with it that you would otherwise do with Excel data. In other words, there are no restrictions on the data just because it is linked from an external data source. Second, this process creates a “live” link between the QuickBooks database and the Excel workbook. You can refresh the link on-demand using a number of options, including right-clicking on the query in the Queries & Connections pane on the right-side of the window in Figure 3, and clicking Refresh. Upon doing so, all reports, analyses, and dashboards you create from the linked data will have access to updated data.
Summary
Although QuickBooks and QuickBooks Online both provide solid reporting tools, complete with the capability to customize the pre-defined reports, sometimes you might need a more flexible set of options. In situations such as these, don’t forget about the ability to link your accounting data into Excel via ODBC. Doing so will require you to obtain and install an ODBC driver, but once that is done, you will have the ability to link QuickBooks data into Excel and enjoy customized reporting with virtually no limits!
Learn More About QuickBooks
You can learn more about how to link QuickBooks data into Excel by watching the video below. You can also learn more about QuickBooks and QuickBooks reporting by participating in one or more of the following K2 Enterprises learning options.
If you’re more interested in QuickBooks Online, you may consider K2’s QuickBooks Online and Other Top Accounting Software for Small Businesses.
Additionally, if you’re interested in how you can create effective internal controls structures in QuickBooks-based companies, you should consider K2’s Implementing Internal Controls in QuickBooks Environments.
