Ask HN: Do you feel Excel is dangerously overused?
I'm an MBA and I took a data analytics course where we used Python and Pandas to manipulate decently-sized datasets. Now I'm taking an optimization course and we use only Excel. However, I'm less confident in my Excel models than I ever was in my pandas-based models.
I feel like we're stretching Excel further than it really should go because it hides a lot of complexity and doesn't allow for easy testing. Couple this with the fact there doesn't appear to be a large open source movement around Excel models so fewer people can verify its correctness.
Here's another a Reddit thread that inspired me to ask this question: http://www.reddit.com/r/finance/comments/35esdl/inside_the_johns_hopkins_finance_class_thats/
37 comments
[ 0.20 ms ] story [ 536 ms ] threadExcel provides a great tool, my issue starts when too many Excel spreadsheets are being used to provide decision support, with little or no control / audit of the logic in these sheets.
This is something I have mey a lot, including in the hotel/restaurant niche, where I work. All of them use it exactly because it is easy and is insanely powerful even for newbies. On the other hand at a certain point they start to learn about the negatives of it and an application that will do what they did in Excel in a proper way, is very easy sell.
Oh, about that... Pandas is great!!!
If you're concerned, perhaps write (or get someone to write) modules for it that you can import. A lot of proprietary stuff gets done in C++ modules, from investment to HR. These modules are pretty easy to break down complexity and testing, and are defacto in my industry (banking).
Excel is, simply, not going away.
BUT
using excel for ANY business critical purpose is insanity. Some desks in some banks rely on crappy old buggy spreadsheets to track the p&l of some large transactions, it's horrible. Academics use excel models and have been bitten because the models they produce are very hard to audit and to check for bugs.
So by all mean, use excel to play around with data if you would like to, and when you get a feeling of what you want, build a clean, well documented script with python tied to a database.
But do you have source control? Diff? Three-way-diff? Unit tests? Code review? Any process at all?
Any programming language lets you hack up a solution for one-off problems; for most languages there are tools and an ecosystem to help "production" code meet higher standards. The problem isn't that Excel is commonly used, it is that there is no ecosystem to support its "production" use.
The reason it isn't done is because by the time end users care about those things enough to get around to implementing them, they're probably ready for a process that already addresses those problems with baked in functionality...e.g. an RDBMS and application layer. When there's a business case for something better than Excel there are products and services and consultants that serve that business case.
The worst I have seen was a team of several hundred people who, on $200 half-broken laptops, were running - in parallel - 500MB Excel files for hours every day, which basically did a few linear operations on about 30 variables, before joining the results in a superfile itself taking up to 20 minutes just to open on a gaming-specced desktop.
In another case, I broke down an Excel model with over 50 tabs and discovered that there were only 2 input variables. Unfortunately the CFO of that business decided to go for the ostrich strategy (after all the monster model impressed investors).
Every business I have ever worked in had some convoluted, cumbersome, buggy, opaque, human-dependent Excel processes. My co-founder and I finance our startup replacing these with tight scripts running on AWS (amongst other things) - it's low hanging fruit. We approach these things as a black box, figure out desired behaviour, and rebuild from scratch.
Excel wins all the time because it's a UI that managers are familiar with, and they are often either the consumers, or want to audit your data processes. It's got a LOT of tooling that lets non-technical users do things they shouldn't.
If you thought Python Pandas was an improvement, try learning about the relational model and how to use a decent relational database (I recommend PostgreSQL, whose error messages will teach you a few things and which has a saner type system where you CAN compare two different integer types without getting NULL). You'd be amazed how far you can go with just SQL.
You should also do so from a UNIX OS, and from the command line (using pg, editing a file then running \f blah.sql). It will take you a few hours of pain but will be worth it in added productivity and transparency over any point and click alternatives.
What you're touching on is the power of abstraction, functional vs imperative programming, and particularly how proving programs can be enormously productive. SQL is powerful for this because it is declarative and very close to mathematics (Codd brought set theory to databases).
The next steps are category and type theory and then the Haskell/Mercury/Idris/Coq rabbit hole... an unpopular one because, as someone once told me, "businesses are resilient to application errors"...
This is my nightmare. I work on a sharedrive with large Excel files that conk out Excel. We are working on building a tool to do this work but it is slow going.
Fortunately, if I remember correctly it was just read-only for monitoring. If it wasn't, I'm sorry... so so sorry.
I undertand relying on piles of crappy Excel models built by underpaid, skill-lacking interns in cash-strapped e-commerce companies where investors don't really care about the real profit margin, but... I mean, we're talking about millions of dollars of hardware and human lives here...
I'm more worried about IT culture which creates a need for such secret side projects really. That this existed and someone potentially made decisions based on that spreadsheet (no testing happened, I got the formulas and test data, but no results for comparison) is just a reflection of the priorities in that place.
That several-hundred-person Excel nightmare? It had grown from "quick and dirty" shortcuts from middle managers, none of which were still around, none of which did the work (that's why you have unpaid interns), designed to bypass the official data warehouse project. They got promoted because they delivered the processes, and nobody checked the numbers (that's another "feature" of Excel shops - "if it looks like it works, it works").
The problem is that it's hard to sell "your numbers are off" to new clients. I toyed with the idea of making it conditional: "if your profit margin is more than 5% off, you pay us"...
There is, at least in my mind, perhaps a lesson for a prospective MBA here. People are not stupid and when everyone is doing something that isn't ideal, it may be because it is good enough or even the best alternative in practice. Part of the reason there isn't a large open source community around Excel is that there good professional support is ubiquitous and Excel has commercial grade documentation in such a diversity of forms that there is something close to any user's particular needs or expectations.
http://www.dummies.com/how-to/computers-software/Software-Fo...
Rather than "Excel dangerously overused", I feel that technology is generally overused in an undisciplined unmindful inefficient way. Excel is often presented as the exemplary to such overuse.
I think that's because people who are motivated to come up with a solution to their common or more pressing problems of the day will naturally reach for the immediately accessible tools, and the more familiar the tool the better. In the usual corporate environment, Excel is already available for use.
Such people will be subjected to annual reviews that will not explicitly include any acquired or proven computer savviness. Instead they will be measured against the expectations of their job roles, which is likely not "applications developer". If they manage to create custom tools (via Excel or the many other manifestations of Microsoft Office) that have empirically demonstrated their worth to somehow become part of the production environment, then so much the better as far as the department or business is concerned (at least at first).
These people are not looking to become programmers. And besides, corporate desktops have gotten tremendously locked down since the late 1990's. It would take a fair bit of effort to make a business case to allow for some other computing environment (or tool) to be put on a corporate device. Which means experimentation would have to be done at home on their own time. Has it been mentioned that these people are not looking to become programmers?
We should put this sort of situation in perspective, and celebrate that there were and are folks willing to really learn a tool sufficiently as part of their problem solving in becoming more effective in their job roles. At some point of course the home-grown Excel-based solution will hit its limitations, which is where we as the truly expert computing professional types step in and get to work. If you really understand Excel and VBA and COM, you can really tease out quite a bit of the user story and business logic out the Excel "prototype" and come up with the more elegant solution.
And we can do this because we should be well versed at using this sort of technology in a more disciplined mindful efficient way.
Excel, on the other hand, lets you bypass a lot of these issues with programming and get to results faster in some situations. First, it is completely visual. I don't have to visualize the data, it's right there in front of me. This reduces the cognitive load on the worker. Second, I can develop iteratively and see the results immediately. Yes, you can do this in programming, but any changes require a recompile-->execute-->look at the data process that Excel includes intuitively. Third, Excel is accessible. People don't need to memorize syntax or commands, they can simply got to the formula finder and start typing in what they're looking for and excel will load the formula for them.
Can you build amazing Excel programs/spreadsheets? Yes. Can you build wonky spreadsheets that would make Bill Gates cry crocodile tears? Yes. Can you build amazing programs? Yes. Can you build convoluted programs that would send the flying spaghetti monster running in terror? Yes. There are two different problems conflated here. One problem is how do I answer a question. In some situations, especially small data ones, excel will provide a faster and more intuitive way of getting to the answer that is easily understood by the widest audience. The other problem is how do I build something that fulfills the requirements and isn't a mess. I think a better question might be what are Excel best practices that keep spreadsheets from becoming a mess.
A small business or project can start with Excel, then migrate to Access, then migrate to SQL Server for ACID compliance. But each migration step is not trivial.
I keep thinking that Kexi would be a good way to go rather than Access, but it is Linux-only.
http://www.eusprig.org/
Ray Panko also has some excellent work on this.
http://panko.shidler.hawaii.edu/SSR/
Personally: yes, spreadsheet use is scary. They're error prone; hard to audit; overly trusted. One person will create a very complex spreadsheet. If that person moves on the company is stuck with a mostly opaque blob created by someone who hasn't had any training in creating readable maintainable code. There are risks for sensitive data to be inadvertantly distributed to the wrong people.
So, a few days later, the spreadsheet came back, and the HR director was ready to process all these raises into payroll. My friend took a look at the sheet, and nothing made sense. Low performers were getting high raises, and vice versa.
After some investigation it turned out that some of the managers had sorted the spreadsheet so that they could find their employees better. But someone had screwed up, and only selected the name column. So, everything got messed up.
What was amazing that even after this was pointed out, the HR director was still pushing for the raises to go through as-is, because it would take too much time to do everything again.
So much abuse.
Xlsx is supposed to be an 'open' format but it's overly complex and a PITA to parse. Like most things produced by Microsoft, there are a ton of unnecessary and overly-complex features added on that have little/nothing to do with data/calculations.
External tooling sucks (because documentation of the format sucks). The additions that are supposed to support networking suck. The security features that are supposed to ensure read-only access suck. The file size of XLS files are unnecessarily large and not conducive to transmission over the web. If there was some way to reasonably decouple the data from all the formatting, object, validation, security, and scripting overhead it wouldn't be so bad to work with.
Unfortunately, it's the most complex data structure manipulation format that 'business types' can reasonably understand so it gets used for anything and everything that deals with data and/or calculation.