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)