How FP&A Professionals Can Use Python in Excel for More Powerful Analysis

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 in Excel: What Does It Mean for Finance Professionals?  

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).  

Python-Excel Use Cases for FP&A Professionals  

Here’s how you can use the Python-Excel integration in your everyday accounting and finance operations:  

1. Transaction Matching 

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: 

2. Predictive Analytics 

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: 

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 

3. Anomaly Detection 

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: 

• With Python’s machine learning algorithms, you can train models on historical data to predict expected values. Any deviation from these values become apparent anomalies.
• You can integrate Python’s flagged data points into Excel for graphical representation and interpret anomalies quicker.
• You can create anomaly detection methods based on the specific characteristics of financial data. (For example, a dynamic threshold that considers the historical sales patterns during peak seasons).  

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 

4. Comparing Diverse Datasets 

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 

Limitations of the Python-Excel Integration (and How Vena Helps) 

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: 
 

1. Dependence on a cloud environment: You need a cloud environment to leverage Python's full potential to perform advanced functions. This impacts accessibility, scalability and may require additional costs to use at scale. The code you create in Python within Excel will run through the Microsoft cloud (Azure), which means you might be waiting a few moments for the results to appear, affecting your workflow.
2. Limited collaboration features: Multiple users may find it hard to edit documents simultaneously in Excel, which can cause version control issues.
3. Reporting limitations: Python can perform complicated calculations, but its reporting interface is limited to Excel (meaning you can’t create interactive dashboards).
4. Low data security: Because your Python calculations in Excel are travelling via the internet to the Microsoft cloud in order to produce your output, this has potentially negative implications for your data security.
5. Excel was never meant to be a database: Although the ability to bring in external data sources into your Excel sheet through the Python integration is great for quick calculations, it’s not a scalable solution for all your financial analysis work.  

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.  

Getting Started 

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).  

Learn How To Build a Business Performance Excel Dashboard With ChatGPT

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.