구글독스로 자동 주식 관리 툴을 만들어보자 (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,13-COLUMNS($C7:C7))

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

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

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

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

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

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

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

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

10년 재무정보 예제 시트

[구글 드라이브/구글 독스/구글 스프레드시트 고수가 되자!]

구글드라이브(구글 독스)의 스프레드시트를 통해 실시간 주식 정보 가져오기. (feat. 야후 파이낸스)

구글드라이브(구글 독스)의 스프레드시트를 통해 실시간 주식 정보 가져오기 2탄 (feat. 구글 파이낸스)

구글독스로 자동 주식 관리 툴을 만들어보자 (1) - 주식 종목 리스트 다운로드 방법 (코스피, 코스닥, 코넥스)

구글독스로 자동 주식 관리 툴을 만들어보자 (2) - 배당률이 높은 고배당주를 찾아보자

구글독스로 자동 주식 관리 툴을 만들어보자 (4) - 국내 포털 사이트 금융 정보 가져와보자 (실시간 주가, 원자재가격, 기업공시 등)

구글독스로 자동 주식 관리 툴을 만들어보자 (5) - 실시간으로 업데이트 되는 내 포트폴리오 만들기

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

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

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