미국 주식 데이터 수집 #3 데이터 저장 및 오류 수정
퀀트 프로그램/데이터 수집2020. 12. 9. 19:27
안녕하세요, 오늘은 미국 주식 데이터 수집 세 번째 시간입니다. 지난 시간까지 Yahoo-fin 라이브러리를 통해 어떻게 원하는 데이터를 가져오는지 알아보았습니다. 이번 포스팅에서는 수집한 데이터를 데이터베이스에 저장하는 것과 발생한 오류를 수정하는 과정을 담아보겠습니다.
수집한 데이터를 이렇게 저렇게 조작해보고, 다양한 인사이트를 얻기 위해 사용할 것이기 때문에 데이터베이스에 저장할 것입니다. 전체 데이터를 가지고 필터를 걸고, 순위를 매기고 하기 위해서는 데이터베이스에 저장하는 것이 필수입니다. 매번 라이브러리를 통해 실시간으로 정보를 받아오게 된다면 시간도 오래 소요되고, 어려울 것이기 때문입니다.
1. 테이블 생성
저는 MySQL이라는 무료 데이터베이스를 사용할 것이지만, 각자 본인이 편하게 다룰 수 있는 DB가 있다면 무엇을 사용하든 상관은 없습니다.
CREATE TABLE `STOCK_INFO_USA` (
`STOCK_CODE` VARCHAR(10) NOT NULL COMMENT '종목 코드',
`PRICE` FLOAT DEFAULT NULL COMMENT '주가',
`PRICE1` FLOAT DEFAULT NULL COMMENT '1개월 등락률',
`PRICE3` FLOAT DEFAULT NULL COMMENT '3개월 등락률',
`PRICE6` FLOAT DEFAULT NULL COMMENT '6개월 등락률',
`PRICE9` FLOAT DEFAULT NULL COMMENT '9개월 등락률',
`PRICE12` FLOAT DEFAULT NULL COMMENT '9개월 등락률',
`VOLUME` FLOAT DEFAULT NULL COMMENT '거래대금',
`OPM` FLOAT DEFAULT NULL COMMENT 'OPM',
`ROE` FLOAT DEFAULT NULL COMMENT 'ROE',
`ROA` FLOAT DEFAULT NULL COMMENT 'ROA',
`MARKET_CAP` FLOAT DEFAULT NULL COMMENT '시가총액',
`PER` FLOAT DEFAULT NULL COMMENT 'PER',
`PEGR` FLOAT DEFAULT NULL COMMENT 'PEGR',
`PBR` FLOAT DEFAULT NULL COMMENT 'PBR',
`PSR` FLOAT DEFAULT NULL COMMENT 'PSR',
`ASSET` FLOAT DEFAULT NULL COMMENT '자산',
`EQUITY` FLOAT DEFAULT NULL COMMENT '자본',
`LIAB` FLOAT DEFAULT NULL COMMENT '부채',
`CUR_RE` FLOAT DEFAULT NULL COMMENT '매출액',
`CUR_OI` FLOAT DEFAULT NULL COMMENT '영업이익',
`CUR_NI` FLOAT DEFAULT NULL COMMENT '순수익',
`PRE1_RE` FLOAT DEFAULT NULL COMMENT '매출액(전)',
`PRE1_OI` FLOAT DEFAULT NULL COMMENT '영업이익(전)',
`PRE1_NI` FLOAT DEFAULT NULL COMMENT '순수익(전)',
`PRE2_RE` FLOAT DEFAULT NULL COMMENT '매출액(전전)',
`PRE2_OI` FLOAT DEFAULT NULL COMMENT '영업이익(전전)',
`PRE2_NI` FLOAT DEFAULT NULL COMMENT '순수익(전전)',
`RE_QOQ1` FLOAT DEFAULT NULL COMMENT '매출액(QOQ1)',
`OI_QOQ1` FLOAT DEFAULT NULL COMMENT '영업이익(QOQ1)',
`NI_QOQ1` FLOAT DEFAULT NULL COMMENT '순수익(QOQ1)',
`RE_QOQ2` FLOAT DEFAULT NULL COMMENT '매출액(QOQ2)',
`OI_QOQ2` FLOAT DEFAULT NULL COMMENT '영업이익(QOQ2)',
`NI_QOQ2` FLOAT DEFAULT NULL COMMENT '순수익(QOQ2)',
`RGST_DATE` VARCHAR(10) DEFAULT NULL COMMENT '입력일',
PRIMARY KEY (`STOCK_CODE`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8;
2. 데이터 입력
conn = connect_db()
curs = conn.cursor()
sql = """insert into STOCK_INFO_USA(STOCK_CODE, PRICE, PRICE1, PRICE3, PRICE6, PRICE9, PRICE12, VOLUME, OPM, ROE, ROA, MARKET_CAP, PER, PEGR, PBR, PSR, ASSET, EQUITY, LIAB, CUR_RE, CUR_OI, CUR_NI, RE_QOQ1, OI_QOQ1, NI_QOQ1, RE_QOQ2, OI_QOQ2, NI_QOQ2, RGST_DATE)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
# 반복문 돌면서 INSERT
for i, ticker in enumerate(stock_list):
try:
stock_info = [ticker] + get_price(ticker)
sleep(2)
stock_info = stock_info + get_stat(ticker)
sleep(2)
stock_info = stock_info + get_valuation(ticker)
sleep(2)
stock_info = stock_info + get_balance(ticker)
sleep(2)
stock_info = stock_info + get_income(ticker)
sleep(2)
stock_info = stock_info + [today]
stock_info = [0 if type(x) != str and math.isnan(x) else x for x in stock_info]
curs.execute(sql, stock_info)
conn.commit()
print(i, ' 번 째 입력 완료 : ', ticker)
except Exception as e:
print(i, ' 번 째 오류 발생 : ', ticker, ' 오류:', str(e))
conn.close() 이전에 생성한 함수들을 결합해 하나의 세트로 만들고 DB에 입력해보겠습니다. 중간에 2초씩 딜레이를 주는 이유는 Yahoo-fin에서 연속된 명령을 차단하는지 끊겨서 응답이 없는 상황이 발생해서 입니다. 약 9천개나 되는 종목을 조회하는 것이라 시간이 매우 오래 걸리기 때문에, 밤새 한 번 돌려보았습니다.
그 결과, 오류가 많이 났네요🤣
물론 이렇게 잘 들어간 데이터도 많이 있습니다.
3.오류 수정
로그로 남긴 에러메시지를 바탕으로 하나씩 수정해나가보도록 하겠습니다.
'float' object has no attribute 'replace'
이 메시지는 float 타입을 replace하려고 시도할 때 발생하는 오류 메시지입니다. replace는 str타입에서 가능한 것인데, 값이 없어서 nan으로 return이 온 것들을 replace하려고 해서 에러가 발생합니다. replace 전에 type이 str인지 확인하는 절차를 추가해서 해결했습니다.
division by zero
말 그대로 0으로 나눌 수 없다고 나는 에러입니다. 등락률을 구할 때 분모에 0이 들어가는 값들이 있어서 에러가 났습니다. 나누기를 하기 전에 값이 0인지 먼저 확인하는 절차를 추가했습니다.
could not convert string to float: '7.03k', '-3,965.04'
str 타입을 float 타입으로 변형할 수 없다고 발생하는 에러입니다. 대부분의 str 값은 치환해서 float형으로 바꿀 수 있도록 처리한 줄 알았는데, 예상치 못하게 PER 값에 k가 들어오는 게 있었습니다. (PER가 몇 천이라니..🙄) 또한 값들 중 천 단위에 쉼표가 포함되어 있는 건들도 있어서 제거해주었습니다.
Length mismatch: Expected axis has 4 elements, new values have 2 elements
이 에러메시지는 get_statssi.get_stats(ticker)에서 에러가 나면서 출력되는 메시지입니다. 에러가 나는 티커들로 직접 Yahoo Finance에 들어가서 조회를 해보니 Statistics 탭이 Premium을 사용해야 접근이 가능한 것처럼 보입니다. Current 정보만이라도 가져올 수 있으면 좋을 것 같은데 API 자체가 오류가 나서 방법이 없어보입니다. 딱히 해결방법이 없어서 일단은 제외하고 진행하도록 했습니다.
4. 수정 후 소스
crtn_ymd = (datetime.datetime.now()-relativedelta(months=13)).strftime('%Y-%m-%d') #13개월전
ymd1 = (datetime.datetime.now()-relativedelta(months=1)).strftime('%Y-%m-%d') #1개월전
ymd3 = (datetime.datetime.now()-relativedelta(months=3)).strftime('%Y-%m-%d') #3개월전
ymd6 = (datetime.datetime.now()-relativedelta(months=6)).strftime('%Y-%m-%d') #6개월전
ymd9 = (datetime.datetime.now()-relativedelta(months=9)).strftime('%Y-%m-%d') #9개월전
ymd12 = (datetime.datetime.now()-relativedelta(months=12)).strftime('%Y-%m-%d') #12개월전
# TICKER를 받아서 주가 및 등락률을 반환하는 함수
def get_price(ticker):
df_price_info = si.get_data(ticker, start_date = crtn_ymd, interval="1d", index_as_date = False)
df_price_info = df_price_info.fillna(0)
length = len(df_price_info)
price, price1, price3, price6, price9, price12, volume = 0, 0, 0, 0, 0, 0, 0
#시기별 주가
price = float(df_price_info.at[length-1,'adjclose']) # 최신 주가
if (df_price_info['date'] <= ymd1).any() :
price1 = df_price_info.loc[df_price_info['date'] <= ymd1,'adjclose'].iloc[-1]
volume = df_price_info[-10:].volume.mean() # 최근 10일 거래량 평균
if (df_price_info['date'] <= ymd3).any() :
price3 = df_price_info.loc[df_price_info['date'] <= ymd3,'adjclose'].iloc[-1]
if (df_price_info['date'] <= ymd6).any() :
price6 = df_price_info.loc[df_price_info['date'] <= ymd6,'adjclose'].iloc[-1]
if (df_price_info['date'] <= ymd9).any() :
price9 = df_price_info.loc[df_price_info['date'] <= ymd9,'adjclose'].iloc[-1]
if (df_price_info['date'] <= ymd12).any() :
price12 = df_price_info.loc[df_price_info['date'] <= ymd12,'adjclose'].iloc[-1]
change1, change3, change6, change9, change12 = 0, 0, 0, 0, 0
#등락률 = (현재주가/과거주가 - 1)
price = round(price, 2)
if price1 != 0 : change1 = round((price / price1 -1) * 100, 2)
if price3 != 0 : change3 = round((price / price3 -1) * 100, 2)
if price6 != 0 : change6 = round((price / price6 -1) * 100, 2)
if price9 != 0 : change9 = round((price / price9 -1) * 100, 2)
if price12 != 0 : change12 = round((price / price12 -1) * 100, 2)
return [float(price), float(change1), float(change3), float(change6), float(change9), float(change12), float(volume)]
# TICKER를 받아서 거래대금(10일 평균), OPM, ROE, ROA을 반환하는 함수
def get_stat(ticker):
df_stat_info = si.get_stats(ticker)
df_stat_info = df_stat_info.fillna(0)
avg_vol = df_stat_info.at[8, 'Value']
opm = df_stat_info.at[31, 'Value']
roe = df_stat_info.at[32, 'Value']
roa = df_stat_info.at[33, 'Value']
# 전처리
if avg_vol[-1] == 'k':
avg_vol = float(avg_vol.replace('k','')) * 1000
elif avg_vol[-1] == 'M':
avg_vol = float(avg_vol.replace('M','')) * 1000000
elif avg_vol[-1] == 'B':
avg_vol = float(avg_vol.replace('B','')) * 1000000000
elif avg_vol[-1] == 'T':
avg_vol = float(avg_vol.replace('T','')) * 1000000000000
if type(opm) == str : opm = float(opm.replace('%','').replace(',',''))
if type(roe) == str : roe = float(roe.replace('%','').replace(',',''))
if type(roa) == str : roa = float(roa.replace('%','').replace(',',''))
return [avg_vol, opm, roe, roa]
# TICKER를 받아서 시가총액, PER, PEGR, PBR, PSR를 반환하는 함수
def get_valuation(ticker):
df_valuation = si.get_stats_valuation(ticker)
df_valuation = df_valuation.fillna(0)
market_cap = df_valuation.iloc[0,1]
if market_cap[-1] == 'k':
market_cap = float(market_cap.replace('k','')) * 1000
elif market_cap[-1] == 'M':
market_cap = float(market_cap.replace('M','')) * 1000000
elif market_cap[-1] == 'B':
market_cap = float(market_cap.replace('B','')) * 1000000000
elif market_cap[-1] == 'T':
market_cap = float(market_cap.replace('T','')) * 1000000000000
per = df_valuation.iloc[2,1]
if type(per) == str and per[-1] == 'k': per = float(per.replace('k','')) * 1000
pegr = df_valuation.iloc[4,1]
if type(pegr) == str and pegr[-1] == 'k': pegr = float(pegr.replace('k','')) * 1000
pbr = df_valuation.iloc[5,1]
if type(pbr) == str and pbr[-1] == 'k': pbr = float(pbr.replace('k','')) * 1000
psr = df_valuation.iloc[6,1]
if type(psr) == str and psr[-1] == 'k': psr = float(psr.replace('k','')) * 1000
return [market_cap, per, pegr, pbr, psr]
# TICKER를 받아서 부채비율을 반환하는 함수
def get_balance(ticker):
df_balance = si.get_balance_sheet(ticker)
df_balance = df_balance.fillna(0)
asset = float(df_balance.iloc[3,0]) # 자산
equity = float(df_balance.iloc[1,0]) # 자본
liab = float(df_balance.iloc[0,0]) # 부채
return [asset, equity, liab]
# TICKER를 받아서 매출액, 영업이익, 순이익(분기별)을 반환하는 함수
def get_income(ticker):
df_income = si.get_income_statement(ticker, yearly = False).loc[['totalRevenue','operatingIncome','netIncome'],:]
df_income = df_income.fillna(0)
cur_re = float(df_income.iloc[0,0]) # 최근 매출액
cur_oi = float(df_income.iloc[1,0]) # 최근 영업이익
cur_ni = float(df_income.iloc[2,0]) # 최근 순이익
pre1_re = float(df_income.iloc[0,1]) # 전분기 매출액
pre1_oi = float(df_income.iloc[1,1]) # 전분기 영업이익
pre1_ni = float(df_income.iloc[2,1]) # 전분기 순이익
pre2_re = float(df_income.iloc[0,2]) # 전전분기 매출액
pre2_oi = float(df_income.iloc[1,2]) # 전전분기 영업이익
pre2_ni = float(df_income.iloc[2,2]) # 전전분기 순이익
re_qoq1, oi_qoq1, ni_qoq1, re_qoq2, oi_qoq2, ni_qoq2 = 0, 0, 0, 0, 0, 0
# 전처리
# 전처리
if pre1_re != 0 : re_qoq1 = round((cur_re - pre1_re) / abs(pre1_re) *100,2) # 현분기 QOQ(매출액)
if pre1_oi != 0 : oi_qoq1 = round((cur_oi - pre1_oi) / abs(pre1_oi) *100,2) # 현분기 QOQ(영업이익)
if pre1_ni != 0 : ni_qoq1 = round((cur_ni - pre1_ni) / abs(pre1_ni) *100,2) # 현분기 QOQ(순이익)
if pre2_re != 0 : re_qoq2 = round((pre1_re - pre2_re) / abs(pre2_re) *100,2) # 전분기 QOQ(매출액)
if pre2_oi != 0 : oi_qoq2 = round((pre1_oi - pre2_oi) / abs(pre2_oi) *100,2) # 전분기 QOQ(영업이익)
if pre2_ni != 0 : ni_qoq2 = round((pre1_ni - pre2_ni) / abs(pre2_ni) *100,2) # 전분기 QOQ(순이익)
return [cur_re,cur_oi,cur_ni,pre1_re,pre1_oi,pre1_ni,pre2_re,pre2_oi,pre2_ni,re_qoq1,oi_qoq1,ni_qoq1,re_qoq2,oi_qoq2,ni_qoq2]
conn = connect_db()
curs = conn.cursor()
sql = """insert into STOCK_INFO_USA(STOCK_CODE, PRICE, PRICE1, PRICE3, PRICE6, PRICE9, PRICE12, VOLUME, OPM, ROE, ROA, MARKET_CAP, PER, PEGR, PBR, PSR, ASSET, EQUITY, LIAB, CUR_RE, CUR_OI, CUR_NI, PRE1_RE, PRE1_OI, PRE1_NI, PRE2_RE, PRE2_OI, PRE2_NI, RE_QOQ1, OI_QOQ1, NI_QOQ1, RE_QOQ2, OI_QOQ2, NI_QOQ2, RGST_DATE)
values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
# 반복문 돌면서 INSERT
for i, ticker in enumerate(stock_list):
try:
stock_info = [ticker] + get_price(ticker)
stock_info = stock_info + get_stat(ticker)
stock_info = stock_info + get_valuation(ticker)
stock_info = stock_info + get_balance(ticker)
stock_info = stock_info + get_income(ticker)
stock_info = stock_info + [today]
sleep(2)
curs.execute(sql, stock_info)
conn.commit()
# print(i, ' 번 째 입력 완료 : ', ticker)
except Exception as e:
print(i, ' 번 째 오류 발생 : ', ticker, ' 오류:', str(e))
conn.close()
댓글()