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