aojdevstudio/retirement-syncing

Retirement Account Syncing

Purpose

Safely import Vanguard and Fidelity retirement account CSV exports into the Google Sheets DataHub retirement section, updating only quantities (Column B).

When to Use

Use this skill when:

• Syncing retirement account positions from notebooks/retirement-accounts/
• User mentions: "sync retirement", "update retirement", "vanguard sync", "401k update", "IRA sync"
• Working with files in notebooks/retirement-accounts/ directory

Source Files

Location: notebooks/retirement-accounts/

| File | Source | Contents | |------|--------|----------| | OfxDownload.csv | Vanguard IRAs | Account 39321600 & 35407271 holdings | | OfxDownload (1).csv | Vanguard Brokerage | Account 53527429 & 50580939 holdings | | Portfolio_Positions_*.csv | Fidelity 401(k) | {employer_name} 401(k) Plan holdings |

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)

| Row | Ticker | Description | |-----|--------|-------------| | 46 | VOO | Vanguard S&P 500 ETF | | 47 | VUG | Vanguard Growth ETF | | 48 | VTSAX | Vanguard Total Stock Market | | 49 | SCHG | Schwab US Large-Cap Growth | | 50 | PLTR | Palantir | | 51 | NVDA | NVIDIA | | 52 | TSLA | Tesla | | 53 | VB | Vanguard Small-Cap ETF | | 54 | ARKK | ARK Innovation | | 55 | VMFXX | Vanguard Money Market | | 56 | FGCKX | Fidelity Growth Company K | | 57 | FXAIX | Fidelity 500 Index | | 58-62 | Reserved | Future holdings |

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:

| Ticker | Range | Aggregation | |--------|-------|-------------| | VOO | B46 | 18.1817 + 196.613 = 214.7947 | | VUG | B47 | 10.9488 + 2.1164 = 13.0652 | | VTSAX | B48 | 126.336 + 102.126 = 228.462 | | SCHG | B49 | 100 + 6 = 106 | | PLTR | B50 | 25 + 42 = 67 | | NVDA | B51 | 150 | | TSLA | B52 | 58 | | VB | B53 | 20 | | ARKK | B54 | 16.13 | | VMFXX | B55 | 2.94 + 0.57 + 179.92 + 428.42 = 611.85 | | FGCKX | B56 | 4.447 | | FXAIX | B57 | 3.705 |

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