def update_profit(self): df_no_na = self.df_profit.copy() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() for i, row in df_no_na.iterrows(): # print(row.P60) query = ''' UPDATE `jazzdb`.`T_STOCK_SND_ANALYSIS_RESULT_TEMP` SET `P1` = %s, `P3` = %s, `P5` = %s, `P20` = %s, `P60` = %s WHERE (`STOCKCODE` = '%s') and (`DATE` = '%s'); ''' % (row.P1, row.P3, row.P5, row.P20, row.P60, self.stockcode, row.DATE) query = query.replace('nan', 'NULL') # print(query) db.insert(query) query = ''' UPDATE `jazzdb`.`T_STOCK_SND_ANALYSIS_LONGTERM` SET `P80` = %s, `P120` = %s, `P160` = %s, `P200` = %s, `P240` = %s WHERE (`STOCKCODE` = '%s') and (`DATE` = '%s'); ''' % (row.P80, row.P120, row.P160, row.P200, row.P240, self.stockcode, row.DATE) query = query.replace('nan', 'NULL') # print(query) db.insert(query)
def insert(stockcode, date, content): import jazzstock_bot.common.connector_db as db from datetime import datetime content = content[:200] if len(date.split('. ')) ==3: date = date.split('. ') yyyy = date[0] mm = date[1].zfill(2) dd = date[2].replace('.','').zfill(2) else: today = datetime.now().date() yyyy= today.year mm = str(today.month).zfill(2) dd = str(today.day).zfill(2) try: query = f"INSERT INTO `jazzdb`.`T_STOCK_TEXT` (`STOCKCODE`, `DATE`, `CONT_TYPE`, `AUTHOR`, `AUTHOR2`, `CONTENT`) VALUES ('{stockcode}', '{'%s%s%s' % (yyyy, mm, dd)}', 'B', 'jameslee', '', '{content}');" print(query) db.insert(query) return True except Exception as e: print(e) return False
def makemc(date): db.insert(''' INSERT INTO jazzdb.T_STOCK_MC SELECT STOCKCODE, DATE, ABS(CLOSE) * SHARE / 100000000000 FROM jazzdb.T_STOCK_SND_DAY JOIN jazzdb.T_DATE_INDEXED A USING (DATE) JOIN (SELECT STOCKCODE, DATE, SHARE FROM jazzdb.T_STOCK_SHARES_INFO WHERE HOLDER = '발행주식수') B USING (STOCKCODE,DATE) WHERE 1=1 AND DATE = '%s' ''' % (date))
def insert_movingaverage(stockcode, date): query = ''' INSERT INTO jazzdb.T_STOCK_MA SELECT STOCKCODE, DATE, MA3, MA5, MA10, MA20, MA60, MA120, VMA3, VMA5, VMA10, VMA20, VMA60, VMA120 FROM ( SELECT STOCKCODE, DATE, B.CNT, CLOSE, VOLUME, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MA3, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MA5, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS MA10, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS MA20, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS MA60, AVG(ABS(CLOSE)) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS MA120, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS VMA3, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS VMA5, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS VMA10, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 19 PRECEDING AND CURRENT ROW) AS VMA20, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 59 PRECEDING AND CURRENT ROW) AS VMA60, AVG(VOLUME) OVER (PARTITION BY STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS VMA120 FROM jazzdb.T_STOCK_SND_DAY A JOIN jazzdb.T_DATE_INDEXED B USING (DATE) WHERE 1=1 AND B.CNT < 200 AND A.STOCKCODE = '%s' ) RS WHERE RS.DATE ='%s' ''' % (stockcode, date) try: db.insert(query) # print('MA SUCCESS', i,stockcode,codeDic[stockcode]) except Exception as e: print('ERROR', i, stockcode, codeDic[stockcode], e)
def makebb(stockcode, day): df = db.selectpd(''' SELECT STOCKCODE, cast(DATE AS CHAR) as DATE, CLOSE FROM jazzdb.T_STOCK_OHLC_DAY JOIN jazzdb.T_DATE_INDEXED USING (DATE) WHERE 1=1 AND STOCKCODE = '%s' AND DATE > '2020-08-01' # AND CNT<25i ''' % (stockcode)) bbwinsize = 20 df['PMA20'] = df['CLOSE'].rolling(20).mean() df['PBBU'] = df['PMA20'] + 2 * df['CLOSE'].rolling(bbwinsize).std().round( 0) df['PBBL'] = df['PMA20'] - 2 * df['CLOSE'].rolling(bbwinsize).std().round( 0) df['BBPOS'] = (df['CLOSE'] - df['PBBL']) / (df['PBBU'] - df['PBBL']).round(3) df['BBWIDTH'] = (4 * df['CLOSE'].rolling(bbwinsize).std() / df['PMA20']).round(3) data = [] for each in df[df['DATE'] == day].dropna()[[ 'DATE', 'PBBU', 'PBBL', 'BBPOS', 'BBWIDTH' ]].round(3).values.tolist(): l = each l.insert(0, stockcode) data.append(tuple(l)) insertQuery = ''' INSERT INTO jazzdb.T_STOCK_BB VALUES ''' + str(data)[1:-1] db.insert(insertQuery)
def update_stockcode(): global codeDic, old_list new_list = apiObj.get_list('p') new_list = new_list + apiObj.get_list('d') for eachcode in new_list: # 신규 상장 종목 인서트 if eachcode[0] != '' and eachcode[0] not in codeDic.keys(): print(eachcode + [dm.todayStr('-')]) db.insert( "INSERT INTO `jazzdb`.`T_STOCK_CODE_MGMT` (`STOCKCODE`, `STOCKNAME`, `MARKET`, `LISTED`, `UPDATEDATE`) VALUES ('%s', '%s', '%s', '%s','%s');" % tuple(eachcode + [dm.todayStr('-')])) print("신규상장 종목", eachcode) # 종목명 바뀐경우 업데이트 if eachcode[0] != '' and eachcode[0] in codeDic.keys( ) and eachcode[1] != codeDic[eachcode[0]]: print("종목명 변경", codeDic[eachcode[0]], eachcode) db.insert( "UPDATE `jazzdb`.`T_STOCK_CODE_MGMT` SET `STOCKNAME` = '%s', `UPDATEDATE` = '%s' WHERE (`STOCKCODE` = '%s')" % (eachcode[1], dm.todayStr('-'), eachcode[0]))
from jazzstock_bot.common import connector_db as db queryt = ''' DELETE FROM jazzdb.T_DATE_INDEXED; ''' querye = ''' INSERT INTO jazzdb.T_DATE_INDEXED ( SELECT A.DATE, @ROWNUM := @ROWNUM+1 AS CNT FROM ( SELECT DATE FROM jazzdb.T_STOCK_SND_DAY GROUP BY DATE ORDER BY DATE DESC ) A , (SELECT @ROWNUM := -1) R ); ''' db.insert(queryt) db.insert(querye)
def analysisSndBasicEachDay(stockcode, date): query = ''' INSERT INTO jazzdb.T_STOCK_SND_ANALYSIS_LONGTERM SELECT RS.STOCKCODE, RS.DATE ,ROUND(I80/SHARE,5) AS I80 ,ROUND(I120/SHARE,5) AS I120 ,ROUND(I160/SHARE,5) AS I160 ,ROUND(I200/SHARE,5) AS I200 ,ROUND(I240/SHARE,5) AS I240 ,ROUND(F80/SHARE,5) AS F80 ,ROUND(F120/SHARE,5) AS F120 ,ROUND(F160/SHARE,5) AS F160 ,ROUND(F200/SHARE,5) AS F200 ,ROUND(F240/SHARE,5) AS F240 ,ROUND((CLOSE-C80)/C80,5) AS P80 ,ROUND((CLOSE-C120)/C120,5) AS P120 ,ROUND((CLOSE-C160)/C160,5) AS P160 ,ROUND((CLOSE-C200)/C200,5) AS P200 ,ROUND((CLOSE-C240)/C240,5) AS P240 ,ROUND(YG80/SHARE,5) AS YG80 ,ROUND(YG120/SHARE,5) AS YG120 ,ROUND(YG160/SHARE,5) AS YG160 ,ROUND(YG200/SHARE,5) AS YG200 ,ROUND(YG240/SHARE,5) AS YG240 ,ROUND(S80/SHARE,5) AS S80 ,ROUND(S120/SHARE,5) AS S120 ,ROUND(S160/SHARE,5) AS S160 ,ROUND(S200/SHARE,5) AS S200 ,ROUND(S240/SHARE,5) AS S240 FROM ( SELECT A.STOCKCODE, A.DATE, ABS(A.CLOSE) AS CLOSE, C.SHARE+0.01 AS SHARE, B.CNT , SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 80 PRECEDING AND 80 PRECEDING) AS C80 , SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 120 PRECEDING AND 120 PRECEDING) AS C120 , SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 160 PRECEDING AND 160 PRECEDING) AS C160 , SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 200 PRECEDING AND 200 PRECEDING) AS C200 , SUM(ABS(A.CLOSE)) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 240 PRECEDING AND 240 PRECEDING) AS C240 # 3,5,10,20 누적 매수 , A.INS AS I1 , SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 79 PRECEDING AND CURRENT ROW) AS I80 , SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS I120 , SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 159 PRECEDING AND CURRENT ROW) AS I160 , SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS I200 , SUM(A.INS) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 239 PRECEDING AND CURRENT ROW) AS I240 , A.FOREI AS F1 , SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 79 PRECEDING AND CURRENT ROW) AS F80 , SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS F120 , SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 159 PRECEDING AND CURRENT ROW) AS F160 , SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS F200 , SUM(A.FOREI) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 239 PRECEDING AND CURRENT ROW) AS F240 , A.YG AS YG1 , SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 79 PRECEDING AND CURRENT ROW) AS YG80 , SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS YG120 , SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 159 PRECEDING AND CURRENT ROW) AS YG160 , SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS YG200 , SUM(A.YG) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 239 PRECEDING AND CURRENT ROW) AS YG240 , A.SAMO AS S1 , SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 79 PRECEDING AND CURRENT ROW) AS S80 , SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 119 PRECEDING AND CURRENT ROW) AS S120 , SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 159 PRECEDING AND CURRENT ROW) AS S160 , SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 199 PRECEDING AND CURRENT ROW) AS S200 , SUM(A.SAMO) OVER (PARTITION BY A.STOCKCODE ORDER BY DATE ASC ROWS BETWEEN 239 PRECEDING AND CURRENT ROW) AS S240 FROM jazzdb.T_STOCK_SND_DAY A JOIN jazzdb.T_DATE_INDEXED B USING (DATE) LEFT JOIN ( SELECT STOCKCODE, SHARE, DATE FROM ( SELECT STOCKCODE, SHARE, DATE FROM jazzdb.T_STOCK_SHARES_INFO WHERE 1=1 AND HOLDER = '유통주식수' ) T1 WHERE 1=1 ) C USING (STOCKCODE, DATE) WHERE 1=1 AND A.STOCKCODE = '%s' AND B.CNT < 800 ) RS WHERE SHARE IS NOT NULL AND RS.CNT=0 ; ; ''' % (stockcode) db.insert(query)
if self.verbose or verbose: print(ret_df[['PREV_IDX', 'DATE_IDX', 'INTERVAL', 'PREVENT', 'EVENT', 'GRAD', 'BBW']]) return ret_df[['PREV_IDX', 'DATE_IDX', 'INTERVAL', 'PREVENT', 'EVENT', 'GRAD', 'BBW']] insertQuery = ''' INSERT INTO jazzdb.T_STOCK_BB VALUES ''' + str(data)[1:-1] db.insert(insertQuery) def gettoday(): td = db.selectSingleValue('SELECT cast(DATE AS CHAR) AS DATE FROM jazzdb.T_DATE_INDEXED WHERE CNT = 0') return td codeDic, itemDic = {}, {} db_readAll() today = gettoday()
, MAX(HIGH) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS PH5 , MIN(LOW) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) AS PL5 , MAX(HIGH) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) AS PH10 , MIN(LOW) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 10 PRECEDING AND 1 PRECEDING) AS PL10 , MAX(HIGH) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS PH20 , MIN(LOW) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING) AS PL20 , MAX(HIGH) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 60 PRECEDING AND 1 PRECEDING) AS PH60 , MIN(LOW) OVER (PARTITION BY STOCKCODE ORDER BY DATE DESC ROWS BETWEEN 60 PRECEDING AND 1 PRECEDING) AS PL60 FROM jazzdb.T_STOCK_OHLC_DAY A JOIN jazzdb.T_DATE_INDEXED B USING (DATE) JOIN jazzdb.T_STOCK_CODE_MGMT USING (STOCKCODE) WHERE 1=1 AND LISTED = 1 AND B.CNT BETWEEN 0 AND 61 AND STOCKCODE = '%s' ) RS; ; ''' % (each) try: db.insert(query) # print('FP SUCCESS', i,each,codeDic[each]) except: print('ERROR', i, each, codeDic[each])
FROM jazzdb.T_STOCK_OHLC_DAY WHERE DATE = '%s' GROUP BY STOCKCODE ) AND A.LISTED = 1 """ % (today) return db.selectSingleColumn(query) if __name__ == '__main__': stockcodes = get_stockcode_to_crawl() # stockcodes = ["999999", "079940"] for i, stockcode in enumerate(stockcodes): print(i, stockcode) query_to_execute = [] query_to_execute = query_to_execute + crawl_ohlc(stockcode) query_to_execute = query_to_execute + crawl_stake_info(stockcode) for j, each_query in enumerate(query_to_execute): try: db.insert(each_query) except Exception as e: print(i, stockcode, j, e, each_query) time.sleep(0.2)