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