여러 증권사를 거래 하다 보니 수익률 계산이 되지 않아 네이버/다음 증권 을 이용 하였으나 입력에 어려움이 있어 수익률 계산 하기 위하여 짧은 엑셀 지식으로 수익률 계산 시트를 만들어 봤습니다 .
개인적으로 사용하려고 사용 중입니다.
● 매도시점에서 평균단가를 계산하여 실현 손익을 구현 합니다.
● 매수는 매수일별로 현재가 기준으로 손익이 얼마인지 표시 하였습니다.
● 종합잔고 평가는 : 현재 평가 손익 + 실현손익 + 배당금수익
매수, 매도,배당금 등의 데이터를 따로 입력 하도록 만들었습니다. 시트는 다음과 같이 5 개로 되어 있습니다 .
파일 다운로드 : https://goo.gl/91ugkg
엑셀시트의 구성
1) 종합
다음과 같은 항목으로 이루어져 있습니다.
종목명,잔여주식수,평균단가,현재가,잔고평가액,현재평가액,평가수익금,평가수익률,총매수금액
매수평균단가,총매도금액,매도평균단가,매도손익금,잔고비중,평가액비중,총배당금
최종평가손익 : 배당금까지 포함한 손익입니다.
이름정의
매수종목명,매수금액,매도종목명,매도금액 등입니다
이름정의는 수식메뉴에 "이름 관리자"에 있습니다. 이렇게 이름을 정의 해두면 $E1:$E65535 형태로 영역을 해주지 않아도 E열의 모든 데이터를 영역화 해서 지정 할 수 있습니다.
예를 들어 E1 에는 1 E2에는 2가 있을때 E1:E2 면 1,2 가 선택 됩니다 E1:E2 를 영역으로 지정하여 덧셈영역 이름을 정해두면 SUM(덧셈영역) = 3 이 된다는 이야기이죠
OFFSET(매수!$B$2,0,0,COUNTA(매수!$A:$A)-1,1)
다음은 매수탭의 11행까지 종목명을 입력 하였다면 정확하게 2~11행까지를 영역으로 찾아내서 매수종목명이라는 이름으로 됩니다. 매수수량이라면 OFFSET(매수!$B$2,0,0,COUNTA(매수!$A:$A)-1,1) 이 되는데 SUM(매수수량) 이라고 정하면 매수한 수량의 합이 계산 됩니다. 이해 하셨나요?
OFFSET 함수는 특정셀에서 가로세로 너비, 높이 를 지정하여 영역을 만들어주는 함수 입니다.
COUNTA()는 해당 영역에 공백을 제외한 셀이 몇개있는가 알아 내는 함수 입니다. A 열 전체에 비어 있지 않는 셀의 갯수가 바로 마지막으로 입력한 종목명의 셀 이라고 할수가 있어요. -1을 해준 이유가 2행부터 시작 되기때문에 1을 빼준 것이구요. 매수탭의 $B$2를 기준으로 B열에 있는 최종 열까지 영역을 지정하는 명령어 입니다.
평균단가수식
B2는 잔고수량입니다. 매수량에서 - 매도량을 뺀것이죠.
{ } 는 배열 수식으로서 SHIFT + CTRL +ENTER 를 동시에 누르면 됩니다.
배열 수식은 설명하기 어려운데 SUMPRODUCT 함수와 같은것으로 이해 하시면 됩니다.
SUMPRODUCT 는 파라미터는 배열로 되어 있는데 배열의 요소들을 모두 1:1 로 합해서 모두 더한 값입니다. 배열 수식은 덧셈 뿐 아니라 다양한 함수등을 구현 할 수 있습니다.
하여튼 위와 같이 지정하고 SHIFT + CTRL +ENTER 를 누르면 해당 선택 영역들의 개별 셀을 검색후 조건에 맞는 것만 더해서 계산해 줍니다.
SUM( (매수종목명=A2)*매수금액 ) 이것은 매수종목명의 영역중에서 A2값만 골라내고 그 행의 매수금액을 곱해서 배열을 만들어 냅니다. SUM 은 이 배열들의 합을 모두 더하는 명령입니다.
이때 CTRL + SHIFT + ENTER를 반드시 눌러 줘야 합니다.
2) 매수
A열에서 H열까지가 데이터를 입력하는 셀입니다.
뒤에 현재가,현재기준금액, 매수건 현재기준 손익금액, 매수건 현재기준 평가금액, 수익률을 표시 했습니다. 이것은 매수단가로 현재 기준의 손익을 나타낸 것으로서 매도 여부에 상관 없이 표시 하였습니다.
매수에서의 수식
매수후 평균단가
매수후에 잔고의 평균단가를 계산 합니다. 이는 매수건별로 검증하기 위해서 계산 하였습니다.
현재가 계산식
주가탭을 보시면 종목명, 코드, 현재가, 가져온시간을 세로로 표시되는데
B열에서 종목명을 찾아서 오른쪽으로 3번째 열에 첫번째 행에 대한 셀을 찾는 방법으로 주가를 가져옵니다.
2번째가 실제 주가탭에서 주가를 추출 하는 방법인데 가르키는 B4 셀에서 ▼ 앞의 공백까지의 글자가 주가에 해당 되는 부분입니다.
3) 매도
실현 손익으로 구현 하였습니다.
매도 시 매도전 평균단가를 계산 하여 매도평균단가로 정확하게 얼마의 손익이 나오는지 계산하여 매도탭에 계산 하였습니다. 세금포함한 경우도표시 하고 마이너스면 빨간색으로 나옵니다.
A2는 매도일입니다.
매도일까지의 매수한 수량 에서 매도일이전에 매도한 수량의 차이 입니다.
4) 배당금
현재까지 받은 배당금을 종합탭에서 현재의 손익금액과 합산하도록 했습니다.
5) 실시간 주가 데이터
Excel의 웹 쿼리(Web Query)를 이용하여 데이터를 가져옵니다.
데이터의 출처는 팍스넷을 이용 하였습니다.
데이터 입력 방법
매수 , 매도 , 배당금 테이블에 증권사에서 뽑은 거래내역 데이터를 종합하여 입력 하면 된다 .
1. 매수데이터는 I 열에서 M 열까지 있는 수식을 복사하기 위해서 제일 마지막의 하나의 행을 복사하여 다음 행에 붙여 넣기를 한다 . A 열에서 H 열까지 해당 종목에 맞는 데이터를 입력 하면 된다 .
2. 매도데이터도 마찬가지 .
3. 배당금은 종목 별로 매년 4 월이나 중간 배당에 받은 배당금을 입력 한다 .
새로운 종목 추가 방법
1. 주가 탭으로 이동하여 B38:F41 까지의 영역을 복사한다 . 이유는 해당 영역에 웹쿼리가 있기 때분입니다 . B42 셀에서 직접 웹 쿼리를 지정해도 되지만 주가를 가져오는 URL 이 이미 들어가 있기 때문에 복사 하는 편이 편합니다 .
2. 주가코드 바꿔치기
C42 셀에 004560 으로 코드를 바꿔 넣습니다 .
C42 셀에서 오른쪽 버튼을 누릅니다 .
에서 이름을 004560 로 바꿉니다 .
쿼리 편집을 누르면
외부 데이터 범위 속성입니다.
이것은 웹쿼리의 고유이름을 정하는 것으로서 중복되면 안됩니다.
현대중공업009540_1 을 004560 으로 바꿉니다 .
다음은 쿼리 편집을 선택 하여 코드를 바꿔야 합니다. 실제 데이터를 가져 오는 부분입니다.
주소에 009540 을 004560 으로 바꿉니다.
데이터를 가져오기 위하여 노란색 오른쪽 화살표를 클릭한 다음 가져오기를 누릅니다 . 바뀐 것을 보실 수 있습니다 .
다음은 종합 탭으로 가서 제일 마지막 열인 10 열을 전체 선택하고 복사를 하고 다음행에 복사한 행을 삽입하여 행을 추가 합니다 .
수익률 관리를 위하여 인터넷 찾아 봤는데 마땅한게 없어서 시간 투자해서 직접 만들었습니다.
만드는데 시간이 좀 걸렸네요. 공유 하실 분은 출처를 밝혀주시면 감사하겠습니다 .
네이버증권과 비교해본 결과 결과값은 맞는 것 같습니다.
아직 완벽 하지 않은 듯 하여 테스트 중입니다. 나름대로 검증을 해 보았고
혹시 사용해보시고 문제 점및 틀린 점이 있으면 피드백 부탁 드립니다.
암호는 비밀 댓글로 남겨 주시면 제가 알려 드리도록 하겠습니다.
파일은 관리를 위하여 네이버블로그로 링크 합니다.
파일 다운로드 : https://goo.gl/91ugkg
'재테크 > 주식' 카테고리의 다른 글
| 자신만의 주식 투자 전략 수립 (0) | 2018.10.17 |
|---|---|
| 주식투자 제무재표 보는법 (0) | 2018.10.17 |
| 시장의 마법사들 (0) | 2018.10.10 |
| 피터린치의 이기는 투자 (0) | 2018.10.09 |
| 엑셀(MS Excel) 로 관리하는 주식수익률(종목 자동생성 버전) (1) | 2016.12.13 |
