I am terrified of the same things. I got given a validated financial model for something ages ago and found a major Excel fuck up in it. When I say validated, it was run through an expensive validation company who charged a lot of money and they didn't even notice it.
Almost a decade ago I worked for a bank and in our little corner of the organization I was rapidly becoming the "Excel Guy". While I was happy to help people, the prospect of being pigeonholed into such a role terrified me and I switched jobs. That wasn't the only reason, but it was a major factor.
There was VBA and cell logic. The sheet was validated by a third party then someone ported it to a proper language (incorrectly). That model was not validated.
Yet another meta breaker - this is another move aimed at the casual audience to bring in the party gamers and make the program an absolute mess competitively.
Could we get some easy aliasing of REGEXREPLACE to reRepl and picking a regex engine that matches the syntax rules you're used to in a the next decade or so?
You could quite easily store a lambda formula (that just passes on the arguments) as a named function. Very neat trick for organizing and re-using formulas in excel.
This is one of those fringe features that 1% of users are clamoring for and 99% of users should never ever use. I’m not surprised it was never at the top of the Excel team’s TODO list.
Yeah the problem is that those 1% of users are probably writing the most critical spreadsheets, and when companies using GSuite think about moving to Office365 the more pain it is to migrate spreadsheets the worse for Microsoft.
Good ideas should have a provisional naming period where a standardize name can be agreed on. Anything else leads to fragmentation and lock in due to domain specific terminology.
I am waiting for the day I can use Excel as a REST API client, so I write the ID of some record in a cell, and Excel does all the work of calling the API repeatedly and fills the rows and sheets with the required information.
This will kill a few Python jobs and make it a very popular REST client =)
I've actually done that. I needed to create a lot of json documents and POST them somewhere (it was metadata for some Chef cookbooks), and a lot of the information was similar with a few variations. Excel lets me create what I call a "conductor's score" of that information. (In music, the conductor's score has all the instruments on one page. Each row is a different instrument, and each column is the same measure [point in time] in the music.) In other words, I can easily look for commonalities, differences, and presence of the data as I update it.
I was just going to copy/paste the fields into JSON which is repetitive and error prone, and realized I could automate it. So I wrote VBA to output a json version of that. (The annoying part of writing that in VBA is that JSON data needed a lot of double-quotes, but then you have to use escape sequences for every double-quote.)
Once I had the JSON data generated, I was going to post each one manually, so again, I looked and realized you can POST the data directly from VBA. Added a button to the page and you could update the data in Excel and click a button to POST it.
Of course, once I turned it over to someone else, they were like "what the hell" and started doing it a different way. lol
I've done this in Google Sheets, with "Google Apps Script", the most horridly named thing I think I've ever worked with.
It's just (utterly ancient) Javascript (I think ECMAScript, technically, since there's no DOM, no browser, etc.), bolted on. I wrote in modern JS and used Babel to transpile the source back into the stone age.
I can say the UX of Apple Numbers is the best, much above the usability I've found of Excel. Much less powerful, but it is much easier to use Apple Numbers
As someone who regularly flits between Google Sheets, LibreOffice Calc, and Numbers regularly (with my most time in Google Sheets and Numbers), and as someone who's made some extremely complicated spreadsheets in all three, I have to say I vastly prefer Numbers if I can get away with it. It has a lot of issues that can make some stuff hard fast (no array formulas is a big one), but I find it significantly easier and faster to prototype in Numbers regardless.
I think the main bit I love so much about it is having actual tables instead of the Infinite Grid that most spreadsheet software uses. You get named ranges for free, and it makes semantical sense too, among a good number of other benefits (sheet organization, refactoring, simpler styling...).
There are some really nice things that Google Sheets does, and I've done a few fancy things with App Script which isn't too bad, and I do really like QUERY though I wish it was a bit higher power. I just always find myself missing the UX of Numbers, though.
You can using VBA functions and VBScript.RegExp. This is restricted to Windows though. Microsoft wants to get rid of VBScript, so maybe they are going through the most common use cases in Excel.
Yes, but that doesn't include getting rid of VBA. They probably do want to do that though, to allow mobile apps and web-based Excel to run interactive sheets using Javascript. Of course they've been trying for years to allow Javascript in Excel, but at least in the first versions that ran using Internet Explorer in the background. I'm not sure if that has evolved further now.
Any information on which standard they have implemented (POSIX BRE, ERE, PCRE, ...)? Since they are Microsoft, I would not be surprised if there is none.
Okay, thanks! Now next question which comes into my mind: is there info about the regex engine they are using? I would expect there is some (proprietary?) C++ library also used in other MS products or are they even using a FOSS licensed one?
Seems likely that if they explicitly say they're supporting the PCRE2 syntax it is because they are using the BSD licensed libpcre.
Reinventing a regular expression system is very far down on the list of things I'd ever want to do. Those things are filled with dragons and require years of refinement to get the bugs out.
I love freedom and I love open source, but the problem is that Libre Office is too far from MS Office in terms of UX and functionality.
If your usecases are not complicated, Libre office could work well. Otherwise your efficiency will be behind those that you can achieve with MS Office.
Currently at least text properties and size is on the main screen in the current defaults for Impress and, just like PowerPoint, only made visible and editable when you click something which has a text size. Maybe it was different in the timeframe you were talking about (barring any actual information on the request) but this combined with how abrasive your comment is leaves little reasoning for why it's horrible and casts serious doubts the ban was actually related to asking about interface enhancements even if that's when you were banned.
FWIW I prefer PowerPoint over Impress as well (particularly when it comes to the browser side of things on the go), using one or the other is just not a vendetta of mine.
> I'm deeply convinced there is a Microsoft plant undermining everything.
It's conspiracy theory thinking, but I'm getting there, too. These things have been too close to being complete replacements for commercial products for so long, but somehow still fall over on problems that have been complained about for a decade or more.
There's not one of them that some massive corporation with sights set on adding a letter to faang couldn't pick up and turn into a legitimate competitor in a month or six, khtml-style. Instead they often sit on moribund subpages of some larger project website, with a blog updated once every year or two.
These projects have to be targets for sabotage by their commercial competitors, just as government initiatives are, just cheaper. For e.g. Adobe it's less than a rounding error to e.g. spend 500K/year supplying a developer to e.g. Scribus[0] to make sure that it remains difficult to contribute to, or makes bad architecture choices, and that hypothetical developer could be one of its biggest actual contributors.
Maybe it's just because they have to spend a lot of time chasing Gtk, which makes it another redhat problem?
edit: The 95% state of all a lot of these FOSS packages is also evidence that there are zero tech billionaire philanthropists. It would take a total of one of them to grab all of these projects and wrangle them into good form.
[0] This is an actual hypothetical, I'm not making an accusation about Scribus. Between the GIMP and Inkscape, they literally are the only people who made a real effort with color for years. Inkscape openly said that their software was only for making images for the web (as opposed to print), as if there were a rational reason to cripple their product and narrow people's interest in it. Now that deviantart is gone, will anyone care about Inkscape anymore? Will the dopamine hit hobbyists get from sharing generative art cause Inkscape to be totally left behind? Why is it hard to design a form for your office's paperwork in Inkscape, a vector drawing program, if forms are just straight horizontal lines and text? Why aren't they trying to merge with Scribus (and LibreOffice Impress/Draw) and create a complete pdf solution? I have no idea.
I'm being very ranty here, and I do want to say that I very much appreciate the work that people are doing for free, in their spare time, for others.
It may appear obvious, but only a very limited subset of real-life spreadsheets could meaningfully take advantage of it. Excel does not support GPU-accelerated calculations.
It’s interesting to see the give and take between google sheets and excel. Google sheets came on the scene shooting for total backwards compatibility and then proceeded to develop some really interesting innovations. Now we see new features emerging on both sides that are quickly replicated by the other player. Notably off the top of my mind:
* spill formulas - google first, now supported in MSFT
* # notation - MSFT enhancement to spill formulas not yet adopted in google
* regular expressions - google first, now in Excel
* check boxes - google first, now supported in excel
There must be others. I would expect competitive dynamics where each side tries to build extensions that can’t be replicated on the other side
92 comments
[ 0.16 ms ] story [ 334 ms ] threadThings im terrified of:
- making an error in a financial model - checking a model - making an error in a regexes - checking a regex
Thins I love; - dumb excel stuff
This is a great time to be alive, and at arms length from finance.
To all the people who will deal with the fallout- I salute you.
This is awesome.
It’s so easy to just miss something. A date function which is off.
Missing something in excel is a big deal for a firm which is mean to do it - and it will always happen. And that’s the experts.
I’m betting Someone was too tired, and uninterested and just followed the script.
Hopefully that experience put the fear of God into some analyst and associate.
Pretty much the only thing that I can rely on when it comes to modeling.
Knowing that someone is as terrified as I am and has put the work in to not be embarrassed.
And let’s agree - it is work.
I remember seeing a date function used by another firm for the first time.
It too me at least an hour to decipher it.
It was cool, I learnt a lot. But it takes time.
Regexes are cool, and I guess people will be learning a lot.
If so, did it at least have embedded VB or was it all cell logic?
> Try asking Bing Copilot for regex patterns!
Or maybe embed a cheaper and more reliable solution like https://regex101.com?
[1] https://support.microsoft.com/en-us/office/regextest-functio...
[2]https://support.google.com/docs/answer/3098292?hl=en
Excel also got a "split" function much later than Sheets did.
I was excited to read on the Sheets blog that Sheets finally will have a table functionality, which Excel has always had: https://workspaceupdates.googleblog.com/2024/05/tables-in-go...
TBH, they should have dumped during switch to OOXML, that was a wasted opportunity. On the other hand, that's hard sell to business.
This will kill a few Python jobs and make it a very popular REST client =)
Microsoft's advice page for wrapping Office in a web application fronted by ASP/ASP.Net: https://support.microsoft.com/en-us/topic/considerations-for...
And that has links to documentation about Excel Web Services of old (on-premises SharePoint days, not sure if it translates to today's cloud), which is SOAP/HTTP: https://learn.microsoft.com/en-us/sharepoint/dev/general-dev...
https://learn.microsoft.com/en-us/graph/api/resources/excel?...
Could also do some crude/fragile parsing with aforementioned regex functions.
https://stackoverflow.com/a/158657/969613
https://support.microsoft.com/en-gb/office/get-started-with-...
I was just going to copy/paste the fields into JSON which is repetitive and error prone, and realized I could automate it. So I wrote VBA to output a json version of that. (The annoying part of writing that in VBA is that JSON data needed a lot of double-quotes, but then you have to use escape sequences for every double-quote.)
Once I had the JSON data generated, I was going to post each one manually, so again, I looked and realized you can POST the data directly from VBA. Added a button to the page and you could update the data in Excel and click a button to POST it.
Of course, once I turned it over to someone else, they were like "what the hell" and started doing it a different way. lol
It's just (utterly ancient) Javascript (I think ECMAScript, technically, since there's no DOM, no browser, etc.), bolted on. I wrote in modern JS and used Babel to transpile the source back into the stone age.
… I'd much rather write Python.
I think the main bit I love so much about it is having actual tables instead of the Infinite Grid that most spreadsheet software uses. You get named ranges for free, and it makes semantical sense too, among a good number of other benefits (sheet organization, refactoring, simpler styling...).
There are some really nice things that Google Sheets does, and I've done a few fancy things with App Script which isn't too bad, and I do really like QUERY though I wish it was a bit higher power. I just always find myself missing the UX of Numbers, though.
Reinventing a regular expression system is very far down on the list of things I'd ever want to do. Those things are filled with dragons and require years of refinement to get the bugs out.
If your usecases are not complicated, Libre office could work well. Otherwise your efficiency will be behind those that you can achieve with MS Office.
I once got banned for asking to put 'text size' on the main screen for the powerpoint knockoff.
Text size seems pretty important to have. You shouldn't have to google how to change the text size.
I'm deeply convinced there is a Microsoft plant undermining everything.
FWIW I prefer PowerPoint over Impress as well (particularly when it comes to the browser side of things on the go), using one or the other is just not a vendetta of mine.
Yours continues to enable the status quo.
"oh he made a great point, but he said it snarkey! Guess M$ can just keep being the only company with text size"
It's conspiracy theory thinking, but I'm getting there, too. These things have been too close to being complete replacements for commercial products for so long, but somehow still fall over on problems that have been complained about for a decade or more.
There's not one of them that some massive corporation with sights set on adding a letter to faang couldn't pick up and turn into a legitimate competitor in a month or six, khtml-style. Instead they often sit on moribund subpages of some larger project website, with a blog updated once every year or two.
These projects have to be targets for sabotage by their commercial competitors, just as government initiatives are, just cheaper. For e.g. Adobe it's less than a rounding error to e.g. spend 500K/year supplying a developer to e.g. Scribus[0] to make sure that it remains difficult to contribute to, or makes bad architecture choices, and that hypothetical developer could be one of its biggest actual contributors.
Maybe it's just because they have to spend a lot of time chasing Gtk, which makes it another redhat problem?
edit: The 95% state of all a lot of these FOSS packages is also evidence that there are zero tech billionaire philanthropists. It would take a total of one of them to grab all of these projects and wrangle them into good form.
[0] This is an actual hypothetical, I'm not making an accusation about Scribus. Between the GIMP and Inkscape, they literally are the only people who made a real effort with color for years. Inkscape openly said that their software was only for making images for the web (as opposed to print), as if there were a rational reason to cripple their product and narrow people's interest in it. Now that deviantart is gone, will anyone care about Inkscape anymore? Will the dopamine hit hobbyists get from sharing generative art cause Inkscape to be totally left behind? Why is it hard to design a form for your office's paperwork in Inkscape, a vector drawing program, if forms are just straight horizontal lines and text? Why aren't they trying to merge with Scribus (and LibreOffice Impress/Draw) and create a complete pdf solution? I have no idea.
I'm being very ranty here, and I do want to say that I very much appreciate the work that people are doing for free, in their spare time, for others.
* spill formulas - google first, now supported in MSFT
* # notation - MSFT enhancement to spill formulas not yet adopted in google
* regular expressions - google first, now in Excel
* check boxes - google first, now supported in excel
There must be others. I would expect competitive dynamics where each side tries to build extensions that can’t be replicated on the other side