Automated handbook change-tracking system for the Department of Management & Marketing, University of Melbourne. Scrapes and parses the University Handbook into structured Excel tables, feeding a proposal workflow for subject and curriculum changes.
- Overview
- How It Works
- Quick Start
- Tech Stack
- Repository Structure
- Workbook Sheets
- Documentation Guide
The system downloads data for all department subjects from the handbook, parses each section (Overview, Eligibility, Assessment, Dates & Times, Further Information) into structured sheets, and provides auto-lookup formulas so users can track proposed changes against current handbook data. An exported standalone tracker workbook is generated for end users to log and review proposals without needing the full pipeline.
Note: Refreshing handbook data currently requires Windows + University VPN. See User Guide for details. The exported tracker and all UX features work on any platform without VPN.
- SubjectList — Populated via Power Automate (reading enrolment tracker from SharePoint) + Office Script. Contains all department subjects with study period, status, and program columns.
- Power Query — Fetches the
/printpage for each subject. Office Script (parseAllSubjectsPrint) extracts section HTML columns. - VBA Parsers — Parse the section HTML into structured sheets: Overview, Eligibility, Assessment, Dates & Times, Intensive & Quota (Additional Delivery), Further Info.
- Tracker — User enters Subject Code + selects Change Type → cascading dropdown filters Entry Field → auto-lookup populates "Current Details" column from parsed sheets via
GetHandbookData/GetHandbookEntryUDFs. - Export — Generates a standalone
.xlsmtracker workbook with all data, formulas, and event handlers intact.
- Open the Handbook Tracker System.xlsm workbook on SharePoint
- Go to the Dashboard sheet
- Fill in the required parameters (Year, Enrolment Tracker filename, Program List, Notification Email)
- Click the Click to Start button to run the full pipeline
- Wait for all steps to complete (~10–30 minutes depending on subject count)
- The exported tracker workbook will be saved to the same SharePoint folder
For detailed instructions, see the User Guide.
| Component | Technology | Purpose |
|---|---|---|
| Data fetching | Power Query (M language) | Fetches /print page per subject |
| HTML section extraction | Office Script (TypeScript) | Splits raw HTML into section columns |
| Data parsing | VBA (Excel macros) | Parses section HTML into structured fields |
| Subject list | Office Script + Power Automate | Populates subject list from the Enrolment Tracker |
| User interface | Excel (.xlsm) | Tracker table, auto-lookup, dashboards |
handbook-tracker-system/
├── README.md ← You are here
├── LICENSE
├── .gitignore
├── docs/
│ ├── CHANGELOG.md ← Version history and release notes
│ ├── DESIGN_DOC.md ← Architecture & module reference
│ ├── DEVELOPER_GUIDE.md ← Tables, columns, file dependencies, export pipeline
│ ├── USER_GUIDE.md ← End-user instructions, tracker columns, formulas
│ ├── REVIEW.md ← PM review items & backlog
├── src/
│ ├── office scripts/
│ │ ├── handbookTrackerSubjectList.osts # Parse enrolment tracker → SubjectList
│ │ ├── parseAllSubjectsPrint.osts # Extract section HTML from /print pages
│ │ └── clearTable.osts # Clear a named table (utility)
│ ├── VBA modules/
│ │ ├── Integration.bas # Orchestrator — RunFullRefresh, RefreshData, Reparse
│ │ ├── HTMLQuery.bas # Power Query refresh + Mac PA fallback
│ │ ├── SubjectListRefresh.bas # Trigger Subject List Power Automate flow
│ │ ├── OverviewData.bas # Parse Overview + shared HTML utilities
│ │ ├── EligibilityData.bas # Parse Eligibility section
│ │ ├── AssessmentData.bas # Parse Assessment section
│ │ ├── DatesTimesData.bas # Parse Dates & Times section
│ │ ├── AdditionalDeliveryData.bas # Parse Intensive & Quota section
│ │ ├── FurtherInfoData.bas # Parse Further Info section
│ │ ├── TrackerLookup.bas # UDFs, RefreshDropdownRanges, prefill logic
│ │ ├── TrackerSheetEvents # Sheet module — cascading dropdowns, auto-fill
│ │ ├── ExportHandbookTracker.bas # Export standalone tracker workbook
│ │ ├── HandbookRefresh.bas # Refresh exported tracker from template
│ │ ├── MacCompat.bas # Cross-platform Dictionary + HTTP + regex helpers
│ │ └── ImportModules.bas # Dev utility — bulk-import .bas files
│ ├── power query/
│ │ └── AllSubjectsPrint.pq # Fetch /print page per subject
│ └── power automate flows/
│ └── handbook-query-print.json # Mac fallback: cloud HTML scraping
└── tests/
└── test-cases.md
| Sheet | Purpose |
|---|---|
| Dashboard | Year parameter, Refresh button, progress bar, timing |
| Handbook Changes Template | Change-tracking table with auto-lookup |
| Reference Data | Dropdown source ranges + suggestion templates |
| Subject List | All department subjects + study period + status + print URL + programs |
| Handbook Data | Power Query output (raw HTML sections) |
| Overview | Subject name, level, points, description, ILOs, generic skills |
| Eligibility | Prerequisites, corequisites, non-allowed, recommended background |
| Assessment | All assessments with UID, timing, percentage |
| Dates & Times | Per-period coordinator, email, tuition pattern, dates |
| Intensive & Quota | Quota flags + special delivery notes |
| Further Info | Prescribed texts, related entries, breadth, CAP/abroad |
| Document | Target Audience | What You'll Find |
|---|---|---|
| 📋 Design Doc | Stakeholders, hiring managers | Architecture, module reference, design decisions |
| 👤 User Guide | Team members (non-technical) | Tracker columns, formulas, change types, export instructions |
| 🔧 Developer Guide | Maintainers, developers | Table/column hard dependencies, file paths, export pipeline |
| 📝 Changelog | All | Version history and release notes |
| 📌 Review | PM, admin | Review items and backlog |
| 🧪 Test Cases | QA, verification | Manual test scenarios |
Arthur Chen — github.com/arthurtheprogrammer