Quicken Amazon Transaction Matcher — auto-enrich Quicken transactions with Amazon order item names

quicken_amazon_matcher.py

#!/usr/bin/env python3
"""
Quicken ↔ Amazon Order History Matcher
========================================
Enriches Quicken transaction memos with Amazon product names by matching
transactions to Amazon order history exports using date + amount heuristics.

Works with Quicken's Core Data SQLite backend (macOS .quicken package).

Usage:
    1. Export your Amazon order history from amazon.com/your-orders
       (or use a browser extension / Amazon data request to get Order History CSV)
    2. Configure the paths and payee names in the CONFIG section below
    3. Run:  python3 quicken_amazon_matcher.py --scan        # preview matches
             python3 quicken_amazon_matcher.py --apply HIGH  # apply HIGH certainty
             python3 quicken_amazon_matcher.py --apply MED   # apply HIGH + MED
             python3 quicken_amazon_matcher.py --apply ALL   # apply everything matched
    4. Always creates a backup before writing. Restart Quicken to see changes.

Requirements:
    - Python 3.7+
    - No external packages needed (stdlib only: sqlite3, csv, datetime, argparse)

How Matching Works:
    - For purchases (debits): finds Amazon orders within ±14 days at the same amount
    - For returns (credits): searches backwards up to 120 days for the original purchase
    - Multi-item orders: sums line items per Order ID to match bundled transactions
    - Confidence tiers:
        HIGH = single match within 7 days (safe to auto-apply)
        MED  = single match within 8-30 days, or close multi-match
        LOW  = multiple candidates at same amount (needs manual review)

Core Data Notes:
    - Quicken uses Apple Core Data (SQLite) with Z_PK, Z_ENT, Z_OPT columns
    - Z_OPT is an optimistic locking counter: MUST increment on every write
    - Date epoch is 2001-01-01 (add 978307200 for Unix timestamp)
    - Close Quicken BEFORE running writes; reopen after to pick up changes

Author: Written with Claude (Anthropic) in an interactive Quicken enrichment project
License: MIT
"""

import sqlite3
import csv
import os
import sys
import shutil
import argparse
from datetime import datetime, timedelta
from collections import defaultdict

# ============================================================================
# CONFIG — Edit these for your setup
# ============================================================================

# Path to the Quicken SQLite database inside your .quicken package.
# macOS default: ~/Library/Application Support/Quicken/Documents/<name>.quicken/data
# Quicken Beta uses "Quicken Beta" instead of "Quicken" in the path.
QUICKEN_DB_PATH = os.path.expanduser(
    "~/Library/Application Support/Quicken/Documents/My Finances.quicken/data"
)

# Path to the Amazon Order History CSV.
# You can request this from Amazon (Your Account → Download order reports)
# or use a browser extension that exports order history.
# Expected columns: Order Date, Order ID, Product Name, Total Amount
AMAZON_CSV_PATH = os.path.expanduser(
    "~/Downloads/Amazon Order History.csv"
)

# Payee names in Quicken that correspond to Amazon transactions.
# Run the discovery query below if you're not sure which names to use:
#   SELECT DISTINCT up.ZNAME FROM ZTRANSACTION t
#   JOIN ZUSERPAYEE up ON t.ZUSERPAYEE = up.Z_PK
#   WHERE up.ZNAME LIKE '%amazon%' OR up.ZNAME LIKE '%amzn%'
AMAZON_PAYEE_NAMES = [
    "Amazon.com",
    "Amazon",
    "AMZN Mktp US",
    "Amazon Marketplace",
    "Amazon.com*",
    "AMAZON.COM*AMZN.COM/BILL",
]

# Transaction PKs to always skip (e.g., multi-item orders you'll split manually,
# Amazon Prime memberships you want to label yourself, etc.)
SKIP_PKS = set()

# Matching parameters (tune if needed)
DEBIT_WINDOW_NARROW = 14    # days: first-pass window for purchases
DEBIT_WINDOW_WIDE = 30      # days: expanded window for purchases (MED/LOW)
CREDIT_WINDOW = 120          # days: how far back to look for original purchase
HIGH_THRESHOLD = 7           # days: max distance for HIGH certainty
MED_THRESHOLD = 30           # days: max distance for MED certainty
MEMO_MAX_LENGTH = 500        # truncate memos longer than this

# Core Data epoch offset (2001-01-01 in Unix time)
CORE_DATA_EPOCH = 978307200

# ============================================================================
# END CONFIG
# ============================================================================


def parse_amazon_date(date_str):
    """Parse Amazon CSV date, handling multiple formats."""
    date_str = date_str.strip()
    # ISO format: 2024-01-15T19:18:06Z
    if "T" in date_str:
        date_str = date_str[:10]
    # Standard: 2024-01-15 or 01/15/2024
    for fmt in ["%Y-%m-%d", "%m/%d/%Y", "%m/%d/%y", "%d/%m/%Y"]:
        try:
            return datetime.strptime(date_str, fmt)
        except ValueError:
            continue
    return None


def parse_amazon_amount(amount_str):
    """Parse Amazon CSV amount, handling currency symbols and commas."""
    cleaned = amount_str.strip().replace("$", "").replace(",", "").replace("£", "").replace("€", "")
    try:
        return round(float(cleaned), 2)
    except ValueError:
        return None


def load_amazon_orders(csv_path):
    """Load and parse the Amazon order history CSV."""
    orders = []
    with open(csv_path, "r", encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        for row in reader:
            # Skip cancelled orders if status column exists
            status = row.get("Order Status", "").strip()
            if status.lower() == "cancelled":
                continue

            date = parse_amazon_date(row.get("Order Date", ""))
            amount = parse_amazon_amount(row.get("Total Amount", "0"))
            name = row.get("Product Name", "").strip()
            order_id = row.get("Order ID", "").strip()

            if date and amount and amount > 0 and name:
                orders.append({
                    "date": date,
                    "amount": amount,
                    "name": name,
                    "order_id": order_id,
                })
    return orders


def build_order_totals(orders):
    """Group orders by Order ID and compute totals for multi-item matching."""
    groups = defaultdict(list)
    for o in orders:
        groups[o["order_id"]].append(o)

    totals = {}
    for oid, items in groups.items():
        total = round(sum(i["amount"] for i in items), 2)
        names = [i["name"] for i in items]
        totals[oid] = {
            "total": total,
            "names": names,
            "date": items[0]["date"],
            "count": len(items),
        }
    return totals


def resolve_payee_pks(cursor, payee_names):
    """Look up Z_PK values for the given payee names."""
    pks = set()
    for name in payee_names:
        cursor.execute(
            "SELECT Z_PK FROM ZUSERPAYEE WHERE ZNAME = ? OR ZNAME LIKE ?",
            (name, name),
        )
        for row in cursor.fetchall():
            pks.add(row[0])

    if not pks:
        # Fallback: search broadly
        print("WARNING: No exact payee matches found. Trying broad search...")
        cursor.execute(
            "SELECT Z_PK, ZNAME FROM ZUSERPAYEE WHERE ZNAME LIKE '%amazon%' OR ZNAME LIKE '%amzn%'"
        )
        for row in cursor.fetchall():
            print(f"  Found payee: PK={row[0]} Name='{row[1]}'")
            pks.add(row[0])

    return pks


def get_empty_memo_transactions(cursor, payee_pks, include_split_memo=False):
    """
    Fetch Amazon transactions that have no memo.

    If include_split_memo=False (default), also excludes transactions that have
    memos at the split/category level (ZCASHFLOWTRANSACTIONENTRY.ZNOTE), since
    those are already documented within their splits.
    """
    pk_list = ",".join(str(p) for p in payee_pks)

    if include_split_memo:
        # Simple: just get everything with empty transaction-level memo
        cursor.execute(f"""
            SELECT Z_PK, ZENTEREDDATE, ZAMOUNT
            FROM ZTRANSACTION
            WHERE ZUSERPAYEE IN ({pk_list})
              AND (ZNOTE IS NULL OR ZNOTE = '')
            ORDER BY ZENTEREDDATE
        """)
    else:
        # Exclude transactions that have split-level memos
        cursor.execute(f"""
            SELECT t.Z_PK, t.ZENTEREDDATE, t.ZAMOUNT
            FROM ZTRANSACTION t
            WHERE t.ZUSERPAYEE IN ({pk_list})
              AND (t.ZNOTE IS NULL OR t.ZNOTE = '')
              AND NOT EXISTS (
                  SELECT 1 FROM ZCASHFLOWTRANSACTIONENTRY cfte
                  WHERE cfte.ZPARENT = t.Z_PK
                    AND cfte.ZNOTE IS NOT NULL AND cfte.ZNOTE != ''
              )
            ORDER BY t.ZENTEREDDATE
        """)

    return cursor.fetchall()


def match_single_item(tx_date, tx_amount, is_credit, orders):
    """Find single-item Amazon orders matching this transaction."""
    candidates = []
    for o in orders:
        if abs(o["amount"] - tx_amount) > 0.01:
            continue

        if is_credit:
            days = (tx_date - o["date"]).days
            if 0 <= days <= CREDIT_WINDOW:
                candidates.append((days, o["name"], o["order_id"]))
        else:
            days = abs((tx_date - o["date"]).days)
            if days <= DEBIT_WINDOW_WIDE:
                candidates.append((days, o["name"], o["order_id"]))

    candidates.sort(key=lambda x: x[0])
    return candidates


def match_multi_item(tx_date, tx_amount, is_credit, order_totals):
    """Find multi-item Amazon orders whose total matches this transaction."""
    candidates = []
    for oid, info in order_totals.items():
        if abs(info["total"] - tx_amount) > 0.01:
            continue

        if is_credit:
            days = (tx_date - info["date"]).days
            if 0 <= days <= CREDIT_WINDOW:
                combined = "; ".join(n[:60] for n in info["names"])
                candidates.append((days, combined, oid))
        else:
            days = abs((tx_date - info["date"]).days)
            if days <= DEBIT_WINDOW_NARROW:
                combined = "; ".join(n[:60] for n in info["names"])
                candidates.append((days, combined, oid))

    candidates.sort(key=lambda x: x[0])
    return candidates


def classify_match(candidates):
    """
    Given sorted candidates, return (certainty, best_name, all_candidates).
    Certainty: 'HIGH', 'MED', 'LOW', or None if no candidates.
    """
    if not candidates:
        return None, None, []

    best_days, best_name, best_oid = candidates[0]

    # Deduplicate by product name (different sizes/colors of same item)
    unique_names = set()
    for _, name, _ in candidates:
        unique_names.add(name[:80])

    unique_orders = set()
    for _, _, oid in candidates:
        unique_orders.add(oid)

    n_unique = len(unique_names)

    if n_unique == 1 and best_days <= HIGH_THRESHOLD:
        return "HIGH", best_name, candidates
    elif n_unique == 1 and best_days <= MED_THRESHOLD:
        return "MED", best_name, candidates
    elif n_unique == 1:
        return "LOW", best_name, candidates
    elif len(unique_orders) == 1 and best_days <= HIGH_THRESHOLD:
        # Multiple items from same order
        return "MED", best_name, candidates
    elif best_days <= HIGH_THRESHOLD:
        # Multiple candidates but very close date
        if len(candidates) >= 2 and candidates[1][0] > best_days + 7:
            return "MED", best_name, candidates  # clear winner by distance
        return "LOW", best_name, candidates
    else:
        return "LOW", best_name, candidates


def run_matching(cursor, payee_pks, orders, order_totals):
    """Run the full matching pipeline. Returns list of match results."""
    txns = get_empty_memo_transactions(cursor, payee_pks)
    print(f"Found {len(txns)} truly-empty Amazon transactions to match")

    results = []
    for pk, entered_date, amount in txns:
        if pk in SKIP_PKS:
            continue

        is_credit = amount > 0
        tx_amount = round(abs(amount), 2)
        tx_date = datetime.utcfromtimestamp(entered_date + CORE_DATA_EPOCH)

        # Try single-item match first
        candidates = match_single_item(tx_date, tx_amount, is_credit, orders)

        # If no single-item match, try multi-item order totals
        if not candidates:
            candidates = match_multi_item(tx_date, tx_amount, is_credit, order_totals)

        certainty, best_name, all_candidates = classify_match(candidates)

        # Build memo
        memo = None
        if best_name:
            memo = best_name
            if is_credit and not memo.startswith("Return:"):
                memo = "Return: " + memo
            if len(memo) > MEMO_MAX_LENGTH:
                memo = memo[: MEMO_MAX_LENGTH - 3] + "..."

        results.append({
            "pk": pk,
            "date": tx_date.strftime("%Y-%m-%d"),
            "amount": tx_amount,
            "is_credit": is_credit,
            "certainty": certainty,
            "memo": memo,
            "days": candidates[0][0] if candidates else None,
            "candidate_count": len(set(c[1][:80] for c in all_candidates)) if all_candidates else 0,
        })

    return results


def print_results(results):
    """Print a summary of matching results."""
    by_cert = defaultdict(list)
    for r in results:
        by_cert[r["certainty"] or "NONE"].append(r)

    total = len(results)
    print(f"\n{'='*80}")
    print(f"  MATCHING RESULTS: {total} transactions")
    print(f"{'='*80}")
    for level in ["HIGH", "MED", "LOW", "NONE"]:
        group = by_cert.get(level, [])
        pct = f"({100*len(group)/total:.0f}%)" if total else ""
        print(f"  {level:>4}: {len(group):>4} {pct}")

    for level in ["HIGH", "MED", "LOW", "NONE"]:
        group = by_cert.get(level, [])
        if not group:
            continue
        print(f"\n{'─'*80}")
        print(f"  {level} ({len(group)})")
        print(f"{'─'*80}")
        for r in group:
            cr = "CR" if r["is_credit"] else "DB"
            if r["memo"]:
                days_str = f"[{r['days']}d, {r['candidate_count']}cand]"
                print(f"  PK={r['pk']:>6} | {r['date']} | ${r['amount']:>8.2f} {cr} | {r['memo'][:65]} {days_str}")
            else:
                print(f"  PK={r['pk']:>6} | {r['date']} | ${r['amount']:>8.2f} {cr} | (no match)")


def apply_updates(db_path, results, min_certainty="HIGH"):
    """
    Apply matched memos to the Quicken database.

    min_certainty: 'HIGH' (safest), 'MED', or 'ALL' (includes LOW)
    """
    levels = {"HIGH"}
    if min_certainty in ("MED", "ALL"):
        levels.add("MED")
    if min_certainty == "ALL":
        levels.add("LOW")

    to_apply = [r for r in results if r["certainty"] in levels and r["memo"]]

    if not to_apply:
        print("No updates to apply at this certainty level.")
        return

    # Create backup
    backup_path = db_path + f".backup_{datetime.now().strftime('%Y%m%d_%H%M%S')}"
    print(f"Creating backup: {backup_path}")
    shutil.copy2(db_path, backup_path)

    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    applied = 0
    skipped = 0
    for r in to_apply:
        # Safety check: verify the memo is still empty
        c.execute("SELECT ZNOTE FROM ZTRANSACTION WHERE Z_PK = ?", (r["pk"],))
        row = c.fetchone()
        if row is None:
            print(f"  WARNING: PK={r['pk']} not found, skipping")
            skipped += 1
            continue
        if row[0] and row[0].strip():
            print(f"  WARNING: PK={r['pk']} already has memo, skipping")
            skipped += 1
            continue

        # Write the memo and increment Z_OPT (Core Data optimistic lock)
        c.execute(
            "UPDATE ZTRANSACTION SET ZNOTE = ?, Z_OPT = Z_OPT + 1 WHERE Z_PK = ?",
            (r["memo"], r["pk"]),
        )
        applied += 1

    conn.commit()
    conn.close()

    print(f"\nApplied {applied} updates ({skipped} skipped)")
    print(f"Backup saved to: {backup_path}")
    print("Restart Quicken to see changes.")


def print_coverage(cursor, payee_pks):
    """Print current memo coverage statistics."""
    pk_list = ",".join(str(p) for p in payee_pks)

    cursor.execute(f"SELECT COUNT(*) FROM ZTRANSACTION WHERE ZUSERPAYEE IN ({pk_list})")
    total = cursor.fetchone()[0]

    cursor.execute(f"""
        SELECT COUNT(*) FROM ZTRANSACTION
        WHERE ZUSERPAYEE IN ({pk_list})
          AND ZNOTE IS NOT NULL AND ZNOTE != ''
    """)
    with_memo = cursor.fetchone()[0]

    cursor.execute(f"""
        SELECT COUNT(DISTINCT t.Z_PK) FROM ZTRANSACTION t
        JOIN ZCASHFLOWTRANSACTIONENTRY cfte ON cfte.ZPARENT = t.Z_PK
        WHERE t.ZUSERPAYEE IN ({pk_list})
          AND (t.ZNOTE IS NULL OR t.ZNOTE = '')
          AND cfte.ZNOTE IS NOT NULL AND cfte.ZNOTE != ''
    """)
    split_memo = cursor.fetchone()[0]

    empty = total - with_memo
    truly_empty = empty - split_memo

    print(f"\n{'='*50}")
    print(f"  COVERAGE REPORT")
    print(f"{'='*50}")
    print(f"  Total Amazon transactions:   {total:>5}")
    print(f"  With transaction-level memo: {with_memo:>5}  ({100*with_memo/total:.1f}%)")
    print(f"  Empty transaction-level:     {empty:>5}")
    print(f"    ├─ With split-level memos: {split_memo:>5}  (already documented)")
    print(f"    └─ Truly empty:            {truly_empty:>5}")
    print(f"  Effective coverage:          {with_memo+split_memo:>5}/{total}  ({100*(with_memo+split_memo)/total:.1f}%)")


def main():
    parser = argparse.ArgumentParser(
        description="Match Quicken Amazon transactions to Amazon order history",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog="""
Examples:
  %(prog)s --scan              Preview all matches without writing anything
  %(prog)s --apply HIGH        Apply only HIGH certainty matches (safest)
  %(prog)s --apply MED         Apply HIGH + MED certainty matches
  %(prog)s --apply ALL         Apply all matches including LOW (review first!)
  %(prog)s --coverage          Show current memo coverage statistics
  %(prog)s --discover-payees   List Amazon-related payees in your Quicken DB
        """,
    )
    parser.add_argument("--scan", action="store_true", help="Scan and preview matches")
    parser.add_argument("--apply", choices=["HIGH", "MED", "ALL"], help="Apply matches at given certainty level")
    parser.add_argument("--coverage", action="store_true", help="Show current coverage stats")
    parser.add_argument("--discover-payees", action="store_true", help="Find Amazon payee names in DB")
    parser.add_argument("--include-split-memo", action="store_true",
                        help="Include transactions that already have split-level memos")
    args = parser.parse_args()

    if not any([args.scan, args.apply, args.coverage, args.discover_payees]):
        parser.print_help()
        sys.exit(1)

    # Validate paths
    if not os.path.exists(QUICKEN_DB_PATH):
        print(f"ERROR: Quicken database not found at:\n  {QUICKEN_DB_PATH}")
        print("Edit QUICKEN_DB_PATH in the CONFIG section of this script.")
        sys.exit(1)

    if args.discover_payees:
        conn = sqlite3.connect(QUICKEN_DB_PATH)
        c = conn.cursor()
        c.execute("""
            SELECT up.Z_PK, up.ZNAME, COUNT(t.Z_PK) as txn_count
            FROM ZUSERPAYEE up
            JOIN ZTRANSACTION t ON t.ZUSERPAYEE = up.Z_PK
            WHERE up.ZNAME LIKE '%amazon%' OR up.ZNAME LIKE '%amzn%'
               OR up.ZNAME LIKE '%Amazon%' OR up.ZNAME LIKE '%AMZN%'
            GROUP BY up.Z_PK
            ORDER BY txn_count DESC
        """)
        print("\nAmazon-related payees in your Quicken DB:")
        print(f"{'PK':>6}  {'Transactions':>12}  Name")
        print(f"{'─'*6}  {'─'*12}  {'─'*40}")
        for pk, name, count in c.fetchall():
            print(f"{pk:>6}  {count:>12}  {name}")
        conn.close()
        print("\nAdd these names to AMAZON_PAYEE_NAMES in the CONFIG section.")
        return

    conn = sqlite3.connect(QUICKEN_DB_PATH)
    c = conn.cursor()

    # Resolve payee PKs
    payee_pks = resolve_payee_pks(c, AMAZON_PAYEE_NAMES)
    if not payee_pks:
        print("ERROR: No matching payees found. Run --discover-payees first.")
        conn.close()
        sys.exit(1)
    print(f"Found {len(payee_pks)} Amazon payee entries")

    if args.coverage:
        print_coverage(c, payee_pks)
        conn.close()
        return

    # Load Amazon CSV
    if not os.path.exists(AMAZON_CSV_PATH):
        print(f"ERROR: Amazon CSV not found at:\n  {AMAZON_CSV_PATH}")
        print("Edit AMAZON_CSV_PATH in the CONFIG section of this script.")
        conn.close()
        sys.exit(1)

    print(f"Loading Amazon orders from: {AMAZON_CSV_PATH}")
    orders = load_amazon_orders(AMAZON_CSV_PATH)
    order_totals = build_order_totals(orders)
    print(f"  Loaded {len(orders)} order lines, {len(order_totals)} unique orders")

    if not orders:
        print("ERROR: No orders loaded. Check CSV format (needs: Order Date, Order ID, Product Name, Total Amount)")
        conn.close()
        sys.exit(1)

    # Show date range
    dates = [o["date"] for o in orders]
    print(f"  Date range: {min(dates).strftime('%Y-%m-%d')} to {max(dates).strftime('%Y-%m-%d')}")

    # Run matching
    results = run_matching(c, payee_pks, orders, order_totals)
    print_results(results)
    print_coverage(c, payee_pks)

    if args.apply:
        print(f"\n>>> Applying {args.apply} certainty matches...")
        conn.close()  # Close read connection before write
        apply_updates(QUICKEN_DB_PATH, results, args.apply)
    else:
        conn.close()
        print("\nRun with --apply HIGH to apply the safest matches.")


if __name__ == "__main__":
    main()

QUICKEN_AMAZON_MATCHER_GUIDE.md

Quicken ↔ Amazon Order History Matcher — AI Context Guide

What This Is

This file is a context document you can give to an AI coding assistant (Claude, ChatGPT, Copilot, etc.) so it understands the Quicken Amazon Matcher project and can help you set it up, troubleshoot it, or extend it.

How to use this file: Copy its contents into a conversation with your AI assistant, or upload it as an attachment. Then ask your question. The AI will have everything it needs to give you specific, accurate help.

---

The Problem We're Solving

When you buy things on Amazon and they show up in Quicken (macOS), the transactions just say "Amazon.com" or "AMZN Mktp US" with no indication of what you bought. If you have hundreds or thousands of Amazon transactions, it's impossible to know what any of them were for without logging into Amazon and cross-referencing manually.

Amazon lets you export your complete order history as a CSV file. That CSV contains the product name, order date, and amount for everything you've ever purchased.

This project matches those two data sources — Quicken transactions and Amazon order history — by date and dollar amount, then writes the actual product names into Quicken's memo field automatically.

---

What You Need Before Starting

1. Your Amazon Order History CSV

You need a CSV file with your Amazon order history. There are a few ways to get this:

• Amazon's built-in export: Go to amazon.com → Your Account → Download order reports. This gives you a CSV but may be limited in date range.
• Request Your Data: Go to amazon.com → Your Account → Request Your Data → Select "Your Orders." Amazon will email you a download link within a few days. This is the most complete option.
• Browser extensions: There are extensions like "Amazon Order History Reporter" that can export your history. These vary in reliability.

The CSV must have these columns (the names must match exactly):

• Order Date — when the order was placed (e.g., 2024-01-15 or 2024-01-15T19:18:06Z)
• Order ID — Amazon's order identifier (e.g., 113-1234567-8901234)
• Product Name — the item name
• Total Amount — the price charged (e.g., 29.99 or $29.99)

An optional but helpful column:

• Order Status — if present, cancelled orders are automatically skipped

2. Your Quicken Database Path

On macOS, Quicken stores its data as a SQLite database inside a .quicken package. The typical path is:

~/Library/Application Support/Quicken/Documents/YOUR_FILE_NAME.quicken/data

If you use Quicken Beta, the path uses "Quicken Beta" instead of "Quicken":

~/Library/Application Support/Quicken Beta/Documents/YOUR_FILE_NAME.quicken/data

To find your exact path:

1. Open Finder
2. Hold Option and click the Go menu → Library
3. Navigate to: Application Support → Quicken (or Quicken Beta) → Documents
4. You'll see a file ending in .quicken — that's a package
5. Right-click it → Show Package Contents
6. Inside you'll find a file called data — that's the SQLite database

Or ask your AI assistant to run:

find ~/Library/Application\ Support -name "data" -path "*.quicken/*" 2>/dev/null

3. Python 3

The script requires Python 3.7 or later. macOS usually has this pre-installed. Check by running:

python3 --version

No additional packages need to be installed — the script uses only Python's standard library.

---

How the Quicken Database Works

Understanding this section isn't required to use the script, but it's essential context if the AI needs to troubleshoot or modify anything.

Core Data / SQLite

Quicken on macOS uses Apple's Core Data framework, which stores its data as a SQLite database. This means:

• Every table name starts with Z (e.g., ZTRANSACTION, ZUSERPAYEE)
• Every table has special columns:
  • Z_PK — the primary key (unique ID for each row)
  • Z_ENT — entity type identifier (which "kind" of thing this row represents)
  • Z_OPT — optimistic locking counter (critical for writes — see below)

Key Tables

ZTRANSACTION — One row per transaction (the main table)
├── Z_PK                — Unique identifier
├── ZENTEREDDATE        — Transaction date (Core Data epoch, see below)
├── ZAMOUNT             — Amount (negative = debit/purchase, positive = credit/return)
├── ZNOTE               — The memo/note field (this is what we write to)
├── ZUSERPAYEE          — Foreign key → ZUSERPAYEE.Z_PK (who you paid)
└── Z_OPT               — Version counter (MUST increment on writes)

ZUSERPAYEE — One row per payee name
├── Z_PK                — Unique identifier
└── ZNAME               — Payee name (e.g., "Amazon.com", "AMZN Mktp US")

ZCASHFLOWTRANSACTIONENTRY — Split lines within a transaction
├── Z_PK                — Unique identifier
├── ZPARENT             — Foreign key → ZTRANSACTION.Z_PK
├── ZAMOUNT             — Split amount
├── ZCATEGORYTAG        — Foreign key → ZTAG.Z_PK (category)
└── ZNOTE               — Split-level memo (may have product info even when
                           the transaction-level memo is empty)

ZTAG — Categories and tags
├── Z_PK                — Unique identifier
└── ZNAME               — Tag/category name (e.g., "Groceries", "Recreation")

Critical: The Z_OPT Column

Every time you update a row, you must increment Z_OPT by 1. This is Core Data's optimistic locking mechanism. The correct update pattern is:

UPDATE ZTRANSACTION SET ZNOTE = 'your memo', Z_OPT = Z_OPT + 1 WHERE Z_PK = 12345

If you forget to increment Z_OPT, Quicken may:

• Silently discard your changes on next launch
• Crash or show a Core Data conflict error
• Corrupt the optimistic lock state for that row

Critical: The Date Epoch

Core Data stores dates as seconds since January 1, 2001 (not 1970 like Unix). To convert:

unix_timestamp = core_data_timestamp + 978307200

Or in SQL:

date(ZENTEREDDATE + 978307200, 'unixepoch')  -- gives you a YYYY-MM-DD string

Critical: Close Quicken Before Writing

The SQLite database is actively used by Quicken when it's open. Writing to it while Quicken is running can cause data corruption or lock conflicts. Always:

1. Quit Quicken completely
2. Run the script
3. Reopen Quicken

The script creates a timestamped backup before every write operation, so you can always recover.

---

How the Matching Logic Works

The Basic Idea

For each Quicken Amazon transaction that has no memo, we search the Amazon CSV for an order at the same dollar amount near the same date.

Purchases (Debits)

Quicken records a purchase as a negative ZAMOUNT. The script looks for Amazon orders:

• At the exact same dollar amount (within $0.01 tolerance)
• Within ±14 days of the Quicken transaction date (first pass)
• Expanding to ±30 days for MED/LOW certainty matches

The date window accounts for the delay between when Amazon charges your card and when the transaction posts in Quicken.

Returns (Credits)

Returns are positive ZAMOUNT in Quicken. The critical insight here: returns must be matched backwards in time. When Amazon refunds you on September 10, the original purchase might have been in July or August. The script looks for:

• Amazon orders at the same dollar amount
• Placed 0 to 120 days before the Quicken credit date

This was one of the biggest bugs we had to fix during development — initially the script searched ±14 days for everything, which meant returns almost never matched because the purchase was weeks or months earlier.

Multi-Item Orders

Sometimes Amazon charges a single total for multiple items. For example, you buy a $15 book and a $10 charger, and Quicken shows a $25 transaction. The script handles this by:

1. Grouping all Amazon CSV lines by Order ID
2. Summing the individual item amounts within each order
3. Matching the order total against the Quicken transaction
4. Writing all item names joined with semicolons as the memo

Confidence Tiers

Every match is classified by confidence:

Tier	Criteria	Recommendation
HIGH	Single match within 7 days	Safe to auto-apply
MED	Single match within 8–30 days, or clear winner among multiple	Review a sample, then apply
LOW	Multiple different products at same price in time window	Needs human judgment

Split-Level Memos

Quicken transactions can have "splits" — sub-entries that break a transaction into categories. These splits have their own memo field. A transaction might have an empty memo at the top level but detailed product names in its splits (e.g., if someone previously categorized the splits by hand).

The script distinguishes between:

• Truly empty: No memo at transaction level AND no memos in splits → needs matching
• Split-documented: Empty transaction memo but splits have memos → already documented, skip

This prevents overstating the number of "missing" memos and avoids unnecessary work.

---

Using the Script — Step by Step

Step 1: Configure

Open quicken_amazon_matcher.py in any text editor. Near the top, you'll see a CONFIG section. Edit these values:

QUICKEN_DB_PATH = os.path.expanduser(
    "~/Library/Application Support/Quicken/Documents/My Finances.quicken/data"
)

AMAZON_CSV_PATH = os.path.expanduser(
    "~/Downloads/Amazon Order History.csv"
)

AMAZON_PAYEE_NAMES = [
    "Amazon.com",
    "Amazon",
    "AMZN Mktp US",
    # ... add your payee names
]

Step 2: Discover Your Payee Names

If you're not sure what Amazon appears as in your Quicken, run:

python3 quicken_amazon_matcher.py --discover-payees

This searches your Quicken database for any payee containing "amazon" or "amzn" and shows you the exact names and how many transactions each has. Copy these names into the AMAZON_PAYEE_NAMES list.

Step 3: Check Current Coverage

python3 quicken_amazon_matcher.py --coverage

This shows how many Amazon transactions already have memos vs. how many are empty.

Step 4: Scan for Matches (Preview Only)

python3 quicken_amazon_matcher.py --scan

This runs the full matching engine and prints every match with its confidence tier, but writes nothing. Review the output, especially the HIGH matches — these should be essentially perfect.

Step 5: Apply HIGH Confidence Matches

Close Quicken, then:

python3 quicken_amazon_matcher.py --apply HIGH

This writes only HIGH certainty matches and creates a backup first. Reopen Quicken and verify a few transactions look correct.

Step 6: Apply MED Matches (Optional)

If HIGH looked good:

python3 quicken_amazon_matcher.py --apply MED

This applies both HIGH and MED matches. (It's safe to run after Step 5 — the script checks each memo is still empty before writing, so HIGH matches from the previous run won't be touched again.)

Step 7: Review LOW Matches Manually

LOW matches have multiple candidate products at the same price. The --scan output shows you all the candidates. You can:

• Use Quicken's category tags to disambiguate (e.g., a transaction categorized as "Groceries" is more likely to be food than electronics)
• Check the Amazon website for the specific order
• Decide that an approximate memo is better than no memo at all

To apply LOW matches: --apply ALL (but review the scan output first).

---

Common Issues and Troubleshooting

"ERROR: Quicken database not found"

The path in QUICKEN_DB_PATH is wrong. Use the Finder method described above to find the correct path, or run:

find ~/Library/Application\ Support -name "data" -path "*.quicken/*"

"No orders loaded. Check CSV format"

Your Amazon CSV may have different column names. Open the CSV in a text editor and check the first line (the headers). The script expects exactly: Order Date, Order ID, Product Name, Total Amount. If yours are different, you can either:

• Rename the columns in the CSV
• Ask the AI to modify the load_amazon_orders() function

All matches come back as "NO MATCH"

This usually means the Amazon CSV dates aren't being parsed. The script handles several date formats (2024-01-15, 2024-01-15T19:18:06Z, 01/15/2024), but yours might be different. Check a few rows of the CSV and tell the AI what format the dates are in.

"Quicken shows old data after running the script"

You may not have quit Quicken before running. Quicken caches data in memory. Quit completely (Cmd+Q, not just close the window) and reopen.

Some transactions match to the wrong product

This happens when two different products cost exactly the same and shipped around the same time. The MED and LOW tiers flag these ambiguous cases. For HIGH matches this is extremely rare, but if you find one, you can manually edit the memo in Quicken.

Returns don't match

If your Amazon order history CSV doesn't go back far enough, returns from older purchases won't find their original order. You may need a more complete CSV export. Also, Amazon sometimes combines multiple items into a single refund at an amount that doesn't correspond to any individual item — these can't be auto-matched.

---

Extending This Script

Here are things you might want to ask your AI assistant to help with:

• Add more payee sources: The same matching logic works for any retailer that gives you order history exports (Walmart, Target, etc.). You'd add a new CSV loader and payee name list.

• Category-based disambiguation: If your Quicken transactions are already categorized (e.g., "Groceries", "Clothing"), the AI can write logic that uses those categories to pick the most likely candidate from LOW matches. For example, if a $15 transaction is categorized as "Groceries" and the two candidates are a protein bar vs. a phone case, the protein bar wins.

• Batch manual overrides: Create a simple CSV of PK,memo pairs for transactions you've manually identified, and have the script apply them in bulk.

• Recurring purchases: If you buy the same item regularly (e.g., a subscription snack box at $12.30 every month), the AI can identify the pattern and apply a generic memo to all of them at once.

• Undo support: The script creates backups, but you could add a --rollback command that restores from the most recent backup.

---

Database Reference (Quick Cheat Sheet)

Find all Amazon payees:

SELECT Z_PK, ZNAME FROM ZUSERPAYEE
WHERE ZNAME LIKE '%amazon%' OR ZNAME LIKE '%amzn%';

Count empty-memo Amazon transactions:

SELECT COUNT(*) FROM ZTRANSACTION
WHERE ZUSERPAYEE IN (your_payee_pks)
  AND (ZNOTE IS NULL OR ZNOTE = '');

See a transaction with its splits:

SELECT t.Z_PK, date(t.ZENTEREDDATE + 978307200, 'unixepoch') as dt,
       t.ZAMOUNT, t.ZNOTE,
       cfte.ZAMOUNT as split_amt, cfte.ZNOTE as split_memo, tag.ZNAME as category
FROM ZTRANSACTION t
LEFT JOIN ZCASHFLOWTRANSACTIONENTRY cfte ON cfte.ZPARENT = t.Z_PK
LEFT JOIN ZTAG tag ON tag.Z_PK = cfte.ZCATEGORYTAG
WHERE t.Z_PK = 12345;

Safe memo update (always use this pattern):

UPDATE ZTRANSACTION SET ZNOTE = 'Your Memo Here', Z_OPT = Z_OPT + 1 WHERE Z_PK = 12345;

Check coverage:

SELECT
  COUNT(*) as total,
  SUM(CASE WHEN ZNOTE IS NOT NULL AND ZNOTE != '' THEN 1 ELSE 0 END) as with_memo,
  SUM(CASE WHEN ZNOTE IS NULL OR ZNOTE = '' THEN 1 ELSE 0 END) as empty
FROM ZTRANSACTION
WHERE ZUSERPAYEE IN (your_payee_pks);

---

Project History

This script was developed over multiple sessions working with Claude (Anthropic) in Quicken's Cowork mode. The process involved:

1. Reverse-engineering the Quicken Core Data schema
2. Building the matching engine iteratively — starting with exact date+amount, then adding multi-item order support, backward-looking return matching, and confidence tiering
3. Applying matches in waves: HIGH first (safest), then MED, then manually-reviewed LOW
4. Discovering the split-memo distinction (transactions with empty top-level memo but documented splits)
5. Using Quicken category tags to disambiguate LOW-confidence matches

The approach went from ~5% memo coverage to 98%+ across 4,150 Amazon transactions spanning 2002–2026.

Thank you. This solves a big problem