웹쿼리를 이용한 보유주식 정리 - 2편

1편에 이어, 2편에서는 가지고 온 웹 데이터를 간단한 수식을 이용해 데이터를 가공하는 부분을 설명한다. 

일단 TO-BE 모델은 아래와 같다.

웹쿼리로 가져온 종목명과 현재가 정보와 내가 매입한 평균단가, 수량, 배당금, 최초매입일 정보를 이용해서, 수익률, 매입액, 평가액, 비중, 세전배당금, 세후배당금, 배당률, 보유일 등을 수식으로 만들어보기로 하자.

1) 먼저 매입평균단가, 수량, 배당금, 최초매입일 정보를 아래 그림과 같이 입력한다. 배당금에 대한 정보도 웹쿼리로 가지고 올 수 있다. 근데 여기서는 내가 직접 입력한 값으로 대체한다.

2) 먼저 수익률을 계산해보자. 수익률 공식은 ((현재가/매입평단가)-1) * 100 이다. 첫 셀에서 이 공식을 지정하고 마우스 드래그 하면 아래 셀의 내용도 채워진다. 그리고, 셀 서식으로 소수 자릿수를 2자리까지만 표기하자. 

3) 수익률이 마이너스 일 때는, 파란색, 플러스 일 때는 빨간색으로 표기해보자. 엑셀에는 조건부 서식이라는게 있다. 조건부 서식 기능을 이용해서 조건을 만족할 때, 서식을 지정하여 해당 기능을 구현해보자.

먼저, 수익률의 데이터를 마우스 드래그로 하여 선택한다. 

그 다음, 조건부 서식=>새 규칙을 선택한다. 

새 서식 규칙에서 "수식을 사용하여 서식을 지정할 셀 결정" 을 선택한다.

그 다음, 다음 수식이 참인 값의 서식 지정 란에, 선택한 첫번째 셀 번호를 입력하고 0보다 적을 때, 사용할 서식을 지정한다. 아래 그림처럼 따라하면 된다.

반대는 새규칙을 하나 더 추가해서, =E3>0 으로 규칙을 지정하고, 반대의 색을 선택하면 된다. 새로운 조건부 서식이 지정되면 아래와 같을 것이다. 생각해보니, 수익을 빨간색으로 표시하는게 맞겠다. 일단 나는 넘어가고, 따라 하시는 분은 색을 바꿔 보시는 걸로...

4) 이제 매입액, 평가액, 비중을 구해보자. 

매입액은 평가액은 각각 매입평단가*수량, 현재가*수량 으로 쉽게 구할 수 있다. 그리고 비중은 (매입액/전체매입액)*100 으로 구할 수 있다.

먼저 매입액, 평가액을 먼저 구한 다음, 자동합계를 통해 전체 매입액과 전체 평가액을 구한다.

그 다음, 위에서 구한 전체 매입액을 통해, 비중을 계산한다. 비중은 (매입액/전체매입액)*100 이다. 여기서 주의할 점이, 첫 번째 값을 비중을 구하는 공식대로 입력하고 아래의 값을 구하기 위해 마우스 드래그를 하면 아래와 같이 나올 것이다. 이유는 전체매입액을 잘 못 참조하기 때문이다.

전체 매입액이 고정되어 있기 때문에, 셀의 절대 지표를 나타내는 $를 사용해야 한다. 고려아연의 비중 계산을 다음과 같이 수정한 후, 마우스를 드래그 하면, 아래와 같이 정상적으로 비중 데이터를 산출할 수 있다.

5) 세전배당금, 세후배당금, 그리고 배당률과 보유일수를 구해보자.

세전배당금은 (전기)배당금과 수량을 곱하면 된다. 세후 배당금을 구하려면, 주식 배당의 세금 비율을 알아야한다. 배당금을 받을 때, 배당세 14% + 주민세 1.4%로 총 15.4%가 된다. 세후 배당금을 구하려면, 세전배당금 * (100-15.4)/100으로 구하면 된다. 배당률은 세전배당금/현재가로 계산하면 된다.

보유일은 어떻게 계산할까?

먼저 오늘 날짜를 셀의 아무데나 넣어 놓는다. 함수를 이용하자. 오늘 날짜를 가지고 오는 함수는 =TODAY() 이다.

그 다음, DATEDIF 함수를 통해, 오늘날짜와 최초매입일 간의 간격을 계산해야 한다. 단위는 일(d)로 표기하기로 한다.

공식은 =DATEDIF(최초매입일, 오늘날짜, "d")  이다. 적용하면 아래와 같다.

6) 이제 끝이다. 라고 말하고 싶지만, 중요한 일이 하나 남아 있다. 엑셀 데이터를 닫고 다시 열어보자.

데이터->모두 새로 고침 버튼을 누르면, 종목명과 현재가의 데이터가 없어지면서 정리한 데이터가 엉망이 된다.

이유는 웹쿼리에서 가져오는 데이터가 로그인을 통해서 가져와야 하는 데이터 이기 때문이다. 굳이 로그인을 통해서 데이터를 가지고 오는 이유는 두 가지를 생각할 수 있다. 

하나는 내가 관리하고자 하는 데이터(종목)만을 가져오기 위해서이다. 

둘째는 현재가를 포털 기준인 실시간으로 업데이트할 수 있다. 

실시간 데이터를 가져오기 위해 네이버의 증권을 이용할 수도 있다. 다만, 글자가 깨는데... 원인은 아직 못 찾았다. 세종데이터의 경우에는 다양한 데이터를 웹쿼리로 가져오기에 가장 적당한 형태로 되어 있다.  하지만....주식 데이터가 하루 단위로 업데이트 되는 데이터로, 실시간 업데이트가 아니다.

그래서 본 포스팅의 예제는 실시간 데이터 쿼리를 위해, 다음 포털의 증권 메뉴를 이용했다. 

로그인 세션 문제로 인한 데이터 초기화 문제를 해결하려면, 최초 로그인을 수행할 수 밖에 없다.

일단 위의 데이터를 저장하지 말고 닫아버리자. 다시 문서를 연 다음, 오른 쪽 마우스 클릭 후, Sheet1의 쿼리 편집을 열자.

아래 그림과 같이 나오면 로그인 후, 다시 데이터를 가져오자.

그 다음, 모두 새로고침 버튼을 누르면, 업데이트 된 데이터를 볼 수 있다.

★ 본 포스팅은 초보 투자자 입장에서 작성한 보유 종목을 정리한 표입니다. 각종 자료를 산출하는데에 대해 오류 및 더 좋은 방법이 있으면 조언 부탁 드립니다. 또한, 문의 사항 남겨 주시면, 최대한 알아보고 답변 드리겠습니다. 첨부는 본 예제에서 사용한 엑셀 파일입니다.

stock_example.xlsx