def makebb(code): q = ''' SELECT REPLACE(DATE, '-', '') AS DATE, CLOSE FROM jazzdb.T_INDEX_OHLC_DAY WHERE 1=1 AND INDEXCODE = '%s' ''' % (code) df = db.selectpd(q) BB_WHOLE = cal._bolinger(df) EXIST_DATE_LIST = getalreadyexists(code, 'jazzdb.T_INDEX_BB') if (len(EXIST_DATE_LIST) == 0): BB_NOT_EXISTS = BB_WHOLE else: BB_NOT_EXISTS = BB_WHOLE[(~BB_WHOLE.DATE.isin(EXIST_DATE_LIST)) & (BB_WHOLE.DATE > EXIST_DATE_LIST.DATE.max())] if (len(BB_NOT_EXISTS) > 0): BB_NOT_EXISTS['INDEXCODE'] = code db.insertdf( BB_NOT_EXISTS[['INDEXCODE', 'DATE', 'BBU', 'BBL', 'BBP', 'BBW']], 'jazzdb.T_INDEX_BB') else: print("DONE ALREADY")
def db_readAll(): q = ''' SELECT INDEXCODE, INDEXNAME FROM jazzdb.T_INDEX_CODE_MGMT WHERE 1=1 ''' df = db.selectpd(q) for each in df.values: codeDic[each[0]] = each[1]
def get_bb(self): self.df = db.selectpd(f''' SELECT DATE, BBU, BBL, BBP, BBW FROM jazzdb.T_STOCK_BB WHERE 1=1 AND STOCKCODE = "{self.stockcode}" AND DATE BETWEEN "{self.start_date}" AND "{self.the_date}" ''')
def get_ohlc(self, cnt=900): date_from = db.selectSingleValue( 'SELECT CAST(DATE AS CHAR) AS DATE FROM jazzdb.T_DATE_INDEXED WHERE CNT = "%s"' % (cnt)) df = db.selectpd( "SELECT STOCKCODE, CAST(DATE AS CHAR) AS DATE, A.OPEN, A.HIGH, A.LOW, A.CLOSE, B.VOLUME FROM jazzdb.T_STOCK_OHLC_DAY A JOIN jazzdb.T_STOCK_SND_DAY B USING(STOCKCODE, DATE) WHERE STOCKCODE = '%s' AND DATE > '%s'" % (self.stockcode, date_from)) return df
def get_ohlc_modified(self): query = ''' SELECT STOCKCODE, CAST(DATE AS CHAR) AS DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, VALUE, 0 AS ADJCLASS, -1 AS ADJRATIO FROM jazzdb.T_STOCK_OHLC_DAY_CORRECTION JOIN jazzdb.T_DATE_INDEXED USING (DATE) WHERE 1=1 AND STOCKCODE = "%s" AND DATE BETWEEN "%s" AND "%s" ''' % (self.stockcode, self.from_date, self.to_date) return db.selectpd(query)
def get_ohlc_origin(self): query = ''' SELECT STOCKCODE, CAST(DATE AS CHAR) AS DATE, OPEN, HIGH, LOW, CLOSE FROM jazzdb.T_STOCK_OHLC_DAY JOIN jazzdb.T_DATE_INDEXED USING (DATE) WHERE 1=1 AND STOCKCODE = "%s" AND DATE BETWEEN "%s" AND "%s" ''' % (self.stockcode, self.from_date, self.to_date) return db.selectpd(query)
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 getalreadyexists(code, table): ''' :param code: 해당종목이 :param table: 해당테이블에 존재하지 않는 날짜들을 :return: RETURN 받는다! ''' q = ''' SELECT REPLACE(DATE, '-', '') AS DATE FROM ( SELECT DATE, ROW_NUMBER() OVER (PARTITION BY INDEXCODE ORDER BY DATE DESC) AS RN FROM %s WHERE 1=1 AND INDEXCODE='%s' ORDER BY DATE DESC ) RS WHERE 1=1 AND RN < 40 ; ''' % (table, code) return db.selectpd(q)
def analysisSndBasicEachDay(stockcode, tdate): qa = ''' SELECT cast(DATE AS CHAR) AS DATE, STOCKCODE, CLOSE, VOLUME, FOREI, INS, PER, FINAN, SAMO, YG, TUSIN, INSUR, NATION, BANK, OTHERFINAN, OTHERCORPOR, OTHERFOR, CNT, MA3, MA5, MA10, MA20, MA60, MA120, VMA3, VMA5, VMA10, VMA20, VMA60, VMA120 FROM jazzdb.T_STOCK_SND_DAY A JOIN jazzdb.T_DATE_INDEXED B USING (DATE) JOIN jazzdb.T_STOCK_MA USING (STOCKCODE, DATE) WHERE 1=1 AND A.STOCKCODE = '%s' AND B.CNT BETWEEN 0 AND 500 ''' % (stockcode) qb = ''' SELECT STOCKCODE, SHARE FROM jazzdb.T_STOCK_SHARES_INFO WHERE 1=1 AND HOLDER = '유통주식수' AND STOCKCODE = '%s' AND DATE = '%s' ''' % (stockcode, tdate) adf = db.selectpd(qa) bdf = db.selectpd(qb) dic = { 'INS': 'I', 'FOREI': 'F', 'PER': 'PS', 'FINAN': 'FN', 'SAMO': 'S', 'YG': 'YG', 'TUSIN': 'T', 'INSUR': 'IS', 'NATION': 'NT', 'BANK': 'BK', 'OTHERCORPOR': 'OC', } winsize = [1, 3, 5, 20, 60] adf.CLOSE = abs(adf.CLOSE) # 수급퍼센티지 만들기 for eachwin in dic.keys(): for eachsize in winsize: adf[dic[eachwin] + str(eachsize)] = adf[eachwin].rolling(eachsize).sum() / bdf.SHARE.values[0] # 단일종목역대랭킹뽑기 if (eachwin in ['YG', 'PER']): adf[dic[eachwin][0] + 'R'] = adf[eachwin].rank(method='first', ascending=False) else: adf[dic[eachwin] + 'R'] = adf[eachwin].rank(method='first', ascending=False) # 주가변동퍼센티지 for eachsize in winsize: adf['P' + str(eachsize)] = adf.CLOSE.pct_change(periods=eachsize) # 볼륨변동 # ,ROUND(VOLUME/VMA3,5) AS V3 # ,ROUND(VOLUME/VMA5,5) AS V5 # ,ROUND(VOLUME/VMA20,5) AS V20 # ,ROUND(VOLUME/VMA60,5) AS V60 for eachsize in winsize[1:]: adf['V' + str(eachsize)] = adf.VOLUME / adf['VMA' + str(eachsize)] # print(adf[['STOCKCODE', 'DATE', 'CLOSE', 'P1', 'P3', 'P5', 'P20', 'P60', 'I1', # 'I3', 'I5', 'I20', 'I60', 'F1', 'F3', 'F5', 'F20', 'F60', 'PS1', 'PS3', # 'PS5', 'PS20', 'PS60', 'FN1', 'FN3', 'FN5', 'FN20', 'FN60', 'YG1', # 'YG3', 'YG5', 'YG20', 'YG60', 'S1', 'S3', 'S5', 'S20', 'S60', 'T1', # 'T3', 'T5', 'T20', 'T60', 'IS1', 'IS3', 'IS5', 'IS20', 'IS60', 'NT1', # 'NT3', 'NT5', 'NT20', 'NT60', 'BK1', 'BK3', 'BK5', 'BK20', 'BK60', # 'OC1', 'OC3', 'OC5', 'OC20', 'OC60', 'IR', 'FR', 'PR', 'FNR', 'YR', # 'SR', 'TR', 'ISR', 'NTR', 'BKR', 'OCR', 'V3', 'V5', 'V20', 'V60']].tail(1), # 'jazzdb.T_STOCK_SND_ANALYSIS_RESULT_TEMP') db.insertdf(adf[['STOCKCODE', 'DATE', 'CLOSE', 'P1', 'P3', 'P5', 'P20', 'P60', 'I1', 'I3', 'I5', 'I20', 'I60', 'F1', 'F3', 'F5', 'F20', 'F60', 'PS1', 'PS3', 'PS5', 'PS20', 'PS60', 'FN1', 'FN3', 'FN5', 'FN20', 'FN60', 'YG1', 'YG3', 'YG5', 'YG20', 'YG60', 'S1', 'S3', 'S5', 'S20', 'S60', 'T1', 'T3', 'T5', 'T20', 'T60', 'IS1', 'IS3', 'IS5', 'IS20', 'IS60', 'NT1', 'NT3', 'NT5', 'NT20', 'NT60', 'BK1', 'BK3', 'BK5', 'BK20', 'BK60', 'OC1', 'OC3', 'OC5', 'OC20', 'OC60', 'IR', 'FR', 'PR', 'FNR', 'YR', 'SR', 'TR', 'ISR', 'NTR', 'BKR', 'OCR', 'V3', 'V5', 'V20', 'V60']].tail(1), 'jazzdb.T_STOCK_SND_ANALYSIS_RESULT_TEMP')
import jazzstock_bot.common.connector_db as db df = db.selectpd(''' SELECT STOCKCODE, CAST(DATE AS CHAR) AS DATE, OPEN, HIGH, LOW, CLOSE, 0 AS VOLUME, VALUE FROM jazzdb.T_STOCK_OHLC_DAY WHERE DATE = '2021-09-24' ''') db.insertdf(df, 'jazzdb.T_STOCK_OHLC_DAY_CORRECTION')
SELECT REPLACE(DATE, '-', '') AS DATE FROM ( SELECT DATE, ROW_NUMBER() OVER (PARTITION BY INDEXCODE ORDER BY DATE DESC) AS RN FROM %s WHERE 1=1 AND INDEXCODE='%s' ORDER BY DATE DESC ) RS WHERE 1=1 AND RN < 40 ; ''' % (table, code) return db.selectpd(q) for eachcode, eachname in db.selectpd( 'SELECT INDEXCODE, INDEXNAME FROM jazzdb.T_INDEX_CODE_MGMT').values: print(eachcode, eachname) sync_ohlc_day(eachcode) makebb(eachcode) time.sleep(0.7) # *OPT20003 : 전업종 지수요청 001 / 101/ # OPT20002 : 업종별 주가요청 # OPT20005 : 업종별 분봉조회 # OPT20006 : 업종별 일봉조회
def get_columns_from_table(self, table): columns = db.selectpd("SELECT * FROM %s LIMIT 1" % (table)).columns.tolist() return columns
SELECT CAST(DATE AS CHAR) AS DATE, CAST(SUBSTRING(DATE, 3,2) AS UNSIGNED) AS YY , SUBSTRING(DATE, 6,5) AS MM_DD FROM ( SELECT DATE, CNT, DATE_FINAN FROM jazzdb.T_DATE_INDEXED LEFT JOIN jazzdb.T_DATE_FINAN USING (DATE) ORDER BY DATE DESC ) A WHERE 1=1 AND A.DATE_FINAN IS NULL AND A.CNT < 500 ''' df = db.selectpd(query) if len(df) > 0: for q in q_due.keys(): if q in ['03', '06']: df.loc[(df['MM_DD'] > q_due[q][0]) & (df['MM_DD'] <= q_due[q][1]), 'PY'] = (df['YY']) df.loc[(df['MM_DD'] > q_due[q][0]) & (df['MM_DD'] <= q_due[q][1]), 'QQ'] = q elif q == '12': df.loc[(df['MM_DD'] > q_due[q][0]) & (df['MM_DD'] <= q_due[q][1]), 'PY'] = (df['YY'] - 1) df.loc[(df['MM_DD'] > q_due[q][0]) & (df['MM_DD'] <= q_due[q][1]), 'QQ'] = q else: