Importing QuickBooks Data into IDEA

QuickBooks is an essential part of everyday business for small- and medium-sized businesses. With limited full-time employees (FTEs), record keepers and signers are often the same person, which can create separation of duties issues. Its common practice to audit by transaction number. To do this, you will need to normalize the data in IDEA. With a few simple steps, you can easily acquire and transform your QuickBooks Enterprise data into a usable format for analysis.
 

QuickBooks Steps:

 
Starting with a standard Journal Entry Report as an example.

1. Go to “Customize” and add “Account” and “Trans #” as additional columns if one or both are not part of the detail line. Drag these columns to the far left. [Note: Splits may still be still an issue.]
    
   
    
2. Export the report to Excel or CSV depending on size of file. CSV preferred.
    
   
    

 IDEA Steps:

1. QuickBooks reports in Excel, are not a “clean” Excel import. In appearance, they are more like a print report – headers may need to be removed prior to import and columns removed after import. This is why CSV is preferred.
    
   
    
2. When importing TEXT – CSV, don’t forget to define a MASK for the DATE fields & bring in amounts as type NUMERIC. You can also exclude blank lines by filtering – example:
    
   
    
3. If any columns are blank, requiring fill down, see: Tech Tip: Filling Empty Cells

More Resources

 
The Financial App is 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.