Spreadsheet application design

I’m finding more and more that any non trivial reporting/analysis app that I write ends up following the same basic design. Here it is:

In smaller models all those elements are implemented with VBA and worksheets, in medium size ones some code may move elsewhere, and the data table ends up as an .mdb. In big systems the guts of it ends up in some OLAP server like Analysis services or Essbase. And the sources may be in DB2 or whatever. If possible I aim for reporting to be pivot table based, that way as long as I get the data right all the reporting should be trivial (emphasis on should!).

I can’t remember the last time I wrote a big complex mainly formula based reporting/analysis app. As soon as the client wants multiple views (say monthly and annual) I am thinking big table driven pivots.

Do you find a lot of the stuff you do forming into patterns?



3 Responses to “Spreadsheet application design”

  1. AlexJ Says:

    Not coincidently, my latest (and hopefully not trivial) creation follows your method exactly. I’m just on the verge of converting from VBA/Excel only to a .mdb back-end “mule”.
    Reporting is based on a set of pivots which drive charts – works really well.

    I’ve often postulated that the only reporting methodology that makes sense requires that the reporting engine and UI be seperate from the other user functions – this does not happen very often in the design of systems that I get to work with.

    Forming into patterns – sounds like lessons learned. (learnt?)

  2. Al Gill Says:

    Yep – I / we’ve written some of those. There are occasional variations (most commonly the client vetoes having a database – sigh) but apart from the fact it wasn’t on a beer mat that diagram looked very familiar.

    More generally, there are a lot of design patterns in PFI (and related) modelling – particularly of debt waterfalls. These are actually fairly well-known (something working as it’s supposed to for once) which quite often limits how bad even a numpty model can be. There is also a bunch of stuff that you see quite often in VC investment type models although it’s by no means as standardised.

    Wonder if it means anything that some areas seem to have far more prevalent design patterns than others?

    Anybody else ever had wistful thought about Office Dev design patterns type books as for proper (OOP) languages? I’d like to read one but it would be way too much effort to write, the market’s not that large and I’m certainly not expert enough to put one together without having a lot of co-authors (and dividing whatever meagre profit might remain.

  3. sam Says:


    90 % of all the projects that I have developed fall in this category
    There are 3 components

    a) Automater – Basically a Template(dictator style) connected to a Database (Excel /Access)
    Its functions are
    i) Add Data
    ii) Edit Data
    This has 2 way communication to the Database (Push + Pull)

    It is normally an XLT file sitting on network folder – Shortcut to this file available on user desktop

    b) Database (Excel / Access – depending on the clients preference)
    Doing nothing other than storing data

    c) Reporter (Excel) – Set of Pivot Tables + Queries + charts: Which refresh via code whenever the file is opened.

    Very rarely a report using formulas – only if something cant be achieved with a pivot.

    Again an XLT file sitting on a network folder – Shortcut to this file available on user desktop

    Only one way communication with the DB (Pull)

    All “Security” (accesses) happens via the network


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: