Rely on others, and you’ll be abandoned. This could be the motto of many financial accounting departments hoping for support from their IT departments. 🤷‍♂️

During my career, I learned an important lesson a bit too late:

My last boss once told me, “An accountant isn’t a striker ⚽, but a defender 🛡️. If you want to be cheered like a striker, you should have chosen a different job.”
When things are going well, there’s no praise. When they aren’t, there’s blame. So, it’s best to make sure everything runs smoothly! 🏃‍♂️💨 Unfortunately, this often means that we in financial accounting only get IT resources when things have already escalated – like just before relegation. Or worse: We’ve already been relegated, and the coach has been fired. 🥴

Sorry to say – but IT often leads to a dead end. 🚧 When asking for ways to automate or for faster, more efficient solutions, you often end up in no man’s land. The request fizzles out, and in the end, you’re back where you started – without having achieved your goal. ❌ Unfortunately, it wasn’t in the OKRs… 😅

But what can you do if you don’t want to drown in day-to-day business and at the same time can’t constantly hire new staff? 🚨 The shortage of skilled workers is real, and the costs for additional personnel are continuously rising. 💸 Employees are often hired to perform repetitive, manual tasks – tasks that could actually be handled much more efficiently through automation. It breaks a financier’s heart ❤️‍🩹 to use expensive resources for routine tasks that don’t add any real value.

The Solution? Automation! 🤖✨
This article is not a step-by-step guide but a collection of ideas. There are many tools that you can use yourself without relying on external help. And yes, the journey can even be fun! 🎉

What Do You Need to Implement This? 🤔

First of all, you need a big dose of curiosity and a willingness to face new challenges and learn continuously. 🧠✍️ The good news is: You don’t have to be a programming expert! No, to use automation tools, all you need is a bit of experimentation and – very importantly – access to ChatGPT. Because even without deep IT knowledge, you can achieve a lot with the right support. 🚀

Where Do You Get the Necessary Knowledge?
It’s easy: The instructions and tutorials are often just a click away. 🖱️ YouTube is a true paradise for learners 📚 (and yes, a YouTube Premium subscription is definitely a worthwhile investment in yourself – ad-free learning is unbeatable!). 😎

Free Tools for Automation: In this article, we focus on a range of free tools available in the Microsoft ecosystem that can be used for automation in finance. These include:

  • 📊 Excel Power Query
  • 🖥️ Excel VBA Programming
  • 🛠️ Microsoft Power Automate
  • 🐍 Python
  • ⏰ Windows Task Scheduler

What Is Not Covered:
Paid tools and solutions like UiPath, Automation Anywhere, AI Builder from Power Automate, Candis, Finway, Pleo, etc., are not part of this article. We focus on tools that are accessible to everyone (with an Office365 license) and can be used immediately to make everyday work easier.

1. Excel Power Query: The Most Underrated Tool EVER 📈

Excel remains the strongest tool in a financier’s toolbox. But no, we’re not talking about VLOOKUP, INDEX, IF formulas, and PIVOT tables; we’re talking about Power Query. With Power Query, any repetitive task in Excel can be done at the push of a button. Especially in accounting, where there are many recurring monthly tasks, Power Query is an indispensable tool. 🔄

Use Cases:

  • PSP Billing: Whether PayPal or Adyen, monthly billing often has to be processed manually, especially if there is no technical integration or specific requirements, such as recording cost centers or other additional information beyond the standard integration. For young companies that want to avoid costly tools but still work efficiently, Power Query is ideal. It allows these files to be automatically prepared, required additional data added, and formatted as needed. Once set up, everything runs at the push of a button, minimizing manual effort.
  • Credit Card Statements: Similar game – the raw data looks the same every month and always needs to be formatted for correct posting. Power Query can turn this manual task into an automated posting template. Once the query is set up, all you have to do in the following months is sit back and press the button – the processing takes care of itself.
  • Provisions Overview: Why book provisions manually when you can create a template with Power Query that calculates and books everything automatically?
  • Bank Accounts: Often, there is no API for retrieving bank statements, especially with foreign banks or new bank accounts where the connection to the accounting system isn’t working yet. In such cases, an interim solution is needed because manual entry of bank statements would be extremely time-consuming. With Power Query, CSV files can be quickly formatted as needed, significantly speeding up the booking process. Once set up, this method saves a tremendous amount of time and reduces manual effort to a minimum.

There are countless other possibilities. You can, for example, automate the monthly close for external reporting, saving a lot of time and increasing accuracy. 📊 Intercompany reconciliations can also be efficiently created and automated with Power Query, so reconciliation processes between different business units are faster and error-free. 🕵️‍♂️ Or personnel cost controlling can be easily set up with Power Query to create an actual vs. target comparison. Even chaotic balance sheet accounts with thousands of bookings can be clustered and systematically tidied up. And that’s not all – there is much more you can automate and optimize with Power Query to save valuable time and increase efficiency. 🌟

2. Macros and VBA: Only for Excel Geeks? 👩‍💻👨‍💻

Power Query is a great tool for editing, cleaning, and manipulating data sets and enables many automations. However, it reaches its limits when it comes to more specific or complex tasks. This is where VBA (Visual Basic for Applications) comes into play. With macros, you can automate almost anything in Excel – from simple data manipulation to creating complex reports. 📊

You don’t have to be a programming pro to use VBA. Thanks to ChatGPT, even a beginner can create simple macros. However, the basics must be known: Where do you enter the code? How do you start the macro? How do you handle it if the code throws errors? ChatGPT helps write the code and debug it when error messages occur. 🧑‍💻

To successfully apply VBA, simply give ChatGPT a clear instruction: Which fields contain which data? What actions should be performed? Where is the file located, and where should it be saved? Then, it’s just a matter of trying it out and reviewing the results. Simply copy the error message into ChatGPT – and after a few iterations, the problems and error messages will be resolved. ✔️

Experimenting is better than studying. Just try it out and get started – you can’t break anything :) 🚀

3. Microsoft Power Automate: The Workflow Assistant 🤖

Microsoft Power Automate is a powerful tool that makes it easier to automate simple but time-consuming tasks. It is particularly well-suited for recurring tasks in finance that are otherwise manual and time-consuming. 🔁

A typical example is month-end planning: Instead of creating the tasks of team members in Microsoft Planner every month manually, Power Automate can automatically generate these tasks. This not only saves time but also reduces error-proneness and ensures a smoother process. 📅

Additionally, Power Automate can be used to send automated reminder emails or obtain approvals, significantly speeding up the approval process and ensuring that all relevant parties are informed and up to date. 📬 By using Power Automate, teams can create simple yet effective workflows that replace many manual processes, thereby increasing efficiency and productivity. 📈

Another major advantage of Power Automate is its user-friendliness and flexibility. It allows users to create workflows that meet their specific requirements without in-depth programming knowledge. Companies can thus develop individual automation solutions tailored exactly to their business processes. 🏢

4. Python: The New Superpower in Finance 🦸‍♂️🐍

For those who want to dig deeper into the automation toolbox, Python is hard to ignore – and here, too, ChatGPT can be a valuable assistant.

I’ve always been fascinated by programming, but I was quickly convinced it would take years before I could program even remotely what I wanted to. Complex codes and even loops seemed daunting, and the path to meaningful applications seemed so far away – like a hobby cyclist trying to keep up with a Tour de France rider. 🚴‍♂️🏆 So, you quickly give up… 🙈

But then ChatGPT came into play. With the right support, it’s surprisingly easy to get acquainted with Python. In essence, Python is much like Excel VBA programming: You need to familiarize yourself with the topic once – about 2 hours for a free YouTube basic course, install Python, understand the programming interface roughly, and learn the basics of the various libraries. Once the basics are in place, ChatGPT can write the first codes. 👩‍💻📝

And suddenly, possibilities open up – a whole universe you’ve always dreamed of 🙂🌌

5. Windows Task Scheduler ⏳

The Windows Task Scheduler may look like a relic from days gone by at first glance. But behind this inconspicuous facade lies a powerful automation tool that still plays an important role in many modern workflows. 🏗️ The Task Scheduler can be used to run scripts, batch files, or programs at specified times, which is particularly helpful when regularly recurring tasks need to be automated. ⏰

A particularly useful application for the Task Scheduler is automating processes in combination with Python. For example, you can create an executable file (EXE) from a Python script – there are plenty of YouTube tutorials for this that offer a quick introduction. 📺 This EXE file can then be executed at precisely defined times via the Windows Task Scheduler, whether daily, weekly, or monthly. Morning, noon, or night – the Task Scheduler works reliably in the background, taking care of tedious routine tasks. 💪

Automated Reports with Microsoft Dynamics NAV 📊

Microsoft Dynamics NAV, also known as Navision, offers several options for automating reports. One option is the scheduling and automatic storage of standard reports. This means that regularly required reports can be created automatically at set times and saved in a defined location. This saves you from manually creating and filing, which can be especially helpful in stressful month-end phases. 😅

Another option is the use of so-called Cubes (NCC Cubes). If this function is available in Navision, you can schedule the Cubes – meaning the data is automatically aggregated and stored so that it is always available for evaluations. Using the appropriate settings in Cube management, you can define exactly how the data should be summarized and when the update should occur. 🔄

Once the data has been prepared through these automated processes, you can continue working with Power Query. Power Query allows you to edit and transform the reports to suit your needs, so the final output format meets your requirements exactly. This way, you not only get efficient and automated reporting but also the flexibility to analyze and present the data as needed. 📈

Automated Reports with DATEV 🗃️

DATEV also offers extensive options for automating reports, especially using KRExport. KRExport can automatically generate ASCII exports. 📂

To realize this automation, you need a batch file that prepares and controls the exports accordingly. This batch file can then be executed at specified times with the Windows Task Scheduler. This way, regular exports can be carried out automatically without manual intervention. For example, all relevant datasets for different clients or several years can be exported simultaneously – a real dream for anyone who hates repetitive tasks! 🎉

Creating such a batch file is straightforward, and with the right instructions, the process becomes even easier. A detailed step-by-step guide on creating and using this batch file is provided by DATEV in documentation number 1021145. This guide explains exactly how to set up the exports and which parameters need to be set to achieve the desired output. 📑

Conclusion

Automation isn’t magic – it just requires a bit of time and willingness to face new challenges. In a time when efficiency and time savings are more important than ever, automation tools like Excel Power Query, VBA, Power Automate, Python, and the Windows Task Scheduler offer a wealth of opportunities to make everyday work as a financier significantly easier. These tools enable you to automate recurring tasks, perform complex data analyses, and handle routine processes with minimal effort. ⚙️

But the most important step is the first: just start and try things out. The threshold for learning something new is often high – especially when it comes to technical topics like automation. But the reward is worth it: more time for strategic tasks, fewer errors due to manual inputs, and significantly increased productivity. 🏆

The great advantage is that you’re not dependent on external help. With the right resources and a little support from tools like ChatGPT, even complex automation processes can be implemented independently. And the best part? You can start right away. There are no long waiting times and no elaborate implementation projects – you can just start and help yourself. 🛠️💪

Digital transformation doesn’t just start with big projects; it can begin with small but effective changes in everyday life. 🌍

♦♦♦