Your conclusion is that it was excel causing these errors and you are implying similar errors would not be made in Python. I think it is more because of your experience as a developer why you were able to spot and correct errors.

It is absolutely the interface to excel that causes errors. I can take your excel spreadsheet, format cells, add things, change a reference and generally fuck it up and hand it back to you and you would never know. Whereas, if I change a text file then you can see what's changed. Similar errors are much less common in a programming language.d

You can write tests for an excel spreadsheet if that is your thing. In fact Excel can be driven and automated by any .NET language. It is quite extensible. But most people don't do that because they are not developers and the word "unit test" is not part of their routine.

Which is why they will not be switching to python or R any time soon and even if they do, it will still have similar issues as the Excel version.

Just because you know how to use git history, diffs, etc.. to spot differences in code doesn't mean that is going to help the layperson.

When you're working with people who are good with Excel, they will notice. If you're only ok with excel and just use vlookups, then you're probably going to have problems, especially since those people don't tend to keep a versioned history of their files. Really the main difference is that version control has become an ingrained habit in software development whereas with Excel it's rare

Ah, a True Scotsman is good at Excel. I understand now.

Sure, you can make errors in Python. But you can also write tests to validate the data in Python. And there is a culture of doing so in Python.

>I think it is more because of your experience as a developer why you were able to spot and correct errors.

I don't feel like I spotted errors. I wrote a script to validate the data and the script told me if there were errors (there were).

The act of writing a script to validate the data says otherwise.

Also the people who use Excel as a primary tool are not the type that write unit tests in Python generally. Or would even think to do something like that. That is my point. You, as a developer, would think of something like that. It's not that you couldn't write similar tests with excel (you could, in any .NET language). But that you thought of doing so.

Ok then I think we're agreeing but using different bits of the same point. In Excel there is no culture of good data validation. My experience working as a developer in Python gave me that culture. Agreed.

In GGGP's case they almost certainly have no tests so I stand by the recommendation that they fight hard to get a validation system in place. Probably by moving to Python and/or a RDBMS.