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