엑셀로 퀀트전략 백테스트 하는 법 – 코스피·코스닥 종목 다운로드부터 수익률 계산까지

앞서 소개한 퀀트 플랫폼의 경우 백테스트, 자동 투자 등 사용자가 전략만 잘 수립한다면 원하는 수익을 얻을 수 있게 잘 구현되어 있습니다.

다만, 초보자로써 플랫폼을 제대로 사용하기 위해서는 연간 40만~50만원 수준의 비용을 지불해야 한다는 부담감이 없을 수 없기에 과연 그 비용을 지불하는게 맞을지 에대한 의구심을 떨치기 어려워 퀀트 투자의 세계에 발을 들이기 어려워하시는 분이 많습니다.

하여, 간단하게 엑셀, 구글스프래드 시트를 활용하여 퀀트 투자의 효과에 대해 간단히 백테스트(Backtest) 할 수 있는 Tip을 이번 장에서 간단히 알려드리겠습니다.

 

목  차

1. 코스피·코스닥 전체 종목 엑셀로 받는 방법 2. 엑셀에서 전략 조건을 적용하기 3. 주가 데이터 수집 - 수동 & 자동 두 가지 방법 4. 수익률 계산 함수와 실전 적용법 5. 엑셀 백테스트 장점 vs 한계점

 

이번 글에서는 코스피·코스닥 종목 전체를 다운로드하는 방법, 수익률 계산, 전략 테스트까지 단계별로 알려드리겠습니다.

복잡한 프로그래밍 없이 매우 간단한 엑셀 함수만으로도 퀀트 전략을 검증할 수 있다는 점 꼭 기억하세요.

1. 코스피·코스닥 전체 종목 엑셀로 받는 방법

가장 먼저 해야 할 일은 전체 종목 리스트를 엑셀로 정리하는 것입니다.
네이버 금융에서 아래 순서대로 따라해 주세요.

1. 네이버금융 (https://finance.naver.com) 접속
2. 상단 메뉴에서 ‘증시 → 국내증시 → 시가총액’ 클릭
3. 우측 상단에 있는 ‘코스피 / 코스닥’ 선택
4. 다운로드 받고자 하는 지표 6개까지 선택하여 적용하기
5. 1페이지당 50개씩 나오는 종목을 복사해 엑셀에 붙여넣기
6. 페이지 넘기면서 반복하면 코스피 또는 코스닥 전체 다운로드 가능

https://finance.naver.com

 

2. 엑셀에서 전략 조건을 적용해보기

이제 받은 데이터에 퀀트 조건을 걸어봅시다. 예를 들어, 다음과 같은 조건을 설정할 수 있습니다.

• PER 10 이하
• PBR 1 이하
• ROE 10% 이상
• 부채비율 100% 이하

제 블로그 '엑셀로 퀀트 전략 만들기-비프로그래머용 기초편'에서 설명드렸던 방식대로 위에 각 지표의 조건별로

참이면 1점, 거짓이면 0점으로 함수를 만들어 줍니다.

함수는 '엑셀로 퀀트 전략 만들기-비프로그매어용 기초편'을 참고해주시면 됩니다.

4가지 조건에 대한 점수를 종합점수로 표현한 후 엑셀 홈-정렬 및 필터-숫자 내림차순 정렬 을 선택하여 아래와 같은 그림으로 정렬 되는 것을 보실 수 있습니다.

네이버금융에서 다운로드한 종목들에 대해 전략 지표들 조건의 총점이 높은 순서

 

3. 주가 데이터 수집 – 수동 & 자동 두 가지 방법

전략 조건으로 종목을 선별했다면 이제 과거 주가 데이터를 수집해야 합니다.

 

① 네이버 금융 수동 방식

• 종목명 검색 후 ‘차트’ 클릭
• 아래에 있는 ‘일별 시세’ 복사
• 60일~200일치 종가를 붙여넣기
• 날짜는 오름차순으로 정렬

② 구글 스프레드시트 자동 방식

• GOOGLEFINANCE() 함수 사용
  예시 :
  =GOOGLEFINANCE("KRX:005930", "close", DATE(2020,1,1), DATE(2022,12,31), "DAILY")

이 함수는 삼성전자 일별 종가를 자동으로 불러옵니다. 엑셀에는 직접 복사해서 붙이는 게 빠릅니다.
하지만 구글 스프레드시트는 자동화에 유리합니다.

 

4. 수익률 계산 함수와 실전 적용법

이제 매수가와 현재가를 바탕으로 수익률을 계산하는 단계입니다.

엑셀 수식은 매우 간단합니다.

 수익률 =(현재가 - 매수가) / 매수가

 

예를 들어, 매수가가 C2 셀, 현재가가 D2 셀이라면

 =(D2 - C2) / C2

수익률을 %로 보고 싶다면, 셀 서식을 퍼센트로 바꾸면 됩니다.

여러 종목을 동일 금액으로 투자했다면 수익률 평균을 구해서 전체 포트 성과를 알 수 있습니다.

수익률 = AVERAGE(E2:E6)

이런 방식으로 한 달, 세 달, n년 전략 성과를 쉽게 비교할 수 있습니다.

5. 엑셀 백테스트의 장점 vs 한계점

엑셀 백테스트는 직관적이고 배우기 쉽습니다. 데이터를 직접 보고 조건을 조절할 수 있기 때문입니다.

하지만 단점도 있습니다. 종목 수가 많아질수록 수작업이 많아지고 복잡한 조건 전략은 구현이 어렵습니다.

그래서 초보자는 엑셀로 퀀트 감각을 익히고 이후 퀀트 플랫폼(젠포트, 퀀트킹 등)이나 파이썬 같은 툴로 전환하면 좋습니다.

중요한 건 복잡한 시스템이 아니라 ‘조건 → 실행 → 검증’의 반복 루틴을 익히는 것입니다.

처음은 작게 시작하더라도 꾸준히 반복하면 나만의 전략이 점점 정교해 질 수 있습니다.