[Google Drive] 구글 드라이브 사용하기(초보) - PC와 동기화하여 사용

목차

• KRX 정보데이터시스템 접속 및 종목코드 다운로드
• 구글시트로 데이터 가공
• GOOGLEFINANCE 함수로 주가 정보 가져오기

 구글시트를 이용하여 GOOGLEFINANCE 함수를 이용하여 주가정보를 확인을 위한 자료를 만들때 가장 힘든 부분은 코스피, 코스닥의 종목코드를 하나씩 확인하는 부분이다.

이러한 불편함을 해소하기 위해서 Google Finance 사이트, 증권사 HTS, 주식정보 제공 사이트 등에서 종목코드를 하나씩 확인하지 않고, 한국거래소의 "KRX 정보데이터시스템"에서 주식 종목 코드를 모두 다운로드 받아 일괄 작업하는 방법이 효율적이다.

KRX 정보데이터시스템 이동하기

KRX 정보데이터시스템 접속 및 종목코드 다운로드

한국거래소의 KOSPI, KOSDAQ 등 모든 거래소의 종목코드를 가져오기 위해서 먼저 KRX 정보데이터시스템 에 접속한다.

접속하여 왼쪽 메뉴의 "주식-종목정보-전종목 기본정보"를 클릭한다. 해당 화면으로 바뀌면 왼쪽 메뉴에서 "주식 - 종목정보 - 전종목 기본정보"를 선택하면 거래소의 주식에 대한 각 종 코드와 종목명, 상장일, 시장구분, 주식종류, 액면가, 상장주식수 등의 정보를 확인할 수 있는 내용이 보인다.

KOSPI 또는 KOSDAQ 등 필요한 시장의 정보만 조회하고 싶을 때는 시장구분에서 해당 항목을 선택하면 된다. 선택된 데이터를 다운로드 하기 위해서 오른쪽 상단의 다운로드 버튼을 눌러 엑셀형식으로 다운로드 받으면 된다.

KRX 정보데이터시스템 접속 및 주식 정보 다운로드

구글시트로 데이터 가공

다운로드 받은 엑셀파일은 그대로 구글시트에서 불러온다. 구글시트에서 엑셀 데이터를 불러오면 KRX 정보데이터시스템에서 다운로드 받을 때와 동일한 데이터가 표시된다.

불러온 엑셀파일은 구글시트 전용으로 저장학고, GOOGLEFINANCE 함수를 적용할 수 있도록 일부 가공을 해야된다.

원하는 데이터에 따라 필요한 데이터를 가공하여 사용하면 된다. 하지만, 추후 KRX 정보데이터시스템에서 종목이 추가되거나 삭제될 때를 대비해 VLOOKUP 함수를 이용하여 연동작업을 하면 원본데이터 변경 시 추가 또는 삭제 시 변경된 종목코드를 식별하기 편하다.

데이터의 가공순서는 다음의 순서대로 진행한다.

1. KRX에서 다운 받은 데이터가 있는 원본시트는 그대로 두고, 구글시트에 새로운 시트를 추가한다.
2. 새시트의 이름을 지정한 후, 원본시트에서 단축코드 전체를 복사하여 새로운 시트의 A열에 붙여 넣는다.
3. 종목코드를 기준으로 VLOOKUP 함수를 사용하여 필요한 데이트를 원본시트에서 가져온다.
4. 가져온 데이터에서 불필요한 부분을 삭제 등 정리한다. (만일, KRX 정보데이터시스템에서 전체데이터를 다운받았다면, KONEX 데이터 등은 필터 기능으로 일괄 삭제한다.)
5. GOOGLEFINANCE 함수로 주식정보를 가져올 수 있도록 종목코드를 GOOGLEFINANCE 함수의 ticker에 맞춰 가공한다.

원본데이터를 GOOGLEFINANCE 함수에서 사용 가능하도록 데이터 가공

그림의 번호에 적용되는 함수는 다음과 같다.

(1) VLOOKUP 함수로 종목명 가져오기

• =VLOOKUP(A:A,KRX_Data!B:D,3,FALSE)

(2) VLOOKUP 함수로 거래소 가져오기

• =VLOOKUP(A:A,KRX_Data!B:G,6,FALSE)

(3) IF 함수로 GOOGLEFINANCE 함수에 맞는 코드로 변형

• =if(C2="KOSDAQ","KOSDAQ",if(C2="KOSPI","KRX",if(C2="KOSDAQ GLOBAL","KOSDAQ","")))

(4) CONCATENATE 함수로 ticker로 변환

• =CONCATENATE(D2,":",A2)

가공된 데이터가 있는 시트에서 위의 함수를 사용하여 데이터를 입력하면, 원본데이터의 시트에 양식을 변경하지 않고 변경된 종목코드를 다시 다운받아 붙여 넣으면 가공된 데이터가 있는 시트의 함수가 자동으로 변경된다.

(3)에서 IF 함수를 이용하여 다운받은 데이터의 거래소 정보를 GOOGLEFINANCE 함수에 맞게 가공하기 위한 것이다.
KOSPI, KOSDAQ, KOSDAQ GLOBAL 등으로 표시되어 있는데, 그 중에서 KOSPI는 KRX로, KOSDAQ은 그대로 KOSDAQ으로, KOSDAQ GLOBAL은 KOSDAQ으로 변경하는 함수이다.

(4)에서 CONCATENATE 함수는 거래소 값과 단축코드를 결합하여 GOOGLEFINANCE 함수에서 사용가능한 ticker를 만든다. GOOGLEFINANCE에서 사용 가능한 ticker의 형태는 KOSPI는 "KRX:단축코드", KOSDAQ은 "KOSDAQ:단축코드"이다.

각각의 함수의 사용 방법은 Google Docs 편집기 고객센터 설명을 참조한다. 이동 후 검색에 함수명을 입력하여 함수의 사용방법을 확인하면 된다.

Google Docs 편집기 고객센터 이동하기

GOOGLEFINANCE 함수로 주가 정보 가져오기

기준이 되는 데이터를 가공했으면 이제 GOOGLEFINANCE 함수를 이용하여 필요한 정보를 불러오면 된다.

GOOGLEFINANCE 함수를 이용하여 주가정보를 가져오는 기본 내용은 이전 포스팅을 참고하면 된다.

GOOGLEFINANCE 함수 사용방법 포스팅 이동

아래 그림의 (1)~(6)에 해당하는 정보를 가져오는 함수는 아래와 같다.

가공된 데이터에 GOOGLEFINANCE 함수를 이용하여 주가정보 가져오기

그림의 각 항목에는 다음의 수식을 입력하면 된다.

(1) 시가총액(억원)

• =GOOGLEFINANCE(E2,"marketcap")/100000000

(2) 현재가(원)

• =GOOGLEFINANCE(E2,"price")

(3) 당일등락(%)

• =GOOGLEFINANCE(A2,"changepct")

(4) 52주 최고가(원)

• =GOOGLEFINANCE(E2,"high52")

(5) 52주 최고가(원)

• =GOOGLEFINANCE(E2,"low52")

(6) 52주 최저가 대비 현재가의 위치 비율

• =(G2-J2)/(I2-J2)*100

위와 같이 함수를 적용하면 시가총액, 현재가, 당일등락율, 52주 최고가, 52주 최저가, 52주 기준 현재가의 주가위치 비율 등을 확인할 수 있다.

(1)에서 시가총액은 기본수식을 적용하면 "원" 단위로 불러오지만, 보기 편하도록 100,000,000을 나누어 준다.

(6)에서 52주 최저가 기준에서 현재 주가의 비율을 확인하는 수식을 적용하여 52주 최저가 대비 현재 주가가 어느정도 위치하는지 확인 할 수 있다. 관련 내용은 이전 포스팅을 참조하면 된다. 

52주 최저가 대비 현재주가 비율 확인 방법 포스팅 이동

**** 데이터를 불러올 때 확인사항 ****

• KRX 정보데이터시스템에서 전체 주식종목의 코드를 다운받아서 처리하면 GOOGLEFINANCE 함수를 처리하는데 시간이 소요된다. (종목코드의 양이 많기 때문이다.)

• 함수를 입력하여 값을 불러올 때는 "로드중"으로 표시되는데 데이터 전체를 다운받으면 2,000종목 이상의 주식 종목 코드가 있어 모두 불러오기까지 시간이 필요하다.

• 또한, "#N/A"와 같이 표시되는 경우가 있는데, 이는 GOOGLEFINANCE가 불러올 데이터가 없는 경우에 표시된다.

• 그러나, 가끔 데이터가 있는 경우에도 불러오지 못하는 경우가 있으니 그대로 두면 다음 로딩에는 데이터를 가져오는 경우가 있으니 참고하시면 된다.

• 그리고, 거래정지 종목 등은 정확한 값을 불러오지 않는 경우가 있다

• 또한, 상장 후 기간이 얼마 되지 않는 종목도 일부 값들이 불러오지 않거나, 전체 값이 불러오지 않을 수 있다. 이런 경우는 그대로 두면 향후에 값을 가져올 수 있을때 값이 표시된다.

• KRX 정보데이터시스템에서 전체 주식종목코드를 다운받으면 KONEX 데이터 등도 불러오므로 구글시트의 필터 기능을 이용하여 불필요한 데이터를 삭제하고 사용하는 것이 좋다. 

• 불필요한 데이터가 많으면 불러오는 시간도 오래 걸리므로 데이터를 사용하는데 좋지 않다.

 
KRX 정보데이터시스템에서 주식종목 코드를 다운받아서 GOOGLEFINANCE 함수와 결합하여 여러가지 정보를 확인해 볼 수 있으므로, 본인의 투자방식에 따라 데이터를  본인만의 투자 데이터를 만들어 보는 것도 괜찮을 것 같다.