Chapter 12. Excel Integration

Microsoft Excel is probably the most successful data analytics platform of all times.

— Kirat Singh

It is fair to say that Microsoft Excel — as part of Microsoft’s Office suite of productivity

tools — is one of the most widely used tools and applications in the finance industry and

the finance functions of corporate and other institutions. What started out as a

computerized version of paper spreadsheets has become a multipurpose tool for financial

analysis and financial application building (in addition to the many use cases in other

fields and industries).

Spreadsheet applications, like Microsoft Excel and LibreOffice Calc, are characterized by

a few main features:

Organization

A workbook is a spreadsheet application file that is organized in single sheets that in

turn are organized in cells.

Data

Data is generally stored in tabular form in single cells; the cells contain the data itself

(e.g., a floating-point number or a text string), formatting information for display

purposes (e.g., font type, color), and maybe some computer code (if, for example, the

data in the cell is the result of a numerical operation).

Functionality

Given the data stored in single cells, you can do computational and other operations

with that data, like adding or multiplying integers.

Visualization

Data can be easily visualized, for example, as a pie chart.

Programmability

Modern spreadsheet applications allow highly flexible programmability, e.g., via

Visual Basic for Applications (VBA) within an Excel spreadsheet.

References

The major tool for implementing functionality or writing, e.g., VBA code is the cell

reference; every cell has unique coordinates (workbook, sheet name, column, and

row) identifying the cell.

This brief characterization might explain the popularity: all technical elements needed to

implement financial analyses or applications are found in a single place. Thinking of

Python and the previous chapters, you need a couple of libraries and tools (Python, NumPy,

matplotlib, PyTables, etc.) combined to have available all of the features just listed.

Such convenience and one-size-fits-all approaches generally come at a cost, though. To

pick just one area, spreadsheets are not suited to storing large amounts of data or data with

complex relationships. This is the reason why Microsoft Excel in the finance industry has