News for hackers. Stuff that matters.
Re:Excel Labs, a Microsoft Garage Project by Closi on Friday May 26, 2023 @06:54AM (#36082488)
The advanced formula environment is great, I've been using it for about a year now and it makes LAMDA so much more useful.
Re:Excel Labs, a Microsoft Garage Project by heisenzombie on Friday May 26, 2023 @06:57AM (#36082512)
There’s something unbeatable about the purity of a spreadsheet. For certain kinds of problem, it’s one of the most understandable ways to communicate a program.

But there are the obvious problems, as formulas get inscrutable and you really want some more powerful data types.

So I’ve been playing with the new Excel features - lambdas and the new kind of array formulas. And they’re kind of great! I ported some non-trivial analysis algorithms from numpy to excel and it makes for a highly shareable and havkable programming environment for non-coders.

There’s all sorts of crazy excel warts (I’m doing maths with complex numbers, and the handling of those is a true “WAT”)

It’s kind of almost-great. I can’t put my finger on it but I feel like it’s close to a really winning programming environment for certain kinds of algorithms-transforming-data programs. I think Excel probably has too much baggage to get there, but these experiments are still really interesting.

Re:Excel Labs, a Microsoft Garage Project by bdavbdav on Friday May 26, 2023 @07:16AM (#36082647)
Coming from finance where we've been trying to wean FO off excel, every time I see things like this, I'm reminded why we shouldn't get comfy relying so heavily on Excel: https://www.businessinsider.com/excel-partly-to-blame-for-tr...
Re:Excel Labs, a Microsoft Garage Project by wslh on Friday May 26, 2023 @07:37AM (#36082791)
It is a grey area, spreadsheets are great but at some point you are building a mammoth with it. I also find Excel, and Google Sheets outdated on its own "form-factor".

It would be great to hear from the HN community about ways to improve spreadsheets. For example, adding new types and easier integrations with other systems. Also having views/forms. Lotus Notes was revolutionary in this aspect. Excel is the measure because they have a good engine. Probably we need to create a great DAG engine and put the spreadsheet over it.

Re:Excel Labs, a Microsoft Garage Project by bradrn on Friday May 26, 2023 @07:53AM (#36082903)
> It would be great to hear from the HN community about ways to improve spreadsheets.

I’m working on this! Very early days so far, and I have nothing much to show for it yet, but I do have some ideas on integrating static type inference and proper data structures into spreadsheets. My initial prototype is at [0]: it’s horrifically buggy in just about every way (don’t even try to get it working!), but the screenshot should give some idea of what I’m thinking should be possible.

[0] https://github.com/bradrn/nexo

Re:Excel Labs, a Microsoft Garage Project by operator-name on Friday May 26, 2023 @08:40AM (#36083276)
There's https://pyspread.gitlab.io/, which uses python. On the more programmable side you've got Jupyter with https://github.com/ipyflow/ipyflow and https://github.com/fonsp/Pluto.jl.
Re:Excel Labs, a Microsoft Garage Project by pcthrowaway on Saturday May 27, 2023 @03:38PM (#36097529)
Grist looks similar, and also interesting: https://www.getgrist.com/

The tables are sqlite tables, so the schema has to be rigid, while I think they also offer views that allow you to make more user-friendly views. They also let you run Python inside the formula

Re:Excel Labs, a Microsoft Garage Project by calvinmorrison on Friday May 26, 2023 @09:05AM (#36083543)
I thought a general purpose spreadsheet language would exist. Would be cool to see programs 'operate', visually
Re:Excel Labs, a Microsoft Garage Project by duped on Friday May 26, 2023 @11:39AM (#36085593)
> Probably we need to create a great DAG engine and put the spreadsheet over it.

Why a DAG? You might want recursion. We have pretty great computational models already, I think that what you're talking about is an interface into such a model (a statically typed language) through a spreadsheet like interface, which isn't totally revolutionary.

The problem is making something that can unseat a behemoth like Excel. The only things that seem to have made a dent are Sheets (on price) and services that unbundle Excel into no-code/low-code products where you make your money by not being as powerful.

Re:Excel Labs, a Microsoft Garage Project by monkeydust on Friday May 26, 2023 @07:51AM (#36082885)
Thats 10 years old! I remember that story. Working in this space I have seen a lot of excel 'systems' get replaced with what we would qualify as proper applications (with all the overhead that comes with that). Of course, traders will miss the nimble nature of being able to update the pricing models themselves instead of having to put a ticket in, but it's prevented a magnitude of other blow-ups that have happened somewhere in the multiverse.
Re:Excel Labs, a Microsoft Garage Project by nhatcher on Friday May 26, 2023 @07:02AM (#36082550)
Are some of those Excel workbooks with non-trivial analysis and complex numbers something you could share? I'm truly curious.
Re:Excel Labs, a Microsoft Garage Project by heisenzombie on Sunday May 28, 2023 @06:36PM (#36108449)
It's annoying for me to share the spreadsheet, but I'm basically implementing the math in:

https://lhc-div-mms.web.cern.ch/tests/MAG/FiDeL/Documentatio...

To do with calculating multipole coefficients from measured data and then doing coordinate transformations on them.

Re:Excel Labs, a Microsoft Garage Project by nhatcher on Tuesday May 30, 2023 @05:47AM (#36122946)
Oh wow! Many thanks! It's impressive what you can do on a spreadsheet these days.
Re:Excel Labs, a Microsoft Garage Project by rickdeckard on Friday May 26, 2023 @07:18AM (#36082659)
Oh, I need to check that out. The universal language and portability of a spreadsheet is really hard to beat.

Seeing how people often read xls-sheets by backtracking formulas, it would be great if MS could add support for comments within formulas (multiline formulas being a thing for many years now)

That wouldn't blow up the whole complexity of formulas, but still allow at least to explain some of these huge formulas people have to deal with on a daily basis.

Re:Excel Labs, a Microsoft Garage Project by loudmax on Friday May 26, 2023 @08:46AM (#36083335)
> The universal language and portability of a spreadsheet

Depends strongly on your definitions of "universal" and "portable". Try reading these files with non-proprietary software.

Re:Excel Labs, a Microsoft Garage Project by rickdeckard on Tuesday May 30, 2023 @03:53AM (#36122217)
Yes, very valid comment.

Portable in the sense of sharing something across different departments of a company, universal in the sense of a tool that can be understood and used across many disciplines of work.

Re:Excel Labs, a Microsoft Garage Project by aaravchen on Friday May 26, 2023 @09:04AM (#36083530)
As a hack workaround, I drop everything into a `let` block and define "variables" named "comment1", "comment2", etc that are just comment strings and otherwise unused. It affects performance if you're hitting that formula a lot with disparate data, but most of the time anything needing performance from a cell should be using the new arrays processing anyway.
Re:Excel Labs, a Microsoft Garage Project by lindbergh on Friday May 26, 2023 @08:08AM (#36083027)
Yes Excel lately is turning into a really compelling product, using functions such as sequence (dynamic arrays), lambda (pure functions) and let (defining sub-variables). You'd think you're coding in a pure functional language, and that's great! Now, ff only Excel had a convolution function! But nonetheless it's a great GUI, everybody knows how to use it and sharing it is dead simple.

The one downside I can find is the lack of a good plotting library. And yes comments as well is something I miss a lot.

Re:Excel Labs, a Microsoft Garage Project by occamrazor on Friday May 26, 2023 @01:22PM (#36087105)
I have implemented a convolution (using radix-2 FFT) as an intermediate step in Bluestein FFT.

Look in the source of: https://github.com/altomani/XL-FFT

Re:Excel Labs, a Microsoft Garage Project by lindbergh on Friday May 26, 2023 @02:32PM (#36087959)
Oh wow that's really impressive. Thanks a lot !
Re:Excel Labs, a Microsoft Garage Project by bambax on Friday May 26, 2023 @09:52AM (#36084127)
Some things in Excel have not improved since 2003, that should be fixed before toying with "AI" . For example, when importing a CSV:

- Excel cannot guess the encoding of the file, and relies on the user selecting it from a list that has maybe a hundred values (?!); the most common encoding, UTF-8, is neither at the top or at the bottom of that list, but somewhere near the end, and is called "65001 : Unicode (UTF-8)" (the preceding value is "65000 : Unicode (UTF-7)"). There is little chance non-technical users will get this right the first time, or any time thereafter, and the result is files that are circulated with garbled encoding and wrong values.

- Excel cannot guess the separators either! (How hard can it be?)

That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel.

Yes, it's a minor thing, but it should be so easy to fix; instead of that, recent versions of Office have brought incredibly annoying animations that take 2-3 screens to disable.

Re:Excel Labs, a Microsoft Garage Project by kubik369 on Friday May 26, 2023 @10:41AM (#36084818)
I have an even better one for you — if the language/region of your system is different, Excel parses the file differently. My example, Slovak region, numbers are officially written with a comma — 1,3 meaning a decimal "one point three". When you open a file that has the numbers from a different region (so 1.3), it completely falls apart.
Re:Excel Labs, a Microsoft Garage Project by towawy on Friday May 26, 2023 @10:49AM (#36084932)
Workaround for anyone struggling with this: Use the Import feature from the Files menu instead of the usual Open dialog for csv files. You can change decimal separators in the import wizard.
Re:Excel Labs, a Microsoft Garage Project by andersa on Friday May 26, 2023 @11:08AM (#36085190)
This is one of those things that make absolutely no sense. The "."/"," difference should only apply to displaying numbers to users like any other localization related change. Why on earth is it writing numbers differently in a standard format meant to be read only by computers? Such a colossal fuckup making csv files always broken for half of Excel users.
Re:Excel Labs, a Microsoft Garage Project by tobr on Friday May 26, 2023 @09:54AM (#36084156)
> That's probably the reason why one cannot "open" a CSV file directly in Excel and having it displayed properly; one has to go through the whole "import" process. Yet Windows insists all CSVs should automatically open in Excel.

Both of these things are consistent with trying to keep users in Excel. Make it easy to accidentally open Excel, but don’t make it too convenient to use open data formats when you have a proprietary one.

Re:Excel Labs, a Microsoft Garage Project by tomjakubowski on Friday May 26, 2023 @11:43AM (#36085644)
XLSX is openly documented and free to use without royalty, it's not proprietary
Re:Excel Labs, a Microsoft Garage Project by resoluteteeth on Friday May 26, 2023 @01:11PM (#36086940)
The OOXML format is heavily oriented around internal details of Office in ways that makes it pretty inconvenient to work with even if it's "open". Also IIRC some more esoteric parts are just binary blobs that aren't actually documented and the specifications just say to do what Office does.
Re:Excel Labs, a Microsoft Garage Project by bambax on Friday May 26, 2023 @12:03PM (#36085945)
Maybe... but I wouldn't be so quick as to assume bad intent ("never attribute to malice", etc.)

What's the deal with "copy" for example? Why does the source of the copy have to be highlighted, why is it so fragile, why does it disappear from the clipboard when one presses the Escape key? This has been the case since I think the very first version of Excel, and never changed.

I don't know of any other program that works that way. It didn't make sense then (I think), and it doesn't make sense now.

Re:Excel Labs, a Microsoft Garage Project by dagw on Friday May 26, 2023 @09:56AM (#36084183)
Excel cannot guess the separators either! (How hard can it be?)

What is the separator in this .csv file ;)

1,2;3,4;5,6

But on a more serious note, I completely agree. There are so many small quality of life things that can and should be fixed in Excel, and it's baffling that they haven't.

Re:Excel Labs, a Microsoft Garage Project by M4v3R on Friday May 26, 2023 @10:03AM (#36084290)
Sure, there are some edge cases where guessing the separator would be hard, but in 99% of cases you can correctly identify the separator pretty easily and in case you can't you just don't pre-fill that field in the import dialog.
Re:Excel Labs, a Microsoft Garage Project by PTOB on Friday May 26, 2023 @10:38AM (#36084767)
Psst, the joke is that it's a "comma separated variable" file.
Re:Excel Labs, a Microsoft Garage Project by cxr on Friday May 26, 2023 @11:29AM (#36085449)
The V in CSV doesn't stand for "variable". And that wasn't the joke.
Re:Excel Labs, a Microsoft Garage Project by sroussey on Friday May 26, 2023 @12:51PM (#36086677)
If the file ends in CSV then the comma is the separator (the C is CSV).
Re:Excel Labs, a Microsoft Garage Project by dagw on Friday May 26, 2023 @01:04PM (#36086846)
Someone should tell the Excel developers, because they obviously didn't get that message. They use the .csv extension even when exporting files with ; separator.
Re:Excel Labs, a Microsoft Garage Project by wombatpm on Friday May 26, 2023 @11:08AM (#36085186)
How about a diff tool to track changes in excel DATA, FORMULAS, and MACROS. Do you know how many business are run with handcrafted excel sheets that are not in source control?

Which is the correct budget model?

Budget.xlsx Budgetv2.xlsx Budget.final.xlsx Budget.v2.final.final.xlsx Budget.final.final.submitted.revised.xlsx

Re:Excel Labs, a Microsoft Garage Project by cxr on Friday May 26, 2023 @11:24AM (#36085369)
Those .xlsx files aren't just blobs. They're ZIP files. If you want version control for Office documents, it's as easy as unzipping them and checking in the results.
Re:Excel Labs, a Microsoft Garage Project by voisin on Friday May 26, 2023 @11:19AM (#36085317)
How about using AI to understand dates? My bank’s CSV output isn’t recognized by Excel and needs to be manipulated so it will follow a date format.
Re:Excel Labs, a Microsoft Garage Project by gigel82 on Friday May 26, 2023 @11:40AM (#36085600)
I've opened thousands of CSV files directly in Excel and never had one not open correctly (unless the file itself was not valid CSV, especially with things not quote-enclosed properly).

It's likely I've mostly had to deal with ASCII / Latin alphabet, so don't know how well it handles unicode.

Re:Excel Labs, a Microsoft Garage Project by downrightmike on Friday May 26, 2023 @11:46AM (#36085689)
I just double click the csv and it opens in excel correctly.
Re:Excel Labs, a Microsoft Garage Project by Tagbert on Friday May 26, 2023 @12:07PM (#36085993)
Excel has some annoying defaults when importing data, too.

My company tracks transactions with an ID that is a numeric string that Excel assumes is an integer but longer than it supports so it truncates the value and displays it in scientific notation.

Original value: -7223371999747962216

Truncated value: -7223371999747960000

Displayed value: -7.22337E+18

It would be fine if it just treated it as text but by discarding digits it makes the values useless. You can’t open a CSV file directly. You have to manually import the data and specify specific columns as text. Every. Single. Time. It cannot be automated.

I’ve been sent so many excel files with truncated data like that because most people don’t even know about the problem, let alone know how to work around it.

Re:Excel Labs, a Microsoft Garage Project by bambax on Friday May 26, 2023 @02:01PM (#36087625)
It may be automatable if you enclose the values in double quotes and specify that double quotes mark text (which is the default I think).
Re:Excel Labs, a Microsoft Garage Project by Tagbert on Sunday May 28, 2023 @01:33PM (#36106053)
Sure, if you have control over the source, but that is not always the case. CSV is very loosely specified and there are large variations in implementations.
Re:Excel Labs, a Microsoft Garage Project by resoluteteeth on Friday May 26, 2023 @01:05PM (#36086868)
It seems like they don't want to change anything that affects existing behavior, so they're mostly adding new features that are orthogonal to existing features.

Unfortunately this means that they're not really fixing most of the annoyances that were already present.

"Excel Labs" appears to just be an add-on using the normal API which is a lot easier than making change to Excel itself.

They've also added stuff like new functions for formulas and a new type of comments that are more like word comments (while renaming the old comments to "note") but it's pretty clear that they have chosen to focus on things that they can tack on without having to change existing functionality (presumably either so they won't break anything and/or because it's simpler easier to add this kind of stuff rather than getting too deep into the existing codebase)

Re:Excel Labs, a Microsoft Garage Project by mcbishop on Friday May 26, 2023 @01:22PM (#36087103)
I mostly agree with you. But I assume dynamic-array formulas (released in 2018) required deep calculation-engine changes. (For me, this feature is a game-changer.)
Re:Excel Labs, a Microsoft Garage Project by erichocean on Friday May 26, 2023 @02:25PM (#36087883)
> Yes, it's a minor thing

I think you may slightly misunderstand the functionality, it's an updateable link to some other file (which is, frankly, great). I can import a CSV file I generate with code ONCE, build up a huge calculation from it (probably in other sheets), and then the next day, regenerate the CSV, update the link WITHIN EXCEL, and boom, all of my computation is redone.

Very underutilized feature, but incredibly useful when using Excel not as a CSV viewer (if you want that, you can buy that elsewhere) but as a critical part of running a business.

Excel exists to make it almost free to write accounting-like software. It does NOT exist to view CSV files.

Re:Excel Labs, a Microsoft Garage Project by prepend on Saturday May 27, 2023 @06:57PM (#36099251)
I open csv files in excel every day without going through these dialogs. Aside from excel doing stupid stuff like misinterpreting columns as dates every once in a while, it’s good enough to eyeball stuff.
Re:Excel Labs, a Microsoft Garage Project by 2Gkashmiri on Friday May 26, 2023 @10:13AM (#36084434)
I'm one of the libreoffice gang and I rely on =regex as a part of my work on a daily basis.

Eventually we 'might' get therein terms of new features but for the time being, I am fine.

Macros are slowly improving in libreoffice but it still ways off anyway.

Re:Excel Labs, a Microsoft Garage Project by null_shift on Friday May 26, 2023 @10:57AM (#36085030)
I’m assuming the generative AI functions are not running purely locally and would be sharing your data to Microsoft/OpenAI ?
Re:Excel Labs, a Microsoft Garage Project by jbullock35 on Friday May 26, 2023 @11:38AM (#36085581)
Most of us who use Excel are still using it as though it were 2013. Are there any good tutorials about the major features, like lambdas and dynamic arrays, that have been introduced in the last few years?