이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.
개요: 파이썬 없이도 되는 퀀트 백테스트
이 글은 구글시트만으로 퀀트 전략의 핵심 백테스트를 구현하는 실전 가이드입니다. 데이터 불러오기부터 월별 수익률 계산, 단순 이동평균 타이밍 전략과 상위 모멘텀 전략, 거래비용 반영, 성과지표 계산까지 순서대로 설명드립니다. 초보자분도 그대로 따라 하실 수 있도록 수식과 시트 구조를 구체적으로 안내합니다.
주의: 구글시트의 데이터는 제공 범위와 지연이 있을 수 있습니다. 본 글의 방법이 모든 종목·시장에 항상 정확히 동작한다고 단정할 수는 없으며, 오류나 누락 가능성이 있습니다.
왜 구글시트로 백테스트인가
- 무료이며 설치가 필요 없습니다.
- 공유가 쉬워 팀 협업과 검증이 편리합니다.
- GOOGLEFINANCE, QUERY, ARRAYFORMULA 등 강력한 함수로 반복 작업을 자동화할 수 있습니다.
- 한계도 있습니다: 큰 데이터의 속도 저하, 일부 거래소 미지원, 함수 버전에 따른 제약 가능성이 있습니다.
프로젝트 구조 설계
아래와 같이 시트를 나누시면 유지보수가 쉽습니다.
| 시트명 | 용도 | 주요 열 |
|---|---|---|
| Universe | 분석 대상 자산 목록 | Ticker, Name, Exchange, 통화 |
| Raw_US | 미국 종목 원천 일별 데이터 | Date, Close |
| Raw_KR | 국내 종목 원천 일별 데이터 또는 CSV 업로드 | Date, Close |
| Prices_M | 월말 종가 집계 | Month, 각 자산 월말 Close |
| Returns_M | 월간 수익률 | Month, 각 자산 월수익률 |
| Signals | 모멘텀 등 신호와 순위 | 12M 모멘텀, Rank, Weight |
| BT | 전략 수익률, 자본곡선, 위험지표 | Strategy R, Equity, MDD 등 |
데이터 불러오기
미국 주식·ETF 불러오기
Raw_US 시트 A1에 아래와 같이 입력합니다.
- 예시 SPY 종가 일별
=GOOGLEFINANCE("SPY","close",DATE(2010,1,1),TODAY(),"DAILY")출력은 보통 A열 Date, B열 Close로 생성됩니다.
국내 주식·ETF 불러오기
- GOOGLEFINANCE가 KRX를 지원하지 않는 경우가 있습니다. 이때는 다음 중 하나를 사용하실 수 있습니다.
- IMPORTHTML로 제공되는 웹의 표를 가져오기. 예시는 네이버 금융의 일별시세 표이며, 페이지별 병합이 필요할 수 있습니다.
=IMPORTHTML("https://finance.naver.com/item/sise_day.nhn?code=005930","table",1)- 또는 증권사/포털에서 CSV를 내려받아 Raw_KR 시트에 붙여넣기합니다.
웹 소스 구조 변경, 호출 제한 등으로 수집이 실패할 수 있으니, 장기 운용용으로는 CSV 업로드가 안정적일 수 있습니다.
환율 데이터가 필요할 때
달러 기준 자산을 원화로 환산하려면 USDKRW를 추가합니다.
=GOOGLEFINANCE("CURRENCY:USDKRW","close",DATE(2010,1,1),TODAY())월말 종가와 월간 수익률 만들기
Raw_US의 A열 날짜, B열 종가를 월말 종가로 변환합니다. Prices_M 시트에서 아래를 따릅니다.
- 월 키 만들기
Prices_M!A2: =UNIQUE(TEXT(Raw_US!A2:A,"yyyy-mm"))- 월말 종가 추출
Prices_M!B1: SPY_Close_MPrices_M!B2: =ARRAYFORMULA(IF(A2:A="","",LOOKUP(2,1/(TEXT(Raw_US!A2:A,"yyyy-mm")=A2:A),Raw_US!B2:B)))- 월간 수익률 계산
Returns_M!A2: =Prices_M!A2:AReturns_M!B1: SPY_R_MReturns_M!B2: =ARRAYFORMULA(IF(Prices_M!B2:B="","",IF(ROW(Prices_M!B2:B)=ROW(Prices_M!B2),,Prices_M!B2:B/INDEX(Prices_M!B2:B,ROW(Prices_M!B2:B)-1)-1)))다른 자산도 같은 방식으로 열을 하나씩 추가해 확장합니다.
전략 A: 10개월 이동평균 타이밍 (월별)
월말 종가 기준으로 10개월 단순 이동평균을 사용해 보겠습니다. 예시는 SPY 보유, 신호가 꺼지면 BIL(미국 단기국채 ETF)을 보유하는 방식입니다.
- 10개월 이동평균
Prices_M 시트에서 SPY 월말 종가가 B열이라고 가정합니다. 11번째 행부터 아래로 드래그하는 수식을 권장합니다.
Prices_M!C11: =AVERAGE(B2:B11) 이후 C열을 아래로 드래그- 신호 생성
Signals!B11: =IF(Prices_M!B11>Prices_M!C11,1,0) 이후 아래로 드래그- BIL 월간 수익률 준비
Raw_US에 BIL도 불러와 동일 절차로 Returns_M에 BIL_R_M 열을 만듭니다.
- 전략 월간 수익률
신호는 현재 월 말에 확정되므로, 실제 투자 수익은 다음 달에 반영해야 시점오염을 피할 수 있습니다.
BT!B12: =IF(Signals!B11=1, Returns_M!SPY_R_M!B12, Returns_M!BIL_R_M!D12)위 수식에서 열 참조는 각자의 시트 구조에 맞게 조정하십시오. 핵심은 신호의 t월 값으로 t+1월 수익을 선택한다는 점입니다.
- 누적수익과 최대낙폭
BT!C11: =1 시작자본 1BT!C12: =(1+BT!B12)*C11 이후 아래로 드래그BT!D11: =1 누적고점BT!D12: =MAX(D11,C12) 이후 아래로 드래그BT!E12: =C12/D12-1 (월별 낙폭)전략 B: 상위 2개 ETF 모멘텀 (12-1)
SPY, EFA, AGG 세 자산을 예시로 합니다. 각 자산의 월말 종가가 Prices_M의 B, C, D열이라고 가정합니다.
- 12-1 모멘텀 계산
Signals 시트 13행부터 입력 후 아래로 드래그합니다. t-1 대비 t-12 가격을 사용해 최근 1개월은 제외합니다.
Signals!B13: =IFERROR(Prices_M!B12/Prices_M!B1-1,"")Signals!C13: =IFERROR(Prices_M!C12/Prices_M!C1-1,"")Signals!D13: =IFERROR(Prices_M!D12/Prices_M!D1-1,"")- 행 단위 순위와 가중치
Signals!E13: =RANK(B13,$B13:$D13,0)Signals!F13: =RANK(C13,$B13:$D13,0)Signals!G13: =RANK(D13,$B13:$D13,0)Signals!B13: 가중치 =IF(E13<=2,0.5,0)Signals!C13: 가중치 =IF(F13<=2,0.5,0)Signals!D13: 가중치 =IF(G13<=2,0.5,0)가중치는 상위 2개를 동등 가중합니다.
- 다음 달 수익 반영
Returns_M 시트의 동일 행 기준으로 t+1월 수익을 가져옵니다.
BT!B14: =SUMPRODUCT(Signals!B13:D13, Returns_M!B14:D14)이후 아래로 드래그합니다.
- 거래비용 반영(선택)
월간 리밸런싱 거래비용률을 cell BT!H1에 0.001과 같이 둡니다(왕복 0.1% 가정 등, 각자 가정). 월간 회전율은 가중치 변화 합의 절반으로 근사할 수 있습니다.
BT!F14: =SUM(ABS(Signals!B13:D13-Signals!B12:D12))/2BT!G14: =BT!B14 - BT!F14*$H$1G열을 전략의 최종 월간 수익률로 사용합니다.
성과지표 계산 예시
- 기간(년)
BT!N2: =COUNTA(BT!G12:G)/12- CAGR
BT!N3: =POWER(INDEX(BT!C:C,MAX(FILTER(ROW(BT!C:C),BT!C:C<>"")))/BT!C11,1/BT!N2)-1- 연환산 변동성
BT!N4: =STDEV.S(BT!G12:G)*SQRT(12)- 최대낙폭
BT!N5: =MIN(BT!E12:E)- 승률
BT!N6: =COUNTIF(BT!G12:G,">0")/COUNTA(BT!G12:G)위 수식은 예시이며, 각 시트의 실제 열 위치에 맞춰 조정하셔야 합니다.
검증 체크리스트와 흔한 함정
- 시점오염 방지: t월 신호로 t+1월 수익을 계산합니다.
- 생존자 편향: 상장폐지 종목이 제외되면 결과가 왜곡될 수 있습니다. 가능하면 당시 유니버스를 고정해 검증하는 방법을 고려하십시오.
- 데이터 품질: GOOGLEFINANCE와 IMPORTHTML은 지연·변경 가능성이 있습니다. 표본을 수시로 스팟 체크하시길 권장드립니다.
- 거래비용과 슬리피지: 고정 비용률만 적용해도 실제와 차이가 줄어들 가능성이 있습니다.
- 통화 효과: 원화 기준 성과를 보려면 환헤지 여부에 따라 환율 반영이 필요합니다.
자동화와 공유 팁
- 이름 정의로 범위를 관리하면 수식 가독성이 좋아집니다.
- 필요 시 보호 범위를 설정해 원시 데이터가 변경되지 않도록 합니다.
- 대시보드 시트를 별도로 만들어 성과지표와 그래프를 요약하면 보고가 편리합니다.
마무리
이 글의 흐름대로 시트를 구성하면, 파이썬 없이도 월별 기준의 대표 퀀트 전략을 충분히 백테스트하실 수 있습니다. 세부 수식과 구조는 유니버스와 리밸런싱 주기에 맞춰 조정해 보시길 권합니다.
다음 글에서는 구글시트만으로 구현하는 자산배분 최적화의 기초를 다루겠습니다. 구독 부탁드립니다.
투자 관련 유의사항
본 글은 교육 및 정보 제공 목적이며, 특정 종목이나 전략을 추천하거나 투자 참여를 유도하는 것이 아닙니다. 모든 투자 판단과 이에 따른 손실 책임은 전적으로 투자자 본인에게 있습니다. 과거의 수익률이 미래 성과를 보장하지 않습니다.
