Migrating from QUAL2K Excel VBA to the Python Web Interface
The original QUAL2K model ships as a Microsoft Excel workbook with embedded VBA macros, available from the EPA Center for Exposure Assessment Modeling. While functional, this architecture creates significant operational challenges: VBA crashes on large models, macro security settings block execution, and the file is limited to Windows. This web-based Python implementation eliminates these limitations while maintaining numerical equivalence with the legacy model.
Feature Comparison
QUAL2K Excel vs Python Web Wizard
| Feature | Excel/VBA (Legacy) | Python/Web (This Tool) |
|---|---|---|
| Platform | Windows only (requires Excel) | Any browser (Windows, Mac, Linux, mobile) |
| Execution | Local VBA macros | Cloud-hosted Python 3.12 |
| Data entry | Manual cell editing across 20+ sheets | 10-card guided wizard |
| Import/Export | Copy-paste within Excel | JSON + CSV import/export |
| Simulation time | 10–60 seconds (VBA interpreted) | 2–5 seconds (compiled Python) |
| Calibration | Manual trial-and-error | Automated genetic algorithm (planned) |
| Crash recovery | Lost if unsaved | Browser state persists, JSON backup |
| Collaboration | Email .xlsm files | Share JSON config file |
| Constituents | 15 state variables | 15 state variables (identical) |
| Hydraulics | Manning's / Rating curves | Manning's / Rating curves (identical) |
| Diel simulation | 24-hour loop | 24-hour loop (identical) |
| Source code | Locked VBA modules | Open Python (documented in these docs) |
Data Mapping: Excel Sheets to Wizard Cards
The Excel model stores data across multiple spreadsheet tabs. Each tab maps to one or more wizard cards in the web interface.
Sheet-to-Card Mapping
| Excel Sheet | Wizard Card | Key Fields |
|---|---|---|
| QUAL2K (rows 8–32) | 1. Project Setup | River name, date, time zone, calc step, options |
| Reach (rows 10–60) | 2. Reach Definition | Reach names, geometry, Manning's n, slopes |
| Headwater (rows 8–28) | 3. Headwater | Flow, temperature, DO, nutrients, pathogens |
| Point Sources (rows 10+) | 4. Point Sources | Location, flow, concentrations |
| Diffuse Sources (rows 10+) | 5. Diffuse Sources | Location range, flow, concentrations |
| Rates (rows 10–80) | 6. Kinetic Rates | Decay rates, stoichiometry, algae parameters |
| Light and Heat (rows 10–20) | 7. Light & Heat | PAR, extinction, solar/wind methods |
| Meteorology (hourly) | 8. Meteorology | Air temp, dew point, wind speed, cloud cover |
| WQ Data (field observations) | 9. Observed Data | Calibration targets at stations |
| — (run button + results) | 10. Review & Run | Summary, submit, view results |
Migration Steps
- Export from Excel: Open your existing .xlsm file and manually note the key parameters (reach geometry, headwater conditions, kinetic rates). Alternatively, use the CSV export format to transfer data in bulk.
- Create a wizard JSON: Build a JSON file matching the JSON Schema with your model parameters. Use the Paraluz test file as a template.
- Import and validate: Use the "Import JSON" button in the wizard to load your configuration. Review each card to confirm the data transferred correctly.
- Run and compare: Execute the simulation and compare results with your Excel output. The Validation section demonstrates the expected agreement between the two engines.
Common Issues When Migrating
| Issue | Cause | Solution |
|---|---|---|
| All zeros in results | Empty reaches list in wizard state | Verify Card 2 has reaches defined; use Import JSON |
| Timezone parsing error | Text timezone like 'Eastern' not recognized | Use numeric offset (e.g., -5) or recognized name |
| Manning's n = 0 | Missing geometry data in reach definition | Ensure all reaches have non-zero Manning n values |
| DO saturation too high | Default elevation = 0 (sea level) | Set correct elevation in reach definition |
| No diel variation | Meteorology card empty | Add hourly met data or use constant values |