Excel warriors who save governments and companies from spreadsheet errors • Hacker News Board

Re: Excel warriors who save governments and companies from spreadsheet errors

#51

Post by johnrgrace » Sat, Oct 17, 2020, 2:17 AM UTC

hermitcrab wrote: post #22

jgilias wrote: post #19

Excel is a great case study. Turns out, if you give people an accessible and widely available Touring complete environment, they will manage to do pretty much anything with it. On another note, it's interesting to observe that the problems spreadsheets have are the same problems that plague poorly designed software everywhere - lack of documentation, lack of testing, lack of input validation, among others.

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…

A well designed high complexity spreadsheets are pretty easy to tell what's going on. The problem is most people don't put thought into how they design their spreadsheets. That's not their fault because there are very few resources that teach people how to design a complex spreadsheet well.

If you have not seen a spreadsheet with say dedicated data input tabs, calculation tabs where you can see and audit the work being done, output tabs, revision notes, summary sheet you haven't seen a good spreadsheet.

Re: Excel warriors who save governments and companies from spreadsheet errors

#52

Post by sciurus » Sat, Oct 17, 2020, 2:23 AM UTC

askvictor wrote: post #27

hermitcrab wrote: post #22

Earlier quoted context omitted.

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…

Not just Excel; Google Sheets too. Our school system has student codes in the format ABC0001 where ABC is the first three letters of the surname, and the number is sequential. So a student with a surname Martin might get a code MAR0004. Which Sheets, in it's wisdom, decides to interpret as the date March 2004. Go figure.

Dates can be weird just about anywhere.

I recently hit a bug where all data was being checked to see if it was a datetime, and python interpreted `st1` as September 1st of the current year.

https://github.com/mozilla/frost/pull/330

Re: Excel warriors who save governments and companies from spreadsheet errors

#53

Post by m3nu » Sat, Oct 17, 2020, 2:38 AM UTC

Not surprised. I modeled company finances in my first internship. We would check the numbers for plausibility, but I'm certain most were still wrong.

For anything serious, I'd probably choose Python/Pandas/Jupyter these days for better reproducibility.

Re: Excel warriors who save governments and companies from spreadsheet errors

#54

Post by onlyrealcuzzo » Sat, Oct 17, 2020, 2:40 AM UTC

johnrgrace wrote: post #51

hermitcrab wrote: post #22

Earlier quoted context omitted.

One of the big issues with spreadsheets is the lack of visibility. It is very hard to tell what is going on in any non-trivial spreadsheet. That something that visual data flow tools such as Alteryx, Knime and (my own) Easy Data Transform try to address. The other problem is specific to Excel. It just loves to mangle your data! The problem is so bad that geneticists have renamed some genes to stop Excel reinterpretin…

A well designed high complexity spreadsheets are pretty easy to tell what's going on. The problem is most people don't put thought into how they design their spreadsheets. That's not their fault because there are very few resources that teach people how to design a complex spreadsheet well. If you have not seen a spreadsheet with say dedicated data input tabs, calculation tabs where you can see and audit the work bei…

I have literally never seen this. Do you have any links?

Re: Excel warriors who save governments and companies from spreadsheet errors

#55

Post by pyromine » Sat, Oct 17, 2020, 2:46 AM UTC

hermitcrab wrote: post #20

1123581321 wrote: post #9

Earlier quoted context omitted.

Alteryx appears to be considerably more expensive, though. Shouldn’t it take a tool of comparable cost to replace Excel? Especially in self-serve use. I’m not disputing the quality of the tool, to be clear. It seems like a good change and a totally reasonable line item. Just questioning what it will take to actually replace Excel in a widespread way. https://www.alteryx.com/products/platform-details/pricing

My own Easy Data Transform software ( https://www.easydatatransform.com ) does a fair amount of what Alteryx does and is just $99 (one time fee). So cheaper than Excel!

How is this better than power query? I know this has a visulaization of the transformations, but that's on the roadmap for PQ.

Re: Excel warriors who save governments and companies from spreadsheet errors

#56

Post by dwaltrip » Sat, Oct 17, 2020, 3:04 AM UTC

DavidPeiffer wrote: post #28

Excel suffers from being accessible to "everyone", and having a low barrier of entry. While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious t…

> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case.

Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.

Re: Excel warriors who save governments and companies from spreadsheet errors

#57

Post by gegtik » Sat, Oct 17, 2020, 3:32 AM UTC

dwaltrip wrote: post #56

DavidPeiffer wrote: post #28

Excel suffers from being accessible to "everyone", and having a low barrier of entry. While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious t…

> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case. Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.

yes. Go to cell properties and you will find there is a "locked cell" property that is on by default.

When you Protect a spreadsheet, all Locked Cells become... locked.

So a typical use case is to only un-lock the cells you wish people to enter variables into, (color the cell to make it clear), and then protect the sheet so nothing else can be tampered with.

Re: Excel warriors who save governments and companies from spreadsheet errors

#58

Post by SMAAART » Sat, Oct 17, 2020, 3:33 AM UTC

Recently switched jobs. In both my old job and my new job we use spreadsheets for data-driven-decision-making.

At my old job I was driving to migrate from classic spreadsheet analysis to Google Data Studio, and I am doing the same at my new job.

There's some resistance in old dogs not wanting to learn new tricks, but for most of what we do, GDS is really impressive.

Re: Excel warriors who save governments and companies from spreadsheet errors

#59

Post by xivzgrev » Sat, Oct 17, 2020, 3:44 AM UTC

moomin wrote: post #15

I used to professionally audit spreadsheets that were used to back up million dollar decisions. I say “back up” because it was fairly clear the decisions were made independently. But they needed to tick a box saying they’d had it audited so they paid us for a two day audit. In that limited time, which included writing the report, you’ll appreciate we rarely had time to audit anything more than the top-level summary s…

I had no idea this was a thing, but makes total sense!

Re: Excel warriors who save governments and companies from spreadsheet errors

#60

Post by nhebb » Sat, Oct 17, 2020, 3:46 AM UTC

dwaltrip wrote: post #56

DavidPeiffer wrote: post #28

Excel suffers from being accessible to "everyone", and having a low barrier of entry. While I'm not an auditor, I've been the go-to Excel person at all my internships and in full time roles. I've reviewed, enhanced, and fixed hundreds for tons of different functions (HR, supply chain, manufacturing production reporting). You can build a respectable system in Excel which has built in error flags that make it obvious t…

> * Lock sheets, even without a password. 90% of the time will prevent people from breaking the spreadsheet in the first case. Is there any way to lock all cells in sheet except for certain input cells? That seems like it would prevent many errors.

I'm reluctant to pimp my own product on HN, but one of the features of Spreadspeed [1] (my Excel add-in) is a Quick Protect tool which protects the sheet but will unlock individual cells based on styling (e.g., Input style).

https://www.breezetree.com/excel-utilities