구글 스프레드시트로 퀀트 투자하는 방법 : Query 함수의 세계로 빠져들자

퀀트 투자..? 나도 할 수 있을까...?

 

 

구글 스프레드시트로 퀀트 투자하기!

 

 

 

제가 생각하는 퀀트 투자란 "주식 데이터를 수집한 후 필요 데이터를 추출하여 투자에 유의미한 정보(숫자)를 찾는 행위"라 생각합니다. 결국 데이터를 다루는 툴은 툴일 뿐, 얼마나 유의미한 정보를 잘 찾을 수 있는지가 중요합니다.

 

(투자 판단을 위한 의미 있는 숫자 찾기!)

 

 

 

그중에 오늘 소개할 데이터 툴은 초보자도 쉽게 사용할 수 있는 구글 스프레드시트입니다. 비록 파이썬 / R 언어는 아니지만, 엑셀만 다룰 수 있다면 누구나 구글 스프레드시트로 퀀트 투자를 할 수 있으리라 믿습니다.

 

 

 

이제 데이터 수집부터 쿼리 함수를 활용한 데이터 추출까지 예제들로 간단히 설명하고자 합니다. 저 또한 스터디 차원에서 남기는 포스팅이오니, 잠시나마 퀀트 투자의 "맛보기"를 함께 느꼈으면 합니다.

 

 

 

※ 기초적인 내용만을 포함하고 있으니 구글링을 통해 심화 과정까지 빠져들었으면 좋겠습니다.

 

 

 

 

 

 

기초 주식 데이터 수집하기

 

 

먼저 예제로 활용할 데이터를 수집하기 위해 네이버 금융에 들어가 보도록 합시다.

 

(네이버 금융 → 국내 증시 → 시가총액)

 

https://finance.naver.com/sise/sise_market_sum.nhn?&page=1 

 

 

 

코스피 상장사

 

 

IMPORTHTML 함수를 사용하면 네이버 금융에 있는 기본적인 데이터를 가져올 수 있습니다. (코스피 상장사)

 

 

=IMPORTHTML("URL 주소", "TABLE", 테이블 번호)

 

 

 

 

 

=IMPORTHTML("http://finance.naver.com/sise/sise_market_sum.nhn?&page=1", "table", 2)

 

 

위 사진과 같이 구글 스프레드시트에서 코스피 금융 데이터를 손쉽게 가져왔습니다.

 

 

 

 

 

1 페이지에는 총 50개 기업만 있습니다. 2페이지, 3페이지... 기업들을 모두 가져오기 위해서는 아래와 같이 함수를 복사/붙여넣기를 합니다.

 

 

=IMPORTHTML("http://finance.naver.com/sise/sise_market_sum.nhn?&page=2", "table", 2)

 

=IMPORTHTML("http://finance.naver.com/sise/sise_market_sum.nhn?&page=3", "table", 2)

 

=IMPORTHTML("http://finance.naver.com/sise/sise_market_sum.nhn?&page=4", "table" ,2)

 

.....

 

 

 

이제 기본적으로 사용할 데이터 세팅은 끝났습니다. 예제로 활용될 데이터는 아래 링크를 통해 다운로드 받을 수 있습니다.

 

("파일 → 사본 만들기"로 다운로드 가능)

 

https://docs.google.com/spreadsheets/d/15WG-LsIR4Gc4sGGjnnFEakQQq53bpxpJRJT7TLQLQpc/edit?usp=sharing 

 

 

 

 

 

 

마법의 함수 Query 사용하기

 

 

쿼리란 간단히 설명하자면, 특정 조건에 맞는 데이터만 추출할 수 있는 함수입니다. 쿼리 기능은 아래 함수 구문으로 사용할 수 있습니다. 

 

=QUERY(데이터 범위, 쿼리, 헤더)

 

 

 

복잡하게 생각할 필요는 전혀 없으니, 쿼리 사용 예제들을 통해 구글 스프레드시트를 활용한 퀀트 투자의 세계로 빠져봅시다. 자세한 사용법은 아래 구글 querylanguage를 참고하시기 바랍니다.

 

https://developers.google.com/chart/interactive/docs/querylanguage

 

 

 

 

 

 

 

 

예제 1. SELECT 절을 활용하기

 

 

SELECT 절을 활용하면 필요한 열의 데이터를 가져올 수 있습니다. 먼저 위에서 가져온 코스피 상장사 데이터를 F1:R3000에 입력하였습니다.

 

 

 

 

SELECT 절을 활용하여 종목명(G열), 현재가(H열), 시가총액(L열) 열만 가져오도록 하겠습니다.

 

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, L")

 

 

종목명(G열), 현재가(H열), 시가총액(L열) 순서로 데이터만 추출 완료하였습니다.

 

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, L", 1)

 

 

검색 범위(데이터)의 첫 행을 헤더로 반영하고 싶으면 함수 구문 마지막에 "1"을 입력하시면 됩니다.

 

 

 

 

 

 

예제 2. SELECT 절 활용 열 순서 바꾸기

 

 

SELECT 절에서는 열 순서를 바꾸어 데이터 추출도 가능합니다.

 

 

 

 

=QUERY(F1:R3000, "SELECT H, L, G")

 

 

SELECT 뒤에 열 입력 순(현재가, 시가총액, 종목명)으로 데이터를 추출하였습니다.

 

 

 

 

 

 

예제 3. SELECT 절 + WHERE 절을 사용하여 시가총액 10조 이상 기업 추출하기

 

 

SELECT 절과 WHERE 절을 활용하여 시가총액 10조 이상 기업만 추출하도록 하겠습니다. (예제 1 응용)

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, L WHERE L >= 100000")

 

 

WHERE 절에 시가총액(L열) 10조 이상 수식만 추가한다면, 총 43개 기업을 손쉽게 가져올 수 있습니다.

 

 

 

 

 

 

예제 4. 시가총액 10조 이상, PER 10 이하, ROE 10 이상 기업 추출하기

 

 

"예제 3"을 응용하여 시가총액 10조 이상 기업 중 PER 10 이하, ROE 10 이상 기업을 가져오도록 하겠습니다. 기본적인 함수 구문은 동일하며 AND 조건 사용하면 됩니다.

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, L WHERE L>=100000 AND P<=10 AND Q>=10")

 

 

1) 시가총액(L열)이 10조 이상이면서,

2) PER(P열)이 10 이하이면서,

3) ROE(Q열)이 10 이상인,

 

 

기업의 종목명(G열), 현재가(H열), 시가총액(L열) 데이터를 추출하였습니다.

 

 

 

 

 

 

예시 5. 외국인 비율이 60% 이상이면서 PER 20 이하 또는 ROE 10 이상 기업 추출하기

 

 

AND 조건과 OR 조건을 다중 조건으로 활용하여 위 조건에 맞는 기업만을 추출하겠습니다.

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H WHERE N>=60 AND (P<=20 OR Q>=10)")

 

 

1) 외국인 비율(N열)이 60% 이상이면서,

2) PER(P열)이 20 이하 또는 ROE(Q열)이 10 이상인,

 

 

기업의 종목명(G열), 현재가(H열)를 추출하였습니다. 

 

 

 

 

 

 

예제 6. 오름차순 / 내림차순으로 정렬하기

 

 

데이터 추출 결과를 깔끔하게 정렬하기 위해선 ORDER BY 절을 사용하면 됩니다. "예제 5"를 활용하여 오름차순, 내림차순으로 정리해보도록 하겠습니다.

 

 

현재가 오름차순

 

 

=QUERY(F1:R3000, "SELECT G, H WHERE N>=60 AND (P<=20 OR Q>=10) ORDER BY H")

 

또는 

 

=QUERY(F1:R3000, "SELECT G, H WHERE N>=60 AND (P<=20 OR Q>=10) ORDER BY H ASC")

 

 

"예제 5"에서 추출한 데이터에서 현재가(H열) 오름차순으로 정렬하였습니다. ORDER BY H 절을 추가하면 됩니다.

(오름차순 = Ascending)

 

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H WHERE N>=60 AND (P<=20 OR Q>=10) ORDER BY H DESC")

 

 

반대로 현재가 내림차순 정렬은 ORDER BY H DESC 절을 입력하면 됩니다.

(내림차순 = Descending)

 

 

 

 

 

 

예제 7. 데이터 추출 수 지정하기

 

 

데이터를 원하는 수량(행)만큼만 추출하고 싶다면 LIMIT 절을 사용하면 됩니다. (예제 6 활용)

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H WHERE N>=60 AND (P<=20 OR Q>=10) ORDER BY H LIMIT 5")

 

 

"예제 6"에서 추출한 데이터 수(행)는 총 17개 행이었습니다. 이때 LIMIT 5를 입력하면 총 5개 행만 추출할 수 있습니다.

 

 

 

 

 

 

예제 8. 헤더(HEADER) 이름 바꾸기

 

 

추출한 데이터의 헤더 이름은 LABEL 문으로 쉽게 바꿀 수 있습니다. 먼저 SELECT 문을 사용하여 종목명, 현재가, PER 열을 가져오도록 하겠습니다.

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, P", 1)

 

 

SELECT 문을 활용하여 종목명(G열), 현재가(H열), PER(P열)을 가져왔습니다. 함수 구문 마지막 "1"은 기존 데이터 헤더를 그대로 반영할 경우에 사용하시면 됩니다.

 

 

 

헤더 이름을 아래와 같이 바꿔서 데이터를 추출하고 싶다면 어떻게 하면 될까요?

 

1) 종목명 → 기업 

2) 현재가 → 주가 

3) PER → PER(배)

 

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, P LABEL G '기업', H '주가', P 'PER(배)'", 1)

 

 

LABEL 절을 사용하여 아래와 같이 헤더 이름을 변경할 수 있었습니다.

 

 

1) 종목명(G열) → 기업

2) 현재가(H열) → 주가 

3) PER(P열) → PER(배)

 

 

 

 

 

 

예제 9. 현대 계열사 찾기

 

 

CONTAINS 절을 사용하면 현대 계열사 기업들의 데이터를 추출할 수 있습니다.

 

(종목명에서 "현대" 문구가 포함된 기업을 현대 계열사로 가정)

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, P WHERE G CONTAINS '현대'", 1)

 

 

WHERE 절을 통해 종목명 G열을 지정하고, CONTAINS 절을 추가하면 됩니다. 종목명에 "현대"가 포함된 데이터는 모두 추출된 것을 확인할 수 있습니다.

 

 

 

만약 "HDC현대EP, HDC현대산업개발"처럼 중간에 "현대"가 포함된 종목이 아닌, "현대"로 시작하는 종목만 추출하고 싶다면 STARTS WITH 절을 사용하면 됩니다.

 

 

 

 

=QUERY(F1:R3000, "SELECT G, H, P WHERE G STARTS WITH '현대'", 1)

 

 

STARTS WITH '현대'를 추가함으로 "현대"로 시작하는 종목만 가져올 수 있었습니다.

 

 

 

 

이제 걸음마 단계 끝!

 

 

구글 스프레드시트에서 QUERY 함수를 활용한 "퀀트 투자 맛보기"가 끝났습니다. 내가 가장 즐겨 쓰는 구문들만 예제로 만들었으니, 추가적인 내용은 구글링을 통해 셀프 스터디하시길 추천드립니다. 무림의 고수들을 얼마든지 만날 수 있을 것입니다.

 

 

감사합니다.

 

 

 

 

 

 

참고 글 1 : 구글 스프레드시트를 활용하여 주식 포트폴리오 관리하기

 

마지막, 주식 포트폴리오 양식 통합본 공유 (Full Version, 구글 스프레드시트)

 

 

 

참고 글 2 : 구글 스프레드시트를 활용하여 S-RIM 적정주가 구하기

 

마지막, S-RIM 적정주가 계산기 통합본 공유 (구글 스프레드시트)