About Site Map Submit Contact Us Log in | Create an account
Create an account Log In
Average Rating
User Rating:
Visitors Rating:
My rating:

Write review
  • License: Freeware
  • Last update: 5 years ago
  • Total downloads: 100
  • Price: Free |
  • Operating system: Linux, Mac OS X, Win All, BSD, Solaris
  • Publisher: Michael Robbins (24 other programs)
See full specifications

scripts default iconAnalyseExcelFormulas (Scripts) Publisher's description

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

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', ...

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

System Requirements:

MATLAB 7.0.4 (R14SP2)
Program Release Status: New Release
Program Install Support: Install and Uninstall

Is AnalyseExcelFormulas (Scripts) your software?

Manage your software

Most Popular

scripts default icon ASK, OOK, FSK, BPSK, QPSK, 8PSK modulation 1.1
ASK, OOK, FSK, BPSK, QPSK, 8PSK modulation contain several functions for digital modulation simulation
scripts default icon Simulink Communication Labs 1.1
Simulink Communication Labs allows you to learn communication systems in greater depth.
scripts default icon M-QAM modulation and demodulation 1.1
M-QAM modulation and demodulation is the QAM modulation and demodulation tech.
scripts default icon LZW Compression/Decompression 1.1
LZW Compression/Decompression - Updated LZW compressor and decompressor with reasonable performance
scripts default icon InSPIRE utility to plot a 2D displacement field (Scripts) 1.0
This program plots the deformation field (displace vectors) contained in vector.txt.