December 30, 2020

How to Get Quickbooks Data into Excel

Quickbooks and Excel are vital tools for many businesses, but it's not obvious how to link them together.

By
Mack Grenfell

Many businesses today rely on both Excel and Quickbooks. They use Excel for their planning, reporting, and measuring, and Quickbooks to manage their company's finances.

Given how popular both of these two tools are, natural to want to integrate them, and to be able to import your Quickbooks data into Excel.

Before we dive into the specifics of how to get your Quickbooks data into Excel, let's briefly look at why Excel is so commonly used in tandem with platforms like Quickbooks.

Using Excel for accounting

There are a number of reasons why Excel can be useful for manipulating financial data from an accounting platform like Quickbooks:

The reasons above explain in large part why Excel is so commonly used alongside accounting services like Quickbooks.

The downsides of Excel

While Excel is great for manipulating, reformatting, and visualising data, it's less great at getting that data in the first place.

Even most Excel power-users have limited experience of integrating Excel with live data sources.

Having access to live data is critical when it comes to accounting. Because accounting data can change so frequently, and unpredictably, it's vital that any tools you use have a real-time connection to your underlying Quickbooks data.

How do I get Quickbooks data into Excel?

If you're looking to get your Quickbooks data into Excel, you have two options to choose from.

Manual Quickbooks exports

Getting a static report from Quickbooks into Excel is fairly easy. All you need to do is:

  1. Log into Quickbooks and navigate to Reports.
  2. Find the report that you want to export to Excel.
  3. At the top of the report, click Export, and then Export to Excel.
  4. Save the file wherever you like, and proceed to open it in Excel.

If all you need is a quick one-off export from Quickbooks to Excel, then the above will work perfectly.

In many cases though, you wont want to have to go through this process of exporting the data and re-doing your work in Excel each time your Quickbooks data updates. If this is something you want to avoid, then you'll have to use the next method

Connecting Excel to Quickbooks

If you need a live connection between Excel and Quickbooks, then this is the method you'll need to take.

To get live Quickbooks data into Excel, you'll need to use something called Open Database Connectivity (ODBC). ODBC is a piece of technology that allows apps like Excel to communicate with external databases, such as the one that holds all your Quickbooks data.

To use ODBC to get your Quickbooks data into Excel, you should:

  1. Purchase a driver that will allow you to request the data from Quickbooks via ODBC. The most popular driver can be found here.
  2. Once you've purchased your driver, install it on your computer.
  3. Open Excel and, in the Data tab, click Get Data → From Other Sources → From ODBC and select your driver (CData Quickbooks Source if you bought from CData).
  4. Choose one (or more) reports that you want to pull from Quickbooks, and hit Load → Load To and then your preferred report type.

Once you've done all this, your data will start flowing into Excel.

There has to be an easier way

If the method above looks needlessly painful, I can sympathise.

Not only is it a complex method for what should be a simple task, but it also requires purchasing a driver that starts at $249 per year.

Fortunately though, there is an easier way to be able to model and manipulate live Quickbooks data.

Using Causal instead of Excel

Causal is a browser-based financial modelling tool which lets you build interactive forecasts and dashboards in minutes.

It goes beyond the features of traditional spreadsheet-based tools like Excel, and lets you create fully customisable models that you can share easily with your team.

You can build models on top of your Quickbooks data, so you have live financial models that update in real-time.

How does Causal work?

Whereas Excel is built around rows and columns, Causal models are built out of variables.

Instead of writing formulae full of arbitrary cell references, Causal lets you write natural language formulae that show how your variables are related.

Once you've constructed your variables, you can build charts and forecasts with the click of a button.

All of your financial model's inputs are made visible to viewers, meaning that they can tweak your assumptions to see how the model changes in real-time.

Integrating Causal with Quickbooks

Causal comes with a range of accounting integrations, including one that allows you to import your Quickbooks data in seconds.

To import your Quickbooks data, all you have to do is:

  1. Connect to your Quickbooks account.
  2. Choose the dataset that you'd like to import.

You'll then see a preview of all the data that's been imported, and be able to reference the data in your model's variables.

Try Causal for free

Want to test out our integration with Quickbooks? Just click here to sign up for a free account, where you'll be able to quickly get started building models off of your Quickbooks data.

While you're here...
If you enjoyed this post, then you might also like our product — Causal. It's a new way to build models, visualise data, and communicate with numbers. Click here to learn more.