A stable column schema for categorization (QuickBooks/Xero-friendly)

A stable column schema for transaction categorization makes QuickBooks and Xero imports seamless and keeps your chart of accounts consistent.

Recommended column structure

• Date: Transaction date (not posting date) in YYYY-MM-DD format
• Description: Full transaction description from the bank
• Amount: Single column with positive/negative values (debits positive, credits negative)
• Balance: Running balance for validation
• Category: Your chart of accounts category code
• Notes: Reconciliation notes or client follow-up items

QuickBooks/Xero compatibility

• Use date format YYYY-MM-DD (ISO format) for maximum compatibility.
• Keep amounts as numbers (not text) with proper decimal precision.
• Include a reference/check number column if your accounting software tracks it.
• Map categories to your chart of accounts codes before import to avoid manual cleanup.

Best practices

• Create a template Excel file with your standard column structure and reuse it for all clients.
• Use consistent category names/codes across all reconciliations for reporting consistency.
• Add a "Reconciled" checkbox column to track which transactions have been matched.
• Keep the original bank description separate from your cleaned description for audit trails.