def getDataFromBloomberg(self, tickers, fields):
LocalTerminal = v3api.Terminal('localhost', 8194)
response = LocalTerminal.get_reference_data(tickers,
fields,
ignore_security_error=1,
ignore_field_error=1)
return response.as_frame()
def getHistDataFromBloomberg(self,
tickers,
init=None,
end=None,
freq='DAILY'):
if not init:
init = dt.datetime.today() - dt.timedelta(weeks=52)
dt.datetime.today() - dt.timedelta(days=1)
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(tickers, ['PX_LAST'],
ignore_security_error=1,
ignore_field_error=1,
start=init,
end=end,
period=freq)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
bloombergData = response.as_frame()
bloombergData.columns = bloombergData.columns.levels[0]
return bloombergData
def getForwardReturn(self, currency_short, currency_long):
cross = currency_short + currency_long
if cross == 'USDCLP' or cross == 'CLPUSD':
tickers = [
'CHN1W LAST Curncy', 'CHN1M LAST Curncy', 'CHN2M LAST Curncy',
'CHN3M LAST Curncy', 'CHN6M LAST Curncy', 'CHN9M LAST Curncy',
'CHN12M LAST Curncy', 'CHN18M LAST Curncy',
'CHN2Y LAST Curncy', 'CHN3Y LAST Curncy', 'CHN5Y LAST Curncy'
]
ccy_ticker = ['USDCLP Curncy']
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(
tickers, ['PX_BID', 'PX_ASK', 'DAYS_TO_MTY'],
ignore_security_error=1,
ignore_field_error=1)
ccy_response = LocalTerminal.get_reference_data(
ccy_ticker, ['PX_MID'],
ignore_security_error=1,
ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
fx_cross = ccy_response.as_frame()
fx_data_retrieved = response.as_frame().sort_values('DAYS_TO_MTY')
fx_data_retrieved['PX_BID'] = fx_data_retrieved['PX_BID'] / fx_cross[
'PX_MID'].values * 360 / fx_data_retrieved['DAYS_TO_MTY']
fx_data_retrieved['PX_ASK'] = fx_data_retrieved['PX_ASK'] / fx_cross[
'PX_MID'].values * 360 / fx_data_retrieved['DAYS_TO_MTY']
fx_data_retrieved.index = fx_data_retrieved['DAYS_TO_MTY']
del fx_data_retrieved['DAYS_TO_MTY']
return fx_data_retrieved
def get_swap_curves(self, currency):
bbg_ticker = {
'CLP': 'YCSW0193 Index',
'COP': 'YCSW0329 Index',
'BRL': 'YCMM0119 Index',
'MXN': 'YCSW0083 Index',
'USD': 'YCSW0023 Index',
'JPY': 'YCSW0013 Index',
'EUR': 'YCSW0045 Index',
'AUD': 'YCSW0001 Index'
}
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(
bbg_ticker[currency], ['CURVE_TENOR_RATES'],
ignore_security_error=1,
ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
data_retrieved = response.as_frame()['CURVE_TENOR_RATES'][0]
swap_curve = pd.DataFrame(data=[
data_retrieved['Ask Yield'], data_retrieved['Mid Yield'],
data_retrieved['Bid Yield']
]).transpose()
swap_curve.index = [
1, 90, 180, 270, 360, 18 * 30, 2 * 360, 3 * 360, 4 * 360, 5 * 360,
6 * 360, 7 * 360, 8 * 360, 9 * 360, 10 * 360, 15 * 360, 20 * 360
]
sc = pd.DataFrame(data=swap_curve, index=np.arange(1, 20 * 360))
sc = sc.interpolate(method='linear')
return sc
def getLocalCurveInstrumentsFromBBG(self, country='CL'):
countryDict = {'CL': 'YCSW0193 Index'}
bbg_tickers = [countryDict['CL']]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(
bbg_tickers, ['curve_tenor_rates'],
ignore_security_error=1,
ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
# 'DAYS_TO_MTY_TDY'
curve_instruments = pd.DataFrame(
response.as_frame()['curve_tenor_rates'].values[0])
curve = pd.DataFrame(curve_instruments['Mid Yield'][1:6])
curve.columns = ['Swap Rate']
# curve.index = curve_instruments.Tenor[1:]
curve.index = [
(dt.datetime.today() +
dt.timedelta(days=(LocalTerminal.get_reference_data(
curve_instruments['Tenor Ticker'][j], ['DAYS_TO_MTY_TDY'],
ignore_security_error=1,
ignore_field_error=1).as_frame()['DAYS_TO_MTY_TDY'][0]
).astype(np.int32)))
for j in range(1,
len(curve) + 1)
]
return curve
def getDataBBGHistorical(self,
start=None,
end=None,
window=None,
period='DAILY',
identifier=None):
#Función que descarga información de Bloomberg
ticker = self.getFXTickerFromDB(identifier=identifier)
response = None
if not start:
start = pd.datetools.BDay(-window).apply(pd.datetime.now()).date()
if not end:
end = pd.datetime.now().date()
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(ticker, ['px_last'],
ignore_security_error=1,
ignore_field_error=1,
period=period,
start=start,
end=end)
except:
print("Unexpected error:", sys.exc_info()[0])
# print response.as_map()
# print response.as_frame()
if response:
data = response.as_map()
return data
return False
def get_swap_curves(currencies):
# bbg_tickers = ['YCSW0045 Index','YCSW0023 Index','YCSW0013 Index','YCSW0001 Index','YCSW0234 Index', 'YCSW0015 Index', 'YCSW0004 Index', 'YCSW0020 Index']
# bbg_currencies = ['EUR', 'USD', 'JPY', 'AUD','CHF','NZD','CAD', 'SEK']
# bbg_tickers = ['YCSW0193 Index','YCSW0329 Index','YCMM0119 Index','YCSW0083 Index','YCSW0023 Index', 'YCSW0013 Index', 'YCSW0045 Index', 'YCSW0001 Index']
# bbg_currencies = ['CLP','COP','BRL','MXN','USD','JPY','EUR','AUD']
bbg_tickers = ['YCSW0193 Index', 'YCSW0023 Index']
bbg_currencies = ['CLP', 'USD']
BBG_queries_total = pd.Series(bbg_tickers, index=bbg_currencies)
BBG_queries = BBG_queries_total[currencies]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(
BBG_queries.values, ['curve_tenor_rates', 'crncy'],
ignore_security_error=1,
ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
# print response.as_map()
# print response.as_frame()
data_retrieved = response.as_frame()
newData = data_retrieved['curve_tenor_rates'].apply(
lambda x: x.apply(generate_tenor, axis=1))
newData = pd.concat([newData, data_retrieved['crncy']], axis=1)
newData[['LINEAL_BID', 'LINEAL_ASK', 'LINEAL_MID'
]] = newData['curve_tenor_rates'].apply(generate_lineal_yc)
return newData
def getPriceDurationVolatilityFromBBG(self,instruments):
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(instruments, ['YLD_YTM_MID', 'DUR_ADJ_MID', 'VOLATILITY_260D', 'DAYS_TO_MTY'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
return response.as_frame()
def getTPM(self, country='CL'):
countryDict = {'CL': 'CHOVCHOV Index'}
bbg_tickers = [countryDict['CL']]
LocalTerminal = v3api.Terminal('localhost', 8194)
response = LocalTerminal.get_reference_data(bbg_tickers, ['PX_LAST'],
ignore_security_error=1,
ignore_field_error=1)
return response.as_frame().values[0][0]
def histSpread(self,
dateInitial='2015-10-02',
dateFinal=dt.datetime.today()):
cl = [
'S0193Z 1M BLC2 Curncy', 'S0193Z 2M BLC2 Curncy',
'S0193Z 3M BLC2 Curncy', 'S0193Z 6M BLC2 Curncy',
'S0193Z 1Y BLC2 Curncy', 'S0193Z 2Y BLC2 Curncy',
'S0193Z 3Y BLC2 Curncy', 'S0193Z 4Y BLC2 Curncy',
'S0193Z 5Y BLC2 Curncy'
]
us = [
'US0001M Index', 'US0002M Index', 'G0052Z 3M BLC2 Curncy',
'G0052Z 6M BLC2 Curncy', 'G0052Z 1Y BLC2 Curncy',
'G0052Z 2Y BLC2 Curncy', 'G0052Z 3Y BLC2 Curncy',
'G0052Z 4Y BLC2 Curncy', 'G0052Z 5Y BLC2 Curncy'
]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response_cl = LocalTerminal.get_historical(cl, ['PX_ASK'],
ignore_security_error=1,
ignore_field_error=1,
start=dateInitial,
end=dateFinal)
response_us = LocalTerminal.get_historical(us, ['PX_LAST'],
ignore_security_error=1,
ignore_field_error=1,
start=dateInitial,
end=dateFinal)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
rates_cl = response_cl.as_frame()
rates_cl.columns = rates_cl.columns.levels[0]
rates_cl = self.set_column_sequence(rates_cl, [
'S0193Z 1M BLC2 Curncy', 'S0193Z 2M BLC2 Curncy',
'S0193Z 3M BLC2 Curncy', 'S0193Z 6M BLC2 Curncy',
'S0193Z 1Y BLC2 Curncy', 'S0193Z 2Y BLC2 Curncy',
'S0193Z 3Y BLC2 Curncy', 'S0193Z 4Y BLC2 Curncy',
'S0193Z 5Y BLC2 Curncy'
])
rates_cl.columns = [
'1M', '2M', '3M', '6M', '1Y', '2Y', '3Y', '4Y', '5Y'
]
rates_us = response_us.as_frame()
rates_us.columns = rates_us.columns.levels[0]
rates_us = self.set_column_sequence(rates_us, [
'US0001M Index', 'US0002M Index', 'G0052Z 3M BLC2 Curncy',
'G0052Z 6M BLC2 Curncy', 'G0052Z 1Y BLC2 Curncy',
'G0052Z 2Y BLC2 Curncy', 'G0052Z 3Y BLC2 Curncy',
'G0052Z 4Y BLC2 Curncy', 'G0052Z 5Y BLC2 Curncy'
])
rates_us.columns = [
'1M', '2M', '3M', '6M', '1Y', '2Y', '3Y', '4Y', '5Y'
]
spread = pd.DataFrame(rates_cl - rates_us)
spread = spread.interpolate()
return spread, rates_cl, rates_us
def histForwardReturn(self,
currency_short,
currency_long,
dateInitial='2015-10-02',
dateFinal=dt.datetime.today()):
cross = currency_short + currency_long
if cross == 'USDCLP' or cross == 'CLPUSD':
tickers = [
'CHN1M LAST Curncy', 'CHN2M LAST Curncy', 'CHN3M LAST Curncy',
'CHN6M LAST Curncy', 'CHN12M LAST Curncy',
'CHN18M LAST Curncy', 'CHN2Y LAST Curncy', 'CHN3Y LAST Curncy',
'CHN5Y LAST Curncy'
]
ccy_ticker = ['USDCLP Curncy']
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(tickers, ['PX_BID'],
ignore_security_error=1,
ignore_field_error=1,
start=dateInitial,
end=dateFinal)
ccy_response = LocalTerminal.get_historical(
ccy_ticker, ['PX_MID'],
ignore_security_error=1,
ignore_field_error=1,
start=dateInitial,
end=dateFinal)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
fx_spot = ccy_response.as_frame()
fx_spot.columns = [cross]
fwd_points = response.as_frame()
fwd_points.columns = fwd_points.columns.levels[0]
fwd_points = self.set_column_sequence(fwd_points, [
'CHN1M LAST Curncy', 'CHN2M LAST Curncy', 'CHN3M LAST Curncy',
'CHN6M LAST Curncy', 'CHN12M LAST Curncy', 'CHN18M LAST Curncy',
'CHN2Y LAST Curncy', 'CHN3Y LAST Curncy', 'CHN5Y LAST Curncy'
])
fwd_spot_matrix = pd.DataFrame([fx_spot[cross].values] * 9).transpose()
fwd_spot_matrix.columns = fwd_points.columns
fwd_spot_matrix.index = fx_spot.index
fwd_points.reindex(index=fwd_spot_matrix.index)
days = [30, 60, 90, 180, 360, 18 * 30, 24 * 30, 36 * 30, 60 * 30]
fwd_days_matrix = pd.DataFrame(np.tile(days, (len(fwd_points), 1)))
fwd_days_matrix.columns = fwd_points.columns
fwd_days_matrix.index = fwd_points.index
fwd_return = fwd_points / fwd_spot_matrix * 360 / fwd_days_matrix
return fwd_return
def getInflationYoY(self,init='2015-02-03',end='2016-10-11'):
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(['CNPINSYO Index'], ['PX_LAST'], ignore_security_error = 1, ignore_field_error = 1, start = init, end = end)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
inflation_historical_yoy = response.as_frame()['GTCOP10Y Govt']
return inflation_historical_yoy
def getHistoricalInflation(self, dateInitial, dateFinal):
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(['CNPINSMO Index'], ['PX_LAST'], ignore_security_error = 1, ignore_field_error = 1, start = dateInitial, end = dateFinal)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
inflation_historical = response.as_frame()['CNPINSMO Index']
return inflation_historical
def getBondCashFlows(self, instrument):
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(instrument, ['DES_CASH_FLOW_ADJ'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
cash_flows = pd.DataFrame(response.as_frame()['DES_CASH_FLOW_ADJ'].values[0])
cash_flows['TCF'] = cash_flows['Interest']+cash_flows['Principal']
del cash_flows['Interest']
del cash_flows['Principal']
return cash_flows
def loadBBGDataCharacteristics(self,identifiers):
# d = pd.datetime.today()
flds = ['ID_BB_SEC_NUM_DES','CRNCY','LONG_COMP_NAME']
#Descarga información sobre los indices desde Bloomberg
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(identifiers, flds, ignore_security_error=1, ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
newData = response.as_frame()
return newData
def loadBBGDataHistoricalPrices(self,identifiers,dateInitial,dateFinal):
# d = pd.datetime.today()
flds = ['PX_LAST']
#Descarga información sobre los indices desde Bloomberg
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(identifiers, flds, ignore_security_error=1, ignore_field_error=1, start = dateInitial, end = dateFinal)
except:
print("Unexpected error:", sys.exc_info()[0])
priceData = response.as_frame().interpolate()
newPrice = priceData.stack()
newPrice.index = priceData.index
return newPrice
def getLocalCurveInstrumentsFromBBG(self, country = 'CL'):
countryDict = {'CL': 'YCGT0351 Index', 'CLi': 'YCGT0362 Index'}
bbg_tickers = [countryDict[country]]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(bbg_tickers, ['curve_tenor_rates'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
curve_instruments = pd.DataFrame(response.as_frame()['curve_tenor_rates'].values[0])
return curve_instruments
def getLocalCurveInstrumentsFromBBG(self, country = 'CL'):
countryDict = {'CL': 'YCFC1FN9 Index'}
bbg_tickers = [countryDict['CL']]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(bbg_tickers, ['CURVE_TENOR_RATES'], ignore_security_error = 1, ignore_field_error = 1)
print response.as_frame()['CURVE_TENOR_RATES'].values[0]
except:
print("Unexpected error:", sys.exc_info()[0])
return False
curve_instruments = pd.DataFrame(response.as_frame()['CURVE_TENOR_RATES'].values[0])
return curve_instruments
def getLocalCurveInstrumentsFromUser(self):
ticker = ['EI964335 Corp', 'EG316074 Corp', 'EJ237864 Corp', 'EI553642 Corp', 'EJ591386 Corp', 'EH381904 Corp', 'EJ716536 Corp','JK818218 Corp',
'EI120454 Corp', 'EK936444 Corp', 'AF217465 Corp', 'EI577823 Corp', 'JK950287 Corp', 'EJ041155 Corp','EJ111061 Corp', 'EJ591441 Corp',
'EK274744 Corp', 'EK877544 Corp', 'EJ041159 Corp', 'EK274762 Corp', 'EK985952 Corp', 'EJ529959 Corp']
tenor = ['0,18Y','0,34Y','0,59Y','1,17Y','1,34Y','1,50Y','1,57Y','2,17Y','3,17Y','3,59Y','3,67Y','4,26Y','4,34Y','5,17Y','5,34Y',
'6,33Y','7,17Y','9,34Y','15,17Y','17,17Y','18,33Y','26,17Y']
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(ticker, ['SECURITY_NAME'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
name = [x for x in response.as_frame()['SECURITY_NAME']]
out = pd.DataFrame(data = [ticker, tenor, name], index=['Tenor Ticker','Tenor', 'Name']).transpose()
return out
def plotTimeSeriesUSDshortRate(self, timeSeries):
months = mdates.MonthLocator() # every month
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(
['US0003M Index'], ['PX_LAST'],
ignore_security_error=1,
ignore_field_error=1,
start=timeSeries.index.min(),
end=timeSeries.index.max())
except:
print("Unexpected error:", sys.exc_info()[0])
return False
libor = response.as_frame()
del timeSeries['1Y']
del timeSeries['2Y']
del timeSeries['3Y']
del timeSeries['4Y']
del timeSeries['5Y']
timeSeries['LIBOR 3M'] = libor.interpolate(method='linear')
f, axarr = plt.subplots(1, sharex=True)
plt.style.use('bmh')
f.set_size_inches(12, 8)
axarr.plot(timeSeries.index, timeSeries.loc[:, '1M':'LIBOR 3M'])
axarr.set_xlim(0, 310)
axarr.set_title('USD On-Shore Synthetic Rate', fontsize=16)
axarr.legend(['1M', '2M', '3M', '6M', '3M LIBOR USD'], loc=4)
axarr.set_ylabel('Rate (%)', fontsize=14)
axarr.xaxis.set_minor_locator(months)
datemin = datetime.date(timeSeries.index.min().year,
timeSeries.index.min().month,
timeSeries.index.min().day)
datemax = datetime.date(timeSeries.index.max().year,
timeSeries.index.min().month + 2,
timeSeries.index.min().day)
axarr.set_xlim(datemin, datemax)
plt.show()
return True
def getLocalCLFCurveInstrumentsFromUser(self):
ticker = ['BCU0300318 Govt','BCU0300718 Govt' ,'BCU0300818 Govt','BCU0301018 Govt','BTU0300119 Govt','BCU0300519 Govt','BTU0300719 Govt','BTU0300120 Govt',
'BCU0300221 Govt','BTU0150321 Govt','BTU0300122 Govt','BCU0300322 Govt','BCU0500922 Govt','BCU0300323 Govt','BTU0451023 Govt','BTU0300124 Govt','BTU0450824 Govt',
'BTU0260925 Govt','BTU0150326 Govt','BTU0300327 Govt','BTU0300328 Govt','BCU0300528 Govt','BTU0300329 Govt','BTU0300130 Govt','BCU0300231 Govt','BTU0300132 Govt',
'BTU0300134 Govt','BTU0200335 Govt','BTU0300338 Govt','BTU0300339 Govt','BTU0300140 Govt','BCU0300241 Govt','BTU0300142 Govt','BTU0300144 Govt']
tenor = ['1,47Y','1,80Y','1,88Y','2,02Y','2,27Y','2,57Y','2,72Y','3,19Y','4,16Y','4,32Y','4,95Y','5,05Y','5,23Y','5,88Y','6,17Y','6,61Y','6,82Y','7,99Y',
'8,75Y','9,03Y','9,76Y','9,91Y','10,48Y','11,13Y','11,89Y','12,51Y','13,82Y','15,33Y','16,29Y','16,86Y','17,43Y','18,09Y','18,57Y','19,60Y']
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(ticker, ['SECURITY_NAME'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
name = [x for x in response.as_frame()['SECURITY_NAME']]
out = pd.DataFrame(data = [ticker, tenor, name], index=['Tenor Ticker','Tenor', 'Name']).transpose()
return out
def getHistDataFromBloomberg(
self,
tickers,
init=dt.datetime.today() - dt.timedelta(weeks=104),
end=dt.datetime.today() - dt.timedelta(days=1)):
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_historical(tickers, ['PX_LAST'],
ignore_security_error=1,
ignore_field_error=1,
start=init,
end=end)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
bloombergData = response.as_frame()
return bloombergData
def getInflationExpectations(self):
tickers = ['CFNP1 Curncy', 'CFNP2 Curncy', 'CFNP3 Curncy',
'CFNP4 Curncy', 'CFNP5 Curncy', 'CFNP6 Curncy',
'CFNP7 Curncy', 'CFNP8 Curncy', 'CFNP9 Curncy',
'CFNP10 Curncy', 'CFNP11 Curncy', 'CFNP12 Curncy',
'CFNP13 Curncy', 'CFNP14 Curncy', 'CFNP15 Curncy',
'CFNP16 Curncy', 'CFNP17 Curncy']
# Descarga de datos para los forwards de CLF
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response = LocalTerminal.get_reference_data(tickers, ['SETTLE_DT','PX_LAST'], ignore_security_error = 1, ignore_field_error = 1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
inflation_expectations = response.as_frame()
# Descarga y agrega el dato del ultimo valor de CLF al DataFrame
response = LocalTerminal.get_reference_data('CHUF Index', ['LAST_UPDATE_DT','PX_LAST'], ignore_security_error = 1, ignore_field_error = 1)
uf = response.as_frame()
uf.columns = ['SETTLE_DT','PX_LAST']
inflation_expectations = inflation_expectations.append(uf).sort_values('SETTLE_DT')
# Agrega la lista de 9 de mes para cada forward
inflation_expectations['ACTUAL_DAYS'] = 1
inflation_expectations['Forecast CPI'] = 0
original_index = inflation_expectations.index
inflation_expectations.index = range(0,len(inflation_expectations))
for x in range(1,len(inflation_expectations)):
date_nine = inflation_expectations['SETTLE_DT'].loc[x-1].to_datetime()+datetime.timedelta(days=-(inflation_expectations['SETTLE_DT'].loc[x-1].day-9))
# number_of_days_between = int((inflation_expectations['NINETH_DAY'][1]-inflation_expectations['SETTLE_DT'][0]).days)
number_of_days_between = int((inflation_expectations['SETTLE_DT'].loc[x]-date_nine).days)
days_in_month = calendar.monthrange(inflation_expectations['SETTLE_DT'].loc[x-1].to_datetime().year, inflation_expectations['SETTLE_DT'].loc[x-1].to_datetime().month)[1]
inflation_expectations['ACTUAL_DAYS'].loc[x] = float(days_in_month)/number_of_days_between
inflation_expectations['Forecast CPI'].loc[x] = ((float(inflation_expectations['PX_LAST'].loc[x])/inflation_expectations['PX_LAST'].loc[x-1])**inflation_expectations['ACTUAL_DAYS'].loc[x]-1)*100
inflation_expectations.index = original_index
return inflation_expectations
def get_zero_swaps(self):
self.chile = [
'S0193Z 2D BLC2 Curncy', 'S0193Z 1W BLC2 Curncy',
'S0193Z 2W BLC2 Curncy', 'S0193Z 1M BLC2 Curncy',
'S0193Z 2M BLC2 Curncy', 'S0193Z 3M BLC2 Curncy',
'S0193Z 4M BLC2 Curncy', 'S0193Z 5M BLC2 Curncy',
'S0193Z 6M BLC2 Curncy', 'S0193Z 7M BLC2 Curncy',
'S0193Z 8M BLC2 Curncy', 'S0193Z 9M BLC2 Curncy',
'S0193Z 10M BLC2 Curncy', 'S0193Z 11M BLC2 Curncy',
'S0193Z 1Y BLC2 Curncy', 'S0193Z 15M BLC2 Curncy',
'S0193Z 18M BLC2 Curncy', 'S0193Z 21M BLC2 Curncy',
'S0193Z 2Y BLC2 Curncy', 'S0193Z 33M BLC2 Curncy',
'S0193Z 3Y BLC2 Curncy', 'S0193Z 4Y BLC2 Curncy',
'S0193Z 5Y BLC2 Curncy', 'S0193Z 6Y BLC2 Curncy',
'S0193Z 7Y BLC2 Curncy', 'S0193Z 8Y BLC2 Curncy',
'S0193Z 9Y BLC2 Curncy', 'S0193Z 10Y BLC2 Curncy',
'S0193Z 15Y BLC2 Curncy'
]
self.us = [
'USDR2T Curncy', 'US0001W Index', 'US0001M Index',
'US0002M Index', 'G0052Z 3M BLC2 Curncy', 'G0052Z 6M BLC2 Curncy',
'G0052Z 1Y BLC2 Curncy', 'G0052Z 2Y BLC2 Curncy',
'G0052Z 3Y BLC2 Curncy', 'G0052Z 4Y BLC2 Curncy',
'G0052Z 5Y BLC2 Curncy', 'G0052Z 6Y BLC2 Curncy',
'G0052Z 7Y BLC2 Curncy', 'G0052Z 8Y BLC2 Curncy',
'G0052Z 9Y BLC2 Curncy', 'G0052Z 10Y BLC2 Curncy',
'G0052Z 15Y BLC2 Curncy'
]
LocalTerminal = v3api.Terminal('localhost', 8194)
try:
response_cl = LocalTerminal.get_reference_data(
self.chile, ['PX_ASK'],
ignore_security_error=1,
ignore_field_error=1)
response_us = LocalTerminal.get_reference_data(
self.us, ['PX_BID'],
ignore_security_error=1,
ignore_field_error=1)
except:
print("Unexpected error:", sys.exc_info()[0])
return False
data_cl = response_cl.as_frame()
chile = pd.DataFrame(data_cl['PX_ASK'])
chile.columns = ['Swap Spread']
chile['days'] = [
10 * 30, 10 * 12 * 30, 11 * 30, 15 * 30, 15 * 12 * 30, 18 * 30,
1 * 30, 7, 1 * 12 * 30, 21 * 30, 2, 2 * 30, 14, 2 * 12 * 30,
33 * 30, 3 * 30, 3 * 12 * 30, 4 * 30, 4 * 12 * 30, 5 * 30,
5 * 12 * 30, 6 * 30, 6 * 12 * 30, 7 * 30, 7 * 12 * 30, 8 * 30,
8 * 12 * 30, 9 * 30, 9 * 12 * 30
]
chile.index = chile['days']
chile = chile.sort_values(['days'])
del chile['days']
data_us = response_us.as_frame()
usa = pd.DataFrame(data_us['PX_BID'])
usa.columns = ['Swap Spread']
usa['days'] = [
10 * 12 * 30, 15 * 12 * 30, 1 * 12 * 30, 2 * 12 * 30, 3 * 30,
3 * 12 * 30, 4 * 12 * 30, 5 * 12 * 30, 6 * 30, 6 * 12 * 30,
7 * 12 * 30, 8 * 12 * 30, 9 * 12 * 30, 1 * 30, 7, 2 * 30, 2
]
usa.index = usa['days']
usa = usa.sort_values(['days'])
del usa['days']
swap_spreads = chile - usa
swap_spreads = swap_spreads.reindex(
index=np.arange(min(swap_spreads.index),
max(swap_spreads.index) + 1))
swap_spreads = swap_spreads.interpolate()
usa = usa.reindex(index=np.arange(min(usa.index), max(usa.index) + 1))
usa = usa.interpolate()
chile = chile.reindex(index=np.arange(min(chile.index),
max(chile.index) + 1))
chile = chile.interpolate()
return swap_spreads, chile, usa
def createCLFpath(self, init='2015-12-01', end=dt.datetime.today()):
iforecast = self.getInflationforecast()
historical = outlook.outlook().getHistoricalInflation(init, end)
historical.columns = ['Dove']
historical['Base'] = historical['Dove']
historical['Hawk'] = historical['Dove']
historical['Average'] = historical['Dove']
expected = outlook.outlook().getInflationExpectations()
forecasted = pd.DataFrame(data = expected['Forecast CPI'][1:])
forecasted.columns = ['PX_LAST']
forecasted.index = [expected['SETTLE_DT'][x+1] - relativedelta(months=2) for x in range(0,len(forecasted))]
forecasted['year'] = [forecasted.index[i].to_datetime().year for i in range(0,len(forecasted))]
forecast_adj = pd.DataFrame(columns = ['Dove','Base','Hawk','Average'], index = forecasted.index)
forecast_adj['Dove'][forecasted.index.to_datetime().year == 2016] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016] + (iforecast['Dove'][0]-historical['Dove'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016]) # Esto se tiene que ajustar cada anio para que funcione correctamente, o mejorar el codigo
forecast_adj['Dove'][forecasted.index.to_datetime().year == 2017] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017] + (iforecast['Dove'][1]-historical['Dove'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017])
forecast_adj['Base'][forecasted.index.to_datetime().year == 2016] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016] + (iforecast['Base'][0]-historical['Base'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016])
forecast_adj['Base'][forecasted.index.to_datetime().year == 2017] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017] + (iforecast['Base'][1]-historical['Base'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017])
forecast_adj['Hawk'][forecasted.index.to_datetime().year == 2016] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016] + (iforecast['Hawk'][0]-historical['Hawk'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016])
forecast_adj['Hawk'][forecasted.index.to_datetime().year == 2017] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017] + (iforecast['Hawk'][1]-historical['Hawk'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017])
forecast_adj['Average'][forecasted.index.to_datetime().year == 2016] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016] + (iforecast['Average'][0]-historical['Average'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2016])
forecast_adj['Average'][forecasted.index.to_datetime().year == 2017] = forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017] + (iforecast['Average'][1]-historical['Average'].sum()-forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017].sum())/len(forecasted['PX_LAST'][forecasted.index.to_datetime().year == 2017])
inflation = historical.append(forecast_adj)
days, dates, short_rate, factor, dF, prob, Z = self.createRatesTable()
fechas = self.dateCount(inflation.index[-1],dates[-1],day_of_month = 9)
inflation_long = pd.DataFrame(columns = forecast_adj.columns, index=fechas[1:])
inflation_long['Dove'] = iforecast['Dove'][2]/12
inflation_long['Base'] = iforecast['Base'][2]/12
inflation_long['Hawk'] = iforecast['Hawk'][2]/12
inflation_long['Average'] = iforecast['Average'][2]/12
inflation = inflation.append(inflation_long)
clf = pd.DataFrame(columns = forecast_adj.columns, index=inflation.index)
x = 0
for i in range(0,len(clf)):
if clf['Dove'].index[i] < dt.datetime.today():
x += 1
clf['Dove'].iloc[i] = self.getUF(inflation.index[i])
clf['Base'] = clf['Dove']
clf['Hawk'] = clf['Dove']
clf['Average'] = clf['Dove']
clf['Dove'][x:] = (1+inflation['Dove'][x-2:-2]/100).cumprod()*clf['Dove'][x-1]
clf['Base'][x:] = (1+inflation['Base'][x-2:-2]/100).cumprod()*clf['Base'][x-1]
clf['Hawk'][x:] = (1+inflation['Hawk'][x-2:-2]/100).cumprod()*clf['Hawk'][x-1]
clf['Average'][x:] = (1+inflation['Average'][x-2:-2]/100).cumprod()*clf['Average'][x-1]
clf_long = pd.DataFrame(data = clf, index = dF.index, columns = clf.columns)
LocalTerminal = v3api.Terminal('localhost', 8194)
response = LocalTerminal.get_historical(['CLF Curncy'], ['PX_LAST'], ignore_security_error=1, ignore_field_error=1, start = clf_long.index[0].to_datetime()-dt.timedelta(days=1), end = clf_long.index[0].to_datetime()-dt.timedelta(days=1))
clf_long.iloc[0] = response.as_frame().values
for col in clf_long:
clf_long[col] = pd.to_numeric(clf_long[col], errors='coerce')
clf_long = clf_long.interpolate(method='linear')
return inflation, clf_long, Z, days, dates
def getUF(self,start=dt.datetime.today()):
LocalTerminal = v3api.Terminal('localhost', 8194)
self.CLFCLP = LocalTerminal.get_historical(['CLF Curncy'], ['PX_LAST'], start - dt.timedelta(days=1)).as_frame()['CLF Curncy']['PX_LAST'][0]
return self.CLFCLP
def refreh_sql():
yesterday = fs.get_prev_weekday(str(dt.datetime.now().date()))
funds = utiles.get_FI_funds()
df_bonos = utiles.get_updated_RFI(funds)
df_depositos = utiles.get_updated_IIF(funds)
#query_f = "SELECT Codigo_Fdo FROM [MesaInversiones].[dbo].[FondosIR]"
#df_fondos = fs.get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1", "usuario1", query_f)
#query_c = "SELECT Codigo_Fdo FROM [MesaInversiones].[dbo].[Perfil Clientes]"
#df_carteras = fs.get_frame_sql_user("Puyehue", "MesaInversiones", "usuario1", "usuario1", query_c)
#fondos = df_fondos['Codigo_Fdo'].values.tolist()
#carteras = df_carteras['Codigo_Fdo'].values.tolist()
#df_f = df.loc[(df['codigo_fdo'].isin(fondos))]
#df_c = df.loc[(df['codigo_fdo'].isin(carteras))]
query_cupones = "SELECT * FROM [MesaInversiones].[dbo].[RF_Cupones]"
df_cupones = fs.get_frame_sql_user("Puyehue", "MesaInversiones",
"usuario1", "usuario1", query_cupones)
cupones = df_cupones['Instrumento'].values.tolist()
print('----------------cupones----------------')
print(len(cupones))
df_emisor = fs.get_frame_sql_user(
"Puyehue", "MesaInversiones", "usuario1", "usuario1",
"SELECT RTRIM(LTRIM([Emisor])) AS [Emisor], [Codigo_SVS] FROM EmisoresIIF"
)
set_bonos = {
row[1]['codigo_ins'].strip() + ' Corp'
for row in df_bonos.iterrows()
}
print(set_bonos)
set_depositos = {
nemo_depo(row[1]['codigo_emi'].strip(), row[1]['moneda'].strip(),
row[1]['fec_vcto'].strip(), df_emisor) + ' M-Mkt':
[row[1]['fec_vcto'], row[1]['moneda']]
for row in df_depositos.iterrows()
}
LocalTerminal = v3api.Terminal('localhost', 8194)
fails = []
for instrumento in set_bonos:
if instrumento[:-5] not in cupones:
try:
final_row = []
response = LocalTerminal.get_reference_data(
instrumento, [
'TRADE_CRNCY', 'NEXT_CASH_FLOW_DT', 'NEXT_CASH_FLOW',
'MATURITY'
])
bloombergData = response.as_frame().replace('nan', 0)
final_row.append(instrumento[:-5])
final_row.append(bloombergData.iloc[0]['TRADE_CRNCY'])
final_row.append(
timestamp_to_string(
bloombergData.iloc[0]['NEXT_CASH_FLOW_DT']))
final_row.append(bloombergData.iloc[0]['NEXT_CASH_FLOW'])
final_row.append(
timestamp_to_string(bloombergData.iloc[0]['MATURITY']))
df_cupones = df_cupones.append(pd.DataFrame(
[final_row],
columns=[
'Instrumento', 'moneda', 'Fecha_corte',
'Cantidad_corte', 'Vencimiento'
]),
ignore_index=True)
except Exception as e:
print(e)
print(instrumento)
fails.append(instrumento)
for instrumento in set_depositos:
if instrumento[:-6] not in cupones:
try:
final_row = []
final_row.append(instrumento[:-6])
moneda = set_depositos[instrumento][1].strip()
if moneda == '$':
moneda = 'CLP'
elif moneda == 'US$':
moneda = 'USD'
elif moneda == 'UF':
moneda = 'CLF'
final_row.append(moneda)
final_row.append(None)
final_row.append(1000000)
final_row.append(set_depositos[instrumento][0])
df_cupones = df_cupones.append(pd.DataFrame(
[final_row],
columns=[
'Instrumento', 'moneda', 'Fecha_corte',
'Cantidad_corte', 'Vencimiento'
]),
ignore_index=True)
except Exception as e:
print(e)
print(instrumento)
fails.append(instrumento)
insert_into_database(df_cupones)
print('----------------fails----------------')
print(len(fails))
