3 Python Packages for Spreadsheet Users
It is inevitable: spreadsheets users will have their workflows drastically altered by programming languages like Python.
Spreadsheets are brilliant pieces of software. They are flexible, visual, and easy to use, a trifecta that most software does not have. As data sizes become larger and the need for scalable analyses grows, the pitfalls of spreadsheets are becoming more dangerous.
There is repeated folklore of spreadsheet errors that lead to huge financial losses for companies, which certainly do happen ( I will return to them later), but they are not close to the majority of spreadsheet detriments. It is the wasted time. Businesses rely on spreadsheets for critical processes, and when these processes run slowly, the engines of the business run slowly.
A normal Excel spreadsheet holds just over 1 million rows of data. This may have sufficed in 1990, but in the cases where spreadsheets are being used as de facto databases, this is not enough space. And even if the data size limit is not prohibiting, trying to manage a spreadsheet with anything over 100k rows is not worth the time.
Python is excellent at handling large data sizes, so many forward looking spreadsheet users are taking advantage of easy ways to integrate Python into their spreadsheet workflows.
XLwings is an open source Python package that allows you to automate spreadsheet work with Python. You can manipulate your spreadsheet data with the flexibility and power of Python. Handing hundreds of thousands of rows of data in a much smoother process using a package like this. You can also use the package to create macros using Python, replacing VBA. I could write a whole blog about the limitations of VBA (here is a blog listing it as the second worst programming language of 2018).
I was recently working with a group of mortgage brokers, who daily received overloaded spreadsheets filled with housing loans. They use XLWings to easily manage and parse these files, and create complex automations to do so.
Openpyxl is a Python package that lets you easily read and write Excel files from a Python environment. You can create an Excel workbook from scratch, define cell values, merge cells, insert images etc. Imagine you are using Excel but all the button are Python commands you can write. There is more nuance to this description, but this is just the high level — here is the full documentation, if interested.
As a spreadsheet user, you may be thinking “Am I going to have to learn to code to make up for the deficiencies of spreadsheet?”
The answer is no because of tools like Mito. The Python packages mentioned above provide Python interfaces to work with spreadsheets. Mito reverses this, giving the user a spreadsheet that generates Python with each edit. Here is a demo video:
In the long run, I believe we will see Python complimenting spreadsheets more frequently, and I urge heavy spreadsheet users to gain familiarity with the types of packages and tools mentioned in this blog, so that they can be ready to embrace Python in the way that is best for their work and skillset.
