AnalyseExcelFormula​s

ANALYSEEXCELFORMULAS Extracts formulas from an Excel workbook and lists them in several different ..

ANALYSEEXCELFORMULAS Extracts formulas from an Excel workbook and lists them in several different ways to aid analysis.

AnalyseExcelFormulas collects all the formulas and values from all the worksheets in an Excel file. It then creates a new Excel file with 2N+1 sheets in it (where N is the number of worksheets in the orginal workbook). Each odd sheet, 2n-1, lists the cell name, value and formula for sheet n (from the original file) in row order and each sheet, 2n, lists the same information in column order. The last sheet (2N+1) contains all the information on all the other sheets in one large list for easy printing. In addition, a structure is returned by the function. The structure's fields have the same name as the sheet tabs in your workbook and contain one cell array of all the values in the worksheet and another with all the formulas (e.g. A.LIBOR_Sheet.Formula and A.LIBOR_Sheet.Value.

Note the example. The file name is "NonGenericSwap.xls" which has a worksheet (the 3rd sheet in the workbook) called "Generic swap cashflows" which has a cell (C12) which has the formula "=(B12-B11)/360" and a numerical value (result) of 0.252778.

AnalyseExcelFormulas creates a new spreadsheet that it titles "NonGenericSwap_formulas.xls." In the 5th worksheet (2*3-1), it puts the title "'Generic swap cashflows' by row" in A1 and then creates three columns below it. The first contains the cell addresses in the row order (e.g. A1, A2, A3, ... B1, B2, C2). The second contains the values of the cells as they appear when you open the workbook. The third column contains the formulas).

The 6th (2*3) worksheet's A1 would contain the title "'Generic swap cashflows" by col" and it's first column would list the addresses in column order (e.g. A1, B1, C1, ... A2, B2, C2).

The last sheet in the new workbbok, number 2N+1, would contain all the data from all the other sheets.

Empty cells are not listed.

The function would return a structure that would have a Genericswapcashflow.Formula field with a cell array of formulas and a Genericswapcashflow.Value field with a cell array of values.

>> A = AnalyseExcelFormulas('c:\NonGenericSwap.xls', ...
'c:\NonGenericSwap_formulas.xls')

A =
Key: [1x1 struct]
Discountfactorslinear: [1x1 struct]
Genericswapcashflows: [1x1 struct]
Forward1v5HS: [1x1 struct]
Forward1v5NPA: [1x1 struct]
Forward1v5IF: [1x1 struct]
Refrateapproach: [1x1 struct]
Amortising5yrHS: [1x1 struct]
Amortising5yrNPA: [1x1 struct]
Amortising5yrIF: [1x1 struct]
ComplexswapHS: [1x1 struct]
ComplexswapNPA: [1x1 struct]
ComplexswapIF: [1x1 struct]
Nondiscountingaswap: [1x1 struct]
NewMarketData: [1x1 struct]
ValuingComplexswapNPA: [1x1 struct]
ValuingComplexswapIF: [1x1 struct]

>> A.Genericswapcashflows

ans =
Formula: {17x4 cell}
Value: {17x4 cell}

It's not fancy, but it works.
Michael Robbins, CFA