def info(self, info_table='AIndexDesc', cn_name=False): """ Get wind index information. Returns ------- out : dict dict that contains wind code and name of the index. Examples ------- >>> code = "000300.SH" >>> code = "007153BI.WI" >>> code = "IC.CFE" # error >>> wi = WindIndex(code) >>> index_info = wi.info() >>> index_info = wi.info(cn_name=True) # NO DICT AINDEXDESCRIPTION """ info_table_map = {'AIndex': AIndexDesc, 'BondIndex': CBIndexDesc} if info_table not in info_table_map.keys(): raise ValueError( "info_table must be in {'AIndexDesc', 'BondIndexDesc'}.") info_query = sess_wind.query(info_table_map.get(info_table)).filter_by( S_INFO_WINDCODE=self.code) index_info = DataBase.query_to_df(info_query, cn_name=cn_name) return index_info
def bm_component_info(codes, cn_name=False): """ Query mutual fund benchmark component information. Parameters --------- codes : Union[str, list] cn_name : bool Returns ------- mf_bm_info : pd.DataFrame Examples -------- >>> codes = "159934.SZ" >>> codes = ["159934.SZ", "159937.SZ"] >>> codes = ["007153.OF", "007154.OF"] >>> bm_comp = MF.bm_component_info(codes) >>> bm_comp = MF.bm_component_info(codes, cn_name=True) """ if isinstance(codes, str): bm_component_query = sess_wind.query(BmComponent).filter( BmComponent.S_INFO_WINDCODE == codes) else: bm_component_query = sess_wind.query(BmComponent).filter( BmComponent.S_INFO_WINDCODE.in_(codes)) bm_component_info = DataBase.query_to_df(bm_component_query, cn_name) return bm_component_info
def shares(codes, start=None, end=None, cn_name=False): """ Query mf shares. Parameters ---------- codes : Union[str, list] start : Union[str, date] end : Union[str, date] cn_name : bool Returns ------- shares : pd.DataFrame Examples ------ >>> codes = "159934.SZ" >>> shares = MF.shares(codes) >>> shares = MF.shares(codes, cn_name=True) """ query_cols = MfShare.F_INFO_WINDCODE, MfShare.CHANGE_DATE, MfShare.FUNDSHARE if isinstance(codes, str): shares_query = sess_wind.query(*query_cols).filter( MfShare.F_INFO_WINDCODE == codes) else: shares_query = sess_wind.query(*query_cols).filter( MfShare.F_INFO_WINDCODE.in_(codes)) shares_query = shares_query.filter( SaUtil.filter_date(MfShare.CHANGE_DATE, start, end, 'YYYYMMDD')) shares = DataBase.query_to_df(shares_query, cn_name) return shares
def market_data(self, *args, start=None, end=None, **kwargs): """ Query market data such as close price、volume、amount and so on of index. Parameters ---------- kwargs : it can be different columns according to requirements. Notice that columns selected will be ignored if they are non-existent in the database table . e.g. 007153BI.WI has no volume. Returns ------- index_md : pd.DataFrame() data frame with at least two columns. ['DATETIME', 'code'] Examples -------- >>> code = "000300.SH" >>> code = "007153BI.WI" >>> code = "IC.CFE" >>> wi = WindIndex(code) >>> index_md = wi.market_data(value = 'S_DQ_CLOSE') >>> index_md = wi.market_data('S_DQ_CLOSE') >>> index_md = wi.market_data(value='S_DQ_CLOSE', volume='S_DQ_VOLUME') >>> index_md = wi.market_data(value='S_DQ_CLOSE', volume='S_DQ_VOLUME', start='20190101', end='20190901') """ columns, column_dict = columns_from_args(*args, **kwargs) md_table_map = { '.WI': IndexWindInd, 'BI.WI': MfBmNav, '.CFE': CfePrices, 'CB': BondIndex, '.HI': HKIndexPrices } fix_match = re.match( ".*?(?P<fix>(.WI)|(BI.WI)|(.CFE)|(.HI)|(CB)|(.SH)|(.SZ)|(.CSI))", self.code) md_table = md_table_map.get(fix_match['fix'], AIndexPrices) query = sess_wind.query(md_table).filter_by( S_INFO_WINDCODE=self.code).options(load_only(*columns)) query = query.filter( SaUtil.filter_date(md_table.TRADE_DT, start, end, 'YYYYMMDD')) md_df = DataBase.query_to_df(query) column_dict.update({'TRADE_DT': 'DATETIME', 'S_INFO_WINDCODE': 'code'}) md_df.rename(columns=column_dict, inplace=True) return md_df.sort_values(by='DATETIME')
def bm_index_nav(codes, start=None, end=None, fund_name=False): """ Query mutual fund bm index nav. Parameters ---------- codes: Union[str, list] mutual fund codes. start : Union[str, date] end: : Union[str, date] fund_name : bool Returns ------- out : pd.DataFrame mutual fund benchmark nav with three columns: 'DATETIME', 'code', 'value'. Examples -------- >>> codes = "159934.SZ" >>> codes = ["159934.SZ", "159937.SZ"] >>> codes = ["007153.OF", "007154.OF"] >>> bm_nav = MF.bm_index_nav(codes) >>> bm_nav = MF.bm_index_nav(codes, fund_name=True) >>> bm_nav = MF.bm_index_nav(codes, fund_name=True, start='20190101', end='20190801') """ if isinstance(codes, str): codes = [codes] bm_codes = [MF.bm_index_code(i) for i in codes] bm_nav_query = sess_wind.query(MfBmNav).filter( MfBmNav.S_INFO_WINDCODE.in_(bm_codes), SaUtil.filter_date(MfBmNav.TRADE_DT, start, end, 'YYYYMMDD')) bm_nav = DataBase.query_to_df(bm_nav_query) bm_nav = PdUtil.select(bm_nav, DATETIME='TRADE_DT', code='S_INFO_WINDCODE', value='S_DQ_CLOSE') if fund_name: bm_index_info = MF.bm_index_info(codes) name_dict = dict( zip(bm_index_info['BENCHMARK_WINDCODE'], bm_index_info['F_INFO_BENCHMARK'])) bm_nav.replace({'code': name_dict}, inplace=True) bm_nav = bm_nav.sort_values(by=['code', 'DATETIME']).reset_index( drop=True) return bm_nav
def nav(codes, start=None, end=None, fund_name=False): """ Query fund nav. Parameters --------- codes : Union[str, list] start : Union[string, date] end : Union[string, date] fund_name : bool Use name of fund as code when fund_name is True. Returns ------ fund_nav : pd.DataFrame Examples ------- >>> codes = "159934.SZ" >>> codes = ["159934.SZ", "159937.SZ"] >>> fund_nav = MF.nav(codes) >>> fund_nav = MF.nav(codes, fund_name=True) >>> fund_nav = MF.nav(codes, fund_name=True, start='20190101', end='20190801') """ nav_columns = MfNav.PRICE_DATE, MfNav.F_INFO_WINDCODE, MfNav.F_NAV_ADJUSTED if isinstance(codes, str): nav_query = sess_wind.query(*nav_columns).filter( MfNav.F_INFO_WINDCODE == codes, SaUtil.filter_date(MfNav.PRICE_DATE, start, end, 'YYYYMMDD')) else: nav_query = sess_wind.query(*nav_columns).filter( MfNav.F_INFO_WINDCODE.in_(codes), SaUtil.filter_date(MfNav.PRICE_DATE, start, end, 'YYYYMMDD')) fund_nav = DataBase.query_to_df(nav_query).rename( columns={ 'PRICE_DATE': 'DATETIME', 'F_INFO_WINDCODE': 'code', 'F_NAV_ADJUSTED': 'value' }) if fund_name: fund_info = MF.fund_info(codes, 'F_INFO_WINDCODE', 'F_INFO_NAME') name_dict = dict( zip(fund_info['F_INFO_WINDCODE'], fund_info['F_INFO_NAME'])) fund_nav.replace({'code': name_dict}, inplace=True) return fund_nav.sort_values(by=['code', 'DATETIME']).reset_index( drop=True)
def manager_products(self, current=True, cn_name=False): """ Query products information of the manager. Parameters --------- current : bool if current is True, we'll query the funds under the manager currently; if current is False, we'll query all the funds including current and historical funds under the manager. cn_name : bool Returns ------- mg_products : pd.DataFrame Examples ------- >>> manager_name = "乐无穹" >>> manager_name = '刘彦春' >>> fm = FundManager(manager_name) >>> mg_products = fm.manager_products() >>> mg_products = fm.manager_products(current=False) >>> mg_products = fm.manager_products(cn_name=True) """ products_query: Query = sess_wind.query(MfManager, MfInfo.F_INFO_NAME).\ join(MfInfo, MfManager.F_INFO_WINDCODE == MfInfo.F_INFO_WINDCODE).\ filter(MfManager.F_INFO_FUNDMANAGER == self.manager_name, MfManager.F_INFO_MANAGER_STARTDATE.isnot(None)) products_query = products_query.options( Load(MfManager).load_only('F_INFO_FUNDMANAGER', 'F_INFO_FUNDMANAGER_ID', 'F_INFO_WINDCODE', 'F_INFO_MANAGER_STARTDATE', 'F_INFO_MANAGER_LEAVEDATE'), Load(MfInfo).load_only('F_INFO_NAME')) if current: products_query = products_query.filter( MfManager.F_INFO_MANAGER_LEAVEDATE.is_(None)) products = DataBase.query_to_df(products_query, cn_name) return products
def fund_info(codes, *args, cn_name=False, **kwargs): """ Query fund information. Attention: You columns select must accord column name after you set cn_name True or Flase. For example, when you set cn_name True, you select columns must in chinese column name. Parameters --------- codes : Union[list, str] mutual fund codes cn_name : bool Returns ------- fund_info : pd.DataFrame Examples -------- >>> codes = "159934.SZ" >>> codes = ["159934.SZ", "159937.SZ"] >>> cols = ["F_INFO_WINDCODE", "F_INFO_NAME", "F_INFO_CORP_FUNDMANAGEMENTCOMP", "F_INFO_SETUPDATE", "F_INFO_MATURITYDATE"] >>> fund_info = MF.fund_info(codes, *cols) >>> fund_info = MF.fund_info(codes, "F_INFO_WINDCODE", 名称="F_INFO_NAME") >>> fund_info = MF.fund_info(codes, "F_INFO_WINDCODE", "F_INFO_NAME") >>> fund_info = MF.fund_info(codes, 'Wind代码', '基金品种ID', cn_name=True) >>> fund_info = MF.fund_info(codes, cn_name=True) >>> fund_info = MF.fund_info(codes) >>> fund_info = MF.fund_info(codes, *['Wind代码', '基金品种ID'], cn_name=True) """ if isinstance(codes, str): fund_info_query = sess_wind.query(MfInfo).filter( MfInfo.F_INFO_WINDCODE == codes) else: fund_info_query = sess_wind.query(MfInfo).filter( MfInfo.F_INFO_WINDCODE.in_(codes)) fund_info = DataBase.query_to_df(fund_info_query, cn_name) if args or kwargs: fund_info = PdUtil.select(fund_info, *args, **kwargs) return fund_info
def various_navs(codes, start=None, end=None, cn_name=False): """ Query various navs mutual fund from wind database. Parameters ---------- codes : Union[string, list] start : Union[string, date] end : Union[string, date] cn_name : bool Set chinese column name for data frame when cn_name is True. Returns ------- fund_nav : pd.DataFrame mutual fund nav including unit nav, accumulated nav and adjusted nav.. Examples -------- >>> codes = "159934.SZ" >>> codes = ["159934.SZ", "159937.SZ"] >>> fund_nav = MF.various_navs(codes) >>> fund_nav = MF.various_navs(codes, cn_name=True) >>> fund_nav = MF.various_navs(codes, cn_name=True, start='20190101', end='20190801') >>> fund_nav = MF.various_navs(codes, cn_name=True, start='2019-01-01', end='20190901') """ nav_columns = MfNav.PRICE_DATE, MfNav.F_INFO_WINDCODE, MfNav.F_NAV_UNIT, MfNav.F_NAV_ACCUMULATED, \ MfNav.F_NAV_ADJUSTED if isinstance(codes, str): nav_query = sess_wind.query(*nav_columns).filter( MfNav.F_INFO_WINDCODE == codes, SaUtil.filter_date(MfNav.PRICE_DATE, start, end, 'YYYYMMDD')) else: nav_query = sess_wind.query(*nav_columns).filter( MfNav.F_INFO_WINDCODE.in_(codes), SaUtil.filter_date(MfNav.PRICE_DATE, start, end, 'YYYYMMDD')) fund_nav = DataBase.query_to_df(nav_query, cn_name) return fund_nav
def manager_performance(self, periods=['SI', '1Y', '2Y', '3Y', '5Y']): """ Analysis of mutual fund manager products. By default, we calculate 'SI'、 '1Y'、 '2Y'、 '3Y'、 '5Y' performance and integer year like '2019'、'2018' and so on. Parameters --------- periods : list time range list.This function will analyze products in different time range. Returns ------- results_df : pd.DataFrame indicators of all the products of the manager in different time range. Examples -------- >>> name = "乐无穹" >>> name = "刘彦春" >>> fm = FundManager(name) >>> mg_pro_nav = fm.products_nav(fund_name=False) >>> mg_fund_bm_info = MF.bm_index_info(fm.codes, cn_name=False) >>> mg_bm_nav = MF.bm_index_nav(fm.codes, fund_name=False) >>> mg_ana = fm.manager_performance() >>> mg_ana = fm.manager_performance(periods = ['SI', '1Y', '2Y']) """ # mg_pro_codes = self.manager_products().F_INFO_WINDCODE.unique().tolist() mg_pro_nav = self.products_nav(fund_name=False) mg_pro_nav['DATETIME'] = DateUtil.parse_dates(mg_pro_nav['DATETIME']) mg_nav_instance = Nav(mg_pro_nav, freq='weekly') mg_fund_bm_info = MF.bm_index_info(self.codes, cn_name=False) mg_fund_bm_map = dict( zip(mg_fund_bm_info['F_INFO_WINDCODE'], mg_fund_bm_info['BENCHMARK_WINDCODE'])) mg_bm_nav = MF.bm_index_nav(self.codes, fund_name=False) mg_bm_nav['DATETIME'] = DateUtil.parse_dates(mg_bm_nav['DATETIME']) mg_bm_rbs = Nav(mg_bm_nav, freq='daily').ret() mg_info_query = sess_wind.query(MfManager).filter_by( F_INFO_FUNDMANAGER=self.manager_name) mg_info = DataBase.query_to_df(mg_info_query) manage_start_date = parse( min([ x for x in mg_info['F_INFO_MANAGER_STARTDATE'] if x is not None ])).date() yearly_date = pd.date_range(manage_start_date, DateUtil.add_date( datetime.datetime.today(), '+1y'), freq='Y') year = list(map(lambda date: str(date.year), yearly_date)) periods += year results_dict = mg_nav_instance.cal_metrics(rbs=mg_bm_rbs, bm_codes=mg_fund_bm_map, periods=periods) results_df = PdUtil.select(pd.DataFrame(results_dict), MetricsMap.metrics_cn_name) results_df.replace( { '产品名称': dict( zip(mg_fund_bm_info['F_INFO_WINDCODE'], mg_fund_bm_info['F_INFO_NAME'])) }, inplace=True) results_df.replace( { '基准': dict( zip(mg_fund_bm_info['BENCHMARK_WINDCODE'], mg_fund_bm_info['F_INFO_BENCHMARK'])) }, inplace=True) return results_df
from wm.data_util import DataUtil from wm.decorator import parse_df_date from sqlalchemy import Column, String, Numeric, DateTime, Table, func from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm.query import Query from sqlalchemy.orm import Load, load_only from dateutil.parser import parse import datetime import re import pandas as pd import empyrical from functools import partial Base = declarative_base() db_wind = DataBase('wind') engine_wind = db_wind.db_engine() sess_wind = db_wind.db_session() "Part of the code written." class DbWind: """ Query API for wind database. """ @staticmethod def mf_manager(): """ Query china mutual fund manager Returns