brunch

You can make anything
by writing

C.S.Lewis

by JK의 계단 밑 연구실 Aug 29. 2017

구글 스프레드시트로 개별 종목 차트 만들기

구글 스프레드시트의 '구글 파이낸스' 함수를 이용하면 정말 쉽게 주식투자에 도움이 되는 도구들을 만들 수 있습니다. 저도 빈누님의 http://financialfreedom.kr/ 블로그에서 구글 파이낸스 함수를 처음 접해보고 깜놀 했는데요, 어차피 기본적인 정보는 빈누님의 블로그에 다 있으니 저는 그것을 응용해서 새로운 툴을 몇 개 만들어 보겠습니다.

이번 포스트에서는 구글 스프레드시트에서 특정 종목의 차트와 기본 정보를 가져오는 도구를 만들어 보렵니다. 마치 다음이나 네이버의 개별 종목 화면같은 도구를 구글 파이낸스를 이용해서 만들어 보는 겁니다. 대충 아래와 같은 결과물을 만들건데요, 각자의 니즈나 입맛에 맛게 커스텀마이징 하면 더 좋을 거 같습니다.

1. KRX 홈페이지에서 종목코드 가져오기

구글 파이낸스 함수들은 인자로 종목명이 아닌 다음과 같은 형태로 종목코드를 받습니다.

코스피 - KRX:종목코드

코스닥 - KOSDAQ:종목코드

종목코드를 입력하기 보다는 종목명을 입력하는 것이 훨씬 쉬우니, 먼저 종목명을 입력하면 종목코드를 가져오는 기능을 만들어 봅시다.

일단 각 종목명에 대칭하여 종목코드 데이터가 있어야 하는데 이는 KRX 홈페이지에서 얻을 수 있습니다.

KRX 홈페이지(http://kind.krx.co.kr/corpgeneral/corpList.do?method=loadInitPage)

위의 링크로 들어가시면 다음과 같은 상장법인목록 화면이 나오는데, 코스피 상장 기업 정보를 얻기 위해 유가증권을 선택하고 검색, 엑셀 다운로드, 코스닥 상장 기업 정보는 코스닥을 선택하고 검색, 엑셀 다운로드하여 따로 받습니다. 이유는 코스피와 코스닥 종목을 구분하기 위해서 입니다.

먼저 엑셀을 다운 받으면 다음과 같은 데이터가 들어있습니다. 구글 스프레드시트에 '종목기본정보'라는 시트를 하나 만들고 붙여 넣어 버립니다.

다운받은 엑셀 자료 내용

고대로 구글 스프레드 시트에 복사해 넣음

복사해 넣은 스프레드 시트에서 B열을 선택하고 'insert 1 right'를 선택합니다. 즉, 여기다 새로운 컬럼을 만들려고 합니다. 무슨 컬럼이냐하면 코스피와 코스닥을 구분하기 위한 컬럼입니다. 지금 데이터는 유가증권을 선택해서 나온 데이터이므로 모두 코스피 데이터 입니다. header에 '코스피/코스닥'이라 적고 그 밑에 코스피라 적습니다. 그리고 끝 부분을 더블클릭해서 맨 밑까지 채워 줍니다. 그럼 다음과 같은 결과가 되겠죠.

이번에는 코스닥 차례입니다. 코스닥 데이터를 다운받고 코스피 데이터 밑에 이어 붙입니다. 이번에는 header는 필요 없겠죠. 주의할 것은 '회사명'과 '종목코드' 컬럼 사이에 빈칸을 만들어야 한다는 것입니다. 이곳에 '코스닥'이라는 정보가 들어갈 것이니까요. 완성하면 아래와 같은 모습입니다. (여기서 주의할 것은 한 번에 붙이면 데이터가 너무 많아서 그런지 구글드라이브에 세이브가 잘 안되더라고요. 코스닥을 붙일 땐 그래서 전 컬럼 별로 한 줄씩 나누어서 붙였습니다.)

이제 어떤 종목이 코스피이고 코스닥인 줄 알았으니 종목코드와 결합하여 구글 스타일로 종목코드를 만들어 보겠습니다. L2칸에 '종목코드 for Google'이라는 새로운 데이터 header를 만듭니다. 이제 그 밑에 해당 종목의 구글형 종목코드를 만들면 됩니다. 여기는 만약 C열에 '코스피'라고 써 있으면 'KRX:' 문자열과 D열에 있는 코드를 합치면 됩니다. 반대로 C열에 '코스닥'이라고 써있으면 'KOSDAQ:' 문자열과 D열에 있는 코드를 합칩니다. 이러한 명령은 if문을 이용하면 구글 스프레드 시트에 쉽게 내릴 수 있습니다. 저는 다음과 같이 만들었습니다.

=if(C3="코스닥", "KOSDAQ:"&text(D3,"000000"),"KRX:"&text(D3,"000000"))

구글 파이낸스에 맞춘 종목 코드

같은 방식으로 옆에는 야후 스타일의 종목 코드를 추가해 보겠습니다. 야후의 경우 코스피의 경우 '종목코드.KS'의 형태이고 코스닥의 경우 '종목코드.KQ'의 형태입니다. 마찬가지로 if함수를 활용하면 쉽습니다.

=if(C3="코스닥", text(D3,"000000")&".KQ", text(D3,"000000")&".KS")

자, 이제 기본적인 준비는 모두 끝났습니다~

2. 본격적으로 개별 종목 차트 만들기

먼저 차트를 그리기 전에 종목명을 넣으면 자동으로 종목와 함께 현재가, 전일종가, 오늘시가, 전일 대비 가격 변동, 고가, 저가, 거래량 등의 정보를 가져오도록 만들어 보겠습니다. 모두 구글 파이낸스 함수(GOOGLEFINANCE())를 이용하여 간단히 불러 올 수 있습니다.

GOOGLEFINANCE() 함수 사용법을 간단히 말하자면 인수로 확인을 원하는 종목코드와 "정보 타입"을 넣으시면 됩니다. 예를들어 실시간 가격 정보(20분 지연)을 원한다면 GOOGLEFINANCE(종목코드, "price")로 하시면 됩니다. 정보 타입에는 실시간 가격이 외에도 위에 말한 고가, 저가 등 수 많은 것들이 있지만 이미 여러 인터넷 컨텐츠 등에서 다루고 있으므로 여기서는 넘어가도록 하겠습니다. (http://financialfreedom.kr/693-2/ 여기에도 있네요~)

저는 위의 그림과 같이 만들었습니다. 우선 하늘색 창에 빨간 화살표로 표시한 부분은 사용자가 입력해야 할 부분입니다. 왼쪽 부분은 보시는 것과 같이 종목명을 입력하는 부분입니다. 오른쪽에 입력 부분은 나중에 차트를 위한 부분으로 차트의 길이를 몇 일 짜리로 만들지 입력하는 부분입니다. 지금 300이라고 되어 있는데 이러면 300일 전 주가까지 나오는 차트를 만들겠다는 의미입니다. 모두 GOOGLEFINANCE() 함수만을 가지고 정보를 불러왔습니다.

지금까지 불러온 정보가 오늘 주가에 대한 정보라면 52주 최저가, 52주 최고가, 평균 거래량 등 더 넓은 타임 라인의 정보도 불러오면 좋겠죠. 이 역시 모두 GOOGLEFINANCE() 함수로 불러 올 수 있습니다. 주가에 대한 정보 뿐만 아니라 업종, 주요제품 등 회사에 관한 정보도 있습니다. 이러한 정보는 우리가 KRX에서 종목코드를 받아올 때 같이 받아왔습니다. 지금 '종목기본정보' 시트에 가보시면 확일 할 수 있으실 겁니다. 이런 정보도 가져와 봅시다. vlookup() 함수를 이용하면 쉽게 가져올 수 있습니다.

이제 차트를 만들어 볼 차례입니다. 차트를 만들기 위해서는 먼저 주가의 시계열 데이터가 필요합니다. 예를 들어 1년 동안의 차트를 만들려면 지난 1년간의 주가가 필요하겠죠. 이 역시 GOOGLEFINANCE() 함를 가지고 해결할 수 있습니다. (GOOGLEFINANCE 짱!)

=GOOGLEFINANCE(종목코드, "all", 시작날짜, 끝날짜 )

라고 하면 해당 종목의 시작날짜부터 끝날짜까지 시가, 종가, 고가, 저가, 거래량까지 모두 가져다 줍니다. 날짜는 구글 스프레드시트의 today()함수를 이용하면 쉽습니다. 이름 그대로 today()함수는 오늘 날짜를 계산해 주고요, 여기서 정수 만큼 더하거나 빼면 그 만큼의 날짜를 계산해 줍니다. 예를 들어 today() - 30 하면 오늘부터 30일전 날짜를 반환합니다. 이를 이용하여

=GOOGLEFINANCE(D5, "all", today()-K7, today() )

라고 입력하면 우리가 입력한 종목의 오늘부터 차트길이 만큼 뺀 날짜까지의 시계열 데이터가 쭉~ 나옵니다.

마지막으로 이제 이 시계열 데이터를 이용해서 그래프를 그려주면 완성입니다. 종가 차트와 거래량 차트를 만들어 봅시다. header 부분을 잡고 insert -> chart를 선택하면 거의 자동으로 그려집니다. 여기서 주의할 건 차트에서 데이터 길이를 R2000과 같이 넉넉하게 지정하는 것 입니다. 그래야 나중에 차트길이를 길게 입력해도 에러 없이 의도한 대로 나옵니다. 저 같은 경우는 close(종가)만 가지고 가격 차트를 만들고 거기에 trendline을 넣어서 추세를 보기 더 편하게 꾸며 보았습니다. 거래량 차트는 막대 그래프로 꾸며 봅시다. 최종적으로 다음과 같은 그림이 나오네요.