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~")
