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: