Is Microsoft Excel an Adequate Statistics Package?
practicalstats.com
practicalstats.com
factories run on excel...terrifyingly so. I know semiconductor semiconductor fabs that basically run on queries in a many sheeted workbooks. The reason is because not everyone codes, especially not factory foreman, technicians, etc. They don't need a continuously running app (well they do but they don't know it) they just need formatted data.
That links to comment two. The issue that Excel solves over code goes back (IMHO) to two issues that come up with coders/noncoders. First, most users of excel are interested in the answer rather than the process. I get the value of being able to audit your steps visually, but the transition from Excel to R is a transition from the primary visual being data (i.e., results) to process. Depending on your perspective that can be highly meaningful.
Second, and probably more importantly, Excel lets you visualize your data as it progresses. For those with lower (or just different/less abstract) spatial and visual reasoning ability, seeing the progress of data from column to column can have a fairly profound effect. This extends to students who are trying to learn by seeing the progression as steps of a processing algorithm are applied. Doing so in code abstracts that process heavily. For some, especially those not used to treating data in the abstract/blind via code.
Chemical plants too. But that does not mean there is no code.
An Excel document can be programmed with VBA to make connections to network resources, read/write files, send emails, etc.
I might not be the most effective tool for every task but it's everywhere and allows workers to automate work without making a request to hire a developer or buy new software. Someone can gradually automate business tasks on their own initiative rather than go through multiple layers of corporate bureaucracy.
If someone is manipulating data in excel and erasing the original data they are not a good analyst. Further, it is very easy to add documentation of steps taken to files.
If anyone is trapped using Excel to make a box plot, it is possible (disclamer please for the love of god never do this): Make a stacked column chart. The first series should be equal to Q1 minus the graph minimum. Format this to be invisible. Series two should be median - Q1, formatted as a black outline on a white bar. Series three should be Q3 - median, formatted like series two. Error bars can be added to give the whiskers. Here's an example: https://i.ytimg.com/vi/ucWmfmXb1kk/maxresdefault.jpg
Again, don't do this.
For a histogram, just make a min and max for each bin, and use countifs(range, ">=" & min, range, "<" & max) to get the amount in each bin.
Is there a use case where you think this wouldn't be possible?
That said, I think there's always a balance between a quick down and dirty business solution that gets the job done, vs. something fully engineered. Additionally, it is much easier for business users to shoulder more of the workload while letting people with programming knowledge focus on other tasks.
The problems with Excel appear when the incoming data is "dirty". Outside the finance industry there less financial incentive to ensure data is reliably interpreted in Excel.
We used to buy in signals from a company who did their work in Excel. I wrote some scripts to export the data and recalculate it in Python. Almost every month I found errors in their reports and had to ask them to fix it.
So I recommend you fight HARD to get someone to reproduce his work in a language that is visible and reproducible.
Which is why they will not be switching to python or R any time soon and even if they do, it will still have similar issues as the Excel version.
Just because you know how to use git history, diffs, etc.. to spot differences in code doesn't mean that is going to help the layperson.
>I think it is more because of your experience as a developer why you were able to spot and correct errors.
I don't feel like I spotted errors. I wrote a script to validate the data and the script told me if there were errors (there were).
Also the people who use Excel as a primary tool are not the type that write unit tests in Python generally. Or would even think to do something like that. That is my point. You, as a developer, would think of something like that. It's not that you couldn't write similar tests with excel (you could, in any .NET language). But that you thought of doing so.
In GGGP's case they almost certainly have no tests so I stand by the recommendation that they fight hard to get a validation system in place. Probably by moving to Python and/or a RDBMS.
I hope you're paying him really well.
Note: I'm a PM on Excel
I was looking for something like this yesterday and the solutions I found were quite ugly.
Ended up having to use DSUM/DCOUNT instead which is still inelegant when one has to to multiple lookups with slightly varying parameters.
However there are other family of analytics tasks which can be summed up as "decision analysis". Taking regression models (for example, maybe even built in R) and simulating them under different inputs, getting quantiles, sensitivity, etc. This goes much further with multiple sets, multiple outcomes with decision trees (NOT regression trees/CART) and even further with solvers. Excel is the best option in those tasks most of the time because of quick data entry and already built output reports and interfaces.
"I am worried by the number of people that couldn’t tell that this was sarcasm"
[0] https://support.office.com/en-us/article/Excel-specification...
I've seen several financial shops where people were moving millions of dollars around using Excel. IMHO, it's always an indicator of deficient processes and lack of coding skill. Yes, I do know that some clever people use it. They are productive in spite of excel, not because of it.
Your main problem with excel is not that some statistical function is missing, or wrong, or misleading. Sure, that's an issue, but you can live with a few things being wrong if you can detect them and fix them yourself. I'll come back to stats later...
The problem with Excel is it's damn near undebuggable. There's simply nothing in the way of someone making a beast of a calculation, with the flow going all over several sheets. You can even make things circular if you want. The data and the code are all together, mixed up. Was a number in a given cell written there by the VB code, or was it an input? You can use the auditing functions, but chances are you will see a spaghetti of arrows.
It's also non trivial to find differences between different versions. Typically the dude who is using excel has also never heard of Git or SVN, so you will see a load of sheets like "portfolio1" and "portfolio2_new_old" and so on. I don't know if it's changed, but when I was using Excel, the files had code files within them, rather than separate files, like we do with most other languages.
Of course you aren't forced to write crappy spreadsheets, but there's simply a tendency for people who don't code to be a bit messy. But Excel is positively inviting trouble. It's so flexible that anyone under a little pressure will hack in some extra bell or whistle, building up tech debt for future generations. It basically lulls the novice into thinking they can build anything.
Amazingly I've met several people in finance who pride themselves on spreadsheets that stretch over thousands of lines. I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit (I think that's gone now, so good luck!). It's as if making things complicated justified their salaries, so maybe that's why excel is so popular.
Now, about statistics. If you're doing anything non-trivial, you absolutely do not have space to look at all the individual numbers in your matrices. Just like if you're solving equations on a piece of paper, you need your own symbolism. You need to be able to give things names and see short statements at the appropriate level of abstraction. You probably need to be able to verify the pieces independently, too. So unit tests for various operations, that aren't intruding into the business logic.
I don't work in finance (or data science really) so I can't comment, but it seems merely a tool to me.
With finance there's a lot of things that seem like good fits, but only if you keep them small. Something like a personal budget is fine, where everything fits on a screen.
Not that it matters. "Data science" is not something that magically kicks in after you go beyond some "big data" threshold.
Excel is heavily used in managerial science type of positions and I can assure you those are rather heavy on the "data science" workflows.
[0] https://support.office.com/en-us/article/Excel-specification...
Nothing beats Excel + Power BI in reporting.
For most companies, all their data science fits in excel easily. Full transaction history with each individual item sold since founding the company? No problem. Every single visitor/page view on their website? I've seen such logs imported to excel to do some rough aggregation. Manufacturing data about each particular widget sub-part that ever went off your conveyor belt? Again, for many companies with hundreds of employees that would still fit in excel without issues.
There are so many companies who speak about big data while their largest datasets can fit into RAM of a cheap laptop. That doesn't mean that data science and analysis is worthless to them, quite contrary.
Would they be more productive if you took Excel from them?
> I remember a billion dollar merger where the analyst in charge of the "modelling" showed me how they reached the line limit. It's as if making things complicated justified their salaries, so maybe that's why excel is so popular.
How should the analyst write his model in a simple way? As a Java (or Haskell, or whatever firs your ideal of simplicity) program? Or maybe he could simplify the model until he can write it down in a piece of paper.
In Python or R, like the rest of us do. Or SAS, Stata, or even SPSS if they want a familiar interface (although using the SPSS GUI instead of its scripting interface will put them at risk of the same kinds of mistakes as Excel is).
Ensuring reproducibility and robustness isn't rocket science, and it doesn't require the obscurity of Haskell or the verbosity of Java to do properly. But it does require learning to script to program their models, instead of relying on GUI (which should be perfectly natural to analysts and their long Excel formulas).
Edit: To be clear, when I say "scripting" here, I mean text-driven programming, as opposed to using a GUI interface (which is rarely robust or reproducible, and certainly isn't in plain Excel).
Why in the world would Python or R be inadequate for that type of work, if that's what you're implying? What do you think they lack that Excel has, other than a friendly GUI interface? Or SAS, Stata, or SPSS (all of whom have versions that they market heavily toward finance).
Models that take up entire spreadsheets are not models. There is never enough data to validate the sheer number of degrees of freedom that such models contain. There would be things like sub-models of entire divisions of firms. Who even has data that could validate all the potential things that could happen? These spreadsheets are pure ludic fallacy; advisors pretending they know how adding or removing some employees will affect some merger.
Yes, I agree. And I think Excel is an appropriate tool for that job.
1. if you shift cells (ctrl c, ctrl v) around, delete or insert rows, you may mess up existing cell references in formulas without realizing it. your vlookups, hlookups will not change your column numbers just because you did. your vba code will not change your A1 cell references. things will blow up here in spectacular fashion.
2. if you have a massive spreadsheet with a lot of lookups, UDFs, non-static cell values (like a Bloomberg real time feed), it's not so clear which UDFs in which cells get calculated in what order. sometimes it results in #value errors, which is a million times more desirable than if there was a iferr(..., 0) or iferr(..., "") and you can't tell if there was a failure.
3. your macros will happily destroy your work if you let it by mistake (writing over formulas in the wrong sheet etc). python will generally not destroy the code it's running.
4. AUTOFORMAT will destroy, without any honor or humanity, any data if it just barely looks like it should be something else. I've had strings get converted into dates, 0s get stripped off (I think geneticists also face that same issue), all kinds of nonsense.
some of the problems I see raised here in HN (such as errors in formulas, sanity checking) are also issues in other tools like scipy, matlab. common errors in these languages are off-by-one matrix references, terminating loops prematurely (esp for numerical solutions), formulas not written correctly, brackets in the wrong place or + instead of -, typos in variable names, nan versus 0 vs na, these are things that affect excel equally.
otherwise, excel is pretty good. it's quick to prototype, it gives passable charts if all you need are passable charts. it's very good at displaying intermediate results. it's pretty ok for WSYWIG presentation, formatting, especially if you have custom reports to produce every week rather than regular ones that tex can solve. the biggest thing about excel is that everyone uses excel and if you try to send over results in a non-excel format they'll (clients or whoever) ask you to send it back in xls.
oh also... mediocre workers can produce excel sheets of passable quality. mediocre works may not even produce a single scipy script of any quality. I have seen some horrific matlab code, written by people with engineering background. i've seen one guy, in his desire to make a programming language look just like excel, write a single line for each of the 50 charts he creates and calls them Chart1, chart2, chart3, rather than use a for loop even though it's just 2 lines. it's totally bizarre.
Also, the folks running R should pay Matlab/Mathworks to give them a workshop on writing help files.
If you're not using tables yet, you should. They solve that problem easily, and make addressing more explicit (relative to the table name rather than the sheet)
=VLOOKUP(value, Sometable, MATCH("Column2", Sometable[#Headers]))Well, the similar can be said about authors of that article (don't know about newer version of Excel though).
Basically they are saying "Of course, it all appears only to old versions of Excel but there were sooo much problems with them".
So, what?
""" Solution #2: Alternatives to Excel Yalta (ref 1) states that p-values [inverse probability distributions] reported by the free OpenOffice’s Calc spreadsheet and the open-source Gnumeric spreadsheet do not have the same numerical problems as does Excel - their programmers used accurate algorithms."""
It is not surprising because with an open source program everyone who can program can fix such issues, while with Microsoft you are at the mercy of the likely overworked Excel team.
[meta: If you want to quote, use two spaces at the beginning of every line, see https://news.ycombinator.com/formatdoc]
Yep, that's the theory behind open source applications. The reality is that in a company, people will prefer Excel because Microsoft is a point of contact that can work with, blame, or yell at to fix because you're paying them. With OpenOffice or LibreOffice, sure, you could have your engineering department fix it, or they could work on the software you need for your business.
The answer was - "never". They wouldn't have listened and so it was not only pointless, but it was actively frowned upon!
You can purchase a rather less expensive support contract with Collabora. You don't have to build or fix the software yourself.