Saturday, 31 March 2012

Coding can be bad for your health!

So we have managed to now complete the assessment of all three codes.

Conclusion - don't try and programme anything complicated in Excel. I had concerns about using this environment to do the scientific calculations before we started this review exercise but now I am absolutely certain its a poor choice. The problem is its easy to get something going - and therein lies the mainissue. Unless your are very very very structured, particularly when writing VBA and using Excel  sheets to feed and record results, it is too easy to implement bad practice into the coding. Cutting and pasting data (was that to the right cell), macros (must have seemed like a good idea at the time), sheets for storing mass data (is there a limit in Excel?) are all inappropriate functionality for producing robust, verified and validated (in the proper sense) code.

We are simply going to have to rewrite the whole lot if we want to take these codes forward.

Just come across this quote from a 2002 paper on 'Spreadsheet Engineering' by

Thomas A. Grossman
School of Business and Management, University of San Francisco,
San Francisco, California, USA  94117-1045

referenced from;

which kind of sums up what should be done - ten years on!

"People have programmed computers for at least five decades. Over this time there has emerged a
field called “software engineering” that considers the myriad approaches people take—and
should take—when they write computer programs. This knowledge includes journal publications
describing theoretical research, laboratory experiments, field observations, and recommended
practices, as well as industry wisdom codified in books and computer magazines.

Since a spreadsheet is nothing more than a computer programming tool, one hopes that some of
the accumulated knowledge of software engineering is relevant to spreadsheets, and [Panko
2000a] recommends we start to adapt traditional programming techniques to spreadsheets.
[Rajalingham et al 2000] take a step in this direction with design recommendations and a formal
hierarchical tree technique. However, we are unaware of any systematic consideration of how
software engineering principles could apply to spreadsheets.

The application of software engineering principles to spreadsheets—call this “spreadsheet
engineering”—has the potential to increase the productivity of spreadsheet programmers,
decrease the frequency and severity of spreadsheet errors, enhance spreadsheet maintainability
over time, and actually be implemented by spreadsheet users. "

from a paper that's well worth a read.

Also this one on spreadsheet QA!

How do you know your spreadsheet is right?
Principles, Techniques and Practice of Spreadsheet Style
Philip L. Bewig — July 28, 200

now I'm convinced its best to start again!

Friday, 23 March 2012

2 down 1 to go........

Second code review completed and documented.

On to the third code which is well structured much better documented!

A sample of the data input sheets for this code;

  1. Button Title Datasheet Title
  2. Parts Count Data Parts Count
  3. Process Release Freq. Data 
  4. Riser Release Frequencies 
  5. Blowout Frequencies 
  6. Special Frequencies 
  7. Events Events
  8. (Ignition Probabilities) 
  9. (Escalation) n/a
  10. Fire and Explosion Frequencies 
  11. (Immediate Fatality Fractions) 
  12. Population Population
  13. Immediate Hydrocarbon Risk 
  14. Travel Risk
  15. Non-Hydrocarbon Risk 
  16. Button Title Datasheet Title
  17. Hydrocarbon QRA Results 
  18. Summary QRA Results 

Just got to figure out how it all links together now!

Saturday, 17 March 2012

More progress - the overview.

Now started to look into the 'onshore' QRA model - which is a little better structured. For each fire scenario the code undertakes the following calculation flow;

The initiating event sheet provides the event list to be processed by the application. The naming convention for initiating events is: Area/Unit_Inventory

  • ·         area is the primary area designator (eg site),
  • ·         unit specifies the location of the release (eg a fire zone, module/unit or equipment name)
  • ·         inventory is the inventory name which can result in a hazardous scenario, e.g. valves, flanges

  1. The initiating events (hazardous release scenarios) are based on releases from inventories. A number of hole sizes are defined (typically small, medium, large and full bore) that relate to the event such that the hole size determines the release frequency.
  2. Release frequencies are defined on a separate sheet and referenced via lookup during calculations.
  3. For each event the model walks through each release (hole) size required for both Impinged and Unimpinged states.
  4. Within this nested loop structure the program calculates the branch probability and outcome frequencies for the event tree and copied the results to the Event Tree sheet. The calculations use a combination of the pre-loaded data acquired during program start plus lookup data from worksheets using offsets dependant on the event.
  5. The risk assessment methodology analyses each of the identified initiating hazardous events (hydrocarbon releases) that can lead to potential  jet fires, pool fires, flash fires, explosions or (unignited) toxic dispersion. 
  6. When the QRA is run, the program fills in the data for each event and calculates the branch probabilities and outcome frequencies. 
  7. The branch probabilities are calculated by a user defined maco (function) embedded in the event tree sheet. Once the main process loop has copied data to the tree sheet an Excel re-calculate is invoked to display the results.


Friday, 9 March 2012

Gone as far as we can ......

We have now gone as far as we can with the first of the three codes which focusses on offshore QRA modelling. Though I think we picked the worst for first rather than last!

We now have a view of how the code operates and have documented the main functional and data models. This first application has been so poorly designed and written it is not advisable to use or develop it any further on projects. We now have the code under version control and can respond if there are any queries on past calculations. 

We have recommended that we simply extract the functional models that are unique to it (so that we can re-use then) then freeze this version in archive.

Moving on to code 2 ......

Monday, 5 March 2012

Alex's Blog: FaEL accepted by Microsoft

Alex's Blog: FaEL accepted by Microsoft: The app has now been successfully added to the windows phone marketplace and is available to download! I have had a lot on my agenda at th...

Sunday, 4 March 2012

Things move on.......

Progress this week has been to map out the high level program overview, a view of the decision flow and a start on the data model - pretty impressive work by Roy!

The main aim of the program is to populate a set of event trees related to the frequency and impact of various fire scenarios - as illustrated below.
A combination of user defined macros build the branching frequency and the VBA code to determine the consequence for the various scenarios.

We plough on......