Featured post

Fixed Income Trading: New venues

A simple question came up recently in a conversation – how many new Fixed Income trading venues are there?  I could not think of anywhere th...

Wednesday, 25 March 2015

Excel VBA - industrialisation...Part Three "What is Excel Industrialisation?"

No code in this post.  Just some description of how this works with regard to VBA code within a spreadsheet. 
Further posts in this series will cover related topics such as:
  • COM Addins
  • Automation Addins
  • C++ XLL addins
  • DDE dependencies
  • RTD Server dependencies
  • External system dependencies
Let's take a worked example of what would normally happen without an Excel industrialisation process...
A developer is tasked to create a spreadsheet for a business unit.  The spreadsheet has a load of VBA code spread between modules, classes and forms. The spreadsheet then goes into use by a number of business users within the firm.  Over time the code is modified and updated but there is no systematic control of the code or of use of the spreadsheet.  This process is repeated hundreds or thousands of times.  Typical firms I have worked with have had thousands of spreadsheets in use, developed by different people at different times with different coding styles and different skill levels.
When a bug is discovered in one spreadsheet, how does the business management team evaluate which other spreadsheets have the same or similar code and therefore the potential for the same bug?  This means that the portfolio of spreadsheets is fragile.  Imagine the spreadsheet developer has created some code he reuses a lot by simple cut and paste into a new module or class.  How many slightly different versions are there "out there" in the wild?
This lack of control of the spreadsheet portfolio is what makes development management dislike Excel. It's what keeps audit and compliance in a state of fear of discovering that there is a nasty bug that has been increasing risk or costing money that is so well hidden in a "cannot see the wood for the trees" scenario that no-one spots it.  Until something goes bang and the problem is discovered, at which point it's generally too late.
This is why Excel Industrialisation is a great way to reduce operational risk and increase management oversight.  It also allows for a reduction in support costs over time as it provides a way to instrument the portfolio of spreadsheets and then reduce the overall size of that portfolio by increasing re-use through modification of the code base. 
As a simple example, a developer may create a spreadsheet for a team.  That team then splits and splits again, each new team copying the spreadsheet and seeking modifications.  the modifications may be user interface, code or both.  In terms of management oversight, there are four risks.  But Excel Industrialisation will show that the spreadsheets are fundamentally similar and can be merged into one with parameterisation for different use cases.
For a lighthearted take on this issue have a look at TDWTF

See also
Excel industrialisation...Part One "Code to write code"    
Excel industrialisation...Part Two "Export VBA code to file system"
Excel industrialisation...Part Three "What is Excel Industrialisation?"
Excel industrialisation...Part Four "Enumerate Addin references"
Excel industrialisation...Part Five "Integration with Git"
Excel industrialisation...Part Six "Error Handling"
Excel industrialisation...Part Seven "Central Error Reporting"
Excel industrialisation...Part Eight "Enumerating RTD Servers"
Excel industrialisation...Part Nine "Enumerating DDE Servers"  
Excel industrialisation...Part Ten "Enumerating COMAddins"

No comments:

Post a Comment