Portfoliosyncing

PortfolioSyncing

Safely import broker CSV position exports into the Google Sheets DataHub tab, ensuring data integrity, validating changes, and protecting sacred formulas.

Multi-Broker Support

Supported Brokers:

• ✅ Fidelity - Fully automated parsing
• ⚠️ *Schwab, Vanguard, TD Ameritrade, ETRADE, Robinhood** - Manual mapping required (coming soon)

Broker Detection: Finance Guru automatically detects your broker from user-profile.yaml (set during onboarding). CSV parsing is tailored to your broker's format.

See: docs/broker-csv-export-guide.md for detailed export instructions per broker.

Workflow Routing

When executing a workflow, output the corresponding notification:

Typical flow: IngestPositions (move from Downloads) -> SyncPortfolio (push to Google Sheets)

Notifications:

Running the **IngestPositions** workflow from the **PortfolioSyncing** skill...

Running the **SyncPortfolio** workflow from the **PortfolioSyncing** skill...

Examples

Example 1: Full flow from Downloads

User: "ingest positions" or "bring in positions"
-> Scans ~/Downloads/ for Portfolio_Positions_*.csv and Balances_*.csv
-> Classifies regular vs dividend view by reading headers
-> Moves regular view as-is (already date-tagged)
-> Renames dividend view to Dividend_Positions_MMM-DD-YYYY.csv
-> Moves Balances file (overwrites existing)
-> Reports files moved and suggests "portfolio-sync" next

Example 2: Sync after ingest

User: "portfolio-sync"
-> Reads Portfolio_Positions_*.csv and Balances_*.csv from notebooks/updates/
-> Compares with Google Sheets DataHub
-> Updates quantities, cost basis, SPAXX, margin debt
-> Reports changes and validates formulas

Example 3: Update positions after trades

User: "I just bought more JEPI, sync my portfolio"
-> Invokes SyncPortfolio workflow
-> Detects quantity change in JEPI
-> If >10% change, asks for confirmation
-> Updates DataHub with new position data

Example 4: Handling duplicate downloads

User downloads both regular and dividend views from Fidelity
-> ~/Downloads/ contains: Portfolio_Positions_Mar-06-2026.csv
                          Portfolio_Positions_Mar-06-2026 (1).csv
-> Reads header of each to classify
-> Regular view (has "Average Cost Basis") -> notebooks/updates/Portfolio_Positions_Mar-06-2026.csv
-> Dividend view (has "Ex-date") -> notebooks/updates/Dividend_Positions_Mar-06-2026.csv

CSV Format Reference

Fidelity Positions CSV (Regular View)

Header row (17 columns):

Account Number,Account Name,Investment Type,Symbol,Description,Quantity,Last Price,Last Price Change,Current Value,Today's Gain/Loss Dollar,Today's Gain/Loss Percent,Total Gain/Loss Dollar,Total Gain/Loss Percent,Percent Of Account,Cost Basis Total,Average Cost Basis,Type

Key fields for sync: Symbol (col 4), Quantity (col 6), Average Cost Basis (col 16), Type (col 17 — "Margin" or "Cash")

Fidelity Positions CSV (Dividend View)

Header row (19 columns):

Account Number,Account Name,Investment Type,Symbol,Description,Quantity,Last Price,Last Price Change,Current Value,Percent Of Account,Ex-date,Amount per share,Pay date,Dist. yield,Distribution yield as of,SEC yield,SEC yield as of,Est. annual income,Type

Quick classifier: If header contains Ex-date -> dividend view. If header contains Average Cost Basis -> regular view.

Fidelity Balances CSV

Key-value format (not columnar). Extract:

• "Settled cash" → SPAXX row (Column L: Current Value)
• "Account equity percentage" → If 100%, margin debt = $0
• "Net debit" → Actual margin balance (negative value = margin debt)
• "Margin interest accrued this month" → If > $1, there IS margin debt

Cash Position Logic:

• Do NOT use SPAXX value from Positions CSV (shows only settled money market)
• Use "Settled cash" from Balances CSV for the SPAXX row
• If "Settled cash" = 0, then SPAXX = $0 (all funds are invested or in margin)
• "Cash market value" is NOT cash — it's the value of positions in your Cash account (vs Margin account)

Critical Rules

WRITABLE Columns (from CSV)

• ✅ Column A: Ticker
• ✅ Column B: Quantity
• ✅ Column G: Avg Cost Basis

SACRED Columns (NEVER TOUCH)

• ❌ Column C: Last Price (GOOGLEFINANCE formulas)
• ❌ Columns D-F: $ Change, % Change, Volume (formulas)
• ❌ Columns H-M: Gains/Losses calculations (formulas)
• ❌ Columns N-S: Ranges, dividends, layer (formulas/manual)

Update Pattern: Individual Cell Updates ONLY

Golden Rule: NEVER include columns C-F in your update range. NEVER pass empty strings to any cell.

Empty strings ("") in columns C-F DELETE the GOOGLEFINANCE and calculation formulas. Always update columns A, B, G individually:

// ✅ RIGHT - Update ONLY writable columns, one at a time
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!B13:B13",  // ✅ Single column, specific row
    values: [["72.942"]]
})

// ❌ WRONG - Multi-column range with empty strings kills formulas
mcp__gdrive__sheets(operation: "updateCells", params: {
    range: "DataHub!A13:G13",
    values: [["JEPI", "72.942", "", "", "", "", "$56.48"]]  // ❌ Empty strings delete formulas
})

Layer Classification for New Tickers

When adding new tickers, classify into the correct portfolio layer in Column S.

Do NOT hardcode layer assignments. Instead, read the current layer definitions from:

• Primary: fin-guru/data/spreadsheet-architecture.md → "Pattern-Based Layer Classification" section
• Fallback: Read existing Column S values from DataHub to learn current classification patterns

If a new ticker doesn't clearly match any layer pattern, set to "UNKNOWN - Manual Review Required" and alert the user for classification.

Safety Gates

STOP conditions (require user confirmation): 1. CSV has fewer tickers than sheet (possible sales) 2. Any quantity change > 10% 3. Any cost basis change > 20% 4. 3+ formula errors detected 5. Margin balance jumped > $5,000 (unintentional draw) 6. SPAXX discrepancy > $100 (cash mismatch between sheet and CSV)

FLAG conditions (alert user but proceed):

• SPAXX differs from "Settled cash" by $1-$100 (minor discrepancy)
• Pending Activity differs from "Net debit" by >$100

When STOPPED: Show clear diff table, ask user to confirm, proceed only after explicit approval.

When FLAGGED: Show the discrepancy, proceed with update but highlight in summary.

Google Sheets Integration

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

Agent Permissions

Builder (Write-enabled): Can update columns A, B, G; can add new rows; can apply layer classification; CANNOT modify formulas.

All Other Agents (Read-only): Market Researcher, Quant Analyst, Strategy Advisor — can read all data, cannot write, must defer to Builder for updates.

Reference Files

• Full Architecture: fin-guru/data/spreadsheet-architecture.md
• Quick Reference: fin-guru/data/spreadsheet-quick-ref.md
• User Profile: fin-guru/data/user-profile.yaml
• Formula Protection: See the formula-protection skill for sacred formula rules

Pre-Flight Checklist

Before syncing (SyncPortfolio):

• [ ] Positions CSV (Portfolio_Positions_*.csv) is latest by date in notebooks/updates/
• [ ] Balances CSV (Balances_for_Account_*.csv) is available and current in notebooks/updates/
• [ ] Both CSVs are from Fidelity (not M1 Finance or other broker)
• [ ] Google Sheets DataHub tab exists
• [ ] No pending manual edits in sheet (user should save first)
• [ ] Current portfolio value is known (for validation)

Files not in `notebooks/updates/` yet? Run IngestPositions first to move them from ~/Downloads/.

Both CSVs Required: Positions CSV alone is insufficient. Balances CSV provides:

• "Settled cash" → SPAXX value
• "Net debit" → Pending Activity and Margin Debt values

---

Skill Type: Domain (workflow guidance) Enforcement: BLOCK (data integrity critical) Priority: Critical

IngestPositions Workflow

Ingest Fidelity portfolio CSVs from Downloads into notebooks/updates/. Handles duplicate file detection, regular vs dividend view classification, and Balances file updates.

Critical Rules

• MOVE, never copy: Always use mv, NEVER cp. Files must be removed from ~/Downloads/ after ingestion. Leaving copies behind causes confusion on re-runs.
• Use `fd` for file discovery: zsh glob expansion fails if any pattern has zero matches, killing the entire command. Always use fd instead of ls with globs.

Triggers

• "ingest positions", "import positions", "bring in positions"
• "portfolio-sync" (chains: IngestPositions -> SyncPortfolio)
• User mentions downloading positions/balances from Fidelity

Step 1: Locate Source Files in Downloads

Account ID: Read from fin-guru/data/user-profile.yaml → accounts[].account_id at runtime. NEVER hardcode.

Scan ~/Downloads/ for all matching files. CRITICAL: Use fd (not ls with globs) because zsh kills the entire command if _any_ glob pattern has zero matches.

# Find all position and balance CSVs (including (1), (2) duplicates)
# MUST use fd — zsh glob expansion fails if Balances file doesn't exist
fd --type f 'Portfolio_Positions_.*\.csv$' ~/Downloads/ --max-depth 1
fd --type f 'Balances_for_Account_.*\.csv$' ~/Downloads/ --max-depth 1
fd --type f 'History_for_Account_.*\.csv$' ~/Downloads/ --max-depth 1

NEVER use these patterns (they fail silently in zsh):

# ❌ WRONG — zsh aborts entire command if any glob has no matches
ls -lt ~/Downloads/Portfolio_Positions_*.csv ~/Downloads/Balances_*.csv 2>/dev/null
# ❌ WRONG — same zsh glob issue
ls ~/Downloads/*.csv | head -10

Expected files (typical download session):

If no files found: Ask user if they've downloaded from Fidelity yet.

Step 2: Classify Regular vs Dividend View

Both position files download as Portfolio_Positions_MMM-DD-YYYY.csv. Distinguish by reading the header row (row 1):

# Read header of each positions file
head -1 "FILE_PATH"

Classification rules:

Quick check: If header contains Ex-date -> dividend view. Otherwise -> regular view.

Step 3: Move Regular View (No Rename Needed)

The regular view file is already date-tagged by Fidelity: Portfolio_Positions_MMM-DD-YYYY.csv

# Move regular view as-is (already properly named)
mv ~/Downloads/Portfolio_Positions_MMM-DD-YYYY.csv notebooks/updates/Portfolio_Positions_MMM-DD-YYYY.csv

If the regular view was the (1) tagged file:

# Remove the (1) tag when moving
mv "~/Downloads/Portfolio_Positions_MMM-DD-YYYY (1).csv" notebooks/updates/Portfolio_Positions_MMM-DD-YYYY.csv

Step 4: Move and Rename Dividend View

Rename the dividend view to follow the Dividend_Positions_MMM-DD-YYYY.csv convention:

# Extract date from the original filename (e.g., "Mar-06-2026" from "Portfolio_Positions_Mar-06-2026.csv")
# Rename to Dividend_Positions_MMM-DD-YYYY.csv
mv "~/Downloads/Portfolio_Positions_MMM-DD-YYYY.csv" notebooks/updates/Dividend_Positions_MMM-DD-YYYY.csv
# OR if it was the (1) file:
mv "~/Downloads/Portfolio_Positions_MMM-DD-YYYY (1).csv" notebooks/updates/Dividend_Positions_MMM-DD-YYYY.csv

Example:

mv "~/Downloads/Portfolio_Positions_Mar-06-2026 (1).csv" notebooks/updates/Dividend_Positions_Mar-06-2026.csv

Step 5: Move Balances File

Balances file overwrites the existing one (no date tag, single canonical file):

# Move balances (overwrites existing)
mv ~/Downloads/Balances_for_Account_{ACCOUNT_ID}.csv notebooks/updates/Balances_for_Account_{ACCOUNT_ID}.csv

Duplicate handling: If Balances_for_Account_{ACCOUNT_ID} (1).csv exists, it's a re-download. Use the newest one (highest number or no number = first download):

# If (1) exists, it's the newer download — use it, discard the older
mv "~/Downloads/Balances_for_Account_{ACCOUNT_ID} (1).csv" notebooks/updates/Balances_for_Account_{ACCOUNT_ID}.csv
# Clean up the older one if still present
rm -f ~/Downloads/Balances_for_Account_{ACCOUNT_ID}.csv 2>/dev/null

Step 6: Verify and Report

# Verify files landed correctly
ls -la notebooks/updates/Portfolio_Positions_*.csv | tail -3
ls -la notebooks/updates/Dividend_Positions_*.csv | tail -3
ls -la notebooks/updates/Balances_for_Account_*.csv

Generate ingestion report:

POSITION INGESTION COMPLETE - {date}
---

FILES MOVED:
  Regular View:  Portfolio_Positions_{date}.csv ✅
  Dividend View: Dividend_Positions_{date}.csv ✅
  Balances:      Balances_for_Account_{ACCOUNT_ID}.csv ✅

DOWNLOADS CLEANED:
  Removed {N} files from ~/Downloads/

NEXT STEPS:
  -> Run "portfolio-sync" to push positions to Google Sheets
  -> Run "sync dividends" to update Dividend Tracker
---

Edge Cases

Only one positions file downloaded

• Classify it (regular or dividend) and move accordingly
• Report which view is missing

Multiple dates in Downloads

• If Portfolio_Positions_Mar-05-2026.csv AND Portfolio_Positions_Mar-06-2026.csv exist:
• Process only the latest date by default
• Ask user if they want to import older files too

File already exists in notebooks/updates/

• If Portfolio_Positions_MMM-DD-YYYY.csv already exists in destination:
• Compare file sizes. If identical, skip with note.
• If different, overwrite (newer download wins)

No Balances file

• Proceed with positions only
• Warn: "Balances file not found in Downloads. SPAXX and Margin values won't update during sync."

Error Handling

File not found

No Fidelity CSV files found in ~/Downloads/.
Please download from Fidelity:
  1. Positions (regular view) — Portfolio tab → Download
  2. Positions (dividend view) — Portfolio tab → Dividend View → Download
  3. Balances — Balances tab → Download

Cannot classify view type

WARNING: Could not determine if this is the regular or dividend view.
Header row: {first 100 chars of header}
Please confirm: Is this the [regular] or [dividend] view?

---

Workflow Type: Local file management Estimated Duration: 5-10 seconds Dependencies: CSV files in ~/Downloads/ Chains to: SyncPortfolio (for Google Sheets push)

SyncPortfolio Workflow

Purpose: Read Fidelity CSV exports from notebooks/updates/, compare with Google Sheets DataHub, and sync position data while preserving sacred formulas.

---

Step 1: Pre-Flight Checks

Before importing CSV:

• [ ] Positions CSV (Portfolio_Positions_*.csv) is latest by date in notebooks/updates/
• [ ] Balances CSV (Balances_for_Account_*.csv) is available and current in notebooks/updates/
• [ ] Both CSVs are from Fidelity (not M1 Finance or other broker)

Files not in `notebooks/updates/`? Run IngestPositions workflow first.

---

Step 2: Read Latest Fidelity CSVs

Positions File

notebooks/updates/Portfolio_Positions_MMM-DD-YYYY.csv — find the latest by date in filename.

CSV has 17 columns. Extract these fields:

• Symbol (col 4) → maps to DataHub Column A
• Quantity (col 6) → maps to DataHub Column B
• Average Cost Basis (col 16) → maps to DataHub Column G
• Type (col 17) → "Margin" or "Cash" (same ticker may appear in both — combine quantities)

Combining Cash + Margin positions: When a ticker appears in both Margin and Cash rows, sum the quantities and use the weighted average cost basis.

Balances File

notebooks/updates/Balances_for_Account_{account_id}.csv

Key fields to extract:

• "Settled cash" → SPAXX row (DataHub Column L)
• "Net debit" → Pending Activity and Margin Debt
• "Account equity percentage" → Margin status

Margin Debt Logic:

IF "Account equity percentage" == 100% THEN
    Margin Debt = $0.00
ELSE
    Margin Debt = Total Account Value × (1 - Equity Percentage)
END

---

Step 3: Read Current Google Sheets DataHub

mcp__gdrive__sheets(operation: "readSheet", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!A1:S50"
})

Extract:

• Column A: Ticker
• Column B: Quantity
• Column G: Avg Cost Basis

---

Step 4: Compare and Identify Changes

Identify:

• ✅ NEW tickers: In CSV but not in sheet (additions)
• ✅ EXISTING tickers: In both (updates)
• ⚠️ MISSING tickers: In sheet but not in CSV (possible sales)

---

Step 5: Safety Checks (STOP if triggered)

STOP conditions (require user confirmation): 1. CSV has fewer tickers than sheet (possible sales) 2. Any quantity change > 10% 3. Any cost basis change > 20% 4. 3+ formula errors detected 5. Margin balance jumped > $5,000 6. SPAXX discrepancy > $100

When STOPPED:

• Show clear diff table
• Ask user to confirm changes
• Proceed only after explicit approval

Transaction History Cross-Check (Optional)

When large quantity changes (>10%) are detected, cross-reference with notebooks/transactions/History_for_Account_{account_id}.csv:

For each ticker with >10% change:
1. Read transaction history for that ticker
2. Sum recent BUY transactions since last sync
3. Verify: Current CSV Qty ≈ Previous Sheet Qty + Net Transactions
4. If mismatch > 1 share, FLAG for manual review

Skip cross-check if: small changes (<10%), user explicitly confirms, or transaction file unavailable.

---

Step 6: Update Position Data

For EXISTING Tickers (update Columns B and G ONLY):

// Update quantity (Column B only)
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!B{ROW}:B{ROW}",
    values: [["{QUANTITY}"]]
})

// Update cost basis (Column G only)
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!G{ROW}:G{ROW}",
    values: [["{COST_BASIS}"]]
})

NEVER touch Columns C-F — these contain formulas.

For NEW Tickers: 1. Add new row with 3 separate calls for Columns A, B, G 2. Read layer definitions from fin-guru/data/spreadsheet-architecture.md → "Pattern-Based Layer Classification" 3. Apply classification to Column S 4. If ticker doesn't match any pattern, set "UNKNOWN - Manual Review Required" and alert user 5. Column C (Last Price) will auto-populate from GOOGLEFINANCE formula

Log Addition:

Added {TICKER} - {SHARES} shares @ ${AVG_COST} - Layer: {LAYER}

---

Step 7: Update Cash & Margin Rows (MANDATORY)

This step is NOT optional. SPAXX and Margin must be updated every sync.

SPAXX (Row 37, Column L):

// From "Settled cash" in Balances CSV
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L37:L37",
    values: [[" $ -   "]]  // or formatted value if > 0
})

Pending Activity (Row 38, Column L):

// From "Net debit" in Balances CSV (negative value)
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L38:L38",
    values: [[" $ (7,822.71)"]]  // format: " $ (X,XXX.XX)" for negative
})

Margin Debt (Row 39, Column L):

// ABS of "Net debit"
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L39:L39",
    values: [[" $ 7,822.71 "]]  // format: " $ X,XXX.XX " positive
})

---

Step 8: Post-Update Validation

Verify:

• [ ] Formulas still functional (no new #N/A errors)
• [ ] SPAXX reflects "Settled cash" from Balances CSV
• [ ] Pending Activity reflects "Net debit" from Balances CSV
• [ ] Margin Debt = ABS(Net debit)
• [ ] Total account value approximately matches Fidelity total

---

Step 9: Log Summary

Output update summary:

✅ Updated {N} positions (quantity + cost basis)
✅ Added {N} new tickers: {LIST}
✅ SPAXX updated: ${VALUE}
✅ Pending Activity: ${VALUE}
✅ Margin debt: ${VALUE}
✅ No formula errors detected
✅ Portfolio value: ${VALUE} (matches Fidelity)

---

Done

Portfolio sync complete. DataHub now matches Fidelity CSV.