4 ms·

Do people working in finance / government / other places where Excel heavy fields really not have any tooling for "linting"/"typing" (formula replaced by static

Do people working in finance / government / other places where Excel heavy fields really not have any tooling for "linting"/"typing" (formula replaced by static number in 1 line, integer displayed as date etc) or "unit testing"? This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money.

theshrike79 3y ago
Nope. Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either. The employees use Excel sheets handed over from "someone" and just plug in the numbers. In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...
- veave 3y ago
  I was paid once to turn an Excel sheet into a basic website because the employees kept inadvertently messing with the formulas...
  - pradn 3y ago
    The startup that became Google Sheets was basically an Excel->HTML conversion tool.
Tyruiop 3y ago
Yes. > This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money. Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.
- AndreasHae 3y ago
  Would love to hear your experience on why that‘s the case. Sounds like an idiot proof value proposition to me, considering the damages that could be avoided.
  - yamazakiwi 3y ago
    A few examples are: The people are stubborn and many have had experiences with subpar enterprise software. If they are billing clients, any additional step in software is akin to a national tragedy to them. They will complain something is "broken" if it doesn't fit their mental model of what a software should be before trying anything new.
  - _dain_ 3y ago
    Unfortunately, nothing beats Excel for "agility" and smoothness of the learning curve. You can whip up something that kinda-sorta-works, very quickly, without any software training. Another thing to consider: we are conditioned to believe that the web browser is a "universal runtime", i.e. it's the thing that everyone has, so you should target it. But actually, it's not the only one. When considering Windows computers (which is all that matters in offices), Excel is a kind of dark-matter universal runtime, for dark-matter IT[1]. Better, it's one that doesn't require a persistent server, or logins, and it's peer-to-peer, in a sense. Everyone has Excel installed, everyone has an org email account, therefore everyone can email xlsm files back and forth, no extra setup required (try doing that with exes). No SaaS offering has that kind of caveman convenience. And you might think -- that's horrifying. But email is the way communication/collaboration actually happens in the median bureaucracy, so Excel's ubiquity and file-orientedness is an insurmountable strength. [1] https://en.wikipedia.org/wiki/Shadow_IT https://en.wikipedia.org/wiki/Shadow_IT
croes 3y ago
I have seen people in the insurance business using Word as their calculation tool. If these tools need any type of user interaction they won't use it. How many people still right align text per spacebar or tab if they are pros?
larkost 3y ago
My SO works in accounting, and they really do run without guardrails. And yes there are a ton of different software packages that people can buy for various uses (and do). The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else. Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.
- jimnotgym 3y ago
  Of course the actual accounts are normally not in Excel, but in a proper double entry system
dmoy 3y ago
> Do people... no they don't. Not even for billion dollar scale accounting > because it sounds like an easy product to sell for lots of money. Is it Excel though? Because if not, then you're going to have the mother of all uphill battles trying to get it sold. It would be akin to trying to convince a giant company with hundreds of millions of lines of Java code to rewrite into golang.
_dain_ 3y ago
I used to work for a UK government ministry that has a budget in the tens of billions of pounds per year. I can tell you with confidence: no, there really is no linting, typing, automated testing, version control, nor any other tooling that you would consider "table stakes" for software development, when it comes to spreadsheets. Every single policy mechanism of that ministry relies, at some point in the pipeline, on at least one untested spreadsheet full of copypasted formulae, and inscrutable VBA macros passed down through generations. There are quality control reviews, of course, but they're manual. They don't scale. And these spreadsheets are used to make decisions that decide the fates of nearly 70 million people. And that ministry is not exceptional wrt other ministries, i.e. they're all as bad as this. There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. Nobody had even heard of git. So nothing changed. It was the dark ages, and a major factor in why I quit.
cultureswitch 3y ago
I'm not sure it would be that easy to sell. The overwhelming majority of Excel users don't realize that they need tests.
rvba 3y ago
There are ways to deal with this: array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection). You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them. The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming. Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to. Other comments are amateur hour. There are lots of bad programs too, but programming is not so democratic. Also it is very convenient to blame a mistake on Excel.
- _dain_ 3y ago
  >The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming. I'm one of the other commenters you called "amateur hour". I know about all of those things you mentioned. >array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection). Problem with all of these is some combination of: - they suck - boomers don't know how to use them - you can't use them for compatibility reasons Array formulae: boomers don't know how to use them, and the good ones aren't backwards compatible with older Excel versions or LibreOffice/Sheets (major problem when dealing with ppl outside the org) Formula inspector: I've never found this to be useful Excel marks inconsistent formulae ... inconsistently. It fights you when you're trying to do something slightly non-standard but otherwise perfectly legit, because it's just pattern-matching. Sometimes you have formulae that are fine, then you close and re-open the file and they're marked as inconsistent. Or vice versa. It leads to alarm fatigue. And boomers don't know what it means. Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros. And it's difficult to express mutually-constrained inputs. Pivot tables for summaries: boomers actually do know these, but the problem is they're not reactive. They don't refresh when the data source changes. Locking cells: there's no convenient way to tell at a glance which cells are locked and which aren't (yes I know about the "Find All" way, that's not convenient). Named cells: inconvenient, no namespacing (well there is a kind of namespacing but it's implicit), you can have duplicates of the same name that refer to different things, or dead ones that point to nothing, and there's no good way to debug it. Tables: these are great, but sadly aren't compatible with LibreOffice/Sheets. Column labels cannot be dynamic. You can't put the summary row at the top, only the bottom. They will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik. They don't interact properly with array spill formulae. >You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them. People can and will fuck up even the best-made sheets in irrecoverable ways, that are difficult/impossible to detect automatically within Excel. Yes, we locked all the cells except for inputs, password-protected the sheets, used data validation, used tables, used named references, we did all that stuff you're meant to do and more. It wasn't enough: we'd get back the filled-in sheets and there would be broken references every-fucking-where. I've forgotten exactly what they were doing (it might have been from dragging cells around), but whatever it was, I had to write Python scripts to diagnose it by looping through all the cells and comparing the formulae and sheet layout against a known good copy.

theshrike79 3y ago

Nope. Source: SO works in a field where they handle other people's money and payroll. Not a small mom & pop shop either. The employees use Excel sheets handed over from "someone" and just plug in the numbers. In Britain they messed up the covid numbers because they used the wrong format to store their infection numbers and lost all lines after 65534...

veave 3y ago
I was paid once to turn an Excel sheet into a basic website because the employees kept inadvertently messing with the formulas...
- pradn 3y ago
  The startup that became Google Sheets was basically an Excel->HTML conversion tool.

Tyruiop 3y ago

Yes. > This sounds like an assumption worth validating, because it sounds like an easy product to sell for lots of money. Unfortunately, it's not so simple. I've worked in exactly this field, getting such institutions/companies to adopt this kind of software is ridiculously hard.

AndreasHae 3y ago
Would love to hear your experience on why that‘s the case. Sounds like an idiot proof value proposition to me, considering the damages that could be avoided.
- yamazakiwi 3y ago
  A few examples are: The people are stubborn and many have had experiences with subpar enterprise software. If they are billing clients, any additional step in software is akin to a national tragedy to them. They will complain something is "broken" if it doesn't fit their mental model of what a software should be before trying anything new.
- _dain_ 3y ago
  Unfortunately, nothing beats Excel for "agility" and smoothness of the learning curve. You can whip up something that kinda-sorta-works, very quickly, without any software training. Another thing to consider: we are conditioned to believe that the web browser is a "universal runtime", i.e. it's the thing that everyone has, so you should target it. But actually, it's not the only one. When considering Windows computers (which is all that matters in offices), Excel is a kind of dark-matter universal runtime, for dark-matter IT[1]. Better, it's one that doesn't require a persistent server, or logins, and it's peer-to-peer, in a sense. Everyone has Excel installed, everyone has an org email account, therefore everyone can email xlsm files back and forth, no extra setup required (try doing that with exes). No SaaS offering has that kind of caveman convenience. And you might think -- that's horrifying. But email is the way communication/collaboration actually happens in the median bureaucracy, so Excel's ubiquity and file-orientedness is an insurmountable strength. [1] https://en.wikipedia.org/wiki/Shadow_IT https://en.wikipedia.org/wiki/Shadow_IT

croes 3y ago

I have seen people in the insurance business using Word as their calculation tool. If these tools need any type of user interaction they won't use it. How many people still right align text per spacebar or tab if they are pros?

larkost 3y ago

My SO works in accounting, and they really do run without guardrails. And yes there are a ton of different software packages that people can buy for various uses (and do). The main problem here is that Excel is so very flexible, and everything else is not (generalizing here, but it gets the point across). So the after the third time you are using Excel to handle the exceptional cases, you start to wonder why you are not using it everywhere else. Of course the counter-problem is that it is so flexible that it accommodates your mistakes without pointing them out.

jimnotgym 3y ago
Of course the actual accounts are normally not in Excel, but in a proper double entry system

dmoy 3y ago

> Do people... no they don't. Not even for billion dollar scale accounting > because it sounds like an easy product to sell for lots of money. Is it Excel though? Because if not, then you're going to have the mother of all uphill battles trying to get it sold. It would be akin to trying to convince a giant company with hundreds of millions of lines of Java code to rewrite into golang.

_dain_ 3y ago

I used to work for a UK government ministry that has a budget in the tens of billions of pounds per year. I can tell you with confidence: no, there really is no linting, typing, automated testing, version control, nor any other tooling that you would consider "table stakes" for software development, when it comes to spreadsheets. Every single policy mechanism of that ministry relies, at some point in the pipeline, on at least one untested spreadsheet full of copypasted formulae, and inscrutable VBA macros passed down through generations. There are quality control reviews, of course, but they're manual. They don't scale. And these spreadsheets are used to make decisions that decide the fates of nearly 70 million people. And that ministry is not exceptional wrt other ministries, i.e. they're all as bad as this. There were low ranking people who recognized how stupid this all was, and pointed it out. But hardly anyone with decisionmaking power even understood the problem. Nobody had even heard of git. So nothing changed. It was the dark ages, and a major factor in why I quit.

cultureswitch 3y ago

I'm not sure it would be that easy to sell. The overwhelming majority of Excel users don't realize that they need tests.

rvba 3y ago

There are ways to deal with this: array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection). You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them. The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming. Excel is a program that "gives power to the people" so there are tons of crappy sheets, that often mix data with calculations and so on. But you can make good sheets if you try. And know how to. Other comments are amateur hour. There are lots of bad programs too, but programming is not so democratic. Also it is very convenient to blame a mistake on Excel.

_dain_ 3y ago
>The people who answer to you dont know much about Excel. What makes me wonder how much they know about programming. I'm one of the other commenters you called "amateur hour". I know about all of those things you mentioned. >array formulas, using formula inspector, Excel marks inconsistent formulas too. You can use data validation for selectors, you can lock cells. You van use pivot tables for summaries.. named cells, perhaps tables (type of data collection). Problem with all of these is some combination of: - they suck - boomers don't know how to use them - you can't use them for compatibility reasons Array formulae: boomers don't know how to use them, and the good ones aren't backwards compatible with older Excel versions or LibreOffice/Sheets (major problem when dealing with ppl outside the org) Formula inspector: I've never found this to be useful Excel marks inconsistent formulae ... inconsistently. It fights you when you're trying to do something slightly non-standard but otherwise perfectly legit, because it's just pattern-matching. Sometimes you have formulae that are fine, then you close and re-open the file and they're marked as inconsistent. Or vice versa. It leads to alarm fatigue. And boomers don't know what it means. Data validation for selectors: boomers don't do this (are you seeing a pattern?). More importantly, they don't interact properly with macros. And it's difficult to express mutually-constrained inputs. Pivot tables for summaries: boomers actually do know these, but the problem is they're not reactive. They don't refresh when the data source changes. Locking cells: there's no convenient way to tell at a glance which cells are locked and which aren't (yes I know about the "Find All" way, that's not convenient). Named cells: inconvenient, no namespacing (well there is a kind of namespacing but it's implicit), you can have duplicates of the same name that refer to different things, or dead ones that point to nothing, and there's no good way to debug it. Tables: these are great, but sadly aren't compatible with LibreOffice/Sheets. Column labels cannot be dynamic. You can't put the summary row at the top, only the bottom. They will fill down a formula even when you don't want them to, there isn't a way to turn that off afaik. They don't interact properly with array spill formulae. >You can also build your sheets properly, so formulas are secured from changes, or "draggable" - so even if someome breaks them you correct them. People can and will fuck up even the best-made sheets in irrecoverable ways, that are difficult/impossible to detect automatically within Excel. Yes, we locked all the cells except for inputs, password-protected the sheets, used data validation, used tables, used named references, we did all that stuff you're meant to do and more. It wasn't enough: we'd get back the filled-in sheets and there would be broken references every-fucking-where. I've forgotten exactly what they were doing (it might have been from dragging cells around), but whatever it was, I had to write Python scripts to diagnose it by looping through all the cells and comparing the formulae and sheet layout against a known good copy.