Live data from Hacker News

You shouldn’t use a spreadsheet for important work

lemire.me

91–100 of 200 posts

Re: You shouldn’t use a spreadsheet for important work

#91
The errors are not produced by using excel, according at lest to the financial times. But to manipulation of data.

http://www.ft.com/cms/s/2/e1f343ca-e281-11e3-89fd-00144feabd...

Other people have praised the statistical work done on the book:

- http://www.nytimes.com/2014/03/24/opinion/krugman-wealth-ove... - http://www.telegraph.co.uk/culture/books/bookreviews/1081616...

So given the controversial nature of the subject, I wonder if we could point to an objective analysis of the work.

Re: You shouldn’t use a spreadsheet for important work

#92
Let me get this straight. It is hard to review, test and maintain code in Excel workbooks, so we should instead write custom software for these tasks?!

Custom software is, to a non-hacker, the definition of "difficult to review, test, and maintain." You may as well suggest they input their data into a mystery box with some pixies inside who will crunch the numbers for them. :)

Remember, Excel (and spreadsheets in general) ARE custom software, written for this exact need, and with decades of polish and bug-fixing so that even non-wizards can use them.

I agree that we should not fly the space shuttle from an Excel macro. But I feel that suggesting economists should write software instead of use spreadsheets is not only throwing out the baby with the bathwater, but also throwing out the bathtub and the whole bathroom too. Specialty tools exist for this very use case. Let's discuss how to improve them instead of indulging our hacker instinct and reinventing a hammer every time we need to drive a nail.

Re: You shouldn’t use a spreadsheet for important work

#93
post #80

I've worked for many large financial institutions as a quant and am intimately familiar with use of spreadsheets in critical functions. I believe this is a multi-billion dollar opportunity. If someone is interested in start-up ventures in this space, I would be very interested in discussing further.

How would you overcome the very large inertial barrier that exists in that world to using something new and untested? The world of finance and investment banking is a world of networking and extreme resistance to change. There's a reason Bloomberg's suite of products sits as pretty as it does, and unless some great upheaval occurs will continue to do so for many years (it's not because the products are easy to use, developer friendly, or even good tools necessarily).

Also, you should put some contact info in your profile, or provide it in your comment, if you want to be contacted for further discussion.

Re: You shouldn’t use a spreadsheet for important work

#94
post #32

JP Morgan's $6.2 billion "London Whale" trading loss: An unnamed overworked staffer in London copied and pasted the wrong figures into an Excel spreadsheet, throwing the firm's risk models out of whack. http://finance.fortune.cnn.com/2013/01/18/jpmorgan-london-wh... Spreadsheet errors are reaching epidemic proportions globally. What we need is a transnational organization to contain the threat with research, best pra…

Nobody ever caused a bug in a piece of custom software that caused an expensive problem? Knight Capital Group? Users make mistakes, users given the power to work with computers that can cause billions of $ to be traded can cause billions of $ in losses. The solution would seem to be to limit the exposure, to put a cap on the amount of $ that a user could trade without going through some form of verification. Spreadsh…

Yeah, I don't get all the Excel-hate. You can most certainly put in tests and other safety factors; back in the 90s I built spreadsheets for tasks ranging from electricity market pricing (for a national power generation company) to interest-rate shopping (for banks to allocate expense spending). There were tests and tests of tests because the sheets were shuffling around huge quantities of money. The object was not just to arrive at the best outcome every month but to generate a defensible audit trail that referenced the canonical steps of the manual procedure.

I get that programmers hate the formulae being distributed all over the place, but this is precisely the advantage for business people; they want to be able to 'walk the heap' and follow the provenance of individual values back to the source, and they want to see things in parallel at all times. CS people lean towards theoretical provability, business people are inclined towards using statistical sampling. So I would write unit tests that would lock most of a spreadsheet while running a batch of checks on the contents of individual sub-sheets, then lock the validated sheet, unlock the next sub-sheet and copy-by-value the data into that for the next round; but I would also load up random historical batches that were known-good and make sure all the final totals matched.

Sure, spreadsheets are inefficient, but a mistake in an a very efficient process can easily lead to monstrous results. And from the domain specialist's point of view (ie the accountants/business managers), the inefficiency cost of doing things in spreadsheets is far preferable to the costs of auditing a black-box process at a later date if queries arise about its integrity.

Re: You shouldn’t use a spreadsheet for important work

#95

Wrong end of the correlation/causation pattern. Human beings make mistakes. A lot of humans use Excel. Hence a lot of errors in Excel. Would all those humans program their logic in Python - we'd have a lot of wrong calculations in Python code and an article stating to not use Python.

I am human, therefore there is no difference between coding in C and coding in Rust/Cyclone? I don't think it works that way. The surface area where you are permitted to make mistakes is completely different in language X compared to excel. In Excel, `="a"+4` is a cell error but a valid spreadsheet. In python it is an invalid program, throwing a runtime error. In haskell, it doesn't even compile.

the parent seems to be referring to survivorship bias.

Re: You shouldn’t use a spreadsheet for important work

#96

Spreadsheets ARE software, anyone arguing differently has insufficient experience in at least one of the fields to have an informed opinion. I've worked professionally in both, and the argument in this article is bunk. Spreadsheets are a different kind of software with advantages and disadvantages compared to compiled/scripted languages. How do you treat a spreadsheet as real software: * One operation per cell * Inpu…

> Spreadsheets are remarkably easy to learn to use and to use correctly compared to scripted/compiled software. I don't think this is true. IME, people who aren't primarily programmers/developers and have been using Excel for years tend to have worse practices developing Excel tools than the same people have with much less time learning, e.g., Python. Excel is much easier to learn to use to get some kind of result, p…

I largely agree with you.

Perhaps it is a difference in semantics.

It is very easy to learn to use correctly, but if you aren't taught by someone who uses it correctly (either self taught or otherwise) you're not likely to discover the best practices.

Easy to learn correctly != obvious way is correct != commonly learned correctly.

Re: You shouldn’t use a spreadsheet for important work

#97

Wrong end of the correlation/causation pattern. Human beings make mistakes. A lot of humans use Excel. Hence a lot of errors in Excel. Would all those humans program their logic in Python - we'd have a lot of wrong calculations in Python code and an article stating to not use Python.

I am human, therefore there is no difference between coding in C and coding in Rust/Cyclone? I don't think it works that way. The surface area where you are permitted to make mistakes is completely different in language X compared to excel. In Excel, `="a"+4` is a cell error but a valid spreadsheet. In python it is an invalid program, throwing a runtime error. In haskell, it doesn't even compile.

It's an oversimplification to assume the hypothetical Python implementation would have less errors. We don't know that. Some type of math errors would overlap between Excel and Python. Some types of errors would be easier to stumble into in Python than Excel.

For example, an inexperienced programmer in Python might use floating point instead of Decimal data type to add currency amounts. His programming loop to sum the amounts would be incorrect. In MS Excel, adding currency in cells correctly down to the penny is a no brainer. Sure, the Python programmer can be taught "best practices" to avoid this type of error but the point is that while Python helps eliminates some errors, it also creates new ones.

Another example is data munging. A programmer might write some Python to slurp a data file (exported from mainframe or whatever) and do some financial calculation on it. Load the values into an array some other memory structure. The problem is that the memory contents are "hidden" from sight unless the programmer uses a visual debugger or prints out all the values to inspect. The Python programmer may not notice that some values are misaligned or garbage. With Excel, the non-programmer imports the data file and he immediately scrolls through the worksheet as a sanity check. His eyeballs notice that the source data is dirty. Again, the Python "problem" can be corrected with best practices but the point still remains: different tools create different problems.

Lastly, Excel spreadsheets are easily emailed among dozens of people. You can't send ???.py programs to everyone because you can't expect all the Windows users to have the Python runtime. Spreadsheets are even sent to iPhones and tablets and you definitely can't expect easy Python deployment there. With less coworkers examining the ???.py file, it has potential for more errors compared to a scenario where everybody can participate in questioning the spreadsheet's numbers.

Our intuition says that on balance, the hypothetical Python/Haskell solution should have less errors than MS Excel but I can't confidently say I've seen any definitive proof of that.

Re: You shouldn’t use a spreadsheet for important work

#98
post #75

Earlier quoted context omitted.

> Much though some people might think that spreadsheets are not for serious work, they're being used for that right now, and that is unlikely to change. Better to figure out how to improve the tooling, rather than undertake the quixotic quest to get business people to abandon their perfectly viable programming tool. Its largely used by business people because they (1) have inadequate IT support to have things develop…

A lot of people who have access to Excel also have access to Access - and a lot of what they do would be better served in database than a spreadsheet. Yet they don't use one - there must be a reason for that, and I don't think it's IT restrictions.

This little tidbit from Chris Granger seems relevant:

"Excel is inherently observable since it doesn't have any hidden state and all values are there for you to see and manipulate. It's also direct. You change values in the grid, drag drop things, do calculations on selections, and so on. And it manages to sidestep a lot of incidental complexity; spreadsheets are timeless, without setup, and don't even have a notion of being run."

http://www.chris-granger.com/2014/03/27/toward-a-better-prog...

Re: You shouldn’t use a spreadsheet for important work

#99
post #49

(Since the blog's website is not responding, I had to read the article from google's cache[1]) The author has well-intentioned advice about avoiding MS Excel but it's misguided. The criticism fails to accommodate the reason why MS Excel was used. MS Excel is the lingua franca of non-programmers . Thomas Piketty is a trained economist, not a programmer. It's not realistic to expect Mr. Piketty to set aside months (yea…

There are plenty of much better tools that many economists use. For example, Stata. Stata allows you to view your data at any point in time in a nice table, but also to manipulate it using a very easy to learn (and yet very expressive) language that you can write interactively as well as in .do files. The economist I worked for wanted the whole process, from cleaning the data, running the regressions, outputting the tables, etc., to be in a set of .do files so we could hand over the original data files and all the code for review and replication. While building the final analysis, though, you could also work interactively to figure out what you wanted the final product to be, though of course you could log everything you did interactively so there would always be a record.

Re: You shouldn’t use a spreadsheet for important work

#100
post #68

The beauty of Excel is its ability to do many things 'well enough' in a way that is accessible to a lot of people. My dad put together his architectural plans for a new house in Excel because it is a tool with which he felt comfortable. Some people do calendars in Excel while others try and recreate a full General Ledger system. Because it works well enough, people don't see the need to invest the time in learning a…

That's why I always thought it'd be a good idea for Microsoft to expose such a semantic layer via a .NET API. When you can program an Excel sheet using the .NET languages and have access to the broader .NET libraries then all kinds of interesting possibilities start to open up. The fact is business users love Excel and a great number of CRUD applications could be easily built on top of Excel if only we had a reasonab…

I used to work on finance spreadsheets that used VBA to perform ftp, connect to https servers to download and parse xml docs, perform file renaming, etc. Horrid, but functional. If MS shipped Excel with C# as a VBA co-equal, the reliability of these kinds of "hacks" would increase ten-fold.
Post reply on HN