def create_macro_index(self, ticker, shifts=False):
macro_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker = '{2}'"\
.format(self._start_date,
self._end_date,
ticker)
with util.DBConnector(DBName='marketdb') as db_conn:
macro_df = db_conn.get_data(macro_query).pivot(index='ValueDate',
columns='Ticker',
values='Last')\
.fillna(method='ffill')
#import pdb;pdb.set_trace()
if shifts: macro_df = macro_df.shift(1).dropna(axis=0)
date_df = pd.DataFrame(cf.create_daily_datelist(start_date=self._start_date,
end_date=self._end_date),
columns=['ValueDate'])
macro_df = pd.merge(date_df,
macro_df.reset_index('ValueDate'),
on='ValueDate',
how='left')\
.set_index('ValueDate')\
.fillna(method='ffill')\
.fillna(method='bfill')
return macro_df
def create_daily_datelist(start_date=date(2001, 4, 1), end_date=date.today()):
config = util.MLConfigParser()
date_query = "SELECT ValueDate FROM bbg_marketprice \
WHERE Ticker = 'USDJPY Index' \
AND ValueDate BETWEEN '{0}' AND '{1}' AND last IS NOT NULL "\
.format(start_date,
end_date)
with util.DBConnector(DBName='marketdb') as db_conn:
return db_conn.get_data(date_query).ValueDate.tolist()
def create_weekly_datelist(
start_date=date(2001, 4, 1), end_date=date.today(), weeknum=6):
config = util.MLConfigParser()
date_query = "SELECT ValueDate FROM bbg_marketprice \
WHERE DAYOFWEEK(ValueDate) = {2} \
AND ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker = 'USDJPY Index'"\
.format(start_date,
end_date,
weeknum)
with util.DBConnector(DBName='marketdb') as db_conn:
return db_conn.get_data(date_query).ValueDate.tolist()
def get_fin_cond(start_date, end_date, ticker):
price_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker = '{2}'".format(start_date, end_date, ticker)
with util.DBConnector(DBName='marketdb') as db_conn:
price_df = db_conn.get_data(price_query).pivot(index='ValueDate',
columns='Ticker',
values='Last').fillna(method='ffill')
#price_df[ticker] = [np.nan] + price_df[ticker].iloc[:-1].tolist()
#import pdb;pdb.set_trace()
price_df[ticker] = [np.nan] + price_df[ticker].diff().dropna(axis=0).tolist()
return price_df.dropna(axis=0)
def get_fx_rate(start_date=date(2001, 1, 1),
end_date=date.today(),
ccy_list=['USDJPY Index', 'EURJPY Index']):
fx_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker IN ('{2}')".format(start_date, end_date,
"','".join(ccy_list))
with util.DBConnector(DBName='marketdb') as db_conn:
return db_conn.get_data(fx_query).pivot(index='ValueDate',
columns='Ticker',
values='Last')
def get_sentiment_index(start_date, end_date, ticker):
date_list = util.common_func.create_weekly_datelist(start_date, end_date)
price_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker = '{2}'".format(start_date, end_date, ticker)
with util.DBConnector(DBName='marketdb') as db_conn:
price_df = db_conn.get_data(price_query).pivot(index='ValueDate',
columns='Ticker',
values='Last').fillna(method='ffill')
price_df = price_df.loc[date_list]
#price_df[ticker] = [np.nan, np.nan] + price_df[ticker].iloc[:-2].tolist()
#import pdb;pdb.set_trace()
price_df[ticker] = [np.nan] + price_df[ticker].diff().dropna(axis=0).tolist() + [np.nan]
return price_df.dropna(axis=0)
def _create_label_df(self):
if self._is_weekly:
date_list = cf.create_weekly_datelist(self._start_date, self._end_date)
else:
date_list = cf.create_monthly_datelist(self._start_date, self._end_date)
label_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker IN ('{2}')".format(self._start_date, self._end_date,
"','".join(self._label_tickers))
with util.DBConnector(DBName='marketdb') as db_conn:
label_src_df = db_conn.get_data(label_query).pivot(index='ValueDate',
columns='Ticker',
values='Last').dropna(axis=0)
label_src_df['NFCIINDX Index'] = [np.nan] + label_src_df['NFCIINDX Index'].iloc[:-1].tolist()
label_src_df.dropna(axis=0, inplace=True)
#import pdb;pdb.set_trace()
for ticker in self._label_tickers:
label_src_df[ticker] = self._normalize_data(label_src_df[[ticker]], term=104)
#import pdb;pdb.set_trace()
#label_src_df[ticker] = label_src_df[ticker].apply(lambda x: x if abs(x) < self._threshold_dic[ticker] else 0)
import pdb;pdb.set_trace()
label_src_df.to_csv('fc_label_src.csv')
label_list = []
for i in range(label_src_df.shape[0]):
if label_src_df.iloc[i].max() < 0:
label = [0, 0, 1]
elif label_src_df.iloc[i][self._label_tickers[0]] > label_src_df.iloc[i][self._label_tickers[1]]:
label = [1, 0, 0]
else:
label = [0, 1, 0]
label_list.append(label)
return pd.DataFrame(label_list,
index=label_src_df.index,
columns=self._label_tickers + ['N/A']).reindex(date_list)
def create_factor(self, ticker_list):
factor_query = "SELECT ValueDate, Ticker, Last \
FROM bbg_marketprice \
WHERE ValueDate BETWEEN '{0}' AND '{1}' \
AND Ticker IN ('{2}')"\
.format(self._start_date,
self._end_date,
"','".join(ticker_list))
with util.DBConnector(DBName='marketdb') as db_conn:
factor_df = db_conn.get_data(factor_query).pivot(index='ValueDate',
columns='Ticker',
values='Last')\
.fillna(method='ffill')
date_df = pd.DataFrame(cf.create_daily_datelist(start_date=self._start_date,
end_date=self._end_date),
columns=['ValueDate'])
factor_df = pd.merge(date_df,
factor_df.reset_index('ValueDate'),
on='ValueDate',
how='left')\
.set_index('ValueDate')\
.fillna(method='ffill')\
.fillna(method='bfill')
return factor_df