Introduction

On August 22, 2023, Microsoft announced the public preview of Python in Excel.¹ This feature allows users to combine Python and Excel within the same Excel workbook, without any Python installation. Python in Excel can currently be used by anyone. One can acquire access via the Beta Channel Insider level, which is part of the Microsoft 365 Insider Program.² Microsoft allows Insider users to give feedback, which can impact the further development of the solution. The solution is therefore still subject to additional developments by Microsoft. The final release date of Python in Excel has not yet been communicated.

We have been testing Python in Excel focusing on the advantages for specific challenges, especially for financial, actuarial and data science professionals within the insurance industry.

Both Excel and Python are presently widely employed within the industry, albeit mostly independently. Combining both could lead to noticeable improvements when facing specific obstacles. Some of the examples illustrated in this article could also be relevant to other industries.

Challenges in data processing and automated reporting

Some of the big challenges that companies constantly face include efficient data processing and automated reporting. Most companies have different systems for data preparation, calculations and reporting. This leads to having manual and time-consuming processes between systems. For example, one tool can be used to collect, prepare and process the data while other systems are used to interpret and analyse the data. The changing environment requires companies to constantly update the different systems.

While both Python and Excel play important roles in these processes, each comes with limitations, and it remains rare that the entire process is done within one tool. Some of these challenges are highlighted below.

Challenges of Excel

Excel is versatile. It is easy to change calculations, allows you to make amendments in limited amount of time and testing the changes is immediate. This is a clear strength of Excel; however, it also leads to substantial risks. An accidental click or a move of a cell can generate errors where tracing back the errors can become time consuming.

Additionally, Excel is not suitable for manipulating large amounts of data, which becomes problematic when large volumes of data need to be processed and analysed.

Challenges of Python

While Python may be considered easier to use compared to other programming languages, professionals may still hesitate to learn and use it. Unlike Excel, Python lacks a simple graphical user interface, making it more difficult for users to dive into. While solutions like Jupyter Notebook and Visual Studio Code offer capabilities for users to combine live code with narrative text, equations and visualisations, they require users to spend time to generate the user environment, which also costs time and effort (learn and do). Excel is more interactive for the user, and updating can be done quickly by using embedded features of Excel’s interface directly (e.g., by simply clicking).

Updating graphs or tables in Python still requires a foundational understanding of programming concepts, where parts of the scripts also need to be updated, which may deter users. The production of interactive dashboards in Python can also be tedious: the dash library is necessary, and some basic HTML and CSS programming knowledge is also required.

Possible solutions

Insurance companies can address the challenges of both Excel and Python in numerous ways. A range of software is available, but Python in Excel scores well in a range of fields which are highlighted in the next section.

Python in Excel

The combination of Python and Excel could be a powerful alternative due to the following:

• Limited prerequisite training, as most financial professionals are familiar with the interface of Excel and Python is available in every cell of the workbook
• Broad application possibilities of the combination of Excel and Python
• Integrated landscape of both solutions
• High flexibility and transparency
• Potentially a cost-effective solution if it would become directly accessible through the standard Microsoft 365 subscription, (please note that certain functionalities may be limited without the use of a paid license³)

Shifting to Python might seem like a big change for a typical Excel user, even when Python presents clear advantages. Python in Excel facilitates the transition by providing users with a familiar environment embedded with enhanced capabilities.

Moreover, it can be used in collaboration with all other Microsoft tools such as Microsoft Teams or Outlook in the same way as Excel presently is used.

Python in Excel as potential solution

Python in Excel combines the strengths of both platforms, Excel's widely known transparent and easy to use user interface and the capabilities with Python's fine data processing, advanced modelling and visualisation abilities. It leads to a combined package with a wide range of strengths:

• Effective data storage
• Extended data processing and data visualisation capabilities
• Simple and flexible user interface
• Testing and extend transparency
• Strong modelling power
• Easy of access and fast learning of Python

Data storage

Python in Excel offers the possibility to output either Excel values or Python objects. When outputs are stored as Python objects, large volumes of data, exceeding the usual Excel limit, can be stored within a single cell. This allows the user to manipulate the data within the same workbook beyond Excel’s current restrictions. In general, this feature can lighten the size of a workbook while containing more outputs/information than before.

Data processing and data visualisation capabilities

Python is known within data science to be a powerful and robust tool when it comes to data processing. It comprises of existing libraries specifically designed for data processing, and can, for instance, handle various data formats.

Moreover, Python is equipped with existing libraries when it comes to data visualisations. Having these options at hand in Excel allows users familiar with Excel to use a wider range of complex chart options while preserving the straightforwardness and readiness of Excel.

Simple and flexible user interface

In Python and other programming languages, the design of a user interface usually implies a substantial amount of additional coding. Excel is a powerful alternative where a user interface can be created without programming skills and where formatting/customising is easily done and intuitive.

Python in Excel retains this strength from Excel while allowing for the advantages of Python mentioned above. Even when cells contain Python code, the workbook can be formatted in the regular Excel way. For instance, if a cell contains Python code which outputs a numerical value, the entire formatting, like number of decimals or cell colour, can be done as one currently would in Excel.

Testing, transparency, traceability and auditability

Python in Excel allows for outputs to be displayed instantly in a comprehensible and/or graphical way. This gives the user the opportunity to quickly pinpoint whether the code does what was expected and can easily be fixed and tested again. If errors arise within the Python code, no output will be generated. The errors will be displayed, enabling the user to understand their origin and how to fix them.

In general, Python in Excel facilitates transparency, traceability and auditability when compared to Python.

Modelling power

Combining the user interface of Excel with the computational power and robustness of Python can lead to noticeable improvements when modelling.

Python in Excel allows for complex models such as time series or machine learning techniques to be incorporated within your Excel workbook. This, in turn, allows for the simultaneous production of a dashboard of these complex models. While machine learning can already be incorporated in Excel, it is much easier with the help of Python and its pre-existing libraries.

Easy to access and fast learning of Python

Python in Excel allows for financial professionals, who mostly already know Excel, an introduction to the abilities of Python.

Individuals who are unacquainted with Python can use Python in Excel as a training tool to enhance their Python learning. The familiar interface of Excel combined with the instant creation of outputs provides the user with clearer visibility of what the code does and how it works, while remaining within a comfortable environment. This can lead to a significantly faster learning curve when it comes to learning the programming language of Python.

Conclusion

The recently released beta version of Python in Excel is a promising feature where the robustness and power of Python is incorporated within the widely used Excel workbook.

We have tested the beta version of Python in Excel, and we are excited about the possibilities and its potential broad application for financial, actuarial and data science professionals within the insurance industry.

This feature is currently in a preview version and it is not yet clear when it will be publicly released, but we look forward embedding this in our work!

What’s next?

We will be following the development of Python in Excel closely and look forward to its formal release. When more information becomes available, we plan to provide further updates on functionality and possible use cases. These will allow curious professionals to get a flavour of the mechanics and hopefully become just as enthusiastic as we are about the possible use.

¹ Microsoft (August 2023). Announcing Python in Excel: Combining the power of Python and the flexibility of Excel. Available at: https://techcommunity.microsoft.com/t5/excel-blog/announcing-python-in-excel-combining-the-power-of-python-and-the/ba-p/3893439.

² Microsoft. Get started with Python in Excel. Available at: https://support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d.

³ Jallow, N. (August 2023). Python in Excel: Elevating your data analysis capabilities. Microsoft365.com. Available at: https://insider.microsoft365.com/en-us/blog/python-in-excel-elevating-your-data-analysis-capabilities.