Finance without spreadsheets5 min read . Updated: 30 Mar 2016, 01:59 AM IST
The use of spreadsheets for quantitative financeby which I mean portfolio analysis, financial engineering and risk managementis inefficient
What is common to Barclays, Fidelity and JP Morgan, other than the fact that they are all large multinational financial institutions? As regular readers of this daily would probably recognize, all three have had to face the embarrassment of acknowledging spreadsheet errors amounting to hundreds of millions of dollars.
It’s not financial services firms alone that have been left red-faced due to “Excel mistakes’’. MI5, the British intelligence agency, also ended up bugging the wrong phones in 2011 due to formatting errors, and the 2012 London Olympics organizing committee oversold 10,000 tickets because of wrong data entry. Such occurrences have become so common that it seems people have learned to live with the risk of spreadsheet errors, and some have even found ways to make money off it. The Basel Committee on Banking Supervision explicitly instructs banks to have “effective mitigants" in place against errors creeping in due to “manual processes". The industry has responded, and several risk consulting firms today provide advisory services to manage spreadsheet risk. Not many outside the risk management world would probably be aware, but there even exists a European Spreadsheet Risks Interest Group.
If one looks back at the evolution of spreadsheet software since the days of Lotus 1-2-3, nothing much has changed in substance. Yes, a user has more tricks up her sleeve and there are web apps to work in groups now, but, fundamentally, the data in Microsoft Office Excel is still organized and analysed in a tabular form.
The first version of Excel dates back to 1985, and what is astonishing is that for 30 years, much of the corporate world has been using versions of the same software sold by one company. As a comparison, despite the market share enjoyed by Maruti Suzuki Ltd, its cars today are no longer the obvious choice for most upper-middle-class Indians, and one would have thought loyalty would matter more for automobiles where the after-sales service network is valued. But in the corporate world, the larger the organization, the more prevalent seems to be the use of Excel.
Lest this piece be seen as a rant against Excel, let me admit that I am no anti-spreadsheet evangelist. I used to be an Excel user in my earlier job, and continue to use LibreOffice for quick calculations. There is no denying the utility of a spreadsheet software, and given the ubiquity of Microsoft systems in organizations, perhaps using it is even unavoidable. It remains useful for prototyping, basic data analysis and teaching elementary statistics. However, it is not suited for large projects and serious scientific research.
As Harvard professors Carmen Reinhart and Kenneth Rogoff found out, despite the quality and substance of their research, a couple of avoidable spreadsheet errors unnecessarily diverted attention from the main message of their work. And then there is the famous ‘London Whale’ incident. As Mint readers would remember, it took more than a year for JP Morgan to get that monkey off its back, and not without its chief executive officer Jamie Dimon having to take a huge salary cut.
I can’t speak for other areas, but the use of spreadsheets for quantitative finance—by which I mean portfolio analysis, financial engineering and risk management—is simply inefficient, if not outright lazy. For the quantum and nature of data involved, it invites too many bad practices: cell references, manual inputs and copy-and-paste habits, virtually unreadable formulas, unwieldy formatting, multiple tabs and resultant humongous file sizes, over-use of colours with clumsy legends, misleading and ugly graphs… and the list goes on. Yes, I know many of these practices can be obviated, and power users often are smart about not employing these practices. But I am not sure beginners appreciate the potential hazards of these practices until it’s too late.
Working on large projects, with interconnected data and modern techniques, is best done in an environment that facilitates the efficient separation of raw data, its organization, analysis and output. A spreadsheet software simply is not up to the task.
Perhaps Newton’s first law of motion has something to do with this phenomenon. But this inertia in industry has meant that several business schools continue to predominantly use spreadsheets for quantitative courses. With the growth of data science and analytics, programming languages R and Python have been increasingly accepted in elective courses. However, much of the basic curriculum still relies on spreadsheets.
All this leads back to the fundamental issue of whether a business school curriculum should be proactive or reactive. In choosing computing environments in curricula, most business schools have operated in a reactive mode, likely because of placement pressures. Most students prefer the status quo, wanting to “blend into" the companies they will join. The question is whether these arguments hold water in today’s environment of easy availability of MOOCs (massive open online courses) on all topics quantitative, including free courses in Excel, R and Python. My opinion is that they don’t.
When it comes to doing complex quantitative analysis using cutting-edge methods, spreadsheets simply lack the statistical sophistication of R and the power of Python. Both today come with their web apps—Shiny for R and Jupyter for Python—which make illustrating and sharing applications a cinch—and fun.
A common refrain against R/Python is that their use requires programming and “I am not from engineering/science background". It is like saying that I do not want to learn English because I studied in a school where the medium of instruction was vernacular. As with any new language, there is a set-up cost involved, but that cost would not be much more than a week for an interested user.
Although we still have a long way to go, I believe that, among Indian business schools, IIMA is at the forefront of introducing open-source computing environment to its students, both at our post-graduate and doctoral programmes. We have done away with our compulsory Excel course in the first year, and many of the faculty use R and Python in the classroom regularly. A few of us in the finance area have not only completely shifted to an open-source environment for our research and teaching, but have also completely ditched proprietary operating systems in favour of Ubuntu, or even Arch Linux. Now, that’s a topic for another day.
Vineet Virmani teaches finance at IIMA. His research interests lie in computational finance and risk management.