Python for Finance: Analyze Big Financial Data

Scripting Excel with Python

The previous section shows how to generate, read, and manipulate Excel spreadsheet files

(i.e., workbooks). Although there are some beneficial use cases, Python is not the only

way, and sometimes also not the best way, to achieve the results presented there.

Much more interesting is to expose the analytical power of Python to Excel spreadsheets.

However, this is a technically more demanding task. For example, the Python library

PyXLL provides means to expose Python functions via so-called Excel add-ins,

Microsoft’s technology to enhance the functionality of Excel. Additionally, the company

DataNitro provides a solution that allows the full integration of Python and Excel and

makes Python a full substitute for VBA programming. Both solutions, however, are

commercial products that need to be licensed.

In what follows, we provide an overview of how to use DataNitro for Excel scripting,

since this is a rather flexible approach to integrating Python with Excel.

Installing DataNitro

DataNitro works on Windows operating systems and Excel installations only. On Mac OS

systems it can be used in a Windows virtual machine environment. It is compatible with

Office 2007 and higher. Refer to the website http://www.datanitro.com for further

instructions on how to get a (trial) license for the solution and how to install it.

When installing DataNitro you have the option to install Python as well. However, if you

have already installed Anaconda (cf. Chapter 2), for example, there is no need to install

another Python version or distribution. You then just have to customize the DataNitro

solution (via the Settings menu) to use the existing Anaconda installation. DataNitro

works with all Python versions 2.6 and higher as well as with versions 3.x.

If successfully installed, you then find the DataNitro ribbon within Excel, as displayed in

Figure 12-4.

Figure 12-4. Screenshot of Excel with DataNitro ribbon

Working with DataNitro

There are two main methods to combine DataNitro with Excel:

Scripting