def read_stock_history():
from qdata.dbmanager import SqlManager
sql_ = """
select p.infocode, marketdate, open_ * a.CumAdjFactor as open_, high * a.CumAdjFactor as high_, low * a.CumAdjFactor as low_, close_ * a.CumAdjFactor as close_, volume / a.CumAdjFactor as volume_
from qai..ds2primqtprc P
join (
select a.infocode, a.AdjDate, isnull(a.EndAdjDate, '9999-12-31') as endadjdate, a.CumAdjFactor
from qai..ds2adj A
where a.infocode in (46238,46712,36100,39988,46171,46113,40142,39985,46244,52853,51959,63649,46669,72983,50565,66846,52588,46415)
and a.adjtype = 2
) A
on p.infocode = a.infocode
and p.marketdate >= a.AdjDate and p.MarketDate <= a.EndAdjDate
where p.infocode in (46238,46712,36100,39988,46171,46113,40142,39985,46244,52853,51959,63649,46669,72983,50565,66846,52588,46415)
and p.marketdate >= '2000-01-01'
order by p.infocode, MarketDate
"""
sqlm = SqlManager()
df = sqlm.db_read(sql_)
item_list = ['open_', 'high_', 'low_', 'close_', 'volume_']
infocode = list(df.infocode.unique())
marketdate = list(df.marketdate.unique())
df_to_arr = np.zeros([len(df.marketdate.unique()), len(df.infocode.unique()), len(item_list)])
for i, item in enumerate(item_list):
df_to_arr[:, :, i] = pd.pivot_table(df, index='marketdate', columns='infocode', values=item)
history = df_to_arr[~np.isnan(np.sum(df_to_arr, axis=(1, 2))), :, :]
return history, infocode, marketdate, item_list
class IO:
def __init__(self):
self.sqlm = SqlManager()
def is_exist_table(self, db_name, table_id):
self.sqlm.set_db_name(db_name=db_name)
table_schema = self.sqlm.db_read(
"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=N'{}'".
format(table_id))
if len(table_schema.values) == 0:
return False
else:
return True
def drop_table(self, table_nm, db_name='qpipe'):
self.sqlm.db_execute("drop table {}..{}".format(db_name, table_nm))
print("Table {} deleted.".format(table_nm))
def create_table(self,
table_nm,
table_structure,
db_name='qpipe',
truncate_table=False):
"""
:param table_structure: format: {'columns': list of tuples for columns (col_name, col_type), 'pk':list of pk}
:return: boolean (success or failure)
"""
self.sqlm.set_db_name(db_name=db_name)
if self.is_exist_table(db_name, table_nm):
if truncate_table is False:
print(
"Table {} already exists in DB {}. You should set 'truncate_table=True' or rename it."
.format(table_nm, db_name))
return False
else:
sql_ = "truncate table {}".format(table_nm)
print("Table {} truncated.".format(table_nm))
else:
# table_structure: {'columns': list of tuples for columns (col_name, col_type), 'pk':list of pk}
table_struct_code = ""
for (col_nm, col_type) in table_structure['columns']:
table_struct_code += col_nm + " " + col_type + ",\n"
if 'pk' in table_structure.keys():
table_struct_code += "primary key(" + ", ".join(
table_structure['pk']) + ")"
sql_ = "create table {name} ({structure})".format(
name=table_nm, structure=table_struct_code)
print("Table {} created.".format(table_nm))
self.sqlm.db_execute(sql_)
return True
def load(self, *args, **kwargs):
raise NotImplementedError
def store(self, *args, **kwargs):
raise NotImplementedError
def _gen_code(self, freq_=None, interval=None, days=None, months=None):
sqlm = SqlManager()
sqlm.set_db_name('qinv')
sql_ = "select qinv.dbo.FS_ScheduleCodeMaker({}, {}, {}, {}, {}, {}, {})".format(
*tuple(
map(obj_to_dbformat, [
self.begin_d, self.end_d, self.type_, freq_, interval,
days, months
])))
code = sqlm.db_read(sql_)
code = code.values[0][0]
return code
def factor_history():
from qdata.dbmanager import SqlManager
sql_ = """
select *
from (
select idxcd, d.eval_d, round(clsprc / lag(clsprc, 1) over (partition by idxcd order by base_d) - 1, 5) as value_
from (
select eval_d
from qinv..DateTableGlobal
where region = 'KR'
and eval_d = work_d
and eval_d >= '2005-02-01'
) D
join wms..indexdata a
on d.eval_d = convert(date, a.base_d)
where idxcd in ('KOSPI','MOM','BEME','GPA','USDKRW','KISCOMPBONDCALL')
and base_d >= '20050201'
) A
pivot (
min(value_)
for idxcd in ([KOSPI],[MOM],[BEME],[GPA],[USDKRW],[KISCOMPBONDCALL])
) B
order by eval_d
"""
sqlm = SqlManager()
df = sqlm.db_read(sql_)
df.columns = [i.lower() for i in df.columns]
df.set_index('eval_d', inplace=True)
df = df[df.isna().sum(axis=1) == 0]
factor_id = list(df.columns)
marketdate = list(df.index.unique())
history = df.values
# history = df_to_arr[~np.isnan(np.sum(df_to_arr, axis=1)), :]
return history, factor_id, marketdate
def get_item(self, name, **kwargs):
item_id = kwargs.get('item_id', None)
sqlm = SqlManager()
sqlm.set_db_name('qpipe')
table_id = self.pipeline[name].table_id
if item_id is None:
item_data = sqlm.db_read("select * from {}".format(table_id))
else:
if item_id not in self.pipeline[name].item.keys():
print('No such item exists')
return None
item_data = sqlm.db_read(
"select * from {} where item_nm='{}'".format(
table_id, item_id))
if item_data.empty:
print('You should [run] first.')
return None
return item_data
import numpy as np
import pandas as pd
from subprocess import check_output
from keras.layers import Dense, Activation, LSTM, Dropout
from keras.models import Model, Sequential
from sklearn.cross_validation import train_test_split
import time
from sklearn.preprocessing import MinMaxScaler, StandardScaler
import matplotlib.pyplot as plt
from numpy import newaxis
from qdata.dbmanager import SqlManager
sqlm = SqlManager()
sql_ = """
select MarketDate as date_, open_ * a.CumAdjFactor as open_
, high * a.CumAdjFactor as high_, low * a.CumAdjFactor as low_, close_ * a.CumAdjFactor as close_, volume / a.CumAdjFactor as volume_
from qai..ds2primqtprc p
join qai..ds2adj a
on p.infocode = a.infocode
and p.MarketDate between a.AdjDate and isnull(a.endadjdate, '9999-12-31')
where p.infocode = 40853 and a.adjtype = 2
order by date_
"""
df = sqlm.db_read(sql_)
close_ = df.close_.values.astype('float32').reshape(-1, 1)
plt.plot(close_)
plt.show()
for i_mon, mon in enumerate(sorted(return_months)):
if i_mon == 0:
# 가장 짧은 주기 기준으로 null 값 제거
base_item = mon
return_ftn = partial(pd.Series.pct_change, periods=mon)
returns['y_{}m'.format(mon)] = close_adj.groupby(
'infocode')['value_'].transform(return_ftn)
# returns = returns[~returns['y_{}m'.format(base_item)].isnull()] # base_item에 해당하는 month 기준으로 null제거
returns = returns[np.sum(returns.isnull(), axis=1) == 0] # null 하나라도 있으면 전부 제거
sch = sorted(list(set(returns.index.get_level_values('eval_d'))))
from qdata.dbmanager import SqlManager
sqlm = SqlManager()
sqlm.set_db_name('qinv')
fundamental_data = None
fundamental_label = None
for i in range(48, len(sch) - 12):
if i % 20 == 0:
print(i)
t_next = sch[i + 12]
t = sch[i]
# universe
cond_sch = (close_['eval_d'] == t)
univ_code = close_[close_['eval_d'] == t][['infocode']]
for j in range(1, 5):
def __init__(self):
self.sqlm = SqlManager()