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, E*TRADE, 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 this workflow, output this notification:

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

Workflow	Trigger	File
SyncPortfolio	"sync portfolio", "portfolio-sync", "import fidelity"	workflows/SyncPortfolio.md

Examples

Example 1: Sync after downloading new Fidelity CSV

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 2: 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 3: Import new Fidelity export

User: "import fidelity CSV"
-> Locates latest CSV files by date
-> Runs safety checks (position count, large changes)
-> Syncs all positions and cash/margin values
-> Logs update summary

Core Workflow

1. Read Latest Fidelity CSVs

Positions File: notebooks/updates/Portfolio_Positions_MMM-DD-YYYY.csv

Key Fields to Extract:

• Symbol → Column A: Ticker
• Quantity → Column B: Quantity
• Average Cost Basis → Column G: Avg Cost Basis

CSV Format:

Symbol,Quantity,Last Price,Current Value,Total Gain/Loss Dollar,...,Average Cost Basis
TSLA,74,$445.47,$32964.78,+$15634.71,...,$234.19
PLTR,369.746,$188.90,$69845.01,+$60235.59,...,$25.99

Balances File: notebooks/updates/Balances_for_Account_{account_id}.csv

Key Fields to Extract for Cash & Margin:

• "Settled cash" → Use for 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

⚠️ IMPORTANT: 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)

Margin Debt Logic:

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

2. Compare with Current Sheet

Read from Google Sheets DataHub:

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

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)

3. Safety Checks (STOP if triggered)

Position Mismatches:

• If CSV has fewer tickers than sheet, STOP and alert user
• User must confirm sales/transfers before proceeding

Large Quantity Changes (>10%):

• If any ticker quantity changes more than 10%, STOP and show diff
• Example: TSLA goes from 74 → 85 shares (+14.9%) = ALERT
• User must confirm intentional trades

Cost Basis Changes (>20%):

• If average cost basis changes more than 20%, FLAG for review
• Possible corporate action (split, merger, dividend reinvestment)
• User should verify this is correct

Formula Validation:

• Scan Columns C-S for #N/A, #DIV/0!, #REF! errors before updating
• If 3+ errors detected, STOP and suggest formula repair first

3.5 Transaction History Cross-Check (Optional Validation)

Transactions File: notebooks/transactions/History_for_Account_{account_id}.csv

When large quantity changes (>10%) are detected, cross-reference with the transaction history to validate:

Key Fields to Check:

• Run Date → Date of transaction
• Action → BUY, SELL, DIVIDEND, etc.
• Symbol → Ticker symbol
• Quantity → Shares bought/sold
• Amount → Dollar value of transaction

Validation Logic:

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

Example Cross-Check:

JEPI shows +18.9 shares (90.82 → 109.72)
Transaction History shows:
  - Dec 15: BUY JEPI 10 shares
  - Dec 18: BUY JEPI 8.9 shares (DRIP)
  Total: +18.9 shares ✅ VERIFIED

When to Skip:

• Small changes (<10%) - trust CSV
• User explicitly confirms changes
• Transaction file unavailable or outdated

4. Update Operations

For EXISTING Tickers:

Update Column B (Quantity) = CSV Quantity
Update Column G (Avg Cost Basis) = CSV Average Cost Basis

DO NOT TOUCH:

• Column C (Last Price) - Google Finance formula auto-updates
• Columns D-F ($ Change, % Change, Volume) - Formulas/Alpha Vantage
• Columns H-M (Gains/Losses) - Calculated formulas
• Columns N-S (Ranges, dividends, layer) - Formulas or manual classification

🚨 CRITICAL: NEVER PASS EMPTY STRINGS TO FORMULA COLUMNS

• WRONG: Updating entire row range with empty strings ("") will OVERWRITE formulas
• RIGHT: Update ONLY columns A, B, G using individual cell ranges
• Empty strings ("") will DELETE formulas in columns C-F - this BREAKS the sheet

For NEW Tickers:

1. Add new row
2. Set Column A (Ticker) = CSV Symbol
3. Set Column B (Quantity) = CSV Quantity
4. Set Column G (Avg Cost Basis) = CSV Average Cost Basis
5. Apply pattern-based layer classification to Column S:
   - If ticker in [JEPI, JEPQ, SPYI, QQQI, CLM, CRF, etc.] → "Layer 2 - Dividend"
   - If ticker in [SQQQ] → "Layer 3 - Hedge"
   - If ticker in [TSLA, PLTR, NVDA, COIN, MSTR, SOFI] → "Layer 1 - Growth"
   - If ticker in [VOO, VTI, FZROX, FNILX] → "Layer 1 - Index"
6. Column C (Last Price) will auto-populate from GOOGLEFINANCE formula

Log Addition:

Added {TICKER} - {SHARES} shares @ ${AVG_COST} - Layer: {LAYER}
Example: Added MSTY - 87.9 shares @ $11.94 - Layer: Layer 2 - Dividend

5. Update Cash & Margin Rows (MANDATORY)

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

SPAXX (Cash Position) - Row 37, Column L:

1. Read "Settled cash" from Balances CSV
2. If "Settled cash" = 0 → Update DataHub!L37 with " $ -   " (zero cash)
3. If "Settled cash" > 0 → Update DataHub!L37 with formatted value
4. ⚠️ SAFETY CHECK: If current sheet SPAXX differs from CSV by >$100, FLAG for user

Pending Activity - Row 38, Column L:

1. Read "Net debit" from Balances CSV (will be negative if margin used)
2. Update DataHub!L38 with this value (format: " $ (X,XXX.XX)" for negative)

Margin Debt - Row 39, Column L:

1. Read "Net debit" from Balances CSV
2. Convert to positive: Margin Debt = ABS(Net debit)
3. Update DataHub!L39 with positive value (format: " $ X,XXX.XX ")
4. If Net debit = 0 → Update with " $ -   "

Example:

// Cash position from Balances CSV "Settled cash" = 0
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L37:L37",
    values: [[" $ -   "]]
})

// Pending Activity from "Net debit" = -7822.71
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L38:L38",
    values: [[" $ (7,822.71)"]]
})

// Margin debt = ABS(-7822.71) = 7822.71
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!L39:L39",
    values: [[" $ 7,822.71 "]]
})

6. Post-Update Validation

Verify:

• Google Finance formulas auto-populated prices for new tickers
• Formulas still functional (no new #N/A errors)
• Row count matches expected additions
• Total account value approximately matches Fidelity total
• SPAXX reflects "Settled cash" from Balances CSV (MANDATORY)
• Pending Activity reflects "Net debit" from Balances CSV (MANDATORY)
• Margin Debt = ABS(Net debit) (MANDATORY)

Log Update Summary:

✅ Updated 25 positions (quantity + cost basis)
✅ Added 3 new tickers: MSTY, YMAX, AMZY
✅ SPAXX updated: $0 (Settled cash = 0)
✅ Pending Activity: -$7,822.71 (Net debit)
✅ Margin debt: $7,822.71 (ABS of Net debit)
✅ No formula errors detected
✅ Portfolio value: $228,809.41 (matches Fidelity)

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)

Pattern-Based Layer Classification

Use these patterns to auto-classify new tickers in Column S:

Layer 2 - Dividend (Income funds):

• JEPI, JEPQ, SPYI, QQQI, QQQY
• CLM, CRF, ETY, ETV, BDJ, UTG, BST
• MSTY, YMAX, AMZY
• Any ticker with "yield" or "income" in description

Layer 3 - Hedge (Downside protection):

• SQQQ (ProShares UltraPro Short QQQ)

Layer 1 - Growth (Core holdings):

• TSLA, PLTR, NVDA, AAPL, GOOGL
• COIN, MSTR

---

Content truncated.