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 MS 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 to MS Excel Files.
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.
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.
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.
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.
CONTACT QUICKBOOKS CUSTOMER SERVICE
Towards the end of this article, it is believed that one might be able to successfully import or export MS excel files or transactions list in QuickBooks desktop. However, for further assistance, you can connect with our QuickBooks Customer Service team and accounting specialists today! Our technically sound experts will ensure to answer all your queries instantly.
Comments