Using Python with Excel for Powerful Financial Modelling Solutions
In this mini-series – Using Python with Excel – we aim to highlight how Excel can be enhanced by the programming language Python to solve big data or complex analytical challenges.
In Part One we overview the strengths, but also limitations of Excel and its local programming language, VBA, and highlight when Python may provide an appropriate alternative partner for Excel.
This mini-series will subsequently provide a practical guide to Python, including an installation guide and working examples of Python integrated with Excel.
Excel’s strengths and limitations
Microsoft Excel remains the go to spreadsheet software for financial planning, analysis and reporting. Major advantages are Excel’s numerous easy-to-use functions and features and user-friendly interface. In most cases users can analyse data and create financial models without ever needing to consider programming.
Nevertheless, where tailored routines or functionality are required users can code within Excel via Visual Basic Applications (VBA), automating any number of tasks from formatting to data manipulations and restructuring. Simply, most tasks that are too time consuming to be done manually or which are too onerous on Excel performance to be solved using normal Excel functionality can easily be resolved with the implementation of VBA code.
However, even with the aid of VBA in Excel models there remain some clear limitations for Excel in two areas:
- Dealing with ‘big data’ – i.e. very large data sets
- Complex data analysis techniques – e.g. machine learning in atypical distributions
The amount of data that can be stored and analysed efficiently within Excel is relatively low. Its statistical power is insufficient for implementing precise modern analytical technologies that utilise, for example, machine learning techniques. On the other hand, Python, as a leading open-source programming language worldwide, is incredibly strong in providing solutions to these limitations.
Notably, in the last two years we have received a steady increase of requests to provide solutions (typically with respect to proprietary ‘big data’) which just cannot be resolved in Excel alone. However, by teaming Python with Excel we have been able to provide precise solutions delivered in a familiar user-friendly framework.
When to use Python over Excel and VBA
Most financial modelling is still done in Excel and at times with the assistance of VBA – largely, as mentioned, to automate repetitive tasks and save time.
However, the increasingly present opportunity and ease with which even moderately sized companies can harvest operational data points means there is a definite trend in the modelling and analytical space towards trying to interpret very large data sets and ultimately Excel is not built for this purpose.
For example, a key tenant of deep and serious data analysis is PRODUCTIONISED UTILITY WORK, something that is simply currently beyond Excel’s processing power or structure. Necessary tasks such as movement of files, data acquisition, data transformations, derivation of new data, etc., all at large scale and speed cannot be done in Excel alone, but can be done by teaming with Python.
Python as an open-source programming language is not designed for one purpose – for example, it is used in web and software development, but also in data science and machine learning. Thankfully, a focus on these two latter and similar fields by the Python programming community mean there are huge libraries of statistical and mathematical tools available. These can be cherry-picked and manipulated to undertake the precise analysis required – importing data from Excel, running routines in Python and exporting results back to the Excel interface.
In the next part of our mini-series we will discuss how we at LMS use Excel in conjunction with Python and introduce some advanced data analysis techniques for big data models.