앞에서는 DART의 재무 제표 데이터를 올렸는데요, 월별 주가를 올려서 월별 주가도 같이 분석한다면 좀 더 좋은 종목을 찾는데 도움이 되기 때문입니다.
본격적인 설명에 앞서, 절대 어떤 종목을 추천하기 위한 글이 아니며, 본 글로 인해 투자한 종목의 손실에 대해서는 절대 누구도 책임지지 않는다는 것을 기억해주시기 바랍니다.
데이터의 정확성 역시 일차적으로는 검토했지만, 데이터 수집 시점이나 과정에서 발생한 실수로 부정확할수도 있으니 양해바랍니다.
오늘 올린 월별 주가 데이터는 2019년 1월부터 2021년 6월까지입니다. (2년 6개월치)
주의할 점은 일별주가가 아닌, 월별주가라는 점입니다.
일별주가 데이터의 경우 데이터 양이 많아 공유하고 DB화하는 과정이 쉽지 않습니다.
데이 트레이딩이나 단기 트레이딩이 아니라면 월별 주가 데이터로도 충분히 투자 종목 분석에 유용하게 사용할 수 있습니다.
월별주가의 특징부터 간단히 설명드리면, 일별 주가 데이터를 월별로 집계한 것입니다. '평생 필요한 데이터 분석' 책에서는 월별주가에 대해 아래와 같이 설명을 하고 있습니다. 참고하기 바랍니다.
[그림-1]
여기서는 앞에서 생성한 DB_DTECH 데이터베이스에 HIST_YM이라는 신규 테이블을 만들어 월별 주가 데이터를 올릴 예정입니다. (평생 필요한 데이터 분석에서 월별주가 테이블은 HISTORY_YM입니다. 일부러 다른 테이블을 사용해 평생 필요한 데이터 분석의 연습 환경을 보존합니다.)
만약에 MySQL과 DB_DTECH라는 DB를 생성하지 않은 환경이라면 앞에 글을 참고해 환경을 먼저 만들어주세요.
본격적인 설명에 앞서, 절대 어떤 종목을 추천하기 위한 글이 아니며, 본 글로 인해 투자한 종목의 손실에 대해서는 절대 누구도 책임지지 않는다는 것을 기억해주시기 바랍니다.
데이터의 정확성 역시 일차적으로는 검토했지만, 데이터 수집 시점이나 과정에서 발생한 실수로 부정확할수도 있으니 양해바랍니다.
상따(상한가 따라잡기), 말 그대로 상한가 갈거 같은 종목을 매매하거나, 상한가인 종목을 따라가서 매매하는 방법입니다. (매매기법이라고 하는 것이 맞는지 잘 모르겠습니다.)
개인적으로 상따와 같은 매매 기법을 선호하지 않습니다. 상따뿐만 아니라 단타 매매 자체를 안하려고 노력하는 스타일입니다. 단타가 필패거나 나쁘다 생각하지는 않지만, 제 경우에는 단타가 잘 맞지 않더군요.
어쨌든, 상따는 과연 얼마나 성공할 수 있는지, 간단하게 확률을 구해봤습니다.
'상따' 매매도 다양한 방법이 있을거 같습니다. 시초가 상따를 따라간다던지, 오후장 상따를 노린다던지, 상따후 기다렸다 눌렸을때 매매한다든지, 상따를 만드는 과정을 고려해서 매매를 한다든지, 다양한 방법이 있겠죠.
안타깝게도 이와 같은 모든 방법을 구현해 상따의 승리(?) 확률을 구하려면 복잡해집니다. 그러므로 여기서는 아주 간단한 로직으로만 진행하겠습니다. 절대, 재미로만 봐주시기 바랍니다.
1. 종가가 15~20% 사이면 종가로 상따 매수
: 종가가 15~20% 사이일 때, 종가로 매수했다고 가정합니다.
: 결과를 보면, 1일후에 -5% 이하로 하락할 확률이 더 큽니다.
: 2% 이상 상승할 확률도 30%가 될까 말까 합니다.
2. 종가가 20~25% 사이면 종가로 상따 매수
: 종가가 20~25% 사이일 때, 종가로 매수했다고 가정합니다.
: 결과를 보면, 1일후에 -5% 이하로 하락할 확률이 위의 1번 케이스보다 더 높아졌습니다.
3. 종가가 25~28% 사이면 종가로 상따 매수
: 종가가 25~28% 사이면, 종가로 매수했다고 가정합니다.
: -5% 이하로 하락할 확률이 40%를 넘습니다.!!
지금까지 결과를 보면 상따한 경우, +5% 이상 상승할 경우보다 -5%이하로 하락할 확률이 더 높은 것을 알 수 있습니다. 특히나, 상한가에 근접하면 할 수록 다음날 -5%이하로 하락할 확률이 더 높아집니다.
(이점을 고려해, 상한가 나오고, 그 다음날 종가에 매수하면 또 어떻게 변할지 계산해볼 수도 있겠죠.)
결론은, 역시 상따는 안하는게 좋아보입니다.^^
위 데이터는 DB화된 일별 주가 데이터를 사용해 아래 SQL로 손쉽게 뽑아낸 결과입니다. 조건만 좀더 변경한다면 각자 원하는 방법으로 테스트 해볼 수 있습니다. 물론, 일별주가 데이터를 모아서 DB화 하는 과정이 순탄하지는 않습니다.
SELECT SUBSTR(T1.YMD,1,4) YY
,COUNT(*) CNT
,SUM(CASE WHEN T2.GRW_RT >= 2 THEN 1 END) 상승횟수
,ROUND(SUM(CASE WHEN T2.GRW_RT >= 2 THEN 1 END) / COUNT(*) * 100,2) 상승률
,SUM(CASE WHEN T2.GRW_RT <= -5 THEN 1 END) 급락횟수
,ROUND(SUM(CASE WHEN T2.GRW_RT <= -5 THEN 1 END) / COUNT(*) * 100,2) 급락률
,SUM(CASE WHEN T2.GRW_RT >= 5 THEN 1 END) 급등횟수
,ROUND(SUM(CASE WHEN T2.GRW_RT >= 5 THEN 1 END) / COUNT(*) * 100,2) 급등률
FROM ORA_STK_USER.FT_STK_YMD T1
,ORA_STK_USER.FT_STK_YMD T2
WHERE T1.YMD >= '2016'
AND T1.GRW_RT <= 28
AND T1.GRW_RT > 25
AND T2.STK_CD = T1.STK_CD
AND T2.STK_YMD_NO = T1.STK_YMD_NO + 1
GROUP BY SUBSTR(T1.YMD,1,4)
ORDER BY YY;
위와 같이 주식 데이터를 마음대로 분석해볼 수 있는 SQL을 공부하고 싶다면 아래 책을 참고해주세요~!
* 참고로 아래 책은, 주식 데이터를 모으는 방법은 설명하지 않습니다.
* 주식 데이터(일별주가 2019년 2020년 2년치)를 데이터베이스화 해놓고 바로 SQL을 실습하는 책입니다.
이처럼, 데이터를 분석하는 과정을 공부해보고 싶으신 분은 아래의 '평생 필요한 데이터 분석'의 교육 과정을 추천합니다. 교육을 통해 SQL을 배운다면, 위 내용을 좀 더 보강할 수도 있고, 자신만의 스타일로 분석을 할 수 있습니다. SQL을 완전히 자신의 것으로 만들 수 있는 교육이니 관심 가져보시기 바랍니다. 감사합니다.~!
업로드한 테이블인 'up_손익계산서_20211q'를 SELECT해보면서 어떤 값들이 있는지 눈여겨 보시기 바랍니다. 테이블에 어떤 데이터가 있는지 잘 알고 있어야, 자신만이 원하는 것을 분석할 수 있으니까요.
DART의 손익계산서 파일에서, 매출액의 항목코드는 ifrs-full_Revenue 입니다. 영업이익 항목코드는 이전 글에서 살펴본 것처럼 dart_OperatingIncomeLoss 로 파악됩니다.
먼저 영업이익률을 간단히 정의해보면 아래와 같습니다.
- 영업이익률 = 영업이익 / 매출액 * 100
어떻게 SQL을 작성하는지에 대한 자세한 설명은 생략하고 결과만 빠르게 살펴보겠습니다. SQL의 원리는 '평생 필요한 데이터 분석' 책을 통해 공부해주시기 바랍니다.
DART 포괄-손익계산서-연결 기준 2021년 1분기에 영업이익률 Top-10 종목을 찾아보면 아래와 같습니다. 여기서는 매출액이 500억 이상인 종목만 대상으로 했습니다. (일부 종목의 경우 USD로 보고된것이 있습니다. USD를 별도 KRW로 변경하지 않고 처리했습니다. 그러므로 USD로 수치가 보고된 데이터 일부는 누락되었을수 있습니다.)
WITH RES01 AS(
SELECT T1.종목코드 ,T1.회사명
,MAX(CASE WHEN T1.항목코드 = 'dart_OperatingIncomeLoss' THEN ROUND(T1.당기1분기3개월/1e8,1) END) 영업이익
,MAX(CASE WHEN T1.항목코드 = 'ifrs-full_Revenue' THEN ROUND(T1.당기1분기3개월/1e8,1) END) 매출액
FROM DB_DTECH.up_손익계산서_20211q T1
WHERE T1.항목코드 IN ('ifrs-full_Revenue','dart_OperatingIncomeLoss')
GROUP BY T1.종목코드 ,T1.회사명
)
SELECT T1.종목코드 ,T1.회사명
,T1.영업이익 ,T1.매출액
,ROUND((T1.영업이익 / T1.매출액) * 100,2) 영업이익률
FROM RES01 T1
WHERE T1.매출액 > 500
AND T1.영업이익 > 0
ORDER BY 영업이익률 DESC
LIMIT 10;
결과는 아래와 같습니다.
이번에는 DART 손익계산서에 포함된 업종명을 이용해, 업종별 영업이익률 Top-3 종목을 찾아보겠습니다. 아래와 같습니다. 마찬가지로 매출액 500억 이상인 종목만 대상으로 했습니다.
# SQL
WITH RES01 AS(
SELECT T1.종목코드 ,T1.회사명 ,T1.업종명
,MAX(CASE WHEN T1.항목코드 = 'dart_OperatingIncomeLoss' THEN ROUND(T1.당기1분기3개월/1e8,1) END) 영업이익
,MAX(CASE WHEN T1.항목코드 = 'ifrs-full_Revenue' THEN ROUND(T1.당기1분기3개월/1e8,1) END) 매출액
FROM DB_DTECH.up_손익계산서_20211q T1
WHERE T1.항목코드 IN ('ifrs-full_Revenue','dart_OperatingIncomeLoss')
GROUP BY T1.종목코드 ,T1.회사명 ,T1.업종명
)
SELECT T2.*
FROM (
SELECT T1.종목코드 ,T1.회사명 ,T1.업종명
,T1.영업이익 ,T1.매출액
,ROUND((T1.영업이익 / T1.매출액) * 100,2) 영업이익률
,RANK() OVER(PARTITION BY T1.업종명 ORDER BY ROUND((T1.영업이익 / T1.매출액) * 100,2) DESC) 업종별순위
FROM RES01 T1
WHERE T1.매출액 > 500
AND T1.영업이익 > 0
) T2
WHERE T2.업종별순위 <= 3;
위의 SQL의 조건만 조금 변경한다면, 매출액 기준이나 Top-N에 대한 기준도 각자 원하는대로 변경이 가능합니다.
위의 글을 통해, DART 손익계산서를 데이터베이스화했다고 가정하고 설명하도록 하겠습니다.
여기서는 앞에서 업로드용으로 사용한 up_손익계산서_20211q 테이블을 사용해 바로 데이터 분석을 진행합니다. 앞으로 좀 더 통합적이고 체계적인 분석을 위해서는 이와 같은 업로드용 테이블이 아닌, 실제 분석용 테이블로 데이터를 한 번 더 정제해서 사용하는 것이 좋습니다. 이러한 내용은 나중에 기회가 되면 다루도록 하고, 일단은 업로드용 테이블을 바로 사용해 분석을 합니다.
먼저 아래와 같은 SELECT SQL로 테이블을 간단히 조회해봅니다.
SELECT T1.*
FROM DB_DTECH.up_손익계산서_20211q T1;
결과를 확인해 보면, 굉자히 많은 데이터가 있습니다. 이중에서 오늘 사용할 것은 영업이익 데이터입니다.
테이블을 보면, 항목코드와 항목명이 있는데, '영업이익' 데이터를 제대로 고르기 위해서는 항목코드를 사용해야 합니다. 그러기 위해서는 영업이익의 항목코드가 무엇인지 먼저 찾아내야 합니다. 아래와 같은 SQL로 영업이익 항목코드를 유추해볼 수 있습니다.
SELECT T1.항목코드
,COUNT(*) CNT
FROM DB_DTECH.up_손익계산서_20211q T1
WHERE T1.항목명 LIKE '%영업이익%'
GROUP BY T1.항목코드
ORDER BY CNT DESC;
[결과]
항목코드 CNT
================================================= ======
dart_OperatingIncomeLoss 1537
ifrs-full_ProfitLossFromContinuingOperations 240
ifrs-full_ProfitLossFromDiscontinuedOperations 170
ifrs-full_ProfitLossBeforeTax 7
ifrs-full_DilutedEarningsLossPerShare 3
항목명에 영업이익이 포함된 데이터를 항목코드별로 카운트를 해서 어떤 항목코드가 가장 많은지 찾아봤습니다. 아마도 dart_OperatingIncomeLoss가 영업이익일거 같습니다.
아래와 같은 SQL로 엔씨소프트의 2021년 1분기 영업이익을 확인해봅니다.
SELECT T1.회사명
,T1.항목명
,T1.당기1분기3개월
,T1.전기1분기3개월
FROM DB_DTECH.up_손익계산서_20211q T1
WHERE T1.항목코드 = 'dart_OperatingIncomeLoss'
AND T1.회사명 = '엔씨소프트';
[결과]
회사명 항목명 당기1분기3개월 전기1분기3개월
================= ====================== ====================== ======================
엔씨소프트 영업이익(손실) 56732864604.00 241424012436.00
결과는 보다시피 SK, SK이노베이션, S-Oil의 증가액이 가장 큰 것을 알 수 있습니다. 여기서, 주의할 것은 삼성전자나 현대차와 같은 경우는 저희가 사용한 DART 파일에 데이터가 존재하지 않습니다. DART에서 다운 받은 파일 중 다른 파일에 존재할 것입니다.
그리고, 결과를 보면 SK의 항목명은 '영업이익(손실)'이라고 나오고 현대중공업지주는 항목명이 '영업이익'입니다. DART에서 받은 파일 자체에 항목코드는 같지만 항목명은 다르게 들어가 있는 경우입니다. 그렇기 때문에 우리는 항목코드를 사용해야 하고, 이와 같은 항목명은 나중에 클린징이 필요합니다.(우선은 그냥 사용합니다.)
위와 같이 영업증가액을 사용하면, 영업이익 금액 자체가 높은 우량 기업 위주로 조회가 됩니다. 이 방법도 좋지만, 영업이익 상승률을 확인해볼 필요도 있습니다.
아래와 같은 SQL로 2020년1분기에 영업이익이 100억 이상이면서 영업이익 상승률이 가장 높은 종목 Top-10을 찾아낼 수 있습니다.
오늘은 DART의 2021년 1분기 재무제표 데이터를 다운로드해서 데이터베이스화하는 작업을 진행하겠습니다.
이후에는 데이터베이스화된 재무제표 데이터를 이용해 다양한 분석도 시도해볼 예정입니다.
1. DART 접속 및 사업보고서 확보
2021년 1분기보고서의 손익계산서를 다운로드하자. 2021년 1분기보고서에는 2021년 1월 1일부터 2021년 3월 31일까지의 재무 현황이 담겨 있다. 안타깝게도 해당 보고서는 1분기가 끝난 한참 후에나 DART에서 확인할 수 있다. DART의 접속 URL은 아래와 같다.
DART의 접속해 아래와 같은 과정으로 2021년 1분기보고서의 손익계산서를 다운로드한다. [그림 1-1]을 참고한다.
1. DART 메뉴 상단의 '공시정보활용'마당 메뉴에 접속
2. '공시정보활용마당'의 좌측 메뉴에서 '재무정보 일괄다운로드'를 선택
3. 2021년을 선택한다.
4. 2021년 데이터 중에, '1분기보고서'의 '손익계산서'를 다운로드 한다.
[그림 1-1]
다운로드한 파일(2020_4Q_PL_20210422040118)의 압축을 풀어보면 아래와 같은 파일이 들어있다. (2021년 6월 18일 기준이다.)
- 2021_1분기보고서_02_손익계산서_20210618.txt
- 2021_1분기보고서_02_손익계산서_연결_20210618.txt
- 2021_1분기보고서_03_포괄손익계산서_20210618.txt
- 2021_1분기보고서_03_포괄손익계산서_연결_20210618.txt
위 파일들 중에, 2021_1분기보고서_03_포괄손익계산서_연결_20210618'만 데이터베이스에 올려볼 예정이다.
2. UPLOAD할 파일을 UTF-8로 변환하기
현재 DART에서 받은 파일은 ANSI 형태로 저장되어 있다. ANSI 형태 파일을 MySQL로 업로드하면 한글이 깨지는 문제가 있다. (환경에 따라 문제없을 수도 있다.) ANSI 형태의 파일을 UTF-8로 변환해 저장해야 한다. 여기서는 손익계산서 파일을 윈도우의 메모장(notepad)으로 열어서 인코딩을 UTF-8로 변환해 저장한다.
'2021_1분기보고서_03_포괄손익계산서_연결_20210618' 파일을 메모장으로 열어서 UTF-8로 변환해 저장한다. 저장할 때 업로드 하기 편하도록 파일명도 변경한다. ([그림 2-1]을 같이 참고한다.)
- 2021_1분기보고서_03_포괄손익계산서_연결_20210618.txt
1. 'UP_손익계산서_20111'로 파일명을 변경한다.
2. 인코딩을 UTF-8로 변경한다.
3. 확인을 눌러 저장한다.
[그림 2-1]
3. SQL Tool(DBeaver) 다운로드 및 접속하기
확보한 데이터 파일을 데이터베이스로 올리기 위해서는 DBMS와 SQL Tool이 필요하다. DBMS는 MySQL을 사용하고 SQL Tool은 DBeaver를 사용한다.
먼저 자신의 PC에 MySQL을 설치해야 한다. MySQL 설치는 아래 글을 참고하기 바란다.
MySQL에 데이터베이스와 테이블을 생성하려면 SQL 툴을 사용해 명령을 내려야 한다. 이를 위해 DBeaver를 설치하도록 하자. DBeaver를 사용하는 이유는, 엑셀이나 CSV(콤마로 구분된 문서 파일) 파일을 데이터베이스로 올릴 때 유용하기 때문이다. 또한 DBeaver는 무료 버전이 있으며 다향한 DBMS에 접속하는 것도 가능하다. DBeaver는 아래 사이트에서 다운로드할 수 있다.
DBeaver를 설치했다면 DBeaver로 MySQL에 접속하도록 하자. DBeaver를 설치했다면 DBeaver로 MySQL에 접속하도록 하자. DBeaver에 접속한 후에, SQL을 실행하기 위해서는 F3을 눌러 SQL Script 창을 열면 된다.
4. UPLOAD용 테이블 만들기
데이터를 업로드 할 때는, 업로드 전용 테이블을 만들어서 처리하는 것이 좋다. 실제 시스템에서 사용하는 테이블에 직접 업로드를 하면, 시스템 장애의 위험성도 있으며, 클린징되지 않은 데이터가 입력되어서 혼선을 일으킬 수 있다. 실제 운영 시스템에서 데이터 업로드를 해야 한다면 반드시 아래와 같은 과정으로 진행하기 바란다.
1. 업로드용 임시 테이블 생성
2. 파일을 업로드용 임시 테이블로 업로드 처리
3. 업로드용 임시 테이블을 이용해, 잘 못된 데이터가 있는지 검토
4. 검토가 완료되어 문제가 없다면, 임시 테이블의 데이터를 실제 테이블로 INSERT 처리한다.
테이블을 만들기 위해서는, 테이블을 생성할 데이터베이스를 먼저 생성해야 한다. DB_DTECH라는 데이터베이스를 생성한다. 데이터베이스를 만들기 위해서는 아래와 같이 CREATE DATABASE SQL을 사용하면 된다. DBeaver로 MySQL에 접속해 아래 SQL을 실행하면 된다.
CREATE DATABASE DB_DTECH;
SQL과 데이터 및 주식 분석에 대해 공부할 수 있는 '평생 필요한 데이터 분석' 책에서는 DB_SQLSTK 데이터베이스를 사용한다. DB_SQLSTK 데이터베이스를 이미 생성했다면 DB_SQLSTK 데이터베이스를 사용해도 된다. 하지만 DB_SQLSTK은 책의 실습을 위해 그대로 남겨두고 별도의 데이터베이스(DB_DTECH)를 만드는 것을 권장한다.
DB_DTECH 데이터베이스를 생성했으면 업로드용 임시 테이블을 만들 차례다. 업로드용 임시 테이블을 만들기 위해서는 다운로드한 파일을 열어서 어떤 컬럼들이 필요한지 살펴보아야 한다. '2021_1분기보고서_03_포괄손익계산서_연결_20210618' 파일을 엑셀로 열어 보자. 엑셀에서 첫 번째 로우의 필드명을 업로드용 임시 테이블의 컬럼으로 사용하면 된다. 결과적으로 아래의 SQL을 이용해 UP_손익계산서_2011Q 테이블을 생성하면 된다.
이제 드디어 업로드 할 차례다. DBeaver의 DATA IMPORT(데이터 가져오기) 기능을 사용해 앞에서 만든 임시 테이블(UP_손익계산서_20211Q)에 'UP_손익계산서_20111'로 저장한 파일의 데이터를 가져올 예정이다.
[그림 5-1]과 같이 DBeaver의 좌측에 있는 Database Navigator에서 DB_DTECH의 Tables 폴더를 확장해보자. 새로 만든 UP_손익계산서_20211Q 테이블이 보일 것이다. 만약에 보이지 않는다면, DBeaver를 다시 접속하거나, Navigator 창에서 F5 키를 눌러 새로고침 해보기 바란다.
UP_손익계산서_20211Q 테이블에 마우스 우클릭을 하게 되면 팝업 메뉴가 나온다. 팝업 메뉴 중에 데이터 가져오기(DATA IMPORT)를 선택한다.
[그림 5-1]
[그림 5-2]와 같은 Data Transfer 창이 나오면, 'CSV에서 가져오기'를 선택하고 '다음' 버튼을 눌러 넘어가도록 한다.
[그림 5-2]
[그림 5-3]과 같이 테이블에 업로드할 파일을 선택하는 창이 나오면 앞에서 UTF로 저장한 'UP_손익계산서_20111'파일을 선택하도록 한다. 이때, DART에서 받은 파일의 확장자가 TXT로 되어 있어, 확장자를 CSV로 변경하지 않는다면 파일이 보이지 않는다. 그림과 같이 확장자를 TXT로 변경한 후에 UP_손익계산서_20111 파일을 선택하다.
[그림 5-3]
아래 [그림 5-4]와 같이 'Input File(s)' 창에서, 컬럼 구분자(Column Delimiter)에 '\t'(Tab)를 입력한다. DART에서 받은 파일이 컬럼(필드)을 탭(Tab)으로 구분하기 때문이다. 만약에 자신이 가지고 있는 파일의 컬럼 구분자가 콤마(,)라면 콤마를 입력해주면 된다. 컬럼 구분자를 탭으로 설정한 후에 다음을 누르도록 하자.
[그림 5-4]
[그림 5-5]와 같은 Tables Mapping 화면은 Source와 Target간에 컬럼을 매핑하는 화면이다. Source 테이블 쪽에 '>' 표시를 클릭해보면 컬럼간 매핑을 확인할 수 있다.
[그림 5-5]
[그림 5-5]에서 Source 쪽의 '>' 부분을 클릭한 후에 스크롤을 내려보면, 아래 [그림 5-6]의 좌측과 같이 일부 컬럼(당기 1분기 3개월~전기 1분기 누적까지)의 Mapping이 create로 되어 있는 것을 확인할 수 있다. DBeaver는 기본적으로 Source 파일의 필드명과 Target 테이블의 컬럼명을 비교해 같은 필드(컬럼)간에 매핑을 처리한다. 앞에서 테이블을 생성할 때, 파일의 "당기 1분기 3개월" 필드명은 "당기1분기3개월"과 같이 빈 칸(Space)을 제거해 테이블의 컬럼으로 만들어 놓은 상태다.
해당 매핑 화면에서 Mapping이 create로 된 항목은, Target쪽의 항목을 하나씩 클릭해 그림의 오른쪽과 같이 create가 existing이 되도록 테이블에 이미 있는 컬럼을 선택하기 바란다.
[그림 5-6]
여기까지 완료한 후, 나머지 화면에서는 '다음'과 'Start'를 선택해 업로드를 실행하면 된다.
오늘은 2차 전지 ETF를 살짝 살펴볼까 합니다. 원래는 21년 1월 13일 기준으로 정리해났던 내용인데 오늘에나 올리게 되었네요.
요즘 증시가 너무 불타오르고 있습니다. 그 중심에는 삼성전자, 현대차, LG전자와 같은 우량주들이 있지만, 2차 전지 관련주 역시 한 몫을 톡톡히 해내고 있습니다.
제 주변의 형님 한 분이 요즘 주가가 너무 올라서.. 이제 슬 빼서 2차 전지 ETF나 좀 들어가 볼까 하시길래... ETF를 한 번 살펴봤습니다. 제 개인적인 생각은 좀 늦은감이 있다 생각이 들었지만... 그건 누구도 알 수 없으니까요.
설명에 앞서, 이 글은 절대 어떤 종목을 추천하거나 투자를 권유하기 위한 글이 아닙니다. 투자에 참고용으로 사용하기 바라며, 데이터의 정확성 또한 보장하지 않습니다.(제가 1차적으로 확인은 하지만 확인 시점과 절차에 따라 오류가 있을 수 있습니다. 투자를 하신다면 반드시 최종 데이터는 본인이 확인해주시기 바랍니다.) 투자는 투자자 본인의 책임임을 항상 명심해주세요.
네이버 증권에 들어가서 2차 전지 관련 ETF를 조회해보면 아래와 같이 세 개가 나옵니다. (TIGER 2차전지 테마, KODEX 2차전지산업, TIGER KRX2차전지K-뉴딜)
종목 하나를 찍고 들어가서 'ETF분석' 탭을 클릭하면 해당 ETF에 자세한 정보를 볼 수 있습니다. 제가 관심을 갖고 있는건 2차 전지 ETF에 포함된 종목들입니다. 아래는 'TIGER 2차전지테마'의 예입니다.
CU당 구성종목을 보면 SK이노베이션, 삼성SDI, 포스코케미칼 등이 10% 이상으로 구성되어 있습니다. 여기서 'CU당'의 CU가 의미하는 바는 정확히 모르겠습니다. 어쨋든, ETF를 구성하는 종목의 비율임은 분명해 보입니다.
위와 같은 방법으로 세 개 ETF의 구성 종목을 모두 모아봅니다.
그리고, 국내 ETF 뿐만이 아니라 외국의 ETF도 좀 들여다 볼 생각입니다. 배터리 관련해서 유명한 외국의 ETF는 아래의 두 개가 있습니다.
- Amplify Lithium & Battery Techn (BATT)
- Global X Lithium & Battery Tech (LIT)
외국 ETF의 구성 종목은 야후 파이낸스에서 확인할 수 있습니다. 모든 종목이 나오지는 않고 Top-10 종목만 나옵니다. Top-10이 많은 포지션을 차지하기 때문에 Top-10 종목만 알아도 될거 같습니다.
위 URL에 들아거서 Holdings 탭을 선택한 후에 아래로 내리다 보면 해당 ETF를 구성하는 Top-10 종목을 알 수 있습니다.
BATT ETF에는 우리나라의 LG화학과 삼성SDI가 자랑스럽게 포함되어 있는 것을 알 수 있습니다. 이 외에도 배터리 업체가 아닌 테슬라, BYD와 같은 전기 자동차 종목이 포함되어 있는 것도 눈에 뜁니다.
우리 나라 ETF 3개와 미국 ETF 2개에 포함되어 있는 종목들을 정리해 보니 아래와 같습니다. 네이버와 DART의 재무 정보를 이용해 당기 3분기(2020년), 전기 3분기(2019년)의 영업이익도 추가를 했습니다.
정리된 내용을 토대로 눈길이 가는 점을 정리해보면 아래와 같습니다.
- 국내 ETF에서 가장 많이 담고 있는 종목은 SK이노베이션이다.
: 반면에 외국 ETF에서는 담고 있지 않다.
- 모두가 잘 아는 SK이노베이션, LG화학, 삼성SDI, 포스코케미칼이 국내 ETF내 많은 비중을 차지한다.
: 곧 네 개 종목의 주가가 ETF의 큰 방향을 결정할거 같습니다.
- KODEX 2차전지 산업 ETF는 '에코프로'를 많은 비중을 담고 있다.
: 자회사인 에코프로비엠까지 고려하면 제법 많이 담고 있다 생각됩니다.
- 영업이익이 10억도 되지 않는 종목들이 담겨져 있다.
: 물론, 이는 중요하지 않을수도 있습니다. 개인적으로 이익이 나오는 회사를 선호하기 때문에 눈에 뜁니다.
- 국내 ETF는 철저하게 배터리를 만드는 회사와 괸련 있는 종목만 있는거 같습니다.
: 외국 ETF는 테슬라나 BYD와 같은 전기차 회사도 담고 있기 때문에 눈에 뜁니다.
: 개인적으로 2차 전지 ETF가 국내 종목 뿐 아니라 외국 종목도 같이 담은 ETF면 재밌겠다 생각이 듭니다.
ETF를 들어가는것이 가장 편하고 안정적인 투자가 될 수 있겠지만.. 좀 더 공격적인 투자라면 위 ETF를 벤치마킹해서 자신만의 포트폴리오를 구성해보는 것도 좋을거 같습니다. 하지만.. 개인적으로 지금은 주가가 너무 높다 생각합니다.
마지막으로 개인적으로 이런 생각을 해봅니다. 2차 전지 관련주는 단기간에 급등을 했고, 앞으로 2차 전지가 많이 필요할 것은 너무나 자명해 보입니다. 그렇다면 2차 전지에 들어갈 주요 소재는 무엇이고 주요 소재의 가격은 지금 어느 위치에 있을까요.. 이 부분도 한 번 생각해볼 필요가 있을거 같습니다.
오늘은 건설주를 살짝 들여다볼까 합니다. 어쩌다 보니 대우건설을 사게 되었는데 운좋게도 빠르게 수익이 나는 덕분에... 혹시 2021년은 건설붐인가 라는 생각이 들었습니다.
뉴스를 봐도 부동산 공급 물량이 부족하다고 합니다. 이는 앞으로 '부동산 공급을 많이 할거야'란 의미로 해석할 수도 있을거 같습니다. 그리고 부동산 물량 증가가 곧 건설주의 상승이 아닐까 생각이 듭니다. 이렇게 개인적으로 그냥 생각하고 '건설주를 사자'라고 결정할 수도 있겠지만... 투자를 위해서는 좀 더 데이터를 들여다 볼 필요가 있습니다.
설명에 앞서, 절대 어떤주를 추천하거나 어떤 종목이 어떻게 될거야라는 것을 주장하려는 것은 아닙니다. 데이터를 살펴보고 각자 생각을 해보심이 어떨까해서 올린 자료입니다.
먼저 팍스넷에서 건설 관련주를 추출해봤습니다. 상승테마 메뉴에서 '건설 대표주'를 확인해 보니 아래와 같이 여섯개의 종목이 나옵니다. 아래 부분의 '테마 히스토리'를 통해서는 건설 대표주 관련 주요 뉴스들을 확인할수도 있습니다. 좋네요~ (물론, 전 팍스넷과 아무 관련이 없습니다.)
위 건설 대표주에서 시가 총액 순으로 다 섯개 종목만 골라내면 삼성물산, 현대건설, GS건설, 대림산업, 대우건설이 나옵니다. 다섯 개 종목에 대해 '네이버 증권' 정보를 이용해 정리해보면 아래와 같습니다.
다섯 개 정도는 네이버 증권 정보에서 검색해보는건 전혀 어렵지 않죠. 네이버에서 사용한 정보는 19.3Q, 20.2Q, 20.3Q의 영업이익과 목표가입니다. 목표가는 다양한 증권사가 제시하는데 그중에 마지막으로 올라온 한 건만 사용했습니다. (이 부분도 여러 증권사의 평균을 사용하거나, 가장 큰 목표가를 제시한 증권사꺼를 사용하든가하는 다양한 방법이 있습니다. 여기서는 그냥 보이는 마지막꺼만 넣어봤습니다.)
데이터를 쑥 보니.. 시가 총액으로 보면 삼성물산이 압도적입니다. DART에 들어가서 삼성물산의 산업의 구성을 자세하게 들여다 보니, 건설, 상사, 패션, 리조트, 급식, 거기에 바이오까지 정말 다양한 포트폴리오를 가지고 있네요. 삼성물산은 너무 많은 업종을 하고 있으니 건너뛰어야 할거 같습니다.
현대건설의 최근 분기보고서(2020.3Q)의 '사업의 내용' 부분을 보면 아래와 같은 표를 볼 수 있습니다. 건축/주택 부분에서 많은 매출이 발생하는 것을 볼 수 있습니다.
GS건설도 분기보고서의 사업 내용을 살펴보면 아래와 같습니다.
GS건설 역시 건축/주택이 매출의 많은 부분을 차지하고 있습니다. 이는 집을 많이 지으면 건설주의 매출이 늘어나고 이는 주가의 상승으로 이어진다고 합리적으로 생각해 볼 수 있게 합니다.
그래서..'국토교통 통계누리'에 들어가서 주택 인허가 데이터를 살펴보기로 했습니다.(너무 집요한가요... 투자를 하려면 이처럼 집요해야하지 않을까요.)
2015년도에 가장 많이 인허가를 해주고.. 점점 줄어드는 것으로 보이네요... 참고로.. 2020년은 11월까지만 집계된 데이터입니다. 아직 12월 인허가 수는 올라오지 않았더군요...
흠... 아직까지 주택 공급을 늘리기 위해 인허가를 해주고 있는거 같진 않습니다. 내년에 많이 인허가 해준다는 건가요....
정책적인거나 정치적인건 제가 잘 모르겠고.. 어쨋든... 위 데이터로는 인허가는 줄어들고 있는 추세로 보입니다
주택 인허가수와 건설주 합쳐서 차트로 그려보면 어떨까요? 주택 인허가가 주가와 관련이 있을까?... 합리적으로 의심해 볼수 있으니까요. 아래와 같습니다.
주택 인허가수와 함께 현대건설, GS건설 두 종목만 주가를 같이 표시해봤습니다. 2015년에 가장 많이 인허가를 해줬는데. 그 부분이 주가를 끌어 올린 흔적이 보이거나 하는건 같진 않습니다.
금융위기 이전, 북경 올림픽 이전인 2008년에 건설주는 최고가를 찍고 계속 하향 추세였습니다. 코로나 저점을 찍고 반등을 해서 상승 방향으로 방향을 돌려 놓은거 같긴 하지만... 다른 종목군에 비하면 얼마나 좋을지는 짐작이 잘 되지 않습니다. 뚜렷하게 오를거야라고 생각되지는 않네요.
현대건설하고 삼성전자 주가를 최대한 그려서 비교해보면 아래와 같습니다. 현대건설을 건설 대표주로 가정하고. 상승구간을 살펴보면, 2007년 상승 구간은 중국 특수가 아니었을까 생각합니다. 2008년 북경 올림픽 전만해도 중국과의 사이도 좋았으니까요.(제 개인적인 생각입니다. 정확한건 해당 시점에 매출액 구성을 봐야 알 수 있겠죠.) 그리고 해당 구간은 2008년 금융위기 전이라 증시도 활황이었던거 같습니다. 2018년초에 잠깐 솟아 오른 경우는.. 남북경협으로 떠들썩 했던 구간인거 같습니다.
지금까지 쭉 살펴봤을때.. 결론은 삼성전자...네요..ㅜㅜ
건설주가 2021년에 어떻게 될지는 각자 생각해보셨으면 합니다. 그리고... 최근에 증시에 유행하는 말처럼... 이와 같이 아무리 분석해봤자... 'GS자이가 아파트가 이뿌네'라고 생각하고 GS건설을 사는 것이 수익률이 더 좋을수도 있습니다.ㅠㅠ
읽어주셔서 감사합니다.~!
이와 같은 분석은 데이터베이스를 구축해 SQL을 활용한다면 훨씬 더 쉽고 강력하게 처리할 수 있습니다.
SQL과 주식에 관심이 있다면 아래 책을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
2021년 연초부터 증시가 불을 뿜네요. 이와 같은 우량주 대상승을 본 적이 없어 후들후들합니다.
주변에서, 증시가 활황이면 증권주를 사라는 말을 들어보셨을 겁니다. 그래서 오늘은 증권주를 살짝 들여다보겠습니다.
종목을 분석하는 다양한 방법이 있지만, 여기서는 간단하게 팍스넷과 엑셀만 사용합니다.(저는 팍스넷과 전혀관련 없습니다.)
설명에 앞서, 이 글은 투자를 권유하거나 특정 종목을 추천하려는 글이 아닙니다. 데이터를 이와 같이 분석할 수 있음을 알려드리고 싶은 글입니다. 또한 이 글에 나온 종목에 투자해 발생한 손해는 투자자 본인의 책임임을 명심하시기 바랍니다.
팍스넷에 접속해 '종목입체분석 > 상승테마' 메뉴에 들어가 '증권' 테마를 찾은 후 아래와 같은 설정으로 데이터를 조회합니다.
- 현잭, 등락률, 시가총액(억), PER(배), 매출액증가율, 영업이익률 선택
- 선택 조건 검색 클릭
- 기간누적을 120일로 선택
팍스넷에 접속해 증권주를 검색한 결과를 카피해서 엑셀로 정리해보면 아래와 분석을 할 수 있었습니다. (팍스넷 데이터이기 때문에, 엑셀로 가공된 자료를 올리는 건 좀 문제가 있을까 싶어, 정리된 그림으로만 올렸습니다.)
팍스넷에서 증권 테마로 조회되는 종목은 위와 같이 22개가 조회됩니다. 위와 같은 데이터를 무료로 조회할 수 있고... 팍스넷에 정말 감사합니다.
보기 좋게, 각 항목별로 Top-3 종목에는 노란색으로 채워 났습니다.
예를 들어, 한국금융지주의 경우 시가 총액도 Top-3에 들어가고 영업이익률까지 Top-3에 들어갑니다. 다만, 기간등락률
(120일) 역시 Top-3에 들어감므로 이미 많이 올랐다고 생각할 수도 있습니다. (그러나 오른 종목은 계속 오른나는 말도 있죠.) 한국금융지주의 경우 개인과 기관의 120 일간 매수세도 Top-3에 들어갑니다. 그만큼 외인은 많이 팔았지만.. 요즘 대세는 외인보다는 개인이니까요.. 이 부분도 고민해볼 필요가 있겠네요.
참고로 제가 가지고 있는 증권주는 NH투자증권인데... 위 데이터로는 형편없네요. ㅋㅋㅋㅋ
위의 데이터만 보고, 어떤 종목을 사야겠다 결정할 수는 없겠죠. 어쨌든... 증권주 투자에 참고는 해볼 만하다고 생각합니다.
요즘 '듀얼 모멘텀 투자 전략'이라는 책을 틈틈히 보고 있습니다. 게리 안토나치 저에, 서태준님, 강환국님이 번역한 책입니다. 부지런하지 못해서 아직도 초반부 읽는 중인데, 저에게는 재밌고 흥미진진합니다.
책에는 간단히 모멘텀을 설명하면 '한 방향으로 움직이기 시작하면 계속 그 방향으로 나아가려는 주식 시장의 움직임'이라고 되어 있습니다.
말 그대로 오르는 주식은 계속 오르고, 내리는 주식은 계속 내린다는 간단하면서도 일리있는 내용입니다. 곰곰히 생각할수록 일리 있는데도, 따르기 쉬운 방법은 아닙니다.
책은 아직 다 읽지 못했지만. '모멘텀'이라는게 과연 우리나라 시장에서 얼마나 효과적일까..란 고민으로 데이터를 좀 두둘겨 봤습니다. 복잡하게 생각하지 않고. 간단하게 모멘텀 관련 ETF를 보면 되지 않을까란 생각으로 접근했습니다.
설명에 앞서. 절대 어떤 이론이 맞다 틀리다를 논하려고 하는 것이 아닙니다. 그리고 어떤 ETF를 추천하거나 문제가 있다라고 말하려는 것도 아닙니다. 모멘텀 관련 ETF에 이런것들이 있고, 흐름은 이렇다라는 현상만 설명하고 싶을 뿐입니다. 절대 어떤 투자를 권유하는 글이 아님을 명심해주시기 바랍니다.
먼저 네이버에 접근해서 금융 정보에서 '모멘텀'이라고 검색어를 입력합니다. 아래와 같은 모멘텀 관련 ETF들을 검색할 수 있습니다. 명칭에 '모멘텀'이 있다고 무조건 모멘텀 전략 ETF는 아닐 수도 있습니다. 아마도.. 모멘텀 관련일것이라는 합리적인(?) 의심으로 접근했습니다. 혹시라도 모멘텀 전략 ETF가 아닌걸로 아는 부분이 있으면 알려주세요.
위 ETF 종목 중에 하나를 찍고 들어가면 ETF에 구성된 종목이 무엇인지도 알 수 있습니다. 투자 아이디어에 도움이 되리라 생각합니다. TIGER 모멘텀을 확인해 보니, LG전자와 종근당, 삼성전자 등을 담고 있습니다.
네이버 증권 정보에서 모멘텀 관련 9개의 ETF를 검색할 수 있었습니다.이 ETF들이 코로나 저점 대비해서 얼마나 올랐는지와, 코스피 대표 종목으로 구성된 코덱스200 ETF랑 비교를 해보려고 합니다. 모멘텀 ETF들이 코스피 대표 종목들을 상화하는지 알고 싶기 때문입니다.
안타깝게도.. 결과는 아래와 같이 코덱스200과 큰 차이가 없습니다.
코로나 저점 대비 코덱스 200이 102%올랐는데, 모멘텀 ETF들도 거의 비슷한 수준입니다.
코로나 이후로 한정해 비교해서 별 차이가 없나 생각이 들기도 합니다. 그렇다면 더 과거의 데이터도 살펴봐야 할거 같습니다.
'코덱스 MSCI모멘텀'과 코덱스200을 비교해봤습니다. 아래와 같습니다.
MSCI 모멘텀이 파란색이고, 수치의 축은 왼쪽입니다. 코덱스200은 빨간색에 수치 축은 오른쪽입니다. 움직임을 비교해보면 거의 비슷합니다. 오히려 코덱스 200이 좀 더 좋아 보입니다. 아마도, 우리나라 환경이 대형주, 우량주 몇개가 전체 지수를 움직이는 경우가 크기 때문이 아닐까 생각해 봅니다.
그래서.. 중소형주 관련 모멘텀 ETF인것 같은.. 'KBSTAR 중소형 모멘텀밸류'하고 코덱스200도 비교해 봤습니다. 아래와 같습니다.
마찬가지로 중소형모멘텀도 코덱스200보다 월등히 하는 모습은 보이지 않습니다. 오히려 코덱스200보다 좋지 않아 보입니다.
이러한 차트로 얻은 결론은... 모멘텀 ETF가 뭔가.. 확실한 우위를 보여주는건 아직 없구나... 느낌입니다.
그렇다고 모멘텀 전략이 잘못되었다고 생각하지는 않습니다. ETF 운영에 문제가 있다고 생각하지도 않습니다.
각자 다양한 입장과 상황이 있겠죠. 개인적으로도 모멘텀을 지지하고, 모멘텀에 대해서는 연구를 더 해볼 생각입니다.
위와 같은 분석을 하기 위해서 파이썬을 이용해 주가 데이터를 가져오는 과정과, 엑셀의 VLOOKUP 작업을 했습니다.
저는 데이터 분석을 위해 SQL을 반드시 배우라고 떠들고 다닙니다. 이와 함께 미는 언어가 하나 더 있습니다. 바로 파이썬입니다.
파이썬은 데이터를 분석하고 다루기 위해 연구하고 공부할 매력있는 언어입니다.
파이썬을 처음 접한게 2016년 말이었습니다. 주식 데이터를 어떻게 분석할까 헤매이다 접하게 되었습니다. 그때 든 생각은.. '아.. 파이썬은 개발자는 물론 비 개발자 직군에도 필요한 언어가 되겠구나'였습니다. 아니나 다를까 현재 일부 회사의 경우 비 개발자도 파이썬을 배우게 하고 시험도 보는 경우가 있습니다. 저 역시 좀 더 파이썬 공부에 열을 올려볼까 생각하고 있습니다.
주식을 분석하기 위해서 필요한 기본적인 정보 두가지는 재무 정보와 주가(가격) 정보입니다.
오늘은 일별 주가 정보를 파이썬을 이용해 얻는 방법을 소개합니다.
일별 주가 정보를 얻는 다양한 방법이 있지만, pandas_datareader를 사용합니다. 그리고 얻은 주가 정보를 엑셀 파일로 저장하는 기능까지 구현합니다.
제가 개발한 환경은 아래와 같습니다. 반드시 아래와 같은 환경이 아니어도 됩니다.
- Anaconda 3.1
- Pycharm
아나콘다는 파이썬에 많이 쓰는 모듈을 모아 놓은 파이썬 버젼이라 생각하시면 됩니다. 그리고 파이참은 개발을 편하게 해주는 개발툴입니다. 반드시 이와 같이 환경을 구성할 필요는 없습니다. 그냥 파이썬만 다운받아 하셔도 됩니다.
여기서는 파이썬 프로그램 파일명을 "SIMPLE_STOCK_HISTORY.py"로 했습니다. 간단한 주가 이력 가져오기 정도의 의미입니다. 먼저 아래와 같이 필요한 모듈을 Import합니다.
import datetime
import time
from pandas_datareader import data as pdr
필요한 모듈을 Import 한 후에, 아래와 같이 getHistory라는 특정 종목의 주가 정보를 오늘자까지 가져오는 함수를 구현합니다.
def getHistory(_code,_excelSaveYN):
start = datetime.datetime(2000,1,1)
end = datetime.datetime(int(time.strftime("%Y")),int(time.strftime("%m")),int(time.strftime("%d")))
print(start, end)
history =pdr.get_data_yahoo(_code,start)
print(history)
if _excelSaveYN == 'Y':
saveExcel(_code,history)
오늘은 DART의 3분기 손익계산서(2016~2020)를 하나의 엑셀로 정리한 파일을 무료 나눔하려 합니다. 아무래도 DART에서 일괄다운로드한 엑셀에는 항목이 너무 많고 년도별로 다 쪼개져 있어서 분석하기 어려운 점이 있습니다.
DART(dart.fss.or.kr/) 에서 2016부터 2020까지 3분기 손익계산서만 사용했습니다. DART는 개인 투자자들에게 반드시 꼭 항상 옆에 두어야 하는 사이트인거 같습니다. (언제나 종목 매매전에 DART 들어가서 사업보고서 챙겨보는 습관이 필요합니다.!) 개인적으로 2016년 이전의 재무제표 데이터도 모두 얻어서 정리해서 공유하고 싶지만 쉽지 않네요.
제가 알기에는 DART 데이터만이 무료로 공유가 가능한거 같습니다.
개인 투자자분들의 분석에 용이하도록 다음과 같이 정리했습니다.
- 매출액, 영업이익, 당기순이익, 금융수익, 주당순이익 항목만 정리
- 원래 DART 에서 받은 파일은 항목별로 로우가 분리되어 있습니다. 이를 결산기준일+종목별로 컬럼으로 처리
(매출액, 영업이익, 당기순이익, 금융수익, 주당순이익이 컬럼으로 표시됩니다.)
- 주가 변동을 같이 분석할 수 있도록 결산기준일에 해당하는 년도의 월별 종가를 추가
- 포괄손익계산서_연결 파일만 사용(일부 종목이 없을 수 있습니다.)
- 개인 참고용으로 만든 데이터이기 때문에, 데이터의 정확성을 책임지지는 않습니다.
아래는 샘플 그림입니다.
이와 같은 파일을 만들기 위해서 MySQL로 DART 보고서를 업로드 한 후에 데이터를 재가공했습니다. 월별 주가 같은 경우는 제가 꾸준히 API로 수집하고 있던 데이터입니다.(액분등의 이벤트로 주가가 수정된 경우 약간 안맞을수도 있겠습니다. 대부분은 맞습니다.)
그리고... 이와 같은 작업은 엑셀보다 데이터베이스화해서 SQL로 작업하는 것이 훨씬 더 쉽습니다. 생각보다 SQL을 배우기는 어렵지 않습니다. 개인 업무와 직장 경력에도 큰 도움이 되는 것이 데이터와 SQL입니다. 여유가 있으시면 한 번 공부해 보시기 바랍니다. 이것도 제 블록그에서 주식 데이터와 연계해 최대한 쉽게 쓰려고 하고 있습니다.
4차 산업 혁명의 중심. 그리고 미래의 원유인 데이터. 데이터를 다루기에 가장 적합한 언어 SQL.. 반드시 배워두시는 것이 좋습니다.
이와 같은 분석은 데이터베이스를 구축해 SQL을 활용한다면 훨씬 더 쉽고 강력하게 처리할 수 있습니다.
SQL과 주식에 관심이 있다면 아래 책을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
이 글은 테마주에 투자를 추천하는 글이 절대 아닙니다. 여기서 이야기하는 테마는 갑작스러운 또는 너무도 불확실한 그런 테마가 아닙니다. 어느 정도 실적이 나오면서 전체적으로 뉴스에서도 많이 다루는 그런 테마를 뜻합니다. 산업의 큰 변화 경제적인 큰 변화를 만드는 테마를 뜻합니다. 그런 테마가 만들어졌을 때(대표적으로 2차 전지, 반도체) 어떤 종목을 어떻게 선별할지를 다루고자 합니다. 그럴 때 팍스넷을 적절하게 사용하면 매우 유용하기에 이렇게 소개하게 되었습니다.( 절대 팍스넷 관련자도 아닙니다! )
그리고.. 저만 알고 싶은 방법입니다. 좀 아꿉네요. ㅜ.ㅜ
- 필요 IT 기술 : 엑셀과 웹서핑 능력, 그리고 Ctlr+C와 Ctrl+V 능력
제 입으로 이런 말씀드리기 그렇지만... 사실 저는 DB(데이터베이스) 전문가입니다. SQL 관련 책도 한 권 집필했지요.(부끄럽습니다) 그러므로 이러한 주식 데이터는 DB화 해서 분석하는 것이 저에게는 더 쉽습니다. 하지만 많은 분들이 DB를 접하기에는 아직 거리가 먼 거 같아.. 우선은 쉽게 데이터에 접할 수 있도록 엑셀만 사용하는 선에서 주식 데이터를 분석하는 과정을 설명하고 있습니다.
포트폴리오 구성 시나리오
a. 팍스넷 접속
- 2차 전지 테마주 관련 종목을 엑셀로 카피한다.
b. 엑셀에서 시가총액, 목표주가 괴리율 등에 순위를 부여한다.
c. 부여된 순위의 합이 가장 좋은 종목들을 추려 투자하거나 분석한다.
여기서는 팍스넷을 사용해 2차 전지 테마주 들 중에 옥석(?)을 가리는 작업을 해보도록 하겠습니다. 이미 2차 전지에 대한 테마는 형성된 지가 제법 되었습니다. 지금 뛰어든다는 것은 위험할 수도 있습니다. 그러므로 여기서 소개하는 글은 과정에 대한 참고로 생각해 주시기 바랍니다.
본격적인 설명에 앞서 늘 드리는 말씀 적어 드리고 시작하겠습니다.
이 글은 투자를 권유하거나 특정 종목을 추천하려는 글이 아닙니다. 데이터를 이와 같이 분석할 수 있음을 알려드리고 싶은 글입니다. 또한 이 글에 나온 종목에 투자해 발생한 손해는 투자자 본인의 책임임을 명심하시기 바랍니다.
위와 같은 작업을 하면, 선택한 항목의 내용들만 추려서 조회가 다시 진행됩니다. 결과가 조회되면 기간을 120일로 설정합니다. [그림. 4]와 같습니다.
[그림. 4]
1. 120일을 클릭합니다.
- 120일을 선택하면, 기간등락률, 외인, 기관, 개인에 관한 내용이 120일로 변경됩니다.
분석할 때 120일 동안 등락률이 어떠한지도 고려하기 위해서 120일을 선택했습니다.
2. 팍스넷 조회 내용 엑셀로 옮기기
위에서 조회된 내용을 엑셀로 카피해서 옮길 차례입니다. 위에서 조회된 결과를 보면 총 세 페이지에 걸쳐 결과가 나옵니다. 우선은 첫 번째 페이지의 내용만 엑셀로 옮깁니다. 새로운 엑셀 시트를 열어 놓습니다. [그림. 5]를 참고하세요.
[그림. 5]
1. 조회된 결과를 카피하기 위해서, 정확히 '2차전지' 바로 앞 부분에서 마우스 좌클릭을 합니다.
2. 1에서 마우스 좌클릭한채로 끌어서(Drag) 리스트 마지막에 다음페이지 화살표 바로 뒤에서 좌클릭을 놓습니다.
- 그림과 같이 파란색으로 글씨들이 선택되어집니다.
Ctrl + C(복사)를 입력합니다.
3. 새 엑셀로 이동합니다. 새 엑셀의 가장 첫 번째 셀에서 Ctrl+V(붙여넣기)를 입력합니다.
팍스넷의 내용을 엑셀로 붙여 넣으면 불필요한 내용도 복사가 됩니다. 적절하게 삭제 작업을 합니다. [그림. 6]을 참고하세요.
[그림. 6]
1. 엑셀 윗부분에 불필요 부분의 행 헤더를 마우스 좌클릭으로 동시 선택 후,
- 선택된 영역에서 마우스 우클릭 후 팝업 메뉴에서 '삭제'를 선택
2. 엑셀을 아래로 내리다 보면 불필요한 부분이 또 있습니다. 1번과 마찬가지 방법으로 삭제합니다.
팍스넷에서 2차 전지를 조회했을 때 총 세 페이지의 데이터가 있었습니다. 이번에는 두 번째 페이지를 엑셀로 옮길 차례입니다. 잊지 말아야 할 것은 팍스넷에서 2 페이지로 이동후 '기간누적'을 반드시 120일로 눌러주셔야 합니다. [그림. 7]과 같습니다.
[그림. 7]
1. 팍스넷에서 2페이지를 선택합니다.
2. 상단의 기간누적을 120으로 선택합니다.
위에서 팍스넷의 1페이지를 엑셀로 옮겼던 거랑 같은 방법으로 2페이지를 기존 엑셀 아래쪽에 추가 카피합니다. [그림. 8]을 참고해주세요.
[그림. 8]
1. 2페이지 결과 중, '2차전지' 앞부분을 마우스 좌클릭으로 선택해서 유지
2. 1번에서 마우스 좌 클릭한 채로 아랫부분 다음 페이지 화살표까지 선택, Ctrl+C 입력
3. 기존 작업하던 엑셀의 마지막 행 아래에서 Ctrl+V 입력
이제, 이전에 작업한 것처럼, 엑셀에서 불필요하게 카피된 빈 행들을 제거합니다. 이와 같은 과정으로 3페이지도 엑셀로 옮겨줍니다.
3. 엑셀에서 불필요 데이터 제거
이제 팍스넷을 접속할 필요는 없습니다. 이와 같이 귀중한 정보들을 보여주는 팍스넷에 감사할 따름입니다.! (팍스넷 관련자 아닙니다.^^)
이제 팍스넷의 내용을 카피한 엑셀만 사용하면 됩니다. 엑셀에서 분석에 지장을 주는 불필요 데이터를 제거하겠습니다.
[그림. 9]와 같이 필터를 사용에 목표주가 괴리율이 0인 데이터를 제거합니다.
작업에 앞서 목표주가 괴리율에 대해 간단히 설명하도록 하겠습니다. 증권사는 종목 별로 목표주가를 제시합니다. 이 목표 주가와 현재 주가가 차이가 나는데, 이를 괴리율이라고 합니다. 만약에 괴리율이 크다면, 목표 주가를 잘 못 제시한 걸 수도 있겠지만, 그만큼 올라갈 주가가 있다고 해석할 수도 있습니다. 그런데... 이러한 목표주가를 모든 종목에 대해서 제시하지는 않습니다. 기본적으로 어느 정도 덩치가 있고, 증권사 나름 투자 가치가 있다 판단하는 종목들에 목표 주가를 제시합니다. 그러므로 여기서는 목표 주가를 제시하지 않아 괴리율이 0인 데이터는 제거하려고 합니다.
[그림. 9]
1. 데이터 > 필터 메뉴를 선택
2. 목표주가괴리율 필터를 선택
3. 0 만 체크해서 선택
4. 확인
5. 목표주가 괴리율이 0으로 불필요 데이터이므로 삭제 처리
- 조회 결과 행의 헤더를 마우스 좌클릭으로 모두 선택 후, 마우스 우클릭 -> '삭제' 메뉴 선택
불필요한 목표주가 괴리율이 0인 종목들을 모두 삭제한 후에 다시 아래와 같이 필터 버튼을 누릅니다. 그러면 필터가 사라지면서 모든 데이터가 조회됩니다.
[그림. 10]
방금 목표주가 괴리율이 0인 데이터를 삭제한 것처럼, '기간등락률'의 값이 '-'인 데이터도 삭제해주세요. 자세한 설명은 생략하겠습니다. 목표주가 괴리율 처리한 것과 똑같이만 하시면 됩니다. (필터를 사용에 기간 등락률이 - 인 데이터를 골라내고 선택 후 삭제 처리, 다시 필터를 눌러 원래대로 돌아가면 됩니다.)
4. RANK를 사용한 순위 작업
엑셀에는 RANK라는 함수가 있습니다. RANK를 사용하면 특정 항목의 값이 참고 값들 중에 몇 위에 해당하는지를 얻어 낼 수 있습니다. 전혀 어렵지 않습니다. 천천히 따라해보시죠.
[그림. 11]을 참고해 시가총액(억)에 대한 순위를 부여합니다.
[그림. 11]
1. L1 부분에 순위_시가로 헤더명을 입력합니다.(사실 헤더명은 아무거나 상관없죠)
2. L2(L열 2행)에 다음 수식을 입력 : = RANK(C2, C:C, 0)
3. L2의 우측 아래 모서리에 마우스를 대고 더블클릭
- L2의 내용이 아래 행들로 모두 자동 카피됩니다.
여기서 RANK 함수에 대해 잠깐 설명을 드리면, RANK 함수는 세 개의 값을 갖습니다. 첫 번째는 순위를 구 할 값입니다. 여기서는 C2에 해당하는 1,860이 됩니다. 두 번째 변수는 순위를 구하기 위해 참고할 리스트입니다. 여기서는 C:C로 입력되어 있습니다. 이것은 C행(시가총액(억)) 전부를 뜻합니다. 그러므로 시가총액들 중에 C2의 값이 몇 등인지를 구합니다. 마지막 세 번째 값은 0 또는 1입니다. 0은 내림차순으로 순위를 정하고 1은 오름차순으로 순위를 정합니다. 여기서는 0을 사용해 내림차순 순위를 구했습니다. 내림차순은 가장 큰 값이 1등이 되고, 작은 값일수록 뒤의 등수가 됩니다. 오름차순이라면 반대겠죠. 가장 작은 값이 1등이 됩니다. 여기서는 시가총액을 사용했고, 시가총액이 클수록 좋다가 생각해 내림차순으로 순위를 처리했습니다.
이번에는 목표주가 괴리율에 대한 순위를 구할 차례입니다. 괴리율 순위를 입력하고 아래 행으로 모두 카피합니다.
[그림. 12]와 같습니다. 앞에서 살펴본 과정과 유사합니다. M2에 넣을 수식은 '= RANK(G2, G:G, 0)'입니다. 순위에는 내림차순을 사용했습니다. 다시 말해, 괴리율이 클수록 좋은 순위를 받도록 했습니다. 목표주가 괴리율이 클수록 아직 오를 가능성이 크다고 생각했습니다. (예를 들어 LG화학은 목표주가 933,953이고 현재 주가가 798,000이므로 괴리율이 17.04입니다.)
[그림. 12]
이번에는 기간등락률에 대한 순위를 부여합니다. 모멘텀이라는 말이 요즘은 대세입니다. 오르는 주식이 계속 오른다. 그러므로 여기서는 기간등락률이 높을수록 좋은 순위를 받도록 수식을 처리합니다. [그림. 13]과 같습니다. N2에 수식을 입력하고 아래행까지 모두 카피합니다. N2의 수식 '=RANK(H2, H:H, 0)'입니다.
[그림. 13]
5. 투자 대상 살펴보기
이제 마지막 단계입니다. 지금까지 구한 순위 3개를 합해서 투자순위를 결정하고 투자순위로 정렬해서 투자할 종목들에 대해 고민해볼 차례입니다.
[그림. 14]를 참고해 투자순위를 구합니다.
[그림. 14]
1. O1에 헤더명을 입력합니다.
2. O2에 다음 수식을 입력 : = L2+M2+N2
3. O2의 수식을 아래 행으로 모두 복사(O2 우측 아래 모서리에 마우스를 대고 더블 클릭)
이제, 투자순위로 정렬해서 조회만 하면 됩니다. [그림. 15]와 같습니다.
[그림. 15]
최종 조회된 결과는 각자 확인해보시기 바랍니다~!
여기서 사용한 순위를 구한 항목들은 제가 임의로 정한 것입니다. 각자 생각하는 투자에 고려할 좋은 항목을 선택해서 투자 대상을 골라내시면 될 거 같습니다.
저는 이와 같은 방법만 이용해서 투자를 하지는 않습니다.이와 같이 종목을 우선 추려놓고 추가적인 공부(?)와 연구를 통해 투자할지를 결정하는 것이 현명하다 생각됩니다.
어떤가요? 테마주 투자... 무턱대고 깜깜이로 종목 추천받아서 투자할 필요가 없겠죠?
감사합니다.
이와 같은 분석은 데이터베이스를 구축해 SQL을 활용한다면 훨씬 더 쉽고 강력하게 처리할 수 있습니다.
SQL과 주식에 관심이 있다면 아래 책을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
오늘은 영업이익과 주가 변화를 활용해 포트폴리오를 만들어 보겠습니다. 간단하게 '영업이익은 올랐지만, 주가는 오히려 떨어진 주식이라면 매수 찬스?'라는 아이디어로 포트폴리오를 구성해서 시뮬레이션하는 과정입니다.
결론부터 말씀드리면, 이 포트폴리오는 보유기간 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분기 영업이익 좋은 종목 찾기'를 먼저 따라 해 주세요.
2018년 사업보고서는 2018년 12월 31일 기준으로 결산한 보고서입니다. 저희가 실제 접할 수 있는 시점은 2019년 4월쯤입니다. 받은 파일의 압축을 풀면 네 개의 파일이 있는데 그중에 아래 파일을 엑셀에서 엽니다.
- 2019_사업보고서_03_포괄손익계산서_연결_20201117.txt
2. 불필요 컬럼 삭제
엑셀에서 분석에 용이하도록 불필요한 컬럼을 삭제합니다. [그림. 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]를 참고해주세요.
6. 2018년 12월 31일만 선택 (종목에 따라 2018년 3월 결산된 데이터가 섞여 있기도 합니다.)
7. 확인을 클릭
이번에는 [그림. 4]와 같은 과정으로 '당기'(2018년)의 영업이익이 500억 이상인 종목만 필터 합니다. (왜 500억이냐고 물으신다면, 특별한 이유는 없습니다. 적당히 이익이 큰 회사를 투자하자 생각했습니다. 나중에는 이러한 금액도 특정한 논리나 계산을 통해 정하면 좋을 거 같습니다. 그리고 이 부분을 변경함에 따라 수익률도 변경되니 도전해 보시기 바랍니다.)
[그림. 4]
4. 작업된 데이터를 새 엑셀 파일로 카피
불필요한 열(Column)은 삭제했지만 불필요한 행(Row)은 삭제가 아닌 필터 처리했습니다. 현재 작업하고 있는 엑셀 시트에 숨겨져 있는 상황입니다. 이대로는 나머지 분석을 하기가 효율적이지 못합니다. 작업한 내용을 카피해서 새로운 엑셀 문서로 옮깁니다. [그림. 5]를 참고합니다.
[그림. 5]
1. 필터 작업을 마친 엑셀에서 Ctrl+A 입력(전체 선택), Ctrl+C 입력(카피), Ctrl+N을 입력(새 엑셀 열기)
2. 열린 새 엑셀에서 Ctrl+V 입력(붙여 넣기)
열린 새 엑셀에 작업한 내용을 붙여 넣은 다음에 '새 이름으로 저장'을 해주세요. 그리고 헷갈리지 않게 DART에서 다운로드하여 열었던 2018년 사업보고서는 닫아 주세요.
5. 특정 월의 종가 가져오기
포트폴리오를 구성하고 수익률까지 계산하기 위해서는 종목별로 특정 월의 종가가 필요합니다. 제가 보유한 데이터를 정리해서 올려드리니 아래 엑셀을 다운해 주세요. 첨부 파일에는 종목별로 18년 4월의 종가, 19년 4월 종가, 19년 9월의 종가가 있습니다.
다운로드한 파일의 내용을 지금 작업하고 있는 엑셀의 AA 열에 붙여주세요. [그림. 6]과 같습니다.
[그림. 6[
1. '종목별_특정월종가' 엑셀을 열고 Ctrl+A(전체 선택) 입력, Ctrl+C(복사) 입력
2. 작업 중인 엑셀의 AA 열로 이동, Ctrl+V(붙여 넣기) 입력
: 다음 작업에 필요한 공간을 넉넉히 비우기 위해 AA정도로 했습니다.
: 만약에 AA가 아닌 다른 곳에 붙여 넣으시면, 다음 VLOOKUP 작업에서 영역 변경이 필요합니다.
6. VLOOKUP으로 종가 붙이기
현재 가져온 월별 종가와 작업 중이던 엑셀의 종목코드는 순서가 맞지 않습니다.
다시 말해 작업 중이던 엑셀의 첫 번째 종목과 월별 종가의 첫 번째 종목은 다른 종목입니다.그러므로 작업 중이던 엑셀에, 자신의 종목코드에 맞는 종가를 찾아서 붙여주어야 합니다. 이러한 기능을 할 수 있는 것이 바로 VLOOKUP입니다.(VLOOKUP 이해를 위해서는 별도 다른 블로그 등을 참고해주시면 좋을 거 같습니다. 우선은 따라 해 보시죠.)
[그림. 7]과 같이 VLOOKUP 작업을 해서 우리가 작업하던 엑셀에 월별 종가를 추가합니다.
[그림. 7]
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]을 참고하세요.
[그림. 8]
1. M2부터 O2를 마우스로 선택 (반드시 세 개 셀만 선택합니다.)
2. 선택된 곳에서 우측 아래 부분에 마우스를 갖다 대면, 십자가 커서가 나옵니다. 이때 더블클릭
: 더블 클릭하면 VLOOKUP 한 세 개의 셀이 아래 로우들에 모두 자동으로 카피가 됩니다.
: 각자 편한 방법으로 카피하셔도 됩니다.
7. 당기 / 전기 수식 추가하기
전기 대비 당기에 영업이익이 얼마나 늘어났는지 계산하기 위해 당기 / 전기 수식을 추가합니다. 2018년 사업보고서이기 때문에 당기는 2018년, 전기는 2017년입니다. [그림. 9]와 같이 작업합니다.
[그림. 9]
1. P열 2행에 '=J2/K2' 수식을 입력합니다.(따옴표(')는 입력하지 않습니다.)
2. 소수점을 표시하기 위해 소수점 늘이기를 세 번 정도 클릭합니다.(엑셀 상단 '홈' 메뉴에 있습니다.)
P2 셀의 수식을 아래 행에 모두 카피해줍니다. 편하신 방법을 사용하시면 됩니다. ('6. VLOOKUP으로 종가 붙이기'에서 입력한 것을 아래 행에 모두 카피한 것처럼 방금 입력한 P2 셀의 우측 하단에 마우스를 갖다 대고 더블 클릭하시면 됩니다.)
8. 당기/전기 정렬 및 순위 구하기
만들어진 당기/전기 값으로 내림차순 정렬합니다. 다시 말해 2017년 대비 2018년 영업이익이많이 늘어난 종목이 먼저 나오도록 정렬합니다. [그림. 10]과 같습니다.
[그림. 10]
1. 엑셀 최상단 메뉴에서 데이터 선택
2. 정렬 메뉴 선택
3. 그림과 같이 '당기/전기', '값', '내림차순'으로 세팅
4. 확인
정렬을 하고 나면 [그림. 11]과 같이 전기의 영업이익이 없는 데이터가 가장 위에 올라와 있습니다. '당기/전기'의 값이 '#DIV/0!'입니다. 분모의 값이 없거나 0인 경우에 이와 같은 에러 값이 나오게 됩니다. 해당 행을 삭제합니다. 분석 과정에 방해가 됩니다. 해당 행(2행) 헤더 부분에 마우스 우클릭 후에 '삭제'를 선택해 행 전체를 지웁니다.
[그림. 11]
데이터가 '당기/전기' 순서로 내림차순 되어 있습니다. 그러므로 '당기/전기 순위'를 만들어 보이는 순서대로 순위를 추가합니다.
만들어진 수식을 아래 행에 모두 카피를 해주시면 됩니다.(앞에서도 반복 설명했기 때문에, 설명은 생략합니다.)
10. 19년4월 / 18년4월 종가 순위 구하기
만들어진 '19년/18년' 값에 따라 오름차순 정렬을 하고 순위를 구합니다. '8. 당기/전기 정렬 및 순위 구하기' 단계와 같은 방법이므로 설명을 생략하고 그림만 올립니다. 주의할 점은 내림차순이 아닌 오름차순입니다. 오름차순이므로 가격이 많이 떨어진 종목부터 조회가 됩니다.
[그림. 14]
[그림. 14]와 같이 정렬을 한 후에 [그림. 15]와 같이 S열에 '19년/18년 순위'를 추가해서 순위 작업을 합니다. '8. 당기/전기 정렬 및 순위 구하기' 단계를 참고하시면 어렵지 않게 하실 수 있습니다.
[그림. 15]
11. 투자 대상 순위 구하기
'당기/전기 순위'와 '19년/18년 순위'를 구했으면 두 셀의 값을 더해서 '투자 대상 순위'를 구합니다. T열 1행에 '투자 대상 순위'라고 항목명을 정하고, T2에 '= Q2 + S2'를 입력합니다. T2의 입력한 내용을 아래 행으로 모두 카피해줍니다. [그림. 16]과 같이 투자 대상 순위가 만들어집니다.
[그림. 16]
12. 투자 대상 순위로 정렬하고 수익률 구하기
길고 긴 내용을 따라오느라 고생 많으셨습니다. 드디어 마지막 단계입니다. 만들어진 '투자 대상 순위'로 오름차순 정렬을 합니다. '투자 대상 순위'는 '당기/전기 순위'(영업이익이 늘어난 비율 순위)와 '19년/18년 순위'(18년 대비 19년에 주가가 많이 떨어진 순위'를 합한 값입니다. 즉, '투자 대상 순위'가 작을수록 영업이익은 늘어났지만 주가는 오히려 많이 떨어졌거나 그만큼 안 오른 종목이 됩니다.
[그림. 17]과 같이 '투자 대상 순위'로 정렬을 합니다.
[그림. 17]
정렬된 결과의 U2에 수익률을 구하는 공식을 입력하고, 해당 공식을 마지막 행까지 복사합니다. [그림. 18]처럼 작업하시면 됩니다.
[그림. 18]
1. U2에 수익률 공식을 입력 : =(O2-N2)/N2
2~3. 입력한 공식의 결과가 백분율로 안 나올 경우에, 홈 메뉴에 % 표시를 클릭, 백분율로 나오도록 처리.
위와 같은 작업을 한 후에 만들어진 수익률을 밑에 행까지 모두 카피해주세요.
13. 결과 마무리
'12. 투자 대상 순위로 정렬하고 수익률 구하기'까지 마무리한 결과, '투자 대상 순위' 상위 10개 종목을 2019년 4월에 매수에 2019년 9월에 매도했다면, -9%의 손해를 보게 됩니다. 절대 사용하면 안 되는 투자 전략이라 할 수 있습니다.
이를 통해 생각해 볼 문제는 이겁니다. '영업이익이 오른 것에 대비해, 주가가 덜 오른 주식은.. 위험할 수 있다.'입니다.
이익은 올랐지만 가격이 오르지 못한 것에는 다 이유가 있는 거겠죠. 그렇다면 반대로 영업이익도 오르고 주가도 오른 주식을 투자하는 역발상 투자는 어떨까요? 스스로 한 번 수익률을 계산해 보시기 바랍니다.!
그리고 이와 같이 엑셀로 데이터를 분석하는 작업에는 한계가 있습니다. 많은 데이터를 다양한 수식으로 변형하며 작업을 하기에는 너무 복잡하고 어렵습니다. 기회가 되신다면 데이터베이스와 SQL을 공부해보시기를 추천드립니다. 좀 더 쉽게 투자 전략을 세울 수 있습니다.
오늘 설명드렸던 내용은, 데이터만 갖추어져 있다면 SQL 한 문장만으로 구현이 가능합니다. 조건에 대한 변경도 매우 쉽고요.
SQL과 주식 공부에 뜻이 있으신 분은 아래 내용을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
다운로드할 수 있는 재무정보에는 재무상태표, 손익계산서, 현금흐름표, 자본변동표가 있습니다. 여기서는 손익계산서를 사용합니다. 이 글을 쓰는 시점으로 다운로드한 손익계산서의 파일 명은 '2020_3Q_PL_20201117041911.zip'입니다.
(재무정보에 변경이 발생하면 파일이 주기적으로 업데이트됩니다. 그러므로 받는 시점에 따라 파일명이 다를 수 있습니다.)
받은 파일의 압축을 풀어 보면 아래와 같이 4개의 파일이 있습니다.
- 2020_3분기보고서_02_손익계산서_20201117.txt
- 2020_3분기보고서_02_손익계산서_연결_20201117.txt
- 2020_3분기보고서_03_포괄손익계산서_20201117.txt
- 2020_3분기보고서_03_포괄손익계산서_연결_20201117.txt
저희는 위 파일 중에 '2020_3분기 보고서_03_포괄손익계산서_연결_20201117.txt'를 사용합니다. 손익계산서가 4개로 나누어져 있는데 일부 기업은 포괄손익계산서_연결이 아닌 다른 리포트에 있는 경우가 있습니다. 그와 같은 경우는 고려하지 않고 처리하겠습니다.
위 파일을 엑셀로 열어봅니다. 엑셀로 여는 방법은 엑셀을 실행한 후에 위 파일을 드래그해서 엑셀 위해 놓으시면 됩니다. [그림. 2]와 같이 파일을 엑셀에서 볼 수 있습니다.
[그림. 2]
2. 영업이익 500억 이상 종목만 찾아내기
손익계산서에는 다양한 항목이 있습니다. 이 중에서 저희는 영업이익만 확인해서 데이터를 분석할 예정입니다. [그림. 3]과 같이 필터를 통해 영업이익 데이터만 찾아냅니다.
[그림. 3]
1. 엑셀 최상단 메뉴에서 '데이터'를 선택
2. 엑셀 메뉴에서 '필터'를 선택
3. 항목코드(K열)에서 'dart_OperatingIncomeLoss' 를 필터로 선택
위와 같이 작업하면 손익계산서에서 영업이익 항목만 확인할 수 있습니다.
이제 엑셀에서 영업이익 수치를 담고 있는 M, N, O, P 열에 대해 알아보겠습니다. 당기는 현재 년도를 뜻합니다. 2020년이고요, 전기는 전년도인 2019년도입니다. 그리고 3분기 3개월은 3분기에 속해는 6~9월을 뜻합니다. 3분기 누적은 1월부터 9월까지(3분기까지) 누적된 값을 뜻합니다. 정리해보면 아래와 같습니다.
- M열, 당기 3분기 3개월 : 2020년 6~9월
- N열, 당기 3분기 누적 : 2020년 1월~9월
- O열, 전기 3분기 3개월 : 2019년 6~9월
- P열, 전기 3분기 누적 : 2019년 1~9월
실적이 좋지 않은 종목을 투자하는 건 리스크가 있습니다. 리스크를 줄이기 위해서 당기 3분기 누적(N열) 영업이익이 500억 이상인 종목만 대상으로 분석을 진행하겠습니다. [그림. 4]와 같이 숫자 필터를 사용해 영업이익 500억 이상 종목만 찾아냅니다.
[그림. 5]
1. 당기 3분기 누적에서 필터를 선택
2. 숫자 필터를 선택
3. 크거나 같음을 선택
4. >=(이상) 조건으로 500억을 입력
5. 확인을 클릭
위의 과정을 거치면, 3분기 누적 영업이익이 500억 이상인 종목만 조회할 수 있습니다.
3. 3분기 영업이익 비중 구하기
2020년도 누적 영업이익 안에서 특히 3분기 영업이익이 좋은 종목을 찾을 계획입니다. 그러므로 누적 영업이익 대비 3분기 영업이익의 비율을 계산식으로 추가합니다. [그림. 6]과 같습니다.
[그림. 6]
1. S열 1행에 'M/N'이라고 컬럼 명을 입력(임의로 아무 명이나 넣어도 상관없습니다.)
2. S열 2행으로 가서 '=M2/N2'을 수식 입력(입력 시 따옴표(')는 제거하고 입력합니다.)
- 수식을 넣으면 그림과 같이 계산된 값이 나옵니다.
- M2/N2 = 2행의 3분기 영업이익 / 누적 영업이익입니다. 즉, 누적 영업이익 대비 3분기의 영업이익입니다.
3. S열 2행에 입력한 수식을 엑셀의 마지막 행까지 복사를 합니다.
- 저 같은 경우 아래와 같은 방법을 사용합니다.(각자 편한 방법을 사용하세요.)
1단계: S열 2행에 커서를 위치시키고 왼쪽 Shift를 누른 채, 마지막 행까지 내려갑니다.
2단계: Shift를 놓습니다. S열 2행부터 마지막 행까지 셀이 선택되어 있습니다.
3단계: Ctrl+D를 입력합니다. S열 2행의 수식이 마지막 행까지 복사됩니다.
4. 3분기 영업 비중 순서로 정렬하기
S열에 3분기 영업 비중을 구했으면, 영업 비중 순서로 내림차순 정렬을 합니다. [그림. 7]을 참고합니다.
[그림. 7]
1. 데이터 메뉴의 '정렬'을 선택
2. 그림과 같이 'M/N' - '값' - '내림차순'을 선택
3. 확인을 클릭
위의 과정을 거치면 3분기 영업이익 비율이 가장 큰 종목들부터 정렬되어 결과가 나옵니다.
5. 결과 및 마무리
지금까지 잘 따라 하셨다면 아래와 같은 결과를 얻을 수 있습니다. 상위 다섯 개 종목만 살펴보면 롯데케미컬, 솔브레인, 한세실업, 한국전력공사, 풍산이 보입니다.
[그림. 8]
생각해볼 문제들이 있습니다. 우선 재무정보가 나오는 시점에 주가는 이미 오른 경우가 많습니다. 재무정보가 취합되어서 일반인들에게 공개되기까지는 시간이 걸립니다. 하지만 아무래도 업계 관계자나 그 기업에 관심이 많은 투자자들은 재무정보를 접하기 전에 실적이나 업황이 좋아짐을 예측해 미리 매수를 하니 당연히 주가는 선반영 된다고 생각합니다.(관계자가 재무 정보를 미리 빼돌리거나 알아냈다는 뜻은 절대 아닙니다.)
그럼에도 불구하고 이와 같이 실적이 좋은 종목들을 추려서 투자하는 것은 개인 투자자들이 가져야 할 기본적인 방향이라고 생각합니다.
그리고 더 중요한 문제가 있습니다. 이와 같이 종목을 찾아내서 투자하면 수익이 나는 것인가이죠. 이를 위해서는 과거 데이터를 더 모으고 주가와 연동해서 테스트를 해볼 필요가 있습니다. 이 부분은 SQL, 파이썬과 관련된 기술이 필요하기 때문에 여기서 다루지는 않습니다.
SQL과 주식에 관심이 있다면 아래 책을 참고해주세요.
※ 주의 사항
▶ 절대 특정 종목을 추천하거나 투자를 권유하는 책이 아닙니다.
▶ 데이터 분석을 공부하기 위한 MySQL 책입니다.
▶ 책의 내용을 통해 얻은 종목에 투자해 발생한 손해는, 저를 비롯한 책 관계자 누구도 책임지지 않습니다.
