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 update_future(self): # print(self.stockcode, 'UPDATE_FUTURE') df_no_na = self.df_future.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_FUTURE_PRICE WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_FUTURE_PRICE')
def update_smar(self): # print(self.stockcode, 'UPDATE_SMAR') df_no_na = self.df_smar.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_DAY_SMAR WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_DAY_SMAR')
def update_ohlc(self): df = self.df_ohlc_day.copy() from_date = df.DATE.min() to_date = df.DATE.max() columns = [ 'STOCKCODE', 'DATE', 'OPEN', 'HIGH', 'LOW', 'CLOSE', 'VALUE', 'ADJCLASS', 'ADJRATIO', 'VOLUME' ] query = "DELETE FROM jazzdb.T_STOCK_OHLC_DAY WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df[columns], 'jazzdb.T_STOCK_OHLC_DAY')
def update_bb(self): df = self.df_bb.copy() table = 'jazzdb.T_STOCK_BB' columns = self.get_columns_from_table(table) if self.check_all_columns_in_dataframe(df, columns): # print("ALL COLUMNS EXISTS") df_no_na = df.dropna() from_date = df_no_na.DATE.min() to_date = df_no_na.DATE.max() query = "DELETE FROM jazzdb.T_STOCK_BB WHERE STOCKCODE='%s' AND DATE BETWEEN '%s' AND '%s'" % ( self.stockcode, from_date, to_date) db.delete(query) db.insertdf(df_no_na, 'jazzdb.T_STOCK_BB')
def sync_ohlc_day(code, dt=recent_trading_day): ''' 특정지수의 일봉을 업데이트하는 함수 :param code: :param dt: :return: ''' # 일단 최신데이터를 키움증권에서 땡겨옴 RESPONSE_WHOLE = am.api_index_ohlc_day(apiObj, code, dt) # OHLC테이블에 존재하는 테이블 EXIST_DATE_LIST = getalreadyexists(code, 'jazzdb.T_INDEX_OHLC_DAY') if (len(EXIST_DATE_LIST) == 0): RESPONSE_NOT_EXISTS = RESPONSE_WHOLE else: RESPONSE_NOT_EXISTS = RESPONSE_WHOLE[ (~RESPONSE_WHOLE.DATE.isin(EXIST_DATE_LIST)) & (RESPONSE_WHOLE.DATE > EXIST_DATE_LIST.DATE.max())] # 새로운 데이터가 있다면 INSERT if (len(RESPONSE_NOT_EXISTS) > 0): RESPONSE_NOT_EXISTS['INDEXCODE'] = code print( code, RESPONSE_NOT_EXISTS[[ 'INDEXCODE', 'DATE', 'OPEN', 'HIGH', 'LOW', 'CLOSE', 'VOLUME', 'VALUE' ]]) db.insertdf( RESPONSE_NOT_EXISTS[[ 'INDEXCODE', 'DATE', 'OPEN', 'HIGH', 'LOW', 'CLOSE', 'VOLUME', 'VALUE' ]], 'jazzdb.T_INDEX_OHLC_DAY') else: print("DONE ALREADY")
ret.append( DartParser(stockcode=stockcode, from_date_searching='20210401', to_date_searching='20210515', year='2021', target_quarter='1Q').get_result()) ret.append( DartParser(stockcode=stockcode, from_date_searching='20210601', to_date_searching='20210814', year='2021', target_quarter='2Q').get_result()) # ret.append(DartParser(stockcode=stockcode, from_date_searching='20210901', to_date_searching='20211115', year='2020', target_quarter='3Q').get_result()) # ret.append(DartParser(stockcode=stockcode, from_date_searching='20220101', to_date_searching='20220415', year='2020', target_quarter='4Q').get_result()) ret = [x for x in ret if x is not None] df = pd.DataFrame(ret) db.insertdf(df, table='jazzdb.T_STOCK_FINAN_DART') print(j, stockcode, 'success') except Exception as e: print(j, stockcode, 'fail') ''' STOCKCODE QUARTER REVENUE OPERATE_INCOME PROFIT PROFIT_OWNER BOOK_VALUE BOOK_VALUE_OWNER 0 079940 2003 39,068,686,372.0 5,687,604,468.0 4,249,206,526.0 2,177,586,864.0 136,687,207,223.0 74,543,971,368.0 1 079940 2006 42,170,043,555.0 7,680,824,492.0 7,179,474,158.0 3,545,444,533.0 160,004,572,694.0 81,575,189,832.0 2 079940 2009 39,644,182,015.0 5,214,977,091.0 4,015,516,346.0 1,704,945,778.0 164,525,928,330.0 83,303,657,536.0 3 079940 2012 64,097,025,298.0 7,129,577,695.0 4,758,213,788.0 NaN 173,203,629,410.0 88,366,165,232.0 '''
def crawl_itooza(stockcode): url = "https://search.itooza.com/search.htm?seName=%s&cpv=#indexTable3" % ( stockcode) response = requests.get(url, headers=headers) response.encoding = 'EUC-KR' html = response.text try: df_list = pd.read_html(html) ## 연환산 df = pd.DataFrame(columns=[ 'STOCKCODE', 'TYPE', 'DATE', 'EPSC', 'EPSI', 'PER', 'BPS', 'PBR', 'DV', 'DVR', 'ROE', 'NPR', 'OPR' ]) _c = df_list[2].transpose() _c.reset_index(inplace=True) for row in _c.values[1:]: df.loc[len(df)] = [stockcode, 'c'] + row[:-1].tolist() ## 분기 _q = df_list[4].transpose() _q.reset_index(inplace=True) for row in _q.values[1:]: df.loc[len(df)] = [stockcode, 'q'] + row[:-1].tolist() ## 연간 _y = df_list[3].transpose() _y.reset_index(inplace=True) for row in _y.values[1:]: df.loc[len(df)] = [stockcode, 'y'] + row[:-1].tolist() df = df.fillna(-1) df.DATE = df.DATE.str.replace('월', '', regex=False) df.DATE = df.DATE.str.replace('.', '', regex=False) df = df[[ 'STOCKCODE', 'DATE', 'TYPE', 'EPSC', 'EPSI', 'PER', 'BPS', 'PBR', 'DV', 'DVR', 'ROE', 'NPR', 'OPR' ]] # df_origin = db.selectpd("SELECT * FROM jazzdb.T_STOCK_FINAN WHERE STOCKCODE = '079940' ORDER BY TYPE ASC, DATE DESC") # # print(df_origin) # current_quarter = '2103' # prev_quarter = '2106' # print(df[df['DATE'].isin([current_quarter, prev_quarter])]) current_quarter = '2106' df_current = df[df['DATE'].isin([current_quarter])] if len(df_current) > 0: query_delete = 'DELETE FROM jazzdb.T_STOCK_FINAN WHERE STOCKCODE="%s" AND DATE = "%s"' % ( stockcode, current_quarter) db.delete(query_delete) db.insertdf(df[df['DATE'].isin([current_quarter])], 'jazzdb.T_STOCK_FINAN') return True else: return False ## IF CHANGED OR NOT EXISTS DO UPDATE 를 구현하도록 ! except Exception as e: print(e)
def insert_dataframe(df): db.insertdf(df, 'jazzdb.T_STOCK_OHLC_MIN')
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')
def get_bb_event(self, threshold=0, mid=0.5): bbp = self.df.BBP.values bbw = self.df.BBW.values date = self.df.DATE.values prev_point = 0 prev_event = '-' curr_event = None ret_df = pd.DataFrame(columns=[ 'PREV_IDX', 'DATE_IDX', 'DATE_FROM', 'DATE_TO', 'INTERVAL', 'PREV_EVENT', 'CURR_EVENT', 'BBP', 'BBW' ]) for i in range(0, len(bbp) - 1): # BBP 상향돌파 if bbp[i + 1] > bbp[i]: if bbp[i] < 1 - threshold <= bbp[i + 1]: curr_event = 'UU' elif bbp[i] < mid <= bbp[i + 1]: curr_event = 'MU' elif bbp[i] < threshold <= bbp[i + 1]: curr_event = 'LU' # BBP 하향돌파 elif bbp[i + 1] < bbp[i]: if bbp[i] > threshold >= bbp[i + 1]: curr_event = 'LD' elif bbp[i] > mid >= bbp[i + 1]: curr_event = 'MD' elif bbp[i] > 1 - threshold >= bbp[i + 1]: curr_event = 'UD' if curr_event is not None and curr_event != prev_event: ret_df.loc[len(ret_df)] = [ prev_point, i + 1, date[prev_point], date[i + 1], i + 1 - prev_point, prev_event, curr_event, bbp[i + 1], bbw[i + 1] ] prev_event = curr_event prev_point = i + 1 elif i + 1 == len(bbp) - 1: ret_df.loc[len(ret_df)] = [ prev_point, i + 1, date[prev_point], date[i + 1], i + 1 - prev_point, prev_event, '--', bbp[i + 1], bbw[i + 1] ] result_df = pd.DataFrame() prev_event_list = ret_df.PREV_EVENT.values.tolist() curr_event_list = ret_df.CURR_EVENT.values.tolist() interval_list = ret_df.PREV_IDX.values.tolist() bbw_list = ret_df.BBW.values.tolist() bbp_list = ret_df.BBP.values.tolist() if curr_event_list[-1] != '--': prev_event_list.append(curr_event_list[-1]) interval_list.append(59) result_df.loc[0, 'STOCKCODE'] = self.stockcode result_df.loc[0, 'DATE'] = self.the_date result_df.loc[0, 'DIR_L1'] = '%s' % (prev_event_list[-1]) result_df.loc[0, 'DIR_L2'] = '%s' % (prev_event_list[-2]) result_df.loc[0, 'DIR_L3'] = '%s' % (prev_event_list[-3]) result_df.loc[0, 'DIR_L4'] = '%s' % (prev_event_list[-4]) result_df.loc[0, 'DAYS_L1'] = int(59 - interval_list[-1]) result_df.loc[0, 'DAYS_L2'] = int(59 - interval_list[-2]) result_df.loc[0, 'DAYS_L3'] = int(59 - interval_list[-3]) result_df.loc[0, 'DAYS_L4'] = int(59 - interval_list[-4]) result_df.loc[0, 'BBW_L1'] = round(bbw_list[-1], 3) result_df.loc[0, 'BBW_L2'] = round(bbw_list[-2], 3) result_df.loc[0, 'BBW_L3'] = round(bbw_list[-3], 3) result_df.loc[0, 'BBW_L4'] = round(bbw_list[-4], 3) result_df.loc[0, 'BBP_L1'] = round(bbp_list[-1], 3) result_df.loc[0, 'BBP_L2'] = round(bbp_list[-2], 3) result_df.loc[0, 'BBP_L3'] = round(bbp_list[-3], 3) result_df.loc[0, 'BBP_L4'] = round(bbp_list[-4], 3) # print(result_df) db.insertdf(result_df, table='jazzdb.T_STOCK_BB_EVENT')
def insert_to_database(self, n=3): db.insertdf(self.df[[ 'STOCKCODE', 'DATE', 'PSMAR5', 'PSMAR20', 'PSMAR60', 'PSMAR120', 'PSMAR240', 'VSMAR5', 'VSMAR20', 'VSMAR60', 'VSMAR120', 'VSMAR240' ]].round(3).tail(n), table='jazzdb.T_STOCK_DAY_SMAR')
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: 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 df.PY = df.PY.astype(int) df["DATE_FINAN"] = df["PY"].astype(str) + df["QQ"] is_df = df[['DATE', 'DATE_FINAN']].copy() if len(is_df) > 0: print(is_df) db.insertdf(df[['DATE', 'DATE_FINAN']], 'jazzdb.T_DATE_FINAN') else: print("DONE~")