Institution Spreadsheet Update

Institution Spreadsheet Update

Update institution data in Excel/CSV files, highlight changes in red font, and deliver a PDF change report.

Quick start

1. Confirm input: user-selected .csv / .xlsx path(s). If multiple files, process each independently.
2. Read schema: first row = headers. Identify key column (school_id preferred; else school_name_en / school_name_zh).
3. Research & update: for each institution row, verify every non-metadata column against official sources. See reference.md.
4. Build changes JSON (see format below). Only include cells whose normalized value actually changed.
5. Apply & report:

   python3 -m pip install openpyxl fpdf2
   python ~/.cursor/skills/institution-spreadsheet-update/scripts/apply_updates_and_report.py \
     --input "/path/to/table.csv" \
     --changes "/path/to/changes.json" \
     --output-dir "/same/folder/as/input"
   

6. Deliver to user:
   • {stem}_updated_{YYYYMMDD}.xlsx — full table, changed cells in red
   • {stem}_updated_{YYYYMMDD}.csv — plain CSV mirror (no color)
   • {stem}_update_report_{YYYYMMDD}.pdf — Chinese change summary
   • {stem}_changes_{YYYYMMDD}.json — machine-readable diff (saved alongside)

Workflow checklist

- [ ] Input file(s) confirmed
- [ ] Baseline captured (current cell values)
- [ ] Each school researched from official / primary sources
- [ ] changes.json written
- [ ] apply_updates_and_report.py executed successfully
- [ ] PDF + highlighted XLSX paths reported to user

Changes JSON format

Write an array of per-school objects:

[
  {
    "school_id": "stanford",
    "school_name_en": "Stanford University",
    "school_name_zh": "斯坦福大学",
    "changes": [
      {
        "field": "test_policy",
        "label_zh": "标化政策",
        "old": "optional",
        "new": "required"
      }
    ]
  }
]

Rules:

• field must match the spreadsheet header exactly.
• label_zh = human-readable column name for the PDF (use reference.md mapping when available).
• Treat empty, —, N/A, whitespace-only as equivalent when comparing.
• Always add/update last_updated → today's date (YYYY-MM-DD) when any field changes.
• Do not list unchanged fields.

Research rules

• Primary sources first: official admissions / international-student pages.
• One school at a time — do not batch-guess across schools.
• Record evidence in source_url and source_notes when those columns exist.
• Put concise Chinese context in notes_zh when policy wording is nuanced.
• If a field cannot be verified, leave the old value and note it under ## 未能核实 in the PDF (via unverified array in JSON — see script --changes extended format).

Extended changes file (optional):

{
  "checked_at": "2026-06-17",
  "schools": [ /* same as above */ ],
  "unverified": [
    {"school_id": "foo", "field": "toefl_min_ibt", "reason": "官网未标明最低分"}
  ]
}

Output conventions

All outputs go in --output-dir (default: same folder as input).

PDF report structure

The script renders:

1. 封面摘要 — 检查日期、文件路径、院校总数、变更院校数、变更字段总数
2. 变更详情 — per school: 中英校名 + table (字段 | 旧值 | 新值)
3. 变更解读 — agent-written summary_zh bullets (pass via --summary-md or embed in changes JSON as "summary_zh": ["..."])
4. 未能核实 — optional unverified list

After the script runs, always add 2–5 bullets of plain-language summary_zh interpretation (policy trends, deadline shifts, testing changes) before handing off to the user.

Multiple files

When the user selects several spreadsheets (e.g. top30.csv, lac_top20.csv):

1. Process each file separately (own changes JSON + outputs).
2. Optionally merge PDFs or provide one combined --summary-md covering cross-file trends.

Metadata columns (auto-handling)

These columns are updated by the pipeline but not highlighted in red:

• last_updated — set to check date when any other field changes

Dependencies

python3 -m pip install openpyxl fpdf2

PDF Chinese rendering uses macOS PingFang.ttc or STHeiti Light.ttc when available; falls back to bundled font path via --font.

Additional resources

• Column labels & research priorities: reference.md