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

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

 

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

 

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

 

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

 

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

 

 

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

 

 

 

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

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

 

 

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

 

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

 

 

 

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

 

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

 

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

 

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

 

 

*2016/11/2 업데이트

 

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

 

 

 

 

 

 

Categories: 돈 굴리기

56 Comments

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

  1. 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

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

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

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

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

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

    감사합니다 ^^

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

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

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

  4. 좋은 자료 배포해주셔서 감사합니다.
    배당주 포트폴리오 시트를 사용하려고 하는데 History 탭은 사용법이 없던데 어떻게 쓰는건가요? 자동으로 입력이 되는건가요?
    알려주세요ㅠㅠ

      1. 자료 정말 감사합니다. 보기도 좋고 예쁘네요 🙂
        4종류의 미국배당주를 보유하고 있는 대학생입니다. 아직 배당을 받아본적이 한번도 없고 소액보유중인데
        스프레드 시트에서 주식의 현재가는 잘 갱신이 되는데 합계가 3천몇만원이 나오고 보유하지도 않은 산업재 및 통신도 그래프에 뜨네요 ㅜㅜ
        엑셀을 잘 몰라서 그런데 무엇이 문제일까요? 알려주시면 감사하겠습니다.

        1. 어떤 문제가 있다고 댓글 남겨주신 것 같은데.. 나중에 수정을 하셨느지 지금은 그런 내용이 없네요. ^_^; 혹시 도움이 필요하시면 언제든 이야기해주세요

          1. 아! 나머진 해결됐습니다. 근데 미국주식 1년간의 배당금을 불러오는 함수가 안되는데 어떻게 하면 작동할 수 있을까요? 일일이 적기가 귀찮아서요 🙂

  5. 좋은 자료 배포해주셔서 감사합니다.
    배당주 포트폴리오 시트를 사용하려고 하는데 History 탭은 사용법이 없던데 어떻게 쓰는건가요? 자동으로 입력이 되는건가요?
    알려주세요ㅠㅠ

    1. 어떤 문제가 있다고 댓글 남겨주신 것 같은데.. 나중에 수정을 하셨느지 지금은 그런 내용이 없네요. ^_^; 혹시 도움이 필요하시면 언제든 이야기해주세요

  6. 아! 나머진 해결됐습니다. 근데 미국주식 1년간의 배당금을 불러오는 함수가 안되는데 어떻게 하면 작동할 수 있을까요? 일일이 적기가 귀찮아서요 🙂

    아 그리고 배당주가 많아서 그런데 원래 샘플에 나와있던 것처럼 접을 수 있는 기능은 어떻게 하는지 알 수 있을까요? 펼치고 나니깐 접는방법으 모르겠네요ㅜㅜ

    1. 확인해보니 해당 data를 가져오기 위해 사용하던 Yahoo Finance API에서 더이상 과거 data를 제공하지 않는다고 하네요. 어딘가에서 가져올수 있는 방법이 있을지 좀 고민해볼게요.

      접는건 구글 시트 왼쪽에 줄 번호 있는 곳을 선택하시고 마우스 우클릭하시면 ‘행 숨기기’라는 메뉴가 있습니다. 그걸로 접으실 수 있어요~

  7. 아! 나머진 해결됐습니다. 근데 미국주식 1년간의 배당금을 불러오는 함수가 안되는데 어떻게 하면 작동할 수 있을까요? 일일이 적기가 귀찮아서요 🙂

    아 그리고 배당주가 많아서 그런데 원래 샘플에 나와있던 것처럼 접을 수 있는 기능은 어떻게 하는지 알 수 있을까요? 펼치고 나니깐 접는방법으 모르겠네요ㅜㅜ

    1. 확인해보니 해당 data를 가져오기 위해 사용하던 Yahoo Finance API에서 더이상 과거 data를 제공하지 않는다고 하네요. 어딘가에서 가져올수 있는 방법이 있을지 좀 고민해볼게요.

      접는건 구글 시트 왼쪽에 줄 번호 있는 곳을 선택하시고 마우스 우클릭하시면 ‘행 숨기기’라는 메뉴가 있습니다. 그걸로 접으실 수 있어요~

  8. 트리커는 권한을 부여하니까 해결됬습니다. 정말 감사합니다.
    한가지 기능을 추가하고싶은게 있는데 배당금 대신 주식으로 받았을 경우는 어떻게 해야할까요? 주식 수량을 1 늘리는것은 수익으로 나타낼수가 없어서 고민입니다…

      1. 항상 잘보고 있습니다. 한가지 질문이 있습니다.
        Portfolio 시트에서 배당금 O행을 E행이 통화가 USD면 배당금 앞에 $를 붙이고 KRW면 W를 붙이는 등 IF함수를 써서 해보려고 하는데 도무지 어떻게 해야할지 모르겠습니다.ㅜㅜ

        1. V19 cell에다가 아래와 같이 붙여넣어보세요.
          =if(E19=”KRW”,text(O19,”W0″),if(E19=”USD”,text(O19,”$0.00″),””))

          O19에는 배당금에 해당하는 값이 들어갑니다. 함수로 되어있다면 함수로 넣어주면 되겠죠?

          1. 아이고 답변 감사합니다. 말씀하신대로 V19에 입력하니 O19값은 그대로 0.42고 V19에 $0.42로 적혀지는데 O19 split함수 끝에다가 추가로 요래저래 적어야하는거 아닌가요?

          2. 저는 이런 함수를 쓰면 된다고 알려드린거니까요. 만약에 O19 cell에나오게 하고 싶다면 O19 cell에다가
            =if(E19=”KRW”,text([O19칸에 원래 들어있던 함수 전체],”W0″),if(E19=”USD”,text([O19칸에 원래 들어있던 함수 전체],”$0.00″),””))

            이렇게 넣으시면 되죠.

  9. 안녕하세요. 엑셀 관련하여 주가를 분석하고자 공부를 하고 있는데 써주신 글이 많은 도움이 되고 있습니다.
    항상 감사드립니다. 🙂

    포스팅된 글하고는 별개로 다른것이 궁금해서 질문드립니다.
    구글독스로 실시간 데이터를 하나의 엑셀로 다 불러오는 것은 확인했습니다만
    상장종목이 약 2,000개정도가 되는데 종목별로 엑셀을 만들려고 합니다.
    (해당 종목 상장이후의 데이터 + 매일 데이터 업데이트)
    이런 경우에는 어떻게 해야 하는지도움부탁드립니다.

    그리고 마스터 엑셀파일을 만들어서
    2,000개의 종목중에서 20일간의 평균가격 이하의 종목들을 추출을 하고 싶습니다.
    이런 경우에도 어찌해야할지 감이 잘 안 잡힙니다.

    도움 부탁드리겠습니다.

    1. 안녕하세요.
      위의 2,000여개가 되는 엑셀만드는 것은 수작업으로 만드는 방법으로 해 보려고 하고요
      그 안에 들어갈 데이터는 글 올리신 것을 보고 아래와 같이 구현해놨습니다.

      =GOOGLEFINANCE(A1,”all”,TODAY()-9000,TODAY())

      이렇게 하면 매일 데이터는 업데이트 될 것 같아요. ^^

    2. 저도 엑셀 전문가는 아니지만 설명해주신 내용을 구현하려면 단순 엑셀이나 구글 시트상의 함수로는 안되고 VBA나 App Script를 이용해야 할 것 같습니다. 특히 수천개 되는 종목을 매일 체크하려면요. ^_^ 그리고 구글 시트가 자체가 대용량의 데이터를 다루기에 그렇게 적합한 툴은
      아닙니다. 저도 예전에 보면 데이터 셀이 만개 단위가 넘어가면 속도가 말도 못하게 느려지더라구요. 좋은 방법을 찾으시길 바랄게요 ^^;;;;

      1. 아~ 그렇군요
        엑셀과 구글시트간 데이터 복사도 잘 안되는 부분이 있어서 어떤 것으로 해야 하나 고민이 많았었는데
        답변 주신 내용으로 어느정도 방향을 잡은 것 같습니다. 감사합니다.^^

  10. 블로그에 써주신 글 감사히 읽으면서 공부하는데 많은 도움이 되고 있습니다.

    아직도 이걸 쓰시는지 잘 모르겠습니다 다만 국내업종 탭에 있던 자료들이 조금 오래된것 같아서요
    제가 나름대로 갱신해보려고 해도
    https://financialfreedom.kr/834-2/
    이 링크에 있는
    http://kind.krx.co.kr/corpgeneral/corpList.do?method=loadInitPage
    이거 다운받았는데 국내업종 탭의 양식이랑은 좀 다르더라구요
    http://www.krx.co.kr/m1/m1_4/m1_4_8/m1_4_8_4/JHPKOR01004_08_04.jsp
    자료 출처에 있는 이 링크로 들어가면 메뉴가 바뀌었는지 없다고 뜹니다.

    제가 산 주식이 회사가 분리되면서 좀 변화가 있어서요 새로 갱신하고 싶은데 국내업종 탭의 자료를 어디서 구하셨는지 알려주시면 감사하겠습니다.

  11. 항상 감사히 읽고 있습니다.
    다른 게시글을 읽고 일단 상장회사 최신화는 했는데
    역시 국내업종 탭의 부문, 산업그룹을 넣고 싶어서요
    이 자료들의 출처라고 적혀있는 링크를 타면 홈페이지에 변화가 있었는지 안나오는데
    혹시 어디서 자료들 얻으셨는지 알 수 있을까요?
    투자한 기업의 분야를 분산시키고 싶어서요

  12. 월간수익률 추적의 금액가중수익률
    (총자산) 금액가중수익률
    (자기자본) 이 계속 에러가 나는데 왜그런지 알수있을까요?
    빈누님의 함수를 카피 페이스트 해도 해결이 안되네요. #NUM!이라고 나오네요

  13. 너무 멋진 툴을 만들어주셔서 감사합니다.
    대단하세요~~~~
    내용중”업종이 안나올때 (#N/A) 이정도의 꼼수로 해결합니다.”
    라는 말이 이해가 안됩니다.
    자세히 설명좀 부탁드려도 될까요….업종이 안나오는게 꽤 많습니다.

댓글 남기기

이메일은 공개되지 않습니다. 필수 입력창은 * 로 표시되어 있습니다