aojdevstudio/TransactionSyncing

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 | |----------|---------|------| | SyncTransactions | "sync transactions", "import transactions", "transaction sync" | workflows/SyncTransactions.md |

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

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)