[구글 스프레드시트] 중국 주식 가격 계산 (feat. IMPORTHTML 함수)

지난 10월에 포스트 했던 글(구글 파이낸스 함수 사용법)에서 상하이 등 중국 본토 주식 정보는 구글 파이낸스 함수로 가져올 수 없다고 알려드렸는데요,

오늘은 구글 스프레드시트에서 제공하는 또 다른 함수인 IMPORTHTML 함수를 통해 중국 본토 거래소(상하이, 심천)의 주식 정보를 가져오는 방법에 대해 알아보겠습니다. 

우선 IMPORTHTML 함수에 대해 간단히 정리해보겠습니다. 이 함수는 특정 웹페이지(HTML 페이지)에서 원하는 '표' 또는 '목록' 정보를 가져오는 함수입니다. 

IMPORTHTML(URL, query, index)

• URL: 정보를 가져올 대상이 되는 웹 페이지의 URL
• query: 웹 페이지에서 가져올 정보의 종류를 가리키며 표(table) 또는 목록(list) 중 지정 가능
• index: 웹 페이지에서 어떤 표 또는 목록을 가져올지 지정하는 인덱스 (1부터 시작)

예를 들어, 아래와 같이 IMPORTHTML 함수를 작성했다면,

IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India","table",4)

http://en.wikipedia.org/wiki/Demographics_of_India 웹 페이지에서 앞에서 네 번째 위치의 '표' 정보를 가져온다는 의미가 됩니다.

이제 이 함수를 활용하여 아래의 절차로 구글 스프레드시트에서 중국 주식 정보를 구해보겠습니다.

1. 중국(상하이, 심천) 거래소에서 거래되는 종목 정보를 제공하는 웹 페이지를 찾는다.
2. IMPORTHTML 함수를 사용해서 웹 페이지에서 종목 정보를 가져온다.
3. 추출된 종목 정보 중 내가 원하는 정보(예를 들어 주가)를 추출한다.

 

1. 중국 거래소 종목 정보를 제공하는 웹 페이지

중국 거래소 종목 정보를 제공하는 웹 사이트가 많이 있겠지만, www.aastocks.com가 영어가 지원되고 많은 분들이 이용하는 것 같습니다.

www.aastocks.com에 접속 후 오른쪽 위 종목 검색 창에서 원하는 종목의 종목 코드를 적고 검색합니다. 이 포스트에서는 귀주모태주(종목코드: 600519)를 예제로 검색해보겠습니다.

종목을 검색하면 아래와 같이 주가 등 종목 정보가 조회됩니다. 

aastocks.com에서 600519(귀주모태주 종목코드) 검색 결과

이제 이 웹 페이지의 URL(www.aastocks.com/en/cnhk/quote/detail-quote.aspx?shsymbol=600519)를 복사해둡니다. 

 

2. IMPORTHTML 함수를 사용해서 웹 페이지에서 종목 정보를 가져오기

구글 스프레드시트에서 아래와 같이 IMPORTHTML를 작성하여 귀주모태주에 대한 종목 정보를 가져옵니다. 

IMPORTHTML 실행 결과

앞에서 복사한 URL을 바로 IMPORTHTML 함수에 넣지 않고, 다른 셀(위의 캡처에선 B2)에 저장한 후 IMPORTHTML 함수에서는 이 셀을 참조하도록 하였습니다.

표(table) 정보를 가져오도록 하였고, 16번째 표를 가져오도록 하였습니다. 이 웹 페이지의 소스보기를 하면 많은 html table 태그가 있는데요, 이 중에서 종목 정보를 포함하는 표가 바로 16번째 표(class="tblM s8 txt_l" id="tbQuote")이기 때문입니다. 

구글 스프레드시트(왼쪽)에 웹 페이지(오른쪽)의 종목 정보가 import된 것을 볼 수 있습니다.

구글 스프레드시트에 데이터를 가져왔으니 이제 여기서 원하는 정보만 추출하면 되겠습니다.

 

3. 구글 스프레드시트에서 원하는 정보만 추출하기

Import 된 데이터를 보면, 주가뿐만 아니라 변동폭, PER, EPS 등 다양한 정보가 포함되어 있습니다. 이들 각각도 추출해낼 수 있지만, 우선 주가 정보를 추출하는 방법을 알아보겠습니다. 

추출된 표에서 주가 정보가 있는 셀은 표의 1열/1행이므로 이 정보만 참조하기 위해 index 함수를 사용합니다.

=INDEX(IMPORTHTML(B2,"table",16),1,1)

이렇게 하면 전체 데이터 중 주가 정보가 포함된 아래 셀만 참조하게 됩니다.

IMPORT된 표의 1열-1행 셀

그런데 이 셀에는 현재 주가뿐만 아니라 전일 종가(1,749.00), 변동 범위, 시작가가 모두 포함되어 있습니다. 이들을 구분하기 위해 SPLIT 함수를 쓰겠습니다.

=SPLIT(INDEX(IMPORTHTML(B2,"table",16),1,1), char(10))

SPLIT 함수는 특정 문자열을 원하는 구분자로 쪼개는 작업을 수행합니다. 위의 식은 주가 정보가 포함된 셀의 내용을 char(10)를 구분자로 하여 쪼갠다(split)는 의미입니다. char(10)은 유니코드 10에 해당하는 문자를 의미하며 유니코드 10은 개행 문자(LF), 쉽게 말하면 엔터키라고 보시면 됩니다.

SPLIT 함수의 수행 결과는 아래와 같습니다.

각 문장이 각기 다른 셀로 분리되었습니다. 이 중에 현재 주가가 있는 네 번째 셀( *1,793.11*)을 참조하기 위해 다시 INDEX 함수를 사용하겠습니다.

=INDEX(SPLIT(INDEX(IMPORTHTML(B2,"table",16),1,1), char(10)), 1, 4)

이제 여기서 앞 뒤의 * 기호를 빼고 숫자 부분만 추출하면 됩니다. 다시 SPLIT 함수를 적용하여 최종적으로 주가 정보를 얻습니다. SPLIT 함수는 문자열을 쪼개면서 기본적으로 구분자를 삭제하기 때문에 * 기호를 구분자로 하면 * 기호가 없어지고 주가 정보만 남게 됩니다. (구분자를 삭제하지 않는 옵션도 가능합니다. 여기서는 불필요한 옵션이기 때문에 생략함.)

최종적으로 아래의 식이 aastocks.com의 웹페이지 정보를 활용한 중국 주식 가격을 구하는 식입니다. (B2 부분만 aastocks.com에서 특정 종목의 URL로 바꾸면 됩니다.)

=SPLIT(INDEX(SPLIT(INDEX(IMPORTHTML(B2,"table",16),1,1), char(10)), 1, 4), "*")

※주의: aastocks.com의 웹페이지 구조가 바뀔 경우, 위의 함수가 제대로 작동하지 않을 수 있습니다.
오늘 (20년 12월 6일) 기준으로는 정상 작동하고 있습니다.

 

지금까지 IMPORTHTML 함수를 이용한 주가 정보 구하는 방법을 살펴보았습니다. 

위의 예제에서는 주가 정보만 구해봤지만, IMPORTHTML 함수를 통해 주가 외에도 PER, EPS, 배당률 등의 정보도 같이 가져왔기 때문에 INDEX 함수와 SPLIT 함수를 적절히 조합하면 이들 정보 등도 추출이 가능합니다.

 

마지막으로 중국 주식의 종목 코드 구하는 방법을 공유드립니다.

인베스팅닷컴(https://kr.investing.com)에 접속해서 시장 → 주식 → 주식 종목검색기 메뉴로 이동합니다.

국가를 중국으로 선택하고 거래소(상하이 또는 심천)를 선택하면 해당 거래소의 종목들이 검색됩니다. 아래 캡처에서는 상하이 거래소 종목을 시가 총액 기준으로 검색하였는데, 2020년 12월 6일 현재 귀주모태주가 시가 총액 1위네요.

     이제 중국 주식 투자에도 구글 스프레드시트를 활용해 보세요~