Automation in modelling
Introduction to the co-author
Benjamin Stoter is a Chartered Accountant and self-taught financial modeller.
He has a wealth of experience in the financial industry, having completed his articles at KPMG before joining a national Real Estate company and working in various roles such as Asset Manager, Operational and Financial Controller, Group Financial Manager, and ultimately Group Head of Treasury, FP&A, and Systems. He has also served as a Group Finance Business Partner, providing strategic support and analysis to business heads within a group of local and international companies.
Currently, Benjamin is a Corporate Finance Executive for one of the largest business management Groups in the US, with offices in the United States and United Kingdom.
Benjamin is passionate about automation, financial modeling, and continuous improvement, constantly seeking ways to improve processes and expand his knowledge.
Why did Benjamin select the topic and why is he passionate about it
Benjamin’s thoughts come from utilizing Automation extensively in his past and current roles with various automation tools.
He selected this topic because Excel automation is a powerful tool that can help streamline and simplify many tedious and time-consuming tasks in finance and other industries. He is passionate about this topic because Excel automation can save a significant amount of time and effort, allowing professionals to focus on more valuable, high-level tasks. Additionally, Excel automation can increase the accuracy and consistency of financial models and other data analysis, leading to more informed decision-making.
Topic and context, what exactly is being automated?
Try to think of one thing today that someone has not attempted to automate.
Not long ago driving a car was 100% manual, literally even changing gears. Manual cars were the default and auto was a luxury extra, now it’s the opposite.
Even calling someone on a phone or tuning on the music was a manual task of having to physically pick up or touch the device and input or push with your fingers the required buttons or dials.
Now it’s simply “OK Google, play music” or “Siri, please call John”.
So what’s happened to financial modelling in this space?
The simple answer is not a lot really. There are certainly many tools and technologies that can be used to automate financial modelling, but the wave of resistance in adopting these has barely taken off. Unlike automation in many other domains, financial modelling automation is only just taking off with a very narrow focus in natively built tools inside Excel like Dynamic Arrays and now Lambda.
Automation of model building has been around in other forms with 3rd party addins (eg Modano and Openbox) but these are still not widely adopted and generally used by expert modellers and not the “average Excel user” who is still just using vlookup, copy and paste and some pivot tables.
This article will hopefully give a broader audience insight to this exciting world of automation for financial modelling.
If we think of modelling automation there are really 3 main areas of automation:
1. Model building automation
This refers to the automation of the process of building financial models. This can include the automation of tasks such as data manipulation, data validation, and scenario analysis. For example, using Excel natively built-in tools like Power Query, VBA, Office Scripts, and Dynamic arrays; add-ons like Power Automate or Modano to automate data cleaning, formatting, and linking to various data sources to create financial models with more efficiency.
2. Automation of model maintenance and updating
This refers to the automation of the process of maintaining and updating financial models. This can include the automation of tasks such as data refresh, updating assumptions, and recalculating results. For example, using a VBA macro to automatically refresh data from a database and update a financial model, or using AI-powered algorithms to automatically generate financial models based on historical data, or pulling and pushing data from Excel to cloud accounting packages.
3. Automation of sharing or using
This refers to the automation of the process of sharing or using financial models. This can include the automation of tasks such as data visualization, reporting, and collaboration. For example, using Power Query, Power BI, VBA, and Office Scripts to automatically generate charts and reports based on financial model data, or using collaboration cloud-based solutions, such as Microsoft Excel Online to share and co-edit financial models with multiple users in real-time.
Each of these has very exciting, yet different use cases and whilst 1 and 2 are closely related and have seen some adoption, #3 has barely seen the light of day.
True multi-user, co-sharing content and data through the same model is an exciting paradigm but generally found outside Excel.
The next few years will see an acceleration of this automation as more people look for better and quicker collaborative decision-making.
If you had to teach this topic in a class to school kids what key tips would you give them to focus on
If we were teaching Excel automation to school kids, we would focus on the following key tips:
Start with the basics: Before diving into automation, it's important to have a solid understanding of Excel basics such as formulas, functions, and pivot tables.
Apply basics to your personal finance: Make sure once you have these basics nailed you can do some what-if analysis and modelling with your pocket money. Perhaps how many cars and when will you have time to wash them so that you can buy a new PS5 VR2?
Check those tools: Explore low or no code automation tools built into Excel. Power Query and Dynamic Arrays are simple examples of built-in automation.
Try VBA Code: Macros are one of the oldest and still very powerful tools for automating tasks in Excel. Learning the macro language is essential for creating more advanced automation where low code might not be able to get all those tricky jobs. Whilst use-cases are declining it’s not dead. Use the recording function: Excel has a built-in macro recording function that can help you learn the macro language by seeing how Excel performs certain actions.
Experiment and play around: Excel automation is a powerful tool with many possibilities. The best way to learn is by experimenting and seeing what you can create.
Keep it simple: It's easy to get carried away with automation, but it's important to keep in mind that simple automation can often be just as effective as complex ones.
Share your knowledge: Automation can be a lot of fun, and sharing what you've learned with others can be a great way to learn even more.
What practical steps can accountants and finance professionals take now to learn more
Firstly make sure you understand the basics of Excel and financial modelling design concepts before getting into automation.
Be clear on the decision and outcome you want to achieve before going too deep as it can get exponentially more complex with some coding and complex design concepts and new tools. Automation can be a powerful tool for streamlining and simplifying many tedious and time-consuming tasks in finance and other industries. However, if you are going in the wrong direction, you can get yourself into a knot and off the track quicker than you would manually.
Once you are clear on what you want to achieve manually, you can automate. There are several practical steps that people can take now to learn more about Excel automation:
Built-in automation inside Excel is only getting better and more advanced as Microsoft pour tons of resources and enhancements into it. Here is a short list and more detailed information has been included below.
PowerQuery has to be the most highly underutilized automation tool yet so easy to use with a low-no code user interface. It’s way easier than using a macro recorder and editing! It can help automate the process of cleaning and transforming large datasets.
Dynamic arrays are game-changing and automatic spill logic across a time series using # and don’t worry about absolute and relative referencing and inconsistent formulas. See this prior article in the series.
This is particularly relevant for reusable pieces of logic through the Advanced Formula Explorer and using Lambda (look out for the article on this soon).
Use the recording function: You can use the recording function to automate a repetitive process of processing a large dataset to generate outputs, and then edit the resulting macro code to customize it to your needs.
In some other cases, you can go deeper and wider with Power Automate, SQL, and Python which can be used to automate tasks and enhance the functionality of Excel and beyond. Power Automate to automate the process of sending email notifications based on specific conditions.
There are a number of third-party add-ins that also have elements of automation.
2. Take advantage of templates: Excel comes with a variety of templates that can be used to automate tasks such as budgeting and invoicing. There are also Excel templates on Eloquens.com (some free, some paid) that can be used to automate tasks such as forecasting, budgeting and basic invoicing. You can use these templates as a starting point to learn how to automate similar tasks. For example, you can use an invoice template to learn how to automate the process of creating invoices. Templates are a double edge sword so be careful how much reliance you place on them, especially if it’s complex and you don’t really understand what it’s doing. They are great for learning, but try to build your own models and re-use those, with caution too.
3. Explore and challenge yourself: The best way to learn Excel automation is by experimenting and seeing what you can create. You can try automating simple tasks, and then gradually increase the complexity as you become more comfortable with the process.
4. Join online communities and forums: There are many online communities and forums dedicated to Excel automation, where you can ask questions, share your work and learn from others.
5. Read books and blogs: There are many books and blogs available on Excel automation, which can provide you with a deeper understanding of the subject.
6. Take online courses: Online courses and certification programs provide a comprehensive and structured way to learn Excel automation. You can find many Excel automation courses on websites like Coursera, Udemy, and LinkedIn Learning.
7. Practice, Practice, Practice: Learning Excel automation takes practice (like any skill), the more you practice the more you will understand the concepts and be able to apply them in real-world scenarios quicker over time.
Need more detailed information and context?
Below expands on Excel automation tools built in/natively installed:
VBA (Visual Basic for Applications) and Macros: VBA is a powerful tool for automating tasks in Excel, and it can be used to create macros, automate data validation, and create custom functions. For example, you can use VBA to create a custom function to calculate the internal rate of return (IRR) of an investment. Macros can be used to automate repetitive tasks such as data manipulation, data validation, and scenario analysis. For example, you can use a macro to automatically format a large dataset or to create a chart based on specific data. More details on VBA can be found in a previous article in this series.
Office Scripts is a new technology that Microsoft has introduced to replace VBA for automating tasks in Office applications such as Excel. Office Scripts (specifically Typescript) is designed to work well with Excel online, while VBA does not. However, it is likely that the transition from VBA to Office Scripts will take some time, as VBA has been widely used for many years and there are a significant number of existing VBA macros and scripts that need to be migrated to the new technology. The Office Scripts feature is designed to automate and simplify mundane, repetitive tasks in Excel. It allows users to record actions performed in an online Excel workbook and save the steps as a script. The script can then be used with Power Automate or integrated with another workflow and shared with others. The feature is incorporated in the Automate tab on the Ribbon for online Excel and does not require users to be programmers. It is similar to VBA macro recording, but is intended for online use and can be shared and reused with others.
Power Query: Power Query was a Microsoft add-on for Excel, but now comes within the Data toolkit that can be used to automate the process of cleaning and transforming large datasets. Power Query can be used to perform tasks such as filtering, sorting, and grouping data, as well as joining multiple datasets together. For example, you can use Power Query to automatically clean and format a large dataset, so that it is ready for analysis. Power Query can be used to accomplish many tasks that would traditionally be done using VBA. Before considering using VBA for a task, it is recommended to explore if the task can be accomplished using Power Query as it may be more efficient and easier to maintain.
Power Automate: Power Automate is part of the broader Power Platform and includes an add-in for Excel that can be used to automate workflows. The Power Automate add-in is called Microsoft Flow in Excel. Power Automate allows you to automate tasks such as sending emails, creating reminders, and updating data in other applications based on specific conditions in Excel. For example, you can use Power Automate to automatically send an email notification when a specific cell in Excel reaches a certain value.
SQL (Structured Query Language) is a programming language that is primarily used to manage and manipulate relational databases. It can also be used as an Excel automation tool to automate tasks such as data manipulation, data validation, and scenario analysis.
SQL can be integrated with Excel in a few ways:
Using Microsoft Query: Microsoft Query is a feature in Excel that allows you to import data from a database into Excel using SQL. You can use Microsoft Query to create a SQL query that imports data into Excel, and then you can use Excel to manipulate and analyze the data. For example, you can use Microsoft Query to import data from a database into Excel, and then use Excel to create a pivot table to analyze the data.
Using Excel Power Query: Power Query is an add-in for excel that can be used to import data from a database into Excel using SQL. Power Query has a built-in SQL editor that allows you to write SQL queries to import data into Excel. For example, you can use Power Query to import data from a database into Excel, and then use Power Query to clean and format the data before it is imported into Excel.
Using a SQL connector: There are several SQL connectors available, such as ODBC or OLEDB, that can be used to connect Excel to a database and execute SQL queries directly from Excel. For example, you can use a SQL connector to connect Excel to a database, and then use Excel to execute SQL queries to import data into Excel.
Using a SQL library for Python: You can use a python library such as pandas-sql to run SQL query on Excel files and import the results into a pandas dataframe. This can then be used for data manipulation and analysis.
6. Python: Python is a programming language that can be used in Excel to automate tasks. Python can be used to automate tasks such as data manipulation, data validation, and scenario analysis. Python can be integrated with Excel using libraries such as Xlwings and Openpyxl. For example, you can use Python to automatically generate a report based on data in Excel
Some examples of Excel automation add-ins are required to be installed by third parties:
Excel add-ins are software programs that can be added to Excel to provide additional functionality. They can be used to automate tasks such as data ingestion, validation, data manipulation, and scenario analysis.
Many third-party companies offer Excel add-ins that can be used to automate specific tasks or provide additional functionality beyond what’s in Excel or make complex tasks significantly easier e.g. building a 3-way model in under 10 minutes.
Some useful links to 3rd party add-ins that assist in automating some of the most manual and challenging tasks in financial modelling, planning, and forecasting (whilst still embracing Excel) include:
These are a few of the many resources available online for learning more about Excel automation and associated apps.
It's worth noting that many of these tools and add-ons have free trials or free versions which you can use to test them out before purchasing which is definitely highly recommended.
The best resource for you will depend on your skill level, learning style, and specific needs (the problem you need to solve).
Where are good places (links) to find out more on the topic
If you are looking to step up your financial modelling skills, make sure you start with a solid understanding of how to build a model in Excel and then step it up with automation training.
There are many resources available online for learning more about Excel automation. Here are a few links that can offer a wide variety of tutorials, templates, and other resources for learning more about Excel automation and financial modelling.
CCH Financial Modelling with Excel: (https://wolterskluwer.cchlearning.com.au/learn/course/external/view/elearning/1606/financial-modelling-with-excel)
Coursera: https://www.coursera.org/courses?query=excel+automation
Excel Campus: https://www.excelcampus.com/
Excel Central: https://excelcentral.com/
Excel Easy: https://www.excel-easy.com/
Excel Jet: https://exceljet.net/
Excel Tips: https://www.exceltip.com/
Excel VBA Programming: https://www.excel-vba-programming.com/
LinkedIn Learning: https://www.linkedin.com/learning/topics/excel-automation
Microsoft Office website: https://support.office.com/en-us/excel
How important is this skill in the context of learning Financial Modelling?
If you are not applying any form of automation in your financial modelling you are:
Falling behind the rest who are
Probably making lots of manual errors
Taking a lot of time and wasting it when you could be learning some of the newest and coolest tech out there.
Excel automation is an important skill to have in the context of learning financial modelling.
Excel automation can help streamline and simplify many tedious and time-consuming tasks involved in building, maintaining, and using financial models from the most basic to the most advanced.
Excel automation can also increase the accuracy and consistency of financial models, which is crucial for collaborative and informed decision-making. For example, by automating the process of data validation, you can ensure that the data used in your financial model is accurate, which can prevent errors that could lead to incorrect decisions.
Additionally, by automating repetitive tasks such as data manipulation, you can free up significant time to focus on more complex and higher-level tasks, such as analysing, visualising, and influencing decisions using these financial models for improved hindsight, insight, and foresight to key stakeholders.
How does all this disruption, AI, and automation talk impact this topic
Let’s just clarify something first shall we, AI is neither artificial nor intelligent, just check out some recent ChatGPT classic logic flaws.
But, it is getting much better and it’s going to learn and improve over time, so we cannot just sit back and laugh without taking some cautious optimism.
But let’s just call it very smart automation and computer engineering code for now in the context of financial modelling. A bot still can’t build a 3-way model (for now).
Automation technologies continue to evolve, they are increasingly being used to automate many of the tasks that were previously done manually in Excel, such as data manipulation, data validation, and scenario analysis.
One of the biggest impacts of Automation on Excel is the increased efficiency and accuracy that these technologies will and have shown to bring today.
For example, some automation tools can be used to automatically identify patterns and trends in large datasets, which can help to improve the accuracy and consistency of larger predictive analytic models.
Similarly, automation tools can be used to automate repetitive tasks such as data validation, which can help to reduce the risk of errors and improve the overall quality of financial models.
The ability to process and analyse large amounts of internal data in real-time whilst subsequently processing and analysing large external datasets in real-time, it then becomes possible to make more informed decisions faster based on the most up-to-date data.
The clincher in all of this is that these powerful automation tools are being built so that they are even easier to access with low code/no code graphical user interfaces.
This means they are available to a much broader audience who don’t code. They are designed to be user-friendly and easy to use, which makes it possible for people including those limited by their technical skills to embrace automation in Excel.
The bottom line, the person next to you who learns how to embrace and apply these tools is more of a threat to your job than the bot inside the machine. The bot still has a long way to go, but the forward and open-minded human will adapt much faster!
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App
If you want to find more information on financial modelling and content visit the Model Citizn website.
