Usage of Excel Constructor Functions

Visit the Mathema Option Pricing System for foreign exchange options and structured product valuation!

MCP Excel functions are designed with an object-oriented approach. If you need to use yield curves or related functions, you must first construct a yield curve object (McpYieldCurve) using Excel functions. Then, you can use specific methods, such as YieldCurveGetZeroRate(...), to retrieve the zero-coupon rate for a specific date from the curve.

Since there are multiple ways to construct yield curves, the constructor supports overloading, meaning it can handle different combinations of parameters. The usage of these parameter combinations is as follows:

---

Constructor Form

=FunctionName(args1, args2, args3, args4, args5, fmt='VP')

The constructor allows users to pass parameters through multiple arrays (args1 to args5) and specify the data format for each array using the fmt parameter. Below is a detailed explanation of the parameters and fmt:

---

1. Parameter Explanation

args1, args2, args3, args4, args5

• These are five arrays used to store the parameters and their corresponding values required to construct the object.
• In the function implementation, these five arrays are merged into a single list for use.
• Flexibility:
  • Users can place parameters in any of the arrays, and unused arrays can be left empty.
  • For example, if only three parameters are needed, they can be placed in args1, args2, and args3, while args4 and args5 are left empty.

fmt

• The fmt parameter is used to specify the data format for each array (args1 to args5).
• fmt supports splitting by |, with each segment corresponding to args1 to args5.
• Each segment supports the following formats:
  1. MT: Represents matrix parameter data.
  2. Combination Formats:
     • First character:
       • V: Indicates one attribute/data per row (vertical arrangement).
       • H: Indicates one attribute/data per column (horizontal arrangement).
     • Second character:
       • P: Represents parameters.
       • D: Represents data.
       • F and V: Represent fields and values, respectively.

---

2. Examples of fmt Parameter

Below are some examples of the fmt parameter and their meanings:

Example 1

VH|MT|VP|HD|VF

• Explanation:
  • args1: VH (vertical arrangement, mixed parameters and data).
  • args2: MT (matrix parameter data).
  • args3: VP (vertical arrangement, parameters).
  • args4: HD (horizontal arrangement, data).
  • args5: VF (vertical arrangement, fields and values).

Example 2

VP|HD|MT||

• Explanation:
  • args1: VP (vertical arrangement, parameters).
  • args2: HD (horizontal arrangement, data).
  • args3: MT (matrix parameter data).
  • args4 and args5: Unused (left empty).

Example 3

MT|VP|HD|VF|

• Explanation:
  • args1: MT (matrix parameter data).
  • args2: VP (vertical arrangement, parameters).
  • args3: HD (horizontal arrangement, data).
  • args4: VF (vertical arrangement, fields and values).
  • args5: Unused (left empty).

---

3. Usage Scenarios

Scenario 1: Constructing an Object

Suppose you need to construct an object with the following parameters:

• Name: Vertically arranged parameters.
• Price: Horizontally arranged data.
• Matrix Data: Matrix parameter data.

The fmt can be set as:

VP|HD|MT||

• args1: VP (vertical arrangement, parameters), stores the name.
• args2: HD (horizontal arrangement, data), stores the price.
• args3: MT (matrix parameter data), stores the matrix data.
• args4 and args5: Left empty.

Scenario 2: Fields and Values

Suppose you need to pass fields and values:

• Fields: Vertically arranged.
• Values: Vertically arranged.

The fmt can be set as:

VF||||

• args1: VF (vertical arrangement, fields and values).
• args2 to args5: Left empty.

---

4. Implementation Logic

In the function implementation, the parameters can be processed in the following steps:

1. Parse the fmt Parameter:
   • Split fmt by | to determine the format for each args.
2. Merge args1 to args5:
   • Combine the five arrays into a single list.
3. Parse Data Based on fmt:
   • For each args, parse the data according to its format.
   • For example, VP indicates vertically arranged parameters, and HD indicates horizontally arranged data.
4. Construct the Object:
   • Use the parsed data to construct the object.

---

5. Summary

• args1 to args5: Five arrays used to store parameters and data. Unused arrays can be left empty.
• fmt: Specifies the format for each array, supporting combinations like MT, VP, HD, and VF.
• Flexibility: Users can place parameters in any array and specify the format using fmt.