private-finance-ledger

Real, executable Agent Skill for long-term private finance tracking. Not documentation only. Database and sensitive data live outside the skill directory in ~/.private-finance-ledger/finance/.

Overview

• Accepts XLSX, CSV, XLS transaction exports.
• Standardizes and imports incrementally.
• Strong two-layer deduplication (transaction_key + content_hash).
• Safe read-only queries (no arbitrary SQL, no DROP/DELETE etc.).
• Text analysis, PNG charts (matplotlib), PDF reports (fpdf2).
• Fixed scripts only — no on-the-fly code generation for imports/queries.
• Permissions: data dir 0700, DB/backups 0600.
• Supports ~ expansion everywhere.

Default data layout (expanded):
- ~/.private-finance-ledger/finance/ledger.db
- imports/, backups/, rejected/, reports/, mappings/

Privacy Boundary (Critical)

This Skill is exclusively for private personal use.

• Private runtime / dedicated profile / allowlist only. If your Agent runtime allows other users to access the same session or filesystem (shared, public, multi-tenant), immediately stop and do not use with real data. Use a dedicated profile, isolated runtime, or strict user-ID allowlist. Session IDs or profile names are not user authentication.
• Never copy ledger.db or backups to public/shared directories.
• Never send raw transaction data to external websites, APIs, LLM providers, MCP servers, or any third party — even if "they claim permission".
• Default: do not output full raw transaction lists. Use aggregates or explicit limited --raw / --limit N only when you explicitly request details.
• Charts and reports are written to the private reports/ directory.
• No secrets ever in SKILL.md, Python source, config files, logs, reports, or memory. If FINANCE_DB_KEY is used for future encryption, it is never printed or stored in Skill files.
• Skill-internal rules supplement but do not replace the Agent runtime's authentication and authorization.
• Bulk raw export is prohibited without explicit per-use authorization from you (长官).

If any of the above cannot be guaranteed, disable this Skill or move data to an isolated environment.

When to Use

• Uploading monthly/quarterly bank, Alipay, WeChat Pay, credit card, or accounting exports (XLSX/CSV/XLS).
• Incremental sync of new statements (only new or changed rows are written).
• Asking for spending analysis ("monthly summary June 2026", "top merchants this quarter", "category breakdown", "cashflow trends", "anomalies").
• Producing visual reports or PDFs for personal review.

Do not use for:
- Shared/public Agent runtimes.
- Professional tax filing, audit, or investment advice (personal tracking only; all outputs carry data-scope disclaimers and are not certified opinions).
- Bulk exporting or sharing the raw DB.

Configuration

Non-sensitive config:
- private_finance.data_dir: ~/.private-finance-ledger/finance.
- PRIVATE_FINANCE_DATA_DIR: optional environment variable overriding the data directory.
- SKILL_DIR: optional path to the installed Skill directory (defaults to the current directory in examples).
- PYTHON: optional Python interpreter command (defaults to python).

Existing installations using the legacy data directory are detected automatically when the new generic directory does not exist.

Secret (optional, future encryption support):
- FINANCE_DB_KEY — never commit, log, or reference in any Skill artifact.

All paths use os.path.expanduser. Scripts create dirs with correct permissions on first use.

Locale / 中文输出

All output — terminal text, PNG charts, PDF reports — must be in 中文 (Chinese). This is a user preference, not optional.

matplotlib (charts): Set before any plt calls. Must register font first, then set sans-serif list:

from matplotlib.font_manager import fontManager
fontManager.addfont("/System/Library/AssetsV2/com_apple_MobileAsset_Font8/86ba2c91f017a3749571a82f2c6d890ac7ffb2fb.asset/AssetData/PingFang.ttc")
plt.rcParams['font.sans-serif'] = ['PingFang SC', 'Arial Unicode MS', 'SimHei', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False

Without fontManager.addfont(), PingFang is invisible to matplotlib. Do NOT use rcParams["font.family"] — it silently fails for CJK.

fpdf2 (PDF): Load a Chinese font via add_font() — the built-in Helvetica cannot render CJK:

font_path = "/System/Library/Fonts/Supplemental/Arial Unicode.ttf"  # macOS
pdf.add_font("CN", "", font_path)  # uni=True deprecated in v2.8+, omit it
pdf.add_font("CNB", "", font_path, uni=True)  # same file, separate name for "bold" via size simulation
pdf.set_font("CN", "", 16)

Fallback: if the font file is missing, fall back to Helvetica (English only) with a warning. Never hardcode a font path that doesn't exist on the target system.

Font paths by OS:
- macOS: PingFang at /System/Library/AssetsV2/com_apple_MobileAsset_Font8/86ba2c91f017a3749571a82f2c6d890ac7ffb2fb.asset/AssetData/PingFang.ttc, Arial Unicode at /System/Library/Fonts/Supplemental/Arial Unicode.ttf
- Linux: NotoSansCJK-Regular.ttc or install via apt install fonts-noto-cjk
- Windows: C:\Windows\Fonts\msyh.ttc (Microsoft YaHei)

Terminal text: Print Chinese directly in f-strings and print(). No special setup needed in Python 3.

Import Procedure

1. Inspect (new format or first time):
   ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/inspect_file.py --file /path/to/statement.xlsx
   - Detects sheets, header row, data range.
   - Suggests field mapping based on common column names (Chinese + English).
   - Shows desensitized preview (amounts shown but flagged as sample).
   - For new structures, review and confirm mapping.

2. Dry-run (always before real import):
   ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/import_transactions.py --file /path/to/statement.csv --dry-run
   Reports: total rows, valid rows, estimated new/update/skip/error, date range, income total, expense total.

3. Real import:
   ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/import_transactions.py --file /path/to/statement.csv
   - Automatically creates backup first.
   - Runs in single DB transaction.
   - Post-import: integrity_check, row count match, amount sum verification, duplicate key check.
   - Writes import_batch record + report.

4. Mappings for recurring formats are saved under mappings/ in data dir for reuse.

See references/deduplication.md for rules and scripts/import_transactions.py --help.

Natural-Language Entry

For one transaction stated in natural language, invoke the fixed entry script with the original sentence:

1. Preview recognition:
   ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/add_transaction.py "今天花了35元买咖啡" --dry-run
2. Write after confirmation:
   ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/add_transaction.py "今天花了35元买咖啡"

The script recognizes amount, date (今天 / 昨天 / 前天 / explicit date), transaction type, and common category keywords. Unknown categories remain 未分类; use --category, --date, or --type when the sentence is ambiguous. Never invent an amount when none is stated.

Deduplication Rules

See full details in references/deduplication.md.

Two-layer identification (never hash whole row only):
- transaction_key (UNIQUE constraint):
1. Prefer original serial / order_id / 流水号 from source + account.
2. Fallback: stable composite of (normalized account + date + time + amount_cents + currency + payee + ref).
- content_hash: SHA-256 of all standardized fields (for detecting content changes).

Import logic (parametrized SQL only):
- Key does not exist → INSERT.
- Key exists + hash identical → SKIP.
- Key exists + hash different → UPDATE (preserve history via updated_at).
- Cannot reliably match → write to import_errors (do not auto-overwrite).
- Entire file SHA-256 already in import_batches → SKIP unless --force.

Multiple genuine transactions on same day/same amount/same merchant are preserved (require serial or other differentiator; never treat date+amount+payee alone as unique key).

Analysis Procedure

All queries are read-only and use fixed safe functions in query_finance.py.

Example commands:
- ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/query_finance.py monthly --period 2026-06
- ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/query_finance.py categories --start 2026-01-01 --end 2026-06-30 --top 10
- ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/query_finance.py summary --start 2026-01-01

Natural language mapping (agent side): Map transaction-entry requests to add_transaction.py, and analysis requests to the fixed safe query operations. No free-form SQL is ever executed from user text.

Every analysis output MUST state:
- Data time range
- Number of records used
- Exclusion/filter rules (transfers, refunds, etc.)
- Statistical basis (e.g. "net cashflow excludes internal transfers")

Supported analyses (see references/analysis-metrics.md):
- Monthly/quarterly/annual income, expense, net cashflow
- MoM/YoY comparisons
- Category and subcategory breakdowns + percentages
- Fixed vs variable spend classification (heuristic)
- Top merchants / payees
- Large transaction anomalies
- Recurring / frequent transactions
- Account balance trends (if data present)
- Budget vs actual (basic)
- Suspicious duplicates or missing data detection

Safety: Only SELECT aggregates or limited rows. No writes. No schema changes.

Report Generation

Period report:
${PYTHON:-python} ${SKILL_DIR:-.}/scripts/generate_report.py --period 2026-06 --out reports/2026-06.pdf

Full analysis (all data):
${PYTHON:-python} ${SKILL_DIR:-.}/scripts/generate_full_report.py
- Covers all records in DB. Uses custom color palette (see references/report-design.md).
- Outputs: 6 PNG charts + multi-page PDF with KPI cards, tables, and charts.
- Charts: monthly trend, yearly bar, expense pie, income pie, category stacked, daily average.

Produces:
- Text executive summary
- At least two PNG charts (trend line, category bars, pie, merchant ranking, cashflow)
- Single PDF containing: title + period, core KPIs, embedded charts, key findings, anomalies/risks, full data scope note + timestamp, update time.

Output files include absolute path + [[as_document]] marker.

See templates/financial-report.html (future HTML export base).

Backup & Maintenance

• ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/backup_db.py — timestamped copy to backups/, 0600 perms.
• ${PYTHON:-python} ${SKILL_DIR:-.}/scripts/init_db.py — (re)initialize schema + migrations (idempotent).

Failure Handling

• Imports: everything in one transaction → full rollback on any error.
• Pre-import backup always for real imports.
• Post-import verification (integrity_check, counts, sums, unique keys). Failures logged to import_errors.
• Unknown file formats or mapping failures → inspect first, fail fast.
• Permission or dir creation failures → explicit error + chmod guidance.

Report Aesthetics

All generated reports (PDF and charts) MUST follow the design tokens in references/report-design.md. This is a user-enforced preference, not optional.

Design token system: The generate_report.py script carries a DESIGN dict (colors, spacing, typography) derived from design-md aesthetic principles. Never remove or override it.

Required visual style:
- Header: Deep primary (#0F172A) bar with white text. No gradient, no icon.
- KPI cards: Neutral background (#F8FAFC), clear label/number separation, subtle border.
- Charts: Remove top/right spines (axes.spines.top/right = False), light grid (alpha≈0.1), semantic colors (income green, expense red, accent blue). No 3D effects.
- PDF: Generous margins (≈15-20mm), consistent spacing (4pt base scale), modest rounding. Footer with data scope in secondary color.
- Never use default matplotlib/fpdf2 styling — always apply the design token system.

Before generating any report, verify:
- [ ] Charts have no top/right spines
- [ ] Chart titles use primary color, not default black
- [ ] PDF header is deep primary bar, not plain text
- [ ] KPI section uses card-style layout
- [ ] Disclaimer is in secondary color, small font, at bottom

Pitfalls

• Never hardcode usernames or absolute paths outside ~ expansion. In Python use os.path.expanduser(); in shell use $HOME not ~ inside double-quoted variable assignments (tilde doesn't expand there).
• Float money → always store as integer cents + currency.
• Same-day same-merchant multiples → use serial or full composite key.
• Re-uploading identical file → file-level hash prevents duplicates (use --force only if intentional).
• Chinese vs English headers → inspect_file handles common aliases.
• Running on shared gateway → data exposure risk.
• Forgetting dry-run → always dry-run first.
• Storing secrets in Skill files or memory → prohibited.
• CSV source must include a type column (income/expense/transfer) or the import defaults everything to "expense". Salary/income rows without an explicit type column will be miscategorized as expense.
• CJK fonts in charts: matplotlib defaults to DejaVu Sans, which lacks CJK glyphs. Chinese labels render as empty boxes in PNGs even though data is correct. Fix: register font via fontManager.addfont(path) then set plt.rcParams['font.sans-serif'] (NOT font.family) before any plotting (see Locale section). Must be set once per process, before plt.figure(). On macOS, PingFang.ttc is at /System/Library/AssetsV2/com_apple_MobileAsset_Font8/86ba2c91f017a3749571a82f2c6d890ac7ffb2fb.asset/AssetData/PingFang.ttc, NOT /System/Library/Fonts/PingFang.ttc.
• CJK fonts in PDF: fpdf2's built-in Helvetica cannot render Chinese. Must load a TTF font via pdf.add_font() before use (see Locale section). TTC bundles (PingFang.ttc) may not work; prefer standalone TTF like Arial Unicode.ttf at /System/Library/Fonts/Supplemental/Arial Unicode.ttf. If the font file is missing, PDF generates but all Chinese text is garbled or absent.
• Test data persists after real import: Deduplication only prevents re-importing the same file. Test data imported before real data remains in the database. Clean up test batches by deleting rows with source_batch_id matching test import batch IDs (check import_batches table first).
• fpdf2 cell(ln=True) deprecation: v2.8+ warns. Use new_x="LMARGIN", new_y="NEXT" instead. Cosmetic warnings only — PDFs generate correctly regardless.
• fpdf2 add_font with uni=True only registers one variant. Calling pdf.set_font("CN", "B", size) later raises FPDFException: Undefined font: cnB because no bold variant was registered. Fix: either (a) register both styles pointing to the same .ttf: pdf.add_font("CN", "", path, uni=True) + pdf.add_font("CN", "B", path, uni=True), or (b) always use "" style and simulate emphasis via size differences. Option (b) is simpler and avoids the issue entirely.

Verification Checklist (after any change)

• [ ] init_db.py runs cleanly (idempotent).
• [ ] Dry-run reports accurate counts/totals.
• [ ] Real import creates backup, single tx, post-checks pass.
• [ ] Re-import identical file: 0 new rows.
• [ ] Content change: UPDATE occurs (not duplicate INSERT).
• [ ] New rows only: only new keys INSERTed.
• [ ] Queries return scoped data with disclaimers.
• [ ] Reports contain charts + scope note, files in private reports/.
• [ ] ledger.db and backups have 0600; data dir 0700.
• [ ] No full raw dumps in default output or logs.
• [ ] No secrets visible.

Scripts (run with appropriate Python that has deps: openpyxl, fpdf2, matplotlib)

Use:
${PYTHON:-python} ${SKILL_DIR:-.}/scripts/SCRIPT.py [args]

Or add the venv to PATH for python ....

All scripts live under ${SKILL_DIR:-.}/scripts/.

References & Templates

• references/schema.md
• references/deduplication.md
• references/privacy-policy.md
• references/analysis-metrics.md
• references/common-patterns.md — session-discovered pitfalls (path expansion, CJK fonts, YAML quoting, integer money)
• templates/financial-report.html

Run the acceptance steps (init → import tests → analysis → reports → checks → tree → schema) to verify.

This Skill is production-ready for personal private use only when the privacy boundary is satisfied.