Business Functions Library for Excel

      1. Getting Started
      2. Using The Help File
      3. Top Dozen Functions
      4. Golden Rules
      5. Excel 2007
      1. Go To
      2. Function Selector
      3. CalculateFull
      4. Calculate WorkSheet
      5. Trace Facility
      6. Quick Paste Example
      7. Tutorials
      8. Function Finder
      9. Examples
      10. Help
      11. Excel"s Function Wizard
      12. Access Internet
      13. Usage of Functions (Audit)
      14. Uninstall
      1. Time Periods
      2. Inclusive and Exclusive Dates
      3. Using Daycount
      4. Examples of DayCount
      5. Annual Date Sequences
      6. ProjMode and Inclusive Dates
      7. Date Rolling Convention
      1. Rate Projections Functions Walkthrough
      2. Accruals and Cash
      3. Repeating Formulae
      4. Range Names and References
      5. Optional Parameters
      6. Using PmtsPerYear
      7. Modelling Seasonality
      8. Calculating Interest
      9. Using Business Functions in VBA (Visual Basic for Applications)
      10. Array Functions
      11. Volatility
      12. Annual Equivalent Rates
      13. Array Function
      14. Auto Multi Functions
      15. Variable Plurality
      16. GoalSeek
      1. Introduction To the Worked Examples
      2. Daycount
      3. General Projections
      4. Business Plans
      5. Cashbasis And Periods
      6. Using Timebases
      7. Using Dates
      1. How To Generate a time scale for a structured financing
      2. How To Project Rents off a Rental Forecast
      3. How To Run a model on different time bases
      4. How To Isolate The Cause of a Errors in Cells using Trace
      1. Introduction to the Utilities
      2. Audit
      3. Synchronized Range Insert/Delete
      4. Database Edit
      5. Insert Macro Button
      6. Link Analyser
      7. Range Describer
      8. PrintScript (beta)
      9. Create Local Range Name
      1. Number Formats
      2. Apply BF"s Color Palette
      3. Bullets
      4. Color Cells
      5. Conditional Formats
      1. Validation DropDowms
      2. Validation Standards
      1. Select Excel Function
      2. Array Function Tools
      3. Sort Sheets
      4. Range Value
      5. Named Range Manager
      6. Enforce WorkBook Settings
      7. Monte-Carlo
      8. TimeChart
      1. The ".ini" file
      1. Forum
      2. Online Help
      3. Templates
      1. Conversion of Input Strings to Values
      2. List of Holidays
      3. Acknowledgements and Trademarks
      4. Published Editions Changes in Behaviour
      5. Range Handling And Constraints
      6. Dates in Excel and Business Functions
      7. Old Composite DayCount Format
      8. DayCount in Excels"s Functions
      1. NPV of Annual To Periodic CashFlows - CorrectionM
      2. Interest - Simple, Annual, Continous and Discount Factors
      1. New Functions
      2. Obsolete Functions
      3. Discontinued Functions
      4. Deprecated Functions
    Trace Facility
    BF"s error tracking and investigation mechanism
    Business Functions has a Trace facility that enables you to walk through the calculations of all the BF function calls in a cell. It contains a lot of information, and although some of it is hard to interpret without access to the source code, it has improved substantially from early versions and tries to explain every calculation and function call. We, the developers, like to think that Trace is our primary debugging tool.

    The information from Trace is provided in a tree format, with each new branch of the tree a function call or significant peice of calculation logic, such as analysis of inputs or passes through a calculation loop.

    Here is some information about Trace:
    • The Trace facility is available from the Business Functions menu. Just highlight the cell containing the Business Function you want to investigate and go "Trace" from the BF menu.
    • You will then see a window with a number of messages from the function(s), displayed in a tree format. In effect, the function is in "chat" mode, telling you what calculations it is doing, what other functions it is calling, and so on. For complex functions, some of this chat may not be obvious, but you will be clearly made aware of errors in your input data, since this is the first check the function does.
    • Click on the little "+" signs to expand the next level of branches (function calls).
    • If you have made an input error (nonsensical input, out of range) the Trace facility will report this and the function will return a #NUM! Error.
    • So if you see a Business Function with a #NUM! Error, apply Trace - you will soon find the problem because the very final message in Trace will be where the problem arose, and the message will describe what happened.
    • Sometimes you just think the function is returning an odd number and don"t quite believe the answer, Again, Trace provides the answer, although sometimes you will have to look through 20-30 messages to understand the calculation. If you"re still flummoxed, load up the example file for the function in question an examine the behaviour.
    • When you run a Trace, a copy is written to a text file for perusal later, called bf_Trace.bfl, and that file is located in your current Excel files directory. It can be opened with any text editor, such as notepad.
    • With an array function, the output from all calls in every cell in the array is returned, typically you will get a main function call and ancillary information for each timeperiod in the range (if its array-like with respect to timeperiods).
    • Notice that, to help you interpret the Trace information, there are a number of color-coded icons and an explanation of what each one means.
    • Take notice too, of the Detail box at the bottom. Some of the messages are too big to fot in the tree itself, and you can see these written in full in the Detail box when you click on the relevant item in the message tree.
    • TIP: The quick way to bring up Trace (in either Excel 2007 or earlier versions) is to go Alt-B and then T .


    As of version 1.48 (Jan 2008), we have finally made Trace really useful, so do use it and let us know where it could be improved!

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure