엑셀(MS Excel) 로 관리하는 주식수익률(종목 자동생성 버전)

종목을 추가 하는데 어려움이 있어 VBA(Visual Basic for Application) 으로 자동 생성 될 수 있도록 버튼을 만들어 두었습니다. 그래서 자동으로 종목을 추가 할 수 있도록 변경 하였습니다.
약간의 버그가 있을 수 있으니  제가 개발자가 아닌 관계로 문제가 발생하면 말씀 해 주시기 바랍니다.
엑셀에 익숙 하지 않으신 분도 쓰실수 있도록 하였습니다. 
매수,매도탭의 데이터를 입력 하셔야 데이터가 집계됩니다. 여러 증권사를 거래 하다 보니 수익률 계산이 되지 않아 네이버/다음 증권 을 이용 하였으나 입력에 어려움이 있어 수익률 계산 하기 위하여 짧은 엑셀 지식으로 수익률 계산 시트를 만들어 봤습니다 .   

● 매도시점에서 평균단가를 계산하여 실현 손익을 구현 합니다. 
● 매수는  매수일별로 현재가 기준으로 손익이 얼마인지 표시 하였습니다.     
● 종합잔고 평가는 : 현재 평가 손익 + 실현손익 + 배당금수익  

매수, 매도,배당금 등의 데이터를 따로 입력 하도록 만들었습니다. 시트는 다음과 같이 5 개로 되어 있습니다 . 

엑셀시트의 구성

1) 종합

다음과 같은 항목으로 이루어져 있습니다. 
종목명,잔여주식수,평균단가,현재가,잔고평가액,현재평가액,평가수익금,평가수익률,총매수금액
매수평균단가,총매도금액,매도평균단가,매도손익금,잔고비중,평가액비중,총배당금
최종평가손익 :  배당금까지 포함한 손익입니다.

 

2) 매수

A열에서 H열까지가 데이터를 입력하는 셀입니다.  
뒤에 현재가,현재기준금액, 매수건 현재기준 손익금액, 매수건 현재기준 평가금액, 수익률을 표시 했습니다. 이것은 매수단가로 현재 기준의 손익을 나타낸 것으로서  매도 여부에 상관 없이 표시 하였습니다. 

3) 매도

실현 손익으로 구현 하였습니다.

매도 시 매도전 평균단가를 계산 하여 매도평균단가로 정확하게 얼마의 손익이 나오는지 계산하여 매도탭에 계산 하였습니다. 세금포함한 경우도표시 하고 마이너스면 빨간색으로 나옵니다. 

---

---

---

4) 배당금

현재까지 받은 배당금을 종합탭에서 현재의 손익금액과 합산하도록 했습니다.

5) 실시간 주가 데이터

Excel의 웹 쿼리(Web Query)를 이용하여 데이터를 가져옵니다. 
데이터의 출처는 팍스넷을 이용 하였습니다.

데이터 입력 방법

매수 , 매도 , 배당금 테이블에 증권사에서 뽑은 거래내역 데이터를 종합하여 입력 하면 된다 .
 
1. 매수데이터는 I 열에서 M 열까지 있는 수식을 복사하기 위해서 제일 마지막의 하나의 행을 복사하여 다음 행에 붙여 넣기를 한다 . A 열에서 H 열까지 해당 종목에 맞는 데이터를 입력 하면 된다 . 
2. 매도데이터도 마찬가지 .
3. 배당금은 종목 별로 매년 4 월이나 중간 배당에 받은 배당금을 입력 한다 . 

새로운 종목 추가 방법

주가데이터추가 버튼을 누르면 다음과 같이 나오는데 종목코드 6자리를 입력 하면 됩니다. 

다운로드 
https://goo.gl/91ugkg

출처. 네이버 블로그 본인 블로그