Skip to content

Instantly share code, notes, and snippets.

Revisions

  1. dharmatech created this gist Nov 4, 2023.
    59 changes: 59 additions & 0 deletions treasury-gov-auction-data-quarter-line-ratio-spx.py
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,59 @@
    import pandas as pd

    from bokeh.plotting import figure, show
    from bokeh.models import NumeralTickFormatter, HoverTool

    from bokeh.models import LinearAxis, Range1d

    import yfinance as yf
    # ---------------------------------------------------------------------
    df = treasury_gov_pandas.update_records(
    'auctions_query.pkl',
    'https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/od/auctions_query')
    # ----------------------------------------------------------------------
    df['record_date'] = pd.to_datetime(df['record_date'])
    df['issue_date'] = pd.to_datetime(df['issue_date'])
    df['maturity_date'] = pd.to_datetime(df['maturity_date'])
    df['auction_date'] = pd.to_datetime(df['auction_date'])

    df['total_accepted'] = pd.to_numeric(df['total_accepted'], errors='coerce')

    df['total_accepted_neg'] = df['total_accepted'] * -1
    # ----------------------------------------------------------------------
    bills = df[df['security_type'] == 'Bill']
    notes = df[df['security_type'] == 'Note']
    bonds = df[df['security_type'] == 'Bond']
    # ----------------------------------------------------------------------
    freq='Q'
    # ----------------------------------------------------------------------
    bills_issued = bills.groupby(pd.Grouper(key='issue_date', freq=freq))['total_accepted'].sum().to_frame()
    notes_issued = notes.groupby(pd.Grouper(key='issue_date', freq=freq))['total_accepted'].sum().to_frame()
    bonds_issued = bonds.groupby(pd.Grouper(key='issue_date', freq=freq))['total_accepted'].sum().to_frame()
    # ----------------------------------------------------------------------
    bills_notes_bonds_issued = bills_issued.merge(notes_issued, how='outer', on='issue_date').merge(bonds_issued, how='outer', on='issue_date')

    bills_notes_bonds_issued.columns = ['bills', 'notes', 'bonds']

    bills_notes_bonds_issued['bills_notes_ratio'] = bills_notes_bonds_issued['bills'] / bills_notes_bonds_issued['notes']

    bills_notes_bonds_issued['bills_notes_bonds_ratio'] = bills_notes_bonds_issued['bills'] / (bills_notes_bonds_issued['notes'] + bills_notes_bonds_issued['bonds'])
    # ----------------------------------------------------------------------
    spx = yf.Ticker('^GSPC')

    data = spx.history(start='1980-01-01', interval='1d')
    # ----------------------------------------------------------------------
    p = figure(title=f'Treasury Securities Auctions Data : {freq}', sizing_mode='stretch_both', x_axis_type='datetime', x_axis_label='date', y_axis_label='', y_range=(0, 10))

    p.line(x='issue_date', y='bills_notes_ratio', color='black', legend_label='Bills/Notes ratio', source=bills_notes_bonds_issued)

    p.extra_y_ranges = {"spx": Range1d(start=min(data['Close']), end=max(data['Close']))}

    p.add_layout(LinearAxis(y_range_name="spx", axis_label='SPX'), 'right')

    p.line(x='Date', y='Close', color='blue', legend_label='SPX', source=data, y_range_name="spx")

    p.legend.click_policy = 'hide'

    p.legend.location = 'top_left'

    show(p)