def inner(start_time, end_time):
if table_name in ['ISY', 'CFSY'] and (freq == 2 or freq == 1):
raise ValueError('Incompatible parameters(table_name={tn}, freq={f}'.\
format(tn=table_name, f=freq))
start_time, end_time = trans_date(start_time, end_time)
start_time_shifted = get_calendar('stock.sse').\
shift_tradingdays(start_time, -(66 * (offset + 2) * freq))
cols = ['symbol', 'data', 'total_share', 'update_date', 'rpt_date']
data_fetcher = data_fetcher_factory(
sql, cols, jydb, {
'data': data_name_sql,
'table_name_sql': table_map[table_name],
'if_adjusted': if_adjusted
}, {
'data': 'float64',
'total_share': 'float64'
})
raw_data = data_fetcher(start_time_shifted, end_time)
raw_data['total_share'] = raw_data.groupby(
'symbol').total_share.fillna(method='ffill')
raw_data['data'] = raw_data['data'] / raw_data['total_share']
raw_data = raw_data.drop('total_share', axis=1).\
drop_duplicates(['symbol', 'update_date', 'rpt_date'])
if table_name in ['ISY', 'CFSY']: # 当前利润表和现金流量表计算季度偏移时仅会计算单季度数据
is_signal_season = True
else:
is_signal_season = False
return shift_processor(raw_data,
['symbol', 'data', 'update_date', 'rpt_date'],
start_time, end_time, offset, freq,
is_signal_season)
def get_jydb_tds(start_time, end_time, market_type=83):
'''
从聚源数据库中获取交易日数据
Parameter
---------
start_time: datetime like
开始时间
end_time: datetime like
结束时间
market_type: int, default 83
市场类型，具体参照聚源数据库QT_TradingDayNew文档，默认83表示上交所和深交所
Return
------
out: pandas.Series
结果包含首尾(若首尾为交易日)
'''
sql = '''
SELECT TradingDate
FROM QT_TradingDayNew
WHERE
IfTradingDay = 1 AND
SecuMarket = {mt} AND
TradingDate > \'{st:%Y-%m-%d}\' AND
TradingDate < \'{et:%Y-%m-%d}\'
'''
start_time, end_time = trans_date(start_time, end_time)
sql = sql.format(mt=market_type, st=start_time, et=end_time)
data = jydb.fetchall(sql)
out = transform_data(data, ['trading_days']).iloc[:, 0].tolist()
return out
def get_trade_status(start_time, end_time):
'''
获取股票的交易状态
Notes
-----
将成交量为0或者最高价等于最低价视为不能交易，返回值为1表示正常交易，0表示不能交易，NA表示未上市而不能交易
'''
sql = '''
SELECT S.TradingDay, S.TurnoverVolume, S.HighPrice, S.LowPrice, M.Secucode
FROM QT_DailyQuote S, SecuMain M
WHERE
S.InnerCode = M.InnerCode AND
M.SecuMarket in (83, 90) AND
S.TradingDay <= \'{end_time:%Y-%m-%d}\' AND
S.TradingDay >= \'{start_time:%Y-%m-%d}\' AND
M.SecuCategory = 1
ORDER BY M.Secucode ASC, S.TradingDay ASC
'''
start_time, end_time = trans_date(start_time, end_time)
offset = 30
start_time_shifted = get_calendar('stock.sse').shift_tradingdays(
start_time, -offset - 10)
sql = sql.format(start_time=start_time_shifted, end_time=end_time)
data = fetch_db_data(jydb,
sql, ['time', 'vol', 'high', 'low', 'symbol'],
dtypes={
'vol': 'float64',
'high': 'float64',
'low': 'float64'
})
data.symbol = data.symbol.apply(add_stock_suffix)
ma_vol = data.groupby('symbol', as_index=False).vol.rolling(
offset, min_periods=offset).mean()
ma_vol = ma_vol.reset_index(level=0, drop=True)
data = data.assign(ma_vol=ma_vol)
data = data.assign(flag=1)
# pdb.set_trace()
data.loc[np.isclose(data.ma_vol, 0, 0.1),
'ma_vol'] = np.nan # (上个交易日)移动平均成交量过低
data.ma_vol = data.vol / data.ma_vol
data.loc[np.isclose(data.vol, 0, 0.1), 'flag'] = 0 # 成家量过低，不可交易
data.loc[data.high == data.low, 'flag'] = 0 # 最高价等于最低价，不可交易
# 移动平均成交量过低，不可交易
data.loc[np.isclose(data.ma_vol, 0, 0.05) | (pd.isnull(data.ma_vol)),
'flag'] = 0
data = data.pivot_table('flag', index='time', columns='symbol')
last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(
pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index)
data = data.loc[(data.index >= start_time)
& (data.index <= end_time)].reindex(columns=universe)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Error, data missed!')
return data
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
cache_size = 100
offset = major_mul * minor_mul
threshold = 1e-5
start_time_shifted = get_calendar('stock.sse').\
shift_tradingdays(start_time, -offset - 20)
to_data = pitcache_getter('TO_RATE', cache_size).\
get_tsdata(start_time_shifted, end_time)
data = to_data.rolling(offset,
min_periods=offset).sum().dropna(how='all')
data[data <= threshold] = np_nan
data = data / major_mul
data = np_log(data)
data = data.loc[(data.index >= start_time) & (data.index <= end_time)]
if start_time > trans_date(DATA_START_DATE):
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
start_time_shifted = get_calendar('stock.sse').shift_tradingdays(start_time, -(lag+1))
ret_data = pitcache_getter(ret_source, lag+1).get_tsdata(start_time_shifted, end_time)
data = ret_data.rolling(lag, min_periods=lag).std()
latest_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(latest_td).index)
data = data.reindex(columns=universe)
data = data.loc[(data.index>=start_time) & (data.index<=end_time)]
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
if not check_jydb_update_state(end_time):
raise ValueError('JYDB has not been updated!')
sql = '''
SELECT S.{field}, S.TradingDay
FROM QT_IndexQuote S, SecuMain M
WHERE
S.InnerCode = M.InnerCode AND
M.SecuCode = \'{symbol}\' AND
M.SecuCategory = 4 AND
S.TradingDay >= \'{start_time:%Y-%m-%d}\' AND
S.TradingDay <= \'{end_time:%Y-%m-%d}\'
ORDER BY S.TradingDay ASC
'''.format(symbol=index_symbol, start_time=start_time, end_time=end_time, field=field)
data = fetch_db_data(jydb, sql, ['data', 'time'], dtypes={'data': 'float64'})
data = data.set_index('time').data
if (start_time != trans_date(DATA_START_DATE) and
not check_completeness(data.index, start_time, end_time)):
raise ValueError('Data Missed!')
return data
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
shifted_start_time = get_calendar('stock.sse').shift_tradingdays(start_time, -period-1)
ret_data = pitcache_getter('CLOSE_DRET', 10).get_tsdata(shifted_start_time, end_time)
data = ret_data.rolling(period, min_periods=period).apply(func)
data = data.loc[(data.index >= start_time) & (data.index <= end_time)]
last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index)
data = data.reindex(columns=universe)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
shifted_start_time = get_calendar('stock.sse').shift_tradingdays(start_time, -start_t-1)
period = start_t - end_t
price_data = pitcache_getter('CLOSE_ADJ', 10).get_tsdata(shifted_start_time, end_time)
data = price_data.shift(end_t).pct_change(period)
data = data.loc[(data.index >= start_time) & (data.index <= end_time)]
last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index)
data = data.reindex(columns=universe)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def xps_shift_year(symbol, date, data_name, tab_name, offset):
# 计算年度偏移后的XPS指标
symbol = drop_suffix(symbol)
date = trans_date(date)
if tab_name == 'LC_BalanceSheetAll':
if_adjusted = ''
else:
if_adjusted = 'IfAdjusted NOT IN (4, 5) AND'
sql = '''
SELECT m.SecuCode, s.TotalShares, s.{data}, s.enddate, s.infopubldate
FROM
(select s1.EndDate, s1.InfoPublDate, s1.{data}, s1.CompanyCode, s1.rnum, s2.TotalShares from
(SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfAdjusted, IfMerged, ROW_NUMBER()
OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name}
WHERE
BulletinType != 10 AND
InfoPublDate < \'{date:%Y-%m-%d}\' AND
{if_adjusted}
IfMerged = 1) s1 left outer join LC_ShareStru s2 on (s1.CompanyCode = s2.CompanyCode AND s1.EndDate = s2.EndDate)) s, SecuMain M
where
s.rnum = 1 AND
S.CompanyCode = M.CompanyCode AND
M.SecuCode = \'{symbol}\' AND
M.SecuCategory = 1 AND
M.SecuMarket IN (83, 90) AND
S.EndDate >= (SELECT TOP(1) S3.CHANGEDATE
FROM LC_ListStatus S3
WHERE
S3.INNERCODE = M.INNERCODE AND
S3.ChangeType = 1)
ORDER BY S.EndDate ASC
'''.format(data=data_name,
tab_name=tab_name,
date=date,
symbol=symbol,
if_adjusted=if_adjusted)
data = fetch_db_data(
jydb, sql,
['symbol', 'total_share', 'data', 'rpt_date', 'update_time'], {
'data': 'float64',
'total_share': 'float64'
})
data['total_share'] = data.total_share.fillna(method='ffill')
if len(data) < 1:
return np.nan
data = data.loc[data.rpt_date.dt.month == 12]
if len(data) < offset or get_calendar('stock.sse').count(
data.iloc[-1, -1], date) > 120:
return np.nan
return data.iloc[-offset, 2] / data.iloc[-offset, 1]
def get_suspend_state(start_time, end_time):
"""
获取股票停牌状态
Notes
-----
其中，0表示未停牌，1表示整日停牌，2表示日内部分交易时间停牌
"""
start_time, end_time = trans_date(start_time, end_time)
sql = '''
SELECT M.SecuCode, S.SuspendDate, S.ResumptionDate
FROM LC_SuspendResumption S, SecuMain M
WHERE
S.InnerCode = M.InnerCode AND
M.SecuMarket in (83, 90) AND
(S.ResumptionDate >= \'{start_time: %Y-%m-%d}\' OR
s.ResumptionDate = '1900-01-01') AND
S.suspenddate <= \'{end_time: %Y-%m-%d}\' AND
M.SecuCategory = 1
ORDER BY M.Secucode ASC, S.SuspendDate ASC
'''.format(start_time=start_time, end_time=end_time)
data = fetch_db_data(jydb, sql,
['symbol', 'suspend_date', 'resumption_date'])
data.symbol = data.symbol.apply(add_stock_suffix)
tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time)
def process_per_symbol(df):
state = {}
for _, line in df.iterrows():
_, start, end = line
if start < end: # 正常超过一个交易日的停牌
state[start] = 1
state[end] = 0
elif end == pd.to_datetime('1900-01-01'): # 停牌至计算日还未复牌
state[start] = 1
else: # 日内停牌
state[start] = 2
state = pd.Series(state).sort_index()
return state
data = data.groupby('symbol').apply(process_per_symbol).unstack().T
data = data.fillna(method='ffill')
data = map2td(data, tds)
latest_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(
pitcache_getter('UNIVERSE', 10).get_csdata(latest_td).index)
data = data.reindex(columns=universe).fillna(0)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def ttm_db(symbol, date, data_name, tab_name):
# 从SQL数据库中取出ttm数据
# symbol: 测试股票代码，带后缀
# date: 测试数据的时间
# data_name: 数据名称(通过聚源查询)
# tab_name: 表名称，仅支持[LC_IncomeStatementAll, LC_CashFlowStatementAll]
symbol = drop_suffix(symbol)
date = trans_date(date)
sql = '''
SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate
FROM
(SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfMerged, ROW_NUMBER()
OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name}
WHERE
BulletinType != 10 AND
InfoPublDate < \'{date:%Y-%m-%d}\' AND
IfMerged = 1
) s, SecuMain M
where
s.rnum = 1 AND
S.CompanyCode = M.CompanyCode AND
M.SecuCode = \'{symbol}\' AND
M.SecuCategory = 1 AND
M.SecuMarket IN (83, 90) AND
S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE
FROM LC_ListStatus S2
WHERE
S2.INNERCODE = M.INNERCODE AND
S2.ChangeType = 1)
ORDER BY S.EndDate ASC'''.format(data=data_name,
symbol=symbol,
date=date,
tab_name=tab_name)
data = fetch_db_data(jydb, sql,
['symbol', 'data', 'rpt_date', 'update_time'],
{'data': 'float64'})
# pdb.set_trace()
if len(data) <= 4 or get_calendar('stock.sse').count(
data.iloc[-1, 3], date) > 120:
if len(data) == 4:
if data['rpt_date'].iloc[0].month != 3:
return np.nan
else:
return np.nan
raw_data = data.set_index('rpt_date').data
seasonly_data = raw_data - raw_data.shift(1)
seasonly_data.loc[seasonly_data.index.month == 3] = np.nan
seasonly_data = seasonly_data.fillna(raw_data)
return seasonly_data.iloc[-4:].sum()
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
start_time_shift = get_calendar('stock.sse').shift_tradingdays(
start_time, -450)
cols = ['symbol', 'data', 'update_date', 'rpt_date']
data_fetcher = data_fetcher_factory(
sql, cols, jydb, {
'data': data_name_sql,
'table_name_sql': table_map[table_name]
}, {'data': 'float64'})
raw_data = data_fetcher(start_time_shift, end_time)
# pdb.set_trace()
raw_data = raw_data.drop_duplicates(
['symbol', 'update_date', 'rpt_date'])
return ttm_processor(raw_data, cols, start_time, end_time)
def bs_shift_db_year(symbol,
date,
offset,
data_name,
tab_name='LC_BalanceSheetAll'):
# 按年度进行偏移的资产负债表数据
# 计算资产负债表偏移后的数据
# symbol: 股票代码
# date: 测试数据时间
# data_name: 数据名称(查询聚源)
# offset: 数据时间偏移量，以季度为单位
# tab_name: 报表名称，默认为资产负债表
symbol = drop_suffix(symbol)
date = trans_date(date)
sql = '''
SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate
FROM
(SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfMerged, ROW_NUMBER()
OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name}
WHERE
BulletinType != 10 AND
InfoPublDate < \'{date:%Y-%m-%d}\' AND
IfMerged = 1
) s, SecuMain M
where
s.rnum = 1 AND
S.CompanyCode = M.CompanyCode AND
M.SecuCode = \'{symbol}\' AND
M.SecuCategory = 1 AND
M.SecuMarket IN (83, 90) AND
S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE
FROM LC_ListStatus S2
WHERE
S2.INNERCODE = M.INNERCODE AND
S2.ChangeType = 1)
ORDER BY S.EndDate ASC
'''.format(symbol=symbol, data=data_name, date=date, tab_name=tab_name)
data = fetch_db_data(jydb, sql,
['symbol', 'data', 'rpt_date', 'update_time'],
{'data': 'float64'})
if len(data) < 1: # 没有发不过年报
return np.nan
data = data.loc[data.rpt_date.dt.month == 12]
if len(data) < offset or get_calendar('stock.sse').count(
data.iloc[-1, 3], date) > 120:
return np.nan
# pdb.set_trace()
return data.iloc[-offset, 1]
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
start_time_shift = get_calendar('stock.sse').shift_tradingdays(start_time, -180)
nonlocal sql
sql = sql.format(code=index_symbol, start_time=start_time_shift, end_time=end_time)
data = fetch_db_data(jydb, sql, ['symbol', 'weight', 'time'], dtypes={'weight': 'float64'})
data.symbol = data.symbol.apply(add_stock_suffix)
tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time)
data = data.pivot_table('weight', index='time', columns='symbol')
data = map2td(data, tds)
last_td = get_calendar('stock.sse').latest_tradingday(end_time, 'PAST')
universe = sorted(pitcache_getter('UNIVERSE', 10).get_csdata(last_td).index)
data = data.reindex(columns=universe)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Error, data missed!')
return data
def process_fundamental_data(data, cols, start_time, end_time, max_hist_num,
func, **kwargs):
'''
处理从数据库中取出的基本面数据，按照给定的函数计算基本面数据，并将基本面数据映射到交易日中
Parameter
---------
data: pandas.DataFrame
原始数据，必须包含cols参数中的列名
cols: iterable
元素为列名，长度为4，依次表示的意思是[证券代码列, 数据列, 更新时间列, 报告期列]
start_time: datetime like
数据的开始时间
end_time: datetime like
数据的结束时间
max_hist_num: int
每个更新日期保存的历史最新数据的最大数量
func: function
格式为function(pandas.DataFrame)-> value，该函数接受的参数为每个股票在每个更新日期
的最新历史数据，包含cols参数中的列
kwargs: dict
以字典形式传给function的参数
Return
------
out: pandas.DataFrame
处理后生成的二维表，index为时间列，columns为股票代码列
'''
start_time, end_time = trans_date(start_time, end_time)
symbol_col, data_col, ut_col, rpt_col = cols
data = data.sort_values([symbol_col, ut_col])
tds = get_calendar('stock.sse').get_tradingdays(start_time, end_time)
def handler_per_symbol(df):
# 对单只证券进行处理
obs_data, obs_flag = expand_data(df, ut_col, rpt_col, max_hist_num)
res = obs_data.groupby(obs_flag).apply(func, **kwargs)
res = map2td(res, tds).reset_index().rename(columns={
obs_flag: 'time',
0: 'data'
})
return res
out = data.groupby(symbol_col).apply(handler_per_symbol).reset_index()
out = out.pivot_table('data', index='time', columns=symbol_col)
return out
def cshift_db(symbol, date, data_name, tab_name, offset):
# 计算流量类报表偏移后的数据
# symbol: 股票代码
# date: 测试数据时间
# data_name: 数据名称(查询聚源)
# tab_name: 报表名称(查询聚源)
# offset: 数据时间偏移量，以季为单位
symbol = drop_suffix(symbol)
date = trans_date(date)
sql = '''
SELECT m.SecuCode, s.{data}, s.enddate, s.infopubldate
FROM
(SELECT EndDate, InfoPublDate, {data}, CompanyCode, BulletinType, IfAdjusted, IfMerged, ROW_NUMBER()
OVER(PARTITION BY COMPANYCODE, ENDDATE ORDER BY INFOPUBLDATE DESC) as rnum FROM {tab_name}
WHERE
BulletinType != 10 AND
InfoPublDate < \'{date:%Y-%m-%d}\' AND
IfAdjusted NOT IN (4, 5) AND
IfMerged = 1) s, SecuMain M
where
s.rnum = 1 AND
S.CompanyCode = M.CompanyCode AND
M.SecuCode = \'{symbol}\' AND
M.SecuCategory = 1 AND
M.SecuMarket IN (83, 90) AND
S.EndDate >= (SELECT TOP(1) S2.CHANGEDATE
FROM LC_ListStatus S2
WHERE
S2.INNERCODE = M.INNERCODE AND
S2.ChangeType = 1)
ORDER BY S.EndDate ASC'''.format(data=data_name,
symbol=symbol,
date=date,
tab_name=tab_name)
data = fetch_db_data(jydb, sql,
['symbol', 'data', 'rpt_date', 'update_time'],
{'data': 'float64'})
# pdb.set_trace()
if len(data) < offset or get_calendar('stock.sse').count(
data.iloc[-1, 3], date) > 120:
return np.nan
raw_data = data.set_index('rpt_date').data
seasonly_data = raw_data - raw_data.shift(1)
seasonly_data.loc[seasonly_data.index.month == 3] = np.nan
seasonly_data = seasonly_data.fillna(raw_data)
return seasonly_data.iloc[-offset]
def inner(start_time, end_time):
start_time, end_time = trans_date(start_time, end_time)
nonlocal sql
sql = sql.format(start_time=start_time, end_time=end_time)
data = fetch_db_data(jydb, sql, cols, dtypes=dtypes)
data.symbol = data.symbol.apply(add_stock_suffix)
if len(cols) == 4: # 当前数据为需要使用前收盘填充的数据
data.loc[data.data == 0, 'data'] = data['prevclose']
data = data.drop('prevclose', axis=1)
data = data.pivot_table('data', index='time', columns='symbol')
latest_td = get_calendar('stock.sse').latest_tradingday(
end_time, 'PAST')
universe = sorted(
pitcache_getter('UNIVERSE', 10).get_csdata(latest_td).index)
data = data.reindex(columns=universe).sort_index(ascending=True)
if not check_completeness(data.index, start_time, end_time):
raise ValueError('Data missed!')
return data
def get_wind_tds(self, start_time, end_time):
'''
从Wind数据库中获取交易日数据
Parameter
---------
start_time: datetime
开始日期
end_time: datetime
结束日期
Return
------
out: list
元素为pandas.TimeStamp格式
'''
self._connect_wind()
start_time, end_time = trans_date(start_time, end_time)
out = self._wind_instance.tdays(start_time, end_time)
if out.ErrorCode == 0:
raise ValueError(out.Data[0][0])
return sorted(to_datetime(out.Data[0]))
def get_tsdata(self, start_time, end_time):
'''
获取时间序列数据(包含边界)
Parameter
---------
start_time: datetime like
end_time: datetime like
Return
------
out: pandas.Series or pandas.DataFrame
返回数据的形式取决于提供数据的函数。若提供数据的函数返回值为pandas.DataFram，则返回pandas.DataFrame，
反之返回pandas.Series
'''
start_time, end_time = trans_date(start_time, end_time)
if start_time >= end_time:
raise ValueError('Improper time parameter order!')
update_direction = self._check_data(start_time, end_time)
if update_direction != CacheStatus.ENOUGH:
self._update_cache(update_direction, left_date=start_time, right_date=end_time)
mask = (self._data_cache.index <= end_time) & (self._data_cache.index >= start_time)
return self._data_cache.loc[mask]
def get_db_update_time():
'''
以指数行情作为标志获取聚源数据库中最新数据的日期
Return
------
date: datetime like
更新时间，如果数据库没有数据，返回时间为1900-01-01
'''
sql = '''
SELECT S.XGRQ
FROM QT_IndexQuote S, SecuMain M
WHERE
S.InnerCode = M.InnerCode AND
M.SecuCode = '000001' AND
M.SecuCategory = 4 AND
S.TradingDay >= '2007-01-01'
ORDER BY S.XGRQ ASC
'''
data = fetch_db_data(jydb, sql, ['date'])
if len(data) <= 0:
return trans_date('1900-01-01')
else:
return data.date.iloc[-1]
def load_metadata(filename):
'''
从文件中加载并转换元数据，元数据的格式如下
{rel_path: update_time}
Parameter
---------
filename: string
元数据的名称，元数据写入到数据库的主文件夹下
Return
------
metadata: dict
转换后的元数据，update_time为datetime类
'''
dbpath = CONFIG['db_path']
file_path = join(dbpath, filename)
if not exists(file_path): # 不存在元数据文件，初始化
return {}
with open(file_path, 'r', encoding=ENCODING) as f:
metadata = json.load(f)
for d, v in metadata.items():
metadata[d] = trans_date(v)
return metadata
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-05-25 16:27 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription, pitcache_getter if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from derivate_template import divide_data_factory dd = DataDescription( divide_data_factory( pitcache_getter('EQUITY', 10).get_tsdata, pitcache_getter('TOTAL_CMKV', 10).get_tsdata), trans_date('2018-05-25'), DataType.PANEL_NUMERIC, ['EQUITY', 'TOTAL_CMKV', 'LIST_STATE'], '归属母公司的权益TTM/总市值')
#!/usr/bin/env python # -*- coding: utf-8 -*- # @Date : 2018-05-10 15:22:15 # @Author : Hao Li ([email protected]) # @Link : https://github.com/SAmmer0 # @Version : $Id$ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from stock_share_template import get_shares dd = DataDescription(get_shares('NonResiSharesJY'), trans_date('2018-05-10'), DataType.PANEL_NUMERIC, dep=['UNIVERSE', 'LIST_STATE'], desc='流通股本')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-05-25 15:46 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from index_template import get_index_weights dd = DataDescription(get_index_weights('000852'), trans_date('2018-05-25'), DataType.PANEL_NUMERIC, dep=['UNIVERSE'], desc='中证1000指数成分权重，数值表示权重((0, 100))，其他(NA)表示非指数成分')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-07-18 11:08 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription, pitcache_getter if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from derivate_template import growth_rate_factory dd = DataDescription(growth_rate_factory('EPS{i:d}Y', 5), trans_date('2018-07-18'), DataType.PANEL_NUMERIC, ['EPS{i:d}Y'.format(i=i) for i in range(1, 6)], desc='过去5年EPS增长率')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-05-23 16:47 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from fundamental_template import shift_factory dd = DataDescription(shift_factory('ISY', 'TotalOperatingRevenue', 1, 1), trans_date('2018-05-23'), DataType.PANEL_NUMERIC, dep=['UNIVERSE', 'LIST_STATE'], desc='最近一个季度的营业收入')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-06-07 15:06 """ from sys import path as sys_path from os.path import dirname import numpy as np from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription, pitcache_getter if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from derivate_template import divide_data_factory dd = DataDescription( divide_data_factory( pitcache_getter('NI_TTM', 10).get_tsdata, pitcache_getter('TOTAL_SHARES', 10).get_tsdata), trans_date('2018-06-07'), DataType.PANEL_NUMERIC, ['NI_TTM', 'TOTAL_SHARES'], 'Earnings per share TTM')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-05-22 13:48 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from quotes_template import lnmkv_factory dd = DataDescription(lnmkv_factory('TOTAL_CMKV'), trans_date('2018-05-22'), DataType.PANEL_NUMERIC, dep=['TOTAL_CMKV', 'LIST_STATE'], desc='对数总市值')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-07-17 13:34 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from fundamental_template import xps_shift_factory dd = DataDescription(xps_shift_factory('ISY', 'NPParentCompanyOwners', 2, 4), trans_date('2018-07-17'), DataType.PANEL_NUMERIC, dep=['UNIVERSE', 'LIST_STATE'], desc='上一个财年的EPS=上一个财年归属母公司的净利润/总股本')
#!/usr/bin/env python # -*- coding:utf-8 """ Author: Hao Li Email: [email protected] Github: https://github.com/SAmmer0 Created: 2018-05-24 11:00 """ from sys import path as sys_path from os.path import dirname from tdtools import trans_date from pitdata.const import CALCULATION_FOLDER_PATH, DataType from pitdata import DataDescription if CALCULATION_FOLDER_PATH not in sys_path: sys_path.append(dirname(CALCULATION_FOLDER_PATH)) from fundamental_template import shift_factory dd = DataDescription(shift_factory('ISY', 'NPParentCompanyOwners', 4, 4), trans_date('2018-05-24'), DataType.PANEL_NUMERIC, dep=['UNIVERSE', 'LIST_STATE'], desc='往前推4个财年的归属母公司净利润')
