def __init__(self, bbg_ticker, price_field='PX_LAST'):
"""
Returns an object with the following attributes:
- ticker: str with bloomberg ticker for the stock
- dividends: DataFrame with dividend information. Its columns are 'Declared Date', 'Amount', 'Frequency',
'Type', 'Ex-Dividend Date', 'Payable Date', 'Record Date'
- price: Series with the stock price
- tr_index: Series with the total return index
- quantity: amount of stocks held
- ts_df: DataFrame with columns 'Price', 'Dividend', 'Quantity' and 'Total Return Index'
:param bbg_ticker: str, Bloomberg ticker of the stock
:param price_field: Price field to be used as settlement price
"""
bbg = BBG()
self.bbg_ticker = bbg_ticker
today = pd.to_datetime('today')
self.dividends = self._get_dividends(today, bbg)
start_date_div = self.dividends['Ex-Dividend Date'].min()
start_date_prc = pd.to_datetime(bbg.fetch_contract_parameter(self.bbg_ticker, 'CALENDAR_START_DATE').values[0][0])
# Metadata to be saved
self.country = bbg.fetch_contract_parameter(self.bbg_ticker, 'COUNTRY_ISO').iloc[0, 0].upper()
self.exchange_symbol = bbg.fetch_contract_parameter(self.bbg_ticker, 'ID_EXCH_SYMBOL').iloc[0, 0]
self.fh_ticker = 'eqs ' + self.country.lower() + ' ' + self.exchange_symbol.lower()
self.asset_class = 'equity'
self.type = 'stock'
self.currency = bbg.fetch_contract_parameter(self.bbg_ticker, 'CRNCY').iloc[0, 0].upper()
self.sector = bbg.fetch_contract_parameter(self.bbg_ticker, 'INDUSTRY_SECTOR').iloc[0, 0].lower()
self.group = bbg.fetch_contract_parameter(self.bbg_ticker, 'INDUSTRY_GROUP').iloc[0, 0].lower()
self.df_metadata = pd.DataFrame(data={'fh_ticker': self.fh_ticker,
'asset_class': self.asset_class,
'type': self.type,
'exchange_symbol': self.exchange_symbol,
'currency': self.currency,
'country': self.country,
'sector': self.sector,
'group': self.group},
index=[self.bbg_ticker])
if isinstance(start_date_div, NaTType):
start_date = start_date_prc
else:
start_date = min(start_date_div, start_date_prc)
self.price = bbg.fetch_series(securities=bbg_ticker, fields=price_field, startdate=start_date, enddate=today)
df = self._get_total_return_index()
self.price = df[self.bbg_ticker].rename('Price')
df['Price'] = self.price
self.tr_index = df['Total Return Index'].rename('TR Index')
self.quantity = df['Quantity'].rename('Quantity')
self.df_ts = df[['Price', 'Dividend', 'Quantity', 'Total Return Index']]
self.df_tracker = self._get_tracker_melted()
def _grab_bbg_data(self):
bbg = BBG()
self.contract_list = bbg.fetch_futures_list(
generic_ticker=self.comm_bbg_code + '1 Comdty')
self.first_notice_dates = bbg.fetch_contract_parameter(
securities=self.contract_list,
field='FUT_NOTICE_FIRST').sort_values('FUT_NOTICE_FIRST')
# Grab all contract series
df_prices = bbg.fetch_series(securities=self.contract_list,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
self.prices = df_prices.fillna(method='ffill')
def __init__(self, ticker, price_field='PX_LAST'):
"""
Returns an object with the following atributes:
- ticker: str with bloomberg ticker for the stock
- dividends: DataFrame with dividend information. Its columns are 'Declared Date', 'Amount', 'Frequency',
'Type', 'Ex-Dividend Date', 'Payable Date', 'Record Date'
- price: Series with the stock price
- tr_index: Series with the total return index
- quantity: amount of stocks held
- ts_df: DataFrame with columns 'Price', 'Dividend', 'Quantity' and 'Total Return Index'
:param ticker: str, Bloomberg ticker of the stock
:param price_field: Price field to be used as settlement price
"""
# TODO allow user to choose currency
bbg = BBG()
self.ticker = ticker
today = pd.to_datetime('today')
self.dividends = self._get_dividends(today, bbg)
start_date_div = self.dividends['Ex-Dividend Date'].min()
start_date_prc = pd.to_datetime(
bbg.fetch_contract_parameter(self.ticker,
'CALENDAR_START_DATE').values[0][0])
if isinstance(start_date_div, NaTType):
start_date = start_date_prc
else:
start_date = min(start_date_div, start_date_prc)
self.price = bbg.fetch_series(securities=ticker,
fields=price_field,
startdate=start_date,
enddate=today)
df = self._get_total_return_index()
self.price = df[self.ticker].rename('Price')
df['Price'] = self.price
self.tr_index = df['Total Return Index'].rename('TR Index')
self.quantity = df['Quantity'].rename('Quantity')
self.ts_df = df[[
'Price', 'Dividend', 'Quantity', 'Total Return Index'
]]
from bloomberg import BBG
import matplotlib.pyplot as plt
from portfolio.backtesting_functions import FHSignalBasedWeights
import pandas as pd
import numpy as np
start_date = '30-mar-2000'
end_date = pd.to_datetime('today')
bbg = BBG()
# Grabs tickers and fields
df = bbg.fetch_series(securities='SPVXMP Index',
fields='PX_LAST',
startdate=start_date,
enddate=end_date)
df2 = bbg.fetch_series(securities='SPUSSOUT Index',
fields='PX_LAST',
startdate=start_date,
enddate=end_date)
df3 = bbg.fetch_series(securities='SPDAUDT Index',
fields='PX_LAST',
startdate=start_date,
enddate=end_date)
df4 = bbg.fetch_series(securities='BXIICSTU Index',
fields='PX_LAST',
startdate=start_date,
enddate=end_date)
from bloomberg import BBG
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
from calendars import DayCounts
dc = DayCounts(dc='bus/252', calendar='anbima')
start_date = "28-aug-1997"
end_date = '28-jun-2019'
bbg = BBG()
try:
df2 = bbg.fetch_series(securities=['LLU99 Comdty', 'LLV99 Comdty'],
fields=['LAST_PRICE'],
startdate=start_date,
enddate=end_date)
except KeyError as e:
pass
d_c1 = df2['LLU99 Comdty'].dropna().index[-1][1].to_pydatetime()
d_c2 = df2.index[-1][1].to_pydatetime()
res = dc.days(pd.to_datetime(start_date), d_c1)
print(res)
res = dc.days(pd.to_datetime(start_date), d_c2)
print(res)
last_day = dc.eom_preceding(date).date().day
start_date = str(last_day) + '-' + meses[-1] + '-' + str(
year_in_use - 1)
else:
date = datetime(year_in_use, month_in_use - 1, 1)
last_day = dc.eom_preceding(date).date().day
start_date = str(last_day) + '-' + meses[
month_in_use - 2] + '-' + str(year_in_use)
print(start_date)
l = [
build_ticker(tck, month_in_use, year_in_use),
build_ticker(tck, month_in_use + 1, year_in_use)
]
try:
df2 = bbg.fetch_series(securities=l,
fields=['LAST_PRICE'],
startdate=start_date,
enddate=end_date)
except KeyError as e:
continue
if df2.shape[1] >= 2:
list = df2[build_ticker(tck, month_in_use, year_in_use)].values
list1 = df2[build_ticker(tck, month_in_use + 1,
year_in_use)].values
first = list[0]
first1 = list1[0]
#print(first)
#print(first1)
# EXCESS RETURN
class BuildBondFutureTracker(object):
futures_ticker_dict = {
'US': 'TY',
'GE': 'RX',
'FR': 'OAT',
'IT': 'IK',
'JP': 'JB',
'AU': 'XM',
'UK': 'G ',
'CA': 'CN'
}
fx_dict = {
'GE': 'EURUSD Curncy',
'UK': 'GBPUSD Curncy',
'CA': 'CADUSD Curncy',
'JP': 'JPYUSD Curncy',
'AU': 'AUDUSD Curncy',
'FR': 'EURUSD Curncy',
'IT': 'EURUSD Curncy',
'US': 'USD Curncy'
}
def __init__(self, country, start_date, end_date):
self.bbg = BBG()
self.country = country
self.start_date = self._assert_date_type(start_date)
self.end_date = self._assert_date_type(end_date)
self.generic_tickers = [
self.futures_ticker_dict[country] + str(x) + ' Comdty'
for x in range(1, 4)
]
self.df_generics = self._get_generic_future_series()
self.df_uc = self._get_underlying_contracts()
self.contract_list = self._get_contracts_list()
self.df_fn = self._get_first_notice_dates()
self.df_prices = self._get_all_prices()
df_tracker = self._build_tracker()
self.tr_index = df_tracker[['er_index']]
self.df_roll_info = df_tracker[[
'contract_rolling_out', 'roll_out_date', 'holdings'
]].dropna(how='any')
def _get_generic_future_series(self):
df = self.bbg.fetch_series(securities=self.generic_tickers,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
return df
def _get_underlying_contracts(self):
df = self.bbg.fetch_series(securities=self.generic_tickers,
fields='FUT_CUR_GEN_TICKER',
startdate=self.start_date,
enddate=self.end_date)
df = df.reindex(self.df_generics.index).fillna(method='ffill')
return df
def _get_contracts_list(self):
contract_list = self.bbg.fetch_futures_list(
generic_ticker=self.futures_ticker_dict[self.country] + '1 Comdty')
return contract_list
def _get_first_notice_dates(self):
df = self.bbg.fetch_contract_parameter(
securities=self.contract_list,
field='FUT_NOTICE_FIRST').sort_values('FUT_NOTICE_FIRST')
return df
def _get_all_prices(self):
tickers = self.contract_list + [self.fx_dict[self.country]]
df = self.bbg.fetch_series(securities=tickers,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
df = df.reindex(self.df_generics.index).fillna(method='ffill')
return df
def _build_tracker(self):
df_tracker = pd.DataFrame(index=self.df_generics.index,
columns=[
'contract_rolling_out', 'er_index',
'roll_out_date', 'holdings'
])
# set the values for the initial date
dt_ini = self.df_uc.index[0]
df_tracker.loc[dt_ini, 'er_index'] = 100
contract_rolling_out = self.df_uc.loc[
dt_ini,
self.futures_ticker_dict[self.country] + '2 Comdty'] + ' Comdty'
df_tracker.loc[dt_ini, 'contract_rolling_out'] = contract_rolling_out
holdings = df_tracker.loc[dt_ini, 'er_index'] / (
self.df_generics.loc[
dt_ini, self.futures_ticker_dict[self.country] + '2 Comdty'] *
self.df_prices[self.fx_dict[self.country]].loc[dt_ini])
df_tracker.loc[dt_ini, 'holdings'] = holdings
roll_out_date = self.df_fn.loc[df_tracker.loc[dt_ini,
'contract_rolling_out'],
'FUT_NOTICE_FIRST'] - BDay(1)
df_tracker.loc[dt_ini, 'roll_out_date'] = roll_out_date
for d, dm1 in zip(df_tracker.index[1:], df_tracker.index[:-1]):
df_tracker.loc[d, 'contract_rolling_out'] = contract_rolling_out
price_dm1 = self.df_prices.loc[dm1, contract_rolling_out]
price_d = self.df_prices.loc[d, contract_rolling_out]
pnl = holdings * (price_d - price_dm1) * self.df_prices[
self.fx_dict[self.country]].loc[d]
if math.isnan(pnl):
pnl = 0
df_tracker.loc[d,
'er_index'] = df_tracker.loc[dm1, 'er_index'] + pnl
if d >= roll_out_date.date():
contract_rolling_out = (
self.df_uc.loc[d, self.futures_ticker_dict[self.country] +
'2 Comdty'] + ' Comdty')
df_tracker.loc[d,
'contract_rolling_out'] = contract_rolling_out
holdings = df_tracker.loc[d, 'er_index'] / (
self.df_generics.loc[
d, self.futures_ticker_dict[self.country] + '2 Comdty']
* self.df_prices[self.fx_dict[self.country]].loc[d])
df_tracker.loc[d, 'holdings'] = holdings
roll_out_date = self.df_fn.loc[df_tracker.loc[
d, 'contract_rolling_out'], 'FUT_NOTICE_FIRST'] - BDay(1)
df_tracker.loc[d, 'roll_out_date'] = roll_out_date
return df_tracker
@staticmethod
def _assert_date_type(date):
if type(date) is pd.Timestamp:
date = date
else:
date = pd.to_datetime(date)
return date
from dataapi import SGS
from bloomberg import BBG
import numpy as np
import pandas as pd
from sklearn import preprocessing
getdata = SGS()
bbg = BBG()
start_date = pd.to_datetime("01-01-2001")
end_date = pd.to_datetime("07-01-2019")
#fetching Brazil FGV Consumer Confidence Index SA Sep 2005=100 Original Date: '30-sep-2005'
df = bbg.fetch_series(securities=['BZFGCCSA Index'],
fields=['PX_LAST'],
startdate=start_date,
enddate=end_date)
consbr = pd.DataFrame(data=df)
consbr = consbr.droplevel(0)
consbr = consbr.reset_index()
consbr = consbr.set_index('TRADE_DATE')
consbr = consbr.resample('Q').mean()
# Normalized series Consumer Confidence
x = np.array(consbr['BZFGCCSA Index'])
x = x.reshape(-1, 1)
min_max_scaler = preprocessing.MinMaxScaler()
x_scaled = min_max_scaler.fit_transform(x)
class FXCarryTrackers(object):
# TODO add ['CNH', 'ILS'] to the list of currencies and deposit rates
currencies = [
'EUR', 'JPY', 'GBP', 'CHF', 'CAD', 'AUD', 'NZD', 'NOK', 'SEK', 'DKK',
'CZK', 'HUF', 'ISK', 'PLN', 'SKK', 'TRY', 'ZAR', 'HKD', 'INR', 'IDR',
'PHP', 'SGD', 'KRW', 'CNY', 'MYR', 'TWD', 'THB', 'ARS', 'BRL', 'CLP',
'COP', 'MXN', 'PEN', 'RUB'
]
deposit_rates_dict = {
'EUR': 'EUDRC',
'JPY': 'JYDRC',
'GBP': 'BPDRC',
'CHF': 'SFDRC',
'CAD': 'CDDRC',
'AUD': 'ADDRC',
'NZD': 'NDDRC',
'NOK': 'NKDRC',
'SEK': 'SKDRC',
'DKK': 'DKDRC',
'CZK': 'CKDRC',
'HUF': 'HFDRC',
'ISK': 'IKDRC',
'PLN': 'PZDRC',
'SKK': 'VKDRC',
'TRY': 'TYDRC',
'ZAR': 'SADRC',
'HKD': 'HDDRC',
'INR': 'IRDRC',
'IDR': 'IHDRC',
'PHP': 'PPDRC',
'SGD': 'SDDRC',
'KRW': 'KWDRC',
'CNY': 'CCNI3M',
'MYR': 'MRDRC',
'TWD': 'NTDRC',
'THB': 'TBDRC',
'ARS': 'APDRC',
'BRL': 'BCDRC',
'CLP': 'CHDRC',
'COP': 'CLDRC',
'MXN': 'MPDRC',
'PEN': 'PSDRC',
}
iso_country_dict = {
'AUD': 'AU',
'BRL': 'BR',
'CAD': 'CA',
'CHF': 'CH',
'CLP': 'CL',
'CZK': 'CZ',
'EUR': 'EU',
'GBP': 'GB',
'HUF': 'HU',
'JPY': 'JP',
'KRW': 'KR',
'MXN': 'MX',
'NOK': 'NO',
'NZD': 'NZ',
'PHP': 'PH',
'PLN': 'PL',
'SEK': 'SE',
'SGD': 'SG',
'TRY': 'TR',
'TWD': 'TW',
'ZAR': 'ZA'
}
quoted_as_XXXUSD = [
'BRL', 'CAD', 'CHF', 'CLP', 'CZK', 'HUF', 'JPY', 'KRW', 'MXN', 'NOK',
'PHP', 'PLN', 'SGD', 'TRY', 'TWD', 'ZAR', 'SEK'
]
def __init__(self, ccy_symbol, start_date='1999-12-31', end_date='today'):
"""
Returns an object with the following attributes:
- spot_rate: Series with the spot rate data vs. the USD
- deposit_rates: Series with the the currency deposit rate and the USD deposite rate
- er_index: Series with the excess return index
- ts_df: DataFrame with columns 'Spot', 'ccy_rate', and 'usd_rate'
:param ccy_symbol: str, Currency symbol from Bloomberg
:param start_date: str, when the tracker should start
:param end_date: str, when the tracker should end
"""
assert ccy_symbol in self.currencies, f'{ccy_symbol} not currently supported'
self.bbg = BBG()
self.ccy_symbol = ccy_symbol
self.start_date = pd.to_datetime(start_date)
self.end_date = pd.to_datetime(end_date)
self.spot_rate = self._get_spot_rate()
self.deposit_rates = self._deposit_rates()
self.country = self.iso_country_dict[self.ccy_symbol]
self.fh_ticker = 'fx ' + self.country.lower(
) + ' ' + self.ccy_symbol.lower()
self.df_metadata = self._get_metadata()
self.df_tracker = self._calculate_tr_index(self.spot_rate,
self.deposit_rates)
self.df_tracker = self._get_tracker_melted()
@staticmethod
def _calculate_tr_index(spot_rate, dep_rates, ann_factor=252):
ts_df = pd.concat([spot_rate, dep_rates], axis=1,
sort=True).fillna(method='ffill').dropna()
er_index = pd.Series(index=ts_df.index)
er_index.iloc[0] = 100.
for d in er_index.index[1:]:
fx_spot_ret = ts_df.loc[:d].iloc[-1, 0] / ts_df.loc[:d].iloc[-2, 0]
# TODO check if all Bloomberg deposite rates are accrued in the same way
long_carry_ret = 1 + (ts_df.loc[:d].iloc[-1, 1] / 100) / ann_factor
short_carry_ret = 1 + (ts_df.loc[:d].iloc[-1, 2] /
100) / ann_factor
carry_return = fx_spot_ret * long_carry_ret / short_carry_ret
er_index[d] = er_index[:d].iloc[-2] * carry_return
return er_index.to_frame('er_index')
def _get_spot_rate(self):
spot_rate_bbg_ticker = self.ccy_symbol + 'USD Curncy'
bbg_raw_spot_data = self.bbg.fetch_series(
securities=spot_rate_bbg_ticker,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
bbg_raw_spot_data.columns = [self.ccy_symbol]
bbg_raw_spot_data = bbg_raw_spot_data.fillna(method='ffill').dropna()
return bbg_raw_spot_data
def _deposit_rates(self):
usd_dep_rate = self.bbg.fetch_series(securities='USDRC BDSR Curncy',
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
usd_dep_rate.columns = ['usd_rate']
if self.ccy_symbol not in ['RUB']:
deposit_rate_bbg_ticker = self.deposit_rates_dict[
self.ccy_symbol] + ' BDSR Curncy'
else:
deposit_rate_bbg_ticker = 'RRDRA Curncy' if self.ccy_symbol == 'RUB' else []
ccy_dep_rate = self.bbg.fetch_series(
securities=deposit_rate_bbg_ticker,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
ccy_dep_rate.columns = [self.ccy_symbol + '_rate']
dep_rates = pd.concat([ccy_dep_rate, usd_dep_rate],
join='outer',
axis=1,
sort=True).fillna(method='ffill').dropna()
return dep_rates
def _get_metadata(self):
df = pd.DataFrame(index=[0],
data={
'fh_ticker': self.fh_ticker,
'asset_class': 'FX',
'type': 'currency forward',
'exchange_symbol': self.ccy_symbol,
'currency': 'USD',
'country': self.country,
'maturity': 1 / 12
})
return df
def _get_tracker_melted(self):
df = self.df_tracker[['er_index']].rename({'er_index': self.fh_ticker},
axis=1)
df['time_stamp'] = df.index.to_series()
df = df.melt(id_vars='time_stamp',
var_name='fh_ticker',
value_name='value')
df = df.dropna()
return df
# BDVD_PROJ_12M_YLD é o ticker para o próximo valor projetado de rendimento para os próximos 12 meses em percentual; também não disponível.
for i in field:
df = bbg.fetch_series(securities=[
'ABEV3 BZ Equity', 'AZUL4 BZ Equity', 'B3SA3 BZ Equity',
'BBAS3 BZ Equity', 'BBDC3 BZ Equity', 'BBDC4 BZ Equity',
'BBSE3 BZ Equity', 'BRAP4 BZ Equity', 'BRDT3 BZ Equity',
'BRFS3 BZ Equity', 'BRKM5 BZ Equity', 'BRML3 BZ Equity',
'BTOW3 BZ Equity', 'CCRO3 BZ Equity', 'CIEL3 BZ Equity',
'CMIG4 BZ Equity', 'CSAN3 BZ Equity', 'CSNA3 BZ Equity',
'CVCB3 BZ Equity', 'CYRE3 BZ Equity', 'ECOR3 BZ Equity',
'EGIE3 BZ Equity', 'ELET3 BZ Equity', 'ELET6 BZ Equity',
'EMBR3 BZ Equity', 'ENBR3 BZ Equity', 'EQTL3 BZ Equity',
'FLRY3 BZ Equity', 'GGBR4 BZ Equity', 'GOAU4 BZ Equity',
'GOLL4 BZ Equity', 'HYPE3 BZ Equity', 'IGTA3 BZ Equity',
'IRBR3 BZ Equity', 'ITSA4 BZ Equity', 'ITUB4 BZ Equity',
'JBSS3 BZ Equity', 'KLBN11 BZ Equity', 'KROT3 BZ Equity',
'LAME4 BZ Equity', 'LREN3 BZ Equity', 'MGLU3 BZ Equity',
'MRFG3 BZ Equity', 'MRVE3 BZ Equity', 'MULT3 BZ Equity',
'NATU3 BZ Equity', 'PCAR4 BZ Equity', 'PETR3 BZ Equity',
'PETR4 BZ Equity', 'QUAL3 BZ Equity', 'RADL3 BZ Equity',
'RAIL3 BZ Equity', 'RENT3 BZ Equity', 'SANB11 BZ Equity',
'SBSP3 BZ Equity', 'SMLS3 BZ Equity', 'SUZB3 BZ Equity',
'TAEE11 BZ Equity', 'TIMP3 BZ Equity', 'UGPA3 BZ Equity',
'USIM5 BZ Equity', 'VALE3 BZ Equity', 'VIVT4 BZ Equity',
'VVAR3 BZ Equity', 'WEGE3 BZ Equity', 'YDUQ3 BZ Equity'
],
fields=[i],
startdate=start_date,
enddate=end_date)
exp_df = df.to_csv(rootdir + '%s.csv' % i)
from trackers import CommFutureTracker
import matplotlib.pyplot as plt
from bloomberg import BBG
import pandas as pd
comm_bbg_code = 'CL'
# Calculate your own commodity tracker
comm = CommFutureTracker(comm_bbg_code)
comm_df = comm.df_tracker[['er_index']]
# Get Bloomberg Commodity index (the 'BCOM' + comm_bbg_code + ' Index' will not work for every BCOM commodity)
bbg = BBG()
bbg_comm_raw = bbg.fetch_series(securities='BCOM' + comm_bbg_code + ' Index',
fields='PX_LAST',
startdate=comm_df.index[0].date(),
enddate=comm_df.index[-1].date())
bbg_comm_raw.columns = ['bcom_tracker']
bcom_index = pd.Series(index=comm_df.index)
bcom_index.iloc[0] = comm_df.iloc[0, 0]
for d in bcom_index.index[1:]:
past_hist = bbg_comm_raw.loc[:d].copy()
ret = past_hist.iloc[-1, 0] / past_hist.iloc[-2, 0]
bcom_index.loc[d] = bcom_index.loc[:d].iloc[-2] * ret
fig, ax = plt.subplots()
comm_df['er_index'].to_frame('my_index').dropna().plot(color='b',
linewidth=3,
ax=ax)
bcom_index.to_frame('bcom_index').plot(color='r', linewidth=1, ax=ax)
'S0023Z 8Y BLC2 Curncy', 'S0023Z 10M BLC2 Curncy',
'S0023Z 20Y BLC2 Curncy', 'S0023Z 33M BLC2 Curncy',
'S0023Z 7M BLC2 Curncy', 'S0023Z 8M BLC2 Curncy', 'S0023Z 11Y BLC2 Curncy',
'S0023Z 14Y BLC2 Curncy', 'S0023Z 18Y BLC2 Curncy',
'S0023Z 19Y BLC2 Curncy', 'S0023Z 23D BLC2 Curncy',
'S0023Z 9Y BLC2 Curncy', 'S0023Z 17M BLC2 Curncy', 'S0023Z 1I BLC2 Curncy',
'S0023Z 22Y BLC2 Curncy', 'S0023Z 28Y BLC2 Curncy',
'S0023Z 2I BLC2 Curncy', 'S0023Z 30Y BLC2 Curncy',
'S0023Z 31Y BLC2 Curncy', 'S0023Z 32Y BLC2 Curncy',
'S0023Z 38Y BLC2 Curncy', 'S0023Z 39Y BLC2 Curncy',
'S0023Z 40Y BLC2 Curncy', 'S0023Z 42D BLC2 Curncy',
'S0023Z 48Y BLC2 Curncy'
]
df_bbg = bbg.fetch_series(tickers_zero_curve,
"PX_LAST",
startdate=pd.to_datetime('today'),
enddate=pd.to_datetime('today'))
df_bbg = df_bbg.transpose()
df_bbg_m = bbg.fetch_contract_parameter(tickers_zero_curve, "MATURITY")
''''
The Zero curve will be used on the interpolation, to discover the rate for a specific term.
'''
# fazendo a curva zero
zero_curve = pd.concat([df_bbg, df_bbg_m], axis=1,
sort=True).set_index('MATURITY').sort_index()
zero_curve = zero_curve.astype(float)
zero_curve = zero_curve.interpolate(method='linear',
axis=0,
limit=None,
class FwdIRSTrackers(object):
"""
Class for creating excess return indices for rolling interest rate swaps using data from bloomberg.
At the start date, we assume we trade 100 notional 1M forward starting swaps with user provided maturity.
We mark-to-market the position over the month and then roll it into the new 1M forward at the end of the month
"""
# TODO: Include more currencies
currency_bbg_code_dict = {
'AUD': 'AD',
'CAD': 'CD',
'CHF': 'SF',
'EUR': 'EU',
'GBP': 'BP',
'JPY': 'JY',
'NZD': 'ND',
'SEK': 'SK',
'USD': 'US'
}
# TODO: Check these are correct
currency_calendar_dict = {
'USD': DayCounts('30E/360 ISDA', calendar='us_trading'),
'AUD': DayCounts('ACT/365', calendar='us_trading'),
'CAD': DayCounts('ACT/365', calendar='us_trading'),
'CHF': DayCounts('30A/360', calendar='us_trading'),
'EUR': DayCounts('30U/360', calendar='us_trading'),
'GBP': DayCounts('ACT/365', calendar='us_trading'),
'JPY': DayCounts('ACT/365F', calendar='us_trading'),
'NZD': DayCounts('ACT/365', calendar='us_trading'),
'SEK': DayCounts('30A/360', calendar='us_trading')
}
def __init__(self,
ccy='USD',
tenor=10,
start_date='2004-01-05',
end_date='today'):
"""
Returns an object with the following attributes:
- spot_swap_rates: Series with the rate for the spot starting swaps
- fwd_swap_rates: Series with the rate for the 1M forward starting swaps
- er_index: Series with the excess return index
:param ccy_symbol: str, Currency symbol
:param tenor: int, Tenor for the swap
:param start_date: str, when the tracker should start
:param end_date: str, when the tracker should end
"""
self.ccy = ccy
self.tenor = tenor
self.start_date = (pd.to_datetime(start_date) + BDay(1)).date()
self.end_date = pd.to_datetime(end_date).date()
self.dc = self.currency_calendar_dict[ccy]
self.bbg = BBG()
self.spot_swap_rates = self._get_spot_swap_rates()
self.fwd_swap_rates = self._get_1m_fwd_swap_rates()
self.df_tracker = self._calculate_tr_index()
self.country = self.bbg.fetch_contract_parameter(
self.ticker_spot, 'COUNTRY_ISO').iloc[0, 0].upper()
self.exchange_symbol = self.bbg.fetch_contract_parameter(
self.ticker_fwd, 'TICKER').iloc[0, 0]
self.fh_ticker = 'irs ' + self.country.lower() + ' ' + self.ccy.lower()
self.df_metadata = pd.DataFrame(data={
'fh_ticker': self.fh_ticker,
'asset_class': 'fixed income',
'type': 'swap',
'currency': self.ccy.upper(),
'country': self.country.upper(),
'maturity': self.tenor,
'roll_method': '1 month'
},
index=[self.ticker_spot])
self.df_tracker = self._get_tracker_melted()
def _calculate_tr_index(self):
spot_rate_series = self.spot_swap_rates.iloc[:, 0].dropna()
fwd_rate_series = self.fwd_swap_rates.iloc[:, 0].dropna()
ts_df = pd.concat([
spot_rate_series.to_frame('spot'),
fwd_rate_series.to_frame('fwd_1m')
],
axis=1,
sort=True).fillna(method='ffill').dropna()
er_index = pd.Series(index=ts_df.index)
er_index.iloc[0] = 100
start_date = er_index.index[0]
fwd_swap_rate = ts_df.loc[start_date, 'fwd_1m'] / 100
ref_swap_rate_d_minus_1 = fwd_swap_rate
roll_date = self.dc.busdateroll(start_date + relativedelta(months=1),
'modifiedfollowing')
fwd_mat = self.dc.busdateroll(
start_date + relativedelta(months=1 + self.tenor * 12),
'modifiedfollowing')
for d in er_index.index[1:]:
current_fwd_swap_rate = ts_df.loc[d, 'fwd_1m'] / 100
current_spot_swap_rate = ts_df.loc[d, 'spot'] / 100
curr_fwd_mat = self.dc.busdateroll(
d + relativedelta(months=1 + self.tenor * 12),
'modifiedfollowing')
curr_spot_mat = self.dc.busdateroll(
d + relativedelta(months=self.tenor * 12), 'modifiedfollowing')
w = 1 if d >= roll_date else self.dc.tf(
curr_fwd_mat, fwd_mat) / self.dc.tf(curr_fwd_mat,
curr_spot_mat)
ref_swap_rate = w * current_spot_swap_rate + (
1 - w) * current_fwd_swap_rate
# This is the present value of a basis point
# Using the interpolated forward swap rate as an internal rate of return
pv01 = np.sum([(1 / 2) * ((1 + ref_swap_rate / 2)**(-i))
for i in range(1, self.tenor * 2 + 1)])
if np.isnan((ref_swap_rate_d_minus_1 - ref_swap_rate) * pv01):
ret = 0
else:
ret = (ref_swap_rate_d_minus_1 - ref_swap_rate) * pv01
er_index[d] = er_index[:d].iloc[-2] * (1 + ret)
if d >= roll_date:
roll_date = self.dc.busdateroll(d + relativedelta(months=1),
'modifiedfollowing')
fwd_mat = self.dc.busdateroll(
d + relativedelta(months=1 + self.tenor * 12),
'modifiedfollowing')
ref_swap_rate_d_minus_1 = ts_df.loc[d, 'fwd_1m'] / 100
else:
ref_swap_rate_d_minus_1 = ref_swap_rate
return er_index.to_frame('er_index')
def _get_spot_swap_rates(self):
if self.ccy == 'EUR':
ticker = self.currency_bbg_code_dict[self.ccy] + 'SA' + str(
self.tenor) + ' Curncy'
elif self.ccy == 'NZD':
ticker = self.currency_bbg_code_dict[self.ccy] + 'SWAP' + str(
self.tenor) + ' Curncy'
else:
# The AUD is using the 6M floating leg case as default to be consistent with the forward ticker
# The correct ticker is ADSW10Q Curncy 3M floating leg but then
# you cannot use S0302FS 1M10Y BLC Curncy for the forward swap
ticker = self.currency_bbg_code_dict[self.ccy] + 'SW' + str(
self.tenor) + ' Curncy'
bbg_raw_spot_data = self.bbg.fetch_series(securities=ticker,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
self.ticker_spot = ticker
bbg_raw_spot_data.columns = [self.ccy + str(self.tenor) + 'Y']
bbg_raw_spot_data.index = pd.to_datetime(bbg_raw_spot_data.index)
return bbg_raw_spot_data.dropna(how='all')
def _get_1m_fwd_swap_rates(self):
if self.ccy == 'EUR':
ticker = self.currency_bbg_code_dict[self.ccy] + 'SAA' + str(
self.tenor).zfill(2) + ' Curncy'
elif self.ccy == 'GBP':
ticker = self.currency_bbg_code_dict[self.ccy] + 'SWA' + str(
self.tenor).zfill(2) + ' Curncy'
elif self.ccy == 'AUD':
# The AUD is using the 6M floating leg case as default to be consistent with the forward ticker
# The correct ticker is ADSW10Q Curncy 3M floating leg but then
# you cannot use S0302FS 1M10Y BLC Curncy for the forward swap
ticker = 'S0302FS 1M' + str(self.tenor) + 'Y BLC Curncy'
elif self.ccy == 'USD':
ticker = self.currency_bbg_code_dict[self.ccy] + 'FS0A' + str(
self.tenor) + ' Curncy'
else:
ticker = self.currency_bbg_code_dict[self.ccy] + 'FS0A' + str(
self.tenor).zfill(2) + ' Curncy'
bbg_fwd_data = self.bbg.fetch_series(securities=ticker,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
self.ticker_fwd = ticker
bbg_fwd_data.columns = [self.ccy + str(self.tenor) + 'Y']
bbg_fwd_data.index = pd.to_datetime(bbg_fwd_data.index)
return bbg_fwd_data.dropna(how='all')
def _get_tracker_melted(self):
df = self.df_tracker[['er_index'
]].rename({'er_index': self.ticker_spot}, axis=1)
df['time_stamp'] = df.index.to_series()
df = df.melt(id_vars='time_stamp',
var_name='fh_ticker',
value_name='value')
df = df.dropna()
return df
import pandas as pd
from sklearn import preprocessing
import numpy as np
import matplotlib.pyplot as plt
bbg = BBG()
min_max_scaler = preprocessing.MinMaxScaler()
# Pulling IBOVESPA and S&P indexes volatility, as well as general US and BR 10-year bonds
# Original Date: '28-fev-1967'
start_date = pd.to_datetime('01-jan-2010')
end_date = pd.to_datetime('today')
df = bbg.fetch_series(securities=['SPX Index', 'IBOV Index', 'USGG10YR Index', 'GEBR10Y Index'],
fields=['VOLATILITY_90D', 'Volatil 90D'],
startdate=start_date,
enddate=end_date)
volSPX_90 = pd.DataFrame(data=df['SPX Index'])
volSPX_90 = volSPX_90.droplevel('FIELD')
volSPX_90 = volSPX_90.resample('Q').last()
volIBOV_90 = pd.DataFrame(data=df['IBOV Index'])
volIBOV_90 = volIBOV_90.droplevel('FIELD')
volIBOV_90 = volIBOV_90.resample('Q').last()
volbonds_90 = pd.DataFrame(data=df['USGG10YR Index'])
volbonds_90 = volbonds_90.droplevel('FIELD')
volbonds_90 = volbonds_90.resample('Q').last()
voltitul_90 = pd.DataFrame(data=df['GEBR10Y Index'])
from bloomberg import BBG
import pandas as pd
from pandas.tseries.offsets import BDay
import matplotlib.pyplot as plt
import math
bbg = BBG()
start_date = (pd.to_datetime('2004-01-05') + BDay(1)).date() # for the data
end_date = pd.to_datetime('today').date()
# value of the qoutes
df_generics = bbg.fetch_series(
securities=['JB1 Comdty', 'JB2 Comdty', 'JB3 Comdty'],
fields='PX_LAST',
startdate=start_date,
enddate=end_date)
# underlying contracts
df_uc = bbg.fetch_series(securities=['JB1 Comdty', 'JB2 Comdty', 'JB3 Comdty'],
fields='FUT_CUR_GEN_TICKER',
startdate=start_date,
enddate=end_date)
df_uc = df_uc.reindex(df_generics.index).fillna(method='ffill')
# all contracts
contract_list = bbg.fetch_futures_list(generic_ticker='JB1 Comdty')
# first notice date for the contract
df_fn = bbg.fetch_contract_parameter(
class BondFutureTracker(object):
futures_ticker_dict = {
'US': 'TY',
'DE': 'RX',
'FR': 'OAT',
'IT': 'IK',
'JP': 'JB',
'AU': 'XM',
'GB': 'G ',
'CA': 'CN'
}
fx_dict = {
'DE': 'EURUSD Curncy',
'GB': 'GBPUSD Curncy',
'CA': 'CADUSD Curncy',
'JP': 'JPYUSD Curncy',
'AU': 'AUDUSD Curncy',
'FR': 'EURUSD Curncy',
'IT': 'EURUSD Curncy',
'US': 'USD Curncy'
}
def __init__(self, country, start_date, end_date):
assert country in list(
self.futures_ticker_dict.keys()), 'Country not yet supported'
self.bbg = BBG()
self.country = country
self.start_date = self._assert_date_type(start_date)
self.end_date = self._assert_date_type(end_date)
self.generic_tickers = [
self.futures_ticker_dict[country] + str(x) + ' Comdty'
for x in range(1, 4)
]
self.df_generics = self._get_generic_future_series()
self.df_uc = self._get_underlying_contracts()
self.contract_list = self._get_contracts_list()
self.df_fn = self._get_first_notice_dates()
self.df_prices = self._get_all_prices()
self.df_tracker = self._build_tracker()
self.tr_index = self.df_tracker[['er_index']]
self.fh_ticker = 'fibf ' + self.country.lower() + ' 10y'
self.df_roll_info = self.df_tracker[[
'contract_rolling_out', 'roll_out_date', 'holdings'
]].dropna(how='any')
self.df_metadata = self._get_metadata()
self.df_tracker = self._get_tracker_melted()
def _get_tracker_melted(self):
df = self.df_tracker[['er_index']].rename({'er_index': self.fh_ticker},
axis=1)
df['time_stamp'] = df.index.to_series()
df = df.melt(id_vars='time_stamp',
var_name='fh_ticker',
value_name='value')
df = df.dropna()
return df
def _get_generic_future_series(self):
df = self.bbg.fetch_series(securities=self.generic_tickers,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
return df
def _get_underlying_contracts(self):
df = self.bbg.fetch_series(securities=self.generic_tickers,
fields='FUT_CUR_GEN_TICKER',
startdate=self.start_date,
enddate=self.end_date)
df = df.reindex(self.df_generics.index).fillna(method='ffill')
return df
def _get_contracts_list(self):
contract_list = self.bbg.fetch_futures_list(
generic_ticker=self.futures_ticker_dict[self.country] + '1 Comdty')
return contract_list
def _get_first_notice_dates(self):
df = self.bbg.fetch_contract_parameter(
securities=self.contract_list,
field='FUT_NOTICE_FIRST').sort_values('FUT_NOTICE_FIRST')
return df
def _get_all_prices(self):
tickers = self.contract_list + [self.fx_dict[self.country]]
df = self.bbg.fetch_series(securities=tickers,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
df = df.reindex(self.df_generics.index).fillna(method='ffill')
return df
def _build_tracker(self):
df_tracker = pd.DataFrame(index=self.df_generics.index,
columns=[
'contract_rolling_out', 'er_index',
'roll_out_date', 'holdings'
])
# set the values for the initial date
dt_ini = self.df_uc.dropna(how='all').index[0]
df_tracker.loc[dt_ini, 'er_index'] = 100
contract_rolling_out = self.df_uc.loc[
dt_ini,
self.futures_ticker_dict[self.country] + '2 Comdty'] + ' Comdty'
df_tracker.loc[dt_ini, 'contract_rolling_out'] = contract_rolling_out
holdings = df_tracker.loc[dt_ini, 'er_index'] / (
self.df_generics.loc[
dt_ini, self.futures_ticker_dict[self.country] + '2 Comdty'] *
self.df_prices[self.fx_dict[self.country]].loc[dt_ini])
df_tracker.loc[dt_ini, 'holdings'] = holdings
roll_out_date = self.df_fn.loc[df_tracker.loc[dt_ini,
'contract_rolling_out'],
'FUT_NOTICE_FIRST'] - BDay(1)
df_tracker.loc[dt_ini, 'roll_out_date'] = roll_out_date
for d, dm1 in zip(df_tracker.index[1:], df_tracker.index[:-1]):
df_tracker.loc[d, 'contract_rolling_out'] = contract_rolling_out
price_dm1 = self.df_prices.loc[dm1, contract_rolling_out]
price_d = self.df_prices.loc[d, contract_rolling_out]
pnl = holdings * (price_d - price_dm1) * self.df_prices[
self.fx_dict[self.country]].loc[d]
if math.isnan(pnl):
pnl = 0
df_tracker.loc[d,
'er_index'] = df_tracker.loc[dm1, 'er_index'] + pnl
if d >= roll_out_date:
contract_rolling_out = (
self.df_uc.loc[d, self.futures_ticker_dict[self.country] +
'2 Comdty'] + ' Comdty')
df_tracker.loc[d,
'contract_rolling_out'] = contract_rolling_out
holdings = df_tracker.loc[d, 'er_index'] / (
self.df_generics.loc[
d, self.futures_ticker_dict[self.country] + '2 Comdty']
* self.df_prices[self.fx_dict[self.country]].loc[d])
df_tracker.loc[d, 'holdings'] = holdings
roll_out_date = self.df_fn.loc[df_tracker.loc[
d, 'contract_rolling_out'], 'FUT_NOTICE_FIRST'] - BDay(1)
df_tracker.loc[d, 'roll_out_date'] = roll_out_date
return df_tracker
@staticmethod
def _assert_date_type(date):
if type(date) is pd.Timestamp:
date = date
else:
date = pd.to_datetime(date)
return date
def _get_metadata(self):
df = pd.DataFrame(index=[0],
data={
'fh_ticker':
self.fh_ticker,
'asset_class':
'fixed income',
'type':
'bond future',
'exchange_symbol':
self.futures_ticker_dict[self.country],
'currency':
'USD',
'country':
self.country,
'maturity':
10
})
return df
from bloomberg import BBG
import pandas as pd
start_date = '17-oct-2001'
#end_date = pd.to_datetime('')
end_date = '09-oct-2003'
bbg = BBG()
# todos contratos negociados desde outubro de 2003
#df1 = bbg.fetch_series(securities=['CTV03 Comdty','CTZ03 Comdty','CTH04 Comdty','CTK04 Comdty','CTN04 Comdty','CTV04 Comdty','CTZ04 Comdty','CTH05 Comdty','CTK05 Comdty','CTN05 Comdty'],
df1 = bbg.fetch_series(securities=['CTV03 Comdty'],
fields=['PX_LAST'],
startdate=start_date,
enddate=end_date)
print(df1)
start_date = '12-dec-2001'
end_date = '05-dec-2003'
bbg = BBG()
df2 = bbg.fetch_series(securities=['CTZ03 Comdty'],
fields=['PX_LAST'],
startdate=start_date,
enddate=end_date)
print(df2)
start_date = '14-mar-2002'
end_date = '09-mar-2004'
bbg = BBG()
df3 = bbg.fetch_series(securities=['CTH04 Comdty'],
from bloomberg import BBG
import pandas as pd
start_date = '01-jul-1959'
end_date = pd.to_datetime('today')
bbg = BBG()
# Grabs tickers and fields
df = bbg.fetch_series(securities=['CT1 Comdty', 'W 1 COMB Comdty'],
fields=['PX_LAST'],
startdate=start_date,
enddate=end_date)
print(df)
# Grabs cashflow payments of corporate bonds
#df = bbg.fetch_cash_flow('EI1436001 Govt', pd.to_datetime('03-jul-2017'))
#print(df)
# Grabs weights of the components of an index
#df = bbg.fetch_index_weights(index_name='IBOV Index', ref_date=pd.to_datetime('03-jul-2017'))
#print(df)
#print(df.sum())
# Grabs all the contracts from a generic series
#futures_list = bbg.fetch_futures_list(generic_ticker='TY1 Comdty')
#print(futures_list)
# grabs the first notice date for each contract
#df_fn = bbg.fetch_contract_parameter(securities=futures_list, field='FUT_NOTICE_FIRST')
#print(df_fn)
from bloomberg import BBG
import pandas as pd
start_date = '30-mar-2015'
end_date = pd.to_datetime('today')
bbg = BBG()
# Grabs tickers and fields
df = bbg.fetch_series(securities=['BRL Curncy', 'DXY Index'],
fields=['PX_LAST', 'VOLATILITY_90D'],
startdate=start_date,
enddate=end_date)
print(df)
# Grabs cashflow payments of corporate bonds
df = bbg.fetch_cash_flow('EI1436001 Govt', pd.to_datetime('03-jul-2017'))
print(df)
# Grabs weights of the components of an index
df = bbg.fetch_index_weights(index_name='IBOV Index',
ref_date=pd.to_datetime('03-jul-2017'))
print(df)
print(df.sum())
# Grabs all the contracts from a generic series
futures_list = bbg.fetch_futures_list(generic_ticker='TY1 Comdty')
print(futures_list)
# grabs the first notice date for each contract
df_fn = bbg.fetch_contract_parameter(securities=futures_list,
bbg = BBG()
getdata = FRED()
min_max_scaler = preprocessing.MinMaxScaler()
# Initial and End Dates
start_date = pd.to_datetime('01-jan-2000')
end_date = pd.to_datetime('15-jun-2019')
### GROWTH SERIES
#fetching US Conference Board Consumer Confidence SA 1985=100
# Original Date: '28-fev-1967'
df = bbg.fetch_series(securities=['CONCCONF Index'],
fields=['PX_LAST'],
startdate=start_date,
enddate=end_date)
concconf = pd.DataFrame(data=df)
concconf = concconf.droplevel(0)
concconf = concconf.reset_index()
concconf = concconf.set_index('TRADE_DATE')
concconf.index.names = ['Date']
concconf = concconf.resample('Q').mean()
#normalizing series Consumer Confidence
x = np.array(concconf['CONCCONF Index'])
x = x.reshape(-1,1)
min_max_scaler = preprocessing.MinMaxScaler()
x_scaled = min_max_scaler.fit_transform(x)
from trackers import FXForwardTrackers
import matplotlib.pyplot as plt
from bloomberg import BBG
import pandas as pd
currency = 'BRL'
# Calculate your own FX tracker
fx = FXForwardTrackers(currency)
fx_tracker_df = fx.df_tracker
# Get Bloomberg's FX tracker
bbg = BBG()
bbg_carry_raw = bbg.fetch_series(securities=currency + 'USDCR CMPN Curncy',
fields='PX_LAST',
startdate=fx_tracker_df.index[0].date(),
enddate=fx_tracker_df.index[-1].date())
bbg_carry_raw.columns = ['bbg_tracker']
bbg_carry_index = pd.Series(index=fx_tracker_df.index)
bbg_carry_index.iloc[0] = fx_tracker_df.iloc[0, 0]
for d in bbg_carry_index.index[1:]:
ret = fx_tracker_df.loc[d, 'er_index'] / fx_tracker_df.loc[:d].iloc[-2, 0]
bbg_carry_index.loc[d] = bbg_carry_index.loc[:d].iloc[-2] * ret
fig, ax = plt.subplots()
fx_tracker_df['er_index'].to_frame('my_index').dropna().plot(color='b',
linewidth=3,
ax=ax)
bbg_carry_index.to_frame('bbg_index').plot(color='r', linewidth=1, ax=ax)
plt.title(currency + ' tracker')
class FXForwardTrackers(object):
"""
Class for creating excess return indices for currencies using data from bloomberg.
At the start date, we assume we trade 100 units of the base currency in 1M forward contracts.
We mark-to-market the position over the month and then roll it into the new 1M forward at the end of the month
"""
# Currently available currencies
# TODO add 'DKK', 'ISK', 'SKK', 'HKD', 'CNY', 'MYR', 'THB', 'ARS', 'COP', 'PEN' to the list of currencies
currencies = [
'AUD', 'BRL', 'CAD', 'CHF', 'CLP', 'CZK', 'EUR', 'GBP', 'HUF', 'JPY',
'KRW', 'MXN', 'NOK', 'NZD', 'PHP', 'PLN', 'SEK', 'SGD', 'TRY', 'TWD',
'ZAR'
]
point_divisor_dict = {
'AUD': 10000,
'BRL': 10000,
'CAD': 10000,
'CHF': 10000,
'CLP': 1,
'CZK': 1000,
'EUR': 10000,
'GBP': 10000,
'HUF': 100,
'JPY': 100,
'KRW': 1,
'MXN': 10000,
'NOK': 10000,
'NZD': 10000,
'PHP': 1,
'PLN': 10000,
'SEK': 10000,
'SGD': 10000,
'TRY': 10000,
'TWD': 1,
'ZAR': 10000
}
fwd_1M_dict = {
'AUD': 'AUD1M Curncy',
'BRL': 'BCN1M Curncy',
'CAD': 'CAD1M Curncy',
'CHF': 'CHF1M Curncy',
'CLP': 'CHN1M Curncy',
'CZK': 'CZK1M Curncy',
'EUR': 'EUR1M Curncy',
'GBP': 'GBP1M Curncy',
'HUF': 'HUF1M Curncy',
'JPY': 'JPY1M Curncy',
'KRW': 'KRW1M Curncy',
'MXN': 'MXN1M Curncy',
'NOK': 'NOK1M Curncy',
'NZD': 'NZD1M Curncy',
'PHP': 'PHP1M Curncy',
'PLN': 'PLN1M Curncy',
'SEK': 'SEK1M Curncy',
'SGD': 'SGD1M Curncy',
'TRY': 'TRY1M Curncy',
'TWD': 'NTN1M BGN Curncy',
'ZAR': 'ZAR1M Curncy'
}
quoted_as_XXXUSD = [
'BRL', 'CAD', 'CHF', 'CLP', 'CZK', 'HUF', 'JPY', 'KRW', 'MXN', 'NOK',
'PHP', 'PLN', 'SGD', 'TRY', 'TWD', 'ZAR', 'SEK'
]
iso_country_dict = {
'AUD': 'AU',
'BRL': 'BR',
'CAD': 'CA',
'CHF': 'CH',
'CLP': 'CL',
'CZK': 'CZ',
'EUR': 'EU',
'GBP': 'GB',
'HUF': 'HU',
'JPY': 'JP',
'KRW': 'KR',
'MXN': 'MX',
'NOK': 'NO',
'NZD': 'NZ',
'PHP': 'PH',
'PLN': 'PL',
'SEK': 'SE',
'SGD': 'SG',
'TRY': 'TR',
'TWD': 'TW',
'ZAR': 'ZA'
}
def __init__(self, ccy_symbol, start_date='1999-12-31', end_date='today'):
"""
Returns an object with the following attributes:
- tickers: list with 2 strs with Bloomberg ticker for the spot rates and 1M forward rates
- spot_rate: Series with the spot rate data
- fwd: Series with the 1M fwd rate data
- er_index: Series with the excess return index
- ts_df: DataFrame with columns 'Spot', 'Fwd', and 'Excess Return Index'
:param ccy_symbol: str, Currency symbol from Bloomberg
:param start_date: str, when the tracker should start
:param end_date: str, when the tracker should end
"""
assert ccy_symbol in self.currencies, f'{ccy_symbol} not currently supported'
self.bbg = BBG()
self.ccy_symbol = ccy_symbol
self.start_date = pd.to_datetime(start_date)
self.end_date = pd.to_datetime(end_date)
self.spot_rate = self._get_spot_rate()
raw_fwd = self._get_1m_fwd_rate()
self.country = self.iso_country_dict[self.ccy_symbol]
self.fh_ticker = 'fx ' + self.country.lower(
) + ' ' + self.ccy_symbol.lower()
self.df_metadata = self._get_metadata()
# calculate forward outrights
fwd_outrights = self.spot_rate + raw_fwd / self.point_divisor_dict[
self.ccy_symbol]
self.fwd_rate_bbg_data = fwd_outrights.fillna(method='ffill')
# get all quotes vs. the USD
if self.ccy_symbol in self.quoted_as_XXXUSD:
bbg_raw_spot_data = 1 / self.spot_rate
fwd_outrights = 1 / self.fwd_rate_bbg_data
else:
bbg_raw_spot_data = self.spot_rate
fwd_outrights = self.fwd_rate_bbg_data
self.df_tracker = self._calculate_tr_index(bbg_raw_spot_data,
fwd_outrights)
self.df_tracker = self._get_tracker_melted()
@staticmethod
def _calculate_tr_index(spot_rate, fwd_rate):
ts_df = pd.concat([spot_rate, fwd_rate], axis=1,
sort=True).fillna(method='ffill').dropna()
ts_df.columns = ['spot', 'fwd_1m']
er_index = pd.Series(index=ts_df.index)
fwd_mtm = pd.Series(index=ts_df.index)
st_dt = ts_df.index[0]
er_index.iloc[0] = 100.
strike = ts_df['fwd_1m'].iloc[0]
holdings = 100. / strike
# TODO: check if we need to use proper calendar for settlement_date calculation
settlement_date = st_dt + timedelta(days=30) + BDay(2)
last_rebalance = st_dt
for d in ts_df.index[1:]:
# TODO: check if we need to use proper calendar for day_count calculation
day_count = (settlement_date - d).days
spot_d = ts_df.loc[d, 'spot']
fwd_d = ts_df.loc[d, 'fwd_1m']
# Using DC 30/365 convention for mtm of the foward
fwd_mtm[d] = np.interp(float(day_count), [2, 32], [spot_d, fwd_d])
er_index[d] = er_index[last_rebalance] + holdings * (fwd_mtm[d] -
strike)
if d >= settlement_date:
strike = ts_df.loc[d, 'fwd_1m']
holdings = er_index[d] / strike
settlement_date = d + timedelta(days=30) + BDay(2)
last_rebalance = d
return er_index.to_frame('er_index')
def _get_spot_rate(self):
spot_rate_bbg_ticker = self.ccy_symbol + ' Curncy'
bbg_raw_spot_data = self.bbg.fetch_series(
securities=spot_rate_bbg_ticker,
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
bbg_raw_spot_data.columns = [self.ccy_symbol]
bbg_raw_spot_data = bbg_raw_spot_data.fillna(method='ffill').dropna()
return bbg_raw_spot_data
def _get_1m_fwd_rate(self):
bbg_raw_fwd_data = self.bbg.fetch_series(
securities=self.fwd_1M_dict[self.ccy_symbol],
fields='PX_LAST',
startdate=self.start_date,
enddate=self.end_date)
bbg_raw_fwd_data.columns = [self.ccy_symbol]
bbg_raw_fwd_data = bbg_raw_fwd_data.fillna(method='ffill').dropna()
return bbg_raw_fwd_data
def _get_metadata(self):
df = pd.DataFrame(index=[0],
data={
'fh_ticker': self.fh_ticker,
'asset_class': 'FX',
'type': 'currency forward',
'exchange_symbol': self.ccy_symbol,
'currency': 'USD',
'country': self.country,
'maturity': 1 / 12
})
return df
def _get_tracker_melted(self):
df = self.df_tracker[['er_index']].rename({'er_index': self.fh_ticker},
axis=1)
df['time_stamp'] = df.index.to_series()
df = df.melt(id_vars='time_stamp',
var_name='fh_ticker',
value_name='value')
df = df.dropna()
return df
