monarch-excel-autopilot

Hands-free pipeline from Monarch Money to a local Excel cash-flow workbook — built for a money file, so it is paranoid by design. Runs happily on a NAS in Docker; degrades gracefully to a one-command manual import on any machine with Python.

 bank → Monarch ──(nightly pull, cookie session)──► inbox/*.csv
                                                        │
                                   watcher (30s poll) ──┤  dedup → envelope tags
                                                        ▼
                            backup → temp copy → verify → atomic swap
                                                        ▼
                                            Cash_Flow_Planner.xlsx
                              (your Forecast/Envelopes formulas read the
                               Tx* named ranges and never notice the robot)

Three cooperating pieces, each usable alone:

Piece	What it does
monarch_import.py	CLI: Monarch CSV export → workbook ledger. Header-name column mapping, category→envelope tagging, multiplicity-aware dedup.
nas_watcher.py	Daemon (or --once cron job): watches an inbox folder, imports every CSV dropped there through the safe-apply path.
monarch_pull.py	Daily puller: fetches settled transactions straight from Monarch and writes export-format CSVs into the inbox. No more manual exports.

Safety rails (why this can run unattended on a money file)

• Atomic writes. The workbook is never edited in place: import into a hidden temp copy, verify it re-opens, then os.replace() over the original. A crash mid-import cannot leave a half-written file.
• Backup before every apply, timestamped and rotated.
• Overflow is fatal. If a CSV won't fit in the ledger, the whole file is refused loudly — never a silent partial import.
• Multiplicity-aware dedup. Re-drops and overlapping pulls add nothing, but two identical same-day charges (two $2 parking taps) both survive. The ledger ends with the same count of each (date, description, amount) key as the source — not collapsed to a set.
• Exact-match envelope tagging. Category rules match exactly (case- insensitive), never by substring — the classic failure this prevents is "Natural Gas" (heating) draining the "Gas" (fuel) envelope.
• Pending transactions are never imported. They mutate when they settle and would slip past dedup as near-duplicates; the puller waits.
• Open-workbook guard. Excel's ~$ lock defers imports instead of fighting a live editor (stale locks from crashes are aged out).
• Single-writer lock + change detection. A cross-process lock serializes writers, and the swap is aborted if the workbook changed on disk during the import window.
• Report-only ramp. Both the watcher (AUTO_APPLY=false) and the puller (PULL_MODE=preview) ship in look-don't-touch mode. You flip each to live only after reading what it would have done.
• Two-writer guard. A .nas-is-master marker file declares the NAS copy the single source of truth so satellite machines refuse to write diverging copies.

Quickstart

1. A workbook. Either point the tools at your own (Transactions sheet, headers on row 5: Date | Description | Amount | Category | Envelope Tag, named ranges TxDate/TxDesc/TxAmt/TxTag over A6:E605) or generate a compatible starter:

pip install -r nas/requirements.txt
python make_template.py                       # -> cashflow_template.xlsx

2. Envelope rules (optional). Copy envelopes.example.json to envelopes.json and map your Monarch categories to envelope tags. No file means every row imports with a blank tag (pure ad-hoc ledger).

3. Manual import (works forever, no daemon needed):

python monarch_import.py Transactions.csv Cash_Flow_Planner.xlsx --dry-run
python monarch_import.py Transactions.csv Cash_Flow_Planner.xlsx

4. NAS autopilot (Docker; tested on UGREEN UGOS, any compose host works):

cd nas
cp .env.example .env        # set HOST_DATA_DIR; leave AUTO_APPLY=false
sudo sh deploy.sh           # build + start watcher & puller, report-only

Drop a CSV into <HOST_DATA_DIR>/inbox/, read the report-only log, and when you trust it: sudo sh go-live.sh.

5. Automated pulls. Monarch has no official public API (and its MCP connector is paused at the time of writing), so the puller uses the community monarchmoney client. The web app authenticates SSO accounts with a session cookie — copy the cookie: header from a signed-in browser request (DevTools → Network → any graphql request) and run:

sudo docker compose run --rm cashflow-pull --login-cookies
sudo docker compose run --rm cashflow-pull --verify /data/<a real export>.csv
# on RESULT: CLEAN -> set PULL_MODE=live in .env, docker compose up -d

--verify diffs a live pull against a genuine Monarch export before you let it feed the inbox. (Cookie-session support was contributed upstream in hammem/monarchmoney#200; until it merges, this repo carries its own implementation.)

Configuration

Everything is environment variables — see the commented nas/.env.example. The ones people actually change:

Var	Default	Meaning
AUTO_APPLY	false	Watcher: report-only until true.
PULL_MODE	preview	Puller: CSVs go to pulled-preview/ until live.
PULL_DAYS	30	Rolling pull window (overlap is safe — dedup).
PULL_ACCOUNTS	(all)	Comma-separated account-name substrings to include. Scope this to the accounts your ledger models — importing card purchases and the card payment double-counts.
ENVELOPE_MAP_FILE	envelopes.json	Category → envelope tag rules.
ACCOUNT_FILTER	(none)	Importer-side account substring filter.
NOTIFY_CMD	(none)	Hook for ntfy/Telegram/email pings per import/failure.

Testing

python -m pytest -q      # 128 tests: importer, watcher, puller

The suite covers the failure modes that matter for a money file: dedup edge cases (blank descriptions, case variants, text dates/amounts), overflow refusal, lock contention, torn-write recovery, report-only routing, pending exclusion, and a round-trip proving pulled CSVs are indistinguishable from Monarch's own exports to the import pipeline.

Known limitations

• Unofficial API. Monarch may change or break it at any time (they have before — a domain migration and an auth change are both survived in this repo's design). The manual-export path always keeps working.
• Cross-account collisions. Dedup keys on (date, description, amount); two identical charges from different accounts on the same day are treated as one. Rare; scope with PULL_ACCOUNTS/ACCOUNT_FILTER if it matters.
• Pivot tables/slicers are known-lossy under openpyxl saves. Charts, formulas, named ranges, and number formats survive (exercised nightly).

License

MIT — see LICENSE.