London

81 Chancery Lane
London
WC2A 1DD
01235 821 160
View map

Edinburgh

1 Lochrin Square
92 Fountainbridge
Edinburgh
EH3 9QA
01235 821 160
View map

Dublin

24A Baggot Street Upper
Ballsbridge
Dublin
D04 V970
01235 821 160
View map

Administrative Office

BH Office
Church Street
Ardington
Wantage
Oxfordshire
OX12 8QA
01235 821 160
View map

Send us a message

CLOSE X
Contact Us
10.10.2023

Python in Excel – an Analyst’s Dream?

There’s no doubt that Python and Excel are two of the most popular tools in an actuary’s toolkit. Python is one of the most popular programming languages in the world, with a wealth of packages offering powerful functionality for working with data; Excel is ubiquitous when it comes to spreadsheets, with near-unparalleled familiarity and ease.

The announcement by Microsoft of the official integration of Python into Excel – something previously only possible through third-party packages such as xlwings – is likely to have many contemplating the actuarial applications. In this article, we aim to explore a few of these potential uses and put this new functionality through its paces.

Exploration

To start using Python in Excel, you’ll need to be in Microsoft’s ‘Beta Channel’. If you can see ‘Python in Excel’ in the ribbon (under the ‘Formulas’ tab), then you should be good to go.

If you can’t see this in the ribbon, you may just need to make it visible (try File > Options > Customise Ribbon), or you may not yet have been granted access to the feature.

A great way to get started is with the ‘Try Python samples’ option, which provides a number of examples of various data analysis tasks using Python in Excel. Clicking any of these samples will create a new sheet in your Excel workbook containing the appropriate data and Python code.

To start writing code, type =PY() or use the “Insert Python” button from the ribbon. Python code can then be written directly in the formula bar. A few points to note here:

For our article today, we will look at a fairly simple actuarial task: principal components analysis on yield curve data. This is something that an insurer might want to do as part of calibrating interest rate stresses in a Solvency II model, for example; however, Excel’s linear algebra functions are not quite up to the task! We’ve loaded our data into a table in Excel (forwards curves courtesy of the BoE), and now we’re going to dive in.

We start by importing the PCA class from sklearn – the sklearn library, also known as scikit-learn, is a free Python library often used for machine learning-related tasks. We then read our forwards data from our Excel table into a pandas DataFrame; the xl function that we see below is the means by which we ‘send’ data from Excel to Python.

Looking good! Note that the default here is to return the value as a Python Object; however, we are also able to return data as an Excel Value using the toggle next to the formula bar, as we’ll highlight in the next step.

In the next block of code, we do a few things in quick succession:

Notice how we’re freely able to refer to variables defined in previous cells– all .

This example illustrates how a task that would have been tricky to carry out purely in Excel becomes nearly trivial with the help of Python. However, let’s not stop there – time for some data visualization!

Using the seaborn and matplotlib libraries in Python (referred to by the shorthand ‘sns’ and ‘plt’ here), we can plot a graph of our three principal components and output it to Excel as an image. To the right, we see a similar graph plotted using Excel for comparison.

Of course, where libraries like seaborn really shine are in more complex data visualisations, like the below illustration of seaborn’s pairplot function:

This is just a small taste of the functionality that Python in Excel might enable: we’ve only touched the tip of the iceberg of what’s available in the default packages, and with over 400 further packages available to play with, there’s sure to be something that captures your attention.

Limitations

Of course, it won’t always be the case that ‘Python in Excel’ is the best option available – different use cases may be better suited to just Excel, just Python, or a different solution entirely (such as R). For more complex processes or workflows, Python in Excel may be too limited; a more robust Python development environment may be required, with connectors to read in / out data as appropriate.

Equally, there may be tasks for which Python is unnecessary or uncomfortably complex; it may be difficult for less technical users to follow the flow of calculations or troubleshoot errors. Vanilla Excel may be the right solution there.

Another risk to be aware of is that making any key actuarial processes depend on Python in Excel is reliant on Microsoft maintaining support for this feature going forwards. Microsoft isn’t averse to changing the nature of their offerings or discontinuing them entirely – see Wolfram data types, Money in Excel, or Power View for a few recent examples – so proceed with caution!

Conclusion

Python in Excel is a great tool for those looking to supercharge Excel’s existing capabilities, or for those who wish to explore what Python and its packages have to offer without the hassle of configuring a full development environment. Readers who are interested in previewing this for themselves in will need to get access to the Beta Channel of the Microsoft 365 Insider Program – and keep their eyes peeled for a move across to General Availability.

Jacob Warbrick

October 2023