Spreadsheet types

This was a post I meant to do a week or 2 ago, but limited access to upload the graph slowed me down.

Simply put, what proportion of a given system is implemented in a spreadsheet grid and what proportion in some other technology?

spreadsheet types

Heres my views, please add yours as comments, especially if they differ. I have combined data and code as the other axis, but it might be better to split them out (not from an ease of graphing POV though!). I’d be very interested to read your ideas on distribution too, they dont have to add up to 100 (mine don’t!).
No big reason to split it into 5, or for even 20% steps, I don’t think it matters much, the key point is this continuum from pure cell formula spreadsheet to pure code/data source based systems. This post tries to make some sense of some of the different types along the way. Of course it is very open to interpretation.

Type A
Description: Almost completely cell based, maybe some simple, non critical macros, perhaps a print macro, or some data import code, or maybe a link to an external data source.
Distribution: I’d say 80-90% of all commercial spreadsheets are in this category, maybe even 99%?
Issues: To me this is the ideal use of a spreadsheet, fully leverage the power of the grid, and add a little automation sugar to help things along.
This sort of spreadsheet should be easy to move between different spreadsheet applications (such as Excel and OpenOffice Calc or Gnumeric)

Type B
Description: Mainly cell based, but with a fair dose of code or data access in places. The code is likely to be fairly important, and may include user defined functions, and/or complex coded manipulations. Could be a fairly smart front end to an enterprise data source.
Distribution: Maybe 5-10%?
Issues: Perhaps beginning to bump up against some intrinsic spreadsheet issues like lack of security or fragility. Much of the code could be workarounds for these issues. Requires dual skill sets to maintain. Seems likely that both the code and the cell based functionality will be fairly complex. These often use such a rich set of host features they would be challenging to move to another spreadsheet program.

Type C
Description:Functionality fairly evenly split between spreadsheet and code. Many dictator apps that take over the Excel environment probably fall into this category. Could be a server based system that outputs a combined data and formula spreadsheet as a smart report. Heavy use of xll UDFs could put a system in this category too.
Distribution: Up to 5%?
Issues:If its server based in may not be within the influence of the users. If its Excel/VBA based then the reasons for using a spreadsheet as the basis could be questioned. As functionality moves from the spreadsheet to the code migration becomes more feasible. Perhaps to another spreadsheet application, but more likely to an executable with some sort of grid or spreadsheet control.

Type D
Description:Mainly code based with significant use of the grid. .net and VSTO type solutions probably often fit in this bracket. Could be making extensive use of a small part, for example the calculation engine. Dictator add-ins could fall into this category. Probably more of an external standalone program, that uses automation to manipulate the spreadsheet application.
Distribution: <5%?
Issues: As the spreadsheet is a minority feature it is likely only a small subset of its features are used. Some of those may have a negative impact, such as lack of security. Might be possible to migrate to a grid control or another spreadsheet program, assuming the required functionality is there.

Type E
Description: Almost completely code based with minor or no spreadsheet functionality. An example might be a spreadsheet that is used as a dumb grid based report for an enterprise data source.
Distribution:5% – 10% (I think there may be lots of basic reports from an external datasource)
Issues:Same as type D, and also deployment? As virtually all the logic sits in code, actually a powerful grid control may be more appropriate. Often will not need advanced spreadsheet skills, mainstream coding would be more useful. The spreadsheet component of this type of system is likely to be easily moved to other technologies, some of which could open up new markets.  Such a system may well have very low client install pre-requisites, making deployment relatively straightforward (potentially).

I would say most of my stuff falls into type A or B. Although some of the Essbase report writing is probably more of a C/D overall.

The reference checker that I wrote is probably an example of an E. it could easily be an executable with a simple grid control or even some messaging component. But an xls is a better deployment story.

Is this classification useful? what do you see as the distribution? what features of each type have I missed? would a code/data split be useful? which categories would you put your stuff in?

cheers

Simon

Advertisements

12 Responses to “Spreadsheet types”

  1. Dennis Wallentin Says:

    Simon,

    Nice approach! I would remove the data from the X-axis unless we can challenge it with a 3D diagram. Maintance is one ‘big issue’ that should also be mentioned. From a general point of view I can agree on the % frequency per group. However, it does not reflect my own, at least not today. Here are my roughly ‘numbers’:

    A: 50 %
    D:

  2. Dennis Wallentin Says:

    Hm? Where did my list go? A: 50% D

  3. Marcus Says:

    Hi Guys,

    Interesting. I could almost make the X axis the time scale. As time has progressed my work has moved from Type A to E.

    In the past most of my spreadsheet work was Type A such as budgeting models where all the calcs were formula based and macros were mainly for navigation and printing.

    This progressed a few years ago to models which had a good deal of code, typically to import and manipulate data in preparation for calculation and reporting.

    Most of my work now is in the E bucket with one caveat – solutions are as much table driven as they are code.

    Example: I just finished an xla Add-in that allowed the mortgages division of a bank capture metrics for progress payments made to builders.

    > all data entry is through forms

    > all the data is in a RDMS

    > All actions are table driven
    to add (edit, delete) a record, the add-in retrieves a list
    of form controls and their respective query parameters
    from the database, loops through the form substituting
    parameter names for values and executes the
    corresponding query. Adding or removing fields involves
    no code changes.

    > virtually all calculations are performed by queries

    > Excel simply provides a familiar environment for users
    and a convenient reporting medium

    I think one reason for this trend is the increase in the volume of data involved in projects.

    Side note: 1 million spreadsheet rows is a potential disaster waiting to happen. 65K rows has provided users with a reasonable trigger point to convince them to migrate their data to a more suitable medium. A million rows will only delay this transition.

    Cheers – Marcus

  4. Simon Says:

    Dennis – sorry, no idea where your list went, good point on maintenance.
    Marcus – x axis time: me too!
    million row post coming up soon
    cheers
    Simon

  5. Charles Says:

    I mostly do D and A.
    My favourite design is a DB backend (usually Jet/access) with a VBA XLA frontend that generates SQL queries and workbooks with help from template files. Data manipulation is done with SQL and calculation mostly with excel formulae except for specialist stuff (time series analysis etc) that gets done in code.
    I have not built a really large complicated type A for a few years now!

  6. Dennis Wallentin Says:

    OK – I try again with my frequence %:
    A: less then 5
    B: less then 10
    C: more then 50
    D: more then 20
    E: less then 10

    I guess that my core interest nowadays is well reflected. I can also echo what Marcus and Simon point out aboutthe X axis being a time scale. The only conclusion of that is that we all will end up in VSTO!

    Kind regards,
    Dennis

  7. Simon Says:

    I dont think it likes the less than sign, a few people have got cut off on various posts.
    Dennis – interesting about you doing mainly C’s, that is probably the sweet spot for VSTO.
    I probably do mainly A/Bs and E’s (Essbase stuff/xlls), so VBA suits me better.

  8. MikeC Says:

    I could have almost written Marcus’ timeframe comment myself (except I doubt I would have put it so eloquently….!)
    (Especially the part about Excel being a “comfortable” environment for users…)
    The A-Bs I produce are mainly where someone needs a “quick & dirty fix” where they have x,000 rows of data and don’t know where to start, I’ll just throw something together for them in a few minutes to provide a basic summary etc, and tell them to come back when they have a better idea of what they want. It’s rare for me to put a dictator-type set-up together in anything less than a D, as I rarely produce any “finished products” these days which are C or below, and control of the user is less important in a “work in progress”.

    (I do get a lot of A/B types handed to me, often calc and resouce-intensive ones that have grown organically and need an overhaul. They usually end up a type C-D by the time I finish.)

  9. Rob Bruce Says:

    An important subset of Type E is the ‘data exchange’ spreadsheet. I’ve worked on several products in the past (data mining; front ends to proprietary databases; adding Windows client front ends to legacy mainfrmes) where the ability to export to .xls format was pretty near the top of the specification must-haves. For nearly all of these systems exporting to CSV would have been perfectly adequate, but you’d be amazed how many end user representatives would dig their heels in and insist that all headers must be bold and all numbers formatted to two decimals! Fortunatly, third party BIFF writers were just beginning to appear around the time of these projects.

  10. Ross Says:

    I think your figures are a bit out mate. I reckon if you looked around most corporate networks you’d find that xls with code account for less than 2-3%. That’s of total population, usage might be different.

    That would make the rest a very small number. I think that A, B, C would be about even, D a bit lower, and very few E’s?

    Just my 2 cents!

    I think I spen 1/2 of my time in A (no code at all) and C.

  11. Monica Santos Says:

    i need to know the different kinds of spreadsheet applications!
    if u have the answer then plz send me to my email adress

    thank you

    Monica

  12. Jennifer Says:

    Hey! I am a student and hope someone can help me. I need to write a paper on the different types of spreadsheets. Not the brands. I have googled for about 45 mins. and can not find anything. I would appreciate any help you could give me.
    Thank you!
    Jennifer

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 )

Google+ photo

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

Connecting to %s


%d bloggers like this: