transaction-syncing

name	transaction-syncing
description	Import and manage Fidelity transaction history CSVs. Two workflows - IngestTransactions (local rolling archive from Downloads) and SyncTransactions (Google Sheets push). USE WHEN user mentions "sync transactions", "import transactions", "ingest transactions", "transaction history", OR wants to import Fidelity History CSV.

TransactionSyncing

Import Fidelity transaction history CSV into Google Sheets using a hybrid architecture: master Transactions tab for full audit trail + auto-routing of debit card purchases to Expense Tracker for Budget Planner integration.

Workflow Routing

When executing this workflow, output this notification:

Running the **SyncTransactions** workflow from the **TransactionSyncing** skill...

Workflow	Trigger	File
IngestTransactions	"ingest transactions", "import history", "bring in transactions", user points to Downloads CSV	workflows/IngestTransactions.md
SyncTransactions	"sync transactions", "push to sheets", "transaction sync"	workflows/SyncTransactions.md

Typical flow: IngestTransactions (local archive) -> SyncTransactions (Google Sheets)

Examples

Example 1: Sync after downloading Fidelity transaction history

User: "sync transactions"
-> Reads History_for_Account_{account_id}.csv from notebooks/transactions/
-> Creates/updates Transactions tab with full Fidelity data
-> Routes DEBIT CARD PURCHASE entries to Expense Tracker
-> Auto-categorizes expenses (H-E-B -> Groceries, Tesla -> Auto & Transport)
-> Reports: "Added 45 transactions, 12 expenses categorized"

Example 2: Import new transaction export

User: "import the transaction history"
-> Invokes SyncTransactions workflow
-> Detects duplicates by date + action + amount
-> Skips existing entries, adds only new ones
-> Flags uncategorized expenses for manual review

Example 3: Check recent transactions

User: "import fidelity transactions and update expense tracker"
-> Full sync with expense routing
-> Generates summary of dividends received, purchases, margin interest

Architecture Overview

Data Flow

Fidelity CSV (notebooks/transactions/)
         |
         v
+-------------------+
| Transactions Tab  |  <- Master source (ALL transactions)
| (Full Fidelity)   |
+-------------------+
         |
         | Filter: DEBIT CARD PURCHASE
         v
+-------------------+
| Expense Tracker   |  <- Budget Planner integration
| (Categorized)     |
+-------------------+

Transaction Types Handled

Fidelity Action	Destination	Category
DIVIDEND RECEIVED	Transactions only	DIVIDEND
REINVESTMENT	Transactions only	REINVESTMENT
DEBIT CARD PURCHASE	Transactions + Expense Tracker	Auto-categorized
MARGIN INTEREST	Transactions only	MARGIN_INTEREST
DIRECT DEPOSIT	Transactions only	INCOME
LONG-TERM CAP GAIN	Transactions only	CAP_GAIN
JOURNALED	Transactions only	INTERNAL_TRANSFER

Smart Categorization

See CategoryRules.md for the full pattern matching rules.

Sample patterns:

• H-E-B, KROGER, COSTCO, WAL-MART -> Groceries
• Tesla, SUPERCHA -> Auto & Transport
• BENIHANA, GOLDEN CORRAL, PAPA JOHN -> Dining Out
• CVS, PHARMACY -> Health & Wellness

Input Source: SnapTrade activities (preferred) — CSV is fallback

As of SnapTrade Phase 2 (#72), the preferred input is live normalized activities, not a CSV:

uv run python -m src.integrations.snaptrade.cli activities --output json

This returns one record per activity with a stable shape — type, date, symbol, amount, quantity, currency, description, account — paged across the full history. Map it onto the master Transactions tab exactly as the CSV columns map (Fidelity action -> type, run date -> date, amount -> amount, etc.).

Dedupe is unchanged: Google Sheets stays the single source of truth. Detect duplicates the same way — by date + type + amount against existing rows — and add only new ones. No local cache or state file is introduced; re-running is idempotent because the Sheet is the ledger.

Fallback: the Fidelity CSV path below still works and remains the fallback until the human reconciliation gate (#72) confirms parity. CSV ingestion is not removed in this phase (deletion is Phase 3 / #73).

Core Workflow

1. Read Fidelity Transaction History CSV

Location: notebooks/transactions/History_for_Account_{account_id}.csv

CSV Columns:

Run Date, Action, Symbol, Description, Type, Price ($), Quantity,
Commission ($), Fees ($), Accrued Interest ($), Amount ($),
Cash Balance ($), Settlement Date

2. Create/Update Transactions Tab

Google Sheets Structure:

Column	Header	Source
A	Date	Run Date
B	Action	Action (cleaned)
C	Symbol	Symbol
D	Description	Description
E	Type	Type (Cash/Margin)
F	Amount	Amount ($)
G	Category	Auto-assigned
H	Balance	Cash Balance ($)
I	Settlement	Settlement Date

3. Deduplicate

Match criteria: Date + Action + Amount

For each CSV row:
  key = f"{run_date}|{action}|{amount}"
  if key exists in sheet:
    SKIP (already imported)
  else:
    ADD to Transactions tab

4. Route Expenses to Expense Tracker

Filter: Action contains "DEBIT CARD PURCHASE"

Expense Tracker Format:

Date	Description	Category	Amount	Month

Category Assignment: See CategoryRules.md

5. Generate Summary

SYNC SUMMARY - [Date]
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

TRANSACTIONS TAB:
  New entries: 45
  Skipped (duplicates): 12

EXPENSE TRACKER:
  Expenses routed: 18
  Auto-categorized: 15
  Needs review: 3

BY TYPE:
  Dividends: $342.50
  Margin Interest: -$18.43
  Debit Card: -$1,245.67
  Direct Deposit: +$5,054.09
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Google Sheets Integration

Spreadsheet ID: Read from fin-guru/data/user-profile.yaml -> google_sheets.portfolio_tracker.spreadsheet_id

Creating Transactions Tab (if needed)

// Check if Transactions tab exists
mcp__gdrive__sheets(operation: "listSheets", params: {
    spreadsheetId: SPREADSHEET_ID
})

// Create if missing
mcp__gdrive__sheets(operation: "createSheet", params: {
    spreadsheetId: SPREADSHEET_ID,
    title: "Transactions"
})

// Add headers
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "Transactions!A1:I1",
    values: [["Date", "Action", "Symbol", "Description", "Type", "Amount", "Category", "Balance", "Settlement"]]
})

Adding to Expense Tracker

// Append expense row
mcp__gdrive__sheets(operation: "appendRows", params: {
    spreadsheetId: SPREADSHEET_ID,
    sheetName: "Expense Tracker",
    values: [[date, description, category, amount, month]]
})

Critical Rules

WRITABLE Destinations

• Transactions tab: All columns (new tab, we control format)
• Expense Tracker: Append new rows only (preserve existing)

NEVER MODIFY

• Budget Planner formulas
• Existing Expense Tracker entries

Deduplication Key

• Transactions tab: Date|Action|Amount
• Expense Tracker: Date|Description|Amount

Reference Files

• CategoryRules.md: Pattern matching rules for expense categorization
• fin-guru/data/user-profile.yaml: Spreadsheet ID
• scripts/google-sheets/portfolio-optimizer/: Apps Script reference

Pre-Flight Checklist

Before syncing transactions:

• Transaction History CSV exists in notebooks/transactions/
• CSV is from Fidelity (not other broker)
• Expense Tracker tab exists in Google Sheets
• Current date retrieved via date command

---

Skill Type: Domain (workflow guidance) Enforcement: SUGGEST Priority: Medium Line Count: < 300 (following 500-line rule)