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(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))
import pymysql
sys.path.append("./")
# User Defined Modules
import util.favis_util as fu
if __name__ == "__main__":
print("1) get krx stock master")
# KOREA[KRX, KOSPI, KOSDAQ, KONEX], AMERICA[NASDAQ, NYSE, AMEX, SP500]
MARKET_KOREA = ['KOSPI', 'KOSDAQ', 'KONEX']
MARKET_AMERICA = ['NASDAQ', 'NYSE', 'AMEX', 'SP500']
df = fdr.StockListing('KONEX')
df = df.fillna('')
conn = fu.get_favis_mysql_connection()
cur = conn.cursor()
# stock_desc 테이블 쓰기
df_desc = df[['Symbol', 'Name', 'Sector', 'Industry']].copy()
df_desc['market'] = ''
print("2) get stock desc and update db")
cnt = 0
for idx, row in df_desc.iterrows():
try:
# print (row['Symbol'], row['Name'], 'KOSPI', row['Sector'],'|', row['Industry'])
cur.execute('INSERT INTO stock_info (code, name, market, sector, description, updateddt) ' \
'VALUES(%s,%s,%s,%s,%s, current_timestamp)', (row['Symbol'], row['Name'], 'KONEX', row['Sector'], row['Industry']))
conn.commit()
def send_data(bot, update, args):
print('code command...', args)
stock_code = args[0]
conn = fu.get_favis_mysql_connection()
cur = conn.cursor()
info_query = "SELECT code, name, name_en, market, wics, sector, description FROM stock_info WHERE CODE = '" + stock_code + "'"
df_info = pd.read_sql_query(info_query, conn)
print(df_info.head())
name = str(df_info['name'].values[0]
) # pandas column value to string without types
name_en = str(df_info['name_en'].values[0]
) # pandas column value to string without types
market = str(df_info['market'].values[0])
wics = str(df_info['wics'].values[0])
sector = str(df_info['sector'].values[0])
desc = str(df_info['description'].values[0])
print(df_info.head())
try:
stock_info = '[%s]%s (%s)' % (stock_code, name, market)
print(stock_info)
bot.send_message(chat_id=update.message.chat_id, text=stock_info)
except Exception as e:
print("error %s" % e.args[0])
query = "select i.date, MAX(CASE WHEN ii.name = si.market THEN i.close END) as index_close \
, d.code, d.close as stock_close, d.volume, t.foreigner, t.institution \
from daily_index i, daily_info d, trading_trend t, stock_info si, index_info ii\
where i.code in ('KS11', 'KQ11') and i.code = ii.code and i.date > '20160101' and i.date = d.date and i.date = t.date and d.code = t.code \
and d.code = '" + stock_code + "' and d.code = si.code GROUP BY i.date, d.code, d.close, d.volume, t.foreigner, t.institution order by date asc"
df = pd.read_sql(query, conn)
df['date'] = pd.to_datetime(df['date'], format='%Y%m%d')
df = df.set_index('date')
if conn:
conn.close()
#df['MA_5'] = pd.stats.moments.rolling_mean(df['Adj Close'], 5)
df['MA_5'] = df['stock_close'].rolling(5).mean()
df['MA_20'] = df['stock_close'].rolling(20).mean()
df['diff'] = df['MA_5'] - df['MA_20']
fig = matplotlib.pyplot.gcf()
fig.set_size_inches(16, 26)
# price (가격)
price_chart = plt.subplot2grid((5, 1), (0, 0), rowspan=1)
price_chart.plot(df.index, df['stock_close'], label='stock_close', lw=3)
price_chart.plot(df.index, df['MA_5'], label='MA 5day', lw=2)
price_chart.plot(df.index, df['MA_20'], label='MA 20day', lw=1)
price_chart.grid(True)
plt.title(stock_code + "(" + name_en + ")")
plt.legend(loc='best')
# 지수 (포인트)
index_chart = plt.subplot2grid((5, 1), (1, 0), rowspan=1)
index_chart.plot(df.index,
df['index_close'],
label='index_close',
lw=1,
color='r')
index_chart.set_title(market)
index_chart.grid(True)
# volume (거래량)
vol_chart = plt.subplot2grid((5, 1), (2, 0), rowspan=1)
vol_chart.bar(df.index, df['volume'], color='c')
vol_chart.set_title('Volume')
vol_chart.grid(True)
# volume (거래량)
trend_chart = plt.subplot2grid((5, 1), (3, 0), rowspan=1)
trend_chart.bar(df.index, df['foreigner'], color='r')
trend_chart.bar(df.index, df['institution'], color='b')
trend_chart.set_title('Foreigner - INstitution')
trend_chart.grid(True)
# 이동평균의 차이
signal_chart = plt.subplot2grid((5, 1), (4, 0), rowspan=1)
signal_chart.plot(df.index, df['diff'].fillna(0), color='g')
plt.axhline(y=0, linestyle='--', color='k')
signal_chart.set_title('MA')
signal_chart.grid(True)
# sell, buy annotate
prev_key = prev_val = 0
# Sell/Buy 시그널 Annotation
def annote_signal(chart, xy, text):
textcoords = 'offset points'
arrowprops = dict(arrowstyle='-|>')
if text == 'Buy':
xytext = (10, -30)
elif text == 'Sell':
xytext = (10, 30)
else:
return
chart.annotate(text,
xy=xy,
xytext=xytext,
textcoords=textcoords,
arrowprops=arrowprops)
for key, val in df['diff'].iteritems():
if val == 0:
continue
if val * prev_val < 0 and val > prev_val:
annote_signal(signal_chart, (key, df['diff'][key]), 'Buy')
elif val * prev_val < 0 and val < prev_val:
annote_signal(signal_chart, (key, df['diff'][key]), 'Sell')
prev_key, prev_val = key, val
for key, val in df['diff'].iteritems():
if val == 0:
continue
if val * prev_val < 0 and val > prev_val:
annote_signal(price_chart, (key, df['stock_close'][key]), 'Buy')
elif val * prev_val < 0 and val < prev_val:
annote_signal(price_chart, (key, df['stock_close'][key]), 'Sell')
prev_key, prev_val = key, val
try:
# plt.savefig(stock_code + '.png', bbox_inches='tight')
buf = io.BytesIO()
plt.savefig(buf, format='png', bbox_inches='tight')
buf.seek(0)
bot.sendPhoto(chat_id=update.message.chat_id, photo=buf)
except Exception as e:
print("error %s" % e.args[0])
startdate = '20170328'
enddate = '20170405'
for
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 = '20160603'
logger.debug(day)
cnt = 0
for idx, row in df_sm.iterrows():
stock_code = row['code']
stock_name = row['name']
#!/usr/bin/python
import requests
import datetime
import pandas as pd
from pandas import DataFrame
import io, os
# user define package import
import sys
sys.path.append("../../favis")
from msgbot.favisbot import favisbot
import util.krx_util as util
import util.favis_util as favis
import pymysql
conn = favis.get_favis_mysql_connection()
cur = conn.cursor()
df_sm = pd.read_sql_query('SELECT * FROM stock_info', conn)
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']))
year_start = 2010
year_end = 2016
for year in range(year_start, year_end + 1):
print(stock_code, stock_name, year)
start = datetime.datetime(year, 1, 1).strftime('%Y%m%d')
end = datetime.datetime(year, 12, 31).strftime('%Y%m%d')
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):
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)})')
