The Perils of financial modeling with Excel

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

The Perils of financial modeling with Excel

Postby bobcat2 » Tue Feb 12, 2013 12:37 pm

The role of Microsoft Excel in the “London Whale” trading debacle.

From James Kwak writing in the Baseline Scenario.
JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”


I write periodically about the perils of bad software in the business world in general and the financial industry in particular, by which I usually mean back-end enterprise software that is poorly designed, insufficiently tested, and dangerously error-prone. But this is something different.

Microsoft Excel is one of the greatest, most powerful, most important software applications of all time.** Many in the industry will no doubt object. But it provides enormous capacity to do quantitative analysis, letting you do anything from statistical analyses of databases with hundreds of thousands of records to complex estimation tools with user-friendly front ends. And unlike traditional statistical programs, it provides an intuitive interface that lets you see what happens to the data as you manipulate them. ...

But while Excel the program is reasonably robust, the spreadsheets that people create with Excel are incredibly fragile. There is no way to trace where your data come from, there’s no audit trail (so you can overtype numbers and not know it), and there’s no easy way to test spreadsheets, for starters. The biggest problem is that anyone can create Excel spreadsheets—badly. Because it’s so easy to use, the creation of even important spreadsheets is not restricted to people who understand programming and do it in a methodical, well-documented way. ...

This is why the JPMorgan VaR model is the rule, not the exception: manual data entry, manual copy-and-paste, and formula errors. This is another important reason why you should pause whenever you hear that banks’ quantitative experts are smarter than Einstein, or that sophisticated risk management technology can protect banks from blowing up. At the end of the day, it’s all software. While all software breaks occasionally, Excel spreadsheets break all the time. But they don’t tell you when they break: they just give you the wrong number.


Link to article. http://baselinescenario.com/2013/02/09/the-importance-of-excel/

BobK
In finance risk is defined as uncertainty that is consequential (nontrivial). | | The two main methods of dealing with financial risk are the matching of assets to goals & diversifying.
User avatar
bobcat2
 
Posts: 4235
Joined: Tue Feb 20, 2007 3:27 pm
Location: just barely Outside the Beltway

Re: The Perils of financial modeling with Excel

Postby Random Musings » Tue Feb 12, 2013 12:44 pm

I utiize excel for financial modeling (including budgets). Works very well, as many cross-checks are built in.

However, the modeling that many of these financial institutions do is complicated and I can't understand some of the "sophisticated" stuff that they do. With respect the 2008 banking crisis and Enron, neither could the auditors that signed off on that goobligook year after year.

Simply put, modern finance can be a high level mathematical game which few understand, and those that do obviously don't sometimes. Clean-up on aisle five, please.

RM
User avatar
Random Musings
 
Posts: 5035
Joined: Thu Feb 22, 2007 4:24 pm
Location: Pennsylvania

Re: The Perils of financial modeling with Excel

Postby bkslainte » Tue Feb 12, 2013 12:51 pm

Thanks for posting, very interesting.

Another example that simplicity rules the day.
bkslainte
 
Posts: 166
Joined: Tue Aug 21, 2012 6:37 pm

Re: The Perils of financial modeling with Excel

Postby midareff » Tue Feb 12, 2013 12:54 pm

Random Musings wrote:I utiize excel for financial modeling (including budgets). Works very well, as many cross-checks are built in.

Simply put, modern finance can be a high level mathematical game which few understand, and those that do obviously don't sometimes. Clean-up on aisle five, please.

RM


+1 Anyone using workbooks sould know how to program for numbers to carry automatically from one sheet to another, from latest entries to other pages, etc. Controlling billions with a risk progrm developed with someone still wearing arm bands and eye shades is silly. If you don't know how to do something in excel just Google it.
User avatar
midareff
 
Posts: 2531
Joined: Mon Nov 29, 2010 10:43 am
Location: Biscayne Bay, South Florida

Re: The Perils of financial modeling with Excel

Postby SSSS » Tue Feb 12, 2013 1:16 pm

There is no way to trace where your data come from, there’s no audit trail (so you can overtype numbers and not know it)


But that's wrong, though.
User avatar
SSSS
 
Posts: 1865
Joined: Fri Jun 18, 2010 11:50 am

Re: The Perils of financial modeling with Excel

Postby Grt2bOutdoors » Tue Feb 12, 2013 1:48 pm

Go cheap, get cheap! :oops:
"Luck is not a strategy" Asking Portfolio Questions
Grt2bOutdoors
 
Posts: 10715
Joined: Thu Apr 05, 2007 8:20 pm
Location: New York

Re: The Perils of financial modeling with Excel

Postby Random Poster » Tue Feb 12, 2013 2:01 pm

I'm probably in the minority, but I have absolutely no idea how to use Excel. I took an introductory class on the software a few years ago, and I didn't learn much beyond how to set print borders.

It is all just too baffling to me, and even when my wife uses the software (somewhat capably) for monthly net worth accounting, I never can quite convince myself to trust the results until I do all the math by hand. At least 5 times in the past year I've found errors in Excel's calculations (clearly, due to input and formula errors), but it does make me think that doing it all by hand is best.
Random Poster
 
Posts: 1055
Joined: Wed Feb 03, 2010 10:17 am

Re: The Perils of financial modeling with Excel

Postby hlfo718 » Tue Feb 12, 2013 2:15 pm

I once interviewed with a giant financial firm within their fund service side for fund of funds. Guess what robust system they used to calculate NAVs on billions of dollars? Excel. Once I heard the lady said excel, my face changed and no longer interested. If an organization is paid to do bookkeeping but too cheap to update their system, not a place I want to be when it blows up.
hlfo718
 
Posts: 650
Joined: Wed Dec 01, 2010 9:17 am
Location: NYC

Re: The Perils of financial modeling with Excel

Postby mack123 » Tue Feb 12, 2013 2:35 pm

hlfo718 wrote:I once interviewed with a giant financial firm within their fund service side for fund of funds. Guess what robust system they used to calculate NAVs on billions of dollars? Excel. Once I heard the lady said excel, my face changed and no longer interested. If an organization is paid to do bookkeeping but too cheap to update their system, not a place I want to be when it blows up.


Every financial institution uses excel.. extensively. Why would you pass a job based on them using industry standard software?
mack123
 
Posts: 48
Joined: Thu Dec 06, 2012 2:26 pm

Re: The Perils of financial modeling with Excel

Postby grayfox » Tue Feb 12, 2013 3:04 pm

bobcat2 wrote:The role of Microsoft Excel in the “London Whale” trading debacle.

Link to article. http://baselinescenario.com/2013/02/09/the-importance-of-excel/

BobK


I've created numerous Excel spreadsheets, and I've also written programs in conventional programming languages. I can testify that Excel is much more tedious, time consuming and prone to error than doing the same thing with conventional programming languages like python or C, or mathematical languages like MatLab or R.

Excel requires a lot of cutting and pasting and editing cell formulas. It is extremely tedious and easy to make a mistake. All the cell formulas are hidden unless you select the cell. And Excel spreadsheets are hard to verify.

Excel has its place:

1. Excel is great for solving problems that are too big for a calculator. But only up to a point. For bigger problems, real programming languages are needed. I'm surprised that professionals in the financial industry would not use something more powerful, like MatLab, when billions of dollars are at risk.

2. Excel is useful for quick and dirty analysis. IMO, it is the computer-age, automated equivalent of the back-of-the-envelope calculation.

3. Excel is also useful for data entry. Then export the data as a .csv file and do the heavy lifting with something more powerful.

4. Excel is also useful for analyzing the output of more robust software. Again, write .csv files, and import into excel for drawing graphs, etc.

But it sounds like some financial firms have chosen the Mickey Mouse solution of using Excel for everything. :oops:
Gott mit uns.
User avatar
grayfox
 
Posts: 4073
Joined: Sat Sep 15, 2007 4:30 am
Location: Anytown, USA

Re: The Perils of financial modeling with Excel

Postby Mandrale » Tue Feb 12, 2013 3:33 pm

A great program to use for a business in place of Excel is a program called SAS Enterprise Guide, allows you to create models, filters, sorts -- Basically anything that excel can do, but allows you to create a process flow that you can schedule for a specific time/recurring date.

The front end point and click interface can't do completely everything, but if you learn the SAS coding language it can be an EXTREMELY powerful tool to use in a business. We just recently got the program here at work and I am absolutely loving everything about it so far.
Mandrale
 
Posts: 160
Joined: Mon Jun 11, 2012 2:55 pm

Re: The Perils of financial modeling with Excel

Postby 3CT_Paddler » Tue Feb 12, 2013 4:07 pm

So how exactly did Excel screw up? Whether one uses a program or excel to do calculations you should always check your results. Sounds like incompetence to me. Lots of engineers use Excel to do sophisticated calculations every day.
User avatar
3CT_Paddler
 
Posts: 3013
Joined: Wed Feb 04, 2009 5:28 pm
Location: Marietta, GA

Re: The Perils of financial modeling with Excel

Postby hlfo718 » Tue Feb 12, 2013 4:19 pm

mack123 wrote:Every financial institution uses excel.. extensively. Why would you pass a job based on them using industry standard software?


Because I was at a job that was using an industry software that is not excel. And these software are not that expensive any more.
hlfo718
 
Posts: 650
Joined: Wed Dec 01, 2010 9:17 am
Location: NYC

Re: The Perils of financial modeling with Excel

Postby Random Musings » Tue Feb 12, 2013 4:26 pm

I see nothing wrong with SAS, but again, if the coding is screwed up, no different than screwing up in Excel.

I also have found that a good number of "dashboard" type programs (that use Excel underneath) are not much different than putting lipstick on a pig. You still have to make sure you are entering the proper inputs and that the flow of data rolls up properly. If you don't, you have data that looks prettier, but still wrong.

RM
User avatar
Random Musings
 
Posts: 5035
Joined: Thu Feb 22, 2007 4:24 pm
Location: Pennsylvania

Re: The Perils of financial modeling with Excel

Postby magician » Tue Feb 12, 2013 4:47 pm

bkslainte wrote:Another example that simplicity rules the day.

I'm pretty sure that it's not an example of anything of the sort. Nor is it an example of the perils of financial modeling with Excel.

It is, however, an example of how important it is to verify your model - whether it's in Excel, in SAS, in Matlab, on a cocktail napkin, or wherever - before putting it into production. Complex models can have errors, of course, but so can simple ones. This was an example that accuracy rules the day, not simplicity.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA

Re: The Perils of financial modeling with Excel

Postby etarini » Tue Feb 12, 2013 5:26 pm

James Kwak writing in the Baseline Scenario wrote:the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”


Why assume that it was an error? Perhaps the "modeler" intended to lower the VaR to get away with a riskier and more remunerative bet, knowing that he could always claim that an there was an unintentional error in the spreadsheet.

I mean, there were additional millions for the taking. Why wouldn't someone just fudge the numbers so it passes muster...why take a chance on losing the opportunity to make more money? These firms obviously have no scruples whatsoever and they know they won't be going to jail.

Eric
etarini
 
Posts: 483
Joined: Sun Jan 24, 2010 6:46 pm

Re: The Perils of financial modeling with Excel

Postby nisiprius » Tue Feb 12, 2013 5:39 pm

That's... fascinating, in an awful way. It illustrates a meta-problem--probably with everything, but I notice it particularly with computers and software. The metaproblem is:

Any problem that is not solved within about five years is never solved ever, because everyone just accepts it as "the way it works."

In this case, the problem is "spreadsheet error," and I remember having lunchtime discussions about it with my colleague back in the 1980s. They were sparked by articles about it, appearing at the time in publications like Computerworld and Datamation. Amazing as Visicalc was in its day, and 1-2-3 that followed, there are numerous ways in which Visicalc almost invite mistakes, and they've just been inherited from product to product to product. Formulas with ranges of cells included in them, for example--insert a row or a column within the range and everything automagically updated to "do what I mean," except not always, and particularly not if you are inserting at the end of the range.

And then there is the human problem--Excel is used by semiprofessionals, people whose programming skills tend to be somewhere at the early stage, where you have learned how to create very complex programs but still have a macho reliance on their own ability to avoid mistakes.

Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
User avatar
nisiprius
Advisory Board
 
Posts: 26098
Joined: Thu Jul 26, 2007 9:33 am
Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry

Re: The Perils of financial modeling with Excel

Postby dbr » Tue Feb 12, 2013 6:07 pm

nisiprius wrote:Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.


Point taken, but why then are my spreadsheets nothing but rows and columns of #DIV/0!, #NUM!, and #VALUE!?
dbr
 
Posts: 14734
Joined: Sun Mar 04, 2007 9:50 am

Re: The Perils of financial modeling with Excel

Postby grayfox » Tue Feb 12, 2013 6:14 pm

nisiprius wrote:
Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.


Everything you say is true. The best word to describe Excel errors is insidious.

P.S. Does that mean that Bill Gates was responsible for the 2008 economic crisis?
Gott mit uns.
User avatar
grayfox
 
Posts: 4073
Joined: Sat Sep 15, 2007 4:30 am
Location: Anytown, USA

Re: The Perils of financial modeling with Excel

Postby magician » Tue Feb 12, 2013 6:21 pm

dbr wrote:
nisiprius wrote:Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.

Point taken, but why then are my spreadsheets nothing but rows and columns of #DIV/0!, #NUM!, and #VALUE!?

Probably a bug in Excel, or they didn't install it properly on your computer.

I'd report it to Microsoft, and wait. Patiently. Very patiently.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA

Re: The Perils of financial modeling with Excel

Postby dbr » Tue Feb 12, 2013 6:29 pm

magician wrote:
dbr wrote:
nisiprius wrote:Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.

Point taken, but why then are my spreadsheets nothing but rows and columns of #DIV/0!, #NUM!, and #VALUE!?

Probably a bug in Excel, or they didn't install it properly on your computer.

I'd report it to Microsoft, and wait. Patiently. Very patiently.


I was being facetious, but referring to the fact that very often in entering a new formula I do something somewhere that generates one of those results and needs to be fixed, in the line of the program immediately complaining about obvious mistakes.
dbr
 
Posts: 14734
Joined: Sun Mar 04, 2007 9:50 am

Re: The Perils of financial modeling with Excel

Postby Mark13 » Tue Feb 12, 2013 6:35 pm

I work in the commercial software industry, though not in the financial domain. I find it perplexing and even a little shocking that people would use Excel to develop complex statistical models with millions of dollars at stake. Excel is great for data entry, simple data analysis, and making charts and graphs. I use it quite a bit myself. But using it for production-level software just screams amateur hour. Being a brilliant mathematician/statistician doesn't mean you can write robust high-quality software. You need software engineers too. It's a different skill set.
User avatar
Mark13
 
Posts: 38
Joined: Fri Feb 26, 2010 11:38 am
Location: Northern VA

Re: The Perils of financial modeling with Excel

Postby texasdiver » Tue Feb 12, 2013 7:07 pm

In my prior life I used to do massive amounts of data analysis for a scientific agency (NOAA). We'd be working with various fisheries and environmental databases with millions of lines of data. Way to much stuff to dump into spreadsheets. But sometimes when I needed to manipulate data quickly for specific reports I'd port the results of a database query into Excel to mess with it and generate tables and graphs. It works great for manipulating data but there are so many ways to make mistakes that aren't easily caught.

We always did the serious work using Oracle and SAS or SPSS. I'm rather surprised that big financial institutions do so much with Excel. Seems so much more robust to use traditional database software and write macros that don't touch the underlying data. With Excel it is so easy to overwrite cells, drag and drop stuff into the wrong place, or just muck things up beyond repair.
texasdiver
 
Posts: 972
Joined: Thu Jun 25, 2009 12:50 am

Re: The Perils of financial modeling with Excel

Postby magician » Tue Feb 12, 2013 7:10 pm

dbr wrote:
magician wrote:
dbr wrote:
nisiprius wrote:Another problem is that programming mistakes typically--not always, but, still, typically--lead to gross bugs, that may be hard to find but at least are easy to detect the occurrence of. The program loops, or throws an exception, or crashes, or does something recognizable as bizarre. But in a spreadsheet, the only effect of a typical mistake is to produce erroneous numbers.

Point taken, but why then are my spreadsheets nothing but rows and columns of #DIV/0!, #NUM!, and #VALUE!?

Probably a bug in Excel, or they didn't install it properly on your computer.

I'd report it to Microsoft, and wait. Patiently. Very patiently.

I was being facetious . . . .

So was I.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1566
Joined: Mon May 02, 2011 1:08 am
Location: Yorba Linda, CA

Re: The Perils of financial modeling with Excel

Postby telemark » Tue Feb 12, 2013 7:59 pm

When personal computers first came out expectations were much more idealistic. Everyone would have a computer and write programs for it as a matter of course, because the genius of the computer was that it was a programmable tool. Now it's clear that most people don't want to write programs, and that we probably wouldn't like it if they did, but it's hard for me to look back on that and not feel sad.
User avatar
telemark
 
Posts: 984
Joined: Sat Aug 11, 2012 6:35 am

Re: The Perils of financial modeling with Excel

Postby interplanetjanet » Tue Feb 12, 2013 8:36 pm

grayfox wrote:I've created numerous Excel spreadsheets, and I've also written programs in conventional programming languages. I can testify that Excel is much more tedious, time consuming and prone to error than doing the same thing with conventional programming languages like python or C, or mathematical languages like MatLab or R.

I agree wholeheartedly with your entire post.

Part of the problem with Excel stems from it's ease of throwing things together quickly, in a visual format. A good program, whether it is in C, R, SAS or whatever, is much more maintainable; inline documentation (comments) are much easier and modular design makes unintentionally breaking things more different. Explicit program flow makes proving correctness more straightforward.

I've taught courses in SAS, R and other mathematical languages, and some of my students had only really used Excel before. There is a learning curve but it is absolutely worth it unless you are truly doing a one-off, or your requirements are straightforward *and you will be the only one touching the nuts and bolts of your spreadsheet*. Unintended consequences are legion when modifying complex spreadsheets.
User avatar
interplanetjanet
 
Posts: 2213
Joined: Mon Jan 24, 2011 4:52 pm
Location: the wilds of central California

Re: The Perils of financial modeling with Excel

Postby Grt2bOutdoors » Tue Feb 12, 2013 9:54 pm

Mark13 wrote:I work in the commercial software industry, though not in the financial domain. I find it perplexing and even a little shocking that people would use Excel to develop complex statistical models with millions of dollars at stake. Excel is great for data entry, simple data analysis, and making charts and graphs. I use it quite a bit myself. But using it for production-level software just screams amateur hour. Being a brilliant mathematician/statistician doesn't mean you can write robust high-quality software. You need software engineers too. It's a different skill set.


I'm not surprised the lower your cost, the higher the profit margin. The higher the profit margin, the higher your pay and bonus. As I said before, go cheap you will get cheap. The person responsible for hiring the quant should be shown the door, along with that person's boss and a whole host of other people. It's likely that Excel was used because most if not all of the risk managers were not able to understand and use any other software platform but Excel. BTW, this was not "millions of dollars at stake", it was "billions of dollars at stake".
"Luck is not a strategy" Asking Portfolio Questions
Grt2bOutdoors
 
Posts: 10715
Joined: Thu Apr 05, 2007 8:20 pm
Location: New York

Re: The Perils of financial modeling with Excel

Postby steadyeddy » Tue Feb 12, 2013 10:12 pm

The whole thing sounds like a scapegoat excuse for a real problem. It's nice and tidy to say, "We told him to fix it, he didn't, but now we did, problem solved." I've heard that one before.
User avatar
steadyeddy
 
Posts: 466
Joined: Tue Mar 31, 2009 5:01 pm
Location: The Alps of the Midwest

Re: The Perils of financial modeling with Excel

Postby Userdc » Tue Feb 12, 2013 11:05 pm

Excel is the perfect tool for this type of work - this looks like this was a breakdown of oversight and controls.

Frankly, I'd question the wisdom of handing this type of work to a developer to implement a back end solution. Not only do you risk losing something in translation, but you are left with an inflexble solution with no way for the end user to follow the math and audit the process. Excel provides flexibilty, but most importanly, it provides transparency.

Thats what makes this story so head-scratching - why weren't there more than one set of eyes on this model? Excel's greatest strength is that it lowers the bar to entry to maximize the population of people who can dig into the process and test the model.
Userdc
 
Posts: 192
Joined: Tue Jun 21, 2011 9:30 am

Re: The Perils of financial modeling with Excel

Postby MathWizard » Tue Feb 12, 2013 11:40 pm

If the process was as described, copy/paste from spreadsheet to spread sheet, then errors were inevitable.

Good software would have checked data for consistency. Good technique is to compute two different models at the same
time, and to verify against one another.
MathWizard
 
Posts: 1533
Joined: Tue Jul 26, 2011 1:35 pm

Re: The Perils of financial modeling with Excel

Postby Tebowed » Tue Feb 12, 2013 11:54 pm

I use Excel for data entry and it is perfect. However, I understand there can be complications when you get sophisticated with it (such as with financial modeling).
Tebowed
 
Posts: 34
Joined: Tue Feb 12, 2013 10:59 pm

Re: The Perils of financial modeling with Excel

Postby bobcat2 » Wed Feb 13, 2013 9:26 am

MathWizard wrote:Good technique is to compute two different models at the same
time, and to verify against one another.

You took the words out of my keyboard. :sharebeer

Why the people at JPMorgan didn't get this, while risking billions of dollars, is unfathomable.

BobK
In finance risk is defined as uncertainty that is consequential (nontrivial). | | The two main methods of dealing with financial risk are the matching of assets to goals & diversifying.
User avatar
bobcat2
 
Posts: 4235
Joined: Tue Feb 20, 2007 3:27 pm
Location: just barely Outside the Beltway

Re: The Perils of financial modeling with Excel

Postby af895 » Wed Feb 13, 2013 9:49 am

I'm going on a bit of a tangent here but...

I started doing a monthly budget in Excel years ago.

I still use it for simple worksheets where errors are immediately obvious and calculations are limited to basic arithmetic.

Somewhere around 2005, I bought a computer that came with Microsoft Money and I've been using it since.

MS Money is a "black box" of sorts but it eliminates errors that creep into more complex spreadsheets.

It introduces other errors but they're consistent - you know where to find and change the setting that caused them and you learn how they affect the program as a whole.

With Excel, errors can become "lost in the sands of time". I would expect hope any reputable financial institution would use a software package that's more robust than Excel.
Last edited by af895 on Thu Feb 14, 2013 7:37 pm, edited 4 times in total.
af895
 
Posts: 116
Joined: Sat Feb 18, 2012 10:15 pm

Re: The Perils of financial modeling with Excel

Postby Hexdump » Wed Feb 13, 2013 9:58 am

As an instructor told me once,

GIGO does not only mean Garbage In, Garbage Out, but more dangerously, Garbage In, Gospel Out.
User avatar
Hexdump
 
Posts: 1498
Joined: Fri Oct 10, 2008 7:28 am
Location: Houston, Texas

Re: The Perils of financial modeling with Excel

Postby Dr. Market » Wed Feb 13, 2013 10:54 am

I consider the issues in the article to be human problems rather than those with Excel. For most people, using programs like SAS/R/Matlab/Mathematica would lead to even more errors since mistakes can be made in coding by the inclusion/exclusion of a single character. Further, even many quants/statisticians/etc don't understand how the built-in algorithms for computing works in these programs. A lot of statistical computing relies on approximation algorithms that are not infallible.

You could make a much more convincing case about how financial modeling is done than to just bash Excel. Because everyone knows how to use Excel's basic functions, it is easy to scoff at its usage for more complex problems. I don't use Excel for my personal work, but if you wanted to create a model that a non-analyst could easily manipulate, you have no choice but to use Excel unless you wanted to make your own GUI as well.
Dr. Market
 
Posts: 18
Joined: Sat Feb 09, 2013 12:58 pm

Re: The Perils of financial modeling with Excel

Postby johnep » Wed Feb 13, 2013 11:07 am

I have used spreadsheets since the inception of Visicalc, the first wildly popular spreadsheet software. Spreadsheets are very powerful and simplify and enable calculations. However, I have seen many really sophisticated looking Excel spreadsheets that looked really good but had the wrong answer. I have seen large RFPs that were mispriced due to these errors. Any good financial person knows you need to cross check and verify your calculations very carefully whatever means you use for financial analysis and this certainly applies to spreadsheets as well.
johnep
 
Posts: 1163
Joined: Wed Dec 28, 2011 9:56 am
Location: North Carolina

Re: The Perils of financial modeling with Excel

Postby MathWizard » Wed Feb 13, 2013 12:53 pm

Dr. Market wrote:I consider the issues in the article to be human problems rather than those with Excel. For most people, using programs like SAS/R/Matlab/Mathematica would lead to even more errors since mistakes can be made in coding by the inclusion/exclusion of a single character. Further, even many quants/statisticians/etc don't understand how the built-in algorithms for computing works in these programs. A lot of statistical computing relies on approximation algorithms that are not infallible.

You could make a much more convincing case about how financial modeling is done than to just bash Excel. Because everyone knows how to use Excel's basic functions, it is easy to scoff at its usage for more complex problems. I don't use Excel for my personal work, but if you wanted to create a model that a non-analyst could easily manipulate, you have no choice but to use Excel unless you wanted to make your own GUI as well.


I am an expert in certain things. Scientific software, Mathematics, certain programming languages, etc.
When I call myself an expert, that means that I know exactly what is going on.

The problem is likely a human one. People (like the whale) who called themselves experts and who give those
upstairs what they want, the impossible, outsized gains with no extra risk, and people upstairs who are not skeptical
because the profit from not understanding. Those upstairs then give more control to these "experts" even though
they do not understand themselves what is really being done.

I often get "impossible" assignments.
They "need" X, which can't be done.
I usually say, X is not possible, but Y is possible and should fit your needs. Here is what Y will provide you, here is how
Y differs from X and why you don't need these things, and here is the budget.
I've always come in on time and on budget, and handle change requests as a new negotiation.

The problem is when you get undercut by consultants who promise X and never deliver, or deliver Y and call it X.

Think of it this way: The whale, the dolphin who is the real expert, and the Boss.

Whale: I can get you 12% on your money with no more risk than corporate bonds.

Dolphin: I can get you 5% basically risk free through arbitrage, or
anywhere up to 15%, but these are the risk and how they vary with return.

Boss: Wow, I can get a 12% return virtually guaranteed because I hired a genius. Let's borrow
10x our money at 5% and leverage 11x to get 12% + (12-5)%*10 = 82%. We'll take 40% for our
operations, we'll give our investors 30%, I'll give Whale a 2% bonus and I'll have the board
give me a 10% bonus because I thought of the leverage. I'll put Dolphin in charge of that
boring index fund we have, since he clearly does not understand high finance.
MathWizard
 
Posts: 1533
Joined: Tue Jul 26, 2011 1:35 pm

Re: The Perils of financial modeling with Excel

Postby Dr. Market » Wed Feb 13, 2013 1:12 pm

I agree. The problem is the whale still thinks he is the the most expert of all and there are enough whales in the sea that a few will come out as wunderkinder.

I can't think of any solutions.


MathWizard wrote:I'll give Whale a 2% bonus and I'll have the board
give me a 10% bonus because I thought of the leverage. I'll put Dolphin in charge of that
boring index fund we have, since he clearly does not understand high finance.
Last edited by Dr. Market on Wed Feb 13, 2013 1:17 pm, edited 1 time in total.
Dr. Market
 
Posts: 18
Joined: Sat Feb 09, 2013 12:58 pm

Re: The Perils of financial modeling with Excel

Postby af895 » Wed Feb 13, 2013 1:16 pm

MathWizard wrote:...The problem is likely a human one. People (like the whale) who called themselves experts and who give those upstairs what they want, the impossible, outsized gains with no extra risk, and people upstairs who are not skeptical because the profit from not understanding. Those upstairs then give more control to these "experts" even though
they do not understand themselves what is really being done...


Neil Postman's excellent book "Technopoly" gets right into that issue. (he uses "pollsters" in relation to policy makers - where the policy maker doesn't question the underlying methodology at all)
af895
 
Posts: 116
Joined: Sat Feb 18, 2012 10:15 pm

Re: The Perils of financial modeling with Excel

Postby lostcowboy » Wed Feb 13, 2013 1:29 pm

bobcat2 wrote:The role of Microsoft Excel in the “London Whale” trading debacle.

From James Kwak writing in the Baseline Scenario.
JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,

“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”


Link to article. http://baselinescenario.com/2013/02/09/the-importance-of-excel/

BobK


Sounds like people failed at doing their jobs!
The modeler develops the model, then he tests it with test data with known answers. If the answers are wrong you know you have a problem and you go back and fix the problem. When you convert the model to a spreadsheet, you do the same thing!
The internal Model Review Group, again is suppose to do the same thing! They are suppose to do other things like make the recommendations they did do. They are suppose to re-review the updated spreadsheets to make sure the approved recommendations were done, and to make sure no new mistakes crept in to the spreadsheets.

When you divide by a sum instead of a average you get a number that is two times smaller than it should be! That would stand out like a sore thumb!
It should have been caught before the spreadsheet went to the internal Model Review Group, but they should also have caught it!
lostcowboy
 
Posts: 207
Joined: Tue Sep 16, 2008 1:30 pm

Re: The Perils of financial modeling with Excel

Postby Dandy » Thu Feb 14, 2013 9:19 am

Oh the bad memories. Early in the spreadsheet era (1986?) I was responsible for recapping a major company's budget with detail of about 20 divisions with sub totals by the 5 most senior executives. Sent it to my boss who "adjusted" it and sent it back. One of the adjustments added a line of detail not reflected in the formula. All the detail was correct but the Grand Total was off by $50 million. It went all the way up the line and was approved. New Year's eve while everyone was "happy" I found the error and was in a panic. My boss pulled an ole' and had me tell the Comtroller. Needless to say I did not have a good New Year's holiday - didn't get fired but felt the black mark from then on.

So - make sure to check those formulas!!
Dandy
 
Posts: 2456
Joined: Sun Apr 25, 2010 7:42 pm

Re: The Perils of financial modeling with Excel

Postby hand » Thu Feb 14, 2013 10:05 am

bobcat2 wrote:
MathWizard wrote:Good technique is to compute two different models at the same
time, and to verify against one another.

You took the words out of my keyboard. :sharebeer

Why the people at JPMorgan didn't get this, while risking billions of dollars, is unfathomable.

BobK


Unprofessional yes. Unfathomable, not really.

Banking compensation is set up so that there is an almost unlimited upside benefit for those individuals and firms who make and win risky bets, with a limited downside.
Is it really that surprising that institutional controls limiting risk are less than perfect?

"It is difficult to get a man to understand something, when his salary depends upon his not understanding it!" -Upton Sinclair
User avatar
hand
 
Posts: 609
Joined: Sun May 17, 2009 8:42 pm

Re: The Perils of financial modeling with Excel

Postby cheese_breath » Thu Feb 14, 2013 11:12 am

I use Excel for all my financial record keeping and modeling. I consider it to be nothing more than a specialized programming language. There are applications where it is appropriate to use, applications where it isn’t. I remember when the university I worked at began offering a computer science degree. Organizationally it was under the engineering department, and most of the faculty was retooled engineers. The Introduction to COBOL class taught the students how to program engineering applications in COBOL. Not the right language for that application.

But back to Excel as a specialized programing language. Any program whether written in Excel, PL/1 (now I’m dating myself), SNOBOL (a specialized text editing language), Smalltalk (a neat OO language that never caught on), or whatever needs to be thoroughly tested before being put into production. And even after it’s in production the results of each run should be able to withstand a reasonably test. Given the inputs, do the results seem reasonable to someone expert in the application area? When more user-friendly languages such as Easytrieve began coming on the market and users at GM began developing their own personal applications, the IT department was consistently having to help users who were blaming the language for their failure to thoroughly test their programs.

It’s so easy to blame Excel when users choose the wrong language for their application or fail to test their formulas thoroughly.
The surest way to know the future is when it's the past.
User avatar
cheese_breath
 
Posts: 3440
Joined: Wed Sep 14, 2011 7:08 pm


Return to Investing - Theory, News & General

Who is online

Users browsing this forum: bowtie, dbp7777, Juniper, letsgobobby, rkhusky, tadamsmar and 63 guests