Programming for Accountants
A large part of both management accounting and financial accounting is the patterns involved in the capturing, manipulating and interpreting of data. Accountants from all over the world disagree on the pattern recognition for treating different types of transactions. That’s basically what accounting standards are meant to address. To give clarity of the treatment of these patterns within transactions. In almost every country in the world, professional qualifications are issued to accountants every year for years of study on these pattern recognition.
The capturing of transactions to a large extent has been automated already as the rise of Enterprise Resource Planning such as SAP and SAGE are now in the hands of SMEs and they don’t need a qualified accountant to capture transactions or post those transactions directly to general ledger accounts unlike in the past.
For data manipulation, Excel has stood as a great tool for accountants. To a large extent, this has served accountants well in the past decades but in the coming decades of big data analytics, data science, machine learning and artificial intelligence, this knowledge will not suffice. Accountants need not fear the automation revolution underway but rather they should embrace it as a survival mechanism. One key way that accountants should embrace automation is by learning to code. To a lot of accountants this would be a ridiculous idea but please hear me out before you bring out the pitch forks.
Covid 19 eventually came to Nigeria in February 2020 as it spread very quick around the world. Around when the lockdown became imminent in Lagos,Nigeria, I had conversations with several professional colleagues who are accountants. Eventually, these conversations usually went towards what we will spend the lockdown doing. Some could work from home and as such would be busy with work. Each person I told I was going to use the lockdown period to learn a programming language laughed in disbelief. The question was usually “why should an accountant bother to learn something so far afield?”.
Why Python?
My response was usually that I plan to learn just Python and the next question was usually “why are you doing this to yourself?why Python?”
My response was usually along the lines of-
A. Python has simple syntax and it is easy to learn compared to other programming languages including VBA for Excel which I have tried to learn before but failed.
B. Python is a free open source program with fantastic resources and libraries all available online for free.
C. It’s a very versatile language that is huge not only in data science and analytics but also in machine learning, robotics, artificial intelligence and web development.
D. It has a huge and very helpful online community. Almost any specific problem you can encounter while using the program has been asked and answered comprehensively on websites like StackOverflow. Solutions are all a Google search away.
E. Most importantly. It can help accountant automate recurring task. The more often the data manipulation is required, the more value Python automation will provide.
I know accountants are still not convinced. They are thinking “these are challenges of the future and those benefits are very general. How can learning Python help me now?”
Speed that leaves an audit trail behind
As accountants, we tend to underestimate the amount of time we take to do some routine data manipulation tasks on Excel. Perhaps one V-lookup or Index Match function here and a few text-to columns there and 30 mins has passed. 1–2 hours a week can quickly add up in the year. The time taken to manipulate the data can also be distracting thus preventing us from looking at the data with fresh eyes. This takes us away from the real value of interpreting data where judgement is involved and an accountant’s brilliance can shine. The speed benefits are exponential the larger your data set. With more digitalization, data sets are becoming even more robust and testing Excel’s limits. This makes the benefits of programming become more obvious.
In addition,program results have easy reproducibility due to the fact that coding automatically provides an audit trail in the code. Anyone that can read your program can understand exactly what you did to the underlying data to manipulate it even if some data has been deleted.
Data integrity
Perhaps you are working on some one else’s or even your own Excel file and there are numerous formulas in the workbook. Rather than copy it to your computer or somewhere else in your directory before doing your manipulation for fear of changing the original file due to error in the process of data manipulation, won’t it be nice to work on the file without manually opening it? Manual processes can lead to errors so where possible you want to avoid it.
Delegation powers
Once upon a time, an accountant was required at the data entry stage of every transaction process. The person had to manually determine the General Ledger to pass double entry for each invoice. Now, even an SMEs no longer requires a qualified accountant for data entry with the arrival of SAP Business One, SAGE and other online accounting software. They can get a non-accountant to do data entry while the accountant focuses on manipulating and interpreting data. In the future, I expect this change to move higher as non-accountants can focus on data entry and more manipulation while accountants do less manipulation with a focus on interpretation of data. With Python, this can be deployed already at accounting departments. The non-accountant just has to run a few programs periodically and the accountant can take it from there.
Upskill
As accountants begin to interface more with coding and programming, they are better able to interface with the IT department with respect to challenges and come up with joint solution on IT in order to improve organisational effectiveness. I am not expecting an accountant to become a full-fledged programmer but, accountants can have IT requirements like requiring a member the IT department to also know coding not just database administration in order to assist the accountant with writing useful code that can save valuable time.
Practical Use Cases of Python for Accountants
As discussed above, Python really shines for an accountant when the task is recurring, when he/she is concerned about the integrity of the data during the manipulation process or he/she simply wants to delegate data manipulation task to a less skilled individual after writing and setting up on person’s computer. However, sometimes Python beats Excel even in some basic one-off processes.
I would like to show 3 practical use cases for Python that shows examples of easy tasks where Python could be a preferred option. Of course these are by no means exhaustive but rather just for illustrative purposes. I will explain the scenario, present the codes which you can tailor for your purpose and hopefully you can understand my appreciation of this wonderful program.
Scenario 1- Splitting files
I have a huge Excel file showing organisational sales data for a period and I need to split the file by regions, (it could be by category. The same logic works for splitting cost data by cost center for example). Different people need the different regional data for their analysis and they only care about their region. I created a sample Excel file for illustration purpose. Your file would probably by more detailed with a larger number of rows and columns. That will not affect the result-
Below is 8 lines of code and comments on the code
Everyday I can run the code for a few seconds and it outputs the result below with separate files for the regions. Lagos, Kano and Abuja (the category or regions can be much larger and the same code would do the work). The 4th item in the directory is where I stored the Python code and split.xlsx is source file.
Below is the output of the Lagos file. I can now send each file to their file owners for further analysis and each file would only contain data that the file owner cares about.
I can even add more code to the script to allow Python to send the files directly via email to the file owner. Its easy to see how that can allow for more data integrity and a fast manipulation even by someone that is not familiar with Excel. The code can easily be copied to his/her system and he/she can run the commands and also send out manipulated files.
Scenario 2 — Combining files
I usually receive periodic report of some transactions. Perhaps the reports are from different subsidiaries, business units or branches. I now have about 100 such reports which I need to combine in order to prepare consolidated analysis. We will use the output from scenario one as the input for scenario two for illustration purpose. The number of files does not matter to the program. It will all be combined perfectly in a new file in my desired directory on my computer. The program can even email the file to the file owner with a customized email message and subject.
I have created a new directory and put the files I want to combine in the directory.
Below are 10 lines of code and comments on each line about what’s going on in the program
Below is the combine Excel file called combine.xlsx added to the directory by Python after running the program
And below is the output of combine.xlsx which only gets the columns I need for each record in the files in the directory. As you can see, I skipped the Unit Cost column because I don’t need it.
You can also merge the files if the column header in the files in the directory are different.
Scenario 3- Removing unwanted characters
I have data from a data source that does not work very well with Excel. I need to remove all special characters from the data before I can work with it. I can’t use text to column because the unwanted characters have been fixed in seeming random parts of the data set and they are different. If only there is an easy way to remove all the special characters without having to do it manually. Well there is an easy way with Python.
For illustrative purposes, below is the unclean data from a data source that’s not very compatible with Excel. The data source could be a database, a website or a pdf file.
Below is the code and accompanying comments. Its just 7 lines of code
Below is the final output
This operation was done using regular expression which can be used to match any type of pattern in your Excel file and either replace or remove them. Regular expression can be used to do much more.
I have chosen very simple instances to show the power of Python. Each of the scenarios above have at most 10 lines of code and could be written in a few minutes and executed in even less time.
Hopefully you are now thinking “I can see how this might be of use to me but how can I get started?”
Well, “Automate the boring stuff with Python” seems like a highly regarded book and popular place to start. The book is freely available here
If you are like me and prefer watching videos to reading books then I would suggest you check out YouTube. There are a lot of very good educators there. Python is a very versatile program and it is used to do a lot of other things. For an accountant I will suggest you just learn the basics of Python to understand the syntax, Pandas for data manipulation and MatPlotLib for charts and graphs.
Resources I personally used for learning the basis of Python are here.I did Pandas series here and I would recommend MatPlotLib here. As you learn, you can easily google or search on YouTube for any concept that you may be struggling with as there are so much online awesome resources on YouTube here
Conclusion
The workflow of an accounting role is evolving. In order for accountants to remain valuable in the future, they must not only be able to understand the output of analysed data but also understand the Data Science tools used to produce the information and work with IT to create value for the organisations that they serve. In order to gain an appreciation of these tools, knowledge of at least the basics of a programming language such as Python or R would be required. This clear knowledge gap in accounting training has recently come in the limelight not only as an area of academic accounting research but also as an area of interest to accounting institutes. I expect accountants to embrace the data revolution as the value of the profession would be significantly reduced if accountants allows themselves to be left behind.
Do you agree that programming can make you a better accountant? I would like to hear from you so kindly leave a comment.
