Skip to content

Creating Formulas

This guide walks through creating a formula using the graph-based builder.

Example: Market Value Calculator

Calculate market value as par_value * price / 100.

Step 1: Define Nodes

Create three nodes:

{
  "nodes": [
    {"id": "n1", "type": "column_ref", "params": {"column": "par_value"}},
    {"id": "n2", "type": "column_ref", "params": {"column": "price"}},
    {"id": "n3", "type": "expression", "params": {"formula": "n1 * n2 / 100"}}
  ]
}

Step 2: Connect Edges

Define data flow from inputs to the expression:

{
  "edges": [
    {"id": "e1", "source": {"nodeId": "n1", "port": "out"}, "target": {"nodeId": "n3", "port": "in1"}},
    {"id": "e2", "source": {"nodeId": "n2", "port": "out"}, "target": {"nodeId": "n3", "port": "in2"}}
  ]
}

Step 3: Validate

POST /api/v1/formulas/validate
{
  "graph": { "version": "1.0", "template_id": "custom", "nodes": [...], "edges": [...] }
}

Response: 

{
  "is_valid": true,
  "errors": [],
  "warnings": [],
  "dependencies": ["par_value", "price"]
}

Step 4: Preview

Test against real data:

POST /api/v1/formulas/preview
{
  "graph": { ... },
  "workbook_id": "550e8400-e29b-41d4-a716-446655440000",
  "sheet_id": "sheet_abc123",
  "sample_size": 5
}

Step 5: Save and Apply

# Create formula config
POST /api/v1/formulas
{
  "name": "Market Value Calculator",
  "template_id": "custom",
  "graph": { ... },
  "status": "active"
}

# Apply to workbook
POST /api/v1/formulas/{config_id}/apply
{
  "version_id": "v1",
  "workbook_id": "550e8400-e29b-41d4-a716-446655440000",
  "sheet_id": "sheet_abc123",
  "target_column": "market_value",
  "output_mode": "new"
}

Conditional Formulas

IF/THEN/ELSE Example

Flag loans with spread above 500bps:

{
  "nodes": [
    {"id": "n1", "type": "column_ref", "params": {"column": "spread"}},
    {"id": "n2", "type": "constant", "params": {"value": 500}},
    {"id": "n3", "type": "compare", "params": {"operator": "gt"}},
    {"id": "n4", "type": "constant", "params": {"value": "High Spread"}},
    {"id": "n5", "type": "constant", "params": {"value": "Normal"}},
    {"id": "n6", "type": "if"}
  ],
  "edges": [
    {"id": "e1", "source": {"nodeId": "n1"}, "target": {"nodeId": "n3", "port": "left"}},
    {"id": "e2", "source": {"nodeId": "n2"}, "target": {"nodeId": "n3", "port": "right"}},
    {"id": "e3", "source": {"nodeId": "n3"}, "target": {"nodeId": "n6", "port": "condition"}},
    {"id": "e4", "source": {"nodeId": "n4"}, "target": {"nodeId": "n6", "port": "true"}},
    {"id": "e5", "source": {"nodeId": "n5"}, "target": {"nodeId": "n6", "port": "false"}}
  ]
}

Compiled formula: IF([spread] > 500, "High Spread", "Normal")

Lookup Formulas

Exact Lookup Example

Look up rating from a reference table:

{
  "nodes": [
    {"id": "n1", "type": "column_ref", "params": {"column": "cusip"}},
    {"id": "n2", "type": "lookup_exact", "params": {"default": "NR"}}
  ]
}

Output Modes

Mode Description
new Create a new column (error if exists)
overwrite Replace existing column values