Home / 잘 굴리기/국내직접투자 / 구글독스로 자동 주식 관리 툴을 만들어보자 (6) – 고급반. 종합적인 배당주 포트폴리오 만들기

구글독스로 자동 주식 관리 툴을 만들어보자 (6) – 고급반. 종합적인 배당주 포트폴리오 만들기

요즘 취미중에 하나가 주식 투자를 하는 해외 블로거들의 블로그를 서핑하는 것인데요. 하던 와중에 국내 배당주 투자자들도 유용하게 쓸 수 있을만한 구글 스트레드시트 템플릿을 발견했습니다. +_+ 저도 앞으로 요긴하게 써볼까 하고, 몇부분 국내 실정에 맞게 수정하여 공개해봅니다. 


 Original Worksheet Credit : No More Waffles. THANK YOU, NMW!

궁극의 배당주 포트폴리오 템플릿!

우선 이렇게 생겼습니다! ^ㅁ^/ (이미지 클릭하시면 크게 나옵니다.) 죄송합니다; 종목이 너무 많죠? ^^;

이 템플릿의 목적은 간단합니다. 배당주에 투자하면서 현금흐름을 발생시키고자 하는 포트폴리오의 현황을 최대한 매뉴얼 작업을 배제하면서 한눈에 보겠다는 것이죠.

우선 늘어나는 해외 주식 직접 투자자를 위해 여러 종류의 통화를 지원합니다. 환율도 구글 파이낸스 함수로 실시간으로 업데이트 됩니다. 간단한 원그래프를 통해 어느 통화의 자산이 어느 정도의 비중을 차지하는지 쉽게 알 수 있습니다. 통화 마다의 합계는 아래의 표를 참조하여 계산합니다.

년간 발생한 배당금은 ‘Income’ 탭에 별도로 입력을 하면, 그 합계를 가져와서 계산합니다. 매년 얼마나 늘었는지도 함께 볼 수 있게 해두었습니다.

증권 계좌에서 배당금 수령 내역을 자동으로 가져올 수는 없는 노릇이니, 어느 정도의 수작업은 필요합니다. Income탭에 배당금을 수령한 날짜와 종목명, 배당금 액수 등을 차례차례 기록해두면, 위의 계산에 포함됩니다. 구글 파이낸스의 함수를 이용해서 해당 시점의 환율도 적용해서 계산합니다.



포트폴리오가 혹시 어떤 특정 업종으로 쏠려있지는 않은지 확인할 수 있는 업종별 비중 그래프도 포함되어있습니다. 

국내 주식 종목같은 경우에는 업종 코드를 자동으로 불러 올 수 있도록 ‘국내업종’ 탭에 KRX 홈페이지에서 가져온 종목별 업종 정보들이 포함되어있습니다. 우선주 등은 완전 자동으로 불러오는데는 약간의 문제가 있으니 적당히 고쳐서 쓰시면 될 것 같습니다. ^-^a;;;

저는 우선주는 그냥 이정도의 꼼수로 해결했습니다. ^-^;; 해외주식 같은 경우는 전체 DB를 구성할 방법이 없었습니다; 그냥 아시는대로 적어주시면 될듯 합니다. ㅎㅎ

하단의 테이블을 구성하는 내용들은 다른 포트폴리오와 크게 다른 점은 없습니다. 매수가와 주식수, 그리고 배당금만 입력이 필요하고, 나머지는 자동으로 계산됩니다. 통화가 다른 주식에 대해서는 현재 환율을 반영해서 원화로 환산하여 계산합니다. 

위는 배당금 Overview 탭의 내용입니다. 사실 우리나라 주식들은 대부분 비슷한 시기에 배당금이 나오기 때문에, 월별 추이가 그렇게까지 중요하진 않지만, 미국 주식 등 해외 주식에 투자하는 분이라면, 꾸준히 현금 흐름이 발생하고 있는지를 확인한다는 점에서 중요한 정보가 될 수 있습니다.

이 정보 역시 앞서서 보여드린 Income 탭에 입력된 정보를 바탕으로 출력되므로, 해당 탭에 배당금을 빠짐없이 정확하게 입력해주시는게 중요하겠죠? 🙂

우측 상단에 숫자를 입력하면 해당 년도의 정보로 다시 불러옵니다. 장기간 투자를 하셨던 분이라면 과거의 정보 등을 쉽게 불러올 수 있어 유용할 듯 합니다.

배당주에 투자하시는 분이라면 아주 유용하게 쓰실 수 있는 템플릿을 소개해보았습니다. 🙂 이 구글 스프레드 시트는 아래 링크를 통해 공개해두었으니, 관심이 있으신 분은 참조하세요. ^^ 단, 이 문서같은 경우는 여러가지 함수나 참조가 많이 걸려있어서, 수정은 안되도록 막아 두었습니다. 혹시 자신의 구글 독스에서 사용하고 싶다 하시면, 파일 > 사본만들기 메뉴를 통해 사본을 만들어서 사용하세요! 🙂

국내외통합 배당주 포트폴리오 자동화 시트 바로가기

질문은 댓글로 받습니다. ლ(´ڡ`ლ)

*2016/11/2 업데이트


구글 파이낸스에서 긁어오는 미국 배당주 정보에 오류가 자주 발생해서 야후 파이낸스에서 지난 1년간의 배당금을 가져오는 것으로 수정했답니다. 좌측 하단의 노란색으로 되어있는 부분이 미국 주식의 지난 1년간의 배당금을 불러오는 함수입니다. 복사해서 사용하세요. ^_^

파이낸셜프리덤 블로그에 올라오는 새글 편하게 읽는 방법

 –  이메일로 받아보기 / Feedly에서 구독 / RSS Feed 구독하기 / 네이버 이웃커넥트  

 그리고 페이스북에서도 만나요!



이 글이 도움되었다 생각하시는 분은 공감 하트  클릭 부탁드려요 😀

Check Also

구글 스프레드시트 활용팁 (11) – 중국 상해 주식 정보 불러오기

최근 구글 스프레드시트 활용팁 시리즈는 여러분의 문제점을 해결해드리고 있습니다. 이번에는 봄바람님께서 googlefinance 함수에서 중국 상해 주식은 …

  • 함석진

    님 최고임. 연락하시면 밥사드림 ㅋㅋ

    • 연락처를 남겨주셔야 연락을 드리죠 =ㅁ=ㅋㅋ

  • 김덕용

    Good! 대단합니다. 좋은 정보 감사합니다.

    • 유용하게 쓰세요 🙂

  • 쇼크렛

    수익률 %가 부정확한게 마음에 안 들어서 ROUND함수로 바꾸었는데 %표시가 안 나오네요. 어떻게 하면 되나요?

    • 메뉴에 있는 % 버튼 누르면 되지 않을까요? 정확히 어떻게 표시가 되시는데요? 5%라고 나와야 하는데 0.05 이렇게 나오는건가요? 그런거면, % 버튼 눌러서 서식만 적용해주시면 될 것 같아요.

    • 쇼크렛

      =ROUND(A2*100/B2, 2) 이렇게 하게해야 %가 정확하게 표현되는데 %버튼을 누르면 5%로 표시되어야 하는데 500.00%이렇게 표시되네요.

    • A2가 수익이고, B2가 매수가인가요? 중간에 *100을 없애시면 될 것 같은데요? A2*100/B2 대신에 A2/B2 라고 하시면 될 듯 하네요.

    • 쇼크렛

      100을 없애고 %양식이 소숫점을 자르게 하면 정확도가 떨어지게 되죠. 그래서 일부러 ROUND를 사용하는건데요.

    • 음? 제가 뭔가 이해를 못하고 있는 것 같은데요. 왜 정확도가 떨어진다고 하시는지 이해가 잘…^^;;; 혹시 좀 설명해주실 수 있나요? 제가 생각할땐 round 함수를 써서 하나, %를 쓰나 별 차이가 없을 것 같은데요.

      굳이 round 함수를 쓰고 뒤에 %만 붙이고 싶다면 concatenate함수를 쓰셔서 뒤에다 붙여버리는 방법도 있겠지만, 그러면 숫자로 인식이 안되서 문제가 생길 수 있으니 말이죠.

    • 쇼크렛

      아 처음 공개해주신 부분이 소숫점 1자리만 나와서 부정확하게 나오는거 같아서 수정해봤는데 0.00% 형식을 쓰니까 정확하게 나오네요. 감사합니다.

  • Youngbin,

    I Can’t understand a word you’re writing, but thank you very much for referring to my template. 🙂 Glad to see it being used all the way on the other side of the globe! Your changes look useful too!

    Best wishes,
    NMW

  • 돌고래

    와우! 템플릿이 어마어마하네요!

    • 유용하게 쓰시길… ^-^*

  • 돌고래

    안녕하세요? 추가매수하거나 일부매도해서 수량과 보유기간에 변동이 있는 경우는 어떻게 관리를 하시나요?

  • 성투하세요

    잘봤습니다.
    A항목에 현재가격, B항목에 매수하고싶은 가격,=>여기까지는 인터넷보고 만들었는데 C항목에 그에대한 괴리율을 %로 만들고싶은데 어떻게 명령어를 써야될까요?
    예로) 가투소에 즐겁게투자 필명쓰시는분처럼 관심종목을 만들고 싶은데 그분은 엑셀을 사용하시는거같아서 염치 불구하고 질문드립니다 ^^

    • 예를 들어 A1 셀에 현재가격 10,000원, B1셀에 매수하고 싶은 가격 9,000원이라면, 괴리율 C1에는 (A1-B1)/A1 이렇게 계산하시면 되지 않을까요? 그러면 이 경우에는 10%라는 결과가 나오게 되겠죠?

  • 성투하세요

    넵…감사합니다 ^^

    • 성투하세요님도 성투하세요 🙂

  • 안녕하세요

    안녕하세요, 좋은 글 늘 감사드립니다~
    본격적으로 파일시트를 공부하고있다가 궁금한점이 있어서 질문 드립니다.

    국내전종목실시간, 국내업종은 주기적으로 업데이트를 해줘야되는 시트인가요?
    (아무래도 신규상장 등이 있어서 여쭤봅니다)

    감사합니다 ^^

    • 아무래도 신규상장되는 종목에 대해서는 별도로 업데이트를 해줘야하는 부분이 있기는 합니다. ^^;

    • 신규 상장되는 종목도 커버할 수 있는 방법도 궁리하여 만들어보았습니다! ^_^
      http://financialfreedom.kr/979

  • 사랑가족

    아~~~~~~정말 정말…감사드립니다……어떻게 이런걸 다 일일이 엄청 귀찮았을텐데요..
    덕분에 구글독스에 대해서 다시한번 생각하게되는 계기였구요….다른 카페분이 올렸는데 이게 왜 이렇게 되는지 몰랐다가 오늘 드디어….노다지를……..네이버까페에서 빈누님이 만들었다고 했는데…그까페에서 도저히 찾을수가 없었죠…암튼….다시한번 감사드립니다.

    • 유용하게 쓰신다면야 저야 글 쓴 보람이 있지요! ^_^ 궁금한것 있으시면 또 오셔서 알려주세요~

  • 질문있습니다

    안녕하세요, 빈누님 ^^
    좋은 자료 설명과 공유를 주셔서 감사합니다
    다름이 아니라 얼마전부터 국내주식들이 에러가 나고있는데 혹시 왜그런지 알수있을까요?
    공부를 해서 직접 만들어보고싶은데 시간이 없어서 시트를 복사해서 제가 보유한
    주식으로 수정하여서 쓰고있는데 에러가 나오는데 답답하네요 ㅠㅠ

    • 아마 daum에서 전종목 리스트를 불러오는 부분에서 에러가 나는 것으로 예상됩니다. 저에게 이메일로 스프레드시트 공유해주시면 제가 수정해드릴게요. ^^;

  • 강좌 너무 감사 합니다 .많은 도움이 될 것 같아요.