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

QUICKBOOKS STEPS:

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.

IDEA STEPS:

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.