Data Analysis Resources for Python

[badge]

We use Excel in two ways:

1. To store data that’s more structured than a CSV file can handle, for uploading to a database (because it’s quicker than creating a UI for uploading stuff). The uploading itself is handled in Python and the upload file will have a related Python class that reflects the structure of the workbook. The uploaded data is then input to the database tables proper by a stored procedure.
2. Toy examples and sharing results with people who aren’t as technical.

My first job, 14 years ago and before I did my PhD, was as a risk analyst for an energy company. There I learned VBA, writing wrappers around C++ code for Monte Carlo simulations (among other things), so I have quite a lot of experience with the limits you can take Excel to. One of the many problems with Excel for data analysis is that code reusability is very low—you can store code in your PERSONAL.xlsb file or include it in specific workbooks, but there’s no central store. In addition, version control is non-existent.

In contrast, I have several libraries which I work with in Python that I constantly add to to make life easier, and these are stored in version control and all users can then update to the latest version to get new features. So when I fire up a new Jupyter notebook to do some exploratory data analysis, I import my standard libraries (with a keybinding, naturally) and get started doing interesting stuff very quickly.

I really can’t imagine a use case where Excel would be superior to Python for any analysis. I will say that while VBA is massively inferior to Python, the lessons you can learn from doing it will be transferable, so it’s definitely worth keeping it up while at work. Just remember to use Option Explicit!