How to Export Data from QuickBooks Online with Ease
There’s plenty you can do with QuickBooks – it’s arguably easier to find things you can’t do with the Intuit tool. Each feature you use generates lots of data about your business. It’s accessible from within your dashboard but, for safety and easy access, it makes a lot of sense to export data from QuickBooks Online periodically.
We’ll explain how to do it – both manually and automatically, on a chosen schedule.
Export data from QuickBooks – the native way
QuickBooks Online features a native method for exporting various types of data into an Excel file. To find it, click on the gear icon in the top-right corner of your screen and select Export data.
From here, you can export the following reports:
- General ledger
- Profit and loss
- Balance sheet
- Trial balance
- Journal
Read our blog post to learn how to export check register from QuickBooks.
There are also three lists available for download:
- Customers
- Employees
- Suppliers
Toggle on and off the reports you wish to fetch, and then press the Export to Excel button.
A .zip file will download, containing separate .xlsx files for each report that you picked.
Fetching QuickBooks reports
Another place where you can export QuickBooks data is the Reports tab. There, you’ll find dozens of standard QuickBooks Online reports, as well as an option to create custom reports.
Click on any to view it. Click on the icon above to export QuickBooks to Excel file. A PDF download also is available now.
Limitations of manual data exports from QuickBooks
As you can see, it’s simple to export data from QuickBooks online with the methods described above. However, easy doesn’t always mean convenient nor sufficient. These exports are problematic for several reasons:
- You have to export manually each time you need a report. It’s suitable if you only need a few reports, but for weekly or monthly reporting on different things. Then it can become overwhelming.
- You’re limited to export QuickBooks to Excel and PDF files (for the more detailed reports). If you’re more accustomed to, e.g., Google products, you’ll have to import each file into a spreadsheet and hope it formats as expected.
- Most reports show only the basic data. For example, the report we generated for our sample employees contained only the four basic data fields. If you need to fetch, for example, their hire data or hourly wage, you’ll have to resort to other methods.
How to export data from QuickBooks automatically
A far more convenient and extensive way of pulling the QuickBooks data is with tools built specifically for that purpose. One such tool is Coupler.io.
With Coupler.io, you can set up automatic exports of many different data entities. You can fetch dozens of details about each employee, sale, customer, and many others.
You can pull this data not only into Excel but also Google Sheets and even connect QuickBooks to BigQuery. And best of all, you can set up a custom schedule for your exports.
One idea would be to fetch the latest sales weekly, right before your 11 am meeting on Wednesday. Another would be to refresh the data hourly, Monday to Friday, during your working hours, so you can always access the near real-time data. You choose what works best for you.
To get started, first create a Coupler.io account and sign in to an existing one. Click on Add new importer to create a QuickBooks importer.
On the next page, pick QuickBooks as a data source. You can export specific QuickBooks entities (the first option – e.g., customers, employees) or the existing QuickBooks reports. Choose the preferred option.
Then, hit the big Connect button to link your QuickBooks account with Coupler.io.
A popup will appear. If you’re logged in to QuickBooks, all you need to do is click on the Connect button. Otherwise, log in and follow the same procedure.
Optionally, customize what you’d like to import (Basic settings tab) and proceed to the destination settings. Here, you can choose between importing the data to Google Sheets, Excel, or BigQuery. We’ll go with the Google Sheets and QuickBooks mix.
Choose what works best for you, connect your account, and specify exactly where the data should be pulled into.
You can run the importer already, or you could set up a schedule that you want the data refreshes to follow. Here’s our example:
Save and run the importer. Here’s a piece of the payment report we were able to generate this way and import from QuickBooks to Google Sheets:
It contains all the information available in the QuickBooks system and is easy to manipulate, with all the capabilities of your favorite spreadsheet tool.
How to export employee data from QuickBooks?
You can export the list of employees directly from QuickBooks. But, as we mentioned earlier, the file will be limited. For that reason, it’s better to set up Coupler.io so it can fetch the list of employees for you – for example, once a week.
While configuring the Source, pick Employee from the Data entity list.
Proceed as previously and import to the chosen destination.
Export employee data from QuickBooks – an example
We ran the Coupler.io importer for the Employee entity. Here’s a sample of what we received.
The advantage of having this data in a spreadsheet is that you can manipulate it how you want. For example, you could sort the list of employees by seniority, build a pivot table based on locations, or feed the list of birthdays or anniversaries into another tool and make this a special day for your people.
We’re doing exactly that at Coupler.io. We export details such as hire date or an employee’s birthday into Google Sheets. Then, we feed this data to a Slack bot – in a similar manner to what we’ve described in our Slack to Google Sheets article.
Both operations happen automatically, and the data will refresh when people join or leave. The result is some pretty nice notifications pushed in our Slack channel by the most reliable kitchen bot.
How to export customer data with QuickBooks?
Customer reports are available in the Export data menu, just like the employee reports. But they are limited. For that reason, you may want to incorporate Coupler.io same as we did for the earlier data entity.
The only difference will be the Data entity – in this instance, it should be Customers.
Export client data from QuickBooks – an example
Here’s a piece of a sample report we received after running the importer above:
How to export customer data with notes from QuickBooks
When you export QuickBooks data directly from the QuickBooks Online dashboard, customer export comes without notes (and plenty of other vital details). Of course, this is something you want to have easy access to, without the need to open each customer’s tab in QuickBooks.
Coupler.io addresses the lack of notes when you use the tool to export the customer data, the same as we described above. Notes are one of the data fields you’ll see in your spreadsheet.
Please note: If no notes are available for any customers, this field won’t be imported.
How to export enterprise data from QuickBooks?
Whether you are on an Enterprise or any other plan, exporting data looks exactly the same. You can export many different data entities automatically, in the same way as we described in the earlier chapter. The available entities are:
- Account
- Attachable
- Bill
- BillPayment
- Budget
- Class
- CompanyInfo
- CreditMemo
- Customer
- Department
- Deposit
- Employee
- Estimate
- ExchangeRate
- Invoice
- Item
- JournalEntry
- JournalCode
- Payment
- PaymentMethod
- Preferences
- Purchase
- PurchaseOrder
- RefundReceipt
- SalesReceipt
- TaxAgency
- TaxCode
- TaxRate
- Term
- TimeActivity
- Transfer
- Vendor
- VendorCredit
You can also export numerous QuickBooks reports, as outlined in our article on exporting QuickBooks reports.
Please note that any custom reports you create with Enterprise’s Advanced Reporting won’t be, at this point, available for automatic export with Coupler.io. You can still export it manually, as we explained earlier.
How to export estimate data from QuickBooks?
If you wish to fetch the estimates. All you need to do is swap the data entity in Coupler.io. Follow the earlier steps to export QuickBooks data automatically. When picking the entity, choose Estimate, and run the importer.
The latest estimate data will be quickly fetched into your spreadsheet and update automatically according to a chosen schedule.
How to export time data from QuickBooks?
Exporting time entries from QuickBooks works in the same fashion. Set up a Coupler.io importer and pick the TimeActivity data entity.
Your spreadsheet will fetch all the time entries. Here’s a piece of it:
How to export sales data from QuickBooks?
To retrieve sales data, your best option would be to import QuickBooks reports with Coupler.io. Follow the earlier steps. On the Source stage, pick QuickBooks Reports as an Application (instead of just QuickBooks).
Then, pick from one of the four available sales reports.
You can also choose the period you want to generate the report. As well as generate more detailed columns.
A simple import of Sales by Customer looks like the follows:
How to export invoice data without tax from QuickBooks?
You can pull the list of invoices from QuickBooks in the same way we pulled estimates, time entries, customers, and a few other entities. When setting up the source, be sure to pick Invoice this time.
Here’s a piece of a sample import:
Note that each invoice will take a few lines of your spreadsheet. There will be one line for each of the products or services mentioned on an invoice. Plus, a separate line summarizing what’s on the invoice (even if there’s just one item).
To see only the totals for each invoice, use a simple filtering option of your tool on the column named Line.DetailType and allow only entries named SubTotalLineDetail.
Going back to the full view, you may need to fetch the invoice data without tax. The imported file is quite detailed and contains all the information you need. Look at the following columns:
- GlobalTaxCalculation states whether tax is included in the price, excluded, or not applicable.
- TxnTaxDetail.TaxLine.0.TaxLineDetail.NetAmountTaxable shows the net taxable amount.
- TxnTaxDetail.TotalTax displays the total amount of tax that was charged.
- TotalAmt is about the total amount on an invoice
Depending on your taxation policy, find the most applicable column and apply it to your calculations.
Benefits of exporting data from QuickBooks
Regular, automated data exports from QuickBooks can be beneficial both for you and your business.
Working with QuickBooks data, you frequently need to look up different numbers, check reports or generate them yourself. Pulling the data that matters to you directly into a spreadsheet, makes it so much simpler – not only to access it but also to manipulate it or feed it to other services.
If you can automate QuickBooks data export, it’s even better. You’ll get rid of mundane tasks of exporting reports and, perhaps, also converting them into the desired output. You’ll make better use of your time.
Analyzing data from a spreadsheet becomes more manageable when using tools such as Tableau or Data Studio. You’ll have a better overview of the state of your finances and will have a QuickBooks backup available, with barely any effort on your side.
That’s all folks, thanks for reading!
