Executing a Python Script within Excel Using xlwings

This post demonstrates how to utilize the xlwings Python package to execute a Python script within Excel.

Running a Python script in Excel using xlwings

In this post, I show how to run a python function in Excel in terms of time series modeling perspective.

Once we know how to handle input and output data in matrix format, we no longer need to think about it. We can solely focus on changing the procedures or models to be calculated or estimated in Python.

Installation of xlwings

Among several approaches, we can use xlwings package to run a Python script in Excel.

xlwings offers various features, including user-defined functions similar to Excel’s. However, this post concentrates on using Python for model estimation with Excel data and viewing the results within Excel. By starting with a simple case, subsequent advanced applications become more manageable.

Before you begin this task, make sure to install xlwings and then the Excel add-in sequentially. First, install xlwings in the conda prompt:

conda install xlwings

Second, install the xlwings add-in in the conda prompt:

xlwings addin install

After completing the two installations correctly, you’ll find an “xlwings” menu in Excel.

Python and Excel VBA codes

As a simple illustration, we’ll utilize a correlation matrix calculation example. The corr_test.xlsm file, depicted in the figure, outlines the process straightforwardly.

Upon clicking the calculation button, the shaded area is populated with the correlation matrix generated by executing the corr_test.py file. Here, the input data resides in the Excel file, and the output (correlation matrix) is then sent back to the Excel file.

Consequently, we have only to know how to code the corr_test.py file and the button’s VBA macro.

The button for executing the Python code is linked to the following VBA macro function. This macro code simply calls the ‘main()’ function in the ‘corr_test.py’ file using “RunPython“.

However, to utilize xlwings’ RunPython() function in a workbook, you need to set a reference to xlwings in the VBA editor by checking the xlwings box in the VBA Editor References menu., just like when using other add-ins.

Sub btn_Click()
    RunPython ("import corr_test; corr_test.main()")
End Sub

Python code using xlwings

As anticipated, the ‘corr_test.py’ file comprises three steps: 1) retrieving data from Excel, 2) computing a correlation matrix, and lastly, 3) storing the results back into Excel. With the assistance of the xlwings Python package, these tasks are simplified.

import numpy as np
import xlwings as xw
 
def main():
 
    #----------------------------------------------
    # 1. select input data sheet
    #----------------------------------------------
    #sht = xw.Book.caller().sheets[0]       # 1st sheet
    sht = xw.Book.caller().sheets['Sheet1'] # with name
        
    #----------------------------------------------
    # 2. read input data as list
    #----------------------------------------------
    lt_name = sht.range('B2:G2').value  # column names
    lt_data = sht.range('B3:G28').value # data
    
    # convert data to a NumPy array
    np_data = np.array(lt_data)
    
    # convert names to a row and column vector
    np_data = np.array(lt_data)
    np_name_row = np.array(lt_name)                
    np_name_col = np.array(lt_name).reshape(-1, 1)
     
    #----------------------------------------------
    # 3. process
    #----------------------------------------------
    # calculate correlation matrix
    np_data = np_data.astype(float) # to ensure numeric data
    np_corr_out = np.corrcoef(np_data, rowvar=False)
 
    #----------------------------------------------
    # 4. write results to specific ranges in Excel
    #----------------------------------------------
    sht.range('J2').value = np_name_row
    sht.range('J3').value = np_corr_out
    sht.range('I3').value = np_name_col

The processing is simple. In this case, Excel files executing VBA code are automatically detected by xlwings, so there’s no need for separate specification.

In the main() function, it first selects Sheet1 and then reads the data. Next, it calculates the correlation matrix using the np.corrcoef() function. Finally, it sends the final result back to Excel.

Now that we’ve learned how to extract data from Excel and store results back into Excel, the remaining step is to tailor the ‘process’ section to suit your analysis objectives.

Originally posted on the SHLee AI Financial Model blog.