In defense of Excel
dangoldin.com
dangoldin.com
It's intensely powerful (my boss can do spectacular things with pivot tables and teaches all the business people to do this as a means to doing their own analysis of our reports) but the high-power features have a positively nasty UI compared to the corresponding SQL query.
I'd love to see an Excel designed-from-scratch that scratches the same itches of approachability and user-friendliness but with an awareness of Excel's failings and without obsessing over the spreadsheet metaphor. Something in-between Excel and Access.
Just need to figure out where the tradeoffs are and I think Excel made a pretty good one. I think Excel shouldn't be used for recurring jobs but for the one off it can't be beat.
It was obviously a really crude approach, but it worked... i've worked at a lot of places with more mature and sophisticated approaches to ad-hoc reporting requests but they were all way more of a headache in terms of IT workload.
Of course, it didn't hurt that one of the VPs quickly became a SQL select-statement guru within the exec team.
What does fronting a database with a web service buy you? Rate throttling? Load balancing? Metering?
Why isn't there a JBDC proxy server thingie that does those things for straight JDBC?
You can leave notes in cells as documentation. I also have an instructions tab in just about every model I have that walks the user through the process. The hidden formula thing can also be partially overcome by using certain colored cells to denote formulas, making the user aware that there are formulas.
If you're really smart about it, Excel is one of the most remarkable tools for analysis ever devised. The problem is virtually everyone who is smart about it can use different better tools that are more suited to their purpose.
More than "replicate", I think the problem is no one else -- and, with enough time and complexity, not even the originator -- can maintain it, which results in Excel often being a vehicle for low-initiation-cost and rapidly-rising-maintenance-cost tools (which, in effect, often end up as straightjackets as no one wants to bear the cost of starting over, or the cost of updating, so business processes end up getting constrained by the tool rather than the tool adapting to processes.)
I proposed this idea once during a job interview; data analysts were using Excel to model data for providing short term financing. I proposed a web-based system that allowed users to share formulas over large datasets; datasets large enough that you wouldn't materialize them in spreadsheets (dropping the spreadsheet metaphor mentioned above).
Yeah, that.
EDIT: My favorite example is http://carywalkin.ca/download-arena-xlsm/
It's fine if you want to keep a small budget or a quick bit of work with numbers, but it's awful for dealing even modest amounts of data and here's why:
Nothing you do is captured and everything is destructive.
Any time someone edits their spreadsheet they are irreparably altering the data without real documentation. Excel encourages moving, shaping, dragging, and futzing that may be easier, but ultimately can cause major problems because there's no audit trail. Ever try and replicate something modestly sophisticated that was done in Excel? Good freaking luck.
There's nothing wrong with Excel for some basic graphs and calculations. The problem is when people start to use Excel as a data base or as a serious tool for analytics (like some of those business folks).
Excel is as ubiquitous as it is because the mental model of a spreadsheet is very simple and easy to wrap one's brain around. Here's a grid of cells, type stuff into them. Boom, you're an Excel user. (It can do a lot more than that, of course, but many many many people never realize that and still get lots of value out of it. Which is kind of remarkable, really.)
Proper databases are much more powerful, but none of them present the user with an interface whose basic metaphors are as immediately obvious as Excel's. You have to learn about tables, data types, relations, etc. to get anything done in them. Most of that stuff comes up with Excel too, but you don't need to know it to get started. The Excel user's first baby steps are much smaller than the database user's are.
Given a choice between a flawed tool that's immediately understandable and a perfect tool that requires learning, people will always choose the flawed-but-understandable tool. So they go with Excel.
I think much of the explosion in data science jobs these last few years isn't really about "big data", but rather the fact that your typical corporate analyst does not have the experience to tackle mid range data analysis jobs because the only tool they have under their belts is Excel. What really is needed is a larger number of people with experience in statistics, scientific computing (Python, R etc..), and database systems. The current tools we have are fine, they just need to be used properly (well, we should always strive to improve our tools, but you get the idea).
Do you think Excel with built in version control would help in solving the problem? I remember reading about some company that was trying to do that but no idea where they are now.
Every tool has a scope that it has to be used on. If you use Excel in situations where you need team collaborations, stability, or other really advanced stuff, you're doomed.
However, if you just need to check a quick hypothethis, or mock up a solution, it's really the best tool for the job.
I guess our fund isn't "hardcore":)
I think the finance industry runs on Excel.
I work in aeronautics and everyone who works on a computer maintains some Excel spreadsheets. It's everywhere but so few people know how to use formula, pivot tables, etc, that it has become a real competitive advantage to master them.
You did remind me of this amusing paper from a few years ago though: http://www.biomedcentral.com/1471-2105/5/80
Tableau is great for visualizations but if you want to do anything on your own servers it is prohibitively expensive and there are other alternatives out there. Not sure it provides the same capability as Excel for analysis.
88% Of excel spreadsheets have errors - I couldn't find the actual study after a quick googling. http://www.marketwatch.com/story/88-of-spreadsheets-have-err...
Recent national debt study found to contain erroneous calculations; http://www.nextnewdeal.net/rortybomb/researchers-finally-rep...
To my mind the key point in the article is lack of facilities for code review of Excel spreadsheets. Yes there are tools to help with that, but they're not widely used. If our developers routinely pushed their development code directly to production without proper review and testing, someone (probably several people) would get fired on the spot, yet important business decisions get made based on unreviewed Excel spreadsheets all the time.
Obviously this is not a serious project, but it's kind of fun. I think this is where Excel gets dangerous. To a certain type of mind (perhaps like mine), the enjoyment from forcing a non-compliant system to do your bidding is greater than the enjoyment of using the proper tool to do the job quickly and effectively.
I still don't use pivot tables. Those are too practical.
You're hacking around with something for fun.
Other people? Not so much. EG, the guy who used MS Works spreadsheet (which was not loadable by anything else) for everything, including word processing. He'd open a spreadsheet, and type text in, and get "nicely" laid out documents.
Also, coming from a Java background but doing all my recent work in Ruby (about which I feel ambivalent), coding in C# was a real pleasure. I'm more of a text-editor type, but I was also profoundly impressed with VisualStudio. Microsoft ain't all bad.
But people are bad with numbers and spreadsheets give them the power to be bad with lots of numbers very quickly.
Spreadsheets are tricky to audit, and often they're not audited because they're just a tool that some guy uses without anyone knowing.
I've mention the EU SPreadsheet Risk Interest Group before, but here's the link for people who haven't seen it (http://www.eusprig.org/)
Panko has a nice website with some information about human error and the things that people do with spreadsheets (http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm)
If you like Excel and pivot tables you might like PowerPivot, a free addon from MS. Here's some information about it: (http://www.powerpivotpro.com/what-is-powerpivot/)
EDIT: I like some of the horror stories here (http://www.eusprig.org/horror-stories.htm), eg
> The London 2012 organising committee (Locog) confirmed on Wednesday that a decidedly unsynchronised error in its ticketing process had led to four synchronised swimming sessions being oversold by 10,000 tickets.
[...]
> Locog said the error occurred in the summer, between the first and second round of ticket sales, when a member of staff made a single keystroke mistake and entered ‘20,000’ into a spreadsheet rather than the correct figure of 10,000 remaining tickets. The error was discovered when Locog reconciled the number of tickets sold against the final layouts and seating configurations for venues, and began contacting ticket holders before Christmas.
I'm not even singling out spreadsheets - thus the link to Panko and "human errors".
But fat-finger errors are very easy to make with spreadsheets, and very hard to find. This error, (20,000 instead of 10,000) was only found when LOCOG realised they didn't have enough seats for all the tickets they'd sold.
Spreadsheets are particularly tricky because people scatter input data among all those cells; Sometimes data is manually copied and pasted (which can either update automatically with outher changes, or not, depending how it's cut and pasted); they then manipulate those cells with formulas (sometimes across multiple sheets) and then they spit out a number. Or sometimes not even a number but a bar on a chart. Few people bother looking at the input or at the formulas used, they just see the output and sometimes apply a brief sanity check.
> You could argue that there should have been error handling or data validation, but most custom software doesn't do that either.
Most people don't write custom software. Spreadsheets give people a very powerful tool. Most people don't have the skills to protect themselves from that power - they don't know about error handling or data validation and spreadsheets make it hard to provide those sanitation and checking tools.
When I read that Eusprig literature on spreadsheet errors a few years ago, it seemed to me of rather low quality. Certainly many spreadsheets contain errors, but what are we comparing this to? Software in general contains errors. Custom app software—the only alternative to spreadsheets in most cases—tends to be of poor quality and is very expensive compared to spreadsheets. So it's far from clear that spreadsheets are a bad tradeoff.
Programmers take it for granted that "proper" software is better than spreadsheets, but I think that is mostly bias. Intellectually and technically, spreadsheets are low-status. Among users, on the other hand, they're high-status, for very good and deep reasons.
I'm also biased. I think that these problems need to be solved by innovation within the spreadsheet space itself.
This is also the advantage that Google Docs Spreadsheets have over Excel. The data is cloud-hosted and automatically keeps a revision history, allowing non-technical users to collaboratively edit without having to learn about version control.
But most companies don't allow their employees to upload company data into Google Docs, and with good reason, especially in light of the NSA spying scandal.
1. All the code is open source; you can host on your own servers if you want.
2. If you want to host on your own servers and you're a commercial outfit, you probably want a support contract.
3. If you don't want to be bothered with that and you don't mind about the NSA, you can use it as a cloud service on our servers with a minimal version for free and a commercial version for a subscription fee.
The concern with the NSA, if they have one, is usually the perception that customer/client information that they hold internally being abused.
On the other hand, as soon as you e-mail an Excel spreadsheet outside to anyone outside your corporate intranet, the NSA can see that data too, if they want.
The JP whale provides a great just-so story for regulators who want to take powerful, flexible tools like excel out of analysts hands. Give it another 10 years, Excel will not have the ubiquity it enjoys today in financial institutions.
If I had to generate these plots every week then I could script it easily with R, but in a fast-paced environment excel has its place