Percent / Basis Points → Decimal Fraction¶

What

Normalise a Rate column that mixes percent (2.5%), basis points (25 bps) and the odd already-decimal legacy value (0.03) into one consistent decimal fraction.

Synthetic / teaching example

The data here is constructed, not sourced — sample.csv is hand-written rows covering each rate notation. The problem class is real; the rows are not.

Why interesting¶

Finance writes the same rate two ways — 2.5% and 25 bps are identical — and stores them as text with their unit glued on. Any arithmetic (rate * principal) throws on the % / bps suffix, and mixing percent and basis points in one column means a single divide-by-100 is wrong for half the rows. You need to detect the notation per cell before converting.

flowchart TD
    R["**Rate** cell"]
    R -->|"contains %"| P["strip %, ÷ 100<br/><small>2.5% → 0.025</small>"]
    R -->|"contains bps"| B["leading number, ÷ 10000<br/><small>25 bps → 0.0025</small>"]
    R -->|"otherwise"| D["already a fraction, × 1<br/><small>0.03 → 0.03</small>"]

Problem class documented in. (sources for the problem class — not for the data)

Basis point — 1 bp = 0.01% = 0.0001; rate tables routinely mix % and bps.

The trick¶

(see inline comments in sample.json):

IF(CONTAINS([Rate], '%'),   REPLACE([Rate], '%', '') / 100,
IF(CONTAINS([Rate], 'bps'), SPLIT_PART([Rate], ' ', 1) / 10000,
                            [Rate] * 1))

% form → strip the sign, divide by 100.
bps form → take the leading number, divide by 10000 (1 bp = 0.0001).
anything else → already a fraction, * 1 to normalise.

Final result¶

Two notations for the same thing land on the same fraction, and the legacy decimal passes straight through:

2.5%     →  0.025
25 bps   →  0.0025
150 bps  →  0.015
0.03     →  0.03

Every rate is now a plain fraction ready to multiply against a balance — no unit parsing in the consuming code.

Sample data¶

Run it with bxp-cli --config ./sample.json --template percent_to_fraction:

sample.json (config)sample.csvsample.csvx (result)

{
  // Teaching example — synthetic data. A rate sheet where the same quantity is
  // written two ways finance loves: percent ("2.5%") and basis points
  // ("25 bps"), plus the occasional already-decimal legacy value. To compute
  // with them you need one consistent fraction (0.025). The template detects
  // the notation and normalises each to a decimal fraction.
  conversion_templates: {
    percent_to_fraction: {
      data_dir:          ".",
      file_pattern_in:   ".csv",
      file_pattern_out:  ".csvx",

      input_schema: {
        $product: "[Product]",

        // THE TRICK — dispatch on the notation:
        //   "2.5%"   → strip '%', divide by 100        → 0.025
        //   "25 bps" → take the number, divide by 10000 → 0.0025
        //   "0.03"   → already a fraction, pass through (* 1 to normalise)
        // (1 basis point = 0.01% = 0.0001, so bps ÷ 10000.)
        $rate: "IF(CONTAINS([Rate], '%'), REPLACE([Rate], '%', '') / 100, IF(CONTAINS([Rate], 'bps'), SPLIT_PART([Rate], ' ', 1) / 10000, [Rate] * 1))"
      },

      row_rules: [ { when: "1 = 1", rows: [ {} ] } ],

      output_schema: {
        product:       "$product",
        rate_fraction: "$rate"
      }
    }
  }
}

Product,Rate
High-yield savings,2.5%
Premium account,0.25%
Corporate bond A,25 bps
Corporate bond B,150 bps
Legacy fixed,0.03

product,rate_fraction
High-yield savings,0.025
Premium account,0.0025
Corporate bond A,0.0025
Corporate bond B,0.015
Legacy fixed,0.03