今天我们继续分享关于美债的数据。上次分享的是每次的拍卖细节，但是为了方便投资者处理信息，美国财政部还整理了一些总结性的数据，最有名的就是根据各个参与者分类的 Investor Class Auction Allotments 数据。比如我在之前的一个想法中就用到了这个数据，以说明美国国库券的承销主要由Primary Dealers来负责，该数据也会成为文章《关于缩表2.0》下次更新的素材之一。
美国国库券拍卖各类参与者所占份额分布图，券商与基金是两大参与者
下面这段简陋的代码处理的就是这个数据集：
# Treasury bills investor class auction allotments
auction_bill_url = 'https://home.treasury.gov/system/files/276/May_9_2022_IC_Bills.xls'
bill_df_raw=pd.read_excel(auction_bill_url, skiprows=3)
bill_df_raw.rename(columns=lambda x: x.strip().replace('\n','').replace(' ',''),inplace=True)
auction_rename_dict = {
'Issuedate':'issue_date',
'Securityterm':'tenor',
'Auctionhighrate%':'high_rate',
'Cusip':'cusip',
'Maturitydate':'maturity_date',
'Totalissue':'total_issue',
'(SOMA)FederalReservebanks':'Fed',
'Depositoryinstitutions':'banks',
'Individuals':'individuals',
'Dealersandbrokers':'broker_dealers',
'PensionandRetirementfundsandIns.Co.':'pension',
'Investmentfunds':'investment_fund',
'Foreignandinternational':'foreign',
'Other(seecategorydescription)':'other'
}
bill_df_raw.rename(columns=auction_rename_dict, inplace=True)
bill_df_raw.dropna(subset=['tenor'],inplace=True)
bill_df_raw['issue_date'] = pd.to_datetime(bill_df_raw['issue_date'])
# transform numerical variables to floats
numeric_cols2 = ['high_rate','total_issue','Fed','depository_inst',
'individuals','broker_dealers','pension','investment_fund','foreign','other']
for c in numeric_cols2:
bill_df_raw[c] = bill_df_raw[c].astype(float)以上给出了一个基本的数据集，没有进一步做什么处理。接下来，我们可以提取出按日程发行的国库券总量数据（另一种是现金管理票据，根据到期日不同有多达一百种类别，在此就不考虑了），然后计算出每次发行时每类参与者所购买的份额。
participants = ['Fed','depository_inst','individuals','broker_dealers',
'pension','investment_fund','foreign','other']
participants_w = [c+'_w' for c in participants]
bill_names = ['4-Week Bill','8-Week Bill','13-Week Bill','26-Week Bill','52-Week Bill']
total_bills = bill_df_raw.set_index(['issue_date','tenor'])['total_issue'].unstack().dropna(how='all')
total_bills_scheduled = total_bills[[c for c in total_bills.columns if 'Bill' in c]]
total_bills_scheduled = total_bills_scheduled.rename(columns=lambda x: x.replace('Bill','').strip())
total_bills_scheduled = total_bills_scheduled[['4-Week','8-Week','13-Week','26-Week','52-Week']]
total_bills_cmb = total_bills[[c for c in total_bills.columns if 'CMB' in c]] # cash management bills
# calculate participation shares
bill_df_idx=bill_df_raw.set_index(['issue_date','tenor'])
for c in participants:
bill_df_idx[c+'_w']=100*bill_df_idx[c]/bill_df_idx['total_issue']
bill_df_idx['broker_dealers_w'].hist(bins=100,color='k')同样的，长期国债的参与数据见此，笔者没有做更多处理，感兴趣的朋友们可自己试试。
# Treasury coupon securities (notes and bonds)
auction_coupon_url = 'https://home.treasury.gov/system/files/276/May_9_2022_IC_Coupons.xls'
coupon_df_raw=pd.read_excel(auction_coupon_url, skiprows=3)
coupon_df_raw.rename(columns=lambda x: x.strip().replace('\n','').replace(' ',''),inplace=True)
auction_rename_dict = {
'Issuedate':'issue_date',
'Securitytype':'security_type',
'(%)CouponrateOrSpread':'coupon_rate',
'Cusip':'cusip',
'Maturitydate':'maturity_date',
'Totalissue':'total_issue',
'(SOMA)FederalReservebanks':'Fed',
'Depositoryinstitutions':'banks',
'Individuals':'individuals',
'Dealersandbrokers':'broker_dealers',
'PensionandRetirementfundsandIns.Co.':'pension',
'Investmentfunds':'investment_fund',
'Foreignandinternational':'foreign',
'Other':'other'
}
coupon_df_raw.rename(columns=auction_rename_dict,inplace=True)
coupon_df_raw.dropna(subset=['security_type'],inplace=True)
coupon_df_raw['issue_date'] = pd.to_datetime(coupon_df_raw['issue_date'])
coupon_df_raw['tenor'] = coupon_df_raw['security_type'].apply(lambda x: x.split(' ')[0])
coupon_df_raw['asset_type'] = coupon_df_raw['security_type'].apply(lambda x: x.split(' ')[1])
# transform numerical variables to floats
numeric_cols = ['coupon_rate','total_issue','Fed','depository_inst',
'individuals','broker_dealers','pension','investment_fund','foreign','other']
for c in numeric_cols:
coupon_df_raw[c] = coupon_df_raw[c].astype(float)来源：知乎 www.zhihu.com
作者：知乎用户（登录查看详情）
【知乎日报】千万用户的选择，做朋友圈里的新鲜事分享大牛。
点击下载
本文转自： http://zhuanlan.zhihu.com/p/516037009?utm_campaign=rss&utm_medium=rss&utm_source=rss&utm_content=title
本站仅做收录，版权归原作者所有。