In [2]:
# Required file: Z1_data.xml; download from: http://www.federalreserve.gov/datadownload/Choose.aspx?rel=Z.1
from __future__ import division
from lxml import etree
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import datetime
%matplotlib inline
In [3]:
# 0. Define a functions for managing parsed z1 data
# 0.1 Create a searchable legend in csv format
def createLegend(root):
legend_df = pd.DataFrame(columns=['Series Name','Description','Frequency','Start','End'])
x = 0
for levelA in root:
for levelB in levelA[4:-1]:
entry = []
ident = levelB.get('SERIES_NAME')
freq = ident[-1]
entry.append(ident)
for levelC in levelB:
for n,levelD in enumerate(levelC):
if n == 0:
entry.append(levelD[1].text)
entry.append(freq)
for levelC in [levelB[1],levelB[-1]]:
d= levelC.get('TIME_PERIOD')
d= datetime.datetime.strptime(d, '%Y-%m-%d').strftime('%m-%d-%Y')
entry.append(d)
legend_df.loc[x] = entry
x+=1
legend_df.to_csv('z1Legend.csv')
return legend_df
# 0.2 Create a Pandas dataframe for the z1 series with code: name
def getSeries(name):
dates = []
value= np.array([])
for levelA in root:
for levelB in levelA:
ident = levelB.get('SERIES_NAME')
if ident in [name]:
for levelC in levelB:
for n,levelD in enumerate(levelC):
if n == 0:
description = levelD[1].text
for levelC in levelB[1:-1]:
v = levelC.get('OBS_VALUE')
d= levelC.get('TIME_PERIOD')
dates = np.append(dates,d)
value = np.append(value,float(v))
for n,d in enumerate(dates):
dates[n]= datetime.datetime.strptime(d,'%Y-%m-%d').strftime('%m-%d-%Y')
df = pd.DataFrame(value,index=dates,columns = [description])
return df
# 0.3 Create a Pandas dataframe for the z1 series with codes in dataList
def getDataSet(dataList):
df = pd.DataFrame([])
for name in dataList:
newSeries=getSeries(name)
df = pd.concat([df,newSeries],axis=1, join_axes=[newSeries.index])
return df
In [4]:
# 1. Import the xml data and create a legend
# 1.2 parse
tree = etree.parse("Z1_data.xml")
root = tree.getroot()
# 1.2 create a legend in csv format
# legend= createLegend(root)
In [4]:
In [5]:
# 4. Create data sets for money market, capital market, and intermeidary assets
# 4.1 Money market data
moneyMarketNames = [
'FL313161113.A',
'FL703135005.A',
'FL893169105.A',
'FL892150005.A']
moneyMarketData = getDataSet(moneyMarketNames)
moneyMarketData.columns = ['T-bills','Negotiable CDs','Commerical paper','Fed funds & repos']
moneyMarketData = moneyMarketData.convert_objects(convert_numeric=True)
moneyMarketData = np.round(moneyMarketData/1000)
In [6]:
# 4.2 Capital market data
capitalMarketNames = [
'FL893064105.A', # Corporate equities L.213 Line 1
'FL893065105.A', # Home mortgages L.217 Line 2
'FL103163003.A', # Nonfinancial Corporate bonds L.101 Line 22
'FL313161125.A', # Other Treas. securities L.209 Line 4
'FL893161705.A', # Agency- and GSE-Backed Securities L.210 Line 1
'FL213162005.A', # State and local governments Securities L.104 Line 20
'FL893065505.A', # Commerical Mortgages L.217 Line 4
'FL703066005.A', # Consumer credit L.109 Line 14
]
capitalMarketData = getDataSet(capitalMarketNames)
capitalMarketData.columns = ['Equities','Home mort','Corp bonds','Treas bonds','Agency/GSE sec','Muni bonds','Commercial mort','Consumer credit']
capitalMarketData = capitalMarketData.convert_objects(convert_numeric=True)
capitalMarketData = np.round(capitalMarketData/1000)
In [7]:
# 4.3 Intermediary data
intermediaryNames = [
'FL704090005.A', # depository inst. L.109 Line 1
# '', # savings and loans
'FL474090005.A', # credit unions L.13 line 1
'FL544090005.A', # life insurance L.115 line 1
'FL514090005.A', # fire and casualty L.114 line 1
'FL574090005.A', # pension funds (private) L.117 line 1
'FL344090005.A', # federal retirement funds L.119 line 1
'FL224090005.A', # state and local retirement funds L.118
'FL614090005.A', # finance companies L.126 Line 1
'FL654090005.A', # mutual funds L.121 Line 1
'FL634090005.A', # money market mutual funds Line.120 Line 1
]
intermediaryData = getDataSet(intermediaryNames)
# len(intermediaryData.columns)
# intermediaryData.columns
intermediaryData.columns = ['depInst','creditU','life','fire','pension','federalFunds','stateLocalFunds','finance','mutual','moneyMarketMutual']
intermediaryData = capitalMarketData.convert_objects(convert_numeric=True)
intermediaryData = np.round(capitalMarketData/1000)
In [8]:
endDate = moneyMarketData.index[-1]
endYear = int(endDate[6:])
midYear = endYear-10
midDate = '12-31-'+str(midYear)
In [9]:
nf = open('table_001_money_market_instruments.tex', 'w')
nf.write('\\begin{tabular}{p{4.5cm}ccccc} \\multicolumn{5}{l}{\\large Selected money market instruments outstanding (bil.~of dollars) \\normalsize} \\\\\\hline\n')
nf.write(' & & \\textbf{'),nf.write(str(midYear)),nf.write('} & & \\textbf{'),nf.write(str(endYear)),nf.write('}\\\\\\hline\n')
nf.write('T-bills & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[midDate]['T-bills']))), nf.write(' & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[endDate]['T-bills']))), nf.write('\\\\\n')
nf.write('Negotiable CDs & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[midDate]['Negotiable CDs']))), nf.write(' & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[endDate]['Negotiable CDs']))), nf.write('\\\\\n')
nf.write('Commerical paper & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[midDate]['Commerical paper']))), nf.write(' & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[endDate]['Commerical paper']))), nf.write('\\\\\n')
nf.write('Fed funds \& repos & & '), nf.write(str('{:,g}'.format(moneyMarketData.loc[midDate]['Fed funds & repos']))), nf.write(' & & '),nf.write(str('{:,g}'.format(moneyMarketData.loc[endDate]['Fed funds & repos']))), nf.write('\\\\\\hline\n')
nf.write('\\end{tabular}\n')
nf.close()
In [10]:
nf = open('table_001_capital_market_instruments.tex', 'w')
nf.write('\\begin{tabular}{p{4.5cm}ccccc} \\multicolumn{5}{l}{\\large Selected capital market instruments outstanding (bil.~of dollars) \\normalsize} \\\\\\hline\n')
nf.write(' & & \\textbf{'),nf.write(str(midYear)),nf.write('} & & \\textbf{'),nf.write(str(endYear)),nf.write('}\\\\\\hline\n')
nf.write('Equities & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Equities']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Equities']))), nf.write('\\\\\n')
nf.write('Home mortgages & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Home mort']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Home mort']))), nf.write('\\\\\n')
nf.write('Corp.~bonds & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Corp bonds']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Corp bonds']))), nf.write('\\\\\n')
nf.write('Treas.~bonds & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Treas bonds']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Treas bonds']))), nf.write('\\\\\n')
nf.write('Agency/GDS sec. & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Agency/GSE sec']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Agency/GSE sec']))), nf.write('\\\\\n')
nf.write('Muni bonds & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Muni bonds']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Muni bonds']))), nf.write('\\\\\n')
nf.write('Commercial mort. & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Commercial mort']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Commercial mort']))), nf.write('\\\\\n')
nf.write('Consumer credit & & '), nf.write(str('{:,g}'.format(capitalMarketData.loc[midDate]['Consumer credit']))), nf.write(' & & '),nf.write(str('{:,g}'.format(capitalMarketData.loc[endDate]['Consumer credit']))), nf.write('\\\\\\hline\n')
nf.write('\\end{tabular}\n')
nf.close()
In [11]:
# nf = open('table_slides_002_capital_market_instruments.tex', 'w')
# nf.write('\\fr{\n')
# nf.write('\\ft{Capital Market Instruments}\n')
# nf.write('\\begin{tabular}{p{4.5cm}ccccc} \\multicolumn{5}{l}{\\large Selected capital market instruments outstanding (bil.~of dollars) \\normalsize} \\\\\\hline\n')
# nf.write(' & & \\textbf{'),nf.write(str(midYear)),nf.write('} & & \\textbf{'),nf.write(str(endYear)),nf.write('}\\\\\\hline\n')
# nf.write('Equities & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Equities']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Equities']))), nf.write('\\\\\n')
# nf.write('Home mortgages & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Home mort']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Home mort']))), nf.write('\\\\\n')
# nf.write('Corp.~bonds & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Corp bonds']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Corp bonds']))), nf.write('\\\\\n')
# nf.write('Treas.~bonds & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Treas bonds']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Treas bonds']))), nf.write('\\\\\n')
# nf.write('Agency/GDS sec. & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Agency/GSE sec']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Agency/GSE sec']))), nf.write('\\\\\n')
# nf.write('Muni bonds & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Muni bonds']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Muni bonds']))), nf.write('\\\\\n')
# nf.write('Commercial mort. & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Commercial mort']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Commercial mort']))), nf.write('\\\\\n')
# nf.write('Consumer credit & & '), nf.write(str('{:,}'.format(capitalMarketData.loc[midDate]['Consumer credit']))), nf.write(' & & '),nf.write(str('{:,}'.format(capitalMarketData.loc[endDate]['Consumer credit']))), nf.write('\\\\\\hline\n')
# nf.write('\\end{tabular}\n')
# nf.write('}')
# nf.close()
In [13]:
In [ ]:
In [ ]:
In [ ]:
In [ ]:
In [32]:
capitalMarketNames = [
'FL893064105.A', # Corporate equities L.213 Line 1
'FL893065105.A', # Home mortgages L.217 Line 2
'FL103163003.A', # Nonfinancial Corporate bonds L.101 Line 22
'FL313161125.A', # Other Treas. securities L.209 Line 4
'FL893161705.A', # Agency- and GSE-Backed Securities L.210 Line 1
'FL213162005.A', # State and local governments Securities L.104 Line 20
'FL893065505.A', # Commerical Mortgages L.217 Line 4
'FL703066005.A', # Consumer credit L.109 Line 14
]
capitalMarketData = getDataSet(capitalMarketNames)
capitalMarketData.columns = ['Equities','Home morts','Corp bonds','Treas bonds','Agency/GSE sec','Muni bonds','Commercial morts','Consumer credit']
# capitalMarketData.plot()
capitalMarketData = capitalMarketData.pct_change()
capitalMarketData.plot(legend=False)
Out[32]:
In [33]:
capitalMarketData
Out[33]:
In [ ]: