데이터로 하는 재테크, D-Tech!
오늘은 영업이익과 주가 변화를 활용해 포트폴리오를 만들어 보겠습니다. 간단하게 '영업이익은 올랐지만, 주가는 오히려 떨어진 주식이라면 매수 찬스?'라는 아이디어로 포트폴리오를 구성해서 시뮬레이션하는 과정입니다.
결론부터 말씀드리면, 이 포트폴리오는 보유기간 6개월에 -9% 의 손해를 보게 됩니다. 하지만 상상력을 발휘해 로직을 조금만 변경하면 6개월에 플러스 46% 수익률이 나오기도 합니다. 제가 설명드리고 싶은 건 엑셀만 활용해서 포트폴리오를 구성하고 시뮬레이션까지 해볼 수 있는 과정을 익혀 본다는 점입니다. 이 과정을 익혀서 실제 수익을 낼 수 있는 포트폴리오를 직접 찾아보시길 권유드립니다. (재미에 빠지면 밤 새 찾아 헤매실지도 모릅니다.)
내용은 좀 길 수 있는데, 따라 하는 과정은 그리 길지도 않습니다.~
- 필요 IT 기술 : 엑셀
- 필요 데이터 : DART의 2018년 사업보고서, 특정 월의 종목별 종가(엑셀로 별도 첨부해드립니다.)
포트폴리오 구성 시나리오
영업이익은 오르고, 주가는 내린 종목을 찾아서 포트폴리오를 구성하는 전략.
a. DART의 2018년 사업보고서를 사용
- 2017년 대비 2018년 영업이익이 가장 많이 오른 종목을 찾는다.
b. 2018년 4월 대비, 2019년 4월에 주가가 가장 많이 내린 종목을 찾는다.
- 왜 2018년, 2019년 4월 주가일까요?
* 2018년 사업보고서는 2019년 4월쯤 접할 수 있습니다.
* 그러므로 해당 시점과 1년 전의 주가를 비교하는 것으로 설정했습니다.
c. a와 b를 각각 순위를 매긴 후에, 두 순위 합이 가장 작은 10개 종목을 투자
- 2019년 4월에 매수해서 2019년 9월에 매도한다.
* 왜 2019년 9월 매도일까요?
* 원래는 1년 보유로 2020년 4월로 시뮬레이션을 해볼까 했습니다.
* 하지만 2020년 초에는 코로나로 인해 시뮬레이션에 적합하지 않습니다.
* 그러므로 2019년9월로 우선 잡아봤습니다.
* 이러한 매수/매도 시점을 어떻게 잡느냐에 결과는 확연히 달라질 수 있습니다.
이와 같은 시나리오로 포트폴리오를 구성하고 수익률까지 계산해 보겠습니다. 주의할 점은, 이와 같이 1년치 데이터를 가지고 시뮬레이션한 결과로 투자 전략을 확정해서는 절대 안 됩니다. 가능한 많은 데이터를 수집해 동일한 로직을 적용해보고 평균적으로 승산이 있을 때, 투자 전략을 사용해야 합니다.
설명에 앞서 항상 강조드리고 싶은 말은, 이 글은 투자를 권유하거나 특정 종목을 추천하려는 글이 아닙니다. 데이터를 이와 같이 분석할 수 있음을 알려드리고 싶은 글입니다. 또한 이 글에 나온 종목에 투자해 발생한 손해는 투자자 본인의 책임임을 명심하시기 바랍니다.
이 글을 처음 접하시는 분은, 전의 글 'DART로 20년 3분기 영업이익 좋은 종목 찾기'를 먼저 따라 해 주세요.
DART로 20년 3분기 영업이익 좋은 종목 찾기
DART로 20년 3분기 영업이익 좋은 종목 찾기 데이터를 활용하는 재테크, D-Tech입니다. 이름만 너무 거창한 거 아닌지 모르겠습니다. 민망함을 무릅쓰고 D-Tech란 주제 아래 글을 하나씩 채워보도
sweetquant.tistory.com
1. DART에서 2018년 사업 보고서 다운로드하기.
[그림. 1]을 참고해 DART(http://dart.fss.or.kr/)에서 2018년의 사업보고서를 다운로드합니다.
2018년 사업보고서는 2018년 12월 31일 기준으로 결산한 보고서입니다. 저희가 실제 접할 수 있는 시점은 2019년 4월쯤입니다. 받은 파일의 압축을 풀면 네 개의 파일이 있는데 그중에 아래 파일을 엑셀에서 엽니다.
- 2019_사업보고서_03_포괄손익계산서_연결_20201117.txt
2. 불필요 컬럼 삭제
엑셀에서 분석에 용이하도록 불필요한 컬럼을 삭제합니다. [그림. 2]를 참고해주세요.
P 열 전체를 지우는 방법은 아래와 같습니다.
1. 열의 헤더인 'P'에 대고 마우스 우클릭을 합니다.
2. 삭제를 선택해 열 전체를 삭제합니다.
위 과정을 반복해, O, M, J, I, G, E, A 열을 제거합니다. (뒤쪽 열부터 제거해야 헷갈리지 않습니다.)
3. 영업이익과 2018년 12월 31일 결산만 골라내기
방금은 열(Column) 단위로 불필요한 것을 제거했습니다. 이번에는 필터 기능을 사용해 필요한 행(Row)만 골라내도록 합니다. 먼저 영업이익 항목만 골라내고, 그다음에 2018년 12월 31일 결산 데이터만 골라냅니다. 그리고 당기(2018년) 영업이익이 500억 이상만 추려내도록 하겠습니다. 아래의 [그림. 3]과 [그림. 4]를 참고해주세요.
1. 엑셀 최상위 메뉴에서 데이터 > 필터를 선택
2. 항목코드 부분의 화살표를 클릭
3. dart_OperatingIncomeLoss를 입력(해당 항목코드가 영업이익입니다.)
4. 확인을 클릭 ( 항목코드가 영업이익인 데이터만 조회됩니다.)
5. 결산기준일 부분의 화살표를 클릭
6. 2018년 12월 31일만 선택 (종목에 따라 2018년 3월 결산된 데이터가 섞여 있기도 합니다.)
7. 확인을 클릭
이번에는 [그림. 4]와 같은 과정으로 '당기'(2018년)의 영업이익이 500억 이상인 종목만 필터 합니다. (왜 500억이냐고 물으신다면, 특별한 이유는 없습니다. 적당히 이익이 큰 회사를 투자하자 생각했습니다. 나중에는 이러한 금액도 특정한 논리나 계산을 통해 정하면 좋을 거 같습니다. 그리고 이 부분을 변경함에 따라 수익률도 변경되니 도전해 보시기 바랍니다.)
4. 작업된 데이터를 새 엑셀 파일로 카피
불필요한 열(Column)은 삭제했지만 불필요한 행(Row)은 삭제가 아닌 필터 처리했습니다. 현재 작업하고 있는 엑셀 시트에 숨겨져 있는 상황입니다. 이대로는 나머지 분석을 하기가 효율적이지 못합니다. 작업한 내용을 카피해서 새로운 엑셀 문서로 옮깁니다. [그림. 5]를 참고합니다.
1. 필터 작업을 마친 엑셀에서 Ctrl+A 입력(전체 선택), Ctrl+C 입력(카피), Ctrl+N을 입력(새 엑셀 열기)
2. 열린 새 엑셀에서 Ctrl+V 입력(붙여 넣기)
열린 새 엑셀에 작업한 내용을 붙여 넣은 다음에 '새 이름으로 저장'을 해주세요. 그리고 헷갈리지 않게 DART에서 다운로드하여 열었던 2018년 사업보고서는 닫아 주세요.
5. 특정 월의 종가 가져오기
포트폴리오를 구성하고 수익률까지 계산하기 위해서는 종목별로 특정 월의 종가가 필요합니다. 제가 보유한 데이터를 정리해서 올려드리니 아래 엑셀을 다운해 주세요. 첨부 파일에는 종목별로 18년 4월의 종가, 19년 4월 종가, 19년 9월의 종가가 있습니다.
다운로드한 파일의 내용을 지금 작업하고 있는 엑셀의 AA 열에 붙여주세요. [그림. 6]과 같습니다.
1. '종목별_특정월종가' 엑셀을 열고 Ctrl+A(전체 선택) 입력, Ctrl+C(복사) 입력
2. 작업 중인 엑셀의 AA 열로 이동, Ctrl+V(붙여 넣기) 입력
: 다음 작업에 필요한 공간을 넉넉히 비우기 위해 AA정도로 했습니다.
: 만약에 AA가 아닌 다른 곳에 붙여 넣으시면, 다음 VLOOKUP 작업에서 영역 변경이 필요합니다.
6. VLOOKUP으로 종가 붙이기
현재 가져온 월별 종가와 작업 중이던 엑셀의 종목코드는 순서가 맞지 않습니다.
다시 말해 작업 중이던 엑셀의 첫 번째 종목과 월별 종가의 첫 번째 종목은 다른 종목입니다. 그러므로 작업 중이던 엑셀에, 자신의 종목코드에 맞는 종가를 찾아서 붙여주어야 합니다. 이러한 기능을 할 수 있는 것이 바로 VLOOKUP입니다. (VLOOKUP 이해를 위해서는 별도 다른 블로그 등을 참고해주시면 좋을 거 같습니다. 우선은 따라 해 보시죠.)
[그림. 7]과 같이 VLOOKUP 작업을 해서 우리가 작업하던 엑셀에 월별 종가를 추가합니다.
1. M~O 열 의 1행에 헤더명을 입력
- M1 : 18년4월종가 , N1 : 19년4월종가 , O1 : 19년9월종가
2. M2(M열 2행) : =VLOOKUP(A2,AA:AD,2,FALSE)
3. N2(N열 2행) : = VLOOKUP(A2,AA:AD,3,FALSE)
4. O2(O열 2행) : = VLOOKUP(A2,AA:AD,4,FALSE)
VLOOKUP 작업을 하실 때, 엑셀의 카피 기능을 사용하면, 자동으로 영역이 변형되어 비정상적인 수치가 나옵니다. 반드시 위와 같이 각각 직접 입력을 해주세요. 위와 같이 입력을 하면, AA부터 붙여놓은 종목별 종가에서 맞는 종가를 찾아 M2, N2, O2에 입력이 됩니다.
이제 입력한 VLOOKUP을 밑으로(행으로) 카피를 해주셔야 합니다. [그림. 8]을 참고하세요.
1. M2부터 O2를 마우스로 선택 (반드시 세 개 셀만 선택합니다.)
2. 선택된 곳에서 우측 아래 부분에 마우스를 갖다 대면, 십자가 커서가 나옵니다. 이때 더블클릭
: 더블 클릭하면 VLOOKUP 한 세 개의 셀이 아래 로우들에 모두 자동으로 카피가 됩니다.
: 각자 편한 방법으로 카피하셔도 됩니다.
7. 당기 / 전기 수식 추가하기
전기 대비 당기에 영업이익이 얼마나 늘어났는지 계산하기 위해 당기 / 전기 수식을 추가합니다. 2018년 사업보고서이기 때문에 당기는 2018년, 전기는 2017년입니다. [그림. 9]와 같이 작업합니다.
1. P열 2행에 '=J2/K2' 수식을 입력합니다.(따옴표(')는 입력하지 않습니다.)
2. 소수점을 표시하기 위해 소수점 늘이기를 세 번 정도 클릭합니다.(엑셀 상단 '홈' 메뉴에 있습니다.)
P2 셀의 수식을 아래 행에 모두 카피해줍니다. 편하신 방법을 사용하시면 됩니다. ('6. VLOOKUP으로 종가 붙이기'에서 입력한 것을 아래 행에 모두 카피한 것처럼 방금 입력한 P2 셀의 우측 하단에 마우스를 갖다 대고 더블 클릭하시면 됩니다.)
8. 당기/전기 정렬 및 순위 구하기
만들어진 당기/전기 값으로 내림차순 정렬합니다. 다시 말해 2017년 대비 2018년 영업이익이 많이 늘어난 종목이 먼저 나오도록 정렬합니다. [그림. 10]과 같습니다.
1. 엑셀 최상단 메뉴에서 데이터 선택
2. 정렬 메뉴 선택
3. 그림과 같이 '당기/전기', '값', '내림차순'으로 세팅
4. 확인
정렬을 하고 나면 [그림. 11]과 같이 전기의 영업이익이 없는 데이터가 가장 위에 올라와 있습니다. '당기/전기'의 값이 '#DIV/0!'입니다. 분모의 값이 없거나 0인 경우에 이와 같은 에러 값이 나오게 됩니다. 해당 행을 삭제합니다. 분석 과정에 방해가 됩니다. 해당 행(2행) 헤더 부분에 마우스 우클릭 후에 '삭제'를 선택해 행 전체를 지웁니다.
데이터가 '당기/전기' 순서로 내림차순 되어 있습니다. 그러므로 '당기/전기 순위'를 만들어 보이는 순서대로 순위를 추가합니다.
1. O열 1행을 '당기/전기 순위'로 하고, O2(O열 2행)에 1, O3에 2, O4에 3을 입력합니다.
2. O2부터 O4까지 세 개 셀을 마우스로 선택한 후에, 우측 아래 부분을 더블클릭합니다.
- 세 개 행 정도가 연속된 숫자인 상태에서 아랫부분을 더블클릭하면, 나머지 행에 순차적 번호가 자동 생성됩니다.
당기/전기 순위까지 만들어졌습니다. 순위가 작은 종목이 좋은 종목이겠죠.(영업이익이 많이 오른 종목)
9. 19년4월 / 18년4월 종가 수식 구하기
영업이익만이 아니라, 주가가 많이 떨어진 것까지 고려해서 포트폴리오를 만들 예정입니다. [그림. 13]과 같이 18년 4월 대비 19년 4월의 종가 비율을 계산합니다.
1. R열 1행에는 '19년/18년'으로 헤더 명을 입력, R열 2행에는 '=N2/M2' 수식을 입력합니다.
만들어진 수식을 아래 행에 모두 카피를 해주시면 됩니다.(앞에서도 반복 설명했기 때문에, 설명은 생략합니다.)
10. 19년4월 / 18년4월 종가 순위 구하기
만들어진 '19년/18년' 값에 따라 오름차순 정렬을 하고 순위를 구합니다. '8. 당기/전기 정렬 및 순위 구하기' 단계와 같은 방법이므로 설명을 생략하고 그림만 올립니다. 주의할 점은 내림차순이 아닌 오름차순입니다. 오름차순이므로 가격이 많이 떨어진 종목부터 조회가 됩니다.
[그림. 14]와 같이 정렬을 한 후에 [그림. 15]와 같이 S열에 '19년/18년 순위'를 추가해서 순위 작업을 합니다. '8. 당기/전기 정렬 및 순위 구하기' 단계를 참고하시면 어렵지 않게 하실 수 있습니다.
11. 투자 대상 순위 구하기
'당기/전기 순위'와 '19년/18년 순위'를 구했으면 두 셀의 값을 더해서 '투자 대상 순위'를 구합니다. T열 1행에 '투자 대상 순위'라고 항목명을 정하고, T2에 '= Q2 + S2'를 입력합니다. T2의 입력한 내용을 아래 행으로 모두 카피해줍니다. [그림. 16]과 같이 투자 대상 순위가 만들어집니다.
12. 투자 대상 순위로 정렬하고 수익률 구하기
길고 긴 내용을 따라오느라 고생 많으셨습니다. 드디어 마지막 단계입니다. 만들어진 '투자 대상 순위'로 오름차순 정렬을 합니다. '투자 대상 순위'는 '당기/전기 순위'(영업이익이 늘어난 비율 순위)와 '19년/18년 순위'(18년 대비 19년에 주가가 많이 떨어진 순위'를 합한 값입니다. 즉, '투자 대상 순위'가 작을수록 영업이익은 늘어났지만 주가는 오히려 많이 떨어졌거나 그만큼 안 오른 종목이 됩니다.
[그림. 17]과 같이 '투자 대상 순위'로 정렬을 합니다.
정렬된 결과의 U2에 수익률을 구하는 공식을 입력하고, 해당 공식을 마지막 행까지 복사합니다. [그림. 18]처럼 작업하시면 됩니다.
1. U2에 수익률 공식을 입력 : =(O2-N2)/N2
2~3. 입력한 공식의 결과가 백분율로 안 나올 경우에, 홈 메뉴에 % 표시를 클릭, 백분율로 나오도록 처리.
위와 같은 작업을 한 후에 만들어진 수익률을 밑에 행까지 모두 카피해주세요.
13. 결과 마무리
'12. 투자 대상 순위로 정렬하고 수익률 구하기'까지 마무리한 결과, '투자 대상 순위' 상위 10개 종목을 2019년 4월에 매수에 2019년 9월에 매도했다면, -9%의 손해를 보게 됩니다. 절대 사용하면 안 되는 투자 전략이라 할 수 있습니다.
이를 통해 생각해 볼 문제는 이겁니다. '영업이익이 오른 것에 대비해, 주가가 덜 오른 주식은.. 위험할 수 있다.'입니다.
이익은 올랐지만 가격이 오르지 못한 것에는 다 이유가 있는 거겠죠. 그렇다면 반대로 영업이익도 오르고 주가도 오른 주식을 투자하는 역발상 투자는 어떨까요? 스스로 한 번 수익률을 계산해 보시기 바랍니다.!
그리고 이와 같이 엑셀로 데이터를 분석하는 작업에는 한계가 있습니다. 많은 데이터를 다양한 수식으로 변형하며 작업을 하기에는 너무 복잡하고 어렵습니다. 기회가 되신다면 데이터베이스와 SQL을 공부해보시기를 추천드립니다. 좀 더 쉽게 투자 전략을 세울 수 있습니다.
오늘 설명드렸던 내용은, 데이터만 갖추어져 있다면 SQL 한 문장만으로 구현이 가능합니다. 조건에 대한 변경도 매우 쉽고요.
SQL과 주식 공부에 뜻이 있으신 분은 아래 내용을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
※ 책 소개: https://sweetquant.tistory.com/243
※ 책 미리보기: https://sweetquant.tistory.com/257
※ 완전판 E-Book
▶ 유페이퍼: https://www.upaper.net/ryu1hwan/1142997
▶ 알라딘: https://www.aladin.co.kr/shop/wproduct.aspx?ItemId=273701425
▶ Yes24: http://www.yes24.com/Product/Goods/102264444?OzSrank=1
길고 따분한 글 읽어주셔서 감사합니다. 마지막으로 거듭 말씀드리지만, 종목을 추천하기 위해 쓴 글이 아니며, 투자는 절대 투자자 본인의 책임임을 명심해주시기 바랍니다.
이상입니다. 감사합니다. 모두 성투하시길~!
'데이터분석 > D-Tech 데이터로 하는 재테크' 카테고리의 다른 글
| 주가는 모멘텀을 타고 - 듀얼 모멘텀 투자 전략 (0) | 2021.01.05 |
|---|---|
| 파이썬 기본편 - 특정 종목의 주가 정보 가져오기 (0) | 2021.01.05 |
| 코스피는 하늘을 뚫고, 인버스를 생각해봐야 하는가? (0) | 2021.01.04 |
| EXCEL 기본편 - VLOOKUP (0) | 2021.01.04 |
| 무료 데이터 나눔 - DART 기준 전체 년도 3분기 손익계산서 (0) | 2020.12.04 |
| 손쉬운 투자 종목 찾기 - Feat. 트레이딩뷰와 베트남 (0) | 2020.12.01 |
| 위험천만 테마주와 알찬 팍스넷 (0) | 2020.11.24 |
| DART로 20년 3분기 영업이익 좋은 종목 찾기 (0) | 2020.11.17 |