Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You shouldn’t use a spreadsheet for important work

lemire.me

Post reply on HN
Print view View original

31–40 of 200 posts

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

#31

Post by collyw »

neotrinity wrote: post #24

having worked for Investment Banking clients I can see what the author intended to address. I have seen a lot of abuse/ over use of excel/VBA I once had to write CRUD front-end with Excel/VBA activeX components retrieving data from Sybase / SQL Server databases. Which could have been easily engineered as a simple web-app. "But NO .. IT HAD TO BE A FUCKING SPREADSHEET"

Welcome to my world. We are using excel for everything here, because basically it means the user doesn't have to think. That means there are countless errors, and I get to be a data entry clerk rather than a software developer.

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

#32

Post by panarky »

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 practices and conferences!

http://www.eusprig.org/

Love their compendium of horror stories. Did you know the US Securities and Exchange Commission has weak accounting because they rely on spreadsheets?

http://www.eusprig.org/horror-stories.htm

  As we have reported in prior audits, SEC's general ledger system
  and certain software applications and configurations are not
  designed to provide the accurate, complete, and timely
  transaction-level data needed to accumulate and readily report
  reliable financial information...

  Many of the agency’s financial reporting processes are still
  manual in nature and reliant on spreadsheets and databases to both
  initiate transactions and perform key control functions.

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

#33

Post by filearts »

talkingquickly wrote: post #9
danielweber wrote: post #2

It makes programmers' heads spin, but the business world is totally used to doing all its stuff in Excel. If Excel code is hard to audit, that's means someone could write a tool to show all the calculations being performed to get to a result. I'm giving this idea away for free because I'm pretty sure someone has already done it. Writing a new program from scratch has issues, too.

I used to work in business modelling and a part of that was creating and auditing spreadsheets so complex they fell into the "this should never be a spreadsheet" category. There are several proprietary excel addons which are designed to do exactly this. Unhelpfully I can't remember the name of the ones I used but if you Google "Excel Auditing Tools" you get quite a few.

A popular addon is the ACE toolkit. I'm sure that there are many more that may simply not be available for public consumption.

I do financial modelling and our firm as developed its own formula auditing (and Excel Swiss Army knife) tool. For example, we can analyze a block of cells and have a visual overlay of which cells contain the same formulas, which cells contain formulas of different types (external file, external sheet, embedded constant, constant, etc..).

This lets us really optimize our time while giving is more confidence that we have properly reviewed an entire spreadsheet.

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

#34

Post by collyw »

protomyth wrote: post #29
dccoolgai wrote: post #22

Data in arrays, exports to CSV, facile API... what's not to love?

I remember on job where I wrote a lot of tools to load a database by reading an excel spreadsheet from perl[1]. It was amazingly fun. 1) perl does have some good libraries to read Excel files without exporting

I have this as a "temporary" solution (now three years old) for getting data into my database. OK, I am using Python, not Perl. There is nothing fun about debugging this.

The non technical users can't tell the difference between 2 2.0 ' 2.0'. Usually excel will display the same thing to them.

I recently replaced one of their excels with a web form. "Ughh,una mierda" (its shit). So I asked whast excel did that the web form didn't. Basically a bit of JS to give some copy paste functionality, and she is now a lot happier than that solution than she was with the excel.

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

#35

Post by JasonFruit »

Pxtl wrote: post #28
mistermann wrote: post #20

Earlier quoted context omitted.

> Excel made a lot of terrible decisions 20 years ago and now they're married to them I can't really think of any off the top of my head, would be interested if you could share one or two that they're permanently married to. However, I could make a very long list of easy to implement fixes & features they could add that would offer different ways of doing things than the braindead way you have to do things now, while…

Little things like how terrible and confusing their formula language is. Anything beyond simple arithmetic requires two Googles per cell. Now, replacing it with Python isn't better - any language that uses double-equals-signs is not fit for a layman-programming-platform.

Your confidence in the layman is inspiring. Why is == more difficult to understand than parentheses for function calls, for example?

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

#36

Post by seabee »

collyw wrote: post #30
jimmcslim wrote: post #17

Earlier quoted context omitted.

Yes, I agree its a bit aggressive. Ultimately it was human nature... spreadsheets don't cause apocalyptic financial crises, overzealous capitalists with spreadsheets cause apocalyptic financial crises! The author was (is?) a recruiter for quants and financial developers so he certainly had a unique perspective.

Is it just me, or does the financial system seem to be designed in precisely the opposite way of how we try to design good software? One huge intertwined web, where one small thing indirectly affects thousands of other things. Its seems to be the equivalent of a "big ball of mud" type piece of software.

You are making the mistake of thinking the financial system was designed in any meaningful way!

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

#37

Post by macintux »

protomyth wrote: post #29
dccoolgai wrote: post #22

Data in arrays, exports to CSV, facile API... what's not to love?

I remember on job where I wrote a lot of tools to load a database by reading an excel spreadsheet from perl[1]. It was amazingly fun. 1) perl does have some good libraries to read Excel files without exporting

Perl also has nice libraries for creating Excel files. Used that at my last job to generate reports from MySQL.

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

#38

Post by pinaceae »

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.

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

#40

Post by seabee »

JasonFruit wrote: post #35
Pxtl wrote: post #28

Earlier quoted context omitted.

Little things like how terrible and confusing their formula language is. Anything beyond simple arithmetic requires two Googles per cell. Now, replacing it with Python isn't better - any language that uses double-equals-signs is not fit for a layman-programming-platform.

Your confidence in the layman is inspiring. Why is == more difficult to understand than parentheses for function calls, for example?

Presumably the layman has a high-school understanding of mathematics notation.
Post reply on HN