A different approach to spreadsheets

Traditional approaches to spreadsheet management focus on replacing them completely, which is impractical in many instances; just concentrating on identifying errors; or large scale systems that discover spreadsheets, perform risk assessments and then take control of and/or monitor those spreadsheets deemed to represent the greatest risk to the company.

However, there is something missing here. Consider your typical organisation where spreadsheets are used for financial reporting. A variety of spreadsheets are typically received from various sources, which have to be combined to produce some particular output. How is this achieved? Generally, by cutting and pasting from one sheet to another. However, there is no audit trail for cutting and pasting let alone the calculations and transformations that may be required on the way. Not only is this prone to error but there is also no formal process that can be documented and you cannot prove that you have actually followed such a process. In other words you are not SOX compliant and you can expect a smack on the wrist (and a large bill) from you auditors.

So we would really like to have a formal, documented process that enables the merging, calculation and transformation of data from multiple spreadsheets into a finished form. If you think about it, we want ETL (extract, transform and load) for spreadsheets. Not, of course, ETL per se – that would be over the top – but equivalent functionality.

However, there’s another problem. We have assumed that incoming data is all in spreadsheets. But often it isn’t. We might well receive information in text files that need to be combined into our spreadsheets. But, further, the separators in these files may be different – tabbed separators, comma separators and so on – so you need some clever technology to automatically recognise what sort of separators are being used.

But that raises another issue. Text files have headers but spreadsheets don’t. So you need a designation function that allows you to define a particular row (say) as the header detail within that spreadsheet. Now you just need software to recognise identical column headers (regardless of their order, which may be different in different sources) and you can start to merge multiple spreadsheets and text files automatically.

All of this is what Ormetis does. Put simply, you merge worksheets or text files by just dragging and dropping one to another and the software effectively performs a join based on common column headers. You can then designate other equivalent columns, drop columns, perform calculations or do whatever you normally do with spreadsheets. At the same time, the software generates an audit trail of precisely what you have done, both for compliance purposes and to construct a repeatable process so that you can ensure that the same procedures are followed every month or every quarter. And since the software is time aware you can ensure that you are using the current version of the numbers and not the last period’s (which has led to some pretty expensive fines and court cases).

The product is relatively young. Version 1.1 was only released in July, so there are a number of features in the roadmap that are not in the product yet. Nevertheless, customers are already playing around with it and doing innovative things that the developers had not intended. For example, one company is using it to migrate to Salesforce.com. Another is using it for applications based on Microsoft Calendar (which is currently in beta). Besides the dozen or so who are using it conventionally.

All-in-all this is an impressive solution. I haven’t seen anything else like it and, as far as I know, it is unique. Definitely worth a look.