Spreadsheets Are Hot–and Cranking Out Complex Code • Hacker News Board

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#91

Post by doctor_eval » Sat, Apr 09, 2022, 9:01 PM UTC

TAForObvReasons wrote: post #38

motohagiography wrote: post #31

I've learned products designed to replace spreadsheets have a huge hurdle because the people who use spreadsheets treat operating the sheet as their job. Replacing them removes their autonomy and control over an information process, and subsumes the value they bring to their employers - so they will resist products that threaten that. Excel is a complete management subculture. The other advice I give is if you are ge…

Excel can be replaced, but it won't be replaced by the current crop of VC-funded SaaS. > Airtable really ought to be killing Excel, but the SaaS model combined with a stupidly low artificial row count limit (over 50000 rows is listed as "contact us for pricing") means that it will never achieve penetration into weird and wonderful use cases like Excel has. Many Excel processes are 20+ years old. No SaaS could replace…

Airtable is great for throwing together a quick prototype, but it has so many arbitrary-feeling limitations that I’ve never actually used it in anger.

It’s a shame, because I really want to like it - but it’s less flexible than a spreadsheet, and less powerful than a database.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#92

Post by beefield » Sat, Apr 09, 2022, 9:04 PM UTC

As much as I hate to admit it, it is going to be a long time before we get rid of spreadsheets in de facto production use. And for that, I really would like to see one thing:

I'd like to have a separate worksheet type for "datasheets". Looks and behaves just like an ordinary worksheet, but:

- plugging in a formula applies the formula on every cell of the column. No exceptions. - you can not have different data types in cells within one column. That is, if you have dates in a column, you can't have a string in one cell of thecolumn etc.

Yes, I know about powerthings in excel. No, I do not want to mess with those. Just normal spreadsheet formulas and sheets.

(Okay, a couple of other things: Get rid of vba and bring in the full python ecosystem instead. And if not yet available, version control. It is luckily a while I have worked with excel, so these may be outdated comments)

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#93

Post by dfox » Sat, Apr 09, 2022, 9:08 PM UTC

janci wrote: post #86

Excel is great and versatile, yet lacks some critical features. - It's easy to load data from database or API without VBA, yet impossible to write updates back without VBA. With VBA it's still messy string concatenation of SQL queries. - VBA is security nightmare - Version control is bad. Distributing spreadsheets in emails and collecting changes back is nightmare. Sharing a file on network drive lacks fine-grained p…

Excel is locale dependent in a weird way. The internal model (which includes the function names) is completely locale independent and only the UI is internationalized (according to OS locale) and localized (according to Excel language version). This is not a bad construction, but breaks down spectacularly when you start to use Excel as any kind of real programming or data analysis environment, because then you get to the cracks in the user-visible “API surface” where the internal/external format distinction is somewhat smeared.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#94

Post by doctor_eval » Sat, Apr 09, 2022, 9:18 PM UTC

civilized wrote: post #58

motohagiography wrote: post #31

I've learned products designed to replace spreadsheets have a huge hurdle because the people who use spreadsheets treat operating the sheet as their job. Replacing them removes their autonomy and control over an information process, and subsumes the value they bring to their employers - so they will resist products that threaten that. Excel is a complete management subculture. The other advice I give is if you are ge…

> I've learned products designed to replace spreadsheets have a huge hurdle because the people who use spreadsheets treat operating the sheet as their job. In general? Not really. Just as frequently, the fancy tool promoters don't care to understand the subtleties of the job and when it requires flexibility or judgment that the spreadsheet accommodates better. They have their hammer -- software formally engineered by…

I agree. One of the great things about Excel is that it’s massively flexible. It’s almost the antithesis of what most development environments want to be. Programming is about considering all the code paths. Spreadsheets are about “what if”.

The flip side of this is that understanding the behaviour of a spreadsheet is generally a specialist job, which is why we have people whose job it is to “run” the spreadsheet. The spreadsheet has rules and boundaries and it will stop working if you just start plugging random values into formula cells.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#95

Post by maxerickson » Sat, Apr 09, 2022, 9:20 PM UTC

willhslade wrote: post #76

maxerickson wrote: post #9

Earlier quoted context omitted.

For stuff at the scale of a small Access database, does the normalization (or lack of) end up being a big deal? I get that there are real advantages, I'm just wondering out loud whether they are universally applicable/important to small scale databases.

Speaking as someone with direct experience maintaining Access databases that should have been SQL Server from inception, yes it absolutely does. If you need to force feed 10+GB through Access artificial 2GB constraint and your programming language (VBA) is both single threaded and interpreted, if you aren't clever then you will run into performance problems, just as you would if you were doing something similar in Ja…

I'm imagining a few hundred thousand small records when I say small, not 10 GB.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#96

Post by kgodey » Sat, Apr 09, 2022, 9:25 PM UTC

throwaway787544 wrote: post #37

I just learned how to use spreadsheets recently, and I love them. As a programmer, spreadsheets are exactly the kind of tech I want to use every day. User-friendly point and click, IDE features, complex functions mapping data across rows/columns/tables. All I want now is to hook it up to Postgres, and then maybe a new app to automatically design simple web pages that pull out and display data with the same functions.…

We're building an (open source, self hosted) tool that does the "hook it up to Postgres and pull out and display data with the same functions" portions of what'd you like. https://github.com/centerofci/mathesar

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#97

Post by vinceguidry » Sat, Apr 09, 2022, 9:27 PM UTC

phkx wrote: post #72

What do are you using spreadsheets for privately? I never dig deep into their capabilities, so I mostly use them to track expenses within some particular scope, e.g. healthcare. When I recently wanted to compare several loans and estimate our financial situation several years in the future, I wrote some Python code and used a Jupyter notebook to enter parameters and make plots. Has any of you done something similar u…

I use Google Sheets for personal finance with TillerHQ to automate downloading bank data. It is, as far as I know, the only workable solution in this space.

http://www.tillerhq.com

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#98

Post by ogogmad » Sat, Apr 09, 2022, 9:28 PM UTC

janci wrote: post #86

Excel is great and versatile, yet lacks some critical features. - It's easy to load data from database or API without VBA, yet impossible to write updates back without VBA. With VBA it's still messy string concatenation of SQL queries. - VBA is security nightmare - Version control is bad. Distributing spreadsheets in emails and collecting changes back is nightmare. Sharing a file on network drive lacks fine-grained p…

> Impossible to make reusable blocks.

The click and drag down feature results in a kind-of code reuse. It bypasses the need to consciously name things. Is this generalisable?

For small scale code, these concerns may be overkill.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#99

Post by cpeterso » Sat, Apr 09, 2022, 9:41 PM UTC

civilized wrote: post #85

dkersten wrote: post #82

Earlier quoted context omitted.

Certainly if you do traditional TDD or something like REPL-driven development, you see the intermediate results and validate the correctness of your code as you go.

Those are great! But they are coding disciplines one must choose to follow, and continue following every step of the way. You aren't inherently following them because that's how the tool works. Sometimes the tool-enforced discipline has advantages.

How would one write tests for a complex, mission-critical Excel spreadsheet? Or use version control?

Spreadsheets often mix the data and code/formulas and the formulas are hidden behind the sheet view and sprinkled across many cells. At least Python scripts separate the code from the data so you can write tests using known good or fuzzing data. And you can use version control to track and review code changes.

Re: Spreadsheets Are Hot–and Cranking Out Complex Code

#100

Post by KarlKemp » Sat, Apr 09, 2022, 9:41 PM UTC

Excel is by far the most successful programming language and IDE. People love to hate it (and the people using it), which is somewhat misguided: there is simply no way to change people (and they keep making new ones), so telling them that what works for them is somehow wrong is both wrong and doesn't work.

Instead, the spreadsheet paradigm has the promise of being far more powerful. Jupyter notebooks are one example of adapting it to a different realm, and it also ended up being used everywhere and looked down upon by the snobs.