QuickBooks Import — Consolidated Report

The Problem (16 March)

Maddy found the original OFX bank feed didn't reconcile against the Transfer Detail. The feed showed $3,198.51 gross for Mar 12, but the actual bank transfer was $1,738.51 net. The original feed had gross individual customer payments without refunds, chargebacks, or Square processing fees — so the numbers didn't match what the bank received.

Two Sandboxes — Same Data, Different Layout

Both contain identical data: 133 Chicago recurring invoices + 133 payments ($25,297) from Mar 1-12 2026. Both OFX feeds include refunds ($5,148), chargebacks ($100), and processing fees ($2,198). The only difference is how the transactions are organised.

Sandbox	Realm ID	Option	Status
US ab91	9341456620900494	Option 1: 3 Split Feeds Recurring / Sales / Deposits — separate account per type	VERIFIED & UPLOADED
US dcf7	9341456620880515	Option 2: 2 Combined Feeds All payments in one account + bank deposit payouts separate	VERIFIED & UPLOADED

Option 1: 3 Split Feeds (Sandbox A — US ab91)

3 separate bank accounts — one per payment type. Each includes its proportional share of refunds, chargebacks, and fees.

File	Transactions	Net Amount	QB Account	Download
QB_Recurring	137 txns	$24,659.81	Square Recurring	↓ OFX
QB_Sales	83 txns	$48,805.35	Square Sales	↓ OFX
QB_Deposits	220 txns	$8,411.15	Square Deposits	↓ OFX
Combined		$81,876.31

Option 2: 2 Combined Feeds (Sandbox B — US dcf7)

1 bank account with all payment types combined + 1 deposit feed matching actual bank transfers.

File	Transactions	Net Amount	QB Account	Download
QB_SquareFeed	437 txns	$81,876.30	Square	↓ OFX
QB_SquareDeposit	11 payouts	$8,652.30	Square Deposit	↓ OFX

Reference Files

File	Purpose	Download
QB_BalanceTransfer CSV	Full detail for review — not uploaded to QB	↓ CSV
QB_Refunds CSV	Refunds detail for review	↓ CSV

    Next step: Elena & Maddy open each sandbox → Banking tab → For Review → try matching transactions to invoices. Whichever reconciles better = the approach we use for production.
  

What Was Verified (18 March)

Check	Sandbox A (ab91)	Sandbox B (dcf7)
Invoices	133 — correct format, no dupes	133 — correct format, no dupes
Payments linked to invoices	128 → Square Recurring	133 → Square
Bank accounts (created in QB UI)	Square Recurring, Sales, Deposits	Square, Square Deposit
OFX files uploaded	3 files uploaded	2 files uploaded
Photo Package item	ID: 24	ID: 24
Deposits income account	YES	YES
Refunds & Chargebacks expense	YES	YES

7 IES template payments exist in each sandbox (Amy's Bird Sanctuary, etc.) — these came pre-loaded and cannot be deleted. They are harmless — ignore them.

Overview

The QuickBooks automation handles two sides of the ledger automatically, every day at 7 AM with zero manual downloads or data entry:

Invoices (A/R side): CRM Daily Accounting Sales → QB Invoices via qb_daily_pipeline.js
Payments (bank side): Square Payouts API → OFX Bank Feeds via qb_balance_transfer.js

Architecture: Two Sides of QB

Side	What It Contains	How It Gets There	Status
A/R (Sales > Invoices)	CRM invoices + Square payments linked to them	Automated via QB API — fully automatic	DONE
Banking Tab	Bank feed transactions matching actual bank deposits	OFX file upload — Natalia uploads daily (~30 sec)	READY TO TEST

QB Online has no API for uploading bank feed transactions. The OFX file generation is fully automated (Payouts API → OFX), but the upload step requires a human click in the Banking tab. This is a QB platform limitation.

How It Works — Fully Automated Pipeline

Step A: CRM → QB Invoices (Automated)

Script: qb_daily_pipeline.js runs daily at 7 AM.
Pulls Daily Accounting Sales from CRM, creates invoices in QB with confirmed format (Photo Package item, Customer Name, Finance Start Date as due date). Builds the A/R side — what customers owe.

Step B: Square Payouts API → Bank Feed Files (Automated)

Script: qb_balance_transfer.js runs daily at 7 AM.
Pulls from three Square API endpoints to get the complete picture:

API Endpoint	What It Captures	Previously
`/v2/payments`	All completed payments with customer IDs, amounts, card details	Already automated
`/v2/payouts`	Bank deposits — actual amounts deposited to the bank each day	Was: manual CSV download
`/v2/payouts/{id}/payout-entries`	Complete breakdown per deposit: payments (CHARGE), refunds (REFUND), chargebacks (OPEN_DISPUTE), fees, adjustments	Was: manual Transfer Detail CSV

Step C: Natalia Matches in QB (Manual)

Upload OFX files to QB Banking tab, match payments to invoices. This is the only manual step — requires accounting judgment.

Payouts API — Verified Against Real Data

Tested against the same dates Elena provided Transfer Detail CSVs for. The API captures the same data automatically:

Mar 10 Payout (covers Mar 9 transactions)

Entry Type	Count	Amount	Previously
CHARGE (payments)	28	+$1,287.66	Already automated
REFUND	7	-$350.00	NEW — was manual CSV only

Matches Elena's Mar 9 CSV: 7 deposit refunds at $50 each = -$350. ✓

Mar 12 Payout (covers Mar 11 transactions)

Entry Type	Count	Amount	Previously
CHARGE (payments)	17	+$748.71	Already automated
OPEN_DISPUTE (chargeback)	1	-$50.00	NEW — was manual CSV only

Matches Elena's Mar 11 CSV: 1 open dispute for $50. ✓

    Confirmed: The Payouts API captures refunds, chargebacks, and dispute adjustments automatically. No manual downloads from the Square Dashboard needed.
  

What Natalia Does Each Day

All files are generated by 7 AM. Natalia's daily process:

Open QB Online. Banking tab.

Upload OFX files: Assign each file to its bank account. Map: Date, Description, Amount. Format: MM/DD/YYYY.

Banking tab → For Review. Match each payment to its invoice:

QB suggests match → verify → click Match
No match suggested → search by CRM Reference ID
No invoice exists → categorize directly or create invoice first

Fees are in the feed as a lump sum deduction. Natalia categorizes to an expense account.

Time: Upload ~2 min. Matching ~15-20 min/day.

What Stays Manual vs What's Automated

Task	Who	Status
CRM Daily Sales → QB Invoices	Automated (7 AM)	BUILT
Square Payments → OFX Bank Feeds	Automated (7 AM)	BUILT
Refunds & Chargebacks in OFX	Automated (Payouts API)	VERIFIED
Processing Fees in OFX	Automated (payout entries)	BUILT
Transfer Detail CSV download	ELIMINATED — Payouts API replaces manual download
Upload OFX files to QB	Natalia	~2 min/day
Match payments to invoices	Natalia	~15-20 min/day
Cash on Hand deposits	Natalia	Unless viewers create Square receipts

Non-Matched Transactions

Scenario	Why	Handling
Payment, no invoice	Walk-in/cash sale	Natalia creates invoice or categorizes directly
Invoice, no payment	Customer hasn't paid	Invoice stays open in A/R
Amount mismatch	Partial installment payment	QB handles partial payments natively
Name mismatch	Card name vs CRM name	CRM Reference ID is the tiebreaker
Refunds	Customer refunded	Negative entry in bank feed. Refund Receipt linked to original invoice.
Chargebacks	Customer dispute	Negative entry in bank feed. Categorize to Refunds & Chargebacks expense.

Elena & Maddy's Confirmed Decisions

Original 12 Decisions (12 March 2026)

#	Decision	Status
1	Square data pull — fully automated via API	✓
2	Deposits — separate bank feed, Income account	✓
3	Item name — "Photo Package"	✓
4	Invoices — only original package amount	✓
5	Recurring — 1 invoice per sale (not per payment)	✓
6	QB categories — Deposits + Cash on Hand	✓
7	Consolidated report — Balance Transfer with refunds/CB/fees	✓
8	Manual section — confirmed	✓
9	Final test — QB sandbox only (no IES trial)	✓
10	Invoice format — Customer Name, mm/dd/yyyy, Ref + payments + Booker, Finance Start Date as due date	✓
11	Pending deposits — excluded (only cleared)	✓
12	Non-matched — confirmed handling	✓

Invoice Format (Confirmed)

QB Invoice Field	Source	Example
Customer Name	CRM (Customer Name, NOT Model Name)	John Smith
Invoice Date	Sale Date (mm/dd/yyyy)	03/09/2026
Line Item	Reference + payments + booker	C2807460, 12 monthly payments of $291.67, Booker: Michael
Amount	Package Price	$3,500.00
Reference / PO	CRM Reference ID	C2807460
Memo	Studio + Booker (NO City)	Chicago Studio, Booker: Michael
Due Date	Finance Start Date (NOT Sale Date)	03/15/2026

Follow-Up Decisions (13-14 March 2026)

Question	Answer
Q1: Booking deposits	Deposits income account (no invoice)
Q2: Studio sales without CRM match	Natalia handles manually
Q3: Historical pull	Skip — focus on refunds/fees/chargebacks first
Q3a: Duplicate customers	Merge under one QB customer, separate invoices per sale
Q4: Historical invoices	Open Ledger from Debt Ledger (Dec 31, 2025 balances)
Q5: Non-recurring	Deposits → income, walk-ins/office → Photo Package (Natalia manual)
Q6: Cancelled/defaulted	PENDING — Elena checking with Oscar & Mike
Q7: Multiple sales per customer	PIF = nothing, new sale = new invoice
Q8: Refunds	Refund Receipt linked to original invoice
Q9: Chargebacks	Custom "Refunds & Chargebacks" expense category
Q10: Processing fees	In bank feed — Natalia categorizes manually
Q11: Open ledger	Pull from Debt Ledger (Dec 31 2025)
Q12: Cities	Chicago first

What's Next

#	Step	Who	Status
1	Compare both sandboxes — Banking tab → For Review → try matching	Elena / Maddy	NOW
2	Pick winning option (3 split or 2 combined)	Elena / Maddy	After testing
3	Confirm Q6 (cancelled accounts — bad debt vs sales)	Elena + Oscar/Mike	Before go-live
4	Open Ledger — Dec 31 2025 balances from Debt Ledger	Automated	After option chosen
5	Natalia tests full daily workflow in sandbox	Natalia	After open ledger
6	GO LIVE	Everyone	April 1, 2026

Full History

Week 1 (9-14 Mar)

Square API connected — all 6 cities, 32 locations
CRM → Invoice pipeline built and tested
Square Payouts API integrated — replaces manual Transfer Detail CSV downloads
Elena & Maddy confirmed all 12 original decisions + Q1-Q12
Multiple sandbox tests with real Chicago data (CRM + Square)
133 invoices + 133 payments pushed with 0 errors, 2 OFX feeds generated

Week 2 (16-18 Mar)

16 Mar: Maddy found OFX gross amounts don't match bank net transfers (missing refunds/CB/fees)
17 Mar: Rebuilt OFX feeds with refunds, chargebacks, and fees included. Created Option 1 (3 split) and Option 2 (2 combined)
18 Mar: 2 new sandboxes (US ab91 + US dcf7) set up, data pushed, verified, OFX files uploaded. Ready for Elena & Maddy to compare

Updated: 18 March 2026 | All decisions confirmed (Q1-Q12, Q6 pending Oscar/Mike) | Chicago first
BOTH SANDBOXES READY: US ab91 (3 split feeds) + US dcf7 (2 combined feeds)
GO-LIVE TARGET: April 1, 2026