Functions

This section includes an introduction to some of the Excel macro functions developed by Finastra to create, manipulate and view JSON files and to invoke scripts and workflows. The functions are implemented as Excel formulas. Note that the formulas arguments separator may vary according to your operating system’s regional settings.

Functions to Create JSON Structures

JsonObject(keys, values, optional name)

  • Creates a JSON object with field names of 1D range keys associated to values of 1D range values
  • If name is provided, it is used in the returned reference.
  • If a cell of keys or values is blank, then it is skipped.
  • It returns a reference representing the JSON object that can be visualized by clicking on the Open JSON in Editor button of the Valuation ribbon tab.

JsonArray(values, optional name)

  • Creates a JSON array with values of 1D range values.
  • If name is provided it is used in the returned reference.
  • If a cell of keys or values is blank, then it is skipped.
  • It returns a reference representing the JSON array that can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.

Functions to Create Pricing Data

FppDataPoint(values, [x], [y], [z])

  • Creates a scenario values structure using the provided optional X/Y/Z coordinate.
  • X/Y/Z axis are optional so this function can be used to create any 0D/1D/2D/3D scenario values.
  • Values is a range specify the multiple scenario values for that point.
  • It can be used along with JsonArray() and JsonObject() macros to create any pricing data.

FppExecuteScriptRequest(scriptName, jsonRef, libraryName)

  • Calls a script named scriptName of library libraryName providing the jsonRef reference.
  • jsonRef is a JSON object reference containing the information to pass to the script.
  • Results can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.

FppExecuteWorkflowRequest(workflowName, jsonRef, libraryName)

  • Invokes the workflow named workflowName of library libraryName providing the jsonRef reference
  • jsonRef is a JSON object reference containing the information to pass to the workflow.
  • Results can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.

Functions to Visualize Script Outputs

FppGetResultValues(invokeRef, optional columnNames, optional forceDocIdOutput, optional forceScenarioIdOutput)

  • Array formula with script output (scalar, array, matrix, and cube).
  • If provided, column names are used to convert cell to date. Column with name containing date are automatically converted to Excel date.
  • By default, document id is output in the first column only if the result contains more than one document, unless forceDocIdOutput is True.
  • By default, scenario id is output after document id only if the result contains more than one scenario, unless forceScenarioIdOutput is True.

Functions to Visualize Workflow Outputs

FppGetResultWorkflow(key, box, endpoint)

For the specified workflow request key, box and endpoint, get the associated node message and:

  • Either returns a JSON reference if it is a valid JSON that can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.
  • Or returns the string representation.

Functions to Vsualize Errors and Traces

The following functions work for both scripts and workflows.

FppGetResultErrors(invokeRef)

  • Two columns array formula containing the list of errors.
  • The first column contains a json reference to the full error message when its truncated.
  • It can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.

FppGetResultTraces(invokeRef)

  • Four columns array formula containing the list of traces.
  • The third column contains a JSON reference to the full trace message when its truncated.
  • It can be viewed while clicking on the Open JSON in Editor button of the Valuation ribbon tab.