The Importance of Excel

One of the amusing/annoying things I've learned when working with business/finance people is how the spreadsheet seems for them to be a freeform tableau with which to conveniently display an assortment of calculations, as opposed to a structured data format.<p>For example, I'm inclined to list financial data in this somewhat-normalized format in an Excel spreadsheet:<p><pre><code> Apples 1/10/2013 40 $50 Oranges 1/12/2013 12 $200 Apples 1/15/2013 30 $80 Oranges 2/1/2013 10 $40 Pears 2/2/2013 50 $100 Pears 2/9/2013 20 $40 </code></pre> However, people I've partnered with, and who most definitely (I think) have a better grounding in financial math than I do, might structure their spreadsheet like this:<p><pre><code> Apples 1/10/2013 40 $50 Oranges 2/1/2013 10 $40 Apples 1/15/2013 30 $80 Oranges 1/12/2013 12 $200 Total Apples: 70 $130 Total Oranges: 22 $240 Pears 2/2/2013 50 $100 Pears 2/9/2013 20 $40 Total Pears: 70 $140 </code></pre> (you can imagine the bespoke text-formatting/cell-coloring that also ends up as part of the spreadsheet)<p>While I understand that their priority is to not care about data processing...not only is this format extremely annoying to machine parse, but it seems unwieldy for <i>any</i> professional use case. When you want to add more rows to each category, you're going to run into layout problems, for one thing. And at some point, your wrists are going to get a nice dose of carpal tunnel syndrome from all the clicking-to-navigate that you end up doing.

Regular person here who learned to code using VBA with Excel and Access to build spreadsheets and "dashboards" (oh the suits love "dashboards"!!) to track sales for what grew to be a half-billion dollar book of insurance business. The problem with all the "professionals" is that they take forfuckingever to get anything useful and usable to you when you're trying to run an actual business. Plus, you have to sit on endless conference calls trying to explain to some "professional" why you want to see the data the way you want to see it, not the way that's easiest for them to program. And building out the specs. And following their painful changelog process. And waiting some more. And know they're getting paid more than you even though a lot of the time they leave at noon on Friday.<p>If you want people to use your "professional" code, learn something about how to get it done now. The people who are making it happen right now don't have time for all this. Not everything needs to be engineered - not even the small chunks of the global financial system I have dealt with.

Why exactly should you be afraid when a turing complete language is used to do computation by people who do not understand more complicated languages?<p>I do not see the point of the article.<p>In fact, we should instead celebrate that excel is used for so many things that can be better automated - just think about the business opportunities!<p>EDIT: and yes I read the article, and it specifically points to a coding mistake. When I work with excel (sometimes I still do!) I create references to the cells holding the formulas, and feed them some known input and compare the results to what I expect - if the computations do not match the formula may have been overwritten so I print a "ERROR DETECTED" in a special "TEST" field next to the cells holding values.<p>Congratulations - you have a test suite!<p>And that's just one way. Sometimes when I have to do a different implementation I just keep the previous implementations of the same algorithm somewhere on the sheet, use the same input and substract the results - any non zero value should get you worried enough to display an error message. This is interesting for new edge cases that your test suite could miss, and especially useful if the initial algorithm was vetted in some way.<p>Congratulations - you have added regression !<p>All this is easy to do, just a copy/paste special away. Even better- you can <i>teach</i> that process to non coders and they will see how useful it is, because anyone who has used excel for non trivial things has been exposed to such problems and is weary of excel limits.<p>The tool is not the problem. Software development is a process, and if you do a poor implementation, without tests, without regression testing, guess what it will bite you - but it would be the same in C, perl or python if you try to do without <i>at least</i> tests.<p>TLDR : There are many ways to make excel more robust. Maybe the "experts" are not really experts if they have never been exposed to copy-paste induced errors and never had to take care of such errors.

Traders don't care. Traders are the profit-generating people. <i>They</i> are the rockstars in banks. Programmers are tools to help them achieve their goals. When traders want something that helps them make money, they want it now. Try explaining code maintainability to them. If they don't like you, you're gone and another keen programmer with an interest in finance will take your place.<p>I find that most people that program excel are most likely quants that just want to get the job done, do something to allow the bank to generate bigger profits.<p>Disclaimer: I work in one of these banks.

Say what you will about Microsoft, but Excel is amazing.<p>I'm no genius, but during my corp years in $LARGE_PHONE_COMPANY the excel macros I was able to write, interfacing with SAP-HR/BW and HP's Quality Center, was a great time-saver and made a lot of things very stream line and less error-prone. All the COM objects that are available to VBscript create crazy possibilities that, really, you could start a whole company just writing macros to stream-line work for other corps.<p>I really should look into that kind of thing in Open/LibreOffice now that I'm more at home in Linux.

Raymond Panko has some interesting writing about spreadsheet errors.<p>(<a href="http://panko.shidler.hawaii.edu/ssr/mypapers/whatknow.htm" rel="nofollow">http://panko.shidler.hawaii.edu/ssr/mypapers/whatknow.htm</a>)<p>The European Spreadsheet Risks Group has some 'horror stories' (<a href="http://www.eusprig.org/stories.htm" rel="nofollow">http://www.eusprig.org/stories.htm</a>)<p>It's an interesting field of research, and you could probably make a bit of money if you can audit unintentional human error; deliberate human deceit; or software errors.

The fault is not in Excel, which is an excellent piece of software. All the problems identified are the fault of poor math.<p><i>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,<p>“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><p>It's not like programmers are immune from making such errors. You can put the wrong equation into a page of C or Java as you can onto an Excel spreadsheet.

Excel is fantastic if you picture it as the World's Best Visual REPL. There's no doubt that it's overused, but it does occupy a very particular sweet spot. In fact, I think one of the best use cases for something like Light Table is to act as a spreadsheet killer.

We software guys complain about this all the time, but Excel completely permeates the corporate world because it:<p>- is universally available (who in an office building DOESN'T have an Office license?)<p>- can be "Programmable" to the extent that it needs to be. You don't have to start with code. Formulas and conditionals are great for most things. People usually ease into Macros gently.<p>- produces a format that is sharable. People "fork" Excel spreadsheets all the time. It's not pretty but it works.<p>- gets the job done. You want data entry with some calculations and maybe a few if-then rules here and there? What's better than Excel?<p>In a corporate environment, often the best way to get things done is to <i>circumvent</i> the official software and just write something that works. When <i>we</i> do it, we call it an elegant hack, but when guys in suits who went clubbing last night do it, we call it a terrible, amateur travesty that should be replaced by PROPER code as soon as possible.<p>And you know what, eventually that happens. Very rarely, an incredibly useful Excel spreadsheet will be replaced by an even more useful (and reliable) piece of custom software that also adds tons of value to both the users and the organization. But I've worked in corporate consulting for years; can you guess how often this happens? I'd wager it's less than 40%.<p>No, what typically happens is that an analyst or software dev notices someone's cool spreadsheet and says "hey, I can make something that does this job, but it'll be a LOT faster and I'll put the data up in the cloud and multiple people can access it at once and..."<p>And that sounds great, so they get a little budget and a project is born. Most of us who have been there and done that know what happens next: higher-level stakeholders get involved, broader objectives get defined, more team members are brought on, timetables are established, results are "metricized", paradigms are going to be shifted, etc.<p>Rarely does a piece of software escape from this process that is as genuinely useful as the spreadsheet which spawns it. Often, rather, it gets delivered 6 months late. It crashes all the time. What used to be one simple input field is now a whole page with checkboxes you have to check and sub-objects you have to define. The end result might look a little prettier but that cool Infragistics graph is locked inside the program and can't be shared like the old Excel report because no one hooked up the "export feature". People are getting upset. Everyone hates this program. But we have to use it, it's mandated by corporate.<p>Meanwhile, a talented new guy comes on the team and notices what a bloated piece of crap this software is. He wonders why no one has written a little Excel sheet to get around it and REALLY get some work done...<p>I know I'm being cynical. And look, I GET that rogue spreadsheets can turn into productivity-damaging unseen business risks. But until the corporate "software project" culture understands why it happens and why people are often far happier with their clunky spreadsheet than with your shiny WPF app or web page, I don't think this problem is going to go away.

The thing that leaps out at me from this thread is how high-status spreadsheets are socially—many spreadsheet users have high social/business status; and how high-status they are economically—huge value flows through Excel; yet how low-status they are technically—many programmers hate them, and they have received surprisingly little serious research attention. There is an enormous market inefficiency in that gap. The question is how to exploit it.

Working in the aerospace industry for a number of years, I've seen and created all types of crazy spreadsheets to design aircraft and ensure they are safe to fly.<p>The good thing about the engineering industry though is that all those spreadsheets get checked by someone more senior than you.<p>The problem is though, thanks to the increased usage of finite element analysis and more detailed models, the amount of data being pushed around in spreadsheets has grown exponentially.<p>Far beyond the ability of someone to reasonably check it all.

My wife was analyst in a company that was took over by international corporation. It was hell of two mismatched systems (SAP and internal oracle based system).<p>They "solved" it so: each morning one person imported the data from both systems into huge excel spreadsheet (file had almost 100 MB), then she send the file to person that know how to correct all the duplicated and missing data in a few columns (it wasn't turing-complete process, it was heuristic, cause systems used different subdivisions and codes for same things, some data required to be sure how to integrate them was missing, and some data was changing without reflection in the systems - like the ingredients used to make one unit of product could change every day, and NOBODY KEPT RECORD of when and how it changed, only some people in production knew how they make the product today).<p>Then that person send the file to the next person, that knew how to fill the next few details. And so on, close to 15:00 file was supposed to be ready to send to the management. Sometimes it took till 20:00 (hours after 16:00 wasn't paid). When somebody important was on leave, nobody knew how to fill the details.<p>That spreadsheets had almost no formulas in them, cause people "cut and pasted as values" all data after calculating it (my wife was told it's to "protect the data from accidential changes"). I think it was to prevent people from seeing the errors.<p>Everybody knew that the data is all wrong. Nobody knew how to even get the correct input for most fields, it was all fuzzy (for example - one person in production knew which labels are bought in 100s, 500s, 1000s, etc. He was sent the file to fill that details, nobody could check that, and there was always some contradictions in the last pages of the spreadsheet. Most of the work went into massaging it untill it seemed OK. The file also crashed half the time, even on computers with 4GB of ram and 64bit windows.<p>I wonder, how they managed to stay in business (they are still around, and have quite big marketshare).

As far as I am aware, the business world has been using Excel for a lot of things for quite some time now, why is it suddenly something we should all be worried about? Excel in capable hands is a strong and multi-purpose software application that is proven to work. It's not merely just a spreadsheet application, Excel allows spreadsheets to be shared around and viewed by people who don't even own Excel.<p>Should we be worried about driving cars and flying in planes because they're so complicated internally errors are easily hidden? A bad mechanic will always blame his tools. There is nothing wrong with Excel and just because errors are easily hidden doesn't make it a bad program to use. I'm sure error rates are pretty low anyway.

Posted yesterday, although it attracted no comment: <a href="http://news.ycombinator.com/item?id=5193999" rel="nofollow">http://news.ycombinator.com/item?id=5193999</a>

Excel has far worse problems in the financial sector than this. Among other things Excel's thread-safety is flakey, which when you're importing a realtime price feed into excel turns out to be fairly important.<p>Most of the time your spreadsheet even if it has perfect logic will fine, until some weird race condition gets hit the price being loaded gets transposed and you model gives you a wildly wrong answer which you then trade on.

Dont blame Excel. Those people were running a scam. And from my experience with people who run banks, its actually not even a little bit out of the ordinary to have completely bullshit calculations spitting out numbers that look good.<p>The problem is that economics is not a science, and finances are designed to enable power plays. We need to apply real science and technology to problems rather than letting a bunch of suits run around scamming everyone.

<i>there’s no easy way to test spreadsheets</i><p>Sure there is... you set up a spreadsheet with a known test data set, and make sure your formulas compute the expected results. Just like testing any other software.

Spreadsheet verification and type systems have been studied extensively by the rather terrifyingly productive Martin Erwig. See <a href="http://web.engr.oregonstate.edu/~erwig/papers/abstracts.html" rel="nofollow">http://web.engr.oregonstate.edu/~erwig/papers/abstracts.html</a>, and scroll down the page.<p>"Software Engineering for Spreadsheets" "Automatic Detection of Dimension Errors in Spreadsheets" "Type Inference for Spreadsheets"<p>etc.

I used to build structured credit risk models at JPMorgan and other banks. A few observations:<p>- Excel models are usually built on a pre-existing template, probably created by someone else. Any errors/hidden assumptions carries through. This is he biggest source of errors.<p>- Any large model usually has a tab devoted to reference tables and assumptions based on external sources, a la dependency injection. Usually you have to use static numbers since you need to email the model around (you don't want your boss seeing a bunch of #NAs due to missing links. Keeping this data updated is another huge source of errors.<p>- A sanity check is your best defense. I'm positive that every CDO model I've ever built has contained at least one error, just like every large software application has at least one bug. The difference is that models have fewer output parameters, which allows you spot incongruities more easily.<p>- People who use big Excel models are not software engineers and will never be. In my opinion, solutions that implement concepts like testing, diffs, commits, etc are a waste of time, because it creates extra work and learning curve.

A lot of work goes into engineering native code Excel add-ins that supply realtime market data in complex ways to spreadsheets. The Bloomberg Excel add-in is almost an entire application unto itself. On top of that, there is an entire team of people whose only job is to help customers develop custom spreadsheets using the data. On top of that, even more tight integration is available, allowing live charts and other components to be embedded into the spreadsheets. There are many who develop really complex "apps" in Excel and would not consider themselves programmers.

I personally am not fan of Excel, even though you can do a lot in it, I prefer other programming languages. But I can see its huge popularity among the "cool" office workers. Learning the functions in Excel its pretty easy and powerful at the same time.<p>Take my uncle for example. He is mediocre office worker, selling lawn-mowers for living. He took the time and learned some advance excel functions and created few cool spreadsheets for his office, and everyone loves it. His reputation grown a lot with bosses and e is known as the guy with solutions.

At one time, the Excel team at Microsoft were considered some of the sharpest programmers at Microsoft.

This is why I strongly believe that regular people should not learn how to code, despite all the "you should learn how to code" articles.<p>Excel is a really good example of how easy it is to get up and running, and how easy it is to get yourself into a lot of trouble. You might not need to program per se, but you do need to know how to DEBUG and TEST your programs as well. This is something that most people are loathe to do. It requires a great deal of discipline, and even though in finance you're supposed to test spreadsheets during internal auditing, most auditors aren't trained in proper testing/coding, so it ends up being ad hoc. In fact, it's scary how much intermediate financial statements are rubber-stamped/robo-signed by people in the finance, usually because they are pressed for time.

Spreadsheet error commission and detection rates are almost identical to those in programming, mathematics, and writing. And professionals make about the same number of errors as novices. What is different in spreadsheet development is the amount of testing. At companies like Microsoft, 40% of development time is spent in testing, and their usually is one tester for each Dev. In spreadsheet development, professional testing is rare. Here is a link on human and spreadsheet error. <a href="https://dl.dropbox.com/u/20338447/WhyThinkingIsBadPublicCopy.pdf" rel="nofollow">https://dl.dropbox.com/u/20338447/WhyThinkingIsBadPublicCopy...</a>

From the interesting submitted article, "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 . . ."<p>This astounding story is a really nasty example of why "always comment your code" is a good idea--but only if someone else with knowledge of business logic reviews the code.

Excel and Bloomberg are the only reasons our FinTech shop has a few Windows boxes in our office--the rest are Linux. If MS Office becomes available on Linux, as rumored, we will have even fewer.<p>Quite simply, the financial world runs on Excel. For this reason we make sure all our APIs work just as well when being called from VBA as they do from Python, JavaScript, Perl, etc. It's Excel's world, we just live in it.

Many years ago I did a short contract at a FX trading bank and my job was to create a trading report.<p>While I was there I found out why the report was needed.<p>The bank had managed to misplace a couple of million dollars in a missing trade.<p>They did an audit, trying and find out what happened to the missing money and came to the conclusion someone had accidentally deleted a row from the spread sheet being used to record the trades.

Very interesting article, but I belive one of the main assumpion is "Excel makes it easy to mess it up" because it's so easy to use that most non-technical people can use it, in a non-professional, careless way.<p>My question is : would the error in the model have been caught if the model would have been written in a more technical mean, say an R script ?<p>Excel does not give any feedback if the "error" is conceptual, but so it happens with more technical programming languages and environments. Excel puts out "errors" if you write a wrong formula, the equivalent of a syntax error for a programming language.<p>On the other hand, one would think the "on your face" presentation and ease of use makes it easy for other (non technical, but skilled in finance/math) people to review the numbers and calculations.<p>I agree with the author that I the fault lies in too many people trusting blindly the model, without reviewing the numbers/calculations, but I'm not sure Excel can be pointed out as the culprit.

Have you ever worked outside of a start up with engineers at a fortune 500 company? Excel is crack for engineers.

The popularity of Excel speaks to what most common enterprise software lacks- flexibility and programmability. It's hard to construct what-if scenarios in most software.<p>In some ways, what has been the evolution of Excel in our company has been to switch to Google Spreadsheets, where you get the audit history, sharing, and version control solved, you can build web forms in less than five minutes to have people submit data, you get a decent amount of programability and web service API.

I think a big problem is reviewing, debugging and editing spreadsheets since they mostly refer to random cell-addresses instead of well thought through variable names and formulas can be hidden anywhere in the massive grid of cells. Which error would you spot by simply skimming through the code: ´E64 / B2´, or ´rateDifference / rateAverage´.<p>Most of the time you wouldn't even see the code, just a bunch of numbers on top. Being able to switch back and forth between the code and the resulting data would be a huge help. I like what Bret Victor said in his learnable programming series where he tries to build on this principle <a href="http://worrydream.com/#!/LearnableProgramming" rel="nofollow">http://worrydream.com/#!/LearnableProgramming</a> (Great read btw if you haven't read it already.)<p><pre><code> &#62; One of the all-time most popular programming models is the spreadsheet. A &#62; spreadsheet is the dual of a conventional programming language -- a language &#62; shows all the code, but hides the data. A spreadsheet shows all the data, but &#62; hides the code. Some people believe that spreadsheets are popular because of &#62; their two-dimensional grid, but that's a minor factor. Spreadsheets rule because &#62; they show the data.</code></pre>

I'm not a huge TDD person, but do appreciate the value of tests around software. This may be a dumb question, but are there any testing tools for Excel? I don't think people would write tests <i>before</i> adding/modifying data and such, but... are there any testing tools that allow you to ensure that your macros and data do what you're expecting, even after changes?<p>I do not by any means write enough tests for my code, but I have some projects where I've got tests around parts of code, and occasionally I catch regressions when I make changes because of a run of the tests (if I had more tests I'd probably catch more over time).<p>I've never seen anyone writing custom functionality in Excel that even understood the idea of tests or test/sample data. That is probably a bigger issue with Excel - it encourages people to just throw stuff together and 'start hacking' - with live data, often the only copy of said data - and not understand the potential consequences. Of course, you've usually got a backup of yesterday's data because it's sitting in your inbox, because often Excel files are just emailed around to people, which perhaps encourages yet another sense of 'security' around the process.

It sounds to me like the problems here are very much like the problems with SW development in the early 1990s. There is a social problem: lack of an engineering professionalism and lack of interest by management in fostering such professionalism AND There is a technical problem: lack of the equivalent of a source code control system.<p>One way to at least start to approach the problem would be to put the equivalent of a SCCS into Excel. You'd be expected to checkin and checkout spreadsheets, changes would be tracked, you could diff against older versions, etc.<p>The analogy is not perfect, not least because many spreadsheets consist of a logic part, written by one person, and a data part, filled in by another. Even so, forcing a checkin/checkout model would provide an audit trail and, as has been said, storage is cheap.<p>Perhaps the second easy modification that could be made would be to make it vastly more visually obvious which cells are locked, and which cells are logic. It should be possible to look at a spreadsheet and just see the logic portion separate from the data portion. Imposing this by visualization would at least make it less likely that spreadsheets get checked into the SCCS without appropriate locking.<p>I could add more, but in all cases it seems to me the idea is: look at the problems we programmers had in the 90s, and how we solved them, and do likewise. I've suggested SCCS and syntax coloring, but one could go to talk about, for example, code commenting, or making it natural for people to name cells (make that the thing that's easy to do, and make the cell names easily visible) so that faffing around with B$3 becomes a weird thing that normal people would not bother with --- just like in coding I don't tell the compiler to use register EAX, I just use a variable name.

Have moved on to other software pastures now, but spent many years building large-scale LOB applications whose sole presentation layer was Excel. Seamless federated data sources, powerful multi-dimensional calculations, easy (re)formatting and charting, and very, very fast delivery, usually around 5-10% of that of some "killer app" from a team of developers the client couldn't afford. And around 5-10% training time, too, because say what you want about their idiocy level, users GET Excel, quickly. It is ultra-intuitive, to the extent that any technology/business tool can be.<p>Those who sanctioned trading on VaR models built on pasted data were obviously not qualified to hold their jobs. Neither are those who over-glorify or over-promote the virtues of "it ain't real unless programmers built it", and I say that as a programmer. I work with other UI tools now, but I have yet to work with a better or faster-adopted one. The ubiquity of Excel in the workplace is well earned; the poverty of its many implementations is no different from all the other ubiquitous human poverties.

I had the misfortune of working for a firm where there were lots of actuaries.<p>Every day the team leader would ask a junior to run his pricing model for a structured product.<p>The junior would duly press F9 and the macro would commence, with the screen flashing away like a strobe light.<p>24 hours later the macro would stop and the boss would return to find out the answer, which was usually wrong by an order of magnitude.

As a DBA/Developer working in the Property/Asset Management Industry for decades I can tell you that Excel will NEVER go away so long as the executives are making the decisions. When it comes to the typical executive and even high level accountant/controller/comptroller, the knowledge level and understanding of things computer related is highly restricted outside of Excel, PowerPoint And Outlook with Excel being the big daddy app among this group.<p>In my experience the typical executive user would prefer spending hours or even days working on a spreadsheet then spending even a few hours learning how to properly use some other task specific software regardless of whether or not it’s better suited for the task at hand. It’s not so much that the executives don’t understand the issues with using Excel so much as how Excel lets them do what they need and more importantly, want to do and all without having to learn a new system. The Excel model (how it works using rows &#38; columns ) is intuitive to one with an accounting mind. I’ve had executives ask me before why can’t our really expensive &#60;fill in the blank&#62; Enterprise Ready Application Do X when Excel can do the same thing and easily? This is especially true when dealing with what I term JCL, Judgment Call Logic. Unlike programming structures (If/Then, CASE, etc) , JCL is perspective based and results in a different value based on who is assessing it. This is why its “judgment” logic and not natural logic. Excel users don’t understand how you can’t code JCL into software.<p>That said there is certainly a need in the software world to do things better, smarter. The world of software engineering needs more byte artisans and less bean counters. Too often short term decisions are made with negative long terms effects, all done in order to meet some artificial deadline that has more to do with someone getting a commission for meeting a deadline then for delivering something of quality.

There has been an annual conference in London for quite a number of years all about spreadsheet mistakes and how to avoid disaster. A few years ago, some entrepreneurs from resolversystems.com built a better spreadsheet named resolver-one specifically to address the problems of the financial services industry. Essentially what it does is write code while you build a spreadsheet model. Then, when you recalculate, it runs the code. Once a model is built, it can be passed on to professional software developers to QA the code, document it, refactor it and build proper unit tests. However the spreadsheet model still appears to end users just like a normal spreadsheet model. I think this is the real solution to this problem. Use Excel to play around with numbers and do one-off analyses, but for serious business rely on a tool like Resolver One.

Excel is the best tool non programmers have in order to write code, that's why it wins. This is one of the problems startups should focus on in order to <i>change the world</i>, because making non programmers more able to program really changes the world, as Excel did, but is possible to improve over it of course.

I work on Wall st. As many others have already pointed out, excel is the bread and butter of many in investment banking and finance. The power and flexibility of excel cannot be underestimated. At the same time, with all the new regulations/Dodd Frank blah blah, there is a lot of discussions about adding "control points" around these mega spreadsheets/macros/VBAs that are used by Traders. I personally work with traders who use an Excel based addin to push trades into downstream systems.<p>Few days ago, I built a very simple VBA utility that queries an oracle database to give me relevant transaction information. They love it and I am the superstar already. Reason: this requirement was discussed with IT teams, sent to priority queue for months. After building this macro which took me 2 hours, traders got what they wanted without going through an IT budget.

Check out the concept of 'enterprise spreadsheet management' as a different way of thinking about the issue of Excel in critical business environments.<p>This perspective recognises that nothing beats Excel as a source of financial innovation and 'can-do'. The problem is that few want to recognize this essential role. The large software vendors need to demonize the issue to sell licences to their replacement solutions. The result is denial at every level.<p>Much better to appreciate the core strategic value that Excel offers and not confuse it with the essentially tactical value that an individual spreadsheet offers. (Though a tactical spreadsheet can be valuably tactical for 10 years).<p>The answer is transparency. Once one buys in to wanting to know what is going on then enterprise spreadsheet management can really help.

This is a very interesting article, but I do find it very one-sided. The problem isn't Excel, it's those who use it from a foundation of ignorance or carelessness. No doubt, human error does creep into every human activity. Even so-called experts fumble. It happens to all of us. Blaming the tool or the tool maker is pure nonsense.<p>I have used the tools mentioned in this article, Excel and Powerpoint, extensively. And I have used them for engineering and business purposes. And, yes, I have made mistakes that have cost me money. Funny enough, never when used for financial applications but rather for engineering.<p>Over time we developed a rather complex (probably a nearly a hundred columns and two-hundred rows with data and formulas) Excel workbook used to calculate timing parameters and counter reset values for a complex timing and synchronization circuit inside an FPGA. After one crank of the wheel this spreadsheet would output a table of hex values that would get plugged into Verilog code to setup the circuit. Everything worked as expected, or so we thought.<p>Then, a few weeks later, as the code was being run through test-benches (unit tests) we discovered that a corner case was broken. Being that it was hardware we immediately thought that the problem was the circuit. The code was checked and re-checked, simulated and re-simulated. After about six months of hard debugging work we discovered that the Excel spreadsheet was the culprit. We had used the ROUND() function in a calculation, when, in reality, we were supposed to use ROUNDUP(). Only one of hundreds of formulas contained this mistake. This cost us six months of engineering time and, therefore, lots of money.<p>Having suffered through something like that I still could not bring myself to blame Excel for the problem. We made a mistake. No, in this case, I made a mistake. And that was that. Blaming Excel or Microsoft would have been nothing less than moronic.<p>I have used Excel to design polyphase FIR filters, maintain state machine code, produce complex lookup table initialization values for a variety of languages, prepare data for EDA tools, process sensor data and even generate G-code to machine bolt patterns on a Haas CNC mill.<p>Oh, yes, also invoices, purchase orders and financial analysis.<p>It's a great tool. And, if you use VBA you can really make it do some amazing stuff.<p>Powerpoint is another interesting tool. Of course, the most visible application is to create slides for presentations. However, you can also access its automation layer via VBA. And, yes, Visual Basic isn't the most elegant of programming languages, but, who cares, this is about getting a job done, not about authoring monuments to computer science. Anyhow, the most interesting application I found for this was the creation of a full simulation of the physical control panel for a device. The simulation included buttons and even a pretty good simulation of a dot matrix display. It was used during a training presentation in lieu of having the actual physical device there. The menu code was pulled out of the state machine that ran the menu on the actual device (written in C but easily translated to VB). It was really cool.<p>What surprises me is that financial folks, as related by the article, don't seem to have a system in place to create "checks and balances" on spreadsheets that might actually be used for very serious financial decisions. That's odd, but I can see it happening. Startup opportunity? Maybe.

The problem isn't Excel.<p>The problem is that those who are developing complex Excel models are not necessarily taking into account the need for solid design, rigorous testing and model validation. The quant generally building an Excel model is generally thinking "how can I get this model built and running as quickly as possible?" Whereas, a software developer who is used to bugs, and the negative impact they cause, might take a more guarded and gradual pace - with the realization of the importance of testing.<p>Neither Excel nor the quants that use it in a fast, ad-hoc basis are going away. And so I believe the solution involves application of good design, QA and validation to the Excel modeling process.

Models which are at best a charicature of some reality as a sole pretense for actions are the problem and distract from the issue of what sorts of controls are appropriate in finance like position limits. Allowing banks to create their own risk models shows whose pockets regulators are in - the Banks pockets. So we talk about Excel and models and don't notice models are distractions that allow banks to do what they please until a trader blows up, when then they can blame the models instead of a lack of controls they don't want in the first place. Get real.

<i>I have a probably untestable hypothesis that, were you to come up with some measure of units of software output, Excel would be the most-used program in the business world.</i><p>Outlook would like to have a word with you...

The scary reality is most of Wall Street and The City is built on Excel. It's universal, and non-CS quants can understand it. Of course QA on these sheets is near impossible.

I make a good living replacing Excel for small businesses.

One limitation of the spreadsheet approach to rapid application development is that, in typical use* your code and data tend to be tied together. This creates a testing problem where you tend to confirm results in just one scenerio. I prefer to think of spreadhseets as electronic squared paper.<p>*while you can structure things to pull data in from other sources, in my experience people evolve a spreadsheet around existing data.

It isn't bad when they push the limits with Excel. That is normal, people tend to push the limits of the technologies they are comfortable with and use often.<p>What is bad is when they think Excel is a superior platform than some other more suitable technology simply because they have seen what it can do. I can excavate tunnels with spoons, but there is a reason we use TBMs for big projects.

Aren't there any tools to generate documentation from an existing excel mixture of formulae and scripts?<p>This might be a great opportunity for new product.

Excel is, simply, the most successful object oriented programming interface. Certainly a lot easier to work with than C++ or Java. That's also its curse; any idjit can pick it up, start typing and mousing, and end up with a couple meg of executable that runs just fine and generates extremely dubious output.

I've always wondered why someone hasn't done a Excel-&#62;C# or Excel-&#62;Java cross-compiler.

The most egregious Excel abuse I have seen was an application that had a "database" embedded in it, i.e. a hidden sheet containing lines of data that was added to, and (linearly!) searched by, macros hanging off buttons in the "app" part of the xls.

If you doubt the power of Excel, try to do the same thing in Numbers... I'll just wait here.

MS Excel is the largest Database in the world ;-)<p>I saw a power user who would put reference values in the lower cells and would change the font color to white to hide the values.

Last year they had this awesome Excel and financial modelling world championships called Modeloff. I bet you they don't make many mistakes. www.modeloff.com

So I have read all the comments and POV in this discussion and have resisted posting this link. But there is only one way to solve this problem. Accept that Excel and Spreadsheets are here to stay, you are never going to impose the controls the IT guys would like the world to live by and put in place a technology that transforms Excel into a true, secure, Enterprise application. Here is the link: <a href="http://vimeo.com/user13359040/review/48949438/7d482fb814" rel="nofollow">http://vimeo.com/user13359040/review/48949438/7d482fb814</a>

Wherefore art thou, Lotus Improv?

Excel is an incredible product, <i>especially</i> if you look at its UI.<p>- Performance is astounding. Sorting thousands of rows by color? No problem.<p>- The formula Editor. I edit the cell which holds the formula and it highlights, in color, the data ranges that provide the input for the formula. I can copy the cell, which moves the formula. I can, through drag and drop, expand the data area for input.<p>- All the little things. The Sum, etc helpers in the status bar. Autoexpand of number ranges.<p>If you sat down now and tried to replicate it, you would tear your hair out. Best piece of software ever done by MS.<p>That being said: Powerpoint is the exact inverse. What an amazing piece of sh*t.

The cost of an ERP is the main issue to stop the SMBs from using excel.

<i>"The world runs on Excel, and we should be afraid"</i><p>Ah. No SQL. No Java. No C#. No webapps. No JavaScript. No IT economy (Apple, eBay, Google, FaceBook, Oracle, SAP, etc.: they're all running on spreadsheets producing what? Spreadsheets of course).<p>Corporate drones use Excel. The corporate world represent less than % of the GDP. Hence:<p><i>"The world runs on Excel, and we should be afraid"</i>.<p>Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.