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)})')