Browse Business Software Categories

A
B
C
D
E
F
H
I
M
N
P
R
S
W
Close  

Accounting

Building a Custom General Ledger Using Spreadsheets + APIs

N. Rowan
Share This Post!
Building a Custom General Ledger Using Spreadsheets + APIs

Not every business is ready for a full accounting platform. With modern spreadsheets and a few APIs, you can assemble a lightweight general ledger (GL) that’s auditable, reconcilable, and surprisingly capable. This guide shows how to design a spreadsheet-first GL, connect bank data, automate postings, and keep proper controls until you’re ready to graduate to dedicated software.

What You’re Building

A modular “near-software” ledger using Google Sheets or Excel as the interface, cloud storage for data files, and bank/commerce APIs for transaction feeds. The core is a double-entry journal with supporting tables (chart of accounts, vendors, customers, dimensions) and scripted automation for imports, validations, and reports.

Solution Architecture (At a Glance)

  • Data layer: CSV/Parquet files in cloud storage; spreadsheet tabs as views.
  • Ingestion: Bank APIs (direct or via aggregators), card/export files, PSP payouts.
  • Processing: Power Query (Excel) or Apps Script (Sheets) to normalize, enrich, and post entries.
  • Controls: Locked tabs, change logs, approval checkboxes, and hashed exports for audit evidence.
  • Reporting: Trial balance, P&L, balance sheet, cash flow, and aging schedules.

Core Tabs (Data Model)

  • COA (ChartOfAccounts): AccountID, Name, Type (Asset/Liability/Equity/Revenue/Expense), ParentID, Active?
  • Dimensions: Class, Department, Location (optional).
  • Journal: Date, DocNo, LineNo, AccountID, Debit, Credit, Entity, Class, Memo, SourceID, ImportedBy, Posted?
  • BankRaw: Raw API transactions (Account, Date, Amount, Counterparty, Memo, TxID).
  • Rules: If/then mappings for auto-categorization (e.g., Vendor contains “LYFT” → Travel:Taxi).
  • Reconciliations: Bank balance, book balance, outstanding items, variance.
  • Reports: Pivot-based TB, P&L, BS.

Step 1: Establish Double-Entry Discipline

  • Every transaction posts at least two lines (debits = credits).
  • Use data validation to prevent negative debits/credits and to ensure AccountID exists.
  • Add a Posting Key column (=TEXTJOIN("|",TRUE,Date,DocNo,LineNo,AccountID,Debit,Credit)) to detect duplicates.

Step 2: Connect Bank & Wallet Feeds

Use a read-only API connection or CSV exports on a schedule.

  • Excel: Power Query → From Web/API or From Folder (CSV drop) → Normalize columns → Load to BankRaw.
  • Google Sheets: Apps Script fetch to REST endpoint or scheduled CSV import to BankRaw.
  • Store the provider’s transaction_id for deduplication; maintain asOf timestamps.

Step 3: Auto-Categorize with Rules

Create a rules table and a deterministic mapping function.

// Sheets Apps Script (example) function mapAccount(memo, counterparty, amount){ const rules = getRules(); // [{contains:"LYFT", acct:"6400-Travel:Taxi"}, ...] for (const r of rules){ if ((memo+counterparty).toUpperCase().includes(r.contains)) return r.acct; } return "6999-Uncategorized"; }

Write back suggested AccountID, Class, and Memo to a Staging tab for review before posting.

Step 4: Posting & Reversal Logic

  • Transform each BankRaw row into two Journal lines: Bank ↔ Expense/Revenue, taxes/fees as additional lines.
  • Use a Posted? checkbox and timestamp. Once posted, lock the row via script.
  • Handle refunds/chargebacks with reversing entries (copy source, swap Dr/Cr, prefix memo with “REV-”).

Step 5: Reconciliations

  • Bank Reco: Sum of posted cash ledger by date vs. API statement balance.
  • Outstanding items = Posted cash not yet cleared (or vice versa). Show aged buckets (0–7, 8–30, 31+).
  • Flag variances with conditional formatting and a sign-off cell for each statement date.

Step 6: Period Close

  1. Lock prior-period Journal lines; move period to “Closed”.
  2. Snapshot Trial Balance to a Close_YYYY-MM sheet and store a CSV hash for evidence.
  3. Generate supporting schedules (AR/AP, prepaid, accruals) as separate tabs linked to Journal.

Step 7: Reporting

  • Trial Balance: Pivot Journal by AccountID (sum Debit – Credit).
  • P&L/BS: Map AccountID to financial statement groups in COA; build pivots and data bars.
  • Cash Flow (indirect): Net income + non-cash adjustments + working capital pivots.

User Roles & Access

  • Preparer: Imports and proposes mappings (edit BankRaw, Rules, Staging).
  • Reviewer: Approves postings; can write to Journal but not to Rules.
  • Viewer/Auditor: Read-only access to Reports and Close snapshots.
  • Enforce with Sheet protection, drive-folder permissions, and an audit log tab capturing email + timestamp.

Data Hygiene & Quality Checks

  • COA validation: VLOOKUP/XLOOKUP on every Journal line; highlight missing accounts.
  • Debits = Credits check: =ABS(SUM(Journal!Debit)-SUM(Journal!Credit)) should be zero.
  • Duplicate detection: count distinct Posting Key; throw warning if > 1.
  • Date guardrails: block postings to closed periods with data validation.

Integrations Beyond Banks

  • Payroll: Import JE templates (gross, withholdings, employer taxes) from CSV; auto-split by department.
  • Sales/PSP: Net payout decomposition: Sales, Fees, Chargebacks, Reserves → tie to bank deposits.
  • Invoicing: Simple AR register tab for invoices, payments, and aging with Open = Amount – Payments – Credits.

Forecasting Hooks

  • Short-term cash model: roll forward opening cash + forecasted AR collections + planned AP runs + payroll dates.
  • Scenario toggles: % collection rate, payment terms, and discretionary spend reductions.

Security & Compliance Lite

  • API keys stored in a separate, locked script property or Office credential vault; never in cells.
  • Enable MFA for the drive account; restrict sharing outside your domain.
  • Weekly immutable exports (CSV + checksum) to an archive bucket; keep a change log of rule edits.

When This Approach Works (and When It Doesn’t)

  • Great fit: Early-stage companies, simple entities, cash- or simple accrual-basis, <50k transactions/year.
  • Outgrows: Multi-entity consolidations, complex revenue recognition, high-volume sub-ledgers, or strict compliance requirements.

Cost & Effort

  • Spreadsheet licenses + API aggregator fees (or manual CSVs at $0).
  • Initial setup: 1–3 days to design COA, rules, and reports; ongoing ~1–3 hours/week to review and reconcile.

Starter Checklist

  • ✅ Create COA and Dimensions tabs with data validation lists.
  • ✅ Set up BankRaw import (API or CSV) and a Rules table.
  • ✅ Build Journal posting script and lock Posted rows.
  • ✅ Pivots for TB, P&L, BS; cash flow worksheet.
  • ✅ Reconciliation tab with statement balances and sign-offs.
  • ✅ Close snapshots (monthly) with file hashes for audit trail.

Example Formulas (Copy/Paste)

 =IFERROR(INDEX(Rules!A:A, MATCH(TRUE, REGEXMATCH(UPPER(BankRaw!D2)&UPPER(BankRaw!E2), Rules!B:B), 0)), "6999-Uncategorized") 

=QUERY(Journal!A:Z, "select F, sum(G)-sum(H) group by F label sum(G)-sum(H) 'Balance'", 1)



=TEXTJOIN("|",TRUE,[@Date],[@DocNo],[@LineNo],[@AccountID],[@Debit],[@Credit])

Bottom Line

A spreadsheet + API GL won’t replace a full accounting suite forever, but with double-entry rules, bank feeds, reconciliations, and basic controls, it can deliver credible books at low cost. Build it with migration in mind—clean COA, stable IDs, and monthly snapshots—so when you’re ready to upgrade, your data will move cleanly into a cloud accounting system.

  • Top Expert

N. Rowan

Director, Program Research, Business-Software.com
Program Research, Editor, Expert in ERP, Cloud, Financial Automation

Users Who Read This Article Also Read