티스토리 뷰
1. 퀀트투자를 하기 위한반자동 자료 추출 필요성
퀀트투자를 하기 위해서는 자신만의 기준에 만족하는 종목을 골라야 합니다. 기준은 소형주, 순이익증가율, PBR, PSR, PER, 부채비율, 유동비율 등 자신의 기준에 맞는 지표를 골라 적용하면 됩니다. 그전에 각 기업들의 실적보고서에서 나에게 필요한 자료를 추출해야겠죠.(만약 매출액증가율을 구하고 싶다면 이번분기 매출액과 전년동기 매출액 자료를 추출해
매출액/전년동기매출액 값을 구해야 합니다.) 자료추출을 복사 붙여넣기로 할 수도 있으나 1년에 4번 실적보고서가 나올때마다 복붙을 하는건 비효율적입니다. 엑셀의 함수를 사용해 반자동화 한다면 좋지 않을까요?
2. 자료는 어디에서 구할 것인가
Fnguide(https://www.fnguide.com/) FnDB Navigator 매뉴에서 실적속보자료를 압축파일로 자료를 받을 수 있습니다. 몇년 전까지만 해도 공짜로 자료를 받을 수 있었는데 이제는 서비스를 구입해야 합니다. FnDB Navigator 1달 이용료는 11만원입니다. 그러나 증권사에 HTS를 활용하면 공짜로 받을 수 있습니다. 저는 이것도 모르고 1년 가까이 돈을 내고 자료를 받았습니다. ㅜㅜ;;
3. 엑셀 INDEX와 MATCH함수의 조합
자료추출 반자동화를 위한 엑셀 함수 공부 INDEX함수는 실적보고서에서 찾고자하는 종목의 열 번호와 찾고자 하는 자료의 행 번호를 알면 해당 값을 반환해 줍니다.
예를들어 경방의 매출액 자료를 찾는다면 우선 경방의 열번호를 찾고 회사명(A열)에서 5번째에 위치(열번호:5),매출액이 자료가 있는 행번호를 찾기 매출액은 목록항목(1행)에서 4번째에 위치(행번호:4) =INDEX(A:D,5,4)을 입력하면 해당 값을 반환이 됩니다. INDEX함수는의 역할을 이해했다면 이제 열번호와 행번호를 찾는 방법을 알아보겠습니다.
열번호와 행번호를 찾는 함수는 MATCH함수 입니다. MATCH함수는 찾을 값이 참조범위(행또는 열)에서 몇 번째에 위치해 있는지 알려줍니다. 경방이라는 회사가 회사명(A열)에서 몇 번째에 위치해 있는지 MATCH함수로 찾으면 열번호 5가 나옵니다. =MATCH("경방",A:A,0)=5 [찾을값 : "경방", 참조범위: A:A, 검색옵션:0(정확히 일치)] 항목(2행)에서 매출액이 몇번째에 위치해 있는지 MATCH함수로 찾으면 행번호4가 나옵니다.=MATCH("매출액",1:1,0)=4 [찾을값: "매출액", 참조범위:2:2, 검색옵션:0(정확히 일치)]
INDEX함수의 열번호, 행번호 위치에 MATCH함수를 넣어보면=INDEX(A:ZZ,MATCH("경방",A:A,0),MATCH("매출액",1:1,0)) 실적보고서 시트의 A부터 ZZ열의 데이터에서 회사명이 있는 열에서 "경방"의 열번호와 항목이 적힌 행에서 "매출액"이 써져 있는 행번호를 찾아 해당위치의 값을 반환해 줍니다.
"경방"이라는 특정회사 대신에 A열의 회사가 선택되도록 ("경방" ☞ $A3), $표시는 A열에 고정한다는 뜻. "매출액"이 아니라 필요한 항목이 적용 되도록 하기 위해서 ("매출액" ☞ D$1), $표시는 1행에 고정하는는 뜻.
=INDEX($A:$ZZ,MATCH($A3,$A:$A,0),MATCH(D$1,$1:$1,0))
4. 자료추출 연습
실적보고서 자료에서 회사명, 시장, 산업코드, 산업명, 회계기준, 매출액(now), 전년동기 매출액, 매출총이익(now), 전년동기 매출총이익, 영업이익(now), 전년동기 영업이익, 순이익(now), 전년동기 순이익, 총포괄이익(now), 전년동기 총포괄이익, 자본(now), 부차(now), 유동자산(now), 유동부채(now)을 추출해 보겠습니다.
새로운 시트(추출시트)에 A열에 실적보고시트에 있는 종목코드를 복사붙여 넣기를 합니다. B1부터 회사명, 시장, 산업코드, 산업명, ....등을 입력합니다. 실적보고 시트에서 방금적은 항목과 일치하지 않은 항목을 바꿔줍니다.
추출시트에서 b2에 아래의 함수를 입력합니다. =INDEX(실적보고!$A:$ZZ,MATCH($A2,실적보고!$B:$B,0),MATCH(B$1,실적보고!$2:$2,0)) 빈칸을 채워넣기 하면 자료가 채워집니다. ^^ 1년에 4번 기업 실적보고서가 나올 때마다 이 방법을 이용해 원하는 자료를 추출해 쓸 수 있습니다.
