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 that have been 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, and 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. That included the business-analysis half of the job, understanding exactly what the downstream process needed each workbook to contain, and not just the scripting half.

• Source pages were inconsistent, so 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 ran 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 was focused where it mattered, on 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