Hy An is a small retail business — under 10 people — selling across Facebook, TikTok Shop, and Shopee, managed through Pancake POS. Like most companies this size, they don’t have a dedicated accountant or finance team. The owner tracks everything herself, alongside running the actual business.
For a while, an Excel spreadsheet worked fine. It had three side-by-side panels per month — one for each bank account — and every inflow was summed as revenue. That’s a reasonable setup when you’re small and moving fast. But as the business grew across more channels, internal transfers between accounts started getting counted as income too. The spreadsheet couldn’t tell the difference between a customer payment and money being moved from one company account to another.
By Q1 2026, this was inflating their revenue figures significantly. Over 90% of the error came from internal transfers, with the rest from capital movements. The P&L looked healthy on paper, but the real numbers told a different story.
Three layers, one system
I built a 3-layer system: migration scripts to clean the old data, Google Sheets as the operational hub, and n8n for daily automation.
Data migration — classifying hundreds of transactions
The old Excel file had three columns per month, per account. There was no classification system — which makes sense when one person is handling everything. But it meant there was no way to tell if money coming in was a real sale or just an internal transfer between accounts.
I wrote a Python script that converted the old XLSX into a flat, classified format. Every transaction gets a tag:
| Tag | What it means | Counts in P&L? |
|---|---|---|
| Khach hang | Real external transaction | Yes |
| Noi bo | Transfer between company accounts | No |
| Von chu | Owner capital injection/withdrawal | No |
| Hoan tra | Refund/return | Yes (negative) |
The classifier uses regex pattern matching on Vietnamese transaction descriptions to detect internal transfers, owner capital movements, and ad spend pass-throughs.
Result: 98% of transactions were auto-classified. Only a handful flagged for manual review.
Google Sheets — the operational hub
The sheet was created programmatically via Google Sheets API with five tabs. All reports are formula-driven — zero manual calculations.
Tab 1: Transaction ledger
A flat table with every transaction: date, account, description, in/out amounts, classification, revenue category, expense category, notes, and receipt links. Dropdown validation enforces consistent data entry. Rows with missing classification are highlighted yellow.
All historical transactions were pre-imported from the migration.
Tab 2: Opening balances
Monthly opening balance per account, extracted from the old XLSX. Referenced by the report tab for balance calculations.
Tab 3: Monthly P&L report
This is the core of the system. Select a month, and the entire report recalculates:
- Account balances — per bank account, reconciled against opening balances
- Real revenue — broken down by retail and wholesale, filtered on classification = “Khach hang”
- Expenses by group — COGS, operations, marketing, logistics
- Profit/Loss — the actual bottom line
- Internal transactions — shown for reference only, structurally excluded from the P&L
- Pancake reconciliation — compares Pancake revenue (from order data) vs bank revenue (from manual entries)
Key design: All values are SUMPRODUCT formulas filtering on the classification column. Internal transfers exist in the data but are structurally invisible in reports. No one has to remember to exclude them.
Tab 4: Yearly overview
A 12-month matrix showing revenue, COGS, gross profit, expense groups, and net profit side by side. Plus a Pancake revenue row for cross-referencing.
Tab 5: Pancake orders
Raw order data from Pancake POS — order ID, date, channel, status, customer, product, quantity, price, COD amount, province. One row per order line item. Populated daily by n8n.
n8n workflow — daily automation
A 4-node workflow running on a self-hosted n8n instance:
Schedule trigger fires daily at 6AM Vietnam time. The workflow clears existing Pancake data to prevent duplicates, then fetches all orders from the Pancake API.
The fetch step paginates through the API, maps order statuses (new, confirmed, packed, shipped, completed, cancelled), detects sales channels from page ID prefixes (tts_ = TikTok, shopee_ = Shopee, otherwise Facebook), and flattens everything to one row per order item.
All rows are written to Google Sheets via a service account.
Resilience: Auto-retry 3 times with 5-minute intervals. Email alert to me on failure.
Why this architecture
Google Sheets as the hub, not a database. The client’s team already lives in Sheets. No new tool to learn. Formulas handle all reporting logic.
Classification-based P&L. Instead of trying to fix the old 3-panel layout, I flattened everything and added a classification column. The P&L formulas simply filter on “Khach hang”. Internal transfers exist in the data but are structurally invisible in reports.
Full-replace sync, not incremental. The n8n workflow clears and re-imports all Pancake orders daily. Simpler than tracking deltas, and the data volume (hundreds of orders) is small enough that it’s fast.
Pancake reconciliation built-in. The P&L tab compares Pancake revenue (from order data) vs bank revenue (from manual entries). The delta = cash in transit — money from completed orders not yet deposited.
What it runs on
| Component | Tool | Cost |
|---|---|---|
| Data migration | Python + openpyxl | One-time |
| Sheet creation | Python + Google Sheets API | Free |
| Operational hub | Google Sheets | Free |
| Daily sync | n8n (self-hosted) | ~$5/mo VPS |
| Order data | Pancake POS API | Included in plan |
| Authentication | Google Service Account | Free |
Impact
- Phantom revenue eliminated — internal transfers and owner capital movements no longer inflate the P&L
- Daily Pancake order data auto-synced — no more manual copy-paste
- Revenue reconciliation (Pancake vs bank) gives visibility into cash flow timing
- Monthly P&L report updates with a single cell change
- Channel breakdown (TikTok / Shopee / Facebook) available automatically
The owner still runs the finances herself — she just doesn’t have to fight the spreadsheet anymore. The system does the heavy lifting, and she can focus on growing the business.