Such a task would normally be ripe for automation, but the challenge is that this information arrives in a wide diversity of formats (which makes automation more difficult and costly).

However, for some of their largest multi-site contracts, a single supplier is responsible for scores of sites, which means that the information from that supplier arrives in a standard (albeit supplier-specific) format.

One such example was selected for automation. This supplier was responsible for almost 40 schools and supplied the information needed in 3 types of report - covering trading, labour and sundries, and meal numbers for each school. Some 70 figures needed to be extracted and monitored per month, per school. There were still some format variations per school, but thankfully only 4 - a tractable number. 

I analysed the information content of each report and developed a _lingua franca_ representation that would work with all of them. I wrote code to allow the user to select and load each file type in turn, with the code performing each format and content translation and loading this into the common representation.

The final step was to develop the means to extract data from the common representation to update figures in the monitoring report. This task was complicated by the fact that terminology is not standardised across the reports, and that output values frequently needed to be summarised from several input ones. A user-configurable mapping table was built to solve problems like these.

The application took just a few days to build and is now being used by the client, achieving a huge time savings, and productivity boost. The application of course would deliver predictable, consistent and accurate results, time after time.

The design is sufficiently flexible to permit the analyst to extend or update the mapping tables, and extensible to allow me to migrate this solution to address similar situations in Litmus' workload in future..