Main Content
Microsoft recently announced Python in Excel, an integration that promises to make financial analysts’ lives much easier.
This integration combines Python’s analytics directly within Excel’s native interface—without additional infrastructure. Essentially, you can input values into your worksheet and run complex Python calculations in the Microsoft cloud (through Azure). Your results will be presented in the Excel sheet along with data visualizations.
In this blog, we explore some potential use cases of Python in Excel for everyday FP&A tasks including transaction matching, anomaly detection and predictive analytics. We walk you through the exact libraries and features you’d need to get started and how you can get even more value from the Python and Excel integration with Vena.
Python opens the doors to a lot of things you can’t traditionally do in Excel.
Simple operations (like data processing) become faster, and complex operations (like allocation optimizations) become possible. The integration also eliminates the reliance on external tools typically used perform these calculations (such as Cloud Service, Jupyter Notebooks and Google Colab) and the manual, complex data transfer processes required to connect with Excel.
Ultimately, the Python-Excel integration allows you to simplify your Excel workflows and remove the noise and errors that come with moving data back and forth.
Most impressively, the Python-Excel integration has a negligible barrier to entry. FP&A professionals can leverage the ever growing libraries of pre-built functions in Python to perform multi-step data computations like analysis and forecasting. You don’t have to build any algoritms yourself—you can just plug and play (more on this in the next section).
Here’s how you can use the Python-Excel integration in your everyday accounting and finance operations:
A universal accounting challenge is translating records from the system they were recorded in to the system they were stored in while painstakingly checking if they match. For example, when reconciling bank transactions with your ERP.
In these situations, common (often irrelevant) errors crop up such as differently typed entries, time discrepancies or a customer ID mismatch.
In Excel, identifying these matches can prove to be an especially time-consuming and unreliable process. Even the best matching solutions are limited in scope, meaning you’ll still spend hours on the process.
Python allows you to take the transaction matching process to new heights, by creating dynamic matching rules, which can be applied across multiple data sets and types.
Examples and use cases include:
| Rule | Use Case |
| Fuzzy Matching Rule: Use fuzzy string matching algorithms to account for slight variations in text entries, allowing for a specified level of similarity (e.g., 80%) to be considered a match. | Handles scenarios where entries may have minor typos or variations. |
| Threshold-Based Matching Rule: If a certain percentage of fields match (e.g., 75%), assume it's a match. | Provides flexibility by allowing for partial matches and accommodating situations where complete matching is challenging. |
| Date Range Matching Rule: If the transaction dates fall within a specified range, consider it a match. | Useful for scenarios where exact matching might not be feasible, but transactions within a certain time frame are likely to correspond. |
| Customer ID Priority Rule: Give higher priority to matching based on customer IDs and consider other fields secondary. | Emphasizes the importance of customer IDs in ensuring accurate matching. |
| Transaction Amount Deviation Rule: If the transaction amounts deviate within a certain percentage (e.g., 5%), consider it a match. | Allows for tolerance in amounts, accounting for potential discrepancies in financial records. |
| Hierarchical Matching Rule: Match transactions hierarchically, considering primary identifiers first and then secondary ones if needed. | Offers a structured approach, focusing on critical matching criteria before evaluating additional factors. |
If you have a range of information in Excel and you’re looking to generate a plan/forecast based on the historical values, you can use Python to run different predictive methodologies.
Whether you prefer ETS cell forecasting, Prophet (Facebook's forecasting algorithm), or ARIMA, Python integrates them into Excel.
Here’s an overview of the various Python libraries and their use cases for forecasting:
| Python Library | FP&A Use Case |
| Scikit-learn | To predict future financial metrics (such as revenue or profit margins). |
| Statsmodels | Time-series analysis to anticipate financial trends and adjust accounting strategies accordingly. |
| Prophet | Forecast seasonality in financial data to optimize resource allocation during peak periods. |
| TensorFlow and Keras | For deep learning (a method of machine learning) in accounting, specifically to detect and prevent fraudulent financial activities. |
The Python integration allows you to set up these functions directly within your existing data range in Excel, so you don’t have to rely on external platforms or complex data transfers.
To make predictive analytics even simpler (and more reliable), Python lets you toggle between the outputs from different models. This comparison gives you a visual representation of how each model performs with your specific data.
An example of a forecast of daily air pollution levels carried out using ARIMA through Python in Excel. Source: Anaconda
Anomaly detection means identifying irregularities within financial data to uncover variations between plan and actual numbers, and looking at whether performance is deviating from the norm.
Here’s how the Python-Excel integration makes it easier:
Python is great at identifying inconsistencies within your data and can even help fix them too (such as by smoothing out anomalies or filling in gaps with machine learning and predictive analytics).
An example of a variance analysis using ANOVA in Python in Excel. Source: Anaconda
If you try to pair diverse datasets across different realms—say from your HRIS system and your CRM system, looking at data such as employee numbers, sales trends and closed opportunities—Excel’s native functionalities can’t keep up.
The integration of Python in Excel allows you to combine and evaluate seemingly unrelated aspects from a variety of the data sources and uncover unexpected connections.
For example, you might ask, “How does the number of employees in my business affect revenue?”
Blending your data from your HRIS, ERP, CRM and HelpDesk Software, with the help of Python in Excel, you might uncover that after a certain number of employees in a department, the number of tickets your help desk receives begins to decrease while revenue remains stable. This may be contrary to your intuition that more employees means more support tickets—meaning increased expenses in technology and support headcount.
You can then dig deeper to find the reason for this; perhaps, as a team grows past a certain point, there is enough internal experience and knowledge that they can resolve issues on their own more often and thereby don’t raise as many tickets. You can then action this by limiting spending on Support as departments reach a certain headcount threshold.
The more complex a function, the more likely that Python is a better choice over Excel alone to find the answer quickly.
An example of a machine learning model using Python and Excel LAMBDA that compares different data sets (temperature, humidity and wind speed) to find commonalities and forecast the weather accordingly. Source: Microsoft
But, don’t count on the Python-Excel integration to support all of your financial planning and analysis needs—it still has its limitations. These are:
This is where Vena helps.
Vena’s centralized database lets you perform complex functions integrating and consolidating data from all your systems—updated in real time—while staying within an Excel interface. Vena also gives you a complete audit trail and central repository of your data, and has in-built reporting templates. Finally, Vena has robust security features that keeps your data safe and gives you complete control over who in your organization has access to the numbers.
But the icing on the cake is how Vena lets you take advantage of the automation capabilities the Python-Excel integration brings, while addressing the gaps we highlighted above.
Let’s say you have data on deals, revenue, and employees in a specific region. Vena will display these numbers in Excel from your CRM and human resource management system. Then, with Python, you can create a model to identify key contributors, roadblocks, and bottlenecks. You can use this model repeatedly in the future for all business analytics—without having to step out of the native Excel interface and with your data getting updated in real time.
Microsoft is making the Python integration available to Excel for Windows users first (now available to all Enterprise, Business, Education, and Family and Personal users running Beta Channel on Windows, the company states), before gradually rolling it out to others (such as users of Excel for Mac, web, iOS and Android).
Join YouTube star Chandoo for this free on-demand workshop. Learn how you can use ChatGPT to create flawless formulas, dazzling dashboards and powerful pivot tables in Excel.
