excel-to-swallow

Files (1)

• SKILL.md

SKILL.md (first lines)

# Excel to Swallow Converter

The user wants to convert an Excel rating workbook into a Swallow pricing engine configuration.

## Inputs

The user will provide:
- **Excel file path** — a `.xls` or `.xlsx` file containing pricing/rating logic
- **Result cell** (optional) — the cell reference containing the final price (e.g. `"Sheet1!C45"` or `"premium!H12"`)

If no result cell is supplied, **auto-detect it** (see Step 2). Only ask the user as a last resort.

## Process

### Step 1 — Read and understand the workbook

Read the Excel file. For each sheet, map out:
- All cells with formulas (these are calculations)
- All cells with static values (these are potential inputs or constants)
- All cells with comments/notes (check for `:::result`, `:::input`, or `:::valid` flags)
- Named ranges and their cell references
- Cross-sheet references

### Step 2 — Identify the result cell

Find the result cell using this priority:

1. **Supplied parameter** — if the user provided a cell reference, use it
2. **`:::result` flag** — scan all cell comments for `:::result`
3. **Auto-detect** — this is the primary method. Find the result cell by:
   a. Build a reference map: for every cell with a formula, record which other cells it references
   b. Invert the map to find which cells are referenced BY other cells
   c. **Terminal formula cells** are cells that have a formula but are NOT referenced by any other formula cell — they are the "end of the chain"
   d. Among terminal cells, rank candidates by:
      - Deepest dependency chain (most steps to reach from inputs) — the final price typically depends on many intermediate calculations
      - Numeric type (the result is almost always a number, not text or boolean)
      - Sheet name heuristics: cells on sheets named "summary", "output", "premium", "result", "quote" rank higher
      - Cell label/header heuristics: cells near labels containing "total", "premium", "price", "result", "final", "gross", "net" rank higher
   e. If there is a single clear winner, use it. If there are 2-3 plausible candidates, present them to the user with their current values and formula descriptions and ask which is the result
4. **Ask the user** — only as last resort if auto-detect produces too many candidates or none

Also look for:
- `:::valid` in comments — the validity/exclusion check cell
- `:::input` in comments — explicitly marked input cells
- Auto-detect validity: terminal boolean formula cells that reference exclusion-like logic

### Step 3 — Trace the dependency tree backwards from result

Starting from the result cell, recursively trace every cell reference in its formula:
- For each referenced cell, check if it has a formula (calculation) or a static value (input/constant)
- For cells with formulas, recurse into their references
- Continue until you reach leaf cells (no formula, no further references)

Build a dependency map:
```
result_cell
  ├── calc_cell_1 (formula: =A1 * B1)
  │   ├── input_cell_A1 (static value: 500)
  │   └── input_cell_B1 (static value: 1.2)
  ├── calc_cell_2 (formula: =VLOOKUP(...))
  │   ├── lookup_table (range of static data)
  │   └── input_cell_C3 (static value: "SW1")
  └── ...
```

### Step 4 — Classify each cell

For every cell in the dependency tree, classify it as:

| Classification | Criteria | Swallow mapping |
|---|---|---|
| **Input** | Leaf cell, no formula, value would change per quote | `input.format` field |
| **Constant** | Leaf cell, no formula, value is fixed (rates, thresholds) | `input.format` field with `static: true` |
| **Lookup table** | Range of static data used by VLOOKUP/INDEX/MATCH | `collection` step |
| **Calculation** | Cell with arithmetic formula | `calculation` step or `transform` step |
| **Conditional** | Cell with IF/AND/OR that determines validity | `exclusion` or `refer` step |
| **Result** | The final price cell | `output.result` |
| **Valid** | The validity check cell | `output.valid` |

When classifying, consider:
- Cells that look like customer data (age, postcode, name, vehicle value) → **Input**
- Cells that look like fixed rates or thresholds (tax rate, commission %, loading factors) → **Constant** (static input)
- Ranges used in VLOOKUP/INDEX/MATCH → **Lookup table** (collection)
- Boolean cells that gate whether a quote is valid → **Conditional** (exclusion)

### Step 5 — Order into Swallow steps

Arrange the cells into an ordered pipeline. Use the dependency depth:
1. **Inputs** (depth 0) — all leaf cells
2. **Collections** (depth 1) — lookup tables
3. **Transforms** (depth 2+) — intermediate calculations, grouped by sheet or logical concern
4. **Exclusions/Refers** — conditional rules
5. **Final calculations** — premium, commission, tax
6. **Output** — result formula and valid expression

### Step 6 — Convert formulas to Swallow expressions

Translate Excel formulas to Swallow syntax:

| Excel | Swallow |
|---|---|
| `=A1 * B1` | `({{input_a}} * {{input_b}})` |
| `=ROUND(A1, 2)` | `round({{input_a}}, 2)` |
| `=IF(A1>50, 1.5, 1.0)` | `({{age}} > 50 ? 1.5 : 1.0)` |
| `=VLOOKUP(A1, Sheet2!A:C, 3, TRUE)` | Collection step: `collection.items.filter(key <= {{lookup_val}}).map(value).last()` |
| `=SUM(A1:A10)` | `_.sum([{{a1}}, {{a2}}, ...])` or collection `collection.items.map(value).sum()` |
| `=INDEX(MATCH(...))` | Collection step with filter/map |
| `=CONCATENATE(A1, B1)` | String template or code step |
| `=AND(A1>18, A1<80)` | Exclusion: `({{age}} < 18 \|\| {{age}} > 80)` (inverted — exclusion triggers on FAIL) |

Key rules:
- Replace all cell references with `{{swallow_property_name}}`
- Use `snake_case` for all property names (derived from cell labels, sheet names, or column headers)
- `id` and `key` must be the same within each step
- Every property needs a `def` (default value) — use the cell's current value. Multiplicative factor defaults must be `1.0` not `0`
- `result` uses `formula`, `valid` uses `exp`
- In calculation `formula` fields: use `and`/`or` not `&&`/`||` (MathJS syntax)
- `min()`/`max()` not supported in transform `exp` — use calculation steps
- Never use `key` as a data column name in collections

From llow-group/swallow-claude-plugin, MIT.

More from llow-group/swallow-claude-plugin