Back to Details

retirement-syncing

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

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).
将Vanguard和Fidelity的退休账户CSV导出文件安全导入Google Sheets DataHub的退休账户区域，仅更新持仓数量（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
在以下场景中使用该技能：
  • 从
    notebooks/retirement-accounts/
    同步退休账户持仓
  • 用户提及："sync retirement"「同步退休账户」、"update retirement"「更新退休账户」、"vanguard sync"「Vanguard同步」、"401k update"「401k更新」、"IRA sync"「IRA同步」
  • 处理
    notebooks/retirement-accounts/
    目录下的文件

Source Files

源文件

Location: 
notebooks/retirement-accounts/
FileSourceContents
OfxDownload.csv
Vanguard IRAsAccount 39321600 & 35407271 holdings
OfxDownload (1).csv
Vanguard BrokerageAccount 53527429 & 50580939 holdings
Portfolio_Positions_*.csv
Fidelity 401(k){employer_name} 401(k) Plan holdings
存储位置：
notebooks/retirement-accounts/
文件来源内容
OfxDownload.csv
Vanguard IRAs账户39321600 & 35407271的持仓数据
OfxDownload (1).csv
Vanguard Brokerage账户53527429 & 50580939的持仓数据
Portfolio_Positions_*.csv
Fidelity 401(k){employer_name} 401(k)计划持仓数据

CSV Formats

CSV格式

Vanguard OFX Format (OfxDownload.csv)

Vanguard OFX格式（OfxDownload.csv）

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)
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,
关键字段：
  • 第3列：Symbol（代码）
  • 第4列：Shares（持仓数量）

Fidelity 401k Format (Portfolio_Positions_*.csv)

Fidelity 401k格式（Portfolio_Positions_*.csv）

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
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,...
关键字段：
  • 第3列：Symbol（代码）
  • 第5列：Quantity（持仓数量）

DataHub Target Location

DataHub目标位置

Spreadsheet ID: Read from 
fin-guru/data/user-profile.yaml
Retirement Section: Rows 46-62 (after the "RETIREMENT ACCOUNTS (VANGUARD)" header at row 45)
RowTickerDescription
46VOOVanguard S&P 500 ETF
47VUGVanguard Growth ETF
48VTSAXVanguard Total Stock Market
49SCHGSchwab US Large-Cap Growth
50PLTRPalantir
51NVDANVIDIA
52TSLATesla
53VBVanguard Small-Cap ETF
54ARKKARK Innovation
55VMFXXVanguard Money Market
56FGCKXFidelity Growth Company K
57FXAIXFidelity 500 Index
58-62ReservedFuture holdings
表格ID：从
fin-guru/data/user-profile.yaml
读取
退休账户区域：第46-62行（位于第45行的"RETIREMENT ACCOUNTS (VANGUARD)"标题之后）
行号代码说明
46VOOVanguard S&P 500 ETF
47VUGVanguard Growth ETF
48VTSAXVanguard Total Stock Market
49SCHGSchwab US Large-Cap Growth
50PLTRPalantir
51NVDANVIDIA
52TSLATesla
53VBVanguard Small-Cap ETF
54ARKKARK Innovation
55VMFXXVanguard Money Market
56FGCKXFidelity Growth Company K
57FXAIXFidelity 500 Index
58-62预留未来持仓

Core Workflow

核心工作流

1. Read All CSV Files

1. 读取所有CSV文件

python
undefined
python
undefined

Read Vanguard files

Read Vanguard files

vanguard_1 = read_csv("notebooks/retirement-accounts/OfxDownload.csv") vanguard_2 = read_csv("notebooks/retirement-accounts/OfxDownload (1).csv")
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)

Read latest Fidelity file (by date in filename)

fidelity = read_csv("notebooks/retirement-accounts/Portfolio_Positions_*.csv")
undefined
fidelity = read_csv("notebooks/retirement-accounts/Portfolio_Positions_*.csv")
undefined

2. Aggregate Holdings by Ticker

2. 按代码聚合持仓

Since the same ticker can appear in multiple accounts, SUM all quantities:
python
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
由于同一代码可能出现在多个账户中，需求和所有持仓数量：
python
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
预期聚合规则：
  • VOO：跨账户求和（IRA + 经纪账户）
  • VUG：跨账户求和
  • PLTR：跨账户求和（53527429 + 50580939）
  • SCHG：跨账户求和
  • VMFXX：跨账户求和（所有货币市场账户）
  • VTSAX：跨账户求和

3. Update DataHub Column B Only

3. 仅更新DataHub的B列

WRITABLE: Column B (Quantity) only
DO NOT TOUCH:
  • Column A (Ticker) - already set
  • Column C onwards - formulas
javascript
// Update VOO quantity (Row 46)
mcp__gdrive__sheets(operation: "updateCells", params: {
    spreadsheetId: SPREADSHEET_ID,
    range: "DataHub!B46:B46",
    values: [["214.7947"]]  // Aggregated quantity
})
可编辑区域：仅B列（持仓数量）
禁止修改：
  • A列（代码）- 已预设
  • C列及以后的列 - 包含公式
javascript
// 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

4. 更新规则

Loop through each retirement ticker and update Column B:
TickerRangeAggregation
VOOB4618.1817 + 196.613 = 214.7947
VUGB4710.9488 + 2.1164 = 13.0652
VTSAXB48126.336 + 102.126 = 228.462
SCHGB49100 + 6 = 106
PLTRB5025 + 42 = 67
NVDAB51150
TSLAB5258
VBB5320
ARKKB5416.13
VMFXXB552.94 + 0.57 + 179.92 + 428.42 = 611.85
FGCKXB564.447
FXAIXB573.705
遍历每个退休账户代码并更新B列：
代码单元格范围聚合结果
VOOB4618.1817 + 196.613 = 214.7947
VUGB4710.9488 + 2.1164 = 13.0652
VTSAXB48126.336 + 102.126 = 228.462
SCHGB49100 + 6 = 106
PLTRB5025 + 42 = 67
NVDAB51150
TSLAB5258
VBB5320
ARKKB5416.13
VMFXXB552.94 + 0.57 + 179.92 + 428.42 = 611.85
FGCKXB564.447
FXAIXB573.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
更新前校验：
  • 确认
    notebooks/retirement-accounts/
    目录下存在全部3个CSV文件
  • 确认行映射与预期代码一致
  • 记录表格中未包含的新代码
大额变动警告（变动>20%）：
  • 若任一持仓数量变动超过20%，显示差异并请求确认

Example Execution

执行示例

javascript
// 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");
javascript
// 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
需验证：
  • 所有持仓数量更新正确
  • C列及以后的公式仍正常运行
  • 退休账户总价值与CSV文件的总价值大致匹配
  • 未引入公式错误
日志摘要示例：
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
  • B列：仅持仓数量

DO NOT TOUCH

禁止修改

  • Column A: Tickers (pre-set)
  • Columns C-S: All formulas
  • A列：代码（已预设）
  • C-S列：所有公式

Row Mapping

行映射规则

Retirement section starts at row 46 (after header at row 45). Rows 46-62 are reserved for retirement holdings.
退休账户区域从第46行开始（位于第45行的标题之后）。第46-62行预留为退休账户持仓区域。

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
  • "sync retirement"「同步退休账户」
  • "update retirement"「更新退休账户」
  • "retirement accounts"「退休账户」
  • "vanguard sync"「Vanguard同步」
  • "401k update"「401k更新」
  • "IRA sync"「IRA同步」
  • "retirement quantities"「退休账户持仓数量」
技能类型：领域类（工作流指引） 执行规则：建议执行 优先级：中等 代码行数：< 200行