4 ms·

IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more ope

IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more open Microsoft really embrace competing on an even playing field that doesn't rely on ossified proprietary file formats.

rchaud 6y ago
Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to. The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
- zelphirkalt 6y ago
  Now we only need to get everyone to export to a standardized CSV, perhaps one adhering to its own name, comma separated values, and we are good.
- LeifCarrotson 6y ago
  The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth. A business might want to get to improve, say, their quoting accuracy. I've seen lots of places that write quotes using Excel. They use a complicated spreadsheet to estimate "We need $4500 in parts from vendor A, but in previous projects with components from vendor A often needed rework, so we multiply their quotes by 1.5 to account for the risk and for someone (typically Bob) to rework them; Bob's workload is over 90% and he's less efficient when he works overtime, so multiply his total hours by an additional 1.25, we also have to adjust his hourly rate by 1.5 to account for overtime..." It's a Hard Problem to convert the quoting process from one of a few engineers who also do quoting by copying and modifying the blank Excel template and years of human domain expertise into a process where data entry techs input stuff to a CRUD webapp. This is fraught with peril because the Javascript/SQL guy you hired to write the webapp (or, heaven help you, the SAP consultant) hasn't been reworking gear from Vendor A for 15 years and sees what looks like an error when the formula for actual cost from vendors B, C, and D takes their quote price multiplied by 1.1 (for shipping? margin? ) and vendor A's quoted price is multiplied by 1.5, and, hold on, the VBA macro separately takes the the estimated dollar amount purchased from Vendor A, divided by 2000, and adds it to the head of maintenance's estimated hourly total for the project? Making business decisions about logic tied up in Excel formulae is hard. Writing logic in something other than Excel where you can more easily see the business logic is probably harder. Convincing non-technical decision makers to learn VBA to evaluate their vendor selection is probably harder still.
  - rchaud 6y ago
    I wasn't really able to follow this, if I'm being honest. But given the number of dependencies you listed, both human and software-based, it does not seem like Excel is the problem here. It sounds like VBA has allowed that team to build an advanced prototype of a quote generation web app. The next step seems to be to convert the Excel formulas and scripts into JS or Python. Quality assurance may be a hassle, but that is to be expected with any kind of refactoring.
    - lmm 6y ago
      If you want to convert a hacked-together VBA app into something more robust, you have a migration path via VB.net which will interop properly with C# or F# or more-or-less-Python (IronPython). Or you can use something like thrift/protocol buffers to have your hacky part talk to your nicer part. It's harder to do that with Excel - make an application that's half Excel sheet and half "real program" - that's the part that's missing (though I don't think it's actually as bad as grandparent is saying - there are options for integrating external datasources into Excel that do work).
    - LeifCarrotson 6y ago
      I'm sorry it was hard to follow. The Excel sheets I've seen have been hard to follow, too! Also, most of the work is not actually VBA (there's a little), but formulas in spreadsheet cells. The key difference is that the Excel spreadsheet is not a prototype: It's an MVP, which includes "viable"; many businesses have been making money with them for years. Another key feature of the 'prototype' is that the team is able to edit the Excel formulas, but burying the formulas as JS or Python (whether locked away serverside or simply obfuscated by nature of being different language with a new learning curve) removes a critical feature.
aarondia 6y ago
Lack of interoperability is an interesting angle, but not one that I've ran into quite as much. I've usually gotten around that by converting to a csv file in the case of data, or screenshotting graphs, etc. Would love to hear a bit more about your workflows where you're trying to process an .xlsx file in another system. I'd imagine it would be a nightmare, but haven't ran into it myself :)
- fartcannon 6y ago
  CSV files is one of the main problems with excel. Auto-guessing formats is the bane of many casual importers. And the warnings about tsv or csv when you open is tantamount to dark pattern. I can accept it once, but let me disable the pop up. Do Not auto save to xls or xlsx.
  - aksss 6y ago
    I don’t see that as a dark pattern because it’s honest - I will often open up a CSV and then turn on filters, sort the data, maybe highlight a few rows. The warning about losing that when trying to save as a csv is an honest warning. The same as opening a jpg in Photoshop or Gimp, adding layers and trying to save it back out - you’ll also get a warning that you’re about to lose work by saving to a simple file format.
    - fartcannon 6y ago
      Do it once, provide a "Don't remind me again" check box and save that for eternity. The dark pattern is in repeatedly nagging me about this fact.
      - orhmeh09 6y ago
        How is that a “dark pattern?” There’s no deception involved. You’re calling things “dark patterns,” but I think you think dark patterns are just UX you find annoying.
        6y ago
bcoates 6y ago
Not really. Processing xlsx files in full generality is basically reimplementing Excel, but if all you need to do is extract/modify values and formulas or produce a workbook excel will accept there's plenty of libraries that will do that, and it's not that crazy to just hack something up using an xml parser. I had to port some stuff that was using the google sheets API over to manipulating xlsx files instead, and it wasn't a big deal.
RcouF1uZ4gsC 6y ago
In terms of interoperability, on the greatest things about Excel is Ole Automation. You can write a C++ or Visual Basic or C#, program that can programmatically drive Excel. You can pretty much do whatever you want.

rchaud 6y ago

Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to. The value Excel provides far outweighs the drawbacks of a vendor-specific solution.

zelphirkalt 6y ago
Now we only need to get everyone to export to a standardized CSV, perhaps one adhering to its own name, comma separated values, and we are good.
LeifCarrotson 6y ago
The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth. A business might want to get to improve, say, their quoting accuracy. I've seen lots of places that write quotes using Excel. They use a complicated spreadsheet to estimate "We need $4500 in parts from vendor A, but in previous projects with components from vendor A often needed rework, so we multiply their quotes by 1.5 to account for the risk and for someone (typically Bob) to rework them; Bob's workload is over 90% and he's less efficient when he works overtime, so multiply his total hours by an additional 1.25, we also have to adjust his hourly rate by 1.5 to account for overtime..." It's a Hard Problem to convert the quoting process from one of a few engineers who also do quoting by copying and modifying the blank Excel template and years of human domain expertise into a process where data entry techs input stuff to a CRUD webapp. This is fraught with peril because the Javascript/SQL guy you hired to write the webapp (or, heaven help you, the SAP consultant) hasn't been reworking gear from Vendor A for 15 years and sees what looks like an error when the formula for actual cost from vendors B, C, and D takes their quote price multiplied by 1.1 (for shipping? margin? ) and vendor A's quoted price is multiplied by 1.5, and, hold on, the VBA macro separately takes the the estimated dollar amount purchased from Vendor A, divided by 2000, and adds it to the head of maintenance's estimated hourly total for the project? Making business decisions about logic tied up in Excel formulae is hard. Writing logic in something other than Excel where you can more easily see the business logic is probably harder. Convincing non-technical decision makers to learn VBA to evaluate their vendor selection is probably harder still.
- rchaud 6y ago
  I wasn't really able to follow this, if I'm being honest. But given the number of dependencies you listed, both human and software-based, it does not seem like Excel is the problem here. It sounds like VBA has allowed that team to build an advanced prototype of a quote generation web app. The next step seems to be to convert the Excel formulas and scripts into JS or Python. Quality assurance may be a hassle, but that is to be expected with any kind of refactoring.
  - lmm 6y ago
    If you want to convert a hacked-together VBA app into something more robust, you have a migration path via VB.net which will interop properly with C# or F# or more-or-less-Python (IronPython). Or you can use something like thrift/protocol buffers to have your hacky part talk to your nicer part. It's harder to do that with Excel - make an application that's half Excel sheet and half "real program" - that's the part that's missing (though I don't think it's actually as bad as grandparent is saying - there are options for integrating external datasources into Excel that do work).
  - LeifCarrotson 6y ago
    I'm sorry it was hard to follow. The Excel sheets I've seen have been hard to follow, too! Also, most of the work is not actually VBA (there's a little), but formulas in spreadsheet cells. The key difference is that the Excel spreadsheet is not a prototype: It's an MVP, which includes "viable"; many businesses have been making money with them for years. Another key feature of the 'prototype' is that the team is able to edit the Excel formulas, but burying the formulas as JS or Python (whether locked away serverside or simply obfuscated by nature of being different language with a new learning curve) removes a critical feature.

aarondia 6y ago

Lack of interoperability is an interesting angle, but not one that I've ran into quite as much. I've usually gotten around that by converting to a csv file in the case of data, or screenshotting graphs, etc. Would love to hear a bit more about your workflows where you're trying to process an .xlsx file in another system. I'd imagine it would be a nightmare, but haven't ran into it myself :)

fartcannon 6y ago
CSV files is one of the main problems with excel. Auto-guessing formats is the bane of many casual importers. And the warnings about tsv or csv when you open is tantamount to dark pattern. I can accept it once, but let me disable the pop up. Do Not auto save to xls or xlsx.
- aksss 6y ago
  I don’t see that as a dark pattern because it’s honest - I will often open up a CSV and then turn on filters, sort the data, maybe highlight a few rows. The warning about losing that when trying to save as a csv is an honest warning. The same as opening a jpg in Photoshop or Gimp, adding layers and trying to save it back out - you’ll also get a warning that you’re about to lose work by saving to a simple file format.
  - fartcannon 6y ago
    Do it once, provide a "Don't remind me again" check box and save that for eternity. The dark pattern is in repeatedly nagging me about this fact.
    - orhmeh09 6y ago
      How is that a “dark pattern?” There’s no deception involved. You’re calling things “dark patterns,” but I think you think dark patterns are just UX you find annoying.
      - 6y ago

bcoates 6y ago

Not really. Processing xlsx files in full generality is basically reimplementing Excel, but if all you need to do is extract/modify values and formulas or produce a workbook excel will accept there's plenty of libraries that will do that, and it's not that crazy to just hack something up using an xml parser. I had to port some stuff that was using the google sheets API over to manipulating xlsx files instead, and it wasn't a big deal.

RcouF1uZ4gsC 6y ago

In terms of interoperability, on the greatest things about Excel is Ole Automation. You can write a C++ or Visual Basic or C#, program that can programmatically drive Excel. You can pretty much do whatever you want.