Treasury Markets Decision Pack

What data it expects

Connect QuickBooks Online or drop a CSV / Excel export with a similar layout — the AI adapts the workflow if your columns differ.

How it works — every step, readable

1. 01Load FRED market data (1 year)

   SELECT * FROM read_csv_auto('FRED_DGS3MO_DGS2_DGS10_SOFR_DEXUSEU_DEXJPUS_DEXUSUK.csv', header=true)

2. 02Market snapshot (latest curve, SOFR, FX)

   filters to the relevant rows

   SELECT MAX(date) AS as_of,
     ROUND(arg_max(dgs3mo, date) FILTER (WHERE dgs3mo IS NOT NULL), 2) AS ust_3m,
     ROUND(arg_max(dgs2, date) FILTER (WHERE dgs2 IS NOT NULL), 2) AS ust_2y,
     ROUND(arg_max(dgs10, date) FILTER (WHERE dgs10 IS NOT NULL), 2) AS ust_10y,
     ROUND(arg_max(sofr, date) FILTER (WHERE sofr IS NOT NULL), 2) AS sofr,
     ROUND(arg_max(dexuseu, date) FILTER (WHERE dexuseu IS NOT NULL), 4) AS usd_per_eur,
     ROUND(arg_max(dexusuk, date) FILTER (WHERE dexusuk IS NOT NULL), 4) AS usd_per_gbp,
     ROUND(arg_max(dexjpus, date) FILTER (WHERE dexjpus IS NOT NULL), 2) AS jpy_per_usd
   FROM input_1

3. 03SYNTHETIC portfolio - edit me

   -- SYNTHETIC portfolio (illustrative only) - EDIT amounts/maturities and rerun
   SELECT * FROM (VALUES
     ('Operating cash', 'Bank deposits', 'JPMorgan', 0.25, 400.0, 0.0),
     ('Govt MMF', 'Money market', 'Fidelity Govt MMF', 0.25, 900.0, NULL),
     ('T-Bills 1M', 'US Treasury', 'UST', 1.0, 600.0, NULL),
     ('T-Bills 3M', 'US Treasury', 'UST', 3.0, 800.0, NULL),
     ('T-Notes 2Y', 'US Treasury', 'UST', 24.0, 500.0, NULL),
     ('CP A-1', 'Commercial paper', 'Toyota Motor Credit', 2.0, 300.0, NULL),
     ('IG corp 18M', 'IG corporates', 'Apple Inc', 18.0, 250.0, NULL),
     ('Time deposit 6M', 'Time deposit', 'Citibank', 6.0, 250.0, NULL)
   ) AS t(instrument, asset_class, issuer, maturity_months, amount_musd, fixed_yield_pct)

4. 04SYNTHETIC FX exposures - edit me

   -- SYNTHETIC FX exposures (illustrative only) - EDIT and rerun
   SELECT * FROM (VALUES
     ('EUR', 1200.0, 700.0, 0.50),
     ('GBP', 400.0, 250.0, 0.35),
     ('JPY', 300.0, 550.0, 0.25)
   ) AS t(currency, annual_revenue_m_ccy, annual_costs_m_ccy, hedge_ratio)

5. 05Output 1 - Cash & liquidity ladder

   aggregates rows into summary totals · buckets values by condition · computes running / windowed totals

   WITH b AS (
     SELECT *, CASE WHEN maturity_months <= 0.5 THEN '0) Overnight-2w'
       WHEN maturity_months <= 1 THEN '1) 2w-1 month'
       WHEN maturity_months <= 3 THEN '2) 1-3 months'
       WHEN maturity_months <= 12 THEN '3) 3-12 months'
       ELSE '4) Over 12 months' END AS bucket
     FROM input_1),
   l AS (SELECT bucket, SUM(amount_musd) AS amount_musd FROM b GROUP BY bucket)
   SELECT bucket, ROUND(amount_musd, 0) AS amount_musd,
     ROUND(SUM(amount_musd) OVER (ORDER BY bucket), 0) AS cumulative_musd,
     ROUND(100.0 * SUM(amount_musd) OVER (ORDER BY bucket) / SUM(amount_musd) OVER (), 1) AS cum_pct_of_total,
     CASE WHEN bucket = '2) 1-3 months' AND 100.0 * SUM(amount_musd) OVER (ORDER BY bucket) / SUM(amount_musd) OVER () < 40
       THEN 'BREACH: <40% available inside 3 months'
       WHEN bucket = '2) 1-3 months' THEN 'OK: >=40% available inside 3 months'
       ELSE '' END AS policy_check
   FROM l ORDER BY bucket

6. 06Output 2 - Duration, yield, concentration, policy

   combines data from multiple inputs · buckets values by condition · computes running / windowed totals

   WITH y AS (
     SELECT p.*, COALESCE(p.fixed_yield_pct,
       CASE WHEN p.maturity_months <= 3 THEN s.ust_3m
            WHEN p.maturity_months <= 24 THEN s.ust_2y
            ELSE s.ust_10y END) AS est_yield_pct
     FROM input_1 p CROSS JOIN input_2 s)
   SELECT instrument, issuer, amount_musd,
     ROUND(maturity_months / 12.0, 2) AS duration_yrs,
     ROUND(est_yield_pct, 2) AS est_yield_pct,
     ROUND(100.0 * amount_musd / SUM(amount_musd) OVER (), 1) AS pct_of_portfolio,
     ROUND(100.0 * SUM(amount_musd) OVER (PARTITION BY issuer) / SUM(amount_musd) OVER (), 1) AS issuer_share_pct,
     CASE WHEN issuer <> 'UST' AND 100.0 * SUM(amount_musd) OVER (PARTITION BY issuer) / SUM(amount_musd) OVER () > 25
       THEN 'CONCENTRATION: >25% single non-UST issuer' ELSE 'OK' END AS policy_check
   FROM y ORDER BY amount_musd DESC

7. 07Output 3 - FX exposure & hedge scenarios

   combines data from multiple inputs · sorts the output

   SELECT f.currency,
     CASE f.currency WHEN 'EUR' THEN s.usd_per_eur WHEN 'GBP' THEN s.usd_per_gbp WHEN 'JPY' THEN ROUND(1.0 / s.jpy_per_usd, 6) END AS spot_usd_per_ccy,
     ROUND((f.annual_revenue_m_ccy - f.annual_costs_m_ccy) *
       CASE f.currency WHEN 'EUR' THEN s.usd_per_eur WHEN 'GBP' THEN s.usd_per_gbp WHEN 'JPY' THEN 1.0 / s.jpy_per_usd END, 1) AS net_exposure_musd,
     f.hedge_ratio,
     ROUND(-0.10 * (1 - f.hedge_ratio) * (f.annual_revenue_m_ccy - f.annual_costs_m_ccy) *
       CASE f.currency WHEN 'EUR' THEN s.usd_per_eur WHEN 'GBP' THEN s.usd_per_gbp WHEN 'JPY' THEN 1.0 / s.jpy_per_usd END, 1) AS usd_up_10pct_impact_musd,
     ROUND(0.10 * (1 - f.hedge_ratio) * (f.annual_revenue_m_ccy - f.annual_costs_m_ccy) *
       CASE f.currency WHEN 'EUR' THEN s.usd_per_eur WHEN 'GBP' THEN s.usd_per_gbp WHEN 'JPY' THEN 1.0 / s.jpy_per_usd END, 1) AS usd_down_10pct_impact_musd
   FROM input_1 f CROSS JOIN input_2 s
   ORDER BY ABS(net_exposure_musd) DESC

8. 08Output 4 - Rate & funding sensitivities

   combines data from multiple inputs · aggregates rows into summary totals · buckets values by condition

   SELECT s.shock_bp,
     ROUND(SUM(CASE WHEN p.maturity_months <= 12 THEN p.amount_musd ELSE 0 END) * s.shock_bp / 10000.0, 1) AS reinvest_income_delta_musd_annual,
     ROUND(-SUM(p.amount_musd * p.maturity_months / 12.0) * s.shock_bp / 10000.0, 1) AS mark_to_market_delta_musd
   FROM input_1 p CROSS JOIN (VALUES (-100), (100), (200)) AS s(shock_bp)
   GROUP BY s.shock_bp ORDER BY s.shock_bp

9. 09Output 5 - Executive decision memo

   buckets values by condition · appends result sets (e.g. a TOTAL row) · filters to the relevant rows

   SELECT ord, section, line FROM (
   SELECT 1 AS ord, 'LIQUIDITY' AS section,
     'Total portfolio $' || (SELECT MAX(cumulative_musd) FROM input_1) || 'M; ' ||
     (SELECT cum_pct_of_total FROM input_1 WHERE bucket = '2) 1-3 months') || '% available inside 3 months (policy: >=40%).' AS line
   UNION ALL SELECT 2, 'PORTFOLIO',
     'Weighted avg duration ' || (SELECT ROUND(SUM(amount_musd * duration_yrs) / SUM(amount_musd), 2) FROM input_2) ||
     ' yrs at est. ' || (SELECT ROUND(SUM(amount_musd * est_yield_pct) / SUM(amount_musd), 2) FROM input_2) || '% blended yield.'
   UNION ALL SELECT 3, 'CONCENTRATION',
     COALESCE((SELECT 'Flag: ' || issuer || ' holds ' || issuer_share_pct || '% of the book.' FROM input_2 WHERE policy_check LIKE 'CONCENTRATION%' ORDER BY issuer_share_pct DESC LIMIT 1),
       'No single non-UST issuer above 25%.')
   UNION ALL SELECT 4, 'FX',
     (SELECT 'Largest net exposure ' || currency || ' $' || net_exposure_musd || 'M at hedge ratio ' || ROUND(hedge_ratio * 100, 0) || '%; a 10% USD rally costs $' || ABS(usd_up_10pct_impact_musd) || 'M unhedged.' FROM input_3 ORDER BY ABS(net_exposure_musd) DESC LIMIT 1)
   UNION ALL SELECT 5, 'RATES',
     (SELECT '+200bp: mark-to-market $' || mark_to_market_delta_musd || 'M; reinvestment income ' || CASE WHEN reinvest_income_delta_musd_annual >= 0 THEN '+' ELSE '' END || reinvest_income_delta_musd_annual || 'M/yr on the sub-1y book.' FROM input_4 WHERE shock_bp = 200)
   UNION ALL SELECT 6, 'RECOMMENDATION',
     CASE WHEN (SELECT cum_pct_of_total FROM input_1 WHERE bucket = '2) 1-3 months') < 40
       THEN 'Rebalance toward sub-3-month instruments to restore the liquidity policy floor.'
       ELSE 'Liquidity ladder within policy; extend duration opportunistically if curve steepens.' END
   UNION ALL SELECT 7, 'RECOMMENDATION',
     (SELECT 'Consider raising the ' || currency || ' hedge ratio toward 60-75% given exposure size.' FROM input_3 WHERE hedge_ratio < 0.6 ORDER BY ABS(net_exposure_musd) DESC LIMIT 1)
   UNION ALL SELECT 8, 'LINEAGE',
     'Every figure above traces to a canvas node: market snapshot (FRED), synthetic exposures (editable), ladder, portfolio metrics, FX scenarios, rate shocks.'
   ) ORDER BY ord