def main_function(s_day, e_day):
logger.info('\n\n' + str(datetime.datetime.today()) + ' : ' + task_id + ' start...' + s_day + '-' +e_day)
conn = favis_util.get_favis_mysql_connection()
cur = conn.cursor()
df_sm = pd.read_sql_query('SELECT * FROM stock_info ORDER BY code ASC', conn)
starttime = datetime.datetime.now()
# day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
s_day = s_day.replace('-','')
e_day = e_day.replace('-','')
cnt =0
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(stock_code)
# logger.debug (stock_code +' ' + stock_name+' ' +isu_cd)
path = "/app/favis/collector/data/"
filename = path + stock_code + "_price.xls"
r = util.get_krx_daily_info(isu_cd, s_day, e_day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['년/월/일', '종가','거래량(주)','시가','고가','저가', '시가총액(백만)','상장주식수(주)'])
df.columns = ['date','close','volume','open','high','low', 'marcap','amount']
df['date'] = df['date'].str.replace('/','')
df_cp = df[['date','close','volume','open','high','low', 'marcap','amount']].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[['stock_code', 'date', 'open', 'high', 'low', 'close', 'volume', 'marcap', 'amount']]
#logger.debug(df_cp.head())
#data = [tuple(x) for x in df_cp.to_records(index=False)]
#logger.debug(df_cp[['stock_code', 'Date','Open','High','Low','Close','Volume','marcap','amount']].head())
for idx, row in df_cp.iterrows():
try:
cur.execute('INSERT INTO daily_info (code, date, open, high, low, close, volume, marcap, amount) ' \
'VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)', (row['stock_code'], row['date'], row['open'], row['high'], row['low'], row['close'], row['volume'], row['marcap'], row['amount']))
conn.commit()
except pymysql.IntegrityError:
pass
except pymysql.Error as e:
if conn:
conn.rollback()
logger.debug ("error %s" % e.args[0])
cnt = cnt +1
endtime = datetime.datetime.now()
logger.info('count :' + str(cnt) + ', elaspsedtime : ' + str(endtime - starttime))
logger.info(str(datetime.datetime.today()) + ' : ' + task_id + ' end...')
if conn:
conn.close()
def main_function(row):
s_day = sys.argv[1]
if len(sys.argv) == 2:
e_day = s_day
else:
e_day = sys.argv[2]
stock_code = row
logger.info('\n\n' + str(datetime.datetime.today()) + ' : price : ' +
stock_code + ' start...' + s_day + '-' + e_day)
starttime = datetime.datetime.now()
isu_cd = util.getIsinCode(stock_code)
# logger.debug (stock_code +' ' + stock_name+' ' +isu_cd)
path = "/app/favis/collector/data/"
filename = path + stock_code + "_price.xls"
r = util.get_krx_daily_info(isu_cd, s_day, e_day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename,
thousands=',',
usecols=[
'년/월/일', '종가', '거래량(주)', '시가', '고가', '저가',
'시가총액(백만)', '상장주식수(주)'
])
df.columns = [
'date', 'close', 'volume', 'open', 'high', 'low', 'marcap', 'amount'
]
df['date'] = df['date'].str.replace('/', '')
df_cp = df[[
'date', 'close', 'volume', 'open', 'high', 'low', 'marcap', 'amount'
]].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[[
'stock_code', 'date', 'open', 'high', 'low', 'close', 'volume',
'marcap', 'amount'
]]
df_cp.to_sql(name='daily_info',
con=engine,
if_exists='append',
index=False)
endtime = datetime.datetime.now()
logger.info('elaspsedtime : ' + str(endtime - starttime))
logger.info(str(datetime.datetime.today()) + ' : ' + task_id + ' end...')
def main_function(day):
print ("collect date : " + day)
# main
conn = fu.get_favis_mysql_connection()
cur = conn.cursor()
df_sm = pd.read_sql_query("SELECT code FROM stock_info WHERE CODE NOT IN (SELECT CODE FROM daily_stock_index WHERE DATE = '"+ day +"') ORDER BY code ASC", conn)
cnt = 0
print (datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
for idx, row in df_sm.iterrows():
stock_code = row['code']
#print (row['code'], util.getIsinCode(row['code']))
if (cnt%100 == 0):
print(str(cnt), end=',', flush=True)
isu_cd = util.getIsinCode(stock_code)
r = util.get_krx_daily_stock_index(isu_cd, 'A'+stock_code, day, day)
f = io.BytesIO(r)
df = pd.read_excel(f, thousands=',', usecols=['일자', '종목코드','EPS','PER','BPS','PBR','주당배당금','배당수익률'], converters={'종목코드':str})
df.columns = ['date','code','eps','per','bps','pbr', 'dividend','dividend_rate']
df['date'] = df['date'].str.replace('/','')
df['per'] = pd.to_numeric(df['per'], errors='coerce')
#print(str(cnt) + " " + stock_code + " : " + str(len(df)))
#data = [tuple(x) for x in df_cp.to_records(index=False)]
#df.set_index('Date', inplace=True)
#df = df.sort_index(0, ascending=True)
if len(df) == 0 :
print(stock_code +' ' + isu_cd+' ' + ' data not found!!')
else :
try:
df.to_sql(name='daily_stock_index', con=engine, if_exists = 'append', index=False)
cnt = cnt + 1
except exc.IntegrityError:
cnt = cnt + 1
pass
except Exception as e:
print ("error %s" % e.args[0])
#df = pd.read_sql_query('SELECT * FROM daily_info limit 5', conn)
#print(df.head())
#print(datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
if conn:
conn.close()
print(day + " : " + str(cnt))
def main_function(row):
s_day = sys.argv[1]
if len(sys.argv) == 2 :
e_day = s_day
else:
e_day = sys.argv[2]
stock_code = row
logger.info('\n\n' + str(datetime.datetime.today()) + ' : price : ' + stock_code + ' start...' + s_day + '-' +e_day)
starttime = datetime.datetime.now()
isu_cd = util.getIsinCode(stock_code)
# logger.debug (stock_code +' ' + stock_name+' ' +isu_cd)
path = "/app/favis/collector/data/"
filename = path + stock_code + "_price.xls"
r = util.get_krx_daily_info(isu_cd, s_day, e_day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['년/월/일', '종가','거래량(주)','시가','고가','저가', '시가총액(백만)','상장주식수(주)'])
df.columns = ['date','close','volume','open','high','low', 'marcap','amount']
df['date'] = df['date'].str.replace('/','')
df_cp = df[['date','close','volume','open','high','low', 'marcap','amount']].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[['stock_code', 'date', 'open', 'high', 'low', 'close', 'volume', 'marcap', 'amount']]
df_cp.to_sql(name='daily_info', con=engine, if_exists = 'append', index=False)
endtime = datetime.datetime.now()
logger.info('elaspsedtime : ' + str(endtime - starttime))
logger.info(str(datetime.datetime.today()) + ' : ' + task_id + ' end...')
if conn:
conn.close()
conn = sqlite3.connect(DB_STOCK_DAILY_INFO)
cur = conn.cursor()
print(datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
day = '20160527'
print(day)
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(row['code'])
print(row['code'], isu_cd)
path = "./data/"
filename = path + stock_code + "_price.xls"
r = util.get_krx_daily_info(isu_cd, day, day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename,
thousands=',',
usecols=[
'년/월/일', '종가', '거래량(주)', '시가', '고가', '저가',
'시가총액(백만)', '상장주식수(주)'
])
conn = sqlite3.connect(DB_STOCK_DAILY_INFO)
cur = conn.cursor()
print (datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
day = '20160527'
print(day)
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(row['code'])
print (row['code'], isu_cd)
path = "./data/"
filename = path + stock_code + "_price.xls"
r = util.get_krx_daily_info(isu_cd, day, day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['년/월/일', '종가','거래량(주)','시가','고가','저가', '시가총액(백만)','상장주식수(주)'])
df.columns = ['date','close','volume','open','high','low', 'marcap','amount']
df['date'] = df['date'].str.replace('/','')
df_cp = df[['date','close','volume','open','high','low', 'marcap','amount']].copy()
df_cp['stock_code'] = stock_code
query = 'SELECT * FROM stock_info '+ where_cond +' ORDER BY code ASC'
logger.debug(query)
df_sm = pd.read_sql_query(query, conn)
#day = datetime.datetime.today().strftime('%Y%m%d')
day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
#day = '20160603'
logger.debug(day)
cnt = 0
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(stock_code)
#logger.debug (stock_code, stock_name, isu_cd)
path = favis_path + "collector/data/"
filename = path + stock_code + "_sellbuy_trend.xls"
r = util.get_krx_daily_sellbuy_trend(isu_cd, day, day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['년/월/일', '기관_순매수(주)','외국인_순매수(주)'])
df = df.dropna()
df.columns = ['date','i_volume', 'f_volume']
df['date'] = df['date'].str.replace('/','')
df_cp = df[['date','i_volume', 'f_volume']].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[['stock_code', 'date','i_volume', 'f_volume']]
def main_function(day):
logger.info('\n\n' + str(datetime.datetime.today()) + ' : ' + task_id +
' start...')
starttime = datetime.datetime.now()
where_cond = ''
# if len(sys.argv) > 1:
# start_code = sys.argv[1]
# logger.debug(start_code)
# where_cond = "where code > '" + start_code + "'"
conn = fu.get_favis_mysql_connection()
cur = conn.cursor()
query = 'SELECT * FROM stock_info ' + where_cond + ' ORDER BY code ASC'
logger.debug(query)
df_sm = pd.read_sql_query(query, conn)
#day = datetime.datetime.today().strftime('%Y%m%d')
#day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
#day = '20170328'
logger.debug(day)
cnt = 0
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(stock_code)
#logger.debug (stock_code, stock_name, isu_cd)
r = util.get_krx_daily_sellbuy_trend(isu_cd, day, day)
f = io.BytesIO(r)
df = pd.read_excel(f,
thousands=',',
usecols=['년/월/일', '기관_순매수(주)', '외국인_순매수(주)'])
df = df.dropna()
df.columns = ['date', 'i_volume', 'f_volume']
df['date'] = df['date'].str.replace('/', '')
df_cp = df[['date', 'i_volume', 'f_volume']].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[['stock_code', 'date', 'i_volume', 'f_volume']]
print(df.head())
if len(df_cp) == 0:
logger.debug(stock_code + ' ' + stock_name + ' ' + isu_cd +
' data not found!!')
else:
logger.debug(stock_code + ' ' + stock_name + ' ' + isu_cd)
try:
row = df_cp.ix[0]
cur.execute('INSERT INTO trading_trend (code, date, foreigner, institution) ' \
'VALUES(%s,%s,%s,%s)', (row['stock_code'], row['date'], int(row['f_volume']), int(row['i_volume'])))
conn.commit()
except pymysql.IntegrityError:
pass
except pymysql.Error as e:
if conn:
conn.rollback()
logger.debug("error %s" % e.args[0])
cnt = cnt + 1
df = pd.read_sql_query('SELECT * FROM trading_trend limit 5', conn)
endtime = datetime.datetime.now()
# logger.info('count :', cnt, ', elaspsedtime : ' , (endtime - starttime))
# logger.info(str(datetime.datetime.today()) + ' : ' + task_id + ' end...')
if conn:
conn.close()
def main_function(day):
print ("collect date : " + day)
# main
conn = pymysql.connect(host='localhost',
user='******',
password='******',
db='favis',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
df_sm = pd.read_sql_query("SELECT * FROM stock_info order by code asc", conn)
count = 0
print (datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
#print (row['code'], util.getIsinCode(row['code']))
isu_cd = util.getIsinCode(stock_code)
path = "/exdata/collect_temp/"
filename = path + 'stockindex_' + stock_code + "_" + day + ".xls"
if not os.path.exists(filename):
r = util.get_krx_daily_stock_index(isu_cd, 'A'+stock_code, day, day)
with open(filename, 'wb') as f:
#f.write(r.content.decode('utf-8'))
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['일자', '종목코드','EPS','PER','BPS','PBR','주당배당금','배당수익률'], converters={'종목코드':str})
df.columns = ['date','stock_code','eps','per','bps','pbr', 'dividend','dividend_rate']
df['date'] = df['date'].str.replace('/','')
print(str(count) + " " + stock_code + " : " + str(len(df)))
#data = [tuple(x) for x in df_cp.to_records(index=False)]
#df.set_index('Date', inplace=True)
#df = df.sort_index(0, ascending=True)
count= count+1
for idx, row in df.iterrows():
try:
# cur.executemany('INSERT INTO daily_info (code, date, open, high, low, close, volume, marcap, amount) ' \
# 'VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s)', data)
cur.execute('INSERT INTO daily_stock_index (code, date, eps, per, bps, pbr, dividend, dividend_rate) ' \
'VALUES(%s,%s,%s,%s,%s,%s,%s,%s)', (row['stock_code'], row['date'], row['eps'], row['per'], row['bps'], row['pbr'], row['dividend'], row['dividend_rate']))
conn.commit()
except pymysql.IntegrityError:
pass
except pymysql.Error as e:
if conn:
conn.rollback()
print ("error %s" % e.args[0])
#df = pd.read_sql_query('SELECT * FROM daily_info limit 5', conn)
#print(df.head())
#print(datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S'))
if conn:
conn.close()
print(day + " : " + str(count))
def main_function(day):
logger.info('\n\n' + str(datetime.datetime.today()) + ' : ' + task_id + ' start...')
starttime = datetime.datetime.now()
where_cond = ''
# if len(sys.argv) > 1:
# start_code = sys.argv[1]
# logger.debug(start_code)
# where_cond = "where code > '" + start_code + "'"
conn = favis_util.get_favis_mysql_connection()
cur = conn.cursor()
query = 'SELECT * FROM stock_info '+ where_cond +' ORDER BY code ASC'
logger.debug(query)
df_sm = pd.read_sql_query(query, conn)
#day = datetime.datetime.today().strftime('%Y%m%d')
#day = (datetime.datetime.today() - datetime.timedelta(1)).strftime('%Y%m%d')
#day = '20170328'
logger.debug(day)
cnt = 0
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
isu_cd = util.getIsinCode(stock_code)
#logger.debug (stock_code, stock_name, isu_cd)
path = favis_path + "collector/data/"
filename = path + stock_code + "_sellbuy_trend.xls"
r = util.get_krx_daily_sellbuy_trend(isu_cd, day, day)
with open(filename, 'wb') as f:
f.write(r)
df = pd.read_excel(filename, thousands=',', usecols=['년/월/일', '기관_순매수(주)','외국인_순매수(주)'])
df = df.dropna()
df.columns = ['date','i_volume', 'f_volume']
df['date'] = df['date'].str.replace('/','')
df_cp = df[['date','i_volume', 'f_volume']].copy()
df_cp['stock_code'] = stock_code
df_cp = df_cp[['stock_code', 'date','i_volume', 'f_volume']]
if len(df_cp) == 0 :
logger.debug(stock_code +' ' + stock_name+' ' +isu_cd + ' data not found!!')
else :
logger.debug(stock_code +' ' + stock_name+' ' +isu_cd)
try:
row = df_cp.ix[0]
cur.execute('INSERT INTO trading_trend (code, date, foreigner, institution) ' \
'VALUES(%s,%s,%s,%s)', (row['stock_code'], row['date'], int(row['f_volume']), int(row['i_volume'])))
conn.commit()
except pymysql.IntegrityError:
pass
except pymysql.Error as e:
if conn:
conn.rollback()
logger.debug ("error %s" % e.args[0])
cnt = cnt + 1
df = pd.read_sql_query('SELECT * FROM trading_trend limit 5', conn)
endtime = datetime.datetime.now()
# logger.info('count :', cnt, ', elaspsedtime : ' , (endtime - starttime))
# logger.info(str(datetime.datetime.today()) + ' : ' + task_id + ' end...')
if conn:
conn.close()
def main_function(date):
logger.info('\n\n' + str(datetime.datetime.today()) + ' : start...' + date)
starttime = datetime.datetime.now()
conn = fu.get_favis_mysql_connection()
cur = conn.cursor()
# logger.info("1) get krx trading trend")
#df_sm = pd.read_sql_query("SELECT code FROM stock_info ORDER BY code ASC", conn)
df_sm = pd.read_sql_query(
"SELECT code FROM stock_info WHERE CODE NOT IN (SELECT CODE FROM trading_trend WHERE DATE = '"
+ date + "') ORDER BY code ASC", conn)
# logger.info(df_sm.values.flatten())
endtime = datetime.datetime.now()
if conn:
conn.close()
cnt = 1
for stock_code in df_sm.values.flatten():
isu_cd = util.getIsinCode(stock_code)
#print (stock_code, stock_name, isu_cd)
try:
r = util.get_krx_sellbuy_detail(isu_cd, date, date)
print(r)
f = io.BytesIO(r)
print(f)
df = pd.read_excel(f, thousands=',', usecols=['투자자명', '거래량_순매수'])
if (cnt % 100 == 0):
print(str(cnt), end='\n')
endtime = datetime.datetime.now()
print(
f'{stock_code} (elaspsedtime1 : {str(endtime - starttime)})'
)
else:
# logger.info('.', end='', flush=True)
print('.', end='')
except Exception as e:
logger.error("error %s" % e)
df['stock_code'] = stock_code
df = df.pivot(index='stock_code', columns='투자자명', values='거래량_순매수')
# del df.index.name
df.columns = ['personal', 'nation_local','financial_investment','institution','etc_fin','etc_ins','etc_for','insurance',\
'private_equity_fund','pension_fund','foreigner','bank','investment_trust','total']
df['date'] = date
df['code'] = stock_code
df_cp = df[['code','date','personal', 'nation_local','financial_investment','institution','insurance',\
'private_equity_fund','pension_fund','foreigner','bank','investment_trust']]
if len(df_cp) == 0:
logger.debug(stock_code + ' ' + isu_cd + ' ' + ' data not found!!')
else:
try:
df_cp.to_sql(name='trading_trend',
con=engine,
if_exists='append',
index=False)
cnt = cnt + 1
except exc.IntegrityError:
cnt = cnt + 1
pass
endtime = datetime.datetime.now()
# logger.info('DATE(%s) count : %s, elaspsedtime : %s ' % date, str(cnt), str(endtime - starttime))
# logger.info('%s : %s end...' % str(datetime.datetime.today()), task_id)
print(f'{date} (elaspsedtime1 : {str(endtime - starttime)})')
