treasury-gov-auction-data-quarter-line-ratio-spx.py

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)