TAKEAWAYS
In today’s fast-paced business environment, proficiency in programming can seem daunting, especially for accounting and finance professionals who may lack technical literacy in this area. This fear often stems from the misconception that programming is an inaccessible skill reserved for tech-savvy individuals. However, this fear can be mitigated by using familiar tools and languages, such as Microsoft Excel or macros programming – a tool that most accounting and finance professionals are already familiar with – to introduce the foundational concepts of programming. Many finance and accounting professionals are already comfortable with Visual Basic for Applications (VBA), a common tool in Excel for automating many of their analysis and reporting tasks. By drawing parallels between Excel VBA and modern programming languages, we can demystify programming and show how learning basic coding can enhance productivity and problem-solving skills.
At its core, programming is about giving any computing device clear instructions to perform tasks, much like writing formulas in Excel or creating financial models. The same precision required in preparing financial statements or tax reports applies to writing code; just as a misplaced figure can cause an error in a financial report, an incorrect symbol in a program will lead to unexpected results. Excel VBA, being close to everyday language, has made it easier for non-technical users to automate repetitive tasks and handle data manipulation in accounting systems. Similarly, learning more modern programming languages like Python (which is already part of the Excel 365 spreadsheet offering as an add-on) can empower accountants to automate more complex processes, such as data analysis, forecasting, and even machine learning. Concepts like loops, conditionals, and data storage, which are present in Excel VBA, translate directly to many other procedural programming languages.
A comparative study between Excel Visual Basic and a modern language like Python can help ease the transition for accounting professionals. For example, while both languages use simple, readable commands (for example, “If …then” statements in Excel VBA or “if” statements in Python), Python offers greater flexibility and a broader range of libraries for handling data and automating workflows. Excel Visual Basic is already a familiar territory for many accountants, making it the perfect bridge towards more versatile programming tools. By starting with what they already know and gradually introducing more powerful languages, accounting professionals can overcome their fear of programming and discover new ways to optimise their work, making programming an essential tool and an inseparable partner in the modern finance landscape.
An understanding of programming concepts and a comparative look at the basics of both Excel VBA and Python can help the accounting professionals to enhance the learning of both programming languages. Programming concepts can be broken down into three fundamental components: sequence statements, control statements, and iterative statements. Each of these is foundational to writing any procedural programs, whether in Excel’s VBA or Python. Let’s examine these concepts and illustrate them with examples in both languages.
1. Sequence statements
The concept of sequence statements refers to the simple execution of instructions in the order they are written. In both Excel VBA and Python, this is the most basic way programs work, where one statement follows another, performing tasks step by step.
Visual Basic example:
In Excel Visual Basic, a simple sequence might look like this:
Here, Excel VBA sets the value of cell r=0.1, n=100, f=1000000, followed by the calculation of p and outputting the result. The steps are executed one after the other.
Python example:
In Python, a similar sequence would be:
Similarly this Python prints the result “The Present Value is: $72.57”, following a sequential execution of instructions.
2. Control Statements
Control statements, such as if-then-else conditions, allow programs to make decisions based on certain criteria. These statements direct the program to different paths depending on the conditions.
Excel Visual Basic example:
This Excel VBA code checks if npv is greater than 0.
If it is, it displays a message box saying, “The NPV is “ & Format(npv, “Currency”)” and “. You should invest.”;
otherwise, it displays “The NPV is “ & Format(npv, “Currency”)” and “. You should not invest.”.
Python example:
In Python, the same logic can be implemented as follows:
Similarly, Python checks if npv is greater than 0,
and prints the corresponding message based on the condition.
3. Iterative statements (loops)
Iterative statements, also known as loops, allow the repetition of a block of code multiple times. Loops are used to automate repetitive tasks.
Excel Visual Basic example:
In this VBA example, the loop sums the year digits from 1 to 5.
The “For” loop runs the block of code five (5) times, updating the value in each subsequent repetition.
Python example:
In Python, the same loop can be written as:
In this Python program, the “For” loop iterates through numbers 1 to 5,
and prints “The sum of the years’ digits is: {total_years_digits}”.
These examples illustrate how procedural languages can be used for data analytics and reporting using basic programming concepts in both Excel VBA and Python. By understanding sequence, control, and iteration in a familiar language like Excel VBA, accounting and finance professionals can easily transition to Python, which offers more flexibility and power for handling larger data sets and more complex financial analyses. Both languages rely on the same foundational ideas, though Python offers a more powerful and flexible approach to solving complex problems.
Working with artificial intelligence (AI) technologies is no longer just another option but a necessity, including for accountants.
ChatGPT is a powerful tool for learning Python and generating code from basic, human-readable prompts. Even without writing code ourselves, we can ask ChatGPT to generate code that performs basic data analysis to reporting. For those familiar with spreadsheets like Excel, this task might be familiar but is likely cumbersome to achieve manually. What makes ChatGPT particularly useful is how it simplifies the process, writing prompts to produce accurate code in a way that feels intuitive. However, it is important to approach this in the right way. Breaking larger problems into smaller, manageable tasks significantly increases the accuracy of the generated code. For instance, using a single large prompt often leads to errors, while breaking it into two smaller, more focused prompts tend to result in more consistent and correct code. This method of dividing complex tasks into parts helps not only with learning, it ensures that ChatGPT provides the best possible results. For example, one could ask ChatGPT to provide the Python codes to automate the process of calculating the payback period, then make a subsequent prompt to ChatGPT to conduct an analysis of the initial outlay and cash inflows, and provide further investment insights in a report for management decision-making.
However, learning Python with ChatGPT still requires a basic understanding of programming concepts, which has been explored earlier. The following process could be adopted in incorporating ChatGPT into working with Python codes.
Writing effective prompts depends on knowing what a computer understands and what it does not. For example, asking ChatGPT to pull specific data, such as an investment decision, will not work unless you specify where the data is stored and how it should be interpreted. Furthermore, understanding code is essential for evaluating the output and ensuring its correctness. Even though ChatGPT can generate correct code, users must review and test it to ensure it performs as expected. Testing is a critical part of the coding process to prevent errors that could range from minor miscalculations to more serious mistakes that could impact business decisions. Therefore, while ChatGPT is a valuable tool for learning and generating Python code, users must stay involved in the process to verify and ensure the accuracy of the results.
Python has emerged as a highly relevant and powerful programming language for modern-day accounting and finance professionals. While many in the field are accustomed to tools like Excel and VBA, Python offers several advantages that can significantly enhance their productivity and analytical capabilities. Its simple, readable syntax makes it accessible to beginners, much like Excel VBA, while its vast libraries, such as Pandas, NumPy, and Matplotlib, provide advanced tools for automating tasks, analysing large data sets, and visualising financial data.
Python’s versatility enables finance professionals to move beyond basic spreadsheets (Python is already part of Excel’s offering this year), enabling them to perform complex tasks such as financial forecasting, portfolio analysis, and even machine learning-driven insights. In a world where data is growing exponentially, Python allows professionals to handle this information more efficiently, uncovering deeper insights and automating repetitive processes.
Moreover, as Python is widely used across industries, acquiring proficiency in it empowers professionals to stay competitive in the evolving landscape. It not only simplifies processes but also opens up new opportunities for innovation and integration with cutting-edge technologies such as blockchain, AI, and big data analytics.
Python is no longer just a technical tool. It is becoming a necessary skill in the toolbox of the modern accounting and finance professional.
Dr Lim Thou Tin, CBIP, is Principal Consultant, Decision Modelling Systems.
© 2024 Institute of Singapore Chartered Accountants