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

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

#61

Post by ptx » Sat, Oct 17, 2020, 3:51 AM UTC

sciurus wrote: post #52

askvictor wrote: post #27

Earlier quoted context omitted.

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

Not really Python as such, it seems, but rather some third party library[1] which promises "generic parsing of dates in almost any string format". This is impossible to do correctly (what is 01/02/03?) and usually a bad idea to attempt.

[1] https://pypi.org/project/python-dateutil/

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

#62

Post by ekianjo » Sat, Oct 17, 2020, 3:55 AM UTC

ourlordcaffeine wrote: post #43

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

Same where I work. Excel was replaced completely with R a long time ago. Even automated pdf reports are made using R programs. Yet people are often shocked to hear it at first.

Same here, moving away from Excel with R and Python for pretty much everything.

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

#63

Post by fouc » Sat, Oct 17, 2020, 3:59 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…

>* Cell styles, much like headings in Word, are really great but nobody uses them. They clue end users about what cells do. If my end users see an orange cell, they know it's an input they can modify.

It would be interesting if excel had some sort of internal markup language perhaps. So those orange cells could be semantically represented as or some such.

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

#64

Post by ZeroCool2u » Sat, Oct 17, 2020, 4:04 AM UTC

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

As someone who has personally had to use Alteryx in multiple projects, fuck Alteryx. What an absolute garbage piece of software.

I've already written a rant about it before and I don't have the energy to repeat myself, but do not fool yourself into thinking switching from excel to Alteryx is doing yourself any favors. You're just trading one monster for another.

Save yourself a huge amount of money, not to mention your sanity and just take the time to learn some Python, Julia, literally anything else to get the same results faster, more reliably, and not be locked into that noveau Oracle-esque nightmare.

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

#65

Post by racecar789 » Sat, Oct 17, 2020, 4:08 AM UTC

About Excel visual grid performance...I have tried many data table libraries over the years in various languages. Almost all have performance issues when reaching 100-1000+ row range (even with virtualization and other tricks enabled). I would like to know how the Excel team made their grids so fast. Scrolling is so smooth.

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

#66

Post by kfk » Sat, Oct 17, 2020, 4:17 AM UTC

ZeroCool2u wrote: post #64

NoPicklez wrote: post #4

I work at a large firm which has shifted itself from using Excel, to significantly promoting data analytics tools such as Alteryx in place of Excel. One of the MAJOR improvements from using Alteryx is that it is easier to audit and review the process. With Excel you can be down the rabbit hole of data manipulation or formula driven outputs and realise you have made a mistake but cannot find where or how. Or cannot fi…

As someone who has personally had to use Alteryx in multiple projects, fuck Alteryx. What an absolute garbage piece of software. I've already written a rant about it before and I don't have the energy to repeat myself, but do not fool yourself into thinking switching from excel to Alteryx is doing yourself any favors. You're just trading one monster for another. Save yourself a huge amount of money, not to mention yo…

Alteryx was my first step into analytics and it helped me get 2 heads funded for my team. But yeah at some point I arrived to your same conclusion and moved my team to python, we never looked back. One thing people don’t realize with these UI tools is that there is no way to use a version control system and I nowadays I cannot think of doing any analytics (including basic BI) without git.

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

#67

Post by gmueckl » Sat, Oct 17, 2020, 4:38 AM UTC

racecar789 wrote: post #65

About Excel visual grid performance...I have tried many data table libraries over the years in various languages. Almost all have performance issues when reaching 100-1000+ row range (even with virtualization and other tricks enabled). I would like to know how the Excel team made their grids so fast. Scrolling is so smooth.

MS Office was updated to use GPU accelerated rendering and I guess this has something to do with it. I don't know how this works (which API, which portions of the software, which algorithms) as details on that seem to be pretty sparse.

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

#68

Post by sjy » Sat, Oct 17, 2020, 5:09 AM UTC

gmueckl wrote: post #67

racecar789 wrote: post #65

About Excel visual grid performance...I have tried many data table libraries over the years in various languages. Almost all have performance issues when reaching 100-1000+ row range (even with virtualization and other tricks enabled). I would like to know how the Excel team made their grids so fast. Scrolling is so smooth.

MS Office was updated to use GPU accelerated rendering and I guess this has something to do with it. I don't know how this works (which API, which portions of the software, which algorithms) as details on that seem to be pretty sparse.

I don’t think so – Excel has felt significantly slower to me ever since they introduced the GPU “accelerated” interface.

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

#69

Post by Sharlin » Sat, Oct 17, 2020, 5:21 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.

Excel being the de facto standard, Sheets has to try to be compatible with it, quirks and all.

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

#70

Post by nerdponx » Sat, Oct 17, 2020, 5:22 AM UTC

racecar789 wrote: post #65

About Excel visual grid performance...I have tried many data table libraries over the years in various languages. Almost all have performance issues when reaching 100-1000+ row range (even with virtualization and other tricks enabled). I would like to know how the Excel team made their grids so fast. Scrolling is so smooth.

I recently opened a 9000 row sheet in LibreOffice Calc on an x220 and had no performance degradation at all. In fact I was impressed at how responsive the application felt compared to Excel. Might be interesting to try bigger and bigger files until it looks like it's slowing down.