4 ms·
Never, ever use Excel for anything other than prototyping, spot-checking data, or as a makeshift GUI while you develop a proper one. I've seen several financia
by lordnacho 10y ago
Never, ever use Excel for anything other than prototyping, spot-checking data, or as a makeshift GUI while you develop a proper one.
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.
- ZanyProgrammer 10y agoI could've sworn I've seen people who work in the financial and maybe data science industries who've praised Excel to the heavens on HN. Like on the same level as Python, R, etc. I don't work in finance (or data science really) so I can't comment, but it seems merely a tool to me.
- lordnacho 10y agoNo way, not data science. How are you going to load in millions, possibly billions of individual numbers? 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.
- blahi 10y agoI have 2.4 billion data points loaded in Excel at the moment. 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.
- ramblenode 10y agoThe maximum number of rows in the most recent version is 1,048,576 [0]. I'm deducing then that your data is in wide format. What would happen if you wanted it in long format? It seems you would just be out of luck with Excel, which is not a problem in any other major statistics software. [0] https://support.office.com/en-us/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f https://support.office.com/en-us/article/Excel-specification...
- blahi 10y agoExcept that there is Power Pivot which is xVelocity - the same columnar store database use in SQL Server.