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.
Hey Skeete - As an experiment in our work, we tried out your analogy for real.
We actually had 6 teams, teams 1 to 5 were the quick-and-dirty teams.
At the first corner, sadly team 1 hadn't thought of a steering system. They went straight on, over a cliff, and they all died.
The rest made it round and got to a level crossing, where team 2 realised they hadn't thought of brakes. Sadly they ploughed under a train and all of them died horribly. Team 3 carried on past the wreckage, laughing heartily, but - due mainly to not actually finding out what was required - discovered too late there was a ford they had to cross. Their car could handle rain, even a small stream, but the volume of water was way more than they'd realised and their little car couldn't cope. They all drowned.
Team 4 almost made it, but they got a puncture. That wouldn't be so bad, but they tried to change it as they were going, which resulted in another puncture which they also tried to change at the same time. Each time they got a puncture, their fix seemed to cause another two or three punctures. Eventually they had so many punctures, none of them were sure what they were trying to fix any more, until eventually in a mass of compressed air and rubber it all blew up completely, and they all died.
To everyone's amazement though, team 5 actually made it, they just scrambled across the line as team 6 came storming up from the rear in their pretty performant and robust looking motor. Boy did team 5 look stressed!
At first, management were going to fund team 5 for next year. But they eventually decided that team 5's approach had led to four right royal fuckups already, and one fluke, so they gave Team 6 the funding. I think team 6 probably swung it when they pointed out to management that for next year they already had a pretty good car - probably decent enough to last the next few years at least - that might just need a little maintenance.
@skeete "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."
Or, as in the case of http://en.wikipedia.org/wiki/Knight_Capital_Group, what is needed to not stay in business.
"Slap-dash development under stress from non-tech managers ? That'll be USD400M sir."
Actually, we do. You don't spec out what parts of the system you want to change at will. I will also say that a good business is like a machine - it does the same thing well repeatedly. So somethings you'll want static, and others you will want dynamic. It is your job as a businessman to understand your machine well enough to know how to automate it and what controls you'd like. After all, you created the thing. If the knob is missing to control the non-existent parameter, look within, and remember that you've not even thought about it until now. You'd like to will it into existence. However, you are not a programmer, so you can't. You are delayed and then become upset with your terrible IT dept. Which is why of course you like Excel. That's good and all. By all means use it. Just don't fuck over your business with it or forget how your business runs because you didn't clean up your spreadsheets.
We IT types sit back and snicker about it. Would I make the same mistake? Sure. But I would not blame my IT dept. That's why we snicker. You see, IT stands for Information Technology. Note that last word: technology. The IT dept. is supposed to take care of the technology that you, as a businessman, use to take care of, not mess up, your information.
After all there are lots of ways of casually making local data processing, from BASIC programs to awk. So why did people, presented with literally dozens of potential alternatives chose one of the worst?
Somehow this reminds me of the time I worked in a hospital and I had an IT problem, and the bug tracker "crashed" on me claiming there weren't enough licenses.
> because it is superficially at least easy to use.
Ah, the reason for windows-as-a-server, too.
And what's this about a web form? Ha! young'uns today! I worked at a bank recently where change control was all done on the mainframe with over 300 lines of mainframe form to complete, much of which was phrased for code change/release, not infrastructure changes.
I've worked in quite a few investment banks, and it's fair to say they couldn't function without spreadsheets. Sadly, in most cases, the people that wrote the spreadsheet are long gone and the people currently use them aren't macro savy, they just know that the spreadsheet works. And when it doesn't, they call IT. And IT tell them where to shove their spreadsheets because IT can't manage them through our rigorous change management system. And after lots of management shouting at each other, eventually one of us gets lumbered with having to pull open the spreadsheet, see how it ticks, and fix it.
Spreadsheets may be the reason why the economy is as far up the creek as it currently is. Or possibly, they're the reason we're not even further into hell.
folk don't introduce all this QA checking and change management because they like it. They have it because its been demonstrated that not having it leads to big ballsups.
Perhaps people in finance and trading are so clever that they don't make big ballsups - but looking at the state of the economy I am not convinced...
yeah, crap change management and box ticking change management is pretty damn awful. I'm living with it myself at the moment. But, you know, crap anything is, well,crap. A good change management system, on the other hand, with decent workflow and notification systems is actually very helpful for efficiency, and the worst of it is that until a bunch of managers got sold on the ITIL *******s we used to have one...
Bless his little cotton socks!
They're so cute when they think they've found out something new.
Next up:
"Turns Out Object Orientation Doesn't Fix The Wheel Reinvention Thing Because It Is Still Quicker To Write New Than Find Whatever You Need Amongst The Three Thousand Classes Defined In The Undocumented, Unindexed Enterprise Library".
Spreadsheets are excellent when you need to add up some columns and rows in an array of values, where some values are not filled in yet. But they are woefully inadequate for the more complex tasks that many people use them for. It is a bit like writing in assembly language: You can do it, but the result is unreadable and even simple errors will not be caught -- neither at compile time nor at runtime.
It is not hard to write domain-specific languages for financial analysis that have build in checks such as preservation of money: You don't accidentally use the same $ twice, and you don't forget that you have them somewhere. Basically, all the money (or streams of interest payments) that get into the system is accounted for exactly once in the what comes out. This gives a safety that spreadsheets do not. You can, of course, still write nonsensical derivations, but a lot of common mistakes are caught. A bit like static versus dynamic types: The latter require you to write a lot more tests to catch bugs. This is no problem if you programs are small and simple enough, but once you get above a certain degree of complexity, you are likely to miss cases that would have been caught by a good static type system.
Also, by being designed specifically for the task, solving a problem in that language is usually easier and faster even than doing it in a spreadsheet (which is faster than doing it in Java).
Separated inputs from actions.
Can be converted to standalone programs
Turing complete (if actions change input variables and that triggers re-evaluation you've got flow control)
With decent support for variable names and import/export of their values it can be quite well documented by default.
Just a thought.
At a previous job in the finance sector, the developers were asked to fix a small portion of the spreadsheet hell that existed. Made our fix, got out of their as quick as possible.
A month after that our company hit the news headlines as projecting 70% of a certain kind of business would go bust within the next 12-18 months. Clients in that industry bailed as rapidly as possibly, regulators jumped all over it and cost the company tens of thousands in fines.
Turns out the spreadsheet authors didnt like the fixed version as it didnt provide them good stories for the news, so they 'tweaked' it, and came up with the figures for the above story. Source control was able to save the developers jobs by proving they didn't do it.
I'd rather programming was left to programmers. There are some talented excel types out there, but for every 1 theres 100 frankensteins waiting to unleash their creations on the world.
At a commodity company I was application support for the traders wanted a straight through processing system set up that would feed the trades from the third party trading system to their third party accounting system.
While I got that arranged I wrote them a little VBA app that read in a file of trades at the end of the day, changed the format so it fitted the accounting systems requirements, and fed it into the accounting system.
When the STP was delivered the traders complained because it didn't consolidate the trades.
It was switched off after a few weeks. 5 years later the VBA is still being used. I'm long gone.
Go on - downvote me for using VBA, the lowest form of programming.
"until you've actually seen an Excel spreadsheet running for multiple simultaneous users and updated in realtime from various internet and Reuters trading feeds .."
A few readers may remember Access Technologies (?) 2020 spreadsheet product, which was brave enough to run on OSes other than Micrsoft OSes.
I remember its VMS incarnation.
It had a variant called 2020 Realtime, which could readily be linked to external realtime data sources.
I saw it demoed on several occasions as intended for use in processing and displaying live factory automation data in a way which other systems at the time found difficult or inconvenient.
It was nice.
Where are they now?
[OPC, aka OLE for Process Control, would be the MS-sponsored equivalent for shop floor to desktop in the years that followed]
A quick scan of Jobserve will reveal that about 90% of (contract) listings include TDD (test-driven development) as a requirement. My experience suggests that for anything but trivial components it takes about as long to write the unit tests as it does to code the units. When you're modifying existing units and their tests, the ratio gets worse: change three lines of code in a big class, then spend the rest of the day trying to understand the test suite and use it to prove that you haven't broken anything. I'm all in favour of TDD, but it does mean that traditional software development isn't getting any quicker.
The real bane of the spreadsheet world is the model that was built by a genius, who has moved on because he was a genius, then modified by a series of other bright people, who are no longer around for similar reasons. It's hugely complex, vitally important, and totally opaque.
> then spend the rest of the day trying to understand the test suite
You're doing it the wrong way round... update the test code and you will have such a good understanding of that part of the system that it's very likely that your 3-line change will be absolutely perfect and with effectively zero risk of breaking something else.
It's not as much fun as developing production code, though...
I developed a model of an active control system in matlab that a client's engineer queried becaues there modeling gave different results. It turned out his model was in Excel and it took me the best part of a day to find and fix the nine or ten significant errors in his model. They had been using ot for some time and were completely unaware of any issues. The only reason I could fix it was because I had a reference model I could examine for comparison. It was the most opaque environment I have ever worked in and that includes debugging a multi-processor, multi-threaded, networked real-time data acquisition and control system in assembler with no ICE or debugger back in the old days.
The idea of developing anything that is complex and needs to be dependable in that environment is frightening.
Success has a zillion heroes.
Failure has a single scapegoat.
Unfortunately us IT types usually get stuck in the failure model until we can prove otherwise. As for being a hero, the IT guy rarely gets to be in that circle.
Life goes on. As the saying goes: Good, Fast, Cheap, pick (at most) two.
... fundamentally, the problem with spreadsheets is that you are trying to do matrix operations on a cell-by-cell basis. Lotus Improv had this sorted years ago. Instead of having, price in the A column, quantity in the B column and the 'C' column containing '=An*Bn', with a special case in row 1 which contains the title headings, you have columns called price, quantity and subtotal, with the single rule 'subtotal=price*quantity'.
If you want to see how a spreadsheet really should work, I exhort you to download a 30 month trial of Quantrix Modeller, the intellectual descendant of Improv, and blow your mind with it.
But it isn't just limited understanding of Excel that contributed to the financial crash. I have seen some amazingly stiff (in the technical sense) models using Monte Carlo integration (not in itself a bad thing) but which produced incredibly fragile results (where the outputs where extraordinarily, or even chaotically, sensitive to the inputs). And yes, they were used for trading decisions.
"I have seen some amazingly stiff (in the technical sense) models using Monte Carlo integration (not in itself a bad thing) but which produced incredibly fragile results (where the outputs where extraordinarily, or even chaotically, sensitive to the inputs). And yes, they were used for trading decisions."
I had not seen that term outside of modelling things like combustion and M1+ airflow.
Something tells me Excel should be *nowhere* near such an application.
Monte Carlos is used in simulating electronic circuits. It allows you to decide what tolerances on component values you need. It means you can spend less on capacitors and such like where their value is not critical to the correct operation of the circuit. And if you're going to make millions of these widgets you can save a bundle...
Not that many electronics engineers pay attention to such details in these digital chippery days.
I also use it in signal processing systems. If your sums feature feedback you can determine how sensitive to loop values and different input data your system is.
This all started before Excel was commonplace, using Lotus 1-2-3, Multiplan and their brethren.
However you can point the finger further back down the path at simple failure to grasp arithmetic basics. When an entire room full of highly paid credit analysts can't follow a simple interest expression because they don't know that multiplication/division operators take precedence over addition/subtraction, then you have a world of hurt waiting to be set down in spreadsheet cells.
#yes_i_saw_it_with_my_own_eyes
I've been through this transition, having lived with an average 17 weeks for a mainframe change that now takes a minute in Excel. But, I've seen the cons. I once dumped a multi-billion dollar corporation's inventory and sales records for 3 years to a spreadsheet. Recalculations took 30 minutes each, but in the end it was worth it. We discovered inventory problems well into the 10's of millions, even including a purchase stream that should have been stopped for a product we had discontinued. This ran to $20M a year, so it paid for all the effort! We also discovered a ton of fraud in a sales team, which only the flexibility of a spreadsheet would have allowed us to do.
I remember too a presentation to the CEO where five presenters all showed up with the basic Excel template for the financial reporting, and the CEO found 'adjustment' to the fixed assumptions in each of them. It didn't help that the presentation files all had the same name, and we couldn't find the original master on our server....scratch one CFO!
So I guess its a mixed blessing!
I was going to write something snarky about excel being a database or work-flow app - the other half of that function being carried out by email.
It is a serious problem, but perhaps something we should focus on instead is the complete failure of enterprise-grade systems to be easy enough to use or to transition to. When the cost of moving from an excel mock-up to a business logic system is too high, the spreadsheet stays.
Perhaps "enterprise" immediately involves a massive price-tag, or perhaps its just the rigorousness of enterprise logic fails when it meets real-world ad-hoc usage. I suspect the latter is the real issue. Rigour is expensive to buy and to do.
That online change request form sounds suspiciously like a banking client I've worked with, and it until recently you'd fill in the whole form using Firefox only to discover that the control that let's you submit didn't work in Firefox. it wouldn't let you save so you could open in IE6 either. You had to go fill in the whole form again.
I sat in on a meeting recently where that same client has chosen to adopt Agile development methodology. The process of adopting it involved a 2 hour meeting going over the current stitched-together process they have now and basically rebadging all their usual terms with the new terms from Agile. That's right, why bother with a new software development method if you can simple rename your all your current processes. It was difficult not to burst out laughing whilst I watched it taking place. Unfortunately my input was not requested and I'd learned with this client long ago that they prefer that we shut up and take their money!
I've never worked in the financial sector, but working for a multinational makes this all seem very familiar. My first job was as a user-end business analyst in mainframe days - back in those days, each operating unit of the company had a budget for IT changes, and it was our job to prioritise and specify changes. It wasn't an incredibly quick process, but it worked rather well. - changes had to be thoroughly thought-through including reporting reqirements. Spreadsheet use was limited as we only had 3 or 4 PCs on site to start with. Then came the Y2K issue, and all development stopped as our systems were re-written. This was the start of our dependence on spreadsheets, as there was no alternative: I (now in a different, non-IT related job) actually devised a stock control system for an emergency warehouse in Excel as it was possible to knock something up in a day or so. It had more holes than the proverbial Swiss Cheese, but it did the job. A new CEO came in, asked what the biggest drag on developing the business was, and was told lack of response to IT changes, so he made the decision to switch to off-the-peg ERP systems. Understandable, perhaps, but it wasn't necessarily the best decision: we spent waaaaay too long arguing over the specification and trying to make too many specifics to suit the way things had always been done. Result - huge overrun, massive additional cost, and a system that didn't work in the warehouse environment when we implemented it - with testing far from complete. And worse still, response times via RF terminals were significantly worse than with our previous mainframe system, and some things just weren't possible due to the intense processing requirements that weren't an issue for a mainframe. Again, all resource was devoted to this, so users developed ever-more complex spreadsheets to perform tasks they were completely unsuited for - our corporate policy of not rolling out Access except where unavoidable meant that there just wasn't an alternative. And that takes us to today - no budget for major IT issues, spreadsheets groaning beyond their capacity, not enough Access expertise to make it a serious alternative.
Change control can be effective and speedy iff it uses a risk based model. Low risk changes should whiz through, while high risk ones need to go through change control in the usual torturous manner. Every minor tweak to a stand alone system need not go through the torture chamber of central IT.
Aside from that, spreadsheets and other desktop tools are what I call 'end user computing'. The local manager and her boss sign off on the risks and, hey, more power to them if they want to take down the company running a 100million deal through untested code.
My approach was every two years do an audit of the active end user tools, sit down with the management, and decide which ones were stable enough to 'productionize' using a more traditional spec, code, test cycle. It was like mowing the grass. let it grow, then once in a while knock it back. The quants, etc actually like this, because they ended up with less to support.
Taking a holistic view to the problem generally yields good results.
...is a user expected to wait for a simple table?
There has been an RFE in the pipeline for FIVE YEARS where an analyst requested a simple table for grouping customers by sales reps so he can set up some automated reports to track their performance and ensure they are paid an apt bonus. It's a ten minute job; he doesn't even want a gui. It will take a little time to work into the reporting universes and test, but nothing major.
After 3 months of waiting he came up with a workaround using macros and spreadsheets, with a little autohotkey in the mix for good measure. It works, and admittedly is pretty impressive, but he is still screaming out for that table/tables because he knows the workaround is a sticky plaster.
He is still waiting, even though his job has changed since then he is still pushing for the enhancement. A team of people has since evolved around his macro solution.
What's wrong with this picture?
what looks like a simple request for calculating bonus to you can look like a spying move by another department. Most places I've worked thieving other peoples customers via 'reporting' as above was common.
The fact he came up with a workaround suggests that security is not what it should be. Its actually quite easy to provide users with the information they have a right to access though a DB. But that require management involvement and they like to thieve customers/salespeople or whatever helps their bonus...
He had full access to all the sensitive data to begin with, just no tables in the db to group that information in a meaningful manner.
The information we thought was "enough" 20 years ago is now sorely lacking and business users are turning to the likes of faceforce.com for their crm requirements instead of optimizing inhouse solutions, or spending crazy money on marketing agencies to crunch their numbers for them. Why? Because inhouse our top heavy IT management teams are full of the kind of generic iPhone wielding sales director types with no love of the craft and cowards who would rather spend millions on SAP so they have someone to blame when it all goes pop.
We have exactly zero web developers left after the latest purge, yet 5 'salesforce developers' (former admins that attended a 4 hour course) have suddenly appeared in the commercial teams. On higher salaries than the devs!
Soon all we will have left is an Excel helpline and a "porn police".
A FTSE 50 company thinks it is an Oracle shop.
Looking at the finance shared drive and there resides 750GB of disk in 1/2 million files.
This is also causing real problems as much of the information is linked and changing versions of Excel or the servers where the files located has an ever increasing impact.
Excel is not a legacy system - but its data is fast becoming one.
Seems to me to be a specific instance of something that happens wherever a user is allowed to make any sort of decision on how they use the IT kit given to them - they'll use the wrong tool for the job, usually because it's the quickest and easiest way of getting the job done (at the time they want it done). Like using email for file transfer. And so on.
Hello All
http://mathematics-diary.blogspot.co.uk/2011/07/k-programming-language.html
This will probably get buried in the noise/signal but as the original article is about the financial sector, has anyone come across K in the wild?
Used to do a bit of non-serious APL and J in the ancient times. The open/libre version is called Kona but does not have the big table stuff so far as I can see.
That was a real wake-up call.
90% of the money went through Excel (having moved from Lotus 123, not long before).
First virus that hit us at the time randomly swapped 6 & 9 around in some cells of a spreadsheet.
When £9,6123,457 suddenly becomes £6,9123,457 people tend to notice, but with a spreadsheet full of tabs and each tab full of money and percentages, nobody could be sure we caught them all.
I've worked in finance for a long time, and this article seems accurate to me. I particularly had to laugh at the description of a change management form copied from "primordial times" as I have seen exactly this pattern!
That's not to say that there aren't successful centralised systems in banks and the like - I've had the joy of creating a fair number. Doing so usually involves reverse engineering logic and workflow out of guts of a creaking, megalithic spreadsheet though!
In my experience, the spreadsheet jockeys are smart people, who innovate and experiment via their sheets, and eventually the sheets end up being core to some new and maturing product or process. Only when operational risk gets way too hideous does anyone dare to wrestle control of these sheets off the traders (and even then, they don't always succeed!).
The resulting projects are fraught with technical and political difficulties. If you throw sub-par outsourcing partners, guarded SMEs, and very bolshy head traders into the mix, you get a recipe for projects which are very hard to deliver (I have the grey hairs and twitch to prove it!).
Beer - because the memories have come flooding back :)
A fantastic article that shows how social technology solutions (i.e. do what I want now) will always triumph vs a centralised development model. However, it would appear that many of the readers here are unaware that the problem of change management has been largely addressed inside many leading organizations. Technology is capable of automating integrity checks every time a spreadsheet has been used. That way one gets the best of spreadsheet agility and the transparency needed to improve core systems.
Not only at trading desks, but in all industries I've worked in. Mailorder/retail fashion, entertainment, commercial real estate. Very good exposé of what actually goes on and how it got that way. The solution? There is no solution. I hate to be a naysayer, but large system development is what it is, even with rapid prototyping and other quick-development-metaphors, and PC-spreadsheet mindset, Visio, SAP-Crystal Reports, etc. are what they are, and never the twain shall meet. Something totally new will have to emerge, perhaps biological computing, that will cause systems to ~need~ to be generated from scratch because there will be no upgrade path, and that will ~permit~ new systems to be generated in an organic and graspable manner. Until then I have zero hope that the problems underscored here will be solved.
Sorry folks. but it's on their desktops and it's available *now*.
No courses
No training.
No oversight.
Today quick & dirty hack *becomes* tomorrows business *critical* infrastructure.
I'm thinking revers engineering tools to suck the BL out of a bunch of linked spread sheets and make all the logic *visible* so it can be audited, discussed and walked through.
But I won't hold my breath.
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2024
