def get_portfolio_strategy_security_master(model=None):
model_id = get_model_id(model.name)
model_param_id = get_model_param_config_id(model_name=model.name,
settings=model.settings)
s_m = db.read_sql("select * from model_portfolio_sec_master " +
" where model_id = {0} and model_param_id = {1}".format(
model_id, model_param_id))
return s_m
def get_futures_ivol_by_series(futures_series=None,
maturity_type=None,
start_date=history_default_start_date,
end_date=market_data_date,
option_type="call",
option_deltas=None):
# Get series id
series_id = db.get_futures_series(futures_series)['id'].values.tolist()
# Handle maturity type choice
if maturity_type == 'constant_maturity':
table_name = "futures_ivol_constant_maturity_by_delta"
elif maturity_type == 'futures_contract':
table_name = "futures_ivol_fixed_maturity_by_delta"
else:
raise ValueError("maturity_type must be constant_maturity"
" or futures_contract")
# TODO: move this to someplace central
if option_type.lower() == "c":
option_type = "call"
if option_type.lower() == "p":
option_type = "put"
if not option_type.lower() in ["put", "call"]:
raise ValueError("option_type must be put or call")
where_str = " option_type = '{0}'".format(option_type)
where_str += " and date >= '{0}'".format(start_date)
where_str += " and date <= '{0}'".format(end_date)
where_str += " and series_id in {0}".format(
dbutils.format_for_query(series_id))
# Case: specific delta requested
if option_deltas is not None and option_type is not None:
delta_str = list()
for delta in option_deltas:
if delta in option_delta_grid:
if delta < 10:
delta_str.append('ivol_0' + str(delta) + 'd')
else:
delta_str.append('ivol_' + str(delta) + 'd')
s = " select series_id, date, days_to_maturity, {0}".format(delta_str)
s += " from " + table_name
else:
s = " select * from " + table_name
s += " where " + where_str + " order by date asc, days_to_maturity asc"
data = db.read_sql(s)
return data
def get_futures_contracts_by_series(futures_series=None,
start_date=history_default_start_date,
end_date=None):
s = "select * from futures_contracts where series_id in " \
" (select id from futures_series where series in {0}".format(
dbutils.format_for_query(futures_series)) + ")"
s += " and maturity_date >= '" + start_date.__str__() + "'"
if end_date is not None:
s += " and maturity_date <= '" + end_date.__str__() + "'"
s += " order by maturity_date asc"
data = db.read_sql(s)
data['maturity_date'] = pd.to_datetime(data['maturity_date'])
return data
def archive_model_param_config(model_name=None, settings=None):
export_settings = model_settings_to_json(settings)
model_id = get_model_id(model_name)
model_params = get_model_param_configs(model_name)
if export_settings not in model_params['model_params'].tolist():
new_id = db.read_sql('select max(id) from model_param_config')
try:
new_id = new_id['max'][0] + 1
except:
new_id = 0
model_params = pd.DataFrame(columns=['id', 'model_id', 'model_params'])
model_params.loc[0] = [new_id, model_id, export_settings]
table = db.get_table('model_param_config')
db.execute_bulk_insert(df=model_params, table=table)
def get_cftc_positioning_by_series(futures_series=None,
start_date=market_data_date,
end_date=None,
cftc_financial=True,
columns=None):
table_name = 'staging_cftc_positioning_financial'
if not cftc_financial:
table_name = 'staging_cftc_positioning_commodity'
if columns is None:
columns = [
'report_date_as_mm_dd_yyyy', 'open_interest_all',
'dealer_positions_long_all', 'dealer_positions_short_all',
'asset_mgr_positions_long_all', 'asset_mgr_positions_short_all',
'lev_money_positions_long_all', 'lev_money_positions_short_all'
]
# Ensure that we get the most recent data
one_week_prior = start_date - BDay(5)
fs = db.get_futures_series(futures_series)
if len(fs) == 0:
return None
cftc_code = str(fs['cftc_code'].values[0])
s = "select {0}".format(columns)\
.replace('[', '').replace(']', '').replace("'", '') + \
" from " + table_name + \
' where cftc_contract_market_code in {0}'.format(
dbutils.format_for_query([cftc_code]))
s += " and report_date_as_mm_dd_yyyy >= '{0}'".format(one_week_prior)
if end_date is not None:
s += " and report_date_as_mm_dd_yyyy <= '{0}'".format(end_date)
s += ' order by report_date_as_mm_dd_yyyy asc'
data = db.read_sql(s)
data = data.rename(columns={'report_date_as_mm_dd_yyyy': 'date'})
return data
def _get_time_series_data(s=None,
start_date=market_data_date,
end_date=None,
index_fields=None,
date_fields=['date']):
s += " and date >= '" + start_date.__str__() + "'"
if end_date is not None:
s += " and date <= '" + end_date.__str__() + "'"
s += " order by date asc"
data = db.read_sql(s)
for field in date_fields:
data[field] = pd.to_datetime(data[field])
if index_fields is not None:
tmp = list()
for field in index_fields:
tmp.append(data[field])
data.index = tmp
for col in index_fields:
del data[col]
return data
'''
--------------------------------------------------------------------------------
strategy pnl analysis
--------------------------------------------------------------------------------
'''
vrv_outputs = mm.get_model_outputs(model_name='vol_rv',
settings=sm.strategies['vol_rv'].settings)
# annoying: i don't have this archived yet
s = "select date, pnl, signal_name from model_signal_data_view " \
"where model_name = 'vol_rv' " \
"and ref_entity_id = 'strategy' and signal_name in ('ts_1', 'rv_iv_21')"
vrv_pnl = db.read_sql(s)
vrv_pnl = vrv_pnl.set_index(['date', 'signal_name'])['pnl']\
.unstack('signal_name')
vrv_pnl['weighted'] = -vrv_pnl['ts_1'] / 2 + vrv_pnl['rv_iv_21'] / 2
# Returns versus risk factors
strategy_name = 'equity_vs_vol'
df = pd.DataFrame()
return_windows = [1, 5, 10, 21, 63]
df['returns'] = underlying_price / underlying_price.shift(t1) - 1
df['pnl'] = pd.to_numeric(sm.outputs[strategy_name]['combined_pnl_net']
['optim_weight']).rolling(t1).sum()
df = df[np.isfinite(df).all(axis=1)]
hasconst=True)
# Neuberger overlapping estimator
# for t in range(0, len(data)):
'''
--------------------------------------------------------------------------------
NLP for matching futures descriptions
--------------------------------------------------------------------------------
'''
s = 'select distinct id, series, description from futures_series'
qfl_1 = db.read_sql(s)
s = 'select distinct "Market_and_Exchange_Names", "CFTC_Contract_Market_Code",' \
' "CFTC_Market_Code"' \
'from staging_cftc_positioning'
cftc_1 = db.read_sql(s)
s = 'select distinct "Market_and_Exchange_Names", "CFTC_Contract_Market_Code",' \
' "CFTC_Market_Code"' \
'from staging_cftc_positioning_financial'
cftc_2 = db.read_sql(s)
cftc = cftc_1.append(cftc_2)
# documents = cftc['Market_and_Exchange_Names'].values.tolist()
documents = qfl_1['description'].values.tolist()
def get_models():
s = 'select * from models'
df = db.read_sql(s)
return df