Finance-Accounts/ar-aging-app

Amazon Accounts Receivable Aging Dashboard

Automates the Finance team's month-end Amazon Accounts Receivable Aging workbook: upload the month's Amazon Custom Unified Transaction files → get a structurally- and financially-identical Accounts Receivable Aging workbook, with validation, settlement reconciliation, exceptions, drill-down, and an audit trail.

Amazon only (no Walmart/TikTok/Temu/Shein/eBay/Shopify). Reverse-engineered from the Jan-2026 files; the USA receivable reproduces to the penny (11,110,433 = Detail!D11). See docs/accounts-receivable-logic.md.

Layout

backend/
  app/core/        # streaming parser + receivable engine (stdlib + openpyxl only)
  app/api/         # FastAPI app (uploads, jobs, endpoints) — Phase 3
  tests/           # unit + Jan-2026 reconciliation integration test
  cli.py           # process files from the command line
frontend/          # React + TS + Vite dashboard — Phase 4
docs/

Run the app

# 1. Configure MySQL (hosted RDS) + data paths
cp example.env .env   # then fill in MYSQL_* credentials

# Option A — Docker (backend + Vite hot reload)
docker compose up --build
# → http://localhost:5173  (API on :8000; uploads/exports on the ar_data volume)

# Option B — local processes
make install          # backend deps + npm install
make backend          # terminal 1 → FastAPI on :8000
make frontend         # terminal 2 → dashboard on http://localhost:5173

Then open http://localhost:5173 → New Closing → pick the month → drag in the three Amazon files → Run processing → review → Download Full A/R Aging Excel.

Uploads and exports are stored under AR_DATA_DIR (default backend/data locally, /data in Docker). The app connects to MySQL using MYSQL_* variables from .env.

Run the engine headless (CLI)

cd backend && pip install -r requirements.txt
python cli.py "/path/USA…01 to 10 January,2026.xlsx" \
              "/path/USA…11 to 20 January,2026.xlsx" \
              "/path/USA…21 to 31 January,2026.xlsx" \
              --month-end 2026-01-31 --lag 2 --reserve-standard 125.44 --out AR_Aging_Jan26.xlsx

Tests

make test        # 16 fast tests (engine, export, API, robustness)
make test-all    # + Jan-2026 reconciliation & sample-comparison (integration, ~4 min)
# point tests at the sample files if not in the repo root:
AR_SAMPLE_DIR="/path/to/samples" make test-all

Status — all phases complete (incl. v2 multi-market)

• ✅ Engine — settlement classification + receivable; USA = 11,110,433 verified to the penny
• ✅ Multi-marketplace — all 13 markets (CA/UK/AU/IE + localized FR/DE/IT/ES/NL/PL/SV/TR) reconcile to the penny vs the Jan-26 workbook; per-market currency & FX; settlement-owner logic for cross-market EU chains; helper-row & pivot-sheet detection
• ✅ AR roll-forward — opening balance (auto carry-forward) + net revenue − payouts = closing; AR Ledger · Finance Summary · Journal Entry (per-marketplace) · Reconciliation Control w/ sign-off
• ✅ Header mapping — localized alias tables + admin rules UI (/api/mapping-rules); unmapped amounts are never silently excluded
• ✅ Storage-fee detection — canonical + description-based (potential/missing storage exceptions)
• ✅ Excel — Full audit workbook + Summary Finance pack
• ✅ Tests — ~60 fast + integration (USA reconciliation, sample comparison, 13-market benchmark)