Rebuilding a company's profit and loss and balance sheet, month by month, from the line-item transactions in QuickBooks Online's API. It ties to the cent. About a third of it is not in the transactions.

While building BooksNav's sync I wanted an answer to one question: can the line-item transactions an app pulls from QuickBooks Online reproduce the profit and loss and balance sheet QuickBooks itself prints, for every month? If they cannot, every report built on that data is an approximation, and an accountant should know by how much and where.

The short version: yes, to $0.00. On Intuit's sample company, and on live books including a distributor with more than twenty years of history and inventory, the profit and loss movements and the balance sheet balances match QuickBooks' own statements at every month-end. But roughly a third of the postings needed to get there are not in any transaction the API returns. Here is what I found, in the order I found it.

The method

QuickBooks Online's API returns documents: invoices, bills, payments, journal entries. It does not return the ledger. An invoice payload carries the revenue lines; the debit to Accounts Receivable is implied, never stated. A bill carries the expense lines; the credit to Accounts Payable is implied. So the first job is to construct the double entry the way QuickBooks would, for every transaction type.

The second job is to check it, twice. First against the GeneralLedger report for the same company, compared to the constructed postings at the grain of transaction type and account. Not account totals. Anywhere the two disagreed, the difference was written out as a separate line, labelled with the transaction type and account that produced it. Nothing was plugged at the account level, because an account-level plug ties by construction and proves nothing. Second, the postings were summed into movements per account per month and compared with the profit and loss and balance sheet QuickBooks prints for each month-end.

On Intuit's sample company that produced 48 of 48 accounts tied, debits equal to credits, $0.00 variance, with eleven difference lines printed. Those eleven lines are the interesting part. On the live books it produced the same $0.00 at every month-end, more than two hundred and fifty of them on the longest.

Five things the transaction API structurally cannot tell you

1. Which account sales tax posts to. The invoice carries the tax amount. It carries no account for it. The TaxAgency entity carries no account either.
2. Sales tax payments. The TaxPayment entity returns an error for US companies. The payment exists only in the report.
3. The value of opening inventory. InventoryAdjustment is queryable and returns the quantity. The amount is null. There is no field to ask for.
4. Cost of goods sold. Not in the invoice, not in the sales receipt, not in any entity. QuickBooks computes FIFO cost internally and the only place it appears is the report.
5. Statement charge revenue. The entity does not exist in the API. The invoice it creates arrives with a total and no lines.

Every one of these is closed by reading the GeneralLedger report and joining on the transaction id, which the report rows carry. None of them can be closed by syncing one more entity.

Sales tax is per agency, and an account-level tie-out cannot see it

The sample company's sales tax of 447.84 splits 38.40 to one agency's payable account and 409.44 to another. Any rule of the form "post tax to the one liability account with a balance" would have been wrong. Worse, the first agency's account had been charged 38.40 and paid 38.40, so it nets to 0.00 on the trial balance. A tie-out at account level would have passed with the tax in the wrong account. Only the transaction-type-by-account grain shows it.

One sales line, three cost postings

I posted a test: buy four units of an item at $150 on top of two already held at $125, then sell three on a single invoice line. QuickBooks recorded three cost postings for that one line: 125.00, 125.00 and 150.00, total 400.00. The obvious formula, quantity times the item's purchase cost, gives 375.00. The item's purchase cost field never moved to 150.

There is no single cost for that sales line. The cost record is finer than the sales line, so no per-line formula can reproduce it, however careful. It has to be read.

The backdated bill that changed two transactions nobody touched

Then I posted one more bill, dated a month earlier, for five units at $80. Nothing else. The invoice above went from 400.00 of cost to 330.00. An inventory adjustment went from 150.00 to 80.00. Two transactions changed value because an earlier purchase re-cut the FIFO layers a later sale consumed.

Neither transaction's LastUpdatedTime moved. The change-data-capture endpoint, which is how every sync tool finds what changed, reported the new bill and the two items whose layers moved. It did not report the invoice or the adjustment. From the API's point of view, the documents did not change. Only their cost did.

For a company with inventory this means an incremental sync can never keep cost of goods sold correct on its own. An inventory item appearing in the change feed is the only signal that the cost on some earlier, unknown set of transactions has shifted.

Two things the reports do that the documentation does not mention

The TrialBalance report closes every prior fiscal year's profit and loss into Retained Earnings regardless of the start date you pass. Ask for one year and you get that year's activity with all prior years already rolled up. Anything you build from the transactions has to apply the same close, from the company's fiscal year start month, or every income and expense account misses by exactly its prior-year total.

And the reports truncate. On a book with more than twenty years of history, a single whole-history GeneralLedger call came back tens of thousands of rows short, with no flag in the response and the "no data" indicator set to false. The same period fetched in date ranges was complete. Above roughly 12 MB the report is silently cut. Any single-call fetch of a report on a real book returns a wrong number with no error.

What this means if you are moving data out of QuickBooks Online

• The transaction export is not the ledger. About a third of the postings on a real book are constructed, not received, and for inventory, sales tax and statement charges the amounts are not in the export at all.
• Monthly comparatives can be produced from the extract and proven against QuickBooks' own statements, month by month, rather than typed from printed reports.
• Anything QuickBooks computes rather than stores must be read from its reports and carried as a reported figure. Recomputing it produces a plausible number that is wrong.
• A trial balance that ties at account level proves less than it looks. Tie at transaction type and account.
• For an inventory company a tie-out is a dated snapshot. A backdated purchase re-cuts cost on transactions that will never show as changed. Reconcile again at cutover.
• Chunk report calls by date and check the response size. The API will not tell you it truncated.

All of this is reproducible on Intuit's sandbox company with the transactions described above. I am happy to compare notes with anyone doing the same work.