Package Complex Algorithms In Excel

Are you aware that you’re a software developer!

Are you:

• an investment analyst who uses Microsoft Excel^® to model a company’s fundamentals,
• a  middle office professional who has built workbooks to manage the benchmarks associated with a fund’s mandate,
• working in the back office, reconciling trades.

Whatever the case, if you build spreadsheets that you consistently use in your day-to-day operations, you have written code!

Excel syntax

A major distinguishing feature of languages in the same programming paradigm is the syntax. And the genius of the modern-day spreadsheet is that it has provided a very intuitive syntax for a functional programming language. Every cell in a spreadsheet is actually a variable. Placing a value or formula in a cell is equivalent to assigning a value or formula to a variable.

As you enter formulas in a worksheet, Excel dynamically builds the calculation dependency tree in the background. You can get an idea of what this tree looks like when you view the cell dependencies, i.e. the precedent and dependent cells. If you could view the formulas in the dependency tree order, you would in effect see the 'code' you have written.

The spreadsheet in the diagram shows a simple calculation .. adding together the values in two cells. If one needed to write this logic in another syntax, it could look something like this:

Developer shouting

I can imagine a whole lot of C++ and C# developers shouting that what I’m talking about is not really programming. Every language has a loop construct! Where is the loop construct in Excel? In fact, this is programming. As mentioned, this type of language falls into the functional programming paradigm.

In functional languages, looping is usually invoked using recursion. As it happens, you can use recursion in Excel. You need to ensure that you allow for circular references (e.g. Cell A2 depends on B2 which then depends on A2), but it can be done. I know of many investment professionals who use this Excel feature to implement the Newton-Raphson method in finding the roots of an equation.

So what’s my point?

I truly believe (and evidence supports) that Excel is one of the most powerful and useful software tools in the modern business world. That said, the tool can get pushed beyond its limits. I have mentioned in other articles that Excel is not necessarily the best place to store your data.

For small algorithms, Excel more than does the job. But when the formulas start to span 20 sheets, and the spreadsheet takes 15 minutes to recalculate, you have probably crossed the threshold of good practice. There is also implicit risk associated with these spreadsheets. It often happens that when a person who created a spreadsheet leaves a company, nobody really knows how to maintain and enhance that work. Furthermore, deploying this algorithm to multiple areas in the research and investment process can be difficult and tedious. So how can an organisation really own the intellectual property encapsulated in spreadsheets, that is developed and maintained by various role players? How can the IP 'scale' - i.e. be rolled out to multiple users? How can the company enhance the work without building a workbook that is slow and difficult to maintain?

Package and import the algorithms created in workbooks directly into the Quintessence platform

The import process translates the algorithms in spreadsheet syntax to a standard functional programming syntax. This code can then be wrapped up into a function that any Quintessence user can call. The intellectual property embedded in the workbook is warehoused and owned by the company, as opposed to an individual in the company. The algorithm is available to all who need to use it and is easily maintainable.