I was wrong about spreadsheets (2017) • Hacker News Board

Re: I was wrong about spreadsheets (2017)

#241

Post by ekianjo » Fri, Jul 12, 2019, 12:32 PM UTC

halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

> I have deep respect for Excel.

I do something around the same line as you describe (build better tools in R/Python for larger data problems) but I have a deep aversion to Excel. It's proprietary, has horrible standards (it does not support native UTF8 csv files for example), and is stuck in the 80's in terms of paradigm. And this is precisely the tool that prevents people from doing things more efficiently because "they can do it manually in Excel even if it takes a long time". It makes people take really, really bad habits.

Re: I was wrong about spreadsheets (2017)

#242

Post by mamcx » Fri, Jul 12, 2019, 12:34 PM UTC

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

My endgame is build a Excel+Access alike dev tool. I'm working in a relational language as the foundation (http://tablam.org) because Excel have another problem: The excel UI paradigm (reactive) is not the VBA macros one (almost OO). So make new functions not follow the way of excel.

I think too a spreadsheet is an interface for a database/source and must not have issues handling gigabytes of data. So I think internally it must a sqlite db for example and put on top the control. Making it virtual and reactive and is done.

Re: I was wrong about spreadsheets (2017)

#243

Post by froindt » Fri, Jul 12, 2019, 12:37 PM UTC

jhbadger wrote: post #215

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

My main issue with them is that they often aren't reproducible as the data transformations aren't logged. As a computational biologist I often deal with spreadsheets that experimentalists give me. I have no idea how the data were transformed from the raw data, and often the experimentalists themselves can't remember either. That's why I infinitely prefer real programming languages for use in data manipulation -- you…

PowerQuery is integrated into excel, gives a visual editing platform (not purely code), and is quite powerful in terms of linking up to any datasource, manipulating data, and giving you the output you need. All the steps are listed on the right side, and you can click back to any previous step in the transformation.

It's not super popular, but it does serve as a nice middle ground for people in the space who aren't strong in coding.

Re: I was wrong about spreadsheets (2017)

#244

Post by formercoder » Fri, Jul 12, 2019, 12:42 PM UTC

ekianjo wrote: post #241

halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

> I have deep respect for Excel. I do something around the same line as you describe (build better tools in R/Python for larger data problems) but I have a deep aversion to Excel. It's proprietary, has horrible standards (it does not support native UTF8 csv files for example), and is stuck in the 80's in terms of paradigm. And this is precisely the tool that prevents people from doing things more efficiently because…

In what way is the paradigm stuck in the 80s?

Re: I was wrong about spreadsheets (2017)

#245

Post by froindt » Fri, Jul 12, 2019, 12:42 PM UTC

chaostheory wrote: post #56

mdorazio wrote: post #7

The sheer number of everyday business use cases that can be solved quickly and efficiently with spreadsheets is pretty astounding. Of course, there comes a point when heavily-used spreadsheets become intricate behemoths on which core business functions are run, and then things can get bad in a hurry. The challenge many programmers face is thinking that every business problem is best solved with code, when in reality…

> The challenge many programmers face is thinking that every business problem is best solved with code, when in reality the majority are solved faster, more efficiently, and more maintainably with a spreadsheet. It's not like you can't do both. Excel supported VBA macros for a long time. Now it supports JavaScript.

It still supports VBA. I've seen multiple manufacturing facilities which are absolutely reliant on VBA macros to be able to produce and ship their products.

Re: I was wrong about spreadsheets (2017)

#246

Post by VBprogrammer » Fri, Jul 12, 2019, 12:43 PM UTC

My first job out of University was working for a large US custodial bank (not in the IT dept). We were responsible for monitoring corporate actions happening on the securities held by their clients and accounting for the proceeds.

Most of the work was done in some old IBM 390 terminal emulators. The work flow was generally scrape a bunch of information from the terminal into Excel. Reformat it and figure out any discrepancies. Copy some adjustments into another workbook which would automatically enter the data into some screen or other in the terminal emulator to fix the discrepancies.

Someone had built a COM object which could be scripted quite easily in Office VBA. I found a few of the scripts they had written for it hadn't been protected and quickly learned to write my own.

It was kinda fun in its own way.

Re: I was wrong about spreadsheets (2017)

#247

Post by jjakque » Fri, Jul 12, 2019, 12:49 PM UTC

safgasCVS wrote: post #226

halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

"just like riding a bike" - best summary of Excel I've read!

And is it just me, or hopping on OpenOffice Calc or Google Sheet felt like a different device altogether: They all look like bikes, but accelerate differently, change gears differently etc?

Re: I was wrong about spreadsheets (2017)

#248

Post by [deleted] » Fri, Jul 12, 2019, 12:49 PM UTC

vidarh wrote: post #165

kgwgk wrote: post #144

Earlier quoted context omitted.

Even worse, the token used to separate arguments is localized. So you may have to use semicolons instead of commas. But in some places you need to pass a string representation of a formula and there Excel will only understand the non-localized commas. Fun all around!

I once had a contracting gig where I was tasked with updating a bunch of Word BASIC macros (this was Office 95 days...) used to maintain the ISO 9001 documentation for a major company. Problem was the original version was written in their Danish office, and whoever wrote it had handed it to the Norwegian office exported as text before he left, and they'd imported it and started munging it and adding lots of stuff bef…

[deleted]

Re: I was wrong about spreadsheets (2017)

#249

Post by eigenvalue » Fri, Jul 12, 2019, 12:50 PM UTC

qntty wrote: post #239

Anyone have any advice on how to learn to use excel well? I've only ever down simple column operations before, but I've seen spreadsheets that do crazy complicated things.

Yes, check out the two books by John Walkenbach, Excel 2013 Formulas and Excel 2013 Power Programming with VBA. Not too much has changed since those versions-- I originally read the 2007 versions of them and it all transfers over because Microsoft is incredibly good about maintaining backwards compatibility.

Re: I was wrong about spreadsheets (2017)

#250

Post by Grustaf » Fri, Jul 12, 2019, 12:55 PM UTC

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

> Half of the mess that makes excel hell comes from the fact it's too easy to put two tables of data + some random constants on a single sheet and refer to them by H3

This is certainly true, but that is also where almost all the power comes from, its generic nature. I had a startup that tried to reinvent Excel for 6 months and we kept moving closer and closer to the excel "sack of undifferentiated data" paradigm...