IDEA Users Working Remotely: We’re here to help! Please contact our IDEA Help Desk to ensure there is minimal disruption to your daily workflow.


Blog Image

Using IDEA to Import QuickBooks General Ledger Data

How much has changed in accounting since Luca Pacioli produced the first standard for the general ledger? Danielle Supkis Cheek, CPA, CFE, CVA shared insights into the growth of the General Ledger at the 2017 IDEA Innovations Conference in Houston, guiding participants through the General Ledger’s evolution, dating back to Pacioli, also known as the Father of Accounting (c. 1447-1517).

Today’s General Ledger is typically the starting point to a vast amount of detailed information, which may be posted at a summary level from the supporting detail ledgers. In some industries or software packages, (such as QuickBooks) the trend is to record more transaction-level detail in General Ledger. However, acquiring the data in a readable, usable format is a challenge. Cheek showed an example of a QuickBooks the General Ledger Export. The conditions for the sample are familiar to most auditors.

In a small to medium business, there is a lack of controls. With limited full-time employees (FTEs) record keepers and signers are the same person in many businesses. Although one-to-one relationships are easier, they are innately controlled- and monitor-free. Another issue is the version of QuickBooks between desktop and online editions. Cheek pointed out that it is a common practice to audit by transaction number. To audit by transaction number, you will need to normalize the data set in IDEA by visually coding the export, as you cannot sort in this manner in QuickBooks.

Importing QuickBooks Data into IDEA




  1. Go to “Customize” and add “Trans #” and “Account” as an additional column. [Note: Splits are still an issue.]
  2. Export the report to Excel.




  1. QuickBooks reports in Excel, are not a “clean” Excel import. In appearance, they are more like a print report. To facilitate the IDEA import, do the following:
    1. Import the Excel File (from QB) without checking the First Row Contains Field Name or Import Empty Numeric Cells as 0.
    2. Export the file created in step a to a Text Fixed Length file.
    3. Import the text fixed length file using the Print/PDF report process.
  2. Summarize by account number, then account name, then none. Then total by debit. This validates the completeness of a General Ledger with Trial Balances.
  3. Join the beginning balance to the summarized general ledger, using the beginning balance as the primary file. [Note: Make sure to take all records in both files and match on account number. If it does not work, make sure to check that the field types match.]
  4. Merge contents in account number and name and append a virtual character filed using: @if(accountnumber<>” ”, accountnumber, accountnumber2). Then append the field for calculations of expected ending balance.
  5. Conduct a new join to find the ending trial balance. [Note: Be sure to match on the combined accountnumber and to select all records in both files.
  6. Append the field for the calculation of the difference; and your data is ready for further analysis.

The Financial App (SmartAnalyzer) is also effective for finding: duplicates, out of balance journal entries, missing entries, date specific entries, time specific entries, and many more points of interest like journal source and period. Benford’s Law can be applied to find absolute value.

IDEAScripts can also be used to find out of balance transactions, numbers ending in 999 or 000, key terms and credits to cash. IDEAScripts are available to all supported IDEA users. To get there from inside IDEA, click the “File” tab, then click the “IDEAScript Vault” tile.

Special thanks to Danielle Supkis Cheek for sharing her expertise on this topic at the 2017 IDEA Innovations Conference.

Best Practices , CaseWare IDEA , Importing

Posted By

By IDEA Help Desk Team
The IDEA Help Desk is staffed by CIDA-certified data analytics experts. Our team has one goal - to help you get the most from IDEA. In addition to providing one-on-one support for using any IDEA feature or function, they also provide Tech Tips to keep your skills sharp.

Related Posts
No Image
Feb 20 While data analysis tools have been around for more than 25 years, the rate of adoption is growing exponentially due to the amount of data auditors are required...
We Have IDEA, Now What?
Aug 24 Long after the angst of choosing the right software has passed, you may be uncertain about what to do with it. Sometimes, new IDEA users find themselves in a ru...
Analyzing Culture
Nov 26 The average person spends more than 90,000 hours of their lifetime at work. Where we spend that 1/3 of our lives matters. Company culture is important to employ...

This website has been designed for modern browsers. Please update. Update my browser now