How spreadsheets (nearly) conquered and killed the financial industry In my first job out of college I worked at a timesharing firm. For those of you who don’t know, timesharing, whose heyday was in the late 1970s, allowed companies to use large mainframe-based systems without themselves having to purchase these huge computers and hire an army of support staff. Once signed up, a company shares a …
This is all very well and good saying change should be centralised, but in all reality when this decision is made it comes down like an anvil and literally all control moves to the centralised team stifling any change which in turn infuriates the users and they circumvent the system due to the incredibly slow and painful experience of documenting in IT requirements what they want when their specialty is something completely different. This article sets out to attack the likes of Excel, but without this most basic of tools computer usage would be a decade behind where we are today so it needs to be given some level of respect. If we were to remove excel it wouldn't mean centralisation but it would mean pen and paper and a calculator. Progress is what is needed in the speed that it is needed. Centralisation works when change is needed to be slow. Listen to the users, they are what matters, not your IT job.
> Listen to the users, they are what matters, not your IT job.
Users like Jerome Kerviel who cost his bank billions because IT folks had very little say in how the information technology was misused? Thats what happens when you use spreadsheets with little regard for the IT department. You get a mole that games the system.
Nonsense. The Excel sheet does not place the trades. It is used for analysis and senarios to aid decision making. That decision may be good or bad, but the trader separately decides what trades to place. That trading system is where IT support is needed, not productionising an analysts calculations. If you don't unerstnad that essential difference you should not be in IT, and are a control freak.
I don't claim to be an expert on in the finance industry but I have seen how stupid the relationship between IT and the enterprise can be in plenty of other industries. A good IT system will do a good job tracking the companies resources which often is IT's biggest contribution to the enterprise. I just remember the write up in this case being about how SG relied much too heavily on spreadsheets for reporting that the trader was able to manipulate due to prior working in IT himself.
Users like Jerome Kerviel who cost his bank billions because .... . You get a mole that games the system
Why is it still not clear to people that, probably, 95% of all trading profits comes from "gaming the system" in some way? The distinction between "modern finance" and straight up "fraud" has become very blurred these days.
Whether the gaming is illegal or legal, the only thing that in fact gets punished it when someone like Jerome Kerviel blows up with a large enough loss to hit the bottom line of the bank! One have to wonder if Jerome is the only rat in the game. Given the lax controls of the financial records that we know is the case from Kerviel and the Nick Leason who is not a racing driver, then whenever some trader blows up in a punishable way, there is an incentive to update his accounts to accommodate ALL the blown-up trades. The "rogue trader"-story sounds so much better than "systemic risk" or "pervasive culture of fraud"!
Of course traders will use spreadsheets to get around the IT department - they are judged on performance, how much money they make for the bank per week, only. IT & Risk Management are roadblocks to success ;-)
PS: I know a person who worked with pricing derivative deals using Excel. Sometimes Excel would crash over circular references in the model, so they would just run it a couple of times until the figures looked "normal", then go with that! The bad part is that electricity, CO2, petrol or grain prices e.t.c. is calculated from buggy, random spreadsheets like that!
Wow food for thought. Of course the fraud thing is sadly common knowledge for those that care to look but the whole pile everything on one junior rogue trader seems to be SOP in the industry. Like the freakonomics guys say incentives are everything and when your incentives are to completely ignore risk nothing but bad things happen (usually to the wrong people too).
"Furthermore, the design and coding of a Java system is always significantly more involved than relatively simple Excel scripting"
Including the tests, right? You do have tests for your spreadsheet? Or do you prove it works by drinking Red Bull and thinking really hard?
...awaits the downvotes as a legion of Register spreadsheet programmers educate me about how to write unit tests for spreadsheets :-)
I have a nice set of unit tests for Excel around here. Few columns of inputs, a column of expected output, a column of actual output, and a nice summary PASSED/FAILED.
Mind you, this Excel spreadsheet is mainly used by QA team, to build a model of calculations which are in turn only used to build actual test cases for actual proper code . Traders use proper code, not Excel. They do get to see Excel though, and if they make a suggestion to change some calculation in Excel, the models (and the test cases it generates) are tweaked accordingly and next used to validate the code changes which are to follow.
#sh ./Megacorp_unit_test
Checking bureaucracy.c.......... 6/108 FAILED
crap, lets run it again.
#sh ./Megacorp_unit_test
Checking bureaucracy.c.......... 45/108 FAILED
WTF screw this.
C:\my_unit_test.bat
Checking user.xls........1/1 PASSED
See, my spreadsheet passed all the unit tests it needed to :p
I think the point of the article was it doesn't matter how many tests the system has, if the system is fucked, the users will route around the damage.
"You do have tests for your spreadsheet? Or do you prove it works by drinking Red Bull and thinking really hard?"
I think that was covered (very briefly) in the early part of the article. Companies that insisted on properly tested anything quickly went bust, overtaken by those of their rivals who were reckless enough to just go for it and lucky enough to get away with it.
From the point of view of an individual company, the best strategy is harder to judge. Spend too much time on testing and you will lose to *someone*. Spend too little time on testing and (eventually) you will lose everything you gained. From the point of view of the ecosystem, however, at any given time *someone* is winning so who cares how much blood is being shed in the process?
My point was that the software engineering profession seems to have accepted that having a codebase that can be automatically proven to be no less broken than it was X days/weeks/months ago leads to a lower rate of defects AND greater development velocity, both initially and on a sustainable basis, therefore allowing the alpha finance types to trade, snort coke, shit on each others' desks or whatever else they do to earn their bonuses.
If there's some other approach that Excel hackers use to achieve the same pleasant outcomes, so be it.
"Including the tests, right? You do have tests for your spreadsheet? Or do you prove it works by drinking Red Bull and thinking really hard?"
I know of several hospitals which run their entire financials on Excel. Said hospitals aren't in good financial shape with millions of dollars unaccounted for.
A programmer from one of these hospitals was a business partner of mine and the other directors decided he would write the accounting system instead of forking out for an business program such as Quickbooks or Sage. 3 years of development and 50k in tax errors (plus penalties) later he still wasn't admitting there was a problem, but Quickbooks got installed and all the previous errors became glaringly obvious as historical data was entered into it. The total was a lot more than 50k worth and approached 2% of gross income.
Spreadsheets are for simple things. If you want to be complex or actually run a business there are better tools for doing up screws than a toy plastic hammer. If one must stay in that model, then FFS move to Access. (It's not as if Quickbooks is a powerful program, but it's clear that "rolling your own" is probably going to end up in disaster for anyone more complex than a low-end contractor, vs using a package which is designed to handle financials properly.)
Somewhere along the lines the article veers from financial management into financial/share trading. Those kinds of things are where spreadsheets work, but if you're plugging more than 20 items into Excel then IMO you're already using it in territory where the answers may not stack up.
"Spreadsheets are for simple things. . ."
Dead right! There are/were lots of SMEs out there who think Excel == a database. I spent a rather enjoyable and lucrative couple of years pointing out the folly of their ways and migrating them to either Access or SQLServer with a decent front end.
One of the benefits was that I found out more than I really wanted to know about various small business processes. Anyone want to know how to automate a steam laundry where their SCADA devices tried to talk directly to Excel?
yep , unsurprisingly Accountants LOVE Excel, and when you show them what VBA can do they wet themselves with excitement, and rapidly every data collection , database type of system can be reduced to an Excel spreadsheet . Screw the fact that only one person at a time can view it , and that on the versions I used at least ( Excel 97 and 2001 ) there was a 65,536 line limit . All you need to do is get someone suitably skilled to "glue" several spreadsheets together with VBA , and not minding emailing around spreadsheets. Job DONE .
Oh did I say , also forget Access , that's way TOO sensible to use. Who'd want to have something that can easily handle more than 65536 rows ? And why on earth would you want to use MySQL etc..... blah blah ...
EXCEL is the KING !
oh happy days being the said idiot that glued together sheets with VBA , I suppose at least I was younger , slimmer and better looking then, even if I was idiotic for listenning to the accountant who thought it was a good idea, and then implementing it. (Robbie Williams was right , youth is wasted on the young) Please will the God-of-IT-and-systems-development please forgive of my sins of idiocy, I have learnt my lord, and haven't touched Excel VBA in many years, and don't even boot into windoze that often, I now worship Perl , Linux , Mysql , Postgres and opensource ... amen ....
( by the way I am really a fundamental atheist )
"A programmer from one of these hospitals was a business partner of mine and the other directors decided he would write the accounting system instead of forking out for an business program such as Quickbooks or Sage. "
Just staggering.
The 2nd sentence in the book Peopleware (1987) reads "There are probably a dozen or more accounts receivable projects underway as you read these words. And somewhere today, one of them is failing."
25 years later the same old s**t in the same old bucket.
If your business is so small it does not *need* an accounts package, why write one?
How many businesses (*including* govt depts) are *so* special that a bespoke solution is *necessary*?
As luck would have it, one of my current projects is to enable the business analysts to test changes to their spreadsheets (including hooks into external data sources) without destroying the production environment. First someone (not me, thankfully) had to explain to them why a test environment was valuable at all. Then they were unable to enumerate the complete list of dependencies, requiring a mountain of iterations of the discovery process. Finally, they assigned the most junior (hence, least knowledgeable) person on their team to do testing. And, of course, if the project is delayed, it's because IT wasn't responsive enough.
Good times.
Of course I had unit tests for spreadsheet systems. You get the test framework for free, so we were doing unit testing before you were born son.
As it happens, my very first spreadsheet system, when the existing accounting system was entirely centralised main-frame accounting, uncovered an accounting/forecasting error that had already gone to the Board without being noticed. That was just a collection of macros, and already it was more 'robust' than the enterprise system it complemented.
In many cases, system tests for spreadsheets are just the unit test and the visual test, but having two or three units is not uncommon. When it gets to 4 or 5 units the development (as the author notes) becomes creaky, but when that happens the unit tests all pass, and the integration tests fail or are neglected.
I moved on from that to doing systems to replace spreadsheet systems. Our replacement systems were faster and more correct: they replaced spreadsheet systems that were slower and wrong. I didn't need to use ignorance of the alternatives to justify our alternative approach.
I worked for seven years inside a trading room at the start of the spreadsheet era. There was a very definite divide between the traders and the glass house, with extremely poor communication between them. Growing into the role of being the "anything mathematical/technical/computational" within the room meant that I was exposed to a helluva lot of weird and wonderful spreadsheets.
It didn't take long to prove my worth by picking up substantial errors in both the dealers' spreadsheet tinkerings and the mainframe calculations delivered to terminals without any "show your working". However I did find that between interviewing the dealers and walking through their spreadsheets that I could either fix/improve them or translate them into more robust PC applications. That was an edge that no other bank in town had as they still struggled to bridge that cultural divide.
There were others who tried to surf this change more profitably, and perhaps less ethically. I was passed a spreadsheet "model" that had a rather pricey consultant was using for our bank. After a night of sifting through this monstrous spreadsheet spaghetti, I found that not only were there buried constants (mostly out of date interest rates or exchange figures), but that about 95% of the spreadsheet was simply garbage formulae that simply didn't have any impact on the output figures. Once I pared the spreadsheet down to the simple I/O relationships, and exposed all of the numerical constants, it was quickly obvious that it didn't say anything useful.
And then there were the options calculators ... scary stuff.
My previous employer rolled out infrastructure to allow central support of excel sheets. It was pretty clever each instance of excel was hosted on a VM and it had some sophisticated error monitoring. There was a VM management front end similar to system center which allowed reallocation of resources on the fly. It was all simple enough that a business user could make changes whenever they liked, it did help get rid of a lot of the drawbacks this article lists.
As an aside, I achieved a "personal best" when I built an options calculator sheet that took 40 minutes to output a single updated price.
This article is bang on the money and I see these problems everyday. Change management, while well intentioned, can be built around ill conceived processes and go completely out of control, to the point where change is virtually impossible. The parallels between the signoff process you describe and the signof process at my current employer is uncanny. So, as well as stopping people changing things for the worse, these processes additionally stop people being able to change things for the better! At this point, almost invariably, people decide that if they're not permitted by change management to do the job properly to a timescale that's acceptable they'll have to fudge something else in under the radar. Cue another spreadsheet based approach, which nobody will know about, nobody will formally support and nobody will even understand once the trader who cobbled it together leaves the desk.
"Having direct control over the “calculators” they use means traders don’t need to put in a formal request for every minor formula-tweak they need implemented, or every slight change to the scenarios they want to run, and then wait for someone else to implement it — a process that would make much of their day-to-day work impossible given the speed of market movements."
So, if we went back to depending on mainframes, the flash crash problem would go away?
Just wondering.
. . . maybe, but spreadsheets are the bane of every IT persons life, especially ones built by so called experts.
The number of times i've found people altering them, without understanding all the background logic that they've just completely fucked up; the number of times i've seen complex spreadsheets created by someone who does know what they are doing, but leave leaving the entire system unsupported and undocumented.
Speradsheets have their place, don't get me wrong, but the level of inappropriate use is insane in any large organisation. I've seen a spreadsheet written that entirely duplicates MS Project (which we also have available) - the main reason for doing this? The Project Managers didn't know how to use Project.
The list of bad uses far outweighs the number of good uses.
"For those of you who don’t know, timesharing, whose heyday was in the late 1970s, allowed companies to use large mainframe-based systems without themselves having to purchase these huge computers and hire an army of support staff."
And people claim "The Cloud" and "Saas" is all the new sexy.
If you see these people, please give them a slap from me. Thanks.
In a previous life I worked in support for a 'Major Multinational Oil Company'.
I was just a desktop support monkey, but one day got a call from part of the business with an Excel problem. "No worries, I'll drop round to your desk".
As it turned out, he was complaining that Excel couldnt always get the correct answer. I dug a little deeper... The spreadsheet was calculating daily production figures from the oil platforms. This was based on linking to (at last count), 14 other Excel spreadsheets. Located in various combinations of onshore and offshore file servers. All with different levels of permissions and user access.
The user seemed most upset when I dragged in a business analyst and said "I think this needs a real solution, you cant trust excel with this." He's probably still waiting for the real solution.
Yes, until you've actually seen an Excel spreadsheet running for multiple simultaneous users and updated in realtime from various internet and Reuters trading feeds ...
You too will say "Excel doesn't do that" and "you can't trust it"
Once you have, you learn very quickly to say "I will need that request in writing before I can look at it"
I was personally responsible for rewriting a centralised, IBM midrange hosted system that used to spit out the G/L account information for reported accounts every month as input to the manual / spreadsheet monthly P&L reporting processes.
They were very pleased that my version ran in a few hours rather than the several days it used to take. (Take crap program that ploughs through the G/L to work out an account state, iterate that for a range of accounts, iterate that for a range of ranges. Yeah. Right.).
They were very pleased that my version saved half the paper use, via the simple expedient of not bothering to generate the cover page, headers, footers and total page for account ranges with no transactions in the reporting period.
They were deeply pissed off that my version was absolutely and verifiably correct, as that proved that the company's financial reporting had been a work of outright fairytales and bullshit since Jesus was a lad.
At least when the spreadsheet monkeys fuck up, they only fuck up their bit......
Not really, they screw up their bit and then tell someone else to buy something based on dodgy info thus losing the company millions.......
Having worked for a few banks where that has happened I noticed that most of them were banning spreadsheets or bringing them under version control.
I stand by my point.. all the IT types just don't understand the speed at which change needs to happen. This is why Excel is so prevalent. Its all very well saying excel is crap and dangerous and overused, but business cannot wait weeks for a changes that needs to happen within an hour. So unless you work in a business that has no change (highly unlikely) or you can speed up controlled change to suit your IT golden shiny tower needs then Excel is here to stay. I suggest spending a month in the undetached real world of business, you would quickly realise why most people say their IT organisations are crap.
ah spoken like many BAs we have around here.
Yes, long change lead times suck but we don't do it just because we like to piss off the business. Things like requirements gathering, design and testing (yes you should be doing it) take time.
I try and provide quick turn around Proof of Concept projects or adhoc analytics platforms when possible (budget and time constraints allowing) but some things simply need to be done properly.
I've lost count of the time the business has gone off in a huff because I've quoted 3+ weeks for delivery for a project where the requirements are vague, the required delivery date was last week and there was no budget for it.
Sometimes the business can hack together a solution that works for them and thats fine, usually however they end up crawling back when they have royally ballsed it up and someone is jumping up and down because they have provided the wrong set of numbers.
I've spent more than enough time with business users, business analysts, systems analysts, developers, the whole gamut, i've even worked in most of those positions at various times.
The person screaming the loudest is usually the person who needs it the least and understands the actual problem even less; the person calmly explaining the situation and has the relevant data to back up their assertions probably needs it the most.
And yes, I work in the financial industry, I have the joy of being told we're crap when we do absolutely everything right and no problems occur (Y2K, a 4 year project to fix) and being told we're crap when we do everything right but the spec from the business was wrong (various product releases over the years) and beign told we're crap when we ask the business to actually pay for what they've asked for (basically, everything).
I've seen change done quickly and then require three times the support and resolution time, as it wasn't tested fully.
On balance, change that goes through the thorough process requires less support once it goes into production. Years of this happening has developed the ITIL change process/
I will use an analogy:
_________________________
There is a race starting in 2 weeks time (custom built vehicles only).
There are two teams.
Team 1) The "do it right team"
Team 2) The "do it quick and do it cheap team"
Neither team has a car yet.
The Winner of the race secures finances to fund their team for another year. The loser, well, they don't.
Team 1 start deciding if they want a shiny F1 car or a Muscular Nascar and deliberate over how padded the seat should be and which direction the window winder will rotate. Oh, and what colour decals. They spend 1.5 weeks documenting what they want. They then determine it will take 14 weeks to build using a sub-contractor and that testing of the car will take another 4 weeks with some modifications made after. Total cost $4,000,000
Meanwhile Team 2 has decided to use a plank of wood with a few cheap caster wheels and some pedals. Designed in 1 day and built in 1 week. Total cost $120
Race day comes !
Team 1 say their car is "going" to be great. Fast, shiny and cool. They are disqualified for not having a car.
Team 2 show up with a pedal car to the bemusement of judges.
The Race :
Although problematic as the wheels fell of twice, but because of the simple design, fixing mid race was a doddle. Team 2 win, although not in style. But they win and secure funding.
__________________
Moral of the story.
It doesn't matter how supportable or amazing your system is if it does not deliver when needed.
Sub-Moral:
Just because you have an IT process does not mean you must doggedly follow it (ITIL). Do what is needed for your organisation. Hell, build them a sh*tty pedal car if that's what is needed to stay in business.
@skeete Actually, Team 1 spend a day looking at the requirements as presented by the business, decide the risk profile is not acceptable and do not take part in the race. They use the resources that would have been wasted on a pointless endeavour to enter a different competition, which they win. Team two lose a wheel at the first corner and crash into a wall. They're no longer in business. Risk management is important, and it's hard to do well across many disciplines.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
