Home / 잘 굴리기/국내직접투자 / 구글독스로 자동 주식 관리 툴을 만들어보자 (3) – 10년 재무정보 가져와 가공해보자

구글독스로 자동 주식 관리 툴을 만들어보자 (3) – 10년 재무정보 가져와 가공해보자

3번째 시간입니다! 많은 분들이 지난 시리즈 1탄, 2탄을 많이 사랑해주셔서 감사합니다. 🙂 이번 시간에는 기업의 재무정보를 가져와서 입맛에 맞게 요리하는 방법에 대해서 이야기해보겠습니다. 


2탄에서 바로 직전 년도의 배당금을 기준으로 한 시가배당률을 가지고 순위를 매겨보았지만, 사실 그것만 가지고는 좋은 기업을 찾아내기가 쉽지 않습니다. 그 배당금이 일시적으로 지급된 것인지, 주당순이익은 작은데 대주주 등이 돈을 빼돌리려는 목적으로 배당을 하는 것은 아닌지, 해당 기업이 앞으로도 꾸준히 이익을 내서 배당을 꾸준히 지급할 수 있을 것인지 등등. 여러가지 면모를 종합적으로 살펴야 장기적으로 투자하기에 적합한 배당주인지 아닌지를 판단할 수가 있습니다.

그러기 위해서는 과거 장기간의 재무제표가 필요합니다. 전자공시시스템 DART에 가면 얻을 수 있는 자료이긴 하지만, 일일히 검색해서 다운로드 받아서 또 거기서 가공을 해야된다는 점이 번거롭기도 하고, 시간도 많이 걸리죠. 그래서 저는 그럴때는 감사하게도 무료로 정보를 제공해 주고 있는 아이투자 사이트를 자주 이용합니다.

아이투자 만세!

물론 재무제표의 모든 정보가 나오는 것은 아닙니다만, 제가 기본적으로 궁금해하는 정보들을 오히려 일목요연하게 볼 수 있어서 자주 참고하는 사이트입니다.

오늘은 아이투자에서 제공하는 정보와 구글 독스를 활용해 보도록 하지요. 🙂 그런데 아마 여기까지 이야기를 읽고, ‘아~ 그러면 아이투자에 있는 테이블을 긁어다가 구글 독스에 붙여넣기를 하는거구나’라고 생각하는 분이 계실지 모르겠으나, 좀 더 고차원적이면서도 덜 번거로운 방법을 소개하겠습니다!

구글 스프레드시트 함수 목록을 보시면 구글에서 재밌는 함수들을 많이 지원하는 것을 확인할 수 있습니다. 그 중에서도 아래의 함수들은 외부의 정보를 import하는 것을 아주 쉽게 만들어주고 있습니다.

  • IMPORTDATA : .csv(쉼표로 구분된 값) 또는 .tsv(탭으로 구분된 값) 형식으로 주어진 URL에서 데이터를 가져옵니다. 
  • IMPORTFEED : RSS 또는 Atom 피드를 가져옵니다.
  • IMPORTHTML : HTML 페이지에서 표 또는 목록에 있는 데이터를 가져옵니다.
  • IMPORTRANGE : 지정된 스프레드시트에서 셀 범위를 가져옵니다.
  • IMPORTXML : XML, HTML, CSV, TSV, RSS 및 Atom XML 피드를 포함한 다양한 구조화된 데이터로부터 데이터를 가져옵니다. 

이 중에서 저는 importhtml 함수를 이용하여 아이투자 홈페이지에 있는 정보를 가져와보겠습니다. 사용법은 간단합니다.

=importhtml(“http://search.itooza.com/index.htm?seName=SK%C5%DA%B7%B9%C4%DE&x=32&y=16”,“table”,4)

위와 같이 가져오고자 하는 페이지의 주소를 첫번째, 두번째는 가져오고자 하는 형식(list와 table 2가지 사용 가능), 그리고 몇번째 table을 가져올지를 넣어주면 됩니다. 몇번째 테이블인지는 웹사이트 페이지만 보고 알기는 어려우므로 그냥 1부터 차례대로 넣어봅니다. 원하는 정보가 나올때까지. ^^

이렇게 하면 구글에서 자동으로 해당 페이지의 테이블을 불러와서 뿌려줍니다. 그런데 한가지 문제점이 보이시나요? 바로 한글이 다 깨져서 나옵니다. =_=;; 이건 해결하는 방법을 찾지 못해서 그냥 A칼럼에다가 따로 제목을 달아주는 것으로 응급조치를 해둡니다;

이제 두번째 문제점을 해결해봅시다. 두번째는 내가 원하는 종목의 정보를 불러오기 위해서 해당 웹사이트의 주소를 계속 복사, 붙여넣기 해와야 한다는 점입니다. 하지만 여기서도 방법이 없으면 제가 말을 안꺼냈겠죠? +ㅁ+ 해당 웹사이트의 주소는 http://search.itooza.com/index.htm?seName=종목명과 같은 구조로 되어있기 때문에 특정 종목명을 입력하면 해당 종목명을 붙여 넣는 함수를 만들면 해결될 듯 합니다. 그런데 여기서 세번째 문제점에 봉착합니다. 바로 한글로 되어있는 종목명을 그대로 붙이는 경우 인코딩 문제인지(?) 부정확한 주소가 되어버리는 듯 합니다.

그러던 중 이게 될까 싶어 실험을 해봅니다.

바로 한글로 되어있는 종목명 대신, 종목 번호로 불러오는 것입니다. 꼼수. 성공적.

이제 종목번호와 주소를 합체시키는 함수를 만들어봅시다. (종목번호 불러오는 방법은 1편을 참조.)

=“http://search.itooza.com/index.htm?seName=”&text(B2,“000000”)

종목번호를 붙일때는 꼭 text(B2, “000000”) 이 부분을 빠뜨리지 말고 해주어야, 맨 앞자리에 나오는 0이 빠지지 않고 입력됩니다. 

이제 종목명만 입력하면 자동으로 주소가 바뀌면서 정보를 불러올 수 있게 되었습니다. 

제가 알고 싶은 값인 배당성향을 계산해서 넣었습니다. 배당성향은 1주당 순이익 중에 얼마만큼을 배당했는지에 대한 비율로, 너무 높아도 기업의 성장성에 방해가 되고 배당의 지속성이 떨어지기 때문에, 적정한 수준으로 유지되는 것이 좋습니다. 

왜인지는 모르겠지만 DART에 올라오는 재무제표도 그렇고 시간 순서가 일반 상식과는 반대로 되어있습니다. 저는 이 자료를 가지고 그래프도 그려보고 싶으니 자료를 한번 뒤집어 보겠습니다.

노가다 안하고 테이블을 뒤집기 위해 사용된 함수. 자세한 설명은 생략합니다. ㅠ_ㅠ (너무 힘들어요;;;) 맨 아래에 붙어있는 예제 시트를 참조해주세요!

=INDEX($C7:$N7,13COLUMNS($C7:C7))



테이블이 완성되었으면 원하는 정보를 골라 그래프를 그려봅니다.

저는 배당금과 배당성향을 그려보려고 하는데, 이 경우에는 양쪽 정보의 스케일 차이가 있으므로 한쪽은 오른쪽 축으로 보내줘야 보기 쉬운 그래프가 그려지게 됩니다.

SK텔레콤의 지난 10년 동안의 배당금과 배당성향을 한눈에 볼 수 있는 그래프가 완성되었습니다. 

이제 큰 틀이 완성되었으니, 종목명만 입력하면 전체적인 data가 모두 자동으로 변경되게 셋팅이 끝났습니다. 지난번 시간에 봤던 고배당주 리스트 중에서 상위에 있던 종목 몇개를 들여볼까요?

1위의 영풍제지입니다. 지난 3년간 같은 금액을 배당하기 위해서 2013년 2014년에는 주당순이익을 훨씬 넘어서는 배당금을 지급했음을 쉽게 알 수 있습니다. 오랜 기간 동안 영업이익률도 떨어지고 있어서, 회사가 미래에 이 정도의 배당금을 계속 지급할 수 있을까 의구심이 드는 부분입니다.

올해 들어 특히 인기를 끌고 있는 전통의 고배당주 동서입니다. 지난 10여년동안 한해도 빼놓지 않고 배당금이 꾸준히 증가해 왔습니다. 배당성향도 30~40% 정도로 아주 안정적입니다. 영업이익률도 꾸준한 편이나 최근 들어 상대적인 고평가 시기임을 쉽게 알 수 있습니다.

아이투자에서 너무 고급진 정보를 무료로 제공해주고 계시는 바람에 나올 수 있었던 강좌였습니다. 🙂 1탄과 2탄에 비해서는 조금 어렵게 느껴지실 수도 있는데, 궁금한 점이 있으시면 언제든 댓글로 남겨주세요! 🙂

언제나와 같이 예제 시트도 열려있습니다.

10년 재무정보 예제 시트

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

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



Check Also

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

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

27 comments

  1. 정말 허니꿀팁!ㅎ
    감사합니다.

    • 왠만큼 IT에 관심 있는 분들이라면 다 알만한 팁이라고 생각했는데, 꿀팁이라고 생각하주시는 분들이 많아서 다행이에요 ^-^

  2. 헉 ㅎ
    지금 하시는 일이….

  3. 와 엄청납니다. 지금 하나하나 다 따라해보면서 여기까지 왔네요.
    감사합니다!

    • 해보시다가 막히는 부분 있으시면 언제든 알려주세요 ^-^* 사실 저도 모르는게 많지만;;; 제가 아는 한도내에서 알려드릴게요. 🙂

  4. 감사합니다. 복 받으실꺼에요~~
    한가지 궁금한 점은.. 표에서 특정 칼럼을 가져오는 방법은 없을까요??
    =importhtml(url, “table”,4) 이렇게 해서 10년치 재무제표를 통채로 가져오셨는데,
    “table4″에 있는 특정 항목만 가져오는 좋은 방법이 있으면 소개 부탁드립니다.

    • 일단 요리조리 찾아보았지만 그런 방법을 찾지 못했네요. 전체 테이블을 불러온 뒤 다른 시트로 불러오는 수밖에 없을 것 같아요.

  5. 감사합니다.
    덕분에 종목분석에 들이는 시간이 많이 줄었습니다.
    시리즈로 올려주시는 구글독스를 활용한 주식관리 아주 유익합니다.
    좋은 글 올려주신 보답이 꼭 수익으로 보답되길 기원합니다.

  6. 정성스런 글 잘 보았습니다!!! 감사합니다!

  7. 자바 스크립트를 이용하면 한글이 안 깨지게 해결할 수 있습니다.

  8. 함수를 안써도 시간의 흐름을 변경하는 법을 알아냈어요~!
    차트 수정할 때 고급수정>>맞춤수정>>기능>>전환 을 체크하면 시간 흐름이 전환되더라고요!

  9. 안녕하세요~~ 덕분에 좋은 정보 얻고 갑니다.. 근데 중간에 =”http://search.itooza.com/index.htm?seName=”&text(B2,”000000″) 이 함수를 입력하면 URL로 안뜨고 함수 error만 뜨네요 ㅠㅠ 왜그런거죠 ㅠㅠ

    • 실제 예제를 보여주시면 제가 더 정확한 답을 드릴 수 있을 것 같아요. 아마 ginger님이 쓰신 엑셀 시트에는 B2셀에 값이 없거나 숫자가 아닌건 아닐까요?

  10. 안녕하세요. 혹시 표 형식의 데이터가 아니라 주가 그래프 옆에 시가총액, 결산월, 상장일, 주식수 데이터를 가져올수 있는 방법이 있을까요? 표가 아니라서 불러와지지가 않는걸까요? 도움 요청드립니다~ 구글시트로 포트폴리오 관리와 전종목 스크리닝을 시도해보고 있습니다. 문과출신이고 엑셀함수에 서툴러서 애를 먹고있는데 엄청 도움이 많이 되고 있습니다. 감사합니다~~~

    • 아참. 테이블 0 이 연환산, 테이블 1이 PER PBR, 테이블 4가 연간, 테이블 5 이 분기별 데이터 더라구요~

    • 안녕하세요. ^_^

      이 내용은 제가 차후에 또 따로 강좌를 올리려고 하는 importxml 함수를 사용하시면 가져오실 수 있습니다.

      =IMPORTXML(“URL”, “//li[@class=’i-11′]”)
      =IMPORTXML(“URL”, “//li[@class=’i-12′]”)
      =IMPORTXML(“URL”, “//li[@class=’i-13′]”)

      이런식으로 하시면 각각 가져오실 수 있어요. ^^

  11. 정말 좋은 꿀팁 감사합니다!

  12. 이제 두번째 문제점을 해결해봅시다. 두번째는 내가 원하는 종목의 정보를 불러오기 위해서 해당 웹사이트의 주소를 계속 복사, 붙여넣기 해와야 한다는 점입니다. 하지만 여기서도 방법이 없으면 제가 말을 안꺼냈겠죠? +ㅁ+ 해당 웹사이트의 주소는 http://search.itooza.com/index.htm?seName=종목명과 같은 구조로 되어있기 때문에 특정 종목명을 입력하면 해당 종목명을 붙여 넣는 함수를 만들면 해결될 듯 합니다. 그런데 여기서 세번째 문제점에 봉착합니다. 바로 한글로 되어있는 종목명을 그대로 붙이는 경우 인코딩 문제인지(?) 부정확한 주소가 되어버리는 듯 합니다.

    죄송한대… 여기서부터 이해가 안갑니다.ㅠㅠ 종목 불러오기편 보고 씨트 2번에 코스피 코스닥 코넥스 따로 다운 받고 리스트 다 복사 하고 코스피, 코스닥, 코넥스 따로 구분해서 시트2번에 복사 해두었습니다!! 그다음부터 어떻게 하는지… 설명을 잘 이해 못하겠는데 상세히 설명점부탁드려용…… ㅜㅜ

    • 안녕하세요! ^^ 해당 항목의 정보를 불러오기 위해서는 정확한 웹페이지 주소가 필요한데, 그것을 위해서 ‘종목명’이 아니라 숫자로 된 ‘종목코드’를 사용해야 한다는 설명을 드린 부분이에요.

      대부분은 제가 올려드린 시트를 통째로 복사하셔서 쓰시면 해결될텐데… 어느 부분이 안되시는지 스크린샷이나 실제 사용하시는 스프레드 시트 주소를 올려주시면 제가 보고 수정해드릴게요. 🙂

댓글 남기기