Retirement Syncing

CSV Formats

Vanguard OFX Format (OfxDownload.csv)

Account Number,Investment Name,Symbol,Shares,Share Price,Total Value,
39321600,VANGUARD S&P 500 INDEX ETF,VOO,18.1817,629.3,11441.74,

Key Fields:

• Column 3: Symbol
• Column 4: Shares (quantity)

Fidelity 401k Format (Portfolio_Positions_*.csv)

Account Number,Account Name,Symbol,Description,Quantity,Last Price,...
86689,{employer_name} 401(K) PLAN,FGCKX,FID GROWTH CO K,4.447,$50.04,...

Key Fields:

• Column 3: Symbol
• Column 5: Quantity

DataHub Target Location

Spreadsheet ID: Read from fin-guru/data/user-profile.yaml

Retirement Section: Rows 46-62 (after the "RETIREMENT ACCOUNTS (VANGUARD)" header at row 45)

Core Workflow

1. Read All CSV Files

# Read Vanguard files
vanguard_1 = read_csv("notebooks/retirement-accounts/OfxDownload.csv")
vanguard_2 = read_csv("notebooks/retirement-accounts/OfxDownload (1).csv")

# Read latest Fidelity file (by date in filename)
fidelity = read_csv("notebooks/retirement-accounts/Portfolio_Positions_*.csv")

2. Aggregate Holdings by Ticker

Since the same ticker can appear in multiple accounts, SUM all quantities:

holdings = {}
for file in [vanguard_1, vanguard_2, fidelity]:
    for row in file:
        ticker = row['Symbol']
        shares = float(row['Shares'] or row['Quantity'])
        holdings[ticker] = holdings.get(ticker, 0) + shares

Expected Aggregations:

• VOO: Sum across accounts (IRA + Brokerage)
• VUG: Sum across accounts
• PLTR: Sum across accounts (53527429 + 50580939)
• SCHG: Sum across accounts
• VMFXX: Sum across accounts (all money market)
• VTSAX: Sum across accounts

3. Update DataHub Column B Only

WRITABLE: Column B (Quantity) only

DO NOT TOUCH:

• Column A (Ticker) - already set
• Column C onwards - formulas

// Update VOO quantity (Row 46)
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!B46:B46",
    values: [["214.7947"]]  // Aggregated quantity
})

4. Update Pattern

Loop through each retirement ticker and update Column B:

Safety Checks

Before updating:

• Verify all 3 CSV files exist in notebooks/retirement-accounts/
• Confirm row mapping matches expected tickers
• Log any new tickers not in current sheet

Large Change Warning (>20%):

• If any quantity changes more than 20%, show diff and ask for confirmation

Example Execution

// Step 1: Read CSVs and aggregate
const holdings = aggregateFromCSVs();

// Step 2: Update each ticker's quantity
const updates = [
    { range: "DataHub!B46:B46", values: [[holdings.VOO.toFixed(4)]] },
    { range: "DataHub!B47:B47", values: [[holdings.VUG.toFixed(4)]] },
    { range: "DataHub!B48:B48", values: [[holdings.VTSAX.toFixed(3)]] },
    // ... etc
];

for (const update of updates) {
    mcp__gdrive__sheets(operation: "updateCells", params: {
        spreadsheetId: SPREADSHEET_ID,
        ...update
    });
}

// Step 3: Log summary
console.log("Updated 12 retirement positions");

Post-Update Validation

Verify:

• All quantities updated correctly
• Formulas in columns C+ still working
• Total retirement value approximately matches sum of CSV totals
• No formula errors introduced

Log Summary:

Updated 12 retirement positions:
- VOO: 214.7947 shares
- VUG: 13.0652 shares
- VTSAX: 228.462 shares
...
Total Retirement Value: ~$387,806

Critical Rules

WRITABLE Column

• Column B: Quantity ONLY

DO NOT TOUCH

• Column A: Tickers (pre-set)
• Columns C-S: All formulas

Row Mapping

Retirement section starts at row 46 (after header at row 45). Rows 46-62 are reserved for retirement holdings.

Trigger Keywords

• "sync retirement"
• "update retirement"
• "retirement accounts"
• "vanguard sync"
• "401k update"
• "IRA sync"
• "retirement quantities"

Skill Type: Domain (workflow guidance) Enforcement: SUGGEST Priority: Medium Line Count: < 200