Posts categorized "Spreadsheets"

The Humans Between Risk and Data

Some of my thoughts on risk management, data management and human behaviour, are to be found on page 20 of the Inside Reference Data Special Report "Managing Risk"

Posted by Brian Sentance | 21 June 2010 | 2:22 pm

"Cut and Paste" Valuation Services

You can talk about more robust modelling, more stringent scenario testing and even moving everything onto an exchange, but unless we move the principles of good data management (in my view: consistency, security and quality of all types of data) into the front office then we will continue to get front-office mis-marking as described in this article in the FT.

Thanks to Ralph Baxter from Cluster7 for highlighting this article for me and those of you interested in this topic of operational risk and spreadsheet mis-use should maybe go along to EuSpRiG this year, and maybe take a look at a paper Xenomorph presented at a previous conference.

Posted by Brian Sentance | 4 February 2010 | 9:49 am

RiskMinds - The Failure of Risk Models

Avinash Persaud of Intelligence Capital gave the opening talk of the morning at RiskMinds (see first of set of posts from last year here) and put forward a lot of the very good ideas that he has contributed to in the recent Warwick Commission Report. Main points that Avinash made:

• Regulators were admirably quick in working out where past regulation had gone wrong in focussing too much on micro (individual institution) rather than macro (whole market)/systematic risk.
• The regulators then came out with promising papers on counter cyclical regulation and other positive ideas.
• These new ideas do not win votes however and do not satisfy the public's desire to punish someone - Avinash called this the "Bad Apple" policy, with "bad bankers, bad products, bad jurisdictions" being the perceived guilty parties.
• All past crises have resulted in demands for three things: i) more risk management; ii) more regulation; and iii) more transparency.
• These are fine as demands but evidently do not prevent financial crises.
• Avinash recalled his work back at JPMorgan in the early 90's when the 4:15 report was produced for Sam Weill, which eventually led to VAR reporting becoming widespread.
• He then fast forwarded to the Asian crisis of 97 where he saw the failings of VAR (or rather its widespread use) first hand with all players using VAR which when volatility increased caused an increase in VAR causing JPM (and all) to sell causing markets to fall, increasing vol causing more selling, increasing correlation and leading to what is called the "loss spiral".
• In light of the recent crisis, Avinash said the public perception is that bankers created a load of toxic bombs (products), through them at an unsuspecting public and ran away...
• ...and in his opinion the reality is that banks created a load of toxic bombs and ran straight towards them i.e. this was a failure of risk management where bankers did not understand the risks they were buying and selling.
• He then took us back to the 1950's and the formation of modern portfolio theory with Markowitz and Danzig working at the RAND Corporation.
• At that time banks and insurers were still separate, with FX and capital controls still in place meaning that not only could the "efficient frontier" of investment portfolios be observed but it could also be acted upon.
• Now everyone has the same information everyone can observe the efficient frontier of investment opportunities but cannot exploit or act upon it, since usually everyone moves in (the "herd") and the value observed is changed by this crowded participation in the market. Here he seems to be echoing a lot of what Bob Litterman said at QuantInvest last week over the "crowded trade" and that the barriers to market knowledge and our ability to act on this knowledge have been lowered forever.
• Avinash put forward that many of the models we use today assume the statistical independence of decision making process whereas the reality is that the market is homogenous (everyone is thinking/acting the same) and hence these models are invalid in this "crowded" context.
• In light of this, the problem of risk management is not about exogenous risk (risks from outside the market, from Black Swan events to normal distributions) but more about endogenous risk i.e. peoples behaviours upon seeing opportunities cause strategic risks. (Interesting given Jean-Phillippe Bouchard at QuantInvest commenting on what makes prices move). Put another way, behaviour is the issue not the financial instruments themselves.
• Avinash proposes that risk capacity (the ability of an institution to absorb a particular type of risk) shoudl be thought through more fully, with for example insurance and pension institutions with long-term liabilities having a much greater capacity to absorb liquidity risk than banks, and banks with short term funding being a better position to manage a loan book.
• He pointed out that regulation that uses market prices to protect us against movements in market prices is doomed to failure before it starts.
• Booms occur due to some perceived "paradigm shift" technolgy leading to dramatically improved risk/return ratios - he cited things such as cars, electricity, rail, dotcom and the mantra from those involved that "This time it is different..." (see "bubble" post from last year)
• Avinash thinks the regulators are significantly to blame for the last crisis since they themselves said the latest financial innovations in credit derivatives were making us safer through sharing out risk in the system.
• He said that there is no theory for making a complex system "safe" as a whole and that the regulators did not/do not "get" this idea.
• Diversity of approach and risks in a large systems (macro financial markets) is our only current defence and regulatory "best practice" has driven conformity not diversity in the market, making systemic risks higher not lower.
• So the regulators are themselves creating a homogenised market.
• In terms of solutions, he proposes that risk and audit committees need separating so that risk management does not become a "tick box" exercise.
• He further proposes that the risk management function is given some capital so that it can place hedges at a macro level for institution (i.e. looking at the resulting risk when divisional risks have been aggregated) - here is proposing moving to risk "management" as opposed to the much more common risk "reporting" found in many institutions.
• One risk management indicator idea he proposed was to put a portfolio management model together that was linked to VAR in order to see where the "herd" is moving to (e.g. low vol, high return Asian markets of the past etc) and to move or hedge against this.
• He is concerned that applying Basel II regulation to the Insurance industry with Solvency II will mean that all players will be dancing to same VAR tune which will introduce more risk as more institutions are forced to react in the same way to market movements and volatility.
• On the same lines, Credit Rating Agency regulation will create barriers to changes in ratings methodology in response to endogenous market risk, again meaning that everyone will be forced to behave and act in the same ways.
• He summarised that "endogenous risk" (movements in the market caused by the market) and not statistical distributions that are the key issue and diversity is the only solution.

Entertaining speaker with some interesting ideas that fly in the face of much of what is being done by the regulators today, and generally well received by many of the risk managers present. Behavioural finance and the "crowded trade" (i.e. everyone doing the same thing in the market causing movements within the market) seem to be key themes occuring in a lot of what academics and practitioners have said on risk management recently. Now what to do about it? Not sure that less (not more) regulation will find many fans at the moment...answers on a postcard please!

Posted by Brian Sentance | 8 December 2009 | 10:04 pm

Views on Fair Value...

Busy week last week for events in London, this time over at the Goodacre / Six Telekurs on Thursday morning. Guy Sears of the IMA was chair of the event, and the event did have a "buy-side" focus to it. Richard Newbury of Six Telekurs started the event and made the following points on the current state of regulation:

• UCITS IV - Richard cited the stats that there are around 37,500 funds in the EU with average value of approximately $180M each as compared to only 8,000 funds in the US with average value over $1B. Richard said that such a proliferation of funds was costly and the more EU could standardise funds and their ability to be transacted everywhere in the EU the better.
• Reg NMS - Richard took a little humorous dig at US regulators when he reminded us that Congress authorised the SEC to form a "National Markets System" in 1975 and so this had taken around 30 years to implement. Whilst Reg NMS is often compared to MiFID, he said that Reg NMS had led to consolidation in the US while obviously MiFID has led to fragmentation in the EU.
• Hedge Funds - Both EU and US regulators are looking at the hedge fund industry. He mentioned the battle the UK was having with some of the (misguided?) regulation that the EU is trying to introduce with over 30,000 HF related jobs in London. The new regulation is likely to increase reporting requirements leading to more need for regular, standardised fair value reporting.
• Credit Rating Agencies - Richard mentioned how there will be more ratings and more ratings types, and the regulation introduced to ensure the CRA do not fall into the conflict of interest trap.
• Data Management - He mentioned the importance of data management within what is happening in the industry and noted how the profile of data management was on the increase.

Mike Jenkins of Ernst & Young tried his best to make the accountancy treatment of derivatives interesting and didn't do too bad an effort but I only took the following few notes from his talk:

• Unlike US GAAP with FAS 157 there is no single standard Fair Value (FV) definition in IFRS, and unsurprisingly IASB are addressing this.
• Mike spent some time mentioning Level 1(quoted), Level 2 (observable) and Level 3 (unobservable) pricing inputs for securites, taken from the IASB exposure draft ED/2009/5 (also see Rowe in earlier post)

Matthew Cox of BoNY Mellon Security Services then gave his presentation on the difficulties/challenges of providing a valuation service to their asset management clients:

• His division often have a "2 hour" window to produce valuations for NAV reporting, often for a 12 midday valuation
• Data exceptions for investigation went through the roof this year due to increased volatility (comment: didn't get chance to ask whether the validations set were "normalised" for market volatility i.e. a price movement threshold would not be fixed but rather be multiplied by a factor relating to recent volatility levels)
• Matthew was very complimentary about the efforts his team put in to cope with this increase in data exceptions.
• He mentioned how many of his clients of established "Fair Value Committees" over the past couple of years, comprised of staff from compliance, risk management, portfolio management etc.
• Matthew mentioned the importance of time zones in valuation and the timeliness of data, with the availability of intraday CDS prices contrasting with bonds who price only from the evening close of the day before.

The panel debate was moderated by Guy Sears, and included the above speakers plus Nigel Reynolds from TD Waterhouse):

• Matthew said that his division sometimes shared the "consensus" price from other clients when one client is looking for some guidance.
• He mentioned that a key timeframe in establishing FV was establishing what is a "reasonable" time frame for sale of a security.
• Nigel Cox said that "suspended stocks" had been a real issue over the past year, where the client "context" (position, situation etc) would very much determine what value a client would want assigned to a holding.
• Guy Sears suggested that valuations should be provided with a confidence interval and not just as a single price
• Mike of E&Y said that this is what full disclosure now requires, other memberrs of the panel suggested this was realistic but not what clients (humans?) expect to receive - they want a single number.
• Guy wondered whether it was an issue that one entity might value an asset at a value X whilst another would value the liability at Y (not equal to X)
• Mike of E&Y pointed out that this was an issue in that current accountancy rules allow a security to be reclassified from "fair value" pricing to "historic cost" basis - this discretion is being removed in future rule implementations
• One member of the audience pointed out that Bloomberg, Reuters and Markit were all trying to extract more revenue from data used for valuation purposes.
• Matthew advocated that the market needed more competition between niche data vendors such as Markit and SuperDerivatives to ensure innovation in service and more competitive pricing.
• The audience asked Guy of the IMA whether the association should have offered more guidance on fair valuation process and best practice.
• Guy said they have provided some, but he advocated that trade associations should not have opinions, since it was not healthy to have the asset management industry collectively herding towards the same valuations.

Well attended event with some good speakers, particularly Guy Sears as host was funny, knowledgeable and kept the other speakers on their toes. I would say the most interesting point was still that "opinions" form prices, opinions formed in the investment/funding "context" of the party with an interest in valuing a security - conceptually this seem to make the asset servicing companies a little uncomfortable since what they are contracted to do is to provide the "right" set of numbers by their clients. Human beings feel more comfortable fixating on a single number than a range of possible outcomes/results it would seem!...

Posted by Brian Sentance | 17 November 2009 | 10:48 am

Integrated Data and Analytics Management

Xenomorph was one of the sponsors on the “Integrated Data Management” webcast last week, hosted by Inside Reference Data (audio recording available here). There were a number of interesting questions that arose from the Webinar.

One fundamental although somewhat academic question was "What is Integrated Data Management?". Certainly everyone seemed convinced that there would be less "Enterprise Data Management" (EDM) projects in future, given the expense, scope and scale of such projects. The concensus was that whilst the need for data management was better under stood across all financial institutions, data management projects would be bitten off in more manageable chunks by asset type, business function or division (so are silos back in fashion I ask myself?!). Coming back to the original question, I guess my slant on Integrated Data Management is that we are seeing more and more data management projects that have an integrated reference data and market data elements to them, primarily driven by the need to sort out data quality/completeness/depth for use within risk management (in light of the financial crisis).

Related to risk management, a topic I pushed was that given the origins of data management for STP/back office, and given the interest in low latency tick data management/analyis in the front office, there seems to be a market gap (particularly in the US?) on how to manage data such as IR/credit curves, volatility surfaces and other derived data sets. These data sets seem to fall into the gap between what is thought of as market data (primarily just prices) and what is reference data (IDs and terms & conditions). This is another area where a more integrated approach to data management would be beneficial, particularly in making all these datasets available for risk management.

Coming back to a "hobby-horse" of mine, then I also raised the issue that whilst it is fine to be doing great data management (high quality, complete datasets etc) what is the point if all of your data is ignored by the front office and Excel is used to download the data traders and risk managers need from Open Bloomberg. I think the management of unstructured data (spreadsheets, word docs etc) needs to be elevated as an issue since this (unfortunately?) is where most data resides currently, despite what we data management professionals like to think.

I also think that the principles of good data management (centralisation, quality and transparency) could apply to other things and not just raw "data", but what about centralised pricing and valuation, centralised curves and centralised scenarios for risk? Again what is the point of doing good data management if the ultimate "information" (e.g. a valuation) is done using poor quality data, with a complete lack of transparency over the data and model used.

A good question was asked about models, which was that given pricing models and their weaknesses have formed some part of the recent crisis, do we need more complex models. On having a few conversations about this and thought about it some more, then some would say it is complexity that got us into the crisis so this is the last thing we need. My view is that we do not necessarily need more complex pricing models and valuation techniques, but we certainly need more robust ones which does not necessarily imply more complexity. Coming back to a point raised by David Rowe previously, then I think all quants and risk managers should think about a "second means of valuation" for all the theoretical models they use, and that hedgeability (see recent post on pricing model validation) seems to be the common theme in producing more robust pricing models.

Posted by Brian Sentance | 21 October 2009 | 10:32 am

High Performance Spreadsheets

Another article about the operational risk generated by the usage of spreadsheets within the financial markets (see earlier posts), appeared in the April issue of Waters Magazine.
 
The articles highlights how spreadsheets are largely used within financial institutions and suggests that the current regulation requirements for more transparency and ad-hoc risk management might push the proliferation of spreadsheets even further. The articles also refers to the progress and improvements made by Microsoft in recent versions of Excel to increase the security of spreadsheets.
 
Xenomorph has worked closely with Microsoft on hosting its time series database within SQL Server 2008. The case study we have written together describes how SQL Server 2008 offers integration within Office Excel 2007 so that whilst the spreadsheet is still the end-user viewing tool, operational risk is reduced by engaging Excel 2007 as an analytics and reporting tool and not as a mean of storing data.
 
Our TimeScape solution offers more than 700 easy to use add-in functions to Office Excel 2007 and we are currently working on the use of Excel Services, part of Microsoft Office Share Point Server 2007, to further enhance the centralized approach to spreadsheet.
 
If you are interested in how Xenomorph solves the problem of spreadsheet management, then take a look at our (newly updated) website. Here we explain how to solve the problem and how Xenomorph Spreadsheet Inside technology can bring unstructured spreadsheet data and complex calculation within a centralized data management system, increasing transparency and reducing operational risk.

Posted by Brian Sentance | 8 April 2009 | 2:35 pm

Data management, derivative analytics and the spreadsheet

Interesting article out doing the rounds on the newswires announcing a forthcoming report called "The Enterprise Spreadsheet: Pushing towards Transparency" by the analyst firm the Tabb Group. It is great to see an analyst firm acknowledging the importance of spreadsheets within the markets, particularly in the area of combining data and analytics together in OTC derivatives management (see earlier post).

Adam Sussman of the Tabb Group reckons that despite its shortcomings, Excel is a valuable tool: “Spreadsheets, either alone or in conjunction with other components, can meet the same requirements as a business application.” In this he seems to be agreeing with the UK Regulator the FSA, who have been recently advocating that spreadsheets and spreadsheet data needs actively managing as an institutional resource. The findings of the Tabb Group on management also seem to echo a recent report called "Buy-Side Data Management in a Changing Landscape" done by Lepus for Asset Control (registered link to report here).

Spreadsheets are a great tool and fulfil a real need in the market to pull together pricing models and data quickly, easily and with a timeframe that is meaningful to the business (see earlier post for some work by Xenomorph in this area). Spreadsheets are a big problem to manage, but they are also the symptom of failings in core systems that are not able to rapidly support new instrument types and pricing models. An institution that ignores analytics, spreadsheets and spreadsheet data within any EDM transparency initiative has already failed before it begins, and so to paraphrase the author Aldous Huxley:

"Spreadsheets do not cease to contain data because they are ignored."

Posted by Brian Sentance | 13 February 2009 | 2:54 pm

Happy Birthday Spreadsheet!

Article on PCMag saying that the spreadsheet is 30 years old. Whilst wishing it a happy birthday, the author, John C. Dvorak, has a good rant about how spreadsheets have been the major weapon in the rise to power of the accountant in business.

Good job he did not spend too much time looking at their usage in financial markets or else his rant would have been much longer, given past issues with spreadsheets in financial markets. The spreadsheet (which means Excel at the moment) is a great tool that is:

• a calculator;
• a report writer;
• a database

In my view it is the latter usage of desktop spreadsheets to store data where the problems mainly reside, not its usage as an analysis tool. Faced with inflexible trading and risk management systems that do not allow instrument and trade data to be represented quickly or correctly, it is unsurprising that traders, portfolio managers and risk managers resort to spreadsheets as the "pressure relief valve" for their business activities.

Delivering systems that can support both complex and non-standard instruments and trades in a transparent manner should be a focus in a world where that the lack of transparency over credit derivative pricing has been such an issue. The inappropriate usage of spreadsheets is a very small part of the current problems we are experiencing in the markets, but addressing this would be a positive step in creating a data management foundation that encompasses all data used by a financial institution, not just that data that is easy for software vendors to represent in their systems.

Anyway, enough of my spreadsheet hobby-horse, for some light relief and to celebrate 30 years of summing rows and columns, take a look at the Eusprig web site for a list of the most notable spreadsheet failures.

Posted by Brian Sentance | 15 January 2009 | 6:42 am

Unstructured Data Management Anyone?

Good example on Finextra this week of another spreadsheet debacle, this time involving Barclays and Lehmans (see article).

Not sure when the financial markets will get serious about managing unstructured data and spreadsheet usage. We can all apply Enterprise Data Management until the cows come home, but if the "real" business is being managed in spreadsheets then really why bother with grand aims like EDM?

Even the regulators are not totally against the usage of spreadsheets (see presentation), then simply want unstructured data to be managed properly as part of an institution's formal processes and not ignored until the inevitable problems arise...

Posted by Brian Sentance | 20 October 2008 | 6:05 am

Spreadsheet meets database at Cornel's Arxiv...

The paper I presented at a "spreadsheet-risk" conference a while back has now been approved and entered at Cornel's Arxiv academic archive, see:

http://arxiv.org/abs/0802.2932

The paper details some of the work that Xenomorph's product development team have done on combining spreadsheet ease of use with database consistency and centralisation. We call this tech "SpreadSheet Inside" and a more detailed whitepaper on it can be found at http://www.xenomorph.com/downloads/whitepapers/spreadsheet-inside/. Clients are using it to centralise and control access to spreadsheet calculations and also using it within (extremely!) complex reports for risk management and trading.

Many thanks to Grenville Croll at Eusprig (www.eusprig.org) for assisting on this!

Posted by Brian Sentance | 13 March 2008 | 12:02 pm