Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dharmatech/a0cbed97a9b1b7e5c718ebaf754302fe to your computer and use it in GitHub Desktop.
Save dharmatech/a0cbed97a9b1b7e5c718ebaf754302fe to your computer and use it in GitHub Desktop.
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment