Note: the bid-to-cover ratio is a measure of demand for a given instrument
A look at US federal debt issuance, according to auctions held by the US Treasury.
import pandas as pd
import numpy as np
import altair as alt
def makeNumeric(data, metric):
data[metric] = data[metric].apply(pd.to_numeric)
return data
def doChart(year, metric='bidToCoverRatio', instruments=['Bill', 'Bond', 'CMB', 'FRN', 'Note', 'TIPS']):
url = 'https://www.treasurydirect.gov/TA_WS/securities/search?startDate={0}-01-01&endDate={0}-12-31&compact=false&dateFieldName=auctionDate'
df = pd.read_json(url.format(year))
df = makeNumeric(df, metric)
recordFilter = ~df[metric].isnull()
df_sub = df[recordFilter][['type', 'auctionDate', metric]]
if metric == 'bidToCoverRatio':
return alt.Chart(df_sub[df_sub.type.isin(instruments)]).mark_point(size=50).encode(
alt.X('auctionDate:T', axis=alt.Axis(title=f"Auction Date ({year})")),
alt.Y(f'{metric}:Q'),
alt.Color('type:N', title='Instrument', legend=alt.Legend(orient='top'), scale=alt.Scale(scheme='category10')),
tooltip=[alt.Tooltip('auctionDate:T'), alt.Tooltip('type:N'), alt.Tooltip(f'{metric}:Q')]
).properties(
title=f"{metric} for Treasury Auctions in {year}",
height=450,
width=750,
background='white'
)
else:
return alt.Chart(df_sub[df_sub.type.isin(instruments)]).mark_line().encode(
alt.X('auctionDate:T', axis=alt.Axis(title=f"Auction Date ({year})")),
alt.Y('{0}:Q'.format(metric)),
alt.Color('type:N', title='Instrument', legend=alt.Legend(orient='top'), scale=alt.Scale(scheme='category10')),
tooltip=[alt.Tooltip('auctionDate:T'), alt.Tooltip('type:N'), alt.Tooltip(f'{metric}:Q')]
).properties(
title=f"{metric} for Treasury Auctions in {year}",
height=450,
width=750,
background='white'
)
doChart(2022).properties(width=750, height=450)
alt.data_transformers.disable_max_rows()
def doTsChart(years, metric='bidToCoverRatio', instruments=['Bill', 'Bond', 'CMB', 'FRN', 'Note', 'TIPS']):
url = 'https://www.treasurydirect.gov/TA_WS/securities/search?startDate={0}-01-01&endDate={0}-12-31&compact=false&dateFieldName=auctionDate'
furls = map(lambda y: url.format(y), years)
dfs = map(lambda u: pd.read_json(u), furls)
dfs = [makeNumeric(df, metric) for df in dfs]
df_subs = (df[~df[metric].isnull()][['type', 'auctionDate', metric]] for df in dfs)
data = pd.concat(df_subs)
"""return data, alt.Chart(data[data.type.isin(instruments)][-5000:]).mark_line().encode(
alt.X('auctionDate:T', axis=alt.Axis(title="")),
alt.Y('{0}:Q'.format(metric)),
alt.Color('type:N', title='Instrument')
).properties(
title="{0} from {1} to {2}".format(metric, min(years), max(years)),
height=450,
width=750,
background='white'
)""";
if metric == 'bidToCoverRatio':
return data, alt.Chart(data[data.type.isin(instruments)]).mark_point(size=25, opacity=0.3, shape="cross").encode(
alt.X('auctionDate:T', axis=alt.Axis(title="")),
alt.Y(f'{metric}:Q'),
alt.Color('type:N', title='Instrument', legend=alt.Legend(orient='top'), scale=alt.Scale(scheme='category10')),
tooltip=[alt.Tooltip('auctionDate:T'), alt.Tooltip('type:N'), alt.Tooltip(f'{metric}:Q')]
).properties(
title="{0} from {1} to {2}".format(metric, min(years), max(years)),
height=450,
width=750,
background='white'
)
else:
return data, alt.Chart(data[data.type.isin(instruments)]).mark_line().encode(
alt.X('auctionDate:T', axis=alt.Axis(title="")),
alt.Y('{0}:Q'.format(metric)),
alt.Color('type:N', title='Instrument', legend=alt.Legend(orient='top'), scale=alt.Scale(scheme='category10')),
tooltip=[alt.Tooltip('auctionDate:T'), alt.Tooltip('type:N'), alt.Tooltip(f'{metric}:Q')]
).properties(
title="{0} from {1} to {2}".format(metric, min(years), max(years)),
height=450,
width=750,
background='white'
)
df, tsChart = doTsChart(range(2007, 2023))
tsChart.display()
# Y -> bidToCover
# X -> Year
# X2 -> Instrument
def doBoxWhiskerFacet(df, metric="bidToCoverRatio", title="Bid To Cover Ratio", instruments=['Note', 'Bill', 'Bond']):
lower_box = 'q1({}):Q'.format(metric)
lower_whisker = 'min({}):Q'.format(metric)
upper_box = 'q3({}):Q'.format(metric)
upper_whisker = 'max({}):Q'.format(metric)
middle_tick = 'median({}):Q'.format(metric)
x_axis = 'year(auctionDate):N'
chart_df = df[df.type.isin(instruments)].copy()
# Compose each layer individually
lower_plot = alt.Chart().mark_rule().encode(
alt.Y(lower_whisker, axis=alt.Axis(title=title, format='.2f')),
alt.Y2(lower_box),
alt.X(x_axis, axis=alt.Axis(title="Year", labelAngle=0, labelOverlap="greedy")),
)
middle_plot = alt.Chart().mark_bar(size=5.0).encode(
alt.Y(lower_box),
alt.Y2(upper_box),
alt.X(x_axis),
alt.Color(x_axis)
)
upper_plot = alt.Chart().mark_rule().encode(
alt.Y(upper_whisker),
alt.Y2(upper_box),
alt.X(x_axis)
)
middle_tick_plot = alt.Chart().mark_tick(
color='white',
size=5.0
).encode(
alt.Y(middle_tick),
alt.X(x_axis)
)
box_and_whisker = lower_plot + middle_plot + upper_plot + middle_tick_plot
return box_and_whisker.facet(column='type:O', data=chart_df, title=None).properties(
title="{0} from {1} to {2}".format(metric, df.auctionDate.min()[:4], df.auctionDate.max()[:4])
)
c = doBoxWhiskerFacet(df)
c.save('us-treasury-auctions.png')
c.display()
dfsa, ca = doTsChart(range(1995, 2023), metric='interestRate')
ca.display()
dfst, ct = doTsChart(range(1995, 2023), metric='averageMedianYield')
ct.display()
doBoxWhiskerFacet(dfsa, metric='interestRate', title='Interest Rate', instruments=['Note', 'Bond'])
doBoxWhiskerFacet(df, instruments=['FRN', 'Bill', 'TIPS'])
doChart(2021, metric='bidToCoverRatio')
doChart(2022, metric='bidToCoverRatio')
doChart(2021, metric='averageMedianYield')
doChart(2022, metric='averageMedianYield')
doChart(2021, metric='interestRate')
doChart(2022, metric='interestRate')
def doChartArea(year, metric='bidToCoverRatio', instruments=['Bill', 'Bond', 'CMB', 'FRN', 'Note', 'TIPS'], op='sum'):
url = 'https://www.treasurydirect.gov/TA_WS/securities/search?startDate={0}-01-01&endDate={0}-12-31&compact=false&dateFieldName=auctionDate'
df = pd.read_json(url.format(year))
df = makeNumeric(df, metric)
#print(df.head())
df['dt'] = df['auctionDate'].apply(pd.to_datetime)
df_sub = df[df['securityType'].isin(instruments)] if len(instruments) else df
df_sub = df_sub[~df_sub[metric].isnull()][['securityType', 'dt', metric]]
#print(df_sub.head())
fix = df_sub.groupby(['dt', metric, 'securityType']).agg(op).reset_index()
#print(fix.head())
return alt.Chart(fix).mark_bar().encode(
alt.X('dt:T', axis=alt.Axis(title="Auction Date ({})".format(year))),
alt.Y('sum({0}):Q'.format(metric)),
alt.Color('securityType:N', title='Instrument')
).properties(
title="{0} for Treasury Auctions in {1}".format(metric, year),
height=450,
width=750,
background='white'
)
doChartArea(2021, instruments=[], metric='totalTendered')
doChartArea(2022, instruments=[], metric='totalTendered')
dfs19, c19 = doTsChart(range(2017, 2023), metric='interestRate', instruments=['Bond', 'Note', 'TIPS'])
c19.display()
dfs8, c8 = doTsChart(range(2003, 2009), metric='interestRate', instruments=['Bond', 'Note', 'TIPS'])
c8.display()
dfs10, c10 = doTsChart(range(2010, 2014), metric='interestRate', instruments=['Bond', 'Note', 'TIPS'])
c10.display()