The Cdata Excel Add-in for QuickBooks Online

by

Cdata is data connectivity software that provides bi-directional access to live app, database and Web API data through standard drivers. Cdata offers Excel Add-ins that work with QuickBooks Desktop, QuickBooks Online or even QuickBooks Point-of-Sale (Desktop) data. 

These Excel Add-ins make it easy to work with your QuickBooks data.

In this product review, we’re going to look at the Cdata Excel Add-in for QuickBooks Online, which not only allows you to query and retrieve data from QuickBooks Online in Excel, but also gives you the ability to edit, add and delete data via the connection.

Installing the Cdata add-in adds a Cdata tab to your Microsoft Excel software. This Cdata tab is similar to the (regular) Data tab, because it allows you to obtain data from external data sources, manage connections, and build queries.

Once Cdata is installed, you’ll connect to your QuickBooks Online account. In order to do this: 

  • Open Excel
  • Select the Cdata Menu tab
  • And from the Cdata ribbon, click the "From QuickBooks Online" button

Cdata will display the Connection Wizard window. If you intend to update, insert (add) or delete (remove) data from QBO, make sure you select the appropriate option(s) under the Enabled Operations section of the connection wizard.

You’re ready to connect to QBO, so click the green “Connect” button. Intuit uses the OAuth authentication standard for QBO, so you’ll be taken to a sign-in screen where you’ll need to enter your QuickBooks Online account email or user ID. 

You’ll also need to enter your password. After entering that information, click the Blue “Sign In” button.

Intuit's OAuth requires the authenticating user to interact with QuickBooks Online to authorize permission for every connection. So, if you have more than one QBO account under your login, you’ll be asked to select the account you want to connect prior to the actual authorization.

QuickBooks will ask you to authorize the sharing of your data between Excel Add-in for QuickBooks Online and Intuit. This is how you authorize QBO to exchange data with Cdata. 

Click the blue “Authorize” button to link QuickBooks Online to Cdata. Cdata now completes the OAuth process.

Once you authorize the connection, you are returned back to the Cdata Excel Add-in and will be notified that the connection to the server was successful. Click OK to proceed with finalizing the connection and beginning your query.

This is a good point to pause in our process to share an important point. The Cdata Excel Add-in for QuickBooks Online isn't limited to one QuickBooks Online file. You can connect with as many different QBO files as you have authorization. The only limitation is that you can only connect with one QBO file at a time.

Unlike many “cloud hosted” apps for data connectivity to QBO, you would need to purchase (even on a monthly basis) the same app multiple times so you can connect to each of your QBO files.

Because Cdata technology is designed to work with a lot of different apps, databases and web-data APIs, you can purchase other “data connectors” for Cdata that enables you to connect into any of those other applications as well.

Cdata provides you with an easy way to manage all of your connections from its “Existing Connections” window accessible from the Cdata ribbon.

So, now we’re back to Excel after making our connection to our QuickBooks Online file. Cdata displays the “Data Selection” window. 

From this window, Cdata can access QuickBooks Online tables, views and stored procedures defined in schema configuration files.

Tables are those available within QBO that can be modified consistent with the requirements of the API. Views are QBO tables that cannot be directly modified. They’re typically read-only data.

Stored Procedures are function-like interfaces to the data source. They can be used to search, update and modify information in the QBO data. Sometimes it’s necessary to update data available from a view using a stored procedure, because the data does not provide for direct, table-like, two-way updates. 

Here, the retrieval of the data is done using the appropriate view or table, while calling a stored procedure does the update.

You can select the appropriate table or view from your QuickBooks Online data. You don’t necessarily need to know the table or view names of your QuickBooks Online Data, because Cdata has all of the currently available data listed in a convenient drop-down to make your selection(s). 

Since the “Account” table is the very first table in the list of QBO tables, it’ll be displayed by default in the data selection contents. The various columns of data making up the table will be listed, along with the column attributes of type, size, key and read-only status.

By default, the sheet name for your Excel sheet will be the same as the table or view name. To refine your query, you can uncheck any columns of data you don’t wish to retrieve. 

Select the maximum number of rows to be retrieved via the Query. Now specify the beginning row for your Query.

If you want your data to automatically refresh from QuickBooks Online, you’ll need to check the “Enable Auto Refresh” selection box. You’ll also need to select the number of seconds between auto-refresh updates.

The various selections you make within the Data Selection window will auto-populate the actual Query (shown in the Query display box) that will be returned to QuickBooks Online.

Without getting too technical, the Cdata Excel Add-in for QuickBooks Online provides functions that are similar to those available within most standard databases. 

There are four functions that can be implemented within the Cdata engine that can cross multiple data sources using consistent API principles. They include "string functions," "date functions," "math functions" and "parameter and nesting SQL functions."

When your skills improve, you can actually write or supplement queries (like the one shown above) in the Query display box of the Data Selection window to do almost anything you’d otherwise do in a direct database interface.

But for purposes of this example, we’re going to perform a very simple query of our QBO Account table. In other words, we want to get a list of the Chart of Accounts from our QBO file. 

When we click OK at the bottom of the Data Selection window, as we have configured it, our query of QBO Accounts begins to populate our Excel worksheet.

Here is our completed query. Check out the field I highlighted in yellow in the picture below.

Do you see that the Name field in Row 5, which is ID 92) of the data says that the account is called “Automobile?” 

What I want to do is change this account name to “Auto Expense,” so I’m simply going to edit my Excel worksheet to change the name. In the picture below, you can see that I have made my change.

I’ve gone ahead and blown up my change so you can see it a little better. I’m almost ready to save my change. Notice that I’ve also clicked on the row number at the left side in order to highlight the entire row.

After highlighting the row, I’m simply going to go up to the Cdata ribbon and click on the Update Rows button. When I do, Cdata not only saves my Excel worksheet with the change, but simultaneously edits/updates my data in QuickBooks Online.

If I log into QuickBooks Online and open the Chart of Accounts, sure enough, there is my change. Cdata has updated my QBO account from Automobile to Auto Expense.

I’ve once again highlighted it in yellow to make it easier to find.

Working with Cdata's Excel Add-in for QuickBooks Online is so easy I was able to eat a slice of whip-cream covered pumpkin pie while making this exact query and data substitution when I first started playing with it over Thanksgiving.

Cdata will allow you to connect to QuickBooks Online through easy-to-use, bi-directional data drivers, just like I showed you, giving you the ease and power of Excel to manipulate your data in a real-time connection. 

And, perhaps best of all, there are no restrictions of needing one Data-connectivity app subscription for each one of your QuickBooks Online companies, because Cdata can connect to as many QBO Companies as you have authorization for.

If you want more information on Cdata Excel Add-in for QuickBooks Online just visit their website.