nlitn

Turning data into actionable insights


Introduction

Do any of the following statements sound familiar to you?

  • Your Excel spreadsheets consist of a large number of tabs, are stuffed with numbers and calculations, and lack structure or overview.
  • Generally these sheets take a while to open and are slow with calculations.
  • Several Excel spreadsheets that perform important calculations were built by staff members that long ago left the company. Over time, new modules were added to the spreadsheet without regard of structure, efficiency, or error checking capabilities.
  • The calculations in your Excel spreadsheets depend partially on poorly documented addins like for example QuantLibXL and therefore you can never be sure that the performed calculations by the addin are correct because you are not able to check them.

The above is often the result of time and budget constraints. If any of the above statements sounded familiar to you, continue reading and find the solution to these problems.

Advantages of Excel

  • A basic knowledge level of Excel can be easily obtained due to its accessibility and easy-to-use nature.
  • The input of data in a spreadsheet can be easy and convenient. After the input of data, calculations can immediately be seen (within Excel) without the necessity to run other software programs.

Disadvantages of Excel

  • The accessibility and easy-to-use nature of Excel is also a disadvantage. Powell et al. (2009) states that given the importance of spreadsheets, one might assume that they are created by professional programmers. However, in many organizations this is not the case. In fact, it is a reasonable conjecture that most spreadsheets are built by business experts, not computer experts. Professional programmers understand the difficulties of creating error-free code and are trained to avoid errors. Most spreadsheet developers, being largely self-taught, are less aware of the dangers that errors pose.
  • Excel cannot handle the amounts of data that a program written in a coding language can.
  • Excel lacks the computing power that you will find in high-level programming languages.
  • Numerical problems render Excel useless for certain types of projects that involve heavy calculation, e.g. inversion of (semi) large matrices or statistical analysis with regression models. We refer to Almiron (2010), and the references therein, for a discussion on numerical errors and imprecisions in Excel.

Excel's limitations

These days, many financial organizations reach the boundaries of Excel's capabilities, due to large amounts of data that need to be processed and the increasingly complex modelling calculations that are often required for financial regulation. Complex and time consuming processes like Monte Carlo simulations, sensitivity analysis, or Economic Value of Equity calculations suffer under the limitations of Excel. Given a data flow chart like the one below, or alternative versions of it, we often see that Excel is the bottleneck in the whole process.

dataflow

Solution

Nlitn recommends building a coding environment in which the role of Excel spreadsheets in the organization is reduced by converting Excel sheets into programs written in higher-level coding languages like Matlab, Python, or R. Nlitn can recommend the most suitable coding language for your organization or can assist you with a pilot to investigate which coding language suits the organization best. The goal of doing a pilot is to seek answers to the questions posed in the table below. Note that the requirements in the table are examples and the table can be extended with much more requirements. We used Matlab, Python, and R as an example here but other coding languages can be considered as well. Once a score is determined for each category and for each language, weights can be assigned to the categories to determine the overall best suited coding language. We already filled in the scores for Excel in the table below and would like to hear if you agree to our Excel ratings. Based on the pilot, the optimal choice for your organization can be made.

Comparison of Excel with programming languages

Requirement Excel Matlab Python R
Cost of software +
Execution speed of code -- --
Numerical precision -- --
Easy to interpret code ++
Alignment with (future) industry practice + --
Adoption new developments (e.g. Machine Learning) -- --
Avoid dependency 1 supplier + --
Avoid black box algorithms --
Modules available +
Support (including all online resources) ++
Graphical capabilities --
General purpose language (suitable broad range tasks) -- --
Complete install (no IDE or other software needed) Y
Open source N

Bibliography

Almiron, M. G. (2010): On the numerical accuracy of spreadsheets, Journal of Statistical Software, 34(4), 1–29.

Powell, S. G., Baker, K. R., and Lawson, B. (2009): Impact of errors in operational spread-sheets, Decision Support Systems, 47(2), 126–132.