Messy Financial Export → Clean Transactions (combined)

View on GitHub

What

Take one realistically messy brokerage/ERP transaction export and clean six things at once in a single template: US date → ISO, transaction-code → label, accounting negatives → signed amount, currency-symbol price → number + currency, percent/bps fee → fraction, and null-variant notes → empty.

Synthetic / teaching example

The data here is constructed, not sourced — sample.csv is hand-written rows that pack one of every mess into each column. Each idiom is taught in isolation in the basic/intermediate tier; this is the capstone that combines them. The problem classes are real; the rows are not.

Why interesting

Real exports rarely have just one problem — a single CSV mixes US dates, parenthesised negatives, currency symbols, percent/bps rates, and a half-dozen null spellings, all in different columns. The point of this example is that bxp handles the whole thing declaratively in one pass, with no glue script orchestrating per-column cleanups.

The tricks

Six idioms in one template; each also has an example of its own:

1. DATE_CONVERT([TradeDate], 'MM/DD/YYYY', 'YYYY-MM-DD')
2. transaction code → label via REMAP over a named map (BUY → purchase)
3. accounting negatives → signed — see intermediate/accounting-negatives
4. price + currency split — see intermediate/price-currency-split
5. percent / bps → fraction — see intermediate/percent-to-fraction
6. null-variant notes → empty — see basic/null-variants

Final result

One ragged input row —

01/15/2024,BUY,AAPL,"(2,500.00)","$187.50",0.25%,

— comes out fully normalised, every field a clean typed value:

2024-01-15,purchase,AAPL,-2500,187.5,USD,0.0025,

Dates sort, amounts sum, the currency is its own column, the fee is a real fraction, and the empty note is genuinely empty — ready to load into a ledger or analytics tool with no further cleanup.

Sample data

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

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

{
  // Teaching example (advanced) — synthetic data. One messy brokerage/ERP
  // transaction export that needs SIX cleanups at once, each shown on its own
  // in the basic/intermediate tier, here combined into a single realistic
  // pipeline: US date → ISO, transaction-code → label (REMAP), accounting
  // negatives → signed, currency-symbol price → value + currency, percent/bps
  // fee → fraction, and null-variant notes → empty.
  maps: {
    // Transaction-code → human label.
    txn_type_label: {
      "BUY":  "purchase",
      "SELL": "sale",
      "DIV":  "dividend",
      "FEE":  "fee"
    }
  },
  conversion_templates: {
    messy_financial_export: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      csv_text_quote_in:  "double",
      csv_text_quote_out: "double",

      input_schema: {
        // 1 — US MM/DD/YYYY → ISO-8601.
        $date:     "DATE_CONVERT([TradeDate], 'MM/DD/YYYY', 'YYYY-MM-DD')",

        // 2 — transaction code → label via REMAP over a maps table.
        $type:     "REMAP([Type], 'txn_type_label')",

        $security: "[Security]",

        // 3 — accounting negatives + comma thousands → signed decimal.
        $amount:   "IF(STARTS_WITH(TRIM([Amount]), '('), 0 - (REPLACE(TRIM([Amount]), '(', '', ')', '', ',', '') * 1), REPLACE(TRIM([Amount]), ',', '') * 1)",

        // 4 — currency-symbol price → clean number + separate currency code.
        $price:    "IF(ISEMPTY([UnitPrice]), '', REPLACE(PRICE_VALUE([UnitPrice]), ',', '') * 1)",
        $currency: "PRICE_CURRENCY([UnitPrice])",

        // 5 — fee as percent ("0.25%") or basis points ("25 bps") → fraction.
        $fee_rate: "IF(CONTAINS([FeeRate], '%'), REPLACE([FeeRate], '%', '') / 100, IF(CONTAINS([FeeRate], 'bps'), SPLIT_PART([FeeRate], ' ', 1) / 10000, [FeeRate] * 1))",

        // 6 — null-variant notes → genuine empty. IN matches the marker
        // list exactly, so there is no emptiness test here to get wrong.
        $notes:    "IF(IN(TRIM([Notes]), 'N/A', 'NA', 'NULL', 'None', 'none', '-'), '', TRIM([Notes]))"
      },

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

      output_schema: {
        date:      "$date",
        type:      "$type",
        security:  "$security",
        amount:    "$amount",
        price:     "$price",
        currency:  "$currency",
        fee_rate:  "$fee_rate",
        notes:     "$notes"
      }
    }
  }
}

TradeDate,Type,Security,Amount,UnitPrice,FeeRate,Notes
01/15/2024,BUY,AAPL,"(2,500.00)","$187.50",0.25%,
01/16/2024,SELL,MSFT,"1,234.56","$415.20",25 bps,Partial fill
01/17/2024,DIV,VTI,42.00,"2.45 USD",0%,N/A
02/01/2024,BUY,SAP,"(5,000.00)","145.30 EUR",0.15%,-
02/02/2024,FEE,,"(9.99)","$9.99",0%,None

date,type,security,amount,price,currency,fee_rate,notes
2024-01-15,purchase,AAPL,-2500,187.5,USD,0.0025,
2024-01-16,sale,MSFT,1234.56,415.2,USD,0.0025,Partial fill
2024-01-17,dividend,VTI,42,2.45,USD,0,
2024-02-01,purchase,SAP,-5000,145.3,EUR,0.0015,
2024-02-02,fee,,-9.99,9.99,USD,0,