Ditching Excel for Python in a legacy industry

- Abstraction. It's very difficult to effectively abstract parts of a model in Excel. It's a bit like a doctor having to 'model' a human being as a collection of atoms, rather than having abstractions like organs, cells etc. This makes it very hard to build re-usable components, so analysts end up reinventing the wheel. You also quickly hit a 'complexity ceiling' in Excel, above which mistakes and errors becoming much more likely, and complexity is very difficult to manage.

- Existing libraries provide a huge range of sophisticated calculations and operations for which we don't need to write any code.

- Separation of concerns - particularly separating data from model. Easy in Python, hard in Excel. Another aspect of this is that using data science software promotes the use of tidy data[0] (i.e. clear thinking about how data should be structured).

- Unit/integration tests. For complex models, these are essential. Users of Excel (even extremely clever/competent people) don't have have a great reputation for producing error-free spreadsheets, and I think this is an important reason why, alongside copy-paste errors. The tools for testing in Excel/VBA are rudimentary.

- Version control. This is particularly important for historical reproducibility because it allows us to run past models, and also understand what has changed in the codebase since.

I appreciate some of the above is also possible in VBA, but if you're writing an entire model in code and not really using Excel at all, my view is it's better to use a more sophisticated programming language.

There is also an important cultural point of having to re-skill everyone, and I can see that in some context that means in the short run at least, Excel/VBA may still be better overall.

I've written a bit more about all of this here: https://www.robinlinacre.com/transforming_analytical_functio...

[0] https://vita.had.co.nz/papers/tidy-data.pdf

Handing over to IT usually means their flexible spreadsheet that they can change as they require, turns into an expensive and inflexible black box that only IT can change and that doesn’t integrate with the rest of their decisions. Also the new solution also probably has errors and pulls currency info from the same endpoint. Excel isn’t perfect, but it’s used for a reason.

To use your analogy, You can wait for an electrician to change your lightbulb, but that means your going to be working in the dark for longer.

This is nicely solved by using R server.

I’ve worked in an R server shop, and the experience is really nice. You log on to the server in chrome or Firefox and the browser window basically becomes RStudio and all calculations are done on the server and all code and data also lives on the server which is a huge bonus in terms of data protection. No copies are floating around on peoples laptops and if Johnny is sick and forgot to push his code to git - no worries, it’s all on the r studio server.

I don’t now of a nearly as good Python solution. I think Conda suggests using jupyter lab, and while that is a great environment it’s not great if it’s all you can use.

I worked for a company that used an opaque excel spread sheet as a part of its accounting system - turns out there where bugs and we found a massive short fall one of the contributing factors in the collapse of the company.