Retrieve QuickBooks Data with Genius Sheets

This is the next post in the add-in series, where we are exploring various add-ins that expand the capabilities of Excel. In this post, we’ll take a look at the Genius Sheets add-in. In summary, it enables us to get QuickBooks Online data into our Excel workbooks. The add-in provides several custom functions that we can use in formulas to retrieve specific values. It also provides the ability to retrieve an entire report.

App Store

The Genius Sheets add-in is available in the app store. To get to the app store, just click Insert > Get Add-ins. Install the Genius Sheets add-in and you’ll be ready to go.

Once installed, you’ll see a new Genius Sheets button on the Home tab.

Click the Genius Sheets button to see the add-in pane on the right side of your Excel window. Use the Sign Up link to create an account. After you create a Genius Sheets account at geniussheets.com, you’ll be able to connect it to your QuickBooks Online account. Once you’ve connected your QuickBooks Online account to your Genius Sheets account, you’ll be ready to login to the add-in and begin to pull data into Excel.

Note: depending on your version of Excel, you many or may not have access to these add-ins.

Walkthrough to pull QuickBooks data into Excel

In the add-in pane, the first step is to login using your newly created Genius Sheets account:

After logging in, you can pick the desired QuickBooks company. You’ll then see a variety of choices in the add-in pane:

The primary commands are:

• Function Detail – provides information about the custom functions you can use in your formulas
• List Categories – view the QuickBooks categories for the selected account
• Pull Reports – retrieve an entire report
• Refresh Data – update formulas and reports with current QB data

Let’s walk through a couple of examples. First, we’ll see how to retrieve an entire report. Then we’ll look at how to use a custom Genius Sheet function in a formula to retrieve a specific value.

Get Entire QuickBooks Report into Excel

To retrieve an entire report, use the Pull Reports button. You’ll be able to specify the date range and select from a variety of reports:

If we select Profit and Loss, and then Pull Consolidated Financials … bam:

We can pull the Trial Balance as well … bam:

Once the trial balance is loaded, we can use the values in other reports. If the QuickBooks categories do not exactly match the report labels, we can use an intermediate mapping table as discussed in my Power of Mapping Journal of Accountancy article.

In addition to retrieving entire reports, we can use a function to retrieve a specific value.

Use Functions to Retrieve QuickBooks Data

The add-in offers several functions that enable you to retrieve a specific value:

• GS.REVENUE(category, startDate, endDate)
• GS.COGS(category, startDate, endDate)
• GS.EXPENSE(category, startDate, endDate)
• GS.OTHERINCOME(category, startDate, endDate)
• GS.OTHEREXPENSE(category, startDate, endDate)

The category argument in each function is a QuickBooks category, for example, Income, Job Cost, Meals, and so on. If the endDate is not specified, the function will return the full month of the startDate argument.

We can pass the argument values directly or through a cell reference.

For example, we could enter the category and date arguments like this:

=GS.EXPENSE("Contractors", "Jan-2020",)

Or, reference cells, like this:

=GS.EXPENSE(A2, B1,)

This is illustrated below in the formula in B2 below:

As you can see, this is far faster than opening QuickBooks, navigating to reports, selecting the report options, and then doing an export (or copy/paste). You can quickly refresh your workbook to retrieve any updated values in QB on an ongoing basis as well.

Conclusion

This add-in will save a ton of time and be helpful if you use QuickBooks online and often need to get values into Excel. If you have any other add-ins you’d like to suggest, please share by posting a comment below … thanks!