5 ms·

Excel spreadsheets are auditable? If that's the case, why do studies show that so many Excel spreadsheets have major errors in them? https://theconversation.c

Excel spreadsheets are auditable? If that's the case, why do studies show that so many Excel spreadsheets have major errors in them? https://theconversation.com/spreadsheet-errors-can-have-disastrous-consequences-yet-we-keep-making-the-same-mistakes-219356 https://theconversation.com/spreadsheet-errors-can-have-disa... https://phys.org/news/2024-08-business-spreadsheets-critical-errors.html https://phys.org/news/2024-08-business-spreadsheets-critical... https://duckduckgo.com/?q=study+excel+spreadsheets+contain+errors&ia=web https://duckduckgo.com/?q=study+excel+spreadsheets+contain+e...

danielmarkbruce 2y ago
Financials are auditable. Code is auditable. The fact that something can be audited doesn't mean it will be, or will be done well if done.
MegaDeKay 2y ago
Yes. Auditable in this context means something that can be examined by a financial auditor. Auditors understand and can check through Excel but they don't understand code. It is not on the auditor to guarantee that what they are looking at has no errors. Their statements on their audit reports say as much. In my old job, we had to switch away from our working but ancient Information System to SAP for similar reasons.
- remus 2y ago
  While it's true that an auditor can look at an excel spreadsheet, my experience is that they'll never really understand what's happening for anything more than the most basic spreadsheets. It's far too easy to build a spreadsheet that's completely inscrutable, and you end up with an audit that's very shallow.
  - ryoshu 2y ago
    That's generally by design.
    - thfuran 2y ago
      Not generally, no.
  - jimnotgym 2y ago
    Then the auditor should ask you to prove it another way. You may think you hoodwinked them, but don't be so sure that they didn't build their own model to check your work... They do this all the time when people say 'I don't know how it works, the ERP calculates it.'
- lupire 2y ago
  If it can be done, but not done right, that's not a big win (except in an arbitrary legal regulatory sense) than something that can be done. (And of course software "can be audited" too, by a different professional.)
- wwweston 2y ago
  > Auditors understand and can check through Excel but they don't understand code. Spreadsheets are code. Their user-facing side is a particular form of livecode oriented around "sheets"/2D arrays of data tightly connected with reactive code -- which definitely has some legibility advantages -- but they're code. Auditability is legibility. The advantages help. But also the reasons why Excel code is legible to auditors probably have some things in common with the reasons Ruby code is legible to devs who've worked with Ruby or sufficiently-Ruby like languages: exposure, training, and culture that have coalesced around it. And I'd guess that people who don't understand that spreadsheets are code are less likely to be able to read the limits of legibility past where there be dragons, fog, and possibly even fraud.
  - pessimizer 2y ago
    I don't think you get it. They can just click around and see all of the numbers and all of the formulas. You can write notes and directions into the spreadsheet to explain what it's doing. It's code and a UI. Everybody knows it's code, I've written thousands of lines of code to back single spreadsheets. But the output is always a spreadsheet that people can flip through and check the calculations themselves if they like. It's code that outputs code. But is that a helpful observation?
    - wwweston 2y ago
      > I don't think you get it. They can just click around and see all of the numbers and all of the formulas. It's code and a UI. I'm glad you apparently agree with my statements like "Their user-facing side is a particular form of livecode oriented around "sheets"/2D arrays of data tightly connected with reactive code -- which definitely has some legibility advantages," though that agreement does make it a bit strange to lead with "I don't think you get it." > Everybody knows it's code When someone enters the statement "Auditors understand and can check through Excel but they don't understand code" into the discussion -- or in other words saying that an Excel spreadsheet is not code (along with asserting that's why auditors can understand it) -- apparently not everyone does know this and so it's reasonable to reassert that a spreadsheet is made of code. > people can flip through and check the calculations themselves if they like. If, as in any other interpreter, they understand the spreadsheet code that's being interpreted and take the time to follow what it's doing. And that's one reason why the observation that spreadsheets are code can be helpful. The distinctive visual presentation and model of spreadsheets is a legibility convenience for sure, but it can also lull people into believing they're working with something less complex than code. It helps people keep in mind that what the spreadsheet appears to be doing may different from what it's actually doing, either because the complexity has pushed it beyond casual legibility (a common problem in all kinds of code), or perhaps because someone intentionally is using the full power of an interpreted functional reactive programming language (to say nothing of VBA macros) to intentionally hide some aspect of the spreadsheet's operations. It helps people keep in mind that whatever margin of accessibility Excel's affordances provide, it still requires some of the same kind of work to audit it that other forms of code would.
- dilippkumar 2y ago
  > It is not on the auditor to guarantee that what they are looking at has no errors. Um, what then is the point of an auditor?
  - danielmarkbruce 2y ago
    "guarantee" is the key word. If you get a security audit, the security auditor doesn't "guarantee" you code/application is secure.
    - MegaDeKay 2y ago
      Right. It is more like a comprehensive series of spot checks. And like a security auditor, they have free rein to dig in to a particular area if they find something that doesn't smell right. I was on the Engineering side in my old job so I didn't interact with them too much, but here is one example: our company would claim depreciation on some test equipment and once I had to track down one of those pieces of test equipment and show that we indeed had the unit we claimed to have, down to the serial number. They didn't check for every piece of equipment, just the one. Auditability is being able to back up what you claim with a record of some kind (receipt, purchase order, etc). Our QA group referred to this type of thing as "objective evidence". If your QA process says that you "shall have a design review", you need to prove that that review was held. In our case, our process said that minutes for that review had to be recorded. Our QA auditors would ask to see those minutes. Thank you ISO9001.
valar_m 2y ago
Excel users can make mistakes, therefore Excel spreadsheets can't be audited? Is that your point?
- Karellen 2y ago
  My point is that if Excel spreadsheets can be audited, practically speaking, why are these major errors going unnoticed all the time? Edit: My contention is that spreadsheets are in fact, very opaque, and it is incredibly difficult to check that every part of a spreadsheet is actually doing what its users think it is supposed to be doing. Specifically, I posit that it is harder to check a spreadsheet, than it is to check a "traditional" program in a normal programming language, which works on pure data.
  - deleted 2y ago
    [deleted]
  - jdougan 2y ago
    I wonder if something like Lotus Improv would be easier to audit.
  - jimnotgym 2y ago
    Because not all spreadsheets are audited? Just because they can doesn't mean they are. Also a financial audit is very different in what it is looking for, compared to a cyber security audit, for instance. They are looking to prove the accounts show a true and fair view of the company, not 'are your formulas perfect'. They are more likely to copy out your raw data and run their own analysis. Answering your edit, not all spreadsheets are opaque either. We design ours to be audited.
  - Spooky23 2y ago
    Sounds like you work well outside of the domain. Auditors don’t show up to critique your formulas. They are looking at business processes, which means usually the data that flows between things. If it gets to the point where they are digging that deep, they have a finding and are trying to assess the severity. Software, including major business systems are often fucked up. There are major companies using RPA software to robotically use excel to correct some fubar in Oracle financials that wont get fixed for a few years. The beauty of excel is that the business speaks it.
x0x0 2y ago
Now do software in general.
jimnotgym 2y ago
Because there are so many spreadsheets? My team probably created 100 last week. Now think of 100 pieces of software that you are confident are 100% error free?
- Karellen 2y ago
  > Because there are so many spreadsheets? The studies show the percentage of spreadsheets with errors in. Unless you have a reason to suspect this is not independent of the number of spreadsheets, I'm not sure what your point is? > Now think of 100 pieces of software that you are confident are 100% error free? Because software is either buggy or not buggy? And measurements like "defect rate" (e.g. defects/kloc) and "bug severity" do not exist? Come on.
  - jimnotgym 2y ago
    Unless your software is formally verified how would you know? I have to say my experience as a user has been that all software is buggy to some extent. Anyone who has worked in software for a reasonable amount of time will have found a bug in their own code that apparently worked fine, but actually was not quite right. Would anybody on hn stand up and say that they never had a bug?
    - fn-mote 2y ago
      I think you misunderstand the point of the poster you are responding to. They are trying to say that 100% error free is neither appropriate nor useful. It is a binary classification that throws away the important nuance. That’s why they mention measures like “defect rate” and “bug severity”. Those measures are not currently being discussed here, and they should be.
__mharrison__ 2y ago
The ability to audit doesn't necessarily mean that the "code" in a spreadsheet is easy to read, only that it is easy to see...
foobiekr 2y ago
Spreadsheets let you click any field and understand where it came from. Excel can be thought of as an amazing post-execution debugger where you can get a previous output and value for almost any piece of memory ("cell"). I would love a debugger that could do this post-execution for some output.
- ThrowawayR2 2y ago
  Not quite the same thing but https://en.wikipedia.org/wiki/Time_travel_debugging https://en.wikipedia.org/wiki/Time_travel_debugging comes close.
  - MichaelZuo 2y ago
    In this case even getting 99.99% of the way there is still practically worthless, as someone will be interacting with Excel millions of times throughout a career.
  - foobiekr 2y ago
    It's not even in the same ballpark. With excel, you are inspecting _the output_ for a specific run. There is no equivalent in modern programming.
TacticalCoder 2y ago
> If that's the case, why do studies show that so many Excel spreadsheets have major errors in them? Your point, which is completely true, is not incompatible with GP's statement. Excel is ubiquitous in the corporate world and most spreasheet in the corporate world are completely full of errors everywhere. Not just errors but also broken assumptions. I've been tasked with porting a spreadsheet to a dedicated app because people noticed numbers were off and, oh the clusterfuck. Never again. The world started making much more sense to me from the moment I realized most business decisions were taken based on powerpoint presentation containing numbers coming from broken Excel spreadsheets.
JetSetIlly 2y ago
I totally agree with this. I've always found debugging Excel spreadsheets to be a real challenge. I work in accountancy and while its possible to audit the output of the spreadsheet, (as I'm sure it's possible to audit the output in other domains) as an erstwhile Software Engineer I can't help but think it would be better if I could better verify the integrity of the spreadsheet itself. It's far too easy to introduce unnoticed errors. Other people have different views on this but I love the idea of spreadsheets but I don't like any spreadsheet software. I think there's massive room for improvement. But if you were to ask me how to improve it specifically, I couldn't really pinpoint anything. I had a go at doing something in this space to scratch a very specific itch but of course, it doesn't address any of my complaints here. Rather, it tried to tackle another of my complaints - the lack of good support for different number bases https://github.com/JetSetIlly/Ivycel/ https://github.com/JetSetIlly/Ivycel/
listenallyall 2y ago
Software is both testable and auditable, and yet, bugs still exist! Even in open-source code which is also freely and publicly reviewable.

danielmarkbruce 2y ago

Financials are auditable. Code is auditable. The fact that something can be audited doesn't mean it will be, or will be done well if done.

MegaDeKay 2y ago

Yes. Auditable in this context means something that can be examined by a financial auditor. Auditors understand and can check through Excel but they don't understand code. It is not on the auditor to guarantee that what they are looking at has no errors. Their statements on their audit reports say as much. In my old job, we had to switch away from our working but ancient Information System to SAP for similar reasons.

remus 2y ago
While it's true that an auditor can look at an excel spreadsheet, my experience is that they'll never really understand what's happening for anything more than the most basic spreadsheets. It's far too easy to build a spreadsheet that's completely inscrutable, and you end up with an audit that's very shallow.
- ryoshu 2y ago
  That's generally by design.
  - thfuran 2y ago
    Not generally, no.
- jimnotgym 2y ago
  Then the auditor should ask you to prove it another way. You may think you hoodwinked them, but don't be so sure that they didn't build their own model to check your work... They do this all the time when people say 'I don't know how it works, the ERP calculates it.'
lupire 2y ago
If it can be done, but not done right, that's not a big win (except in an arbitrary legal regulatory sense) than something that can be done. (And of course software "can be audited" too, by a different professional.)
wwweston 2y ago
> Auditors understand and can check through Excel but they don't understand code. Spreadsheets are code. Their user-facing side is a particular form of livecode oriented around "sheets"/2D arrays of data tightly connected with reactive code -- which definitely has some legibility advantages -- but they're code. Auditability is legibility. The advantages help. But also the reasons why Excel code is legible to auditors probably have some things in common with the reasons Ruby code is legible to devs who've worked with Ruby or sufficiently-Ruby like languages: exposure, training, and culture that have coalesced around it. And I'd guess that people who don't understand that spreadsheets are code are less likely to be able to read the limits of legibility past where there be dragons, fog, and possibly even fraud.
- pessimizer 2y ago
  I don't think you get it. They can just click around and see all of the numbers and all of the formulas. You can write notes and directions into the spreadsheet to explain what it's doing. It's code and a UI. Everybody knows it's code, I've written thousands of lines of code to back single spreadsheets. But the output is always a spreadsheet that people can flip through and check the calculations themselves if they like. It's code that outputs code. But is that a helpful observation?
  - wwweston 2y ago
    > I don't think you get it. They can just click around and see all of the numbers and all of the formulas. It's code and a UI. I'm glad you apparently agree with my statements like "Their user-facing side is a particular form of livecode oriented around "sheets"/2D arrays of data tightly connected with reactive code -- which definitely has some legibility advantages," though that agreement does make it a bit strange to lead with "I don't think you get it." > Everybody knows it's code When someone enters the statement "Auditors understand and can check through Excel but they don't understand code" into the discussion -- or in other words saying that an Excel spreadsheet is not code (along with asserting that's why auditors can understand it) -- apparently not everyone does know this and so it's reasonable to reassert that a spreadsheet is made of code. > people can flip through and check the calculations themselves if they like. If, as in any other interpreter, they understand the spreadsheet code that's being interpreted and take the time to follow what it's doing. And that's one reason why the observation that spreadsheets are code can be helpful. The distinctive visual presentation and model of spreadsheets is a legibility convenience for sure, but it can also lull people into believing they're working with something less complex than code. It helps people keep in mind that what the spreadsheet appears to be doing may different from what it's actually doing, either because the complexity has pushed it beyond casual legibility (a common problem in all kinds of code), or perhaps because someone intentionally is using the full power of an interpreted functional reactive programming language (to say nothing of VBA macros) to intentionally hide some aspect of the spreadsheet's operations. It helps people keep in mind that whatever margin of accessibility Excel's affordances provide, it still requires some of the same kind of work to audit it that other forms of code would.
dilippkumar 2y ago
> It is not on the auditor to guarantee that what they are looking at has no errors. Um, what then is the point of an auditor?
- danielmarkbruce 2y ago
  "guarantee" is the key word. If you get a security audit, the security auditor doesn't "guarantee" you code/application is secure.
  - MegaDeKay 2y ago
    Right. It is more like a comprehensive series of spot checks. And like a security auditor, they have free rein to dig in to a particular area if they find something that doesn't smell right. I was on the Engineering side in my old job so I didn't interact with them too much, but here is one example: our company would claim depreciation on some test equipment and once I had to track down one of those pieces of test equipment and show that we indeed had the unit we claimed to have, down to the serial number. They didn't check for every piece of equipment, just the one. Auditability is being able to back up what you claim with a record of some kind (receipt, purchase order, etc). Our QA group referred to this type of thing as "objective evidence". If your QA process says that you "shall have a design review", you need to prove that that review was held. In our case, our process said that minutes for that review had to be recorded. Our QA auditors would ask to see those minutes. Thank you ISO9001.

valar_m 2y ago

Excel users can make mistakes, therefore Excel spreadsheets can't be audited? Is that your point?

Karellen 2y ago
My point is that if Excel spreadsheets can be audited, practically speaking, why are these major errors going unnoticed all the time? Edit: My contention is that spreadsheets are in fact, very opaque, and it is incredibly difficult to check that every part of a spreadsheet is actually doing what its users think it is supposed to be doing. Specifically, I posit that it is harder to check a spreadsheet, than it is to check a "traditional" program in a normal programming language, which works on pure data.
- deleted 2y ago
  [deleted]
- jdougan 2y ago
  I wonder if something like Lotus Improv would be easier to audit.
- jimnotgym 2y ago
  Because not all spreadsheets are audited? Just because they can doesn't mean they are. Also a financial audit is very different in what it is looking for, compared to a cyber security audit, for instance. They are looking to prove the accounts show a true and fair view of the company, not 'are your formulas perfect'. They are more likely to copy out your raw data and run their own analysis. Answering your edit, not all spreadsheets are opaque either. We design ours to be audited.
- Spooky23 2y ago
  Sounds like you work well outside of the domain. Auditors don’t show up to critique your formulas. They are looking at business processes, which means usually the data that flows between things. If it gets to the point where they are digging that deep, they have a finding and are trying to assess the severity. Software, including major business systems are often fucked up. There are major companies using RPA software to robotically use excel to correct some fubar in Oracle financials that wont get fixed for a few years. The beauty of excel is that the business speaks it.

x0x0 2y ago

Now do software in general.

jimnotgym 2y ago

Because there are so many spreadsheets? My team probably created 100 last week. Now think of 100 pieces of software that you are confident are 100% error free?

Karellen 2y ago
> Because there are so many spreadsheets? The studies show the percentage of spreadsheets with errors in. Unless you have a reason to suspect this is not independent of the number of spreadsheets, I'm not sure what your point is? > Now think of 100 pieces of software that you are confident are 100% error free? Because software is either buggy or not buggy? And measurements like "defect rate" (e.g. defects/kloc) and "bug severity" do not exist? Come on.
- jimnotgym 2y ago
  Unless your software is formally verified how would you know? I have to say my experience as a user has been that all software is buggy to some extent. Anyone who has worked in software for a reasonable amount of time will have found a bug in their own code that apparently worked fine, but actually was not quite right. Would anybody on hn stand up and say that they never had a bug?
  - fn-mote 2y ago
    I think you misunderstand the point of the poster you are responding to. They are trying to say that 100% error free is neither appropriate nor useful. It is a binary classification that throws away the important nuance. That’s why they mention measures like “defect rate” and “bug severity”. Those measures are not currently being discussed here, and they should be.

__mharrison__ 2y ago

The ability to audit doesn't necessarily mean that the "code" in a spreadsheet is easy to read, only that it is easy to see...

foobiekr 2y ago

Spreadsheets let you click any field and understand where it came from. Excel can be thought of as an amazing post-execution debugger where you can get a previous output and value for almost any piece of memory ("cell"). I would love a debugger that could do this post-execution for some output.

ThrowawayR2 2y ago
Not quite the same thing but https://en.wikipedia.org/wiki/Time_travel_debugging https://en.wikipedia.org/wiki/Time_travel_debugging comes close.
- MichaelZuo 2y ago
  In this case even getting 99.99% of the way there is still practically worthless, as someone will be interacting with Excel millions of times throughout a career.
- foobiekr 2y ago
  It's not even in the same ballpark. With excel, you are inspecting _the output_ for a specific run. There is no equivalent in modern programming.

TacticalCoder 2y ago

> If that's the case, why do studies show that so many Excel spreadsheets have major errors in them? Your point, which is completely true, is not incompatible with GP's statement. Excel is ubiquitous in the corporate world and most spreasheet in the corporate world are completely full of errors everywhere. Not just errors but also broken assumptions. I've been tasked with porting a spreadsheet to a dedicated app because people noticed numbers were off and, oh the clusterfuck. Never again. The world started making much more sense to me from the moment I realized most business decisions were taken based on powerpoint presentation containing numbers coming from broken Excel spreadsheets.

JetSetIlly 2y ago

I totally agree with this. I've always found debugging Excel spreadsheets to be a real challenge. I work in accountancy and while its possible to audit the output of the spreadsheet, (as I'm sure it's possible to audit the output in other domains) as an erstwhile Software Engineer I can't help but think it would be better if I could better verify the integrity of the spreadsheet itself. It's far too easy to introduce unnoticed errors. Other people have different views on this but I love the idea of spreadsheets but I don't like any spreadsheet software. I think there's massive room for improvement. But if you were to ask me how to improve it specifically, I couldn't really pinpoint anything. I had a go at doing something in this space to scratch a very specific itch but of course, it doesn't address any of my complaints here. Rather, it tried to tackle another of my complaints - the lack of good support for different number bases https://github.com/JetSetIlly/Ivycel/ https://github.com/JetSetIlly/Ivycel/

listenallyall 2y ago

Software is both testable and auditable, and yet, bugs still exist! Even in open-source code which is also freely and publicly reviewable.