US Treasury Yield Curve (wide → long) + tenor mapping¶

What

Melt the US Treasury's daily par-yield-curve CSV from its native wide layout (one column per maturity — 1 Mo, 2 Yr, … 30 Yr) into long/tidy rows (one row per date-tenor), converting the US date to ISO and mapping each maturity label to its length in months — all in one template.

Why interesting¶

The Treasury par yield curve is one of the most-watched series in finance (it's where "the 2s/10s inversion" is read off), and the official CSV ships in exactly the shape analysis tools least want: each maturity is its own column, so before you can sort the curve, plot it, or compute a spread you must (1) melt the 13 maturity columns to long form, and (2) turn the text maturity labels into a numeric tenor — because "10 Yr" sorts before "2 Yr" alphabetically, scrambling the curve. Both steps are normally a pandas.melt + a hand-written label→months dict; bxp does them declaratively in the template, with one daily row fanning out to 13 tidy rows.

flowchart LR
    W["**1 business day** — 2024-12-31<br/><small>1 Mo = 4.4 · 2 Yr = 4.25 · 10 Yr = 4.58 · 30 Yr = 4.78 · …</small>"]
    W -->|"row_rules fan-out (13×)"| A["2024-12-31 · 1 Mo · tenor 1 · 4.4"]
    W --> B["2024-12-31 · 2 Yr · tenor 24 · 4.25"]
    W --> C["2024-12-31 · 10 Yr · tenor 120 · 4.58"]
    W --> D["2024-12-31 · 30 Yr · tenor 360 · 4.78"]

Edge cases sourced from.

pandas melt / tidyr pivot_longer — wide financial time series need reshaping before analysis.
Maturity labels are text (1 Mo … 30 Yr); a tidy tenor needs a numeric sort key, hence the tenor_months column.
The header is double-quoted because maturity names contain a space ("1 Mo"); referenced as [1 Mo] once unquoted.

Data source. US Treasury — Daily Treasury Par Yield Curve Rates via the resource-center daily CSV endpoint. Public domain (US Government work). (This slice: 5 real business days from late 2024, spread far enough apart that the curve visibly moves — 5 input rows fan out to 65 tidy date-tenor rows.)

The trick¶

Convert the date once in input_schema — DATE_CONVERT([Date], 'MM/DD/YYYY', 'YYYY-MM-DD') — and every emitted row reuses that row-constant.
Unpivot via multi-row row_rules. Each entry in rows: [ … ] emits one row, pulling a different maturity column ([2 Yr]) and stamping both the tenor label and its length in months as literals. 13 entries → 13 date-tenor rows per business day.

Field references inside overrides

Inside an override you reference fields with [..], not other $variables.

At full scale¶

bash fetch-full.sh          # downloads 2023+2024 daily rates into ./full/
bxp-cli --config full.json  # ~499 business days → ~6.5k tidy date-tenor rows

fetch-full.sh deliberately limits itself to 2023-2024: both years carry the full 13-maturity schema. Older Treasury files have a different column set (the 2 Mo tenor began 2018, 4 Mo only in Oct 2022), so concatenating them under one header would misalign columns.

Final result¶

The 2024-12-31 row holding 13 yields across 13 columns becomes 13 tidy rows carrying a numeric tenor:

2024-12-31,1 Mo,1,4.4
2024-12-31,2 Yr,24,4.25
2024-12-31,10 Yr,120,4.58
2024-12-31,30 Yr,360,4.78

Sorted by tenor_months that is the yield curve — it drops straight into a GROUP BY date time series or an ORDER BY tenor_months curve plot, with no melt step and no label→months dictionary in Python.

Sample data¶

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

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

{
  // US Treasury "Daily Treasury Par Yield Curve Rates" — the canonical wide
  // yield-curve file: one column PER MATURITY ("1 Mo", "2 Yr", … "30 Yr") and
  // one row per business day. Almost every quant/plotting workflow first melts
  // it to long/tidy form (one row per date-tenor) and maps each maturity label
  // to a numeric tenor so the curve can be sorted and plotted. bxp does both
  // declaratively: one daily row fans out to 13 tidy rows via multi-row
  // row_rules, each stamping the tenor label + its length in months.
  conversion_templates: {
    treasury_curve_to_long: {
      data_dir:           ".",
      file_pattern_in:    ".csv",
      file_pattern_out:   ".csvx",
      // TRICK 0 — the header is double-quoted because maturity names contain a
      // space ("1 Mo"); referenced as [1 Mo] once unquoted.
      csv_text_quote_in:  "double",
      csv_text_quote_out: "double",

      input_schema: {
        // TRICK 1 — convert the US MM/DD/YYYY date to ISO-8601 ONCE; every
        // emitted row below reuses this row-constant.
        $date: "DATE_CONVERT([Date], 'MM/DD/YYYY', 'YYYY-MM-DD')"
      },

      // TRICK 2 — UNPIVOT (wide → long). Each `rows` entry emits one tidy row,
      // pulling a different maturity column and stamping (a) the tenor label
      // and (b) its length in MONTHS as a literal — so the curve sorts/plots
      // numerically instead of by accidental column order. 13 maturities in →
      // 13 date-tenor rows out per business day. (Inside an override you
      // reference *fields* with [..], not other $variables.)
      row_rules: [
        {
          when: "1 = 1",
          rows: [
            { $tenor: "'1 Mo'",  $months: "1",   $yield: "[1 Mo]"  },
            { $tenor: "'2 Mo'",  $months: "2",   $yield: "[2 Mo]"  },
            { $tenor: "'3 Mo'",  $months: "3",   $yield: "[3 Mo]"  },
            { $tenor: "'4 Mo'",  $months: "4",   $yield: "[4 Mo]"  },
            { $tenor: "'6 Mo'",  $months: "6",   $yield: "[6 Mo]"  },
            { $tenor: "'1 Yr'",  $months: "12",  $yield: "[1 Yr]"  },
            { $tenor: "'2 Yr'",  $months: "24",  $yield: "[2 Yr]"  },
            { $tenor: "'3 Yr'",  $months: "36",  $yield: "[3 Yr]"  },
            { $tenor: "'5 Yr'",  $months: "60",  $yield: "[5 Yr]"  },
            { $tenor: "'7 Yr'",  $months: "84",  $yield: "[7 Yr]"  },
            { $tenor: "'10 Yr'", $months: "120", $yield: "[10 Yr]" },
            { $tenor: "'20 Yr'", $months: "240", $yield: "[20 Yr]" },
            { $tenor: "'30 Yr'", $months: "360", $yield: "[30 Yr]" }
          ]
        }
      ],

      output_schema: {
        date:         "$date",
        tenor:        "$tenor",
        tenor_months: "$months",
        yield_pct:    "$yield"
      }
    }
  }
}

Date,"1 Mo","2 Mo","3 Mo","4 Mo","6 Mo","1 Yr","2 Yr","3 Yr","5 Yr","7 Yr","10 Yr","20 Yr","30 Yr"
12/31/2024,4.40,4.39,4.37,4.32,4.24,4.16,4.25,4.27,4.38,4.48,4.58,4.86,4.78
12/30/2024,4.43,4.42,4.37,4.33,4.25,4.17,4.24,4.29,4.37,4.46,4.55,4.84,4.77
12/03/2024,4.66,4.56,4.49,4.48,4.40,4.27,4.17,4.13,4.11,4.17,4.23,4.50,4.40
11/01/2024,4.75,4.74,4.61,4.53,4.42,4.28,4.21,4.18,4.22,4.30,4.37,4.68,4.57
10/03/2024,4.99,4.85,4.68,4.61,4.37,4.02,3.70,3.62,3.62,3.71,3.85,4.24,4.18

date,tenor,tenor_months,yield_pct
2024-12-31,1 Mo,1,4.4
2024-12-31,2 Mo,2,4.39
2024-12-31,3 Mo,3,4.37
2024-12-31,4 Mo,4,4.32
2024-12-31,6 Mo,6,4.24
2024-12-31,1 Yr,12,4.16
2024-12-31,2 Yr,24,4.25
2024-12-31,3 Yr,36,4.27
2024-12-31,5 Yr,60,4.38
2024-12-31,7 Yr,84,4.48
2024-12-31,10 Yr,120,4.58
2024-12-31,20 Yr,240,4.86
2024-12-31,30 Yr,360,4.78
2024-12-30,1 Mo,1,4.43
2024-12-30,2 Mo,2,4.42
2024-12-30,3 Mo,3,4.37
2024-12-30,4 Mo,4,4.33
2024-12-30,6 Mo,6,4.25
2024-12-30,1 Yr,12,4.17
2024-12-30,2 Yr,24,4.24
2024-12-30,3 Yr,36,4.29
2024-12-30,5 Yr,60,4.37
2024-12-30,7 Yr,84,4.46
2024-12-30,10 Yr,120,4.55
2024-12-30,20 Yr,240,4.84
2024-12-30,30 Yr,360,4.77
2024-12-03,1 Mo,1,4.66
2024-12-03,2 Mo,2,4.56
2024-12-03,3 Mo,3,4.49
2024-12-03,4 Mo,4,4.48
2024-12-03,6 Mo,6,4.4
2024-12-03,1 Yr,12,4.27
2024-12-03,2 Yr,24,4.17
2024-12-03,3 Yr,36,4.13
2024-12-03,5 Yr,60,4.11
2024-12-03,7 Yr,84,4.17
2024-12-03,10 Yr,120,4.23
2024-12-03,20 Yr,240,4.5
2024-12-03,30 Yr,360,4.4
2024-11-01,1 Mo,1,4.75
2024-11-01,2 Mo,2,4.74
2024-11-01,3 Mo,3,4.61
2024-11-01,4 Mo,4,4.53
2024-11-01,6 Mo,6,4.42
2024-11-01,1 Yr,12,4.28
2024-11-01,2 Yr,24,4.21
2024-11-01,3 Yr,36,4.18
2024-11-01,5 Yr,60,4.22
2024-11-01,7 Yr,84,4.3
2024-11-01,10 Yr,120,4.37
2024-11-01,20 Yr,240,4.68
2024-11-01,30 Yr,360,4.57
2024-10-03,1 Mo,1,4.99
2024-10-03,2 Mo,2,4.85
2024-10-03,3 Mo,3,4.68
2024-10-03,4 Mo,4,4.61
2024-10-03,6 Mo,6,4.37
2024-10-03,1 Yr,12,4.02
2024-10-03,2 Yr,24,3.7
2024-10-03,3 Yr,36,3.62
2024-10-03,5 Yr,60,3.62
2024-10-03,7 Yr,84,3.71
2024-10-03,10 Yr,120,3.85
2024-10-03,20 Yr,240,4.24
2024-10-03,30 Yr,360,4.18

Full-scale & binary files (run it on the complete dataset): fetch-full.sh · full.json.