How to get started with programming as an accountant

If you’ve ever experienced the fear of “breaking your spreadsheet” or the feeling of dread when Excel freezes up or becoming frustrated with having to do the same formatting and calculations over and over again you will have no doubt thought to yourself “I bet this could be automated”.

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.

If you are eager to learn to code, then the next question is how you should go about doing it.

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.

This approach to learning to code contrasts with how most courses teach programming with a focus on theory, baffling concepts such as objects and classes, building servers and making websites. As an accountant you do not need this knowledge to get started building useful scripts.

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.

And once you’ve got the hang of this you should be feeling pretty satisfied with yourself and hopefully excited about what more you can do. Remember that anything that can be done in Excel can be done in Python - and then some.

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.

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)

Edward Franklin is a Big 4 trained ACA and was a software engineer for Dext.

He is the founder of Two Franklins an ICAEW firm which provides bespoke software to companies to automate spreadsheet processes in finance teams.