On a refinery the annual leak detection program involves an operator going out in the field and using a portable gas detector to measure atmospheric VOC (Volatile Organic Compound) concentrations at each one of 50 000 potential leak points (around flanges, valves, etc). Measurements were recorded on paper and transcribed into Excel spreadsheets by back-office staff. An extrapolated annual figure was calculated for each leak point by multiplying the sampled value by an emission factor, which was in turn calculated from the characteristics of the leakpoint and the relevant hydrocarbon product.
The existing system did not expose this valuable data to other people and automated systems in the refinery. It also required a lot of human input, but with 50 000 values being manually entered across 35 spreadsheets it was very difficult to pick up errors. Considerations of compliance and auditability led to a decision to upgrade to a more robust and auditable system.
The solution managed to retain the Excel user interface, with which the staff were familiar and highly competent, and which allowed bulk-manipulation of data using the standard Excel autofill and copy-and-paste capabilities.
However, all the formulae and data were migrated from the spreadsheets to an Oracle database back-end. The Excel spreadsheet ceased to be a database and calculation engine, but continued on as a sophisticated and highly interactive GUI, loading and saving data via a series of web services, much like a web page.
The resultant application was well received by the stakeholders. The formulae could now be centrally controlled in Oracle, eliminating duplication and the chance of accidental changes in spreadsheets. Data entry staff retained the highly-efficient bulk edit capability of Excel, with reduced surface area for error. The wider enterprise now enjoyed direct access to the data for environmental reporting purposes.
As a footnote, a side-by-side comparison revealed that the old system had been significantly over-reporting VOC emission for some years, despite being given a clean bill of health by auditors from a major accounting firm.