ToDo – Formula Parsing

We could build models much more quickly if we could directly parse formulae.

A good start on fomulae parsing in VBA has been made by Rob Vangelder with his AudXL add-in for breaking formulae into their component parts (but see below for an updated version).

On 12 Jan 2010, Rob wrote: “You can use AudXL however you wish. I’m ok with the licensing, and I do appreciate the thought with regard to acknowledgement.  AudXL was written about 5 years ago. About a year ago I re-wrote the engine. You’ll probably want to use that instead.” Rob’s new add-in has been updated here.

The project http://poi.apache.org/spreadsheet/formula.html includes Java code for reading and writing Excel spreadsheets. This includes setting a formula in a cell, which requires parsing the formula and storing it using easy-to-evaluate token. The format for these spreadasheets is documented by OpenOffice at http://sc.openoffice.org/excelfileformat.pdf

Other resources (from http://stackoverflow.com/questions/2049355/c-evaluate-excel-logical-formulas) include:

• Excel Formula Parsing, in C#
• Invesitgation into Excel Syntax and a Formula Grammar
• Runtime code generation to speed up spreadsheet computations
• VectorExcel
• Excel Formula Formatter
• Implementing an Excel-like formula engine
• A .Net Excel formula parser and evaluator
• Excel formula to C# code
• Excel Formulae Parsing, including C# for .Net 2, Python, PhP, C++ and Delphi.
• Compiling Python to Excel, and then using this within a non-linear optimizer