"Mine" ?? I didn't submit one. I'm just pointing out that this is a duplicate. Sometimes people wonder why they don't get any comments, and it might be useful to know that many people may have read it recently.
We software guys complain about this all the time, but Excel completely permeates the corporate world because it:
- is universally available (who in an office building DOESN'T have an Office license?)
- 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.
- produces a format that is sharable. People "fork" Excel spreadsheets all the time. It's not pretty but it works.
- 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?
In a corporate environment, often the best way to get things done is to circumvent the official software and just write something that works. When we 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.
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%.
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..."
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.
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.
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...
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.
I've developed an ERP that replaced a set of buggy Excel sheets and nowadays processes tens of millions of euros every year. Employees of the customer organization mostly like it, despite that it has it's problems like any organically grown piece of software has.
However, the problem with custom-made ERP
software (or how they are typically developed) is that adapting them to new processes and ideas is typically slower and requires "real" programmers, where as systems like Excel that empower people without programming experience to model their processes, is much more dynamic and adaptable to needs of the business.
That's not a bad thing in high-value areas, though. This is the exact problem that the article points out - people without the skills and expertise to design and build robust, fail-obvious software are moving around sums of money that can be large enough to crash the global economy.
The problem isn't Excel - it's lack of standards and precision in financial and business software. If you build a new ERP that replaces Excel, but lets users modify the algorithms, then you will have the exact same issue as you'd have with Excel. The slower adaptation to new processes and ideas is a good thing as it means that these new processes are going to be specced correctly and hopefully tested to some degree. It's not okay for a financial institution to save a bit of time and money on their software and put client's life savings and public tax money at risk.
Of course, this is all very idealistic, and I personally have no doubt that Excel spreadsheet hacks are here to stay. Idealism will always fall down if a few bucks can be made.
"The slower adaptation to new processes and ideas is a good thing as it means that these new processes are going to be specced correctly and hopefully tested to some degree"
I disagree. Most businesses need more agility and experimentation, not less. There is a huge spectrum of businesses. It would be ridiculous to say to a startup that you need to experiment less and put safeguards to a place, so that you don't make huge losses of revenue. There are lot of traditional businesses in which downside can be guarded easily (retail, many sales organizations) but finding better ways to run the machine can give significant edge.
Most ERPs and Excel-sheet apps don't model automatic stock trading, and don't move billions of dollars without safeguards in place. Rather, they model and assist in existing human processes and the sad fact is that the ERP software is the stumbling point for rapid experimentation.
I don't see any reason why you can't have the agility of Excel with a few of the nice tools that modern IDEs have to make it easier to debug or secure a spreadsheet "app". Excel actually has a lot of features for preventing issues such as formula debugging, input validation and named ranges that make formulas more readable, but the UI is terrible and it's not very discoverable. The problem with Excel seems to be primarily that the MS desktop monopoly made it very hard for people to market better UIs for the "quick financial model" use case. The only way around that was either in B2B sales (where the economics forced you into building big ERP systems where management locked down the processes) or over the web, where we've had to wait for browser technology to reach a sufficient level of power before such a system could be built. I think since IE9, we're now at that point. It wouldn't surprise me if we now start to see a multitude of apps chip away at Excel's dominance in each of its use cases (like trello is doing for the 'lists of stuff' use case).
We (Fivetran) are doing exactly this, bridging the gap between spreadsheets and coding and making real algorithms accessible to non-programmers. The primary thing that makes spreadsheets more approachable is the live-updating, and this feature can be separated from the grid-of-cells model with a cleverly designed language. We'll be starting a private beta in the next few weeks, stay tuned.
"they model and assist in existing human processes"
Isn't that the point though - the "RP" in "ERP" is "Resource Planning" so they really are mainly focused on process oriented manufacturing or services.
If you don't need the "RP" bit of an ERP then aren't you really just left with a financials package?
I totally agree. I work for a finance team in a top tech company. As the place is full of engineers, all the time someone tries to replace Excel tool with web based dashboards. The result is that end users are complaining, that they lost all the flexibility and can't easily get the data they want.
Making nice charts and dashboards is not enough. People need the ability to process the data.
My company created this: http://www.synapseinformation.com - and is currently used by a leading UK High Street Bank (case study at the site) - it solves the multi-user data sharing problem for Excel - as well as providing automated data integrity in Excel Spreadsheets - we are looking for more users for this - comments/feedback/questions about it would be welcome ...
Depends. At my old job, they had a ton of spreadsheets in accounting. Most of them required manual data entry of stuff that we had in the database. Most spreadsheets took 1 ~ 2 days of manual data entry by a single person... per month! Even more when it was time to close a year or a quarter.
Needless to say, when upper management turned over a bit, there was a big push to get rid of all of this wasted time. (I know of at least one person from Accounting that quit because of the mandatory evenings and weekends just to get necessary things done)
Not to mention the addition of complex rules with little to no documentation. Sometimes these spreadsheets are created by someone who knows Excel macros, then they move on, leaving others that don't know anything about it to run it. Maybe new requirements come down, and they poke around it at it to make it seem like things are ok, but they miss an edge case... etc. Not that this doesn't happen in the programming world too, but in the programming world we try to tackle these issues as part of honing our craft. In (e.g.) the accounting world, it's just a necessary evil to get done and over with.
Strange... Excel talks to everything, couldn't they use ODBC, or at least NSLOOKUP from an imported report? Rarely a reason to manually re-enter, even with Excel.
Except the people managing these spreadsheets where not programmers and had probably never heard of ODBC. People outside the process generally suggest a rewrite over trying to maintain Excel spreadsheets which is generally a bad idea.
You would need to add a new user account for the database from the dba who is in another department. Something like that would require communicating with your manager who, as retric says, is non technical and probably won't buy in.
1. Accounting people were non-programmers, so they probably didn't know what ODBC was/is.
2. Rewriting them found errors in the Excel spreadsheets in some cases.
3. They weren't a Microsoft shop, so most/all of the experience was with an Open Source stack.
4. Most of the functionality was implemented as a series of views in the database, with a layer of reports on top of it.
5. It was easier to just pull them into our existing system as additional reports, and just have an option to export the report to Excel (which was already baked in functionality).
> Rarely a reason to manually re-enter, even with Excel.
I wish it was so. People using excel can get very — ahem — creative, of which I've encountered many a spreadsheet designed by some minion of hell, including (but not limited to):
- a GIS implemented with cells, one cell per bitmap image block, and excel vector drawing features, with atrocious macros doing things
- an insurance broker contract and customer database, whose records were separated by fuzzy formatting (like cell border colors and width), full of varying labels (typos and inconsistencies), and without specific cell placement for data, notably would-be primary keys.
Every single one of them should be locked down in a digital safe, guarded night and day, only to serve as last-stand honeypot tactical weaponry on sufficiently smart cyberwar adversaries, who, once spoiled by the guarding words of "Abandon all sanity, ye who enter here", would quickly have their mind cower in fear back into reptilian neurologic territory[0]. The Snow Crash noise was probably one of those file's raw data.
Are you sure that you understand the meaning of positive and negative feedback? Positive feedback leads to runaway (it adds); negative feedback is self limiting (it subtracts).
I always produce Excel outputs from my applications and it works great, stops people from pestering me for stuff like different sorting options.
The problem comes when people start to view their database as the "point of truth" for some data and then want to suck their data back into the database because the sheet is "more upto date".
This of course is a hell of a problem when different people have different versions of said spreadsheet and want to somehow merge them all back together.
One advantage of macro laden spreadsheets though is that they are often much better for gathering actual requirements than some word document put together by committee.
I use Excel to bulk edit TFS (don't ask) work items. It works by having a plugin that lets you refresh and publish your changes back to the data source. Something like this for regular data would be ideal.
For those who might ask, it's actually useful when you have a series of work items that you want to rapidly change, like the status of a project, deadline, etc. It's a lot less time consuming to add a new row to a sheet rather than having to go through a series of menus and windows to get to where you need.
In a corporate environment, often the best way to get things done is to circumvent the official software and just write something that works. When we 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....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...
The idea does have merit. On the average case it should be able to work.
A few things may be an issue with implementation, one of which would be excel macros. As it stands excel undo cannot regress past the last use of a macro. I suspect a state list may encounter a similar roadblock.
As it stands excel can be very very slow on large sets of data.
On a sufficiently complex and clean model, I've seen load times and computer slow downs which begin to make Tokyo traffic seem mild, if it doesn't just hang.
This would make it easier to blame the person who "made the mistake" of mistyping something in a spreadsheet that is error-prone by nature and used in a process without QA. Exactly what problem would that solve?
Still does not prevent revision pruning. For personal use, this is very rarely a problem. For anybody with Google Apps for Education or Business, it's a pretty frequent issue with (AFAIK) no real or good solution:
I have worked with some immensely powerful and complicated spreadsheets with entire worksheets nearly full of formulas and 10's of thousands of lines of VBA code. This includes spreadsheet driven systems trading 50-100 million dollars daily as black box systems.
Nearly always, these started as "prototype" systems that became so useful, so quickly that they end up as "production" systems.
This is the power of Excel. But...
When spreadsheets get this large and complicated they are incredibly fragile and become hugely difficult to maintain and bug fix. It is just too easy to make an accidental change and not know your spreadsheet is broken. The VaR example given in the OP is just typical. Sure, you can protect sheets but as a developer/maintainer you need access and then you can very easily break things, especially because version control with spreadsheets can be next to impossible.
The moment you start taking your spreadsheets seriously, as a business process, is the moment you need to consider very seriously recoding them as a bespoke system using conventional software development.
Excel and similar tools are never going to go away. It's just got too much utility for that. The best thing we can do is provide spreadsheet users with the same quality of tools as we ourselves are used to.
I know a startup in the Netherlands working on just that, and it's a golden business opportunity with a large untapped market!
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.
Can you imagine a spreadsheet with first-class functions, and possibly other functional features? What would such a thing look like?
Strictly speaking, Excel doesn't have functions at all, let alone first-class, since you can't define a new function using only cells and formulas. You have to go to VBA.
I think thirsteh was talking about the big models that operate at the spreadsheet layer, without VBA. VBA is imperative, but when you chain worksheet functions together, you have a more explicit, more visible way of manipulating data the way you might with a functional programming language.
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?
I do not see the point of the article.
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!
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.
Congratulations - you have a test suite!
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.
Congratulations - you have added regression !
All this is easy to do, just a copy/paste special away. Even better- you can teach 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.
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 at least tests.
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.
Because people make errors. This is well documented. For simple tasks people have an error rate of about 0.5%, but for more complex tasks people have an error rate of about 5%
So, when people are creating their own spreadsheets to get stuff done, and using that for multi-million dollar transactions, and not getting the software audited or approved, they're introducing risk to the company.
> When most people look at Tables 1 2, and 3, their first reaction is that such high error rates are impossible. In fact, they are not only possible. They are entirely consistent with data on human error rates from other work domains. The Human Error Website (Panko, 2005a) presents data from a number of empirical studies. Broadly speaking, when humans do simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities, such as writing programs, the error rate rises to about 5%. These are not hard and fast numbers, because how finely one defines reported "action" will affect the error rate. However, the logical tasks used in these studies generally had about the same scope as the creation of a formula in a spreadsheet.
Did you read it? The problem is that for all its power, quirks about Excel as a product make it very easy to make entirely hidden errors. Too much magic in the hands of unsophisticated users can lead to trouble, but in Excel's case, even experts can make mistakes and not notice.
And when components of the global financial system are exposed to such risk, that gets problematic indeed, as the article details persuasively.
I'v seen the process, and I know of what the process is in one of the Big Firms. There is almost always someone above you to check your work, because the drudgery and pain of a model is farmed of to an analyst. The associate/seniors take a look at the work, and finally there is almost always a sanity check which is not trivial.
All of the major finance firms have lots of people who've burnt their fingers with excel mistakes.
They know that their analysts and their associates can and will break something. (Heck the analysts do so regularly, I'm sure there are several analysts freaking out over REF errors right now)
The London Whale wasn't just because mistakes in excel. There were several things that broke here.
The assumption that the users are unsohpisticated in the use of excel is a bit naiive. If anything, the most arcane applications and commands of excel will likely be best known in the major banking firms.
There were several things that broke with the London Whale. And spreadsheets are not to blame for things like LIBOR fixing, or late trading on mutual funds or any of the other fraudulent practices of big banking.
But still, spreadsheets are full of normal human errors, and based on flawed assumptions, with little oversight, and they control huge amounts of money in novel instruments.
From EuSpRIG:
> "This market grew very quickly due to the ease with which it was possible to design and promulgate opaque financial instruments based on large spreadsheets with critical flaws in their key assumptions." Grenville J. Croll, 2009 [http://arxiv.org/abs/0908.4420]*
> "Complex financial instruments such as CDO’s are implemented within, and valued by, large and complex spreadsheets. CDO’s and other credit derivatives played a leading role in collapse of the global financial system”. Grenville Croll [http://arxiv.org/abs/0908.4420]*
> “Spreadsheets have been shown to be fallible, yet they underpin the operation of the financial system. If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before another ‘Black Swan’ event occurs [Taleb, 2001], causing catastrophic loss. It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution, significantly damaging the City of London’s reputation”. Grenville J. Croll, 2009 [http://arxiv.org/abs/0709.4063]*
Say what you will about Microsoft, but Excel is amazing.
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.
I really should look into that kind of thing in Open/LibreOffice now that I'm more at home in Linux.
you could start a whole company just writing macros to stream-line work for other corps
Oh, quite a lot of companies started that way. Many of them still do it exactly like that.
Worse: for a while the trend was clearly "all these Excel sheets are terrible, let's replace 'em with <SHINY_NEW_CENTRALISED_WEB_SYSTEM>!", but now established web-based software products are busy touting their wonderful Excel add-ins that seamlessly push data back-and-forth from spreadsheets.
Traders don't care. Traders are the profit-generating people. They 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.
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.
> If they don't like you, you're gone and another keen programmer with an interest in finance will take your place.
Where are they getting all this talent from? I assume they need atleast a certain degree of expertise which is incredibly difficult to get out of people right now...
This completely makes sense and probably applies to any industry (aside from companies whose main purpose is to program, of course)...but do you think there is any possibility that someone with a well-developed, bespoke software package that can be tweaked, tested, hypothesized-with in a much more flexible manner would have a significant advantage than someone armed with an Excel spreadsheet?
And if so, is the reason why no one has gone for the more bespoke route is because no one who programs is in charge of these companies, and thus does not even know that they could benefit from such an approach?
The only parallel I have some experience is in the publishing industry. No one thinks that the technical details of the CMS is important, as long as stories are visible on the Internet. But if a clunky CMS requires you to hire 10 to 20 people just to move pieces around on the frontpage (this is on top of the backend that automatically adds stories to the system and to their proper categories), then that is not a trivial cost.
And I'm sure The Verge, with its custom CMS (in Rails, it seems) has some clunkiness to deal with, but they've been able to produce varied and interesting formats for their stories on a much more everyday basis than even the New York Times (I'm not counting interactive graphics/multimedia, I mean the general story form).
The "engineering" in financial engineering does not refer to engineering as a technical profession. Rather, it refers to engineering in the sense of "engineered lumber."
Engineered lumber is when you glue a bunch of different woods together. The resulting product has different properties from any of the original woods. It is more profitable for the lumber company because it can be sold at a price premium.
Similarly, financial engineering involves packaging up multiple financial products into a more complicated product. The product can then be sold at a higher price to gullible fools, aka "customers."
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.
For example, I'm inclined to list financial data in this somewhat-normalized format in an Excel spreadsheet:
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:
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
(you can imagine the bespoke text-formatting/cell-coloring that also ends up as part of the spreadsheet)
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 any 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.
I see the latter case, and far worse, at my work every day as a mechanical engineer. What it boils down to I think is, is that this "annoying" implementation has worked fine for them for many years, and our company at least has little competition and thus little incentive to develop more efficient methods. Seeing this is painful to anyone who's done modern database processing, and I'm making inroads with my manager at improving this state of affairs, but some guys don't know any better and hate being told what to do. One coworker prefers to punch in hundreds of values into his Casio calculator at a time instead of dragging a row in Excel. He knows how to do this in Excel, I've seen it in spreadsheets he's made, but for that particular usage case an old habit dies hard.
Interestingly, Apple's Numbers tries to break people of this (perhaps unsuccessfully) by decoupling the 'sheet' with the column/row/cells grids. You can have multiple cell tables on a single page.
When I first saw this functionality I was really excited by it as a way to keep data consistent and independent of the layout. In reality I think the tooling around creating/moving/linking the cell groups is a bit awkward to use. Perhaps some day it will get there, or someone else will pick up the idea and run with it some more.
You miss the point, both Lotus Improv and Excel are an (pseudo-)endless wall of cells.
Numbers puts bounded tables on a page[0][1] instead of fitting the pages (and everything, really) into an endless table dating from Multiplan, thus solving the "hacking the cells to implement layout" problem. In this example[2] a table is actually selected, and allows for south, east and south-east extension.
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.
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.
The good thing about the engineering industry though is that all those spreadsheets get checked by someone more senior than you.
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.
Far beyond the ability of someone to reasonably check it all.
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.
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.
I just finished a 5 week long project revolving around replacing an Excel
sheet filled with a bus load of macros. Now the client has a web based solution that can use the excel sheet as an optional data import medium, but not be beholden to it.
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.
> The usual aim of spreadsheet audit is to verify correctness. There are two problems with this: first, it is often difficult to tell whether the spreadsheets in question are correct, and second, even if they are, they may still give the wrong results.
You're assuming that people know what model to use; use the correct model; implement it correctly; and use the correct inputs for that model.
Since most of these ad-hoc spreadsheets don't have any kind of specification, let alone a formal spec, it's really hard to know what they're supposed to do, and that makes it impossible to know if they're actually doing it.
And that's just one spreadsheet. Many spreadsheets are linked - you take data from some other unauditable spreadsheet for an input.
After all of that you still have users manipulating the spreadsheet. We know that users will make errors. We can minimise the errors using excellent design (excellent design isn't always used for expensive customer facing planned websites; what hope is there for semi-secret ad-hoc internal quick and dirty spreadsheets?) but even with clear labels we know that users will make errors, and that some of those errors will trickle through to future versions of this spreadsheet, and maybe other spreadsheets that depend on this one.
If you can automate (or partly automate) spreadsheet testing there's probably money to be made.
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.
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.
I generally agree with you, I really do (I code, myself, professionally, or, well, have until very recently) – but I think that your wish that everyone learn to code is missing a few things.
First, what people are doing in Excel is coding.
Second, they do it because Excel is, thus far, the best environment available for lowering the (many) barriers of entry that are perceived as blocking one from learning to "program".
Therefore, in my never to be humble opinion, we'd also be wise to try to figure out a way of helping build in testing methodologies into excel. I think it wouldn't have to be that hard. Imagine a testing mode or a test overlay that you could toggle on and off, allowing you to identify input and output cells, setting test data and expected output, etc.
The crucial thing is that the best work on this front would have to come from Microsoft, if it has to be in Excel. One possible fix would be a testing environment that reads .xlsx files – build something great, sell it to managers and MSFT will either buy it or, more likely, imitate it.
> 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
It requires first and foremost the realizations that bugs in code are frequent, normal and to be expected, no matter how smart and well-prepared you are.
And that's one thing you learn when you learn to code. The first programmers were quite shocked when they found out.
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 before 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?
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).
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.
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.
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.
I'm not normally the type to nitpick over minor grammar/semantic matters of no real import, but since this particular one touches on a key line from Shakespeare, I'm going to point out that "wherefore" in Elizabethan English means "why" and not "where". So when Juliet says "wherefore are thou Romeo", she's lamenting, "why are you Romeo" (i.e., why were you born a Montague), not wondering where he is.
"The world runs on Excel, and we should be afraid"
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).
Corporate drones use Excel. The corporate world represent less than % of the GDP. Hence:
"The world runs on Excel, and we should be afraid".
Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.
The Corporate Word does represent a smallish % of GDP. But the impact of 1) Government Departments, 2) Banks, 3) Infrastructure Providers (eg Telco's) are felt by everyone in the economy.
Quick Thought Experiment: which would be more disruptive - every YC company shutting down for a day (as-in all their services going offline and completely unavailable) or $LARGEST_BANK or $LARGEST_TELCO in your country shutting down for the same 24 hours?
>Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.
Simon and James are both best selling authors and have been blogging about economics and finance for years. Simon was the economic head hauncho at the IMF and is a professor at MIT.
I'm sure they are concerned about the traffic spike from HN...
"The world runs on Excel, and we should be afraid"
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).
Corporate drones use Excel. The corporate world represent less than % of the GDP. Hence:
"The world runs on Excel, and we should be afraid".
Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.
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.
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.
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.
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.
This is true. However, in a professional environment, it is reasonable to expect that a professional level of attention and diligence is being applied to most tasks. With a half-billion dollar spreadsheet, it's not going to take much of a rounding error to make that software engineer's salary look insignificant.
You draw a distinction between "professionals" and people who make spreadsheets. Why is that a good distinction? Shouldn't those people have some professional standards too? What are you doing if you don't have time to do a good job on something? Heck, why is it okay for you Excel programmers to not have the same set of standards that the rest of us follow?
Following all these rules of procedure and specs and change requests isn't any more fun from the other side of the table. The point of that stuff is, if the product does not conform substantially to those specifications, the person coding it is (usually) liable. Reimagine your spreadsheet-writing life with the idea that you could be sued into oblivion for any losses incurred by your code, and you might develop an all-new appreciation for testing and specifications.
But you know, maybe it's your money on those spreadsheets.
> The point of that stuff is, if the product does not conform substantially to those specifications, the person coding it is (usually) liable.
Can you point out a single case where a programmer was held personally liable for losses incurred by their code? Just one?
Libility is usually limited to willful wrongdoing and negligence, and when it involves work done by an employee, it's the employer not the employee that's held liable.
I don't think he means liable in the literal sense but in a figurative way: that permanent employee might lose out on a bonus, raise or future greenfield projects if they screw up. A consultant or contractor might not get their contract renewed or told to sod off, depending on the severity.
But you're right about the financial liability: I've never heard it happen; but what with Murphy's Law I'm still retaining a Liability insurance just in case it should ever happen to me.
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 . . ."
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.
But this is really the nub of the problem. By default (and most of the time), Excel hides the formulae and comments - requiring clicking into cells to figure out what's going on. By only exposing a glimpse of the whole model at a time, it's very difficult to get an overview of how the model interacts.
208 comments
[ 2.9 ms ] story [ 255 ms ] threadIs that a new elaborated form of Karma whoring?
- is universally available (who in an office building DOESN'T have an Office license?)
- 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.
- produces a format that is sharable. People "fork" Excel spreadsheets all the time. It's not pretty but it works.
- 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?
In a corporate environment, often the best way to get things done is to circumvent the official software and just write something that works. When we 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.
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%.
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..."
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.
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.
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...
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.
However, the problem with custom-made ERP software (or how they are typically developed) is that adapting them to new processes and ideas is typically slower and requires "real" programmers, where as systems like Excel that empower people without programming experience to model their processes, is much more dynamic and adaptable to needs of the business.
This is something that I'm currently working on.
The problem isn't Excel - it's lack of standards and precision in financial and business software. If you build a new ERP that replaces Excel, but lets users modify the algorithms, then you will have the exact same issue as you'd have with Excel. The slower adaptation to new processes and ideas is a good thing as it means that these new processes are going to be specced correctly and hopefully tested to some degree. It's not okay for a financial institution to save a bit of time and money on their software and put client's life savings and public tax money at risk.
Of course, this is all very idealistic, and I personally have no doubt that Excel spreadsheet hacks are here to stay. Idealism will always fall down if a few bucks can be made.
I disagree. Most businesses need more agility and experimentation, not less. There is a huge spectrum of businesses. It would be ridiculous to say to a startup that you need to experiment less and put safeguards to a place, so that you don't make huge losses of revenue. There are lot of traditional businesses in which downside can be guarded easily (retail, many sales organizations) but finding better ways to run the machine can give significant edge.
Most ERPs and Excel-sheet apps don't model automatic stock trading, and don't move billions of dollars without safeguards in place. Rather, they model and assist in existing human processes and the sad fact is that the ERP software is the stumbling point for rapid experimentation.
That is why Excel is so popular.
Isn't that the point though - the "RP" in "ERP" is "Resource Planning" so they really are mainly focused on process oriented manufacturing or services.
If you don't need the "RP" bit of an ERP then aren't you really just left with a financials package?
Making nice charts and dashboards is not enough. People need the ability to process the data.
Needless to say, when upper management turned over a bit, there was a big push to get rid of all of this wasted time. (I know of at least one person from Accounting that quit because of the mandatory evenings and weekends just to get necessary things done)
Not to mention the addition of complex rules with little to no documentation. Sometimes these spreadsheets are created by someone who knows Excel macros, then they move on, leaving others that don't know anything about it to run it. Maybe new requirements come down, and they poke around it at it to make it seem like things are ok, but they miss an edge case... etc. Not that this doesn't happen in the programming world too, but in the programming world we try to tackle these issues as part of honing our craft. In (e.g.) the accounting world, it's just a necessary evil to get done and over with.
2. Rewriting them found errors in the Excel spreadsheets in some cases.
3. They weren't a Microsoft shop, so most/all of the experience was with an Open Source stack.
4. Most of the functionality was implemented as a series of views in the database, with a layer of reports on top of it.
5. It was easier to just pull them into our existing system as additional reports, and just have an option to export the report to Excel (which was already baked in functionality).
I wish it was so. People using excel can get very — ahem — creative, of which I've encountered many a spreadsheet designed by some minion of hell, including (but not limited to):
- a GIS implemented with cells, one cell per bitmap image block, and excel vector drawing features, with atrocious macros doing things
- an insurance broker contract and customer database, whose records were separated by fuzzy formatting (like cell border colors and width), full of varying labels (typos and inconsistencies), and without specific cell placement for data, notably would-be primary keys.
Every single one of them should be locked down in a digital safe, guarded night and day, only to serve as last-stand honeypot tactical weaponry on sufficiently smart cyberwar adversaries, who, once spoiled by the guarding words of "Abandon all sanity, ye who enter here", would quickly have their mind cower in fear back into reptilian neurologic territory[0]. The Snow Crash noise was probably one of those file's raw data.
[0]: http://en.wikipedia.org/wiki/The_Funniest_Joke_in_the_World
If your users spend all their time in Excel, why not connect it to the enterprise back end?
It gives fast positive feedback.
It gives very slow, often quite spectacular, negative feedback.
The problem comes when people start to view their database as the "point of truth" for some data and then want to suck their data back into the database because the sheet is "more upto date".
This of course is a hell of a problem when different people have different versions of said spreadsheet and want to somehow merge them all back together.
One advantage of macro laden spreadsheets though is that they are often much better for gathering actual requirements than some word document put together by committee.
I suspect that individual files would become humongous with those features added.
Space gets cheaper every day.
A few things may be an issue with implementation, one of which would be excel macros. As it stands excel undo cannot regress past the last use of a macro. I suspect a state list may encounter a similar roadblock.
As it stands excel can be very very slow on large sets of data. On a sufficiently complex and clean model, I've seen load times and computer slow downs which begin to make Tokyo traffic seem mild, if it doesn't just hang.
https://docs.google.com/support/bin/answer.py?answer=95902
Nearly always, these started as "prototype" systems that became so useful, so quickly that they end up as "production" systems.
This is the power of Excel. But...
When spreadsheets get this large and complicated they are incredibly fragile and become hugely difficult to maintain and bug fix. It is just too easy to make an accidental change and not know your spreadsheet is broken. The VaR example given in the OP is just typical. Sure, you can protect sheets but as a developer/maintainer you need access and then you can very easily break things, especially because version control with spreadsheets can be next to impossible.
The moment you start taking your spreadsheets seriously, as a business process, is the moment you need to consider very seriously recoding them as a bespoke system using conventional software development.
I know a startup in the Netherlands working on just that, and it's a golden business opportunity with a large untapped market!
http://app.infotron.nl/
http://app.infotron.nl/Home/Blog
http://www.felienne.com/?cat=15
(http://panko.shidler.hawaii.edu/ssr/mypapers/whatknow.htm)
The European Spreadsheet Risks Group has some 'horror stories' (http://www.eusprig.org/stories.htm)
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.
Improving the world's most popular functional language: user-defined functions in Excel
http://research.microsoft.com/en-us/um/people/simonpj/Papers...
Strictly speaking, Excel doesn't have functions at all, let alone first-class, since you can't define a new function using only cells and formulas. You have to go to VBA.
http://qconlondon.com/london-2008/speaker/Lennart+Augustsson
http://urchin.earth.li/~ganesh/icfp08.pdf
I do not see the point of the article.
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!
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.
Congratulations - you have a test suite!
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.
Congratulations - you have added regression !
All this is easy to do, just a copy/paste special away. Even better- you can teach 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.
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 at least tests.
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.
So, when people are creating their own spreadsheets to get stuff done, and using that for multi-million dollar transactions, and not getting the software audited or approved, they're introducing risk to the company.
Can you cite the source for this data? I would like to read more.
> Consistent with Other Human Error Data
> When most people look at Tables 1 2, and 3, their first reaction is that such high error rates are impossible. In fact, they are not only possible. They are entirely consistent with data on human error rates from other work domains. The Human Error Website (Panko, 2005a) presents data from a number of empirical studies. Broadly speaking, when humans do simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions. When they do more complex logical activities, such as writing programs, the error rate rises to about 5%. These are not hard and fast numbers, because how finely one defines reported "action" will affect the error rate. However, the logical tasks used in these studies generally had about the same scope as the creation of a formula in a spreadsheet.
He has cites at his website too.
(http://panko.shidler.hawaii.edu/ssr/)
Did you read it? The problem is that for all its power, quirks about Excel as a product make it very easy to make entirely hidden errors. Too much magic in the hands of unsophisticated users can lead to trouble, but in Excel's case, even experts can make mistakes and not notice.
And when components of the global financial system are exposed to such risk, that gets problematic indeed, as the article details persuasively.
I'v seen the process, and I know of what the process is in one of the Big Firms. There is almost always someone above you to check your work, because the drudgery and pain of a model is farmed of to an analyst. The associate/seniors take a look at the work, and finally there is almost always a sanity check which is not trivial.
All of the major finance firms have lots of people who've burnt their fingers with excel mistakes.
They know that their analysts and their associates can and will break something. (Heck the analysts do so regularly, I'm sure there are several analysts freaking out over REF errors right now)
The London Whale wasn't just because mistakes in excel. There were several things that broke here.
The assumption that the users are unsohpisticated in the use of excel is a bit naiive. If anything, the most arcane applications and commands of excel will likely be best known in the major banking firms.
But still, spreadsheets are full of normal human errors, and based on flawed assumptions, with little oversight, and they control huge amounts of money in novel instruments.
From EuSpRIG:
> "This market grew very quickly due to the ease with which it was possible to design and promulgate opaque financial instruments based on large spreadsheets with critical flaws in their key assumptions." Grenville J. Croll, 2009 [http://arxiv.org/abs/0908.4420]*
> "Complex financial instruments such as CDO’s are implemented within, and valued by, large and complex spreadsheets. CDO’s and other credit derivatives played a leading role in collapse of the global financial system”. Grenville Croll [http://arxiv.org/abs/0908.4420]*
> “Spreadsheets have been shown to be fallible, yet they underpin the operation of the financial system. If the uncontrolled use of spreadsheets continues to occur in highly leveraged markets and companies, it is only a matter of time before another ‘Black Swan’ event occurs [Taleb, 2001], causing catastrophic loss. It is completely within the realms of possibility that a single, large, complex but erroneous spreadsheet could directly cause the accidental loss of a corporation or institution, significantly damaging the City of London’s reputation”. Grenville J. Croll, 2009 [http://arxiv.org/abs/0709.4063]*
etc etc.
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.
I really should look into that kind of thing in Open/LibreOffice now that I'm more at home in Linux.
Oh, quite a lot of companies started that way. Many of them still do it exactly like that.
Worse: for a while the trend was clearly "all these Excel sheets are terrible, let's replace 'em with <SHINY_NEW_CENTRALISED_WEB_SYSTEM>!", but now established web-based software products are busy touting their wonderful Excel add-ins that seamlessly push data back-and-forth from spreadsheets.
You can start with that now, and be done with it by tea time...
Since LibreOffice embeds macro writing in Python, the environment is pretty rich - even compared to the mighty Excel VBA...
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.
Disclaimer: I work in one of these banks.
Where are they getting all this talent from? I assume they need atleast a certain degree of expertise which is incredibly difficult to get out of people right now...
Turns out, it isn't a perfect description of reality.
And if so, is the reason why no one has gone for the more bespoke route is because no one who programs is in charge of these companies, and thus does not even know that they could benefit from such an approach?
The only parallel I have some experience is in the publishing industry. No one thinks that the technical details of the CMS is important, as long as stories are visible on the Internet. But if a clunky CMS requires you to hire 10 to 20 people just to move pieces around on the frontpage (this is on top of the backend that automatically adds stories to the system and to their proper categories), then that is not a trivial cost.
And I'm sure The Verge, with its custom CMS (in Rails, it seems) has some clunkiness to deal with, but they've been able to produce varied and interesting formats for their stories on a much more everyday basis than even the New York Times (I'm not counting interactive graphics/multimedia, I mean the general story form).
Engineered lumber is when you glue a bunch of different woods together. The resulting product has different properties from any of the original woods. It is more profitable for the lumber company because it can be sold at a price premium.
Similarly, financial engineering involves packaging up multiple financial products into a more complicated product. The product can then be sold at a higher price to gullible fools, aka "customers."
For example, I'm inclined to list financial data in this somewhat-normalized format in an Excel spreadsheet:
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: (you can imagine the bespoke text-formatting/cell-coloring that also ends up as part of the spreadsheet)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 any 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.
Then show them how to lock panes in Excel. Problem pretty much solved.
When I first saw this functionality I was really excited by it as a way to keep data consistent and independent of the layout. In reality I think the tooling around creating/moving/linking the cell groups is a bit awkward to use. Perhaps some day it will get there, or someone else will pick up the idea and run with it some more.
Numbers puts bounded tables on a page[0][1] instead of fitting the pages (and everything, really) into an endless table dating from Multiplan, thus solving the "hacking the cells to implement layout" problem. In this example[2] a table is actually selected, and allows for south, east and south-east extension.
[0]: http://www.file-extensions.org/imgs/app-picture/3615/iwork-n...
[1]: http://maymay.net/blog/wp-content/uploads/2008/09/example-bu...
[2]: http://farm2.static.flickr.com/1400/1050927588_8765bb65a6.jp...
The good thing about the engineering industry though is that all those spreadsheets get checked by someone more senior than you.
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.
Far beyond the ability of someone to reasonably check it all.
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.
I just finished a 5 week long project revolving around replacing an Excel sheet filled with a bus load of macros. Now the client has a web based solution that can use the excel sheet as an optional data import medium, but not be beholden to it.
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.
> The usual aim of spreadsheet audit is to verify correctness. There are two problems with this: first, it is often difficult to tell whether the spreadsheets in question are correct, and second, even if they are, they may still give the wrong results.
You're assuming that people know what model to use; use the correct model; implement it correctly; and use the correct inputs for that model.
Since most of these ad-hoc spreadsheets don't have any kind of specification, let alone a formal spec, it's really hard to know what they're supposed to do, and that makes it impossible to know if they're actually doing it.
And that's just one spreadsheet. Many spreadsheets are linked - you take data from some other unauditable spreadsheet for an input.
After all of that you still have users manipulating the spreadsheet. We know that users will make errors. We can minimise the errors using excellent design (excellent design isn't always used for expensive customer facing planned websites; what hope is there for semi-secret ad-hoc internal quick and dirty spreadsheets?) but even with clear labels we know that users will make errors, and that some of those errors will trickle through to future versions of this spreadsheet, and maybe other spreadsheets that depend on this one.
If you can automate (or partly automate) spreadsheet testing there's probably money to be made.
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.
First, what people are doing in Excel is coding.
Second, they do it because Excel is, thus far, the best environment available for lowering the (many) barriers of entry that are perceived as blocking one from learning to "program".
Therefore, in my never to be humble opinion, we'd also be wise to try to figure out a way of helping build in testing methodologies into excel. I think it wouldn't have to be that hard. Imagine a testing mode or a test overlay that you could toggle on and off, allowing you to identify input and output cells, setting test data and expected output, etc.
The crucial thing is that the best work on this front would have to come from Microsoft, if it has to be in Excel. One possible fix would be a testing environment that reads .xlsx files – build something great, sell it to managers and MSFT will either buy it or, more likely, imitate it.
It requires first and foremost the realizations that bugs in code are frequent, normal and to be expected, no matter how smart and well-prepared you are.
And that's one thing you learn when you learn to code. The first programmers were quite shocked when they found out.
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).
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.
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.
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).
Corporate drones use Excel. The corporate world represent less than % of the GDP. Hence:
"The world runs on Excel, and we should be afraid".
Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.
Quick Thought Experiment: which would be more disruptive - every YC company shutting down for a day (as-in all their services going offline and completely unavailable) or $LARGEST_BANK or $LARGEST_TELCO in your country shutting down for the same 24 hours?
Simon and James are both best selling authors and have been blogging about economics and finance for years. Simon was the economic head hauncho at the IMF and is a professor at MIT.
I'm sure they are concerned about the traffic spike from HN...
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).
Corporate drones use Excel. The corporate world represent less than % of the GDP. Hence:
"The world runs on Excel, and we should be afraid".
Perfect linkbait title. Well done. I'm sure the spike of trafic thanks to HN has been noticeable.
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.
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.
This is true. However, in a professional environment, it is reasonable to expect that a professional level of attention and diligence is being applied to most tasks. With a half-billion dollar spreadsheet, it's not going to take much of a rounding error to make that software engineer's salary look insignificant.
You draw a distinction between "professionals" and people who make spreadsheets. Why is that a good distinction? Shouldn't those people have some professional standards too? What are you doing if you don't have time to do a good job on something? Heck, why is it okay for you Excel programmers to not have the same set of standards that the rest of us follow?
Following all these rules of procedure and specs and change requests isn't any more fun from the other side of the table. The point of that stuff is, if the product does not conform substantially to those specifications, the person coding it is (usually) liable. Reimagine your spreadsheet-writing life with the idea that you could be sued into oblivion for any losses incurred by your code, and you might develop an all-new appreciation for testing and specifications.
But you know, maybe it's your money on those spreadsheets.
Can you point out a single case where a programmer was held personally liable for losses incurred by their code? Just one?
Libility is usually limited to willful wrongdoing and negligence, and when it involves work done by an employee, it's the employer not the employee that's held liable.
But you're right about the financial liability: I've never heard it happen; but what with Murphy's Law I'm still retaining a Liability insurance just in case it should ever happen to me.
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.