trade-log

A small, read-only pipeline that pulls executed option trades from the Schwab Trader API, pairs the individual fills into closed round-trips, and appends them to a personal trade journal kept in a Google Sheet. It runs unattended on a Windows scheduled task each weekday after the close, de-duplicates against what is already in the journal, and surfaces the one thing it cannot automate — Schwab's weekly browser re-login — with a desktop pop-up instead of failing silently.

What it does

• Fetches TRADE transactions for one linked Schwab account over a lookback window (default 90 days) using schwab-py, from a cached token only — it never opens a browser during a sync.
• Reads option contracts from Schwab's structured fields (underlyingSymbol, putCall, strikePrice, expirationDate) rather than parsing OCC symbol strings; the symbol is used only as the grouping key. Non-option items (e.g. the CURRENCY fee legs) are ignored.
• Pairs fills into round-trips with a per-contract position-tracking state machine: legs are walked in time order, and one row is emitted each time the running position returns to flat. Partial fills collapse into a single row using quantity-weighted average open/close prices; same-day re-entry of the same strike yields separate rows. Long-only and closed-only: short/credit opens and still-open positions are skipped with a warning.
• Writes only the data columns (C–K) — open/close dates, ticker, strike, Call/Put, expiry, contracts, fill, sold — and leaves the sheet's pre-filled formula columns (P&L, sizing, W/L, day/swing) untouched. Full mapping in docs/column-mapping.md.
• De-duplicates on re-run by keying every existing row (dates + ticker + strike + type + expiry
  • contracts + prices) so a missed day simply backfills on the next run.
• Scheduled via Windows Task Scheduler: TradeLogDailySync runs run_sync.bat Mon–Fri at 4:30 PM ET (wake-from-sleep, catch-up on missed runs), logging to logs/sync.log.
• Re-auth reminders: exit code 2 (expired login) triggers notify_reauth.ps1, a Yes/No pop-up that launches reauth.bat. A second task, TradeLogReauthCheck, runs check_reauth.ps1 daily and warns before expiry when the token is 6+ days old or missing.

Architecture

Module	Role
src/main.py	CLI entry point (--days, --dry-run, --limit); maps auth failures to exit code 2
src/schwab_client.py	Headless client from the cached token; fetches TRADE transactions for the target account
src/trades.py	Leg extraction + round-trip pairing (Trade dataclass) — the correctness-critical core
src/sheets_client.py	Google Sheets writer via gspread service account; dedupe and append of columns C–K
src/authenticate.py	One-command OAuth re-login with clipboard capture and a local HTTPS callback fallback

Hard problems / lessons

The 7-day refresh token. Schwab refresh tokens expire roughly weekly and can only be renewed by a human logging in through a browser — there is no way to automate past that. The sync is therefore built to fail cleanly: get_client() uses client_from_token_file, so an expired token produces exit code 2 and a log line rather than a hung browser prompt. Even so, an expiry once went unnoticed for weeks: the notification pop-up was launched inline and blocked the scheduled task (which then showed as "Running" until Windows killed it). The fix was to launch the pop-up detached and add the proactive TradeLogReauthCheck so the warning arrives a day before expiry. Lesson: check logs/sync.log before blaming the scheduler.

Getting the redirect back. schwab-py's built-in local-server capture did not work on this machine (the browser would not complete the self-signed 127.0.0.1 TLS handshake). authenticate.py instead watches the clipboard for the redirect URL while also running a best-effort HTTPS server, with manual paste as the final fallback. When the registered callback later dropped its port (falling to privileged 443), the clipboard path kept working unchanged.

One token, many projects. Credentials and the token path live in user-level environment variables and the token itself at %USERPROFILE%\.schwab\token.json, so a single re-login serves every Schwab-reading project on the machine (docs/schwab-global.md). Cloud hosting was rejected deliberately: it would make the weekly re-auth harder, not easier, and dedupe plus a 90-day lookback means exact run timing is not load-bearing. Known limitation: two truly identical round-trips (same day, strike, quantity, and prices) collapse into one row — rare and accepted.

Stack

Python 3.11 · schwab-py (Trader API + OAuth) · gspread (Google Sheets API, service account) · python-dotenv · pytest · Windows Task Scheduler + PowerShell/batch for automation.

Running it

1. Schwab app — register a "Trader API – Individual" app at developer.schwab.com and wait for Approved – Ready For Use. Its callback URL must exactly match SCHWAB_CALLBACK_URL.
2. Google Sheet — create a Google Cloud service account, download its JSON key as service_account.json (gitignored), and share the journal sheet with the key's client_email as Editor.
3. Environment — python -m venv .venv, activate, pip install -r requirements.txt, then copy .env.example .env and fill in the Schwab key/secret, GOOGLE_SHEET_ID, tab name, and (if more than one account is linked) SCHWAB_ACCOUNT_NUMBER. Schwab values may instead be set as user-level env vars to share one login across projects (see docs/schwab-global.md).
4. Authenticate — python -m src.authenticate (run in a real terminal; repeat weekly, or double-click reauth.bat).
5. Sync — python -m src.main --dry-run to preview, python -m src.main --limit 5 for a canary, python -m src.main for the real thing.
6. Schedule — register run_sync.bat as a Task Scheduler job; docs/setup.md has the task settings (weekdays 4:30 PM ET, WakeToRun, StartWhenAvailable) and management commands.

Tests

.venv/Scripts/python.exe -m pytest -q

5 tests cover the pairing logic in src/trades.py: simple round-trip, weighted-average partial fills, ticker normalisation, still-open skip, and same-symbol re-entry producing two trades.

---

Read-only against Schwab: this tool fetches transaction history only and never places, modifies, or cancels orders.