If you use VLOOKUP, XLOOKUP, INDEX and MATCH, SUMIF etc. in your work and you enjoy building sophisticated spreadsheets, then the good news is you have the aptitude to be a software engineer and you can learn to code. In this article, Edward Franklin, founder of TechFranklin, shares his experience with learning how to programme as an accountant to improve the power, speed, and flexibility of working with spreadsheets and data.
And the answer is of course, yes it can. However, it is hard to know where to start and if you do get started it is easy to become demoralised as you will find that, at first, using code is harder than just using Excel; you will face temptation to give up.
Excel is arguably the most successful software product ever. Its success relies in part on its low barrier to entry. Almost anybody can get started with it as a calculator and incrementally improve their skills.
Programming by comparison has a high barrier to entry. Setting up your computer to run code can be a nightmare and even once it is set up writing code is challenging because unlike Excel the visualisation of the numbers in a grid is abstracted away into words which can be hard to understand.
However, once you “get the hang” of programming you will find yourself with power, speed, flexibility, and reliability that is incomparable to Excel.
Most accountants when they dabble in programming do not get to the inflexion point where they get any benefit from programming and understandably give up.
How to get started
The good news is if you are an accountant, I can strongly recommend you start with Python.
This is because Python has the easiest syntax of any language and can play nicely with Excel, the latter being the key for accountants to get some early success. You want to focus on writing code which does something for you (no matter how trivial) rather than learning theory.
I would strongly advise you try to find some data in csv format (not xlsx) and attempt to reperform manipulations you would do in Excel using Python.
There are lots of ways to do this, but I would advise using a Python library called Pandas and a data structure called a DataFrame, which is similar to a spreadsheet and so, from an accountant’s perspective, is relatively intuitive. For example you can sum the numbers in a column or multiply the values of two columns together to create a new column, just like in Excel. An example is shown below.
Input CSV
Code
Output CSV
Once you have got the hang of manipulating csv data, a plausible next step would be to automate generating formatted spreadsheets using something like OpenPyXl – this is another Python library designed specifically to read and write Excel files.
For example, you can automate the highlighting of headers or the resizing of columns and rows to make the output presentable for reports which can be particularly helpful for monthly management accounts and similar recurring reporting.
An example of highlighting cells is shown below.
Code
Output
As well as following the path I have set out I would also recommend the ICAEW Data Analytics Certificate’s Analyst Pathway Programme which teaches you the fundamentals of Python and is well tailored to accountants.
In addition to online courses, I can recommend a book, which is freely available online, called “Automate the Boring Stuff with Python” which takes a practical non-theoretical approach to using Python and has specific chapters on CSV and Excel.
IT Department says no
Even though Python is open source and free, some organisations may block downloading it to your computer, given the power of what it can achieve and associated risks. If this is the case you have a number of options.
- Use your own computer but be aware of your organisation’s policy regarding putting corporate data on personal devices.
- Use web-based Python environments. Platforms like Anaconda Cloud include many standard libraries and a limited amount of storage for free, but for more complex activities there are limitations.
- Use VBA, which is a programming language embedded into Excel. Getting anything done in VBA is much harder than Python, and with security restrictions and lack of support in Excel Online it's increasingly being sidelined. It's a powerful tool but with newer solutions like Power Automate and Office Scripts (introduced by ICAEW's Excel Community here), using VBA should be a last resort.
- Use JavaScript which runs in web browsers like Chrome and Edge which means your access cannot be blocked. JavaScript forms the basis of Google Apps Script (for integration with Google Sheets) and Microsoft Office Scripts in Excel (for integration with wider M365 online capabilities including Power Automate)
- Wait for the Python in Excel integration to be released by Microsoft (or access it now through the Excel Beta Channel if your organisation allows it)
About
He is the founder of Two Franklins an ICAEW firm which provides bespoke software to companies to automate spreadsheet processes in finance teams.