HESSIAN_MATRIX

Overview

Computes the Hessian matrix (second derivatives) of a scalar function with respect to its variables. This function enables business users to analyze curvature, perform advanced optimization, and understand the local behavior of functions directly in Excel. It leverages the CasADi package for symbolic and numeric differentiation, and is accessible via the Boardflare Python for Excel add-in.

The Hessian matrix $H$ of a scalar function $f(\mathbf{x})$ with respect to variables $\mathbf{x} = [x_1, x_2, \ldots, x_n]$ is defined as:

H_{ij} = \frac{\partial^2 f}{\partial x_i \partial x_j}

or, in matrix form:

H(f) = \begin{bmatrix} \frac{\partial^2 f}{\partial x_1^2} & \cdots & \frac{\partial^2 f}{\partial x_1 \partial x_n} \\ \vdots & \ddots & \vdots \\ \frac{\partial^2 f}{\partial x_n \partial x_1} & \cdots & \frac{\partial^2 f}{\partial x_n^2} \end{bmatrix}

This function parses the symbolic expression, constructs symbolic variables, and uses CasADi to compute the Hessian matrix symbolically. The resulting Hessian is then evaluated at the provided point (the values for each variable) and returned as a 2D list. This allows for efficient and accurate computation of second-order derivatives, which are essential for optimization, sensitivity analysis, and understanding the curvature of functions.

Usage

To use the HESSIAN_MATRIX function in Excel, enter it as a formula in a cell, specifying your function, variable values, and variable names:


=HESSIAN_MATRIX(expression, variables, [variable_names])

Arguments

Argument	Type	Required	Description	Example
expression	string	Yes	Symbolic expression for the scalar function.	`"x*2 + 3xy + y*2"`
variables	2D list float	Yes	Point at which to evaluate the Hessian (values for each variable).	`[[1.0, 2.0]]`
variable_names	2D list string	No	Names of the variables (if not inferred from the expression).	`[["x", "y"]]`

Returns

Type	Description	Example
list[list[float]]	The Hessian matrix evaluated at the given point.	`[[2.0, 3.0], [3.0, 2.0]]`
str	Error message if the calculation fails or input is invalid.	`"Invalid input: expression must be a valid scalar function."`

Examples

Risk Analysis in Finance

Business context: A financial analyst wants to compute the Hessian of a risk function to understand how portfolio risk changes with respect to asset allocations.

Excel usage:


=HESSIAN_MATRIX("x**2 + 3*x*y + y**2", {1.0, 2.0}, {"x", "y"})

Expected outcome: Returns the Hessian matrix at the point (1.0, 2.0):

2.0	3.0
3.0	2.0

Legend:

$H = \begin{bmatrix} \frac{\partial^2 f}{\partial x^2} & \frac{\partial^2 f}{\partial x \partial y} \\ \frac{\partial^2 f}{\partial y \partial x} & \frac{\partial^2 f}{\partial y^2} \end{bmatrix}$

Engineering Design Sensitivity

Business context: An engineer analyzes the curvature of a cost function to assess sensitivity to design variables.

Excel usage:


=HESSIAN_MATRIX("x**4 + y**4 + 2*x**2*y**2", {1.0, 1.0}, {"x", "y"})

Expected outcome: Returns the Hessian matrix at the point (1.0, 1.0):

12.0	8.0
8.0	12.0

Limitations

The function must be a valid scalar expression in Python syntax.
Only supports scalar or 2D list arguments of type float or string.
The Hessian is only defined for scalar-valued functions.

Benefits

Excel does not provide built-in tools for symbolic or numeric Hessian calculation. This Python function, powered by CasADi, enables advanced sensitivity and curvature analysis directly in Excel, which would otherwise require complex manual calculations or external tools.

Python Code


import casadi as ca
 
def hessian_matrix(expression, variables, variable_names=None):
    """
    Computes the HESSIAN_MATRIX matrix (second derivatives) of a scalar function with respect to its variables.
 
    Args:
        expression: A string representing the scalar function (e.g., "x**2 + 3*x*y + y**2").
        variables: 2D list of floats, point at which to evaluate the HESSIAN_MATRIX (e.g., [[1.0, 2.0]]).
        variable_names: 2D list of strings (optional), names of the variables (e.g., [["x", "y"]]).
 
    Returns:
        list[list[float]]: The HESSIAN_MATRIX matrix evaluated at the given point, or error message string.
    """
    try:
        if not isinstance(expression, str):
            return "expression must be a string."
        if not (isinstance(variables, list) and len(variables) > 0 and isinstance(variables[0], list)):
            return "variables must be a 2D list of floats."
        var_vals = variables[0]
        if variable_names is not None:
            if not (isinstance(variable_names, list) and len(variable_names) > 0 and isinstance(variable_names[0], list)):
                return "variable_names must be a 2D list of strings."
            var_names = variable_names[0]
        else:
            import re
            var_names = sorted(set(re.findall(r'\\b[a-zA-Z_]\\w*\\b', expression)))
        if len(var_names) != len(var_vals):
            return "Error: Number of variable names and values must match."
        sym_vars = [ca.MX.sym(name) for name in var_names]
        vars_dict = {name: sym_vars[i] for i, name in enumerate(var_names)}
        try:
            expr = eval(expression, {**vars_dict, 'ca': ca})
        except Exception as e:
            return f"Invalid expression: {str(e)}"
        H = ca.hessian(expr, ca.vertcat(*sym_vars))[0]
        hess_func = ca.Function('hess_func', sym_vars, [H])
        result_matrix = hess_func(*var_vals)
        if isinstance(result_matrix, ca.DM):
            return result_matrix.full().tolist()
        else:
            return "Error during CasADi calculation: Unexpected result type."
    except ca.CasadiException as e:
        return f"Error during CasADi calculation: {e}"
    except Exception as e:
        return str(e)

Live Notebook

Edit this function in a live notebook .