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

Re: I was wrong about spreadsheets (2017)

#341

Post by phonon » Fri, Jul 12, 2019, 7:11 PM UTC

tonygrue wrote: post #43

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…

I'd be over the moon to see one change to Excel. Native support for a language other than VBA. Perhaps the CLR. Perhaps Java/Typescript. Just something (optional) for programmers who want to use Excel and not want to deal with the garbage that VBA is. Some workarounds exist, but afaik they require collaborators to also have the tool install, which is dead in the water. I know MS has considered it, I'm still pretty su…

JS functions just became GA.

https://developer.microsoft.com/en-us/office/blogs/office-ex...

Re: I was wrong about spreadsheets (2017)

#342

Post by grep_name » Fri, Jul 12, 2019, 7:20 PM UTC

kgwgk wrote: post #89

tonygrue wrote: post #43

Earlier quoted context omitted.

I'd be over the moon to see one change to Excel. Native support for a language other than VBA. Perhaps the CLR. Perhaps Java/Typescript. Just something (optional) for programmers who want to use Excel and not want to deal with the garbage that VBA is. Some workarounds exist, but afaik they require collaborators to also have the tool install, which is dead in the water. I know MS has considered it, I'm still pretty su…

Microsoft would also like to see that change, that’s why they tried to kill VBA. The reason for not following through (in killing VBA, because they definitely support and prefer other extension mechanisms) was pushback from users.

Wait, really? I've been working in a thoroughly MS ecosystem for the first time this year, and I can't seem to find any evidence that they've tried to kill VBA based on how often I run into situations where VBA (or worse, VBscript) is the ONLY option, especially when working with excel's developer functions or writing expressions in SSRS utilities. Did the users push back because the options they were presented with were use VBA or have no scripting capabilities?

Re: I was wrong about spreadsheets (2017)

#343

Post by phonon » Fri, Jul 12, 2019, 7:22 PM UTC

thanatosmin wrote: post #15

Is there anything like a spreadsheet to Python converter, with cells becoming made up variable names? I wonder if it’d be useful for quickly noodling together a set of expressions, then “baking” it into code.

https://dirkgorissen.com/2011/10/19/pycel-compiling-excel-sp...

Re: I was wrong about spreadsheets (2017)

#344

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

ubermonkey wrote: post #259

There's nothing WRONG with the spreadsheet model, and never has been. It's immensely powerful and useful, and the growth in capability over the life of a product like Excel is remarkable and enviable. Excel is perhaps Microsoft's greatest product. The problem with it -- and there IS a problem -- is really a problem of applicability. Excel, like Lotus before it, is the first place many people encounter the ability to…

I think there is a tipping point in complexity where Excel needs to be replaced by something else. In the IT world I've seen businesses that have essentially created bespoke ERP applications for themselves by evolving a web of interlinked Excel files over years.

A food distributor I worked for was handling their upstream vendors, warehouse inventories, truck inventories, customer list, order history, deliveries, invoicing, and more this way. These businesses suffer because the "Excel ERP" is constantly breaking or losing data, and no one knows how it really works anymore.

The food distributor eventually migrated to an actual ERP made for their industry, and the amount of effort and stress saved at all levels of the company was massive.

Re: I was wrong about spreadsheets (2017)

#345

Post by clausok » Fri, Jul 12, 2019, 7:39 PM UTC

simonh wrote: post #122

I used to work at a bank that built a huge integrated application development and deployment stack on Python. One aspect of it was a custom library implementing a Directed Acyclic Graph, basically a way to ink up properties so that when the results of one property changed, any dependent properties on the DAG would automatically recalculate. This was explicitly implemented so that we could directly translate the logic…

We also found using Excel as an output to be a powerful design pattern. Excel is a great language for explaining the flow of calculations. We used to have WPF screens where a portfolio manager / trader / analyst could right-click on a number and select "show details" and he/she would get an Excel workbook where that same number would be selected and they could see the derivation by following the cell's precedents. It was not always feasible to show a complete derivation, but we'd try to show a useful amount of elaboration. This was a heavily used and loved feature, often leading to the users making edits or additions to these detail sheets and giving it back to us as a spec. Building the 'details' Excel books programmatically was a lot of work, but part of the payoff was that we also used them as a nightly check on our primary system, i.e., we'd automatically run 'show details' on everything and make sure the 'detail' result matches the primary system result. That comparison caught many bugs. Kent Beck: "More important than tests vs types is the principle of double checking. If you say something twice in independently derived ways, you're more likely to be correct than if you just say it once. Tests are a form of double checking. So are types."

Re: I was wrong about spreadsheets (2017)

#346

Post by HeavyStorm » Fri, Jul 12, 2019, 7:45 PM UTC

ozim wrote: post #207

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…

Yeah it works and you don't need support contracts and then: - Fidelity's "Minus Sign Mistake": loss of $1.3 billion - TransAlta "Clerical Error": loss of $24 million - Fannie Mae "Honest mistake": loss of $1.3 billion Then you get employee turn over where new employees don't get "arcane" knowledge passed down by people who left and took their spreadsheet foo with them. Excel does not have "access control", "auditing…

Ahn, so sorry, but that's a very flawed argument.

Arcane knowledge will be an issue with magical spreadsheets, as it is with any software (and I could argue that with custom software you have this legacy knowledge over both code AND ui, which is worse than just a spreadsheet).

The last paragraph is key, though - auditing and access control are tough ones for excel, and there are many tasks that require this functionality.

The trade-off is customizability - any excel can be changed to your specific need whereas that nice and shiny enterprise software you build may lack one or two things which you will never be able to change.

Re: I was wrong about spreadsheets (2017)

#347

Post by kgwgk » Fri, Jul 12, 2019, 7:52 PM UTC

grep_name wrote: post #342

kgwgk wrote: post #89

Earlier quoted context omitted.

Microsoft would also like to see that change, that’s why they tried to kill VBA. The reason for not following through (in killing VBA, because they definitely support and prefer other extension mechanisms) was pushback from users.

Wait, really? I've been working in a thoroughly MS ecosystem for the first time this year, and I can't seem to find any evidence that they've tried to kill VBA based on how often I run into situations where VBA (or worse, VBscript) is the ONLY option, especially when working with excel's developer functions or writing expressions in SSRS utilities. Did the users push back because the options they were presented with…

To be fair, they denied that VBA was at risk: https://blogs.msdn.microsoft.com/architectsrule/2008/01/23/v...

But the fact is that it was missing from macos Excel for a while and they wanted people to migrate to VSTO: https://searchwindevelopment.techtarget.com/tip/On-migrating...

Re: I was wrong about spreadsheets (2017)

#348

Post by behringer » Fri, Jul 12, 2019, 7:53 PM UTC

michaelmrose wrote: post #339

behringer wrote: post #335

Earlier quoted context omitted.

And meanwhile the manager that broke the rules, connected their personal copy of excel straight into the database and made the company 8 figures in profit over the course of the last year just outdid you and your team of report programmers.

Logically most of the profit making activities may be enabled by tech it usually isn't created by tech. The managers rule breaking activities are probably unrelated to his success.

In my experience, business success is often hinged on the reports and the insights they provide. Without accurate numbers, you've got nothing to go on.

Re: I was wrong about spreadsheets (2017)

#349

Post by mulmen » Fri, Jul 12, 2019, 8:11 PM UTC

t0astbread wrote: post #328

mulmen wrote: post #183

Spreadsheets are fine. Excel is not. Excel conflates the ideas of data and presentation. This leads to an entire class of headaches that just aren't necessary. It's the desktop application equivalent of the string 'null'. If the spreadsheet layer (calculations and formatting) was separate from the data layer (types and values) then we could all be happy. Wrap that up with a UI that wasn't designed at an office in Red…

This sounds interesting but I don't quite understand. Is this idea documented somewhere more detailed?

Not really, maybe I should write something. It's a thought I frequently have in the depths of Excel Hell. I thought for a long time about why Excel is so frustrating to me. I think fundamentally it comes down to Excel putting the spreadsheet ahead of the data.

If the data was clearly separated from the spreadsheet itself then the logic becomes much easier to reason about and test and the data is no longer susceptible to the Excel data loss.

The display layer really needs to be logically separate from the data. For example if I change a column format from text to number and back I should not lose the leading zeros in the actual data.

Spreadsheet tools are great visual programming environments but they are lousy databases. The data layer in Excel leaves a lot to be desired but there's really no reason a spreadsheet has to be so limited.

Basically I'd like to see some kind of hybrid SQL(database) client/Spreadsheet UI/Pivot Table builder where all three concepts are first class.

Re: I was wrong about spreadsheets (2017)

#350

Post by zelphirkalt » Fri, Jul 12, 2019, 8:51 PM UTC

I think spreadsheet usage can be done in good ways, but not when it is done in Excel, because Excel is a typical MS lock in product.

Things that should be obvious to do when having something important in a spreadsheet:

* Use well labeled (row name, column name, cell above, below, left of it, whatever) cells for in-between results. * Have some checking for mistakes formulas for cells to show you a warning when there seems to be some calculation or entry mistake - like assertions in normal programming. * Use plain text formats and use version control. Do not run around with 10 copies of the file named after what the index of the copy is. * Have explanations of the formulas and the reasoning behind them somewhere, maybe even best inside the spreadsheet. * Make only use of macros in there is no other way. Macros simply break things, at least in Excel. Only a few days ago I witnessed a case, where someone simply could not run some macro, even after reinstalling Excel, using an MS cleaning tool for "completely" removing Excel and various other attempts. And it only happened on that person's machine. The macro is not as reliable as plain text formulas. * Have your data elsewhere as well. Do not use a spreadsheet as your single database.

And those are only the few things that come to my mind, although I am not a daily spreadsheet user. More frequent users might have many more guidelines.

I also recommend people to take a look at Emacs Org mode spreadsheets in combination with various other Org mode functionality. Those can be quite neat.