Data or logic?

What do you think is the biggest issue with having multiple copies of (probably) similar spreadsheets floating around? (ie versionitis?) (I’m just using that as a normal word now, seeing we don’t know who coined the phrase – if it was you claim it in a comment!)

Is it the data duplication/possible modification or is it having multiple copies of business logic floating around? or the opportunity to use the wrong version? or something else?

I think the data thing is fairly easily solved if the s/s has a reasonable structure. You can just pull in the data as and when needed from some server based moderately reliable source.

The business logic, for example the pricing process, seems a bit harder to resolve. This is the bit that is often in a spreadsheet because its not simple enough for some web service type approach. I have worked on projects (well a project anyway) that used ILog Rules, I remain unconvinced that that would work as a replacement for the stuff I see.

Me, I think its the business logic tied up in the formulas that is the critical thing that would benefit from improved management. In my experience much of the reference data is just a replica (in theory!) of some corporate data source.

What do you think and why?

Cheers Simon


7 Responses to “Data or logic?”

  1. Marcus Says:

    Hi Simon,

    Google found 26,100 entries on versionitis (15,000 in English). The old reference found was from 1999.

    I also think that the data component is relatively simple to resolve. As I’ve mentioned previously, as much as possible I have the underlying data in a relational database and drag in what’s needed for calculation or presentation.

    I tend to approach business logic from two angles. Much of the work I do is MIS & reporting solutions which is typically aggregations and some fairly basic maths. Where possible I let the database perform the calculations and only return the results. IN some other solutions I’ve used an XLA Add-In to perform the calculations. As this is stored on a file server, maintaining a current version isn’t painful.

    However there are projects, like one I’m currently engaged in, where the above approaches wont suffice. The business unit has a template on their intranet which users down load when they require to perform this particular calculation. We recently spent two stakeholder meetings to investigate why the model worked for some users but not others (at a different physical site) who had just upgraded to Windows XP. XP turned out to be a Red Herring, one group of users was using an old URL to retrieve the template and thus weren’t using the most recent version. Morale: Managing the distribution mechanism is just as important as managing the product/solution.

    Cheers – Marcus

  2. Charles Says:

    Mostly I build systems using a relational DB, an XLA on a server, a template xls (which may be built into the xla) and probably a Control.xls on the server.

    The calculations are built into the XLA or the Template. I try to combat versionitis by using version numbers for everyting, displaying them on the output, and doing version cross-checking.

    For instance the XLA can check whether the Template or Control file or DB is the current version, and can check whether the Template being used contains a later XLA version number (Msg – Get updated XLA) etc.

    Several years ago I wrote a prototype excel workbook generator (called GenExcel) which abstracted an Excel workbook into a compact high-level Excel based modelling language (somewhat like Improv) and a database. I used it on a few customer projects but concluded that the modelling language was too difficult for the mass market and the effort required to commercialise as a product too large. It had some versioning

    But I still think the approach was right!


  3. Ross Says:

    As far as the data side goes I agree with the pull side, but sometimes the push (back) side can be tricky. I tend to use a .mdb as the back end, but I wouldn’t want to use that for more than about 10 user or so. After that your in to a popper DB – which IT will have to set up and maintain – not always the easiest thing to get done!
    If your working with a ERP/ERM type system you might be luckily enough to get a SQL interface out, but I’ve never been able to get one to write back? – So in those cases keeping the data up-to-date can be a really pain.

    I have just been approach about a reporting/analysis tool by a colleague, at the mo it’s in a SS and is with one customer. The idea is to grow it to many customers across the world! – How this would be kept updated I don’t’ know – it would essentially be like an Office product – I guess you would just have to release service packs and new versions? I think the issue here would be keeping records enough so that when Bob in Northampton with version 1 phones up and asks why David in London with version 2 has different numbers you could explained why? – That and just like Marcus says “Managing the distribution” – and might I add access? It’s so easy to e-mail someone a model, then they don’t get the update/new model, and bang before you know it you’ve got a call and a head ache as to why “your” numbers don’t make sense!!!!!

  4. dermot Says:

    I work at the smaller end, with users. I’ve seen multiple s/s most often with a number of small scale unit pricing investment systems run by consultants, where each of their clients has its own s/s & data, and the differences begin to multiply, leading to inconsistencies and errors.

    I’ve tried everything, from individual s/s (and data) managed by a central XLA, to a database with a s/s as the UI.

    I have established that the really important things are
    1. protect the data
    2. be consistent
    3. make the business logic visible and transparent (don’t lock it away in VBA)

    Recently, I took on a relatively small system of individual s/s, so I brought all the data into one s/s, in a few sheets laid out in database style, built a calculation sheet to do all the standard calcs, and then added a sheet per client, to handle the client-specific requirements. It is a nice tight solution given the degree of flexibility I need to handle individual client needs.

  5. Harlan Grove Says:

    Guess I have to be the dissenting voice. *IF* there’s a formal specification to work with, checking spreadsheet formulas for compliance with that spec isn’t that difficult, and there may be multiple ways to implement given calculations, all of them equally valid.

    That said, the formulas, defined names and obviously the XLM and VBA, if any, are the programming content of spreadsheets. Data is data, but it can be very difficult to verify data. In the company where I now work, there are different though presumably functionally equivalent systems on the company intranet for in-house use and on the company’s internet site for customers and intermediaries to use. They pull from the same company databases, but the intranet system refreshes cached summary data more frequently than the internet system. That’s led to some big problems.

    There may be one truth, but there are usually dozens of ways to calculate it, and the data subject to those calculations can vary by the second. In my own experience, ensuring different systems use the SAME data has been, is and is very likely to continue to be much more difficult than verifying code/formula correctness or adherence to a common specification.

  6. Simon Says:

    Interesting comments, its reassuring that we all face the same issues, but also a shame we are all doing such similar things.
    I guess which is the big issue depends on your criteria, but I was thinking, data is pretty much solved (at least in theory). Decentralised data logic does not seem so well solved. Well actually I think it may be but we are not using the solution – VSTO stuff can easily check with a server and update itself if necessary. Should we be pushing our clients to .net? (I suppose its only a few lines of VBA too)
    on a seperate issue I’ve got well burned with my add-ins not playing nice with Excel 2007. I am going to put soft time bombs in everything else I release (soft = warning, not refusing to work) and I’m also going to check if app.version greater than x then warning.
    I do check that they are running at least 97, but never thought to check the other end.
    do you all check?

  7. Ross Says:

    Funny I tend to check for >12, then bail if it is. I’ve never checked for

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: