What is Xlwings, and how can actuaries use it?

Xlwings is an open-source Python library that bridges Python and Microsoft Excel. This allows for bidirectional interaction between Python and Excel to enhance automation and data analysis capabilities. The package lets users implement logic in Python (with access to the full Python ecosystem) instead of VBA.

Xlwings interacts with Excel through the use of scripts or user-defined functions (UDFs). This is done through a Microsoft Excel add-in that can be downloaded directly in Microsoft Excel.

Python in Excel developed by Microsoft has gained popularity in recent months. Xlwings differentiates itself from Python in Excel in the following ways.

• Xlwings executes code locally on your machine, whereas Python in Excel runs in the cloud. Hence, your data also remains on your local machine (or local data infrastructure when using pro versions) whereas Python in Excel processes data in the cloud.
• Xlwings allows full manipulation of Excel, like adding/removing sheets and controlling the user interface, which is limited using Python in Excel. Note that the functionality is mostly available for Microsoft Windows and there are more restrictions on other operating systems.
• Xlwings allows you access to install any library on your local machine. Python in Excel only allows you access to preapproved Anaconda packages, and users do not have the ability to install additional packages. Typical packages not in the preapproved lists are packages that require internet access or packages designed for direct file system interaction and other specialised packages.

How does Xlwings work?

There are various editions of Xlwings available. In the first instance, we will focus on the free version that’s available for download online.

Users will need to install the Xlwings add-in within Microsoft Excel. At the same time, users will require an install of Python on their local machine, which will be used for executing scripts.

Users will need to maintain a script with their required Python logic along with their Microsoft Excel file. The Python script is saved as a separate file and can be modified with the integrated development environment (IDE) of the user’s choice. The script could be saved in any location accessible to the users. The configuration requires the user to point to the location.

The Microsoft Excel add-in will bridge the connection between the Python script and the workbook that allows native access. Scripts and UDFs will be executed by launching VBA macros that will launch the relevant code in Python.

Xlwings allows users to debug Python code directly from the IDE (similar to a normal Python script), simplifying the development process.

There are additional features available to enterprises who wish to make use of this package. There are paid versions available that make configuration and execution of Python scripts easier. For paid versions, users can host a Xlwings server (on their own hardware or in the cloud) that will perform all Python script executions. This eliminates the need to install Python on each user’s machine and offload computationally intensive tasks to a server. There are several other features available that are not discussed in this article.

Who would benefit from Xlwings?

Xlwings equips users with the tools necessary to automate their workflows and improve efficiency. Users who are relying on multiple manual steps as part of an overall workflow can improve efficiency by developing the end-to-end process within Microsoft Excel.

Xlwings will benefit users who would like to improve the automation of existing workflows in Microsoft Excel but are limited by:

• Functionality that is not available in Microsoft Excel or VBA
• Data transformation processes that are too complex for VBA to handle efficiently; the same applies to very large datasets that are too complex to handle with VBA without bypassing Excel’s row limits
• Complex and/or computationally intensive calculations within Excel
• Limited code governance features available in VBA
• Users already making use of process developed in Python who would also like to implement the logic in Microsoft Excel for improved flexibility and ease of ad hoc analyses

What are the advantages of using Xlwings for actuarial calculations?

The advantages of using Python instead of Microsoft Excel is clear (especially in the context of processing large amounts of data). However, users may wonder what the specific advantages are of making use of Xlwings as opposed to VBA or just standalone scripts. These advantages include the following.

• Users retain the spreadsheet nature of their end-to-end process that allows the flexibility actuaries need in their day-to-day work.
• Access to Python and all its features equips the user to perform tasks otherwise not possible (or not without great complexity) with VBA. Examples include more advanced statistical analysis and data science tasks; integration with existing systems (e.g., databases, SFTP sites, application programming interfaces (APIs)); and faster, more optimised logic.
• Xlwings allows the ability to code in more modern IDE, which is not available for VBA.
• Users have access to improved code governance processes not available to VBA. These include introduction of version control, code review, unit tests on actuarial logic, audit trails and deployment sign-off.
• Code can now be deployed to multiple users by updating central scripts and are no longer locked in VBA that’s linked to a specific spreadsheet.
• Processes that reduce operational risks and reduce cost through improved staff efficiency are automated.
• Python is widely used and has significant support from the online community. The rapid improvement of generative artificial intelligence (GenAI) also makes code development very fast and efficient.
• Xlwings, along with the correct coding strategy, can allow users to get results from running the same code within Microsoft Excel that is used in production. In other words, the same Python logic can be executed as part of another process without Microsoft Excel.
• Execution of code on your local machine or on your internal infrastructure ensures that you meet your data governance requirements.
• Xlwings allows you to partition your data to process chunks at a time, where VBA typically needs to process the full dataset in one go.
• Xlwings allows you to perform calculations on multiple core processing unit (CPU) threads, where VBA is limited to single-thread processing.
• Xlwings allows you full control over your Python environment. Therefore, you have full control over the version of Python and the packages used in your workflow.

What are the disadvantages of Xlwings?

Following are some disadvantages or considerations that users should think about when considering using Xlwings.

• The platform only supports Python. Although Python is a popular language, with a large collection of public libraries available, it may lack in some areas compared to other languages (for example, execution speed).
• Initial configuration can be complicated and require some experience with Python. The process also requires trial and error to get right. This is less of an issue with paid versions that offer more advanced deployment solutions.
• Installing required software on machines is additionally complex due to IT restrictions.
• Data transfers between Microsoft Excel and Python are slower than other methods, and may be inefficient when large data transfers between Python and Microsoft Excel are required. This can be avoided by reading large datasets directly from Python or reducing the output to Microsoft Excel as much as possible. Users are encouraged to perform as much processing as possible in a single command to avoid the runtime overhead.
• Python’s computational performance is typically slower than other compiled applications; however, some libraries exist that attempt to address this shortcoming, such as the Pandas package.
• Some features are not available for other operating systems. Windows offers the most complete feature set, including comprehensive UDF and UI automation. MacOS supports most automation but with some limitations around UDF behaviour and UI control. Linux isn’t supported for Excel automation because Excel isn’t available on Linux.
• Custom functions are exposed in Excel via the Xlwings add‑in, which routes calls to Python under the hood. It could be complex to debug issues where the Python code works as expected but fails in Microsoft Excel.
• Advanced pro features that unlock more capabilities come require a paid licence.
• Custom Xlwings functions rely on good local documentation, where official Microsoft functions are typically very well documented by Microsoft.

Case studies: Using XIwings for reserving and capital requirement calculations at insurers

Following are two case studies where we used the Xlwings package to improve efficiencies in an overall process.

Case study 1: Non-life reserving process

A particular reserving process started from a claim paid data extract direct from a client’s systems. This particular file had more than 1.5 million lines, which cannot be fully opened by Microsoft Excel.

The example reserving process looked as follows:

• Processed raw claims data that involved several transformations and aggregations to derive a claims triangle
• Performed a basic chain ladder reserving exercise on the claims data and produced outputs
• Using the triangulated data, performed a Mack bootstrap stochastic simulation to derive a distribution of the unpaid claims provisions; the simulation was then used to derive an empirical distribution of the unpaid claims provisions and hence an appropriate margin for risk

We implemented this reserving process in Microsoft Excel through the use of Xlwings. Given that we already had the process coded in another coding language, transformation to Python code was fast through the use of GenAI.

The first step around the data processing was mostly handled with the Pandas Python package. From Microsoft Excel, a script was launched providing metadata such as the file path of the claims data extract, valuation date (for calculation of development periods), delimiter used in the claims data extract and a threshold for classification of large losses.

The script used the file path along with the delimiter to read in the data through the Pandas Python package. All data transformations and aggregations were handled in Python, taking full advantage of Python’s speed. The script then produced a claims triangle in a table structure for downstream consumption. The triangle was determined through the aggregation of claims by accident month and development month.

The initial estimate of deterministic unpaid claims provisions was determined using the basic chain ladder method. This was done through a UDF that consumed the flattened claims triangle data. The basic chain ladder method estimated ultimate claims by calculating the age-to-age development factors and applying them to claims paid to date. This was used to derive the estimate of the unpaid claims provisions by accident month.

The last step of the process was to derive a margin of risk using stochastic simulation of the unpaid claims provisions to derive an empirical distribution. This was done using the Mack bootstrap method.

The simulation was performed using a UDF that consumed the aggregated triangulated claims data from earlier processes. The UDF performed the Mack bootstrap to simulate estimates of the unpaid claims reserves. The UDF returned the number of simulated figures that the user defined as a parameter.

Another UDF was then used on the simulated unpaid claim reserve estimates to derive appropriate summary statistics to inform the derivation of the margin of risk.

The overall reserving process was several times faster for the following reasons:

• Data processing using Xlwings directly consumed the source claims file and produced the claims triangle. The same processing would have required additional manual steps to produce the same information in Microsoft Excel.
• The bootstrapping process in Python is faster compared to the process in Microsoft Excel.

Case study 2: Estimating interest rate capital requirements on portfolio of bonds

The second case study considers the calculation of the Solvency Assessment and Management (SAM) interest rate risk capital requirement for a portfolio of bonds.

The estimate of the interest rate risk capital requirements is derived using a discounted cashflow model. The capital requirements are derived through taking the difference in present values of the cashflows under the various shocks relative to the base.

In this case study, we explored an alternative way to derive this capital requirement in Microsoft Excel. The typical solution is to loop through the portfolio of bonds using VBA.

The first step of the process was to derive shocked yield curves for both the upwards and downwards shocks as per the SAM specification. The shocked yield curves were derived using a UDF that consumed the base yield curve along with the parameters used to derive the shock. The UDF calculated the shocked yield curves using logic in Python, and returned a dataset that contained the shocked yield curve for the base scenario, upwards shock and downwards shocks.

A second UDF was used to read in the information for the portfolio for bonds used for the projection (such as nominal values, coupon rates, maturity dates, coupon frequency, etc.), along with the shocked yield curves derived. The UDF returned the estimated present value of each bond under the base scenario, upwards shock and downwards shock. These results were used to derive the interest rate capital requirement for this particular portfolio of bonds.

For enhanced debugging of particular bonds, a third UDF was created in Python that provided a projection summary of a selected bond. Using this UDF returned the estimates of capital and other summary statistics, along with a full projection of cashflows of the bond from the valuation date to the maturity date.

We appreciate that all of this processing would have been possible in VBA. However, Python had the advantage of performing the calculation more efficiently and with easier code management.

Conclusion: Sleeker actuarial processes through Xlwings

Xlwings is a pragmatic way for actuaries and analysts to pair Excel’s flexibility with Python’s power, especially for large data and advanced analytics that strain VBA.

It offers broad library access, robust automation, and deployment options that can keep data and compute on‑premises when required. Success hinges on good engineering hygiene: batch data transfers; keep custom functions lightweight and non‑volatile; and invest in testing, version control and controlled deployments.

For teams weighing Python in Excel, the right choice depends on licensing, data residency, platform support and package needs. Used thoughtfully, Xlwings can streamline actuarial workflows and reduce operational risk without forcing teams to abandon the spreadsheet interfaces they rely on.