Using Python with Excel – Part Four

Excel and Python for Financial Modelling: Installation Guide

Building powerful models with Microsoft Excel and Python

This article provides an installation guide for running Python through Excel. This is achieved via xlwings, which is a Python library and Excel add-in. It is assumed that Excel and Python are already installed on your computer. For readers that do not have Python yet we have provided a link, which guides the user through the installation process.

Python installation guide

First Python has to be installed. We recommend installing Anaconda, which is a free and open-source distribution already containing all packages for mathematics, science, engineering and data science.

Datacamp has an excellent tutorial on how to install the Python – Anaconda package manager on Microsoft Windows – the tutorial can be found here.

Second, install Python packages pandas and xlwings. To do this open the Anaconda prompt – make sure to run the prompt as an administrator. The code to install them is simple – in the prompt type:

pandas:

conda install pandas

xlwings:

conda install xlwings

This is all that is required to install the pandas and xlwings packages in Python.

VBA installation guide

The last step is for the xlwings add-in to be added to Excel. Open the command prompt (make sure to run this as an administrator) and type:

xlwings addin install

After opening Excel, the xlwings add-in will be shown on the Excel ribbon as per below:

The official xlwings add-in installation guide can be found here.

Interactive data analysis with Excel and Python

Getting started

As a first test we code a simple script that copies the content from one cell into another. We want this to be performed after clicking the “Test Connection” button in the model that can be downloaded at the end of this tutorial.

The code for copying is written in Python, however the user will only interact with Excel. The communication between Excel and Python is achieved through xlwings.

We recommend writing the Python code in Spyder, a platform that is included in Anaconda.

The Python script is shown below:

import pandas as pd
import xlwings as xw

#Function to copy from one cell to another
def TestConnectionPython():

    # Make a connection to the calling Excel file
    wb = xw.Book.caller()

    # Store the main sheet location
    MainSheet = wb.sheets['Main']

    # Store the content of the named cell 'Copy_Test'
    CopyTestValue = MainSheet.range('Copy_Test').value

    # Output the data just to make sure it all works
    MainSheet.range('Paste_Test').value = CopyTestValue

Most of the Python code utilises the ‘xlwings’ library. Users familiar with VBA will notice a clear similarity between VBA and xlwings Python code. To run the above Python script from within Excel, VBA code is required. The VBA code makes use of the ‘xlwings’ add-in to control Python. For most Excel-Python application the following code structure is suffice:

 RunPython (import PYTHONFILE; PYTHONFILE.PYTHONFUNCTION()) 

RunPython is a xlwings function. It imports a Python script and calls a specific function from this script. To call Python function ‘TestConnectionPython’ (which we coded before) the following VBA sub can be used:

 

Sub TestConnectionVBA()
    RunPython (import TestConnection; TestConnection.TestConnectionPython())
End Sub

What the code does is first import the Python script ‘TestConnection.py’. Subsequently, the ‘TestConnection.py’ function calls ‘TestConnectionPython’. This Python function will access the Excel spreadsheet and copies a value from one cell to another.

At the end of this tutorial an Excel sample model and Python script can be downloaded. The Python script will download as a .txt file. Rename it to TestConnection.py and make sure that both the Excel model and the Python script are saved in the same folder, otherwise there might be complications.

Next steps

This concludes the first example on utilising Python in conjunction with Excel through the xlwings bridge. In the next article we will illustrate how powerful Python is when it comes to reading, analysing and manipulating vast amounts of data. The resulting dataset will be written back into the master Excel model.