def get_locked_shares(stock_list, start_date=None, end_date=None, forward_count=None): ''' 获取指定日期范围内的个股限售股解禁数据 :param stock_list:单个股票或股票代码的列表 :param start_date: 开始日期 :param end_date: 结束日期 :param forward_count: 交易日数量,与 end_date 不能同时使用。与 start_date 配合使用时, 表示获取 start_date 到 start_date+count-1个交易日期间的数据 :return: dataframe |date|stock_code|num|rate1|rate2| |----------|-----------|--------|----|----| |2017-07-01|000001.XSHG|20000000|0.03|0.02| |2017-07-01|000001.XSHG|20000000|0.03|0.02| #### 注意单日个股多条解禁数据的问题 #### ''' import pandas as pd from six import StringIO from ..utils.utils import convert_date, is_lists from ..db_utils import query, request_mysql_server if forward_count is not None and end_date is not None: raise ParamsError("get_locked_shares 不能同时指定 end_date 和 forward_count 两个参数") if forward_count is None and end_date is None: raise ParamsError("get_locked_shares 必须指定 end_date 或 forward_count 之一") start_date = convert_date(start_date) if not is_lists(stock_list): stock_list = [stock_list] if stock_list: stock_list = [s.split('.')[0] for s in stock_list] if forward_count is not None: end_date = start_date + datetime.timedelta(days=forward_count) end_date = convert_date(end_date) q = query(StkLockShares.day, StkLockShares.code, StkLockShares.num, StkLockShares.rate1, StkLockShares.rate2).filter(StkLockShares.code.in_(stock_list), StkLockShares.day <= end_date, StkLockShares.day >= start_date ).order_by(StkLockShares.day).order_by(StkLockShares.code.desc()) sql = compile_query(q) cfg = get_config() if os.getenv('JQENV') == 'client': # 客户端 csv = request_mysql_server(sql) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError( "you must config FUNDAMENTALS_SERVERS for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY, is_random=False) csv = sql_runner.run(sql, return_df=False) dtype_dict = {} dtype_dict['code'] = str df = pd.read_csv(StringIO(csv), dtype=dtype_dict) return df pass
def get_billboard_list(stock_list=None, start_date=None, end_date=None, count=None): ''' 返回执指定日期区间内的龙虎榜个股列表 :param stock_list:单个股票或股票代码列表, 可以为 None, 返回股票的列表。 :param start_date: 开始日期 :param end_date: 结束日期 :param count: 交易日数量,与 end_date 不能同时使用。与 start_date 配合使用时, 表示获取 start_date 到 start_date+count-1个交易日期间的数据 :return:Dataframe | date | stock_code | abnormal_code | abnormal_name | sales_depart_name | abnormal_type | buy_value | buy_rate | sell_value | sell_rate | net_value | amount | |----------|------------|---------------|--------------------------|-------------------|---------------|-----------|----------|------------|-----------|-----------|--------| |2017-07-01| 000038.XSHE| 1 |日价格涨幅偏离值达7%以上的证券| None | ALL | 35298494 |0.37108699| 32098850 | 0.33744968| 3199644 |95121886| ''' import pandas as pd from ..utils.utils import convert_date, is_lists from ..db_utils import query, request_mysql_server if count is not None and start_date is not None: raise ParamsError("get_billboard_list 不能同时指定 start_date 和 count 两个参数") if count is None and start_date is None: raise ParamsError("get_billboard_list 必须指定 start_date 或 count 之一") end_date = convert_date(end_date) if end_date else datetime.date.today() start_date = convert_date(start_date) if start_date else \ (get_trade_days(end_date=end_date, count=count)[0] if count else TRADE_MIN_DATE) if not is_lists(stock_list): if stock_list is not None: stock_list = [stock_list] if stock_list: stock_list = [s.split('.')[0] for s in stock_list] q = query(StkAbnormal).filter(StkAbnormal.day <= end_date, StkAbnormal.day >= start_date) if stock_list is not None: q = q.filter(StkAbnormal.code.in_(stock_list)) q = q.order_by(StkAbnormal.day.desc()).order_by(StkAbnormal.code.desc()) sql = compile_query(q) cfg = get_config() if os.getenv('JQENV') == 'client': # 客户端 csv = request_mysql_server(sql) dtype_dict = {} dtype_dict['code'] = str df = pd.read_csv(six.StringIO(csv), dtype=dtype_dict) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError( "you must config FUNDAMENTALS_SERVERS for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY, is_random=False) df = sql_runner.run(sql, return_df=True) return df pass
def get_fundamentals(query_object=None, date=None, statDate=None, sql=None): # noqa if query_object is None and sql is None: raise ParamsError("get_fundamentals 至少输入 query_object 或者 sql 参数") cfg = get_config() if date: date = convert_date(date) if query_object: sql = fundamentals_query_to_sql(query_object, date, statDate) check_string(sql) if os.getenv('JQENV') == 'client': # 客户端 from jqdata.db_utils import request_mysql_server csv = request_mysql_server(sql) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError("you must config FUNDAMENTALS_SERVERS for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY, is_random=False) # return csv 在转成 DataFrame, 跟kaunke保持兼容, 防止直接return df 跟以前不一样 csv = sql_runner.run(sql, return_df=False) return pd.read_csv(StringIO(csv))
def get_fundamentals_continuously(query_object=None, end_date=None, count=1): ''' query_object:查询对象 end_date:查询财务数据的截止日期 count:查询财务数据前溯天数,默认为1 返回一个pd.Panel, 三维分别是 field, date, security. field: 下面的表的中属性 https://www.joinquant.com/data/dict/fundamentals ''' if query_object is None: raise ParamsError("get_fundamentals_continuously 需要输入 query_object 参数") if end_date is None: end_date = datetime.date.today() cfg = get_config() trade_day = get_trade_days(end_date=end_date, count=count) if query_object: sql = fundamentals_continuously_query_to_sql(query_object, trade_day) check_string(sql) # 调用查询接口生成CSV格式字符串 if os.getenv('JQENV') == 'client': # 客户端 from jqdata.db_utils import request_mysql_server csv = request_mysql_server(sql) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError("you must config FUNDAMENTALS_SERVER for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY, is_random=False) csv = sql_runner.run(sql, return_df=False) # 转换成panel,设置时间和股票code为索引 df = pd.read_csv(StringIO(csv)) df = df.drop_duplicates() newdf = df.set_index(['day', 'code']) pan = newdf.to_panel() return pan pass
def get_valuation(security_list, end_date, fields, start_date=None, count=None): """ 返回一个pd.Panel, 三维分别是 field, date, security. security_list/start_date/end_date/count的含义同get_price field: 下面的表的中属性 https://www.joinquant.com/data/dict/fundamentals#市值数据 底层通过转换成sql语句查询数据库实现 """ import pandas as pd from six import StringIO from ..utils.utils import convert_date, is_lists from ..db_utils import query, request_mysql_server if count is not None and start_date is not None: raise ParamsError("get_valuation 不能同时指定 start_date 和 count 两个参数") if count is None and start_date is None: raise ParamsError("get_valuation 必须指定 start_date 或 count 之一") if 'code' in fields or 'day' in fields: raise ParamsError("get_valuation fields 不能查询 code 和 day字段") end_date = convert_date(end_date) if not is_lists(security_list): security_list = [security_list] if not is_lists(fields): fields = [fields] val_fields = [] for field in fields: val_fields.append(getattr(valuation, field)) if count is not None: count = int(count) q = query(valuation.code, valuation.day, *val_fields).filter( valuation.code.in_(security_list), valuation.day < end_date ).order_by(valuation.day.desc()).limit(count) else: start_date = convert_date(start_date) q = query(valuation.code, valuation.day, *val_fields).filter( valuation.code.in_(security_list), valuation.day < end_date, valuation.day > start_date, ).order_by(valuation.day.desc()) sql = compile_query(q) cfg = get_config() if os.getenv('JQENV') == 'client': # 客户端 csv = request_mysql_server(sql) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError( "you must config FUNDAMENTALS_SERVERS for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY, is_random=False) csv = sql_runner.run(sql, return_df=False) df = pd.read_csv(StringIO(csv)) newdf = df.set_index(['code', 'day']) return newdf.to_panel()
def get_money_flow(security_list, start_date=None, end_date=None, fields=None, count=None): """ 获取资金流向数据 security_list: 股票代码或者 list start_date: 开始日期, **与 count 二选一,不可同时使用**. str/datetime.date/datetime.datetime 对象, 默认为平台提供的数据的最早日期 end_date: 结束日期, str/datetime.date/datetime.datetime 对象, 默认为 datetime.date.today() fields: 字段名或者 list, 可选, 默认全部字段 count: 数量, **与 start_date 二选一,不可同时使用**. 表示返回 end_date 之前 count 个交易日的数据, 包含 end_date 返回pd.DataFrame, columns: 日期, 股票代码, 涨跌幅(%), 主力净额(万), 主力净占比(%), 超大单净额(万), 超大单净占比(%), 大单净额(万), 大单净占比(%), 中单净额(万), 中单净占比(%), 小单净额(万), 小单净占比(%) date, sec_code, change_pct, net_amount_main, net_pct_main, net_amount_xl, net_pct_xl, net_amount_l, net_pct_l, net_amount_m, net_pct_m, net_amount_s, net_pct_s """ import pandas as pd from ..db_utils import query, request_mysql_server # 参数处理 if start_date and count: raise ParamsError("start_date 参数与 count 参数只能二选一") if not (count is None or count > 0): raise ParamsError("count 参数需要大于 0 或者为 None") if count: count = int(count) security_list = obj_to_tuple(security_list) check_string_list(security_list) if security_list: security_list = [s.split('.')[0] for s in security_list] end_date = convert_date(end_date) if end_date else datetime.date.today() start_date = convert_date(start_date) if start_date else \ (get_trade_days(end_date=end_date, count=count)[0] if count else TRADE_MIN_DATE) keys = ["date", "sec_code", "change_pct", "net_amount_main", "net_pct_main", "net_amount_xl", "net_pct_xl", "net_amount_l", "net_pct_l", "net_amount_m", "net_pct_m", "net_amount_s", "net_pct_s"] if fields: fields = obj_to_tuple(fields) check_string_list(fields) check_fields(keys, fields) else: fields = keys # 开始查询 start_date = convert_date(start_date) end_date = convert_date(end_date) q = query(StkMoneyFlow).filter(StkMoneyFlow.sec_code.in_(security_list)).filter( StkMoneyFlow.date >= start_date).filter(StkMoneyFlow.date <= end_date) sql = compile_query(q) cfg = get_config() if os.getenv('JQENV') == 'client': # 客户端 csv = request_mysql_server(sql) else: if not cfg.FUNDAMENTALS_SERVERS: raise RuntimeError( "you must config FUNDAMENTALS_SERVERS for jqdata") sql_runner = get_sql_runner( server_name='fundamentals', keep_connection=cfg.KEEP_DB_CONNECTION, retry_policy=cfg.DB_RETRY_POLICY) csv = sql_runner.run(sql, return_df=False) dtype_dict = {} dtype_dict['sec_code'] = str df = pd.read_csv(StringIO(csv), dtype=dtype_dict) df['date'] = pd.to_datetime(df['date']) df = df.loc[:, list(fields)] return df pass