Many developers think they know the solution to a problem before understanding the problem. They jump to the solution, so to speak. This is exactly what HN has been doing about Excel the last few days... if you want to replace Excel, spend a year building models in it, then you'll see how it's harder to replace than teaching someone development.
Agreed. I'm a non-programmer, Excel power user for finance. The HN community seems to think that Excel is the primary cause of all these financial catastrophes and that some new software solution will prevent them going forward. In reality, the problem is user error, missing checks and balances, and oftentimes, complete lack of common sense.
An organization that doesn't have the discipline to incorporate some checks into its influential financial models or doesn't have quants who can "back of the envelope" gut check the model is going to make errors regardless of what number crunching software it uses.
I could see a consequence of taking the calculations normally in Excel formulas and making them even more blackbox so only the software engineers can check the calculations to lead to MORE errors, not fewer because its less accessible to the people who know the math.
I could go on and on about how mid-sized private equity shops like mine would never adopt some proprietary solution over Excel because then they'd be too dependent on the developer to make changes, but that's not the point.
Guess it depends on the individual use case. Escel is great to use for data stratification and basic data analysis. One of the biggest advantages of Office 2010 was, at least from my poijnt of view, the abolishment of the 64k line limit. I never got on terms with Access and having to look at huge amounts of data right out of SAP got me in trouble with this limit before.
What Excel should never ever be used for is a real application, e.g. for production planning, if there is such a system in place. I've seen myself what damage, even if it's hard to quantify the amaount, this can do. But this is not a problem of Excel per se and more a use / user issue.
Agree that this is dependant on the size of the operation. For a small machine shop doing everthing in Excel is absolutly OK. For bigger operations, well, not so much.
> the problem is user error, missing checks and balances, and oftentimes, complete lack of common sense.
You are somewhat right, but I don't think you get what we are saying. My background: I was a developer tasked with integrating cost models that were implemented completely in Excel. The problems we had were not because the organization didn't have checks, but because Excel makes it Very Hard to do such checks on a spreadsheet with sufficient complexity -- or perhaps makes it Very Easy to make it hard to do that.
It's easy to say "The model sucked because they built it poorly", but honestly that's disingenuous. The models sucked because the tool was pushed too far. There were VB functions with it which were well-written - it was the mess of calculation logic that was hard to deal with. Here are a laundry list of the types of problems I encountered, and I'll go into more detail later on what programmers want instead.
- Debugging an error (bad value, NaN, etc) meant tracing the parent formulas through several sheets, with no way to unit tests smaller calculations that were used as part of larger ones. Large amounts of stuff were done with table transforms, because Excel makes that Easy, but it was hard to understand what it was doing because of the dependencies.
- Moving input cells, or copy/pasting output tables, often meant that later versions of the sheet had an ever-changing interface of where you write your inputs and read your outputs (its API, essentially), which had to be constantly updated, and then debugged.
- Formulas that had any decision logic in them nearly never had humanly readable names, but rather were cell addresses -- does 'intermediate_calcs'!C42 refer to the Frobulated Foo, or is it the un-normalized Foo before we frobulate it?
- Results tables had frequent copy and paste errors, where they referred to the wrong sub-range of some more-detailed results page. These are really easy to do wrong, because it's hard to tell by looking at the formulas what you're referring to -- since regions and the like are quite often left un-named.
Why is this bad? What do programmers see as missing in Excel? For me, it's all about testability and source control.
In contrast, developers are accustomed to building things by aggregating smaller pieces of logic, each of which has a unique name, and code comments to describe its purpose and intended use, and with well-defined interfaces for communication between the pieces. We write tests which ensure that each little piece works right, tests that verify that we are passing them the right data, and tests that verify that the results we get match what we expect. (This sort of testability is Very Hard to do with excel.)
The code we work in are stored in text files, which means that everything we do is traceable in Version Control -- you can ensure that you have the correct version of a file by more than its checksum or modified date, and can see the logic changes which were added to fix a problem. Variables have names like "foo_frobulated" and "foo_normalized", so it's much harder to accidentally use the wrong variable for a calculation.
Many times formulas in Excel start off simple, and later become very complex in order to account for exceptional data. Code does too, but it tends to be more readable, so you can more easily notice when you make a mistake.
[edit: finished the paragraph that I prematurely posted.]
edit 2:
You make a VERY good point about the accessibility to people who know the math. The math's correctness is sometimes hard to validate when there's a mistake in the inputs -- mistakes which can be hard to find. You're correct that describing parts of it in code might make it more complicated, but I feel that the ability to more easily sanity-check the inputs, outputs, and intermediate calculations of The Maths will make for a more robust calculation tool.
Programming isn't wizardry, any more than SQL or creating complex Excel tools are. Excel presents data well, as complexity scales it's easy to introduce human errors -- whether in inputs, calculations, or outputs. Software development has mostly solved that through tests and source control, which is why we rail so hard about Excel. It would be like watching someone struggle with talking with someone remotely by spooling a Very Long wire, when you're standing there holding a cellular phone.
I think that's absolutely true. Many of the most highly compensated people I know are in the finance industry and work all day every day in excel. The rest of them are programmers.
If you had to hire people who had the domain talent and expertise of the former, and the programming skills of the later, you'd be looking at seven figures per in employee and there'd only be so many to go around at any price.
But that's what you'd need. A bespoke system that could only be effectively modified by programmers would be useless in short order no matter how great it was at launch.
