Ready to write Python code 4x faster?
No, Spreadsheets Are not Going Away
The common first question I get is “why do you think Python can eat Excel”? This is the wrong approach, in my opinion. Python is not going to eat Excel – it is too foundational (large parts of the global economy depend on spreadsheets – not without detriment). But other than market share, it’s just very good at a lot of things.
Spreadsheets are an amazing piece of software, arguably the most successful piece of software ever — Excel itself has over 750 million users. If they are going away, it is not on a time horizon worth prioritizing around. Excel and Google Sheets may lose some market share with new innovative spreadsheets, but if you are looking to tabulate simple data, track some records, or run simple models, Excel is still the platinum standard. In my experience, Python is best used as an augmentation to workflows, eating up the parts where Excel lacks.
Spreadsheet Pain points in Finance
I am explaining these pain points in a financial context, but people from all industries will recognize them.
Slow!
Financial analysts can spend hours a day waiting for models to load/refresh. This could be because of complex formulas or connections to external data sources. Spreadsheets are great when they are a nimble, flexible canvases for analysis – waiting for pivot tables to load is decidedly not nimble.
Can’t handle large data sizes
In finance, analysts are often presented datasets that don’t fit into a spreadsheet, so they need to pair it down. This eats up time, and in some cases diminishes the amount of the data taken into account for an analysis. It can also lead to repeated processes across subsections of a dataset, since the entirety can’t fit in a single spreadsheet.
Lack of repeatability
Spreadsheets can be overly manual and hard to automate. In finance, we see this with data validation work. Combing through a 20 tab spreadsheet to make sure certain values match is a prime process for automation, but VBA is lacking at best.
Python Takes Hold in Finance
Pandas, the most common Python library for data analysis, was developed by Wes Mckinney while at AQR capital, a hedge fund. Now pandas is a staple for financial analysis in Python.
Other great packages for Python financial analysis include:
- Pyfolio – perform risk analysis on financial portfolios (developed by Quantopian)
- Statsmodels – run statistical tests and data exploration on your data
- Zipline – an essential package for connecting to live exchanges for high frequency trading
With packages like these, we see quant teams grow in size at financial institutions, but a bifurcation grows between the Python based teams and the Excel based as well. Python is incredibly powerful, but it can be hard to pickup, which can make collaboration between these teams a challenge.
Bridging the Gap between Python and Excel
Fortunately, Python packages have come along that allow Excel users to start bringing their workflows to Python. Here is a write-up on which workflows are best to start transitioning.
Here are a few packages to consider as you begin to transition financial workflows to Python:
- Mito (I’m one of the creators)
Mito is a spreadsheet interface for Python. You can open Mito in your Python environment and each edit you make in the Mito spreadsheet will generate the equivalent Python. This is a great for spreadsheet users who want to start using Python, but don’t want to spend months learning the syntax.
This package makes reading and writing spreadsheets with Python much easier than using general packages for tabular data ingestion. You can manipulate your Excel file, such as adding and deleting rows, editing formulas, and using filters, all from your Python environment. You can also make new Excel workbooks from scratch. Imagine you wanted to programmatically create a new workbook three times a day — this is hard to automate in Excel, but Openpyxl makes it much more manageable with Python.
This package is not spreadsheet based, but it easily allows non-technical users to create visualizations in Python. All the user does is pass in their dataset, and Lux auto-generates visualizations for that data, which the user can select and use in their notebook.
One important benefit of Python not discussed here, is the ability to directly access databases. I will cover that more in-depth in a later post :)
More Like This
Automating Spreadsheets with Python 101
How to tell the difference between a good and bad Python automation target.
10 Mistakes To Look Out For When Transitioning from Excel To Python
10 Common Mistakes for new programmers transitioning from Excel to Python
Research shows Mito speeds up by 400%
We're always on the hunt for tools that improve our efficiency at work. Tools that let us accomplish more with less time, money, and resources.
3 Rules for Choosing Between SQL and Python
Analysts at the world's top banks are automating their manual Excel work so they can spend less time creating baseline reports, and more time building new analyses that push the company forward.