PSLO Automation System

An institutional research office needed program student learning outcome (PSLO) information collected from 300+ program websites and delivered as 300+ individual, consistently formatted Excel workbooks. The existing plan was months of manual copy-paste work, likely with temporary staffing. I built a Python pipeline that automated the collection, structuring, workbook generation, and QA — saving roughly two months of manual effort in a single cycle.

300+

program websites processed

300+

Excel workbooks generated

~2 months

manual effort saved

Program learning outcomes live on public program webpages, maintained by many hands over many years — which means inconsistent page structures, inconsistent formatting, and no machine-readable source. The institutional process required this information gathered into a separate, per-program Excel workbook with a specific structure, for every program, on a deadline.

Done by hand, this is weeks of a person's time spent copying text between a browser and Excel — tedious, error-prone, and expensive. The anticipated solution was temporary staffing. That was the moment to ask the automation question: is this actually a people problem, or a pipeline problem?

I identified the automation opportunity, scoped the requirements with the process owners, built the pipeline, validated the output, and handled the exceptions. This included the business-analysis half — understanding exactly what the downstream process needed each workbook to contain — not just the scripting half.

• Source pages were inconsistent — the pipeline had to be robust to structural variation and fail loudly, not silently, on pages it couldn't parse.
• Output workbooks had to match the expected format exactly; downstream users would open them in Excel, not in a database.
• A hard institutional deadline: the automation had to beat the manual alternative in calendar time, not just effort.
• Accuracy standards of an IR office — a wrong outcome statement attached to a program is worse than a blank one.

The pipeline has four stages: collect page content from the program URL inventory, extract and structure the outcome information, generate per-program workbooks with openpyxl, and run QA checks comparing structured output back against source content. Programs that failed automated parsing dropped into a short manual-review queue — automating 95% and routing the rest is how the deadline was met.

for program in structured_programs:
    wb = build_workbook(template)
    sheet = wb["PSLOs"]
    sheet["B2"] = program.name
    for row, outcome in enumerate(program.outcomes, start=OUTCOME_START_ROW):
        sheet.cell(row=row, column=2, value=outcome.text)
        sheet.cell(row=row, column=3, value=outcome.source_url)
    validate_workbook(wb, program)   # raises on structural mismatch
    wb.save(out_dir / f"{program.code}.xlsx")

• Every extracted outcome kept a pointer to its source page for spot-check auditing.
• Structural validation on every generated workbook before save — missing sections fail the run for that program instead of shipping a bad file.
• Coverage reconciliation: the set of generated workbooks was checked against the full program inventory, so nothing fell through silently.
• Human review focused where it mattered — the exception queue — instead of spread thin across 300+ programs.

300+ validated workbooks delivered, roughly two months of manual effort avoided, and the anticipated temporary staffing need removed or reduced. Just as importantly, the process is now repeatable: the next cycle starts from a pipeline, not from a blank spreadsheet and a webpage.

• Source-page inconsistency means some manual review will always remain; the win is shrinking it, not pretending it's zero.
• The longer-term fix is upstream: a structured source of record for outcomes, so future cycles are exports rather than extractions. That's a systems recommendation, not just a script.

• Python, web automation, and data extraction against inconsistent sources
• Excel automation with pandas and openpyxl
• Requirements analysis with process owners; validation and QA design
• Process improvement framing: recognizing a staffing plan as an automation opportunity