QuickBooks Export File: Explanation

December 2, 2021


This is a short description of how back office assembles the QuickBooks export file (IIF file).

Specification

The generated file is a collection of General Ledger entries. Each entry contains:

  • A sum of money;
  • Credit account OR debit account;
  • Description

Erply allows to download the dataset as an IIF file (which is basically a CSV), and to view it on screen in tabular layout (to verify the data and to diagnose errors).

In the file, debits are indicated by a positive sum, and creditings are indicated with a negative sum.

In accordance with double-entry bookkeeping, the sum of all accumulated debit entries and the sum of all accumulated credit entries must be equal. In other words, each sum of money must appear in the ledger twice; once on the Debit side, and once on the Credit side.

The General Ledger entries are generated by date and location.

Configuration Settings

We recommend to apply configuration parameter quickbooks_include_same_day_invoices_and_payments = 1.

If this parameter is not applied, Erply additionally looks which invoices were created and paid within the same day, and excludes those from the Accounts Receivable debit and credit lines. This, however, makes the numbers difficult to verify.

File Contents

For each date and location, the entries which may appear are as follows:

Account Description

 

Type

 

How Erply calculates the data

 

Undeposited FundsD

Total sum of payments for the given date, grouped by payment type. 

(Cards are additionally grouped by card type.)

Accounts Receivable - Trade (Payments applied to Invoice-Waybills)CTotal sum of payments for the given date.
Cash Over and ShortD/C

If cashier has counted a $10 overage at the end of the day, the QuickBooks export file shows day earnings (Undeposited Funds) as $10 higher, and adds a corresponding “Cash Over and Short” credit entry of $10.

With a shortage, the approach is the other way around: day earnings are shown smaller, and a corresponding “Cash Over and Short” debit entry is added.

D Undeposited Funds = C Accounts Receivable

Accounts Receivable - Trade (Invoice-Waybills Total)DTotal sum of all confirmed, revenue-generating sales documents (net total + tax)
Sales and Use Tax PayableCTotal tax, calculated over all confirmed, revenue-generating sales documents.
SalesC

Net total, calculated over all confirmed, revenue-generating sales documents.

Erply lists these net sales totals separately by main product group (the top-level group where the product belongs). Do not know whether this is needed or not.

D Accounts Receivable = C Sales Tax Payable + C Sales

Cost of Goods SoldD

Total COGS (Cost of Goods Sold), calculated over all confirmed sales documents that affect inventory. The same value appears on both lines.

Erply lists totals separately by main product group (the top-level group where the product belongs). Do not know whether this is needed or not.

InventoryC

C Inventory = D Cost of Goods Sold

See Also

https://learn-api.erply.com/concepts/sales-and-revenue

Sample

!TRNS,TRNSID,TRNSTYPE,DATE,ACCNT,AMOUNT,DOCNUM,MEMO
!SPL,SPLID,TRNSTYPE,DATE,ACCNT,MEMO,AMOUNT
!ENDTRNS
TRNS,0003030002,GENERAL JOURNAL,11/13/19,3900,0.00,0003030002,Day Transactions
SPL,0003030002,GENERAL JOURNAL,11/13/19,,Invoice-Waybills Total,106.67
SPL,0003030002,GENERAL JOURNAL,11/13/19,,Tax Collected - Sales Tax,-11.80
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Gloves and Mitts,-23.50
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Gloves and Mitts,23.50
SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - Gloves and Mitts,-49.99
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - New product group,0.00
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - New product group,0.00
SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - New product group,-35.88
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Cafe,0.00
SPL,0003030002,GENERAL JOURNAL,11/13/19,,COGS - Cafe,0.00
SPL,0003030002,GENERAL JOURNAL,11/13/19,,Net sales - Cafe,-9.00
ENDTRNS