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

구글 독스(구글 드라이브/스프레드시트)를 이용해서 야후 파이낸스나 구글 파이낸스를 이용해서 실시간(20분 지연이지만...) 정보를 받아 오는 방법에 대해서 포스팅 한 이후로 관련 문의를 하시거나, 해당 키워드로 검색해서 블로그를 찾으시는 분들이 꽤 많습니다. 사실 주식 투자 하시는 분들 중에서는 HTS나 MTS를 사용할 수 없는 분들도 있을 수 있고, 저처럼 계좌가 여러개라거나 여러가지 이유로 관리에 어려움을 겪으시는 분들이 많을 듯 합니다. 또한, 제가 매달 올리는 월간 운용 보고서 등 도 사실은 수작업으로 만들려고 하면 시간이 많이 걸리겠지만, 구글 독스를 사용하면 아주 간단하게 할 수 있는 작업들이 많이 있습니다. 

앞으로 몇차례에 걸쳐서 구글 독스를 이용해서 자동 주식 관리툴을 구축(?)하는 방법에 대해서 한번 설명해드려보도록 할게요. :) 

(1) 주식 종목 리스트 다운 받아서 구글 독스에서 활용하기

첫번째는 주식 종목 리스트를 다운 받아서 구글 독스에서 활용하는 방법입니다. 물론 나는 투자 종목이 몇개 안되서 종목 정보나 종목 코드 같은 것들은 수작업으로 입력해도 상관없다 생각하시는 분들이라면 모르겠지만, 저처럼 여러 종목에 투자하거나 스크리닝 목적으로 여러 종목에 대한 정보를 받아와야 하는 경우에는 종목명과 종목코드에 대한 정보가 필요합니다. 

우선은 주식 종목 전체 리스트를 다운받으러 KRX 홈페이지에 갑니다.

2016/9/7 업데이트

그동안 홈페이지에 조금 변동사항이 생겼네요. 아래의 링크로 가셔서 동일하게 다운받으시면 됩니다. (유가증권, 코스닥 분류해서 다운로드)

http://kind.krx.co.kr/corpgeneral/corpList.do?method=loadInitPage

코스피 종목 리스트를 원하면 '유가증권'을 선택한 후 '조회' 아이콘을 클릭.

코스닥 종목 리스트를 원하면 '코스닥'을 선택한 후 '조회' 아이콘을 클릭.

코넥스 종목 리스트를 원하면 '코스넥'을 선택한 후 '조회' 아이콘을 클릭하면 됩니다. 

(참고로 코스넥은 야후나 구글에서 실시간 정보를 긁어오는 것은 불가능합니다. 또한 전체로 그냥 다운로드를 하면 나중에 구분할 수 있는 방법이 없으니 꼭 따로 다운로드 받으세요.)

깔끔하게 엑셀로 받을 수가 있습니다. :) 이제 엑셀로 받은 자료를 복사 + 붙여넣기로 구글 독스 스프레드시트를 하나 만들어서 정보를 입력합니다. 주의하실 점은 다운로드 받으실때 코스피, 코스닥, 코스넥을 따로 받지 않고 전체로 다운로드 받으면 해당 정보를 알려주는 칼럼이 따로 없으니, 따로따로 선택하셔서 다운로드 받으신뒤, 붙여넣기 하실때 코스피인지, 코스닥인지를 써 놓아야 합니다.

위의 스크린샷에는 문제가 없지만, 아마 붙여넣기를 해보시면 어느 문제에 하나 봉착하게 되는데, 바로 종목코드가 0으로 시작하는 경우에는 0이 사라진다는 것입니다. 예를 들어 CJ의 종목코드는 001040인데, 붙여넣기 후에 확인해보면 1040이라는 숫자가 입력되어있습니다. 이는 해당 코드를 텍스트가 아닌 숫자로 인식하기 때문인데, 향후 야후나 구글에서 인식하는 종목코드에는 숫자 6개가 다 필요하기도 하고, 일단 부정확한 정보가 되어버리므로 6자리의 숫자를 다 표시해줄 수 있는 서식으로 변경해야합니다.

변경하기 위해서는 C칼럼 전체를 선택한 뒤, 서식 > 숫자 > 추가형식 > 맞춤 숫자 형식 메뉴에 가서 위와 같이 000000 이라고 입력해주면, 6자리 숫자를 보여주되, 앞에 숫자가 모자라면 0을 채워넣어 보여주게 됩니다.

이렇게 기본 Data를 만들었으면, 야후와 구글에서 인식할 수 있는 코드를 만들어줘야 합니다. 각각의 종목 코드는 다음과 같이 구성되어있습니다.

• 야후
• 코스피 : 종목코드.KS
• 코스닥 : 종목코드.KQ
• 구글
• 코스피 : KRX:종목코드
• 코스닥 : KOSDAQ:종목코드

이를 참조하셔서 엑셀 함수를 이용해서 코드를 만들면 되는데, 유의할점은 앞서서 언급했듯이 종목코드가 숫자 6자리가 되어야 한다는 점입니다. 

엑셀 좀 하시는 분이라면 누구나 쉽게 아실 수 있는 함수를 활용하시면 됩니다. 

• 야후 코드 만들기 함수 : =if(B3="코스닥", text(C3,"000000")&".KQ", if(B3="코스넥", "N/A",text(C3,"000000")&".KS"))
• 구글 코드 만들기 함수 : =if(B4="코스닥", "KOSDAQ:"&text(C4,"000000"), if(B4="코스넥", "N/A","KRX:"&text(C4,"000000")))

이제 기본 작업은 다 끝났습니다. 이것을 data sheet로 해서 다른 tab을 만든 뒤, 거기에서는 종목명만 입력하면 종목코드를 가져올 수 있도록 vlookup 함수를 써서 만들면 됩니다. :)

• 야후 코드 가져오기 함수 : =vlookup(B8,'주식종목리스트'!D:P,11,false)
• 구글 코드 가져오기 함수 : =vlookup(B8,'주식종목리스트'!D:P,13,false)

여기에서 중간에 11, 13은 D칼럼에서 P칼럼까지를 data로 봤을때 야후 코드는 11번째 칼럼, 구글 코드는 13번째 칼럼에 위치해있기 때문에 들어간 값입니다. 만일 여러분의 data sheet에 다른 종류의 data가 더해졌거나 없는 경우에는 이 값이 다르게 들어가야 하겠죠?

위의 스프레드시트는 아래 링크를 클릭하시면 보실 수 있습니다. 모든 사용자에게 오픈 되어있으므로 연습은 자유롭게 하시되, 들어있는 data들이 지워지지 않도록 조심해주세요! ^-^

*구글 독스 종목 리스트 관리 예제 바로가기

[구글 독스 활용王이 되자!]

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

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

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

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

구글독스로 자동 주식 관리 툴을 만들어보자 (4) - 국내 포털 사이트 금융 정보 가져와보자

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

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

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

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