Ever since I started my journey into the world of management accounts at Pavers Shoes, I’ve tried to offload as much work as possible onto the spreadsheet. I had 150 stores to report on as quickly as possible so any manual interventions to get the data ready or calculate the results was time I could ill afford. I would go home every night with a fresh problem from that day’s toil and a determination to figure out a way to get Excel to do it for me.

Over time I built up a wealth of Excel knowledge, enough that I might even say I am ‘expert level’; at least compared to your rank and file employee. I was proud of my status as an Excel nerd! 🤓

But in recent times I’ve started to question my faith in my beloved Excel. I’ve even started wonder if Excel is even the right tool for the job. Could there be something that is better?

Discovering PowerQuery a few years back, did a lot to extend the functionality and power of what I could do with Excel, but even with that, I’ve increasingly found myself frustrated with certain limitations within Excel.

At PBH we go through a lot of data. Everything we do is large scale. Certain spreadsheets we have just chug due to the amount of formulas contained within them. And not even PowerQuery is immune to this, as it can sometimes take an age to update. When dealing with these large, complex data files Excel increasingly feels like a hinderance more than a help.

Sometimes its not even the scale of the data, but the complexity of what we are trying to automate. I’ve had a lot of success automating processes with VBA Excel Macros. Most notably with our Bank Reconciliation process, which saves several hours of work every single day. But VBA always feels like a ‘hack’ rather than a solution. Not helped by how icky it is to write VBA code compared to doing ‘real’ programming.

So what is the alternative?

In the world of data science, where data records can be in the millions, and all kinds of advanced data manipulation is required; the tool used is not Excel, but rather Pandas. An extension for the popular programming language Python.

Pandas allows the user to open CSV files, Excel files, query SQL databases and more; then perform advanced data clean up; merge between data sources; and then perform data analysis. With additional libraries like Matplotlib you can then produce beautiful charts and graphs.

Recently I have been tinkering around with Pandas and completed the free training course on the excellent not-for-profit FreeCodeCamp.org and I have to say I am very impressed.

It has several advantages over Excel:

• It can deal with much larger data sets with no slow down.
• Every step is typed out, rather than a sequence of button presses. [While this might initially seem like a downside, it is actually a huge win, as it is much easier to resolve complex issues if they arise, or follow what another person has done with the data previously.] In complex spreadsheets, the abstraction between what you see and what has been done to data is a huge hinderance at times.
• There is just one language to learn. Not a whopping four you have to learn for advanced Excel manipulation (regular Excel formula language, VBA language, M PowerQuery language, DAX PowerPivot language)
• The language you do have to learn is very elegant and logical. Learning VBA, M and DAX in Excel was headache inducing. Python and by extension Pandas is simple. With a few quick lessons I was able to make sense of everything.
• You have the full power of Python available, so with enough programming knowledge you can do anything with the data that a computer is capable of doing with data. Need to have some complex multi-variable “If-statement” thing to add a condition to a column. No problem. Python has your back.
• There are a wealth of additional features that can be added via additional libraries, including a whole world of machine learning and A.I (Python and Pandas is king in the world of A.I)

So Pandas is clearly a badass tool, but could it be used by an accounts team for preparing reporting?

In most businesses… Probably not.

The existing workflows are so ingrained around Excel that the inertia needed to pull it off the change would be nearly impossible, and for most businesses they wouldn’t have the scale to warrant the change. And even if you had a team of Pandas Ninjas 🥷as soon as someone else wants to tinker with the data outside of that team, you end up needing to export it back into Excel.

But I do have this ‘what if’ fantasy in my head… What if you had a team of management accountants who knew these tools. What would that do in terms of automating the reporting cycle each month. I feel like there is potentially a lot of innovation that could done in that sort of scenario.

It almost begs the question, what is better: A data analyst who has been taught about basic accounting like prepayments and accruals, or a management accountant who knows a little of the data analysis tools?

But either way, for now that is just a little idea in my mind.

In the mean time I will continue to tinker with Pandas for fun. Because playing with data IS fun.