Excel Developers’ Conference Jan 25 London report

http://xlconf.wordpress.com/2011/11/22/uk-excel-developer-conference-london-january-2012/

Simon Murphy of Codematic had lined up an array of speakers and topics that drew a packed crowd. Morten Siersted’s company F1F9 sponsored the lunch for all the delegates.  Simon opened the conference with a summary of the evolution of Excel development and closed it with a personal view of project management.

Stephen Allen of ACBA spoke on his auditor’s Electronic Working Papers (EWP) application, which he developed from the need to demonstrate a chain of evidence from the system being audited through the risk to the audit finding, conclusions, and recommendations. He describes this as “a recursive programming approach required to rearrange branched thought processes into linear output for reporting”.  A trial copy of ACBA-EWP can be requested from ACBA (UK) where you can also download a demonstration workbook.

Bob Phillips spoke on Personal BI (Business Intelligence) made possible by the PowerPivot addin for Excel 2010. He covered the DAX language and mentioned the work of Tom Gleeson, the datasmith. There were some questions from the Access developers in the audience, as Bob indicated that PowerPivot is the future of data access and reporting by Excel.

Dr. Mike Staunton of the London Business School spoke on his approach to coding style to permit portable code from VBA to VB.Net and C#. He likes the free tool ExcelDNA and TangibleSoftwareSolutions.com code translators, who feature his Wilmott article on VBA to C#.Net.

Mathias Brandewinder spoke on VSTO, showing that version 2 was not the bugbear that version 1 had been. He demonstrated several simple applications including Ribbon and Custom Task Panes; see VSTOStocks.codeplex.com. For UDFs he recommends ExcelDNA and XLL-plus. The audience were with him right up to the point he mentioned that VSTO needs a Visual Studio 2010 Professional licence at $800. However, ask your national MS developers’ contact who may know of special offers with eg Technet.

Charles Williams  recounted his experience of migrating 8000+ lines of VBA code in his FastExcel addin to C++. This was necessitated by the introduction of the 64-bit version of Excel. To save him time, he used the Plantech XLL+ addin for Visual Studio and the STL/Boost library. His blog at decisionmodels.com is worth following for tips on improving Excel performance which can be better done by more efficient algorithms than language tweaks.

Morten Siersted of F1F9 spoke of the FAST modelling standard. Aimed at the general spreadsheet financial modeller, it stresses virtues such as transparency (comprehensibility) and being “beautifully simple”. This is achieved through a few rules for organising code in consistent formula blocks, grouped detail rows. He enlivened the rules by comparing their recommendations of staged inputs to a TV cook and font color coding to plumbing diagrams.

Simon provided the usual swag that conference attendees love to collect and also gave away draw prizes – I had contributed my book on Spreadsheet Check and Control, and the Q-Validus Spreadsheet Safe training manual. Some of us,because of our connection with EuSpRIG, were interviewed by the BBC for their upcoming programme on spreadsheet risks in “More or Less” (about statistics and economics) to go out on the World Service. It was good to meet so many faces belonging to people I had hitherto known by email or newsgroups, such as Simon Hurst, Anthony Prince, Andy Pope, Russell Proctor of BetterSolutions, Philip O’Brien of Pasigraphy, and colleagues from previous work. I’ve taken away some New Year resolutions for things to work on!

Talk at SoftTest 26 Jan 6pm Dublin on “Test Centres of Excellence”

http://softtesteventjan2012.eventbrite.co.uk/

Annex meeting room in the Central Hotel, 1 – 5 Exchequer Street Dublin 2 Thursday, 26 January 2012 from 18:00 to 20:00 

Too often companies are relying on hard work, experience and luck rather than planning and executing tests in an appropriate and focused way. With increasing demands these systems grow to be more inefficient, unreliable, costly and can ultimately become a serious risk to the business.

So how are organisations meeting this challenge head on? The answer is by developing and growing Test Centres of Excellence (CoE). A Test CoE is a professional service organisation that delivers testing services to the IT group and the business. It comprises a dedicated and independently managed team of people with a core competency in testing, operational processes to support testing for the enterprise and supporting tools and technologies.

Presenter: David Cass. David is a Senior Manager in Strategy and Operations Consulting for Deloitte Ireland with over 9 years experience in software testing, test programme management, test strategy development and organisational change. David specialises in developing organisational test strategies, end to end quality assurance and test organisation planning & optimisation for large organisations.

Microsoft Translator VBA code sample

MicrosoftTranslator.com provides an API that is free up to 2MB per month.
MS are moving from the Bing appID to an authorization token for their translator but as this forum post says have not brought the documentation up to date and don’t have VBA examples. So  I’ve decided to post my own so people can suggest improvements.

MicrosoftTranslator VBA module

Happy new year!

Twelve years ago I wrote the book on the Euro Changeover in IT systems (“Managing the Euro in Information Systems”, Addison-Wesley, 1999). I wonder will 2012 see the year some countries have to switch back? Internally it should be easy enough if they adopt 1 new NC (eg púnt nua) = 1 euro then national prices remain the same. International prices will then need foreign currency handling. Ah well. At the time (1999-2001) I had believed there were controls in place on financial discipline but the realpolitik proved otherwise.

On the side of End User Computing and Compliance and Best Practices and Spreadsheet Controls and all those good things, well, I’m still developing techniques, tools, processes, and training for organizations to get to grips with the spreadsheet Hydra – ask me for more info!

Happy Christmas and good wishes for 2012

Our card this year is Megan’s photo of the Festival Arbres et Lumières in Geneva last Christmas. Her calendar for 2012 features photos she took at the Annecy Venetian Festival.

http://www.meganobeirne.com/

Dictionary VBA Class update

I’ve uploaded a new version of the Dictionary Class and a helper KeyValuePair Class. This exposes the KeyValuePairs collection so iteration is now very fast, about 2 seconds to iterate 1 million items using a for each loop:

 
dim oKeyValuePair as KeyItemPair 
For Each oKeyValuePair In td.KeyValuePairs ' direct access to collection
   var = oKeyValuePair.Value 'either of .key,.value
Next

Updated 25-Nov-11: The need for the helper class is to allow updates to the .Item without changing the order.

The Science of Spreadsheet Risk Management, July 5-6 2012, Manchester UK

The European Spreadsheet Risks Interest Group (www.eusprig.org) has issued a call for papers for their 13th Annual Conference on the theme “The Science of Spreadsheet Risk Management”. The conference will be held on the 5th and 6th of July 2012 in Manchester, United Kingdom. Papers are welcomed on the management, development and use of spreadsheets.

Academic papers and practitioner reports describing how organisations have developed enterprise policies and best practices covering the development, testing, use, deployment and control of spreadsheets will be particularly welcome.

Here are some sample paper titles I’d like to see:

Spreadsheet Success: How we did It
Spreadsheet Efficiency and Productivity
Balancing Spreadsheet Controls and Benefits
Spreadsheet Development : Smart and Safe
Safe and Effective Prescriptions for Spreadsheet Users

More info at www.eusprig.org/call-for-papers.htm