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

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)
# ----------------------------------------------------------------------