Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save dharmatech/b38567a997cfef1feaf1f17be41751a3 to your computer and use it in GitHub Desktop.
Save dharmatech/b38567a997cfef1feaf1f17be41751a3 to your computer and use it in GitHub Desktop.
import pandas as pd
import treasury_gov_pandas
from bokeh.plotting import figure, show
from bokeh.models import NumeralTickFormatter, HoverTool
# ---------------------------------------------------------------------
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'])
# ----------------------------------------------------------------------
p = figure(title=f'Treasury Securities Auctions Data : {freq}', sizing_mode='stretch_both', x_axis_type='datetime', x_axis_label='date', y_axis_label='')
p.add_tools(HoverTool(
tooltips=[
('issue_date', '@issue_date{%F}'),
('bills_notes_ratio', '@bills_notes_ratio{0.00}')
],
formatters={ '@issue_date': 'datetime' }))
p.yaxis.formatter = NumeralTickFormatter(format='0a')
p.line(x='issue_date', y='bills_notes_ratio', color='black', legend_label='Issued Bills/Notes ratio', source=bills_notes_bonds_issued)
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