Q2Q Custom Reporter for QBO is Easy to Use

by

A few weeks back, I wrote an article about one of the new data utilities from Patrick Frost at Q2Q.us. In this article, I want examine a different kind of tool that Patrick is featuring for QuickBooks Online.

In my way of thinking, this isn't so much a data utility as it is a reporting tool that makes use of both Microsoft Access and Microsoft Excel. This tool essentially has been adapted to work with QBO from the version that worked with QuickBooks Desktop products.

Probably the biggest reason why the QuickBooks desktop version of the tool was not used by many ProAdvisors was that QuickBooks Statement Writer was available for QuickBooks Enterprise customers who are the most likely candidates needing a more sophisticated set of financial statements. 

So even though QB-Desktop users might not have used the Custom Reporter from Q2Q, I can see more QBO users potentially using the online version of Custom Reporter since QBO has limited report customization abilities.

As with the other tools from Q2Q, the product is built around Microsoft Access. And even though this tool works with QuickBooks Online, it's still a desktop software product that communicates with QBO via a browser established link. 

One big advantage of these desktop-based products, as opposed to cloud based apps, is the ability to connect to multiple QBO companies without having to pay for a separate subscription or app per connection.

With the Online Custom Financial Reporter, you can quickly and easily produce presentation quality financial statements.

This Reporter allows you to use Excel, one of the most popular tools ProAdvisors use, to create customized financial statement formats and then merge QuickBooks Online data into those formats. 

The same statement formats can be used over and over again, and for all of your clients or, you can have different formats for different clients all linked to QBO via this reporting tool.

Here are a few of the really neat capabilities of the Online Custom Financial Reporter:

  • Provides a default Excel workbook containing a set of financial statement formats that you can modify or copy. You also can create your own format workbooks.
  • Other than the columns of data that are available, you have total control over the design of your financial statement formats within Excel.
  • You can have as many heading and data rows as you need, and use any Excel fonts, type styles, number format, colors and functional capabilities.

Installation and Getting Started

I always suggest you "try before you buy," and this tool is no exception. Fortunately, you have a trial available for download. You can purchase and register the product later if you decide it is the right one for you.

Once you have downloaded the product from the Q2Q website and launch the installation process, the installer will guide you through a few simple steps. Be sure to review the system requirements (shown on the right below).

During installation, you'll be asked to confirm the location where you want the product installed and to confirm the installation completed. After the Reporter installs, you'll need to install the companion "encryption engine," which is necessary for sending/receiving QBO data across the internet from a desktop-based product. 

You'll need to agree to the terms of use for the product. As with most software, this is the End User License Agreement (EULA). Speaking of the EULA, you'll also need to register the product if you buy it. In order to complete the registration, open the Settings page by clicking on the Wrench Icon (shown below).

When the settings page opens, click the "Enter Registration" button. When the Owner Registration page opens, enter the owner's name and owner ID just as you received them on the order confirmation email from Q2Q. (Yes, I redacted my registration ID).

Close the firm to save the registration information. You'll be returned to the main page of the Reporter.

To begin using this tool, you must first connect to a QBO company file. Of course, as with all of the Q2Q tools, all you really do is follow the numbered steps.  

If you are unclear as to what's going on, look in the Processing Status window below Step 7. It let’s you know where you are in terms of what is taking place.

Clicking on the "Add New" in Step 1 (the Big Blue arrow above) opens the QuickBooks Online Connection window as seen below. This window allows you to add or update QBO companies with which you want to use this product.

Begin the process of adding a new company using the Open button. Don’t worry, we'll get to the "Add" button in a little bit. Reporter launches our default browser and takes us to the Q2Q QuickBooks Online Connector web page so that we can connect our QBO file.

Now, click the Connect to QuickBooks button at the bottom of the web page to launch the QBO Oauth process. You'll need to log in using your QBO credentials – either your email or user ID and your password.

When asked, be sure to Authorize the sharing of your QBO Data with the QBO connector that exchanges the data between your QuickBooks online account and the Q2Q Reporter.

Once the connection confirmation web page is displayed on your browser, you can close your browser and finalize the connection by clicking the Add button back on the QBO Company page of the Reporter tool.

When you do, the connection information will be displayed on the page. (Note: The Company Realm ID has been redacted from the illustration below to maintain file security.)

While you can connect multiple QBO Companies to the Reporter tool, you can only connect one of them at a time. You'll need to go through the process of adding each company individually to add additional companies.

Closing the QBO Company page returns us back to the main window of the Reporter. The Realm ID of the company now will appear in the drop-down list for Step 1, but I have redacted it in these illustration (although, the name of my QBO Company appears above the wrench).

Step 2 provides the date information associated with the trial balance data you want to export, including the current period beginning and ending dates, and the previous period data to be used.

Once you have set your Export Criteria in Step 2, you're ready to export the financial trial balances from QBO by clicking the Export Data button at the right side of Step 3.

The Reporter exports multiple trial balances from QuickBooks Online and puts the data into your Excel workbook. While the Reporter is sending queries to QBO and receiving responses, you'll see various messages flash within the Processing Status window.

When the process is finished, the Reporter will display the message "Finished Exporting Data" in the Processing Status window.

Optionally, you can View an on-screen report of the data downloaded before you send the trial balance data to Excel. Click the View button at the right end of Step 3.

This step really isn't necessary, but it you're curious if you received the data you intended, you might want to verify the export.

Before sending the exported QBO data to Excel, you must first locate the Excel worksheet that Q2Q created during installation of the Reporter. Click the Browse button in Step 5 to locate the Excel workbook with a set of financial statement formats that the Financial Reporter created during installation.

The Reporter will take you to the default location based upon the installation path you selected.

If you choose to change or add your own Excel workbooks in alternative locations, you'll need to browse to the location of the workbooks you create.

Selecting the file from the browse window posts the location and file name into the window on Step 5. We're now ready to transfer the exported QBO data from the Reporter into the Excel workbook. Click the Update Excel button at the right end of Step 6.

Click the Open Excel button at the right end of Step 7 to open the Excel workbook to the trial balance tab so you can begin the process of configuring your data.

I want to call your attention in the above illustration to the two columns with arrows. This is how we tie information together on the trial balance tab of the worksheet. The big blue arrow above points to the Account Names that were imported from QBO, while the big yellow arrow points to the names that ties the data to your financial format worksheet rows.

While the example below is a little complex to look at, I think it helps explain the relationships that are going on here between the trial balance information and the rest of the financial format worksheets.

The data in the Trial Balance is linked to your financial format worksheets using the Excel SUMIF function that matches user definable "Row Names" in the financial statement format sheets with the same names as in the Trial Balance sheet.

To create a link between a financial statement format sheet and the trial balance sheet, you give a row in each financial statement a row name. Then, in the trial balance sheet, you assign the same row name to all accounts to be accumulated on that same row.

Once the mapping is complete, the data in the Trial Balance sheet automatically flows to your financial statement format sheets based on matching row names.

Look closely at the above depiction and you'll see that I have called both the Loan Account and Wells Fargo bank accounts cash on the left. That name corresponds to the rows appearing in column A of the Balance Sheet Assets on the right. 

Our total Cash for 2016 is $6905, which is the sum of both the $2918.36, plus the -$23.00. The two thin blue arrows merge to form one amount combined under Cash.

The thin purple line represents a one-to-one relationship between an account and a row on the balance sheet, in this case A/R (accounts receivable).

The Reporter tool saves the row names you assign to accounts so that the next time you export data from your QBO Company, the previously assigned row names are automatically included on your Trial Balance sheet. Any new accounts not previously mapped will appear in a special report alerting you to the lack of mapping.

For more details on the mapping process, you should watch the YouTube video that was created for this product.

Of course, you're also free to change the exact names you want displayed on your financials.  For example, I simply change the name "Cash" under my assets to "Cash and Other Cash Accounts." This in no way changed either the linked data or the data itself.

There essentially is no limitations in the organization of your data, so long as you preserve the relationship in the naming scheme by designating each Excel row with the appropriate row names that link the data together. 

The Excel workbook created by the Reporter will contain the default financial statement formats, but you can have as many financial statement format sheets as you need to meet your reporting requirements.

The provided workbook includes sheets that have been formatted for Balance Sheet Assets, Balance Sheet Liabilities & Equity, a Profit and Loss Statement, a Cash Flow Statement, and even a Cover Sheet and Accountants Report. You can copy and modify the default workbook to meet your needs.

One nifty feature is the ability to print all of financial statement pages with a single click.

Personally, I like this tool because of the simplicity and, perhaps, because I'm a long time fan of Q2Q utilities. I had one little glitch that arose during my first use following installation, but a reboot of my computer resolved the issue. I think it had something to do with failure to finalize the program write to the Windows' Registry. 

While there are a lot of very fancy tools on the market for use with QBO in terms of financial reporting, they're a lot more expensive as well. And remember, you can use this tool with more than one QBO file.