Ralph Baxter's Big Data Blog

If you want to follow up on any comment, please feel free to post a comment or contact Ralph Baxter at rbaxter@clusterseven.com, or tel: +44 20 7148 6270.


Solvency II - Back to the Future?

With Solvency II now back on the agenda, firms are dusting off all their old Solvency II projects. Many are picking up from where they left off in March 2013 when project teams were downsized or disbanded. With hindsight some of these projects will never be rejuvenated since their value to the business or to regulatory objectives was marginal at best. However, one area is set to stay centre stage for both communities – data.

For anyone returning to the data agenda after all these months there is one document that should be a "must read". This is the thematic review on data published by the FSA (now the Prudential Regulation Authority or PRA) in 2012, entitled "Solvency II: Internal model approval process data review findings" In particular, Section IX on "IT environment, technology and tools" makes some key comments on end user computing (EUC) that will need to be addressed by firms:

4.41 Where EUC tools, such as spreadsheets, are material to the internal model data flow, we will be looking for appropriate controls for data quality such as reasonableness checks, input validations, peer reviews, logical access management, change and release management, disaster recovery, and documentation.

4.42 Automation of spreadsheets reduces the risk of manual error, but can also introduce different problems such as reduced oversight, inadequate transparency about the extent of linking and proliferation of nested linked spreadsheets. Linked spreadsheets typically pass only single numerical values, without an indication of the date of last update, creating the risk of passing stale data around the system.

And if one is in any doubt about the extent of nested linked spreadsheets prevalent in many firms then look at this page providing a visual example


Historic Excel cell data now available in QlikView

One of the hidden benefits of using ClusterSeven to monitor spreadsheet activity is that the database stores a full history of every cell value as they evolve from one version to the next (such as JanP&L.xlsx going to FebP&L.xlsx to MarP&L.xlsx etc). This means that, on demand, users can visualize and compare historic trends of any important business parameter that would otherwise be hidden across multiple old spreadsheets. Without this many financiers will continue to have to laboriously copy and paste each piece of data to yet another spreadsheet – a process with which most will be frustratingly familiar.

At ClusterSeven we have to admit, however, that we are not the leading dashboard company in the world. So it makes great sense for us to make all this historic business data available to BI consumers. We are therefore extremely excited to see the work done by a leading QlikView partner to present this information in QlikView dashboards. To explore the capabilities of this ClusterSeven Reporter for QlikView visit the QlikMarket.

The benefits do not stop there. Even more exciting is that QlikView users can now combine their traditional analysis of structured data with this new source of unstructured historical financial data being fed from their spreadsheets. So, if your business process combines robust structured data sources and spreadsheets (e.g. multi-office reporting) you only have to go to one location to see everything. No need for yet more spreadsheets and no more cut-and-paste!


BYOA (Build Your Own Application)

There has been a large amount of comment over the last year or so about the business and IT issues attached to the growing enthusiasm for BYOD (Bring Your Own Device) i.e. if/how to allow users to use personal technology gadgets such as iPads to meet their business needs. These issues include security, reliability, maintenance, visibility, replacement etc.

It recently occurred to me that many of the corporate issues exposed by BYOD are already manifested in the work that users commonly perform to meet their individual needs in moving the business forward. A good example is the creation of a new spreadsheet solution to solve a recurrent financial need in the business which cannot be solved by existing applications. If this solution works effectively it is typically absorbed and adopted within the business infrastructure with little business concern, no IT awareness and rarely any plans for security, maintenance etc. So for all the concern over BYOD perhaps it is time to look more closely at the older practice of BYOA (Build Your Own Application) as another source of corporate risk and exposure.


Financial Times puts the spotlight on ClusterSeven and spreadsheet management

Here at ClusterSeven I have been promoting the need for companies to think more seriously about the huge value and risk of operational spreadsheets and follow the trend of many financial institutions to implement reliable, automated controls. So it's great to see that recent spreadsheet errors and spreadsheet mis-management have prompted the Financial Times and FT Alphaville to step into the sector with survey conclusions reported at...

Click here to read the full article (registration may be required)

and continuing banking disclosures at...

Click here to read the full article (registration may be required)


Spreadsheets and Regulators – the Gloves Come Off

You do not have to spend very much time in any financial services or energy firm to know that spreadsheets are pervasive in all operational areas; not just pricing, risk and settlement but also the office of the CFO for all regulatory and statutory returns including tax and disclosures. Enter any specialized business areas – such as actuarial in insurance – and the story is the same.

This activity is well known to those in each of these departments. However, the really strange aspect of these operations is that all this spreadsheet activity often disappears from view as soon as one comes to document formally the business systems and processes for reports to senior management and auditors. The result is that everyone convinces themselves that System A really is directly connected to System B without any other intermediate activity. Or that Report X really is directly generated from System C without any subsequent manipulation. The result of this ‘Group-Think’ is that when regulators and auditors have asked for lists of critical applications somehow the critical spreadsheets get left off. The same is true for the corresponding controls and IT audits.  

This attitude of sub-conscious (and often conscious) denial has been swept away by two recent regulatory documents. The first is a consultation paper from the Basel Committee on Banking Supervision entitled “Principles for effective risk data aggregation and risk reporting” that can be found at this link:

Click here for Basel Committee pdf

The second is the latest Solvency II report from the Financial Services Authority entitled “internal model approval process data review findings” available at the following link:

Click here for FSA pdf

Both reports avoid their past approaches that relied on general terms such as ‘systems’ or ‘applications’ to uncover critical spreadsheets. Now they are specifically called out for control. Whether in the words of the Basel Committee:

“Note 28 b Where a bank relies on manual processes and desktop applications (eg spreadsheets, databases) and has specific risk units that use these applications for software development, it should have effective mitigants in place (eg end-user computing policies and procedures) and other effective controls that are consistently applied across the bank’s processes."

or the words of the FSA:

“4.41 Where EUC tools, such as spreadsheets, are material to the internal model data flow, we will be looking for appropriate controls for data quality such as reasonableness checks, input validations, peer reviews, logical access management, change and release management, disaster recovery, and documentation.”

it seems to be clear that misunderstanding, ignorance or denial is no longer any defence.