def load_bm(self):
sql = "SELECT index_id, date, value " \
"FROM base_finance.index_value " \
"WHERE index_id in ({iids}) AND date BETWEEN '{sd}' AND '{ed}'".format(
iids=sqlfmt(self.index_id), sd=self.start, ed=self.end)
df = pd.read_sql(sql, self.engine) # SQL Table
df["date"] = df["date"].apply(lambda x: dt.datetime(x.year, x.month, x.day))
return df.pivot(index="date", columns="index_id", values="value")
def load_nv(self):
sql = "SELECT fund_id, statistic_date as date, swanav as nav " \
"FROM base_public.fund_nv " \
"WHERE fund_id in ({fid}) AND statistic_date BETWEEN '{sd}' AND '{ed}'".format(
fid=sqlfmt(self.fund_id), sd=self.start, ed=self.end)
df = pd.read_sql(sql, self.engine) # SQL Table
df["date"] = df["date"].apply(lambda x: dt.datetime(x.year, x.month, x.day))
return df.pivot(index="date", columns="fund_id", values="nav")
def fetch_multisource_nv(update_time_l):
"""
Fetch records of DOrgInfo table where record update time >= `update_time`
Args:
update_time_l: record update time
Returns:
pandas.DataFrame
"""
where = "update_time BETWEEN '{upt_l}' AND '{upt_r}'".format(
upt_l=update_time_l.strftime("%Y%m%d%H%M%S"),
upt_r=(update_time_l +
relativedelta(hours=1, minutes=5)).strftime("%Y%m%d%H%M%S"))
sql_upt = "SELECT fund_id, statistic_date FROM fund_nv_source " \
"WHERE fund_id IN (SELECT DISTINCT fund_id FROM fund_nv_source WHERE {cond}) " \
"AND {cond}" \
.format(cond=where)
df_cond = pd.read_sql(sql_upt, _engine_wt)
# df_cond["statistic_date"] = df_cond["statistic_date"].apply(lambda x: "'" + str(x) + "'")
df_gbs = df_cond.groupby("statistic_date")["fund_id"].apply(
lambda x: "(" + sqlfmt(x) + ")").reset_index()
df_gbf = df_cond.groupby("fund_id")["statistic_date"].apply(
lambda x: "(" + sqlfmt(x) + ")").reset_index()
df = df_gbf if len(df_gbf) <= len(df_gbs) else df_gbs
col_eq, col_in = df.columns
conds = [
"({col_eq} = '{val_eq}' AND {col_in} IN {val_in})".format(
col_eq=col_eq, val_eq=val_eq, col_in=col_in, val_in=val_in)
for val_eq, val_in in zip(df[col_eq], df[col_in])
]
res = pd.DataFrame()
for i in range(len(conds)):
sql_conds = " OR ".join(conds[i:i + 1])
sql_all = "SELECT fund_id, fund_name, data_source, statistic_date, nav, added_nav " \
"FROM fund_nv_source " \
"WHERE {conds}".format(conds=sql_conds)
df = pd.read_sql(sql_all, _engine_wt)
res = res.append(df)
res.index = res[["fund_id", "statistic_date"]]
return res
def get_nv_data(fund_ids):
sql = "SELECT fi.fund_id, IFNULL(fn.statistic_date, fy.statistic_date) statistic_date " \
"FROM base_public.fund_info fi " \
"LEFT JOIN base_public.fund_nv fn ON fi.fund_id = fn.fund_id " \
"LEFT JOIN base_public.fund_yield fy ON fi.fund_id = fy.fund_id " \
"WHERE fi.fund_id IN ({fids})".format(fids=sqlfmt(fund_ids))
df = pd.read_sql(sql, engine).dropna()
df["statistic_date"] = df["statistic_date"].apply(
lambda x: dt.datetime(x.year, x.month, x.day))
return df
def load_factordata(cls, factor_ids, start, end, freq):
from utils.sqlfactory.constructor import sqlfmt
table = "factor_style_%s" % freq
sql = "SELECT factor_id, date, factor_value as value " \
"FROM factor.{tb} " \
"WHERE factor_id IN ({fids}) " \
"AND date > '{start}' AND date <= '{end}'".format(fids=sqlfmt(factor_ids), start=start, end=end, tb=table)
df = pd.read_sql(sql, cls.engine)
return df.pivot(index="date", columns="factor_id", values="value")
def fetch_swsindex(start, end):
sql = "SELECT index_id, `date`, `value` " \
"FROM base_finance.`index_value` " \
"WHERE index_id IN ({fid})" \
"AND (date BETWEEN '{start}' AND '{end}' OR update_time BETWEEN '{start}' and '{end}')".format(
fid=constructor.sqlfmt(TM_SWS), start=start, end=end)
df = pd.read_sql(sql, ENGINE_RD)
if len(df) == 0:
return pd.DataFrame()
df.rename(columns={"date": "statistic_date", "value": "index_value"}, inplace=True)
df["index_id"] = df["index_id"].apply(lambda x: TM_SWS.get(x))
return df
def load_indexvalue(self):
if len(self.index_ids) == 0:
return None
sql = "SELECT curr_id as index_id, date, weighted_ratio as value " \
"FROM base_finance.currency_pledge_ratio " \
"WHERE curr_id IN ({iids}) AND date BETWEEN '{sd}' AND '{ed}'".format(
iids=sqlfmt(self.index_ids), sd=self.start, ed=self.end)
df = pd.read_sql(sql, self.engine).pivot(index="date", columns="index_id", values="value")
if len(df) == 0:
return None
return df
def data_roe(self):
start, end = self.date_range
stock_ids = self.valid_stockids
sql = "SELECT stock_id, roe_ttm2 as value " \
"FROM base_finance.stock_ability_revenue " \
"WHERE date BETWEEN '{start}' AND '{end}' " \
"AND stock_id IN ({sids})".format(start=start.strftime("%Y%m%d"), end=end.strftime("%Y%m%d"), sids=sqlfmt(stock_ids))
return pd.read_sql(sql, self.engine)
def data_turnover(self):
start, end = self.date_range
stock_ids = self.valid_stockids
sql = "SELECT stock_id, market_price, pb_lf " \
"FROM base_finance.stock_valuation " \
"WHERE date BETWEEN '{start}' AND '{end}' " \
"AND stock_id IN ({sids}) " \
"AND market_price != 0 AND market_price IS NOT NULL ".format(start=start.strftime("%Y%m%d"), end=end.strftime("%Y%m%d"), sids=sqlfmt(stock_ids))
df = pd.read_sql(sql, self.engine)
df["bp"] = 1 / df["pb_lf"]
return df
def data_price(self):
start, end = self.date_range
stock_ids = self.valid_stockids
sql = "SELECT stock_id, `date`, `close` as `value` " \
"FROM base_finance.stock_price " \
"WHERE date BETWEEN '{start}' AND '{end}' " \
"AND stock_id IN ({sids}) " \
"AND last_trading_day = date " \
"AND close != 0 AND close IS NOT NULL".format(start=start.strftime("%Y%m%d"), end=end.strftime("%Y%m%d"), sids=sqlfmt(stock_ids))
df = pd.read_sql(sql, self.engine).pivot(index="date",
columns="stock_id",
values="value")
return df
def data_totalasset(self):
start, end = self.date_range
stock_ids = self.valid_stockids
sql = "SELECT stock_id, date, tot_assets as value " \
"FROM base_finance.stock_asset " \
"WHERE date BETWEEN '{start}' AND '{end}' " \
"AND stock_id IN ({sids}) ".format(start=(start-relativedelta(years=1)).strftime("%Y%m%d"), end=end.strftime("%Y%m%d"), sids=sqlfmt(stock_ids))
df = pd.read_sql(sql, self.engine).pivot(index="date",
columns="stock_id",
values="value")
return df