백테스팅 구축 (15) - 엑셀로 분석할 데이터 제작하기

이번 게시글에서는 엑셀에 입력된 데이터를 바탕으로 결과값들을 분석하는 방법에 대해 살펴볼 예정이다. 엑셀의 경우에는 vlookup이나 count, if 등의 다양한 함수들이 존재하지만 사실 우리가 사용할 함수는 몇 개 없다. 왜냐하면 단순하게 그 결과값을 바탕으로 가불가의 여부만 판단하는 것으로 족하기 때문이다. 함수만 간단하게 알아보고 결과값들을 분석해보도록 하겠다.

 

엑셀의 대표적인 함수

사실 가장 많이 사용되는 함수들이 있긴 하지만, 그건 논외로 하고 우리는 가장 많이 사용할 함수만 익히고 넘어가자.

• if 함수 : 조건문을 바탕으로 True와 False를 판단, 그 반환값을 바탕으로 출력값 설정 가능
• count 함수 : 특정 조건에 맞는 셀의 개수를 세어주는 함수. counta, countif, countblank 등이 있음
• and, or 함수 : 몇 가지 조건을 설정할 때 사용하는 함수 

분석의 대상이 될 거래 종목의 주가 데이터

 

부수적인 데이터 구축하기 - 정배열과 역배열

특정 셀의 값이 0보다 크면 수익이고 0보다 작으면 손실이라는 결과값을 입력하는 if문의 경우에는 지난 게시글에서 간략하게 살펴봤으니 제외하도록 하고, 이번에는 이동평균선들의 정배열과 역배열을 판단해보도록 하겠다. 이번에도 역시 마찬가지로 if문을 사용함으로써 해결할 수 있다.

일단 우리는 매수 시점의 이동평균선 값들을 모두 얻어왔고, 그를 엑셀 파일 안에 저장해주었다. 그렇기 때문에 그 데이터를 바탕으로 대소 비교만 진행하면 이동평균선의 배열이 정배열인지 또는 역배열인지를 확인할 수 있는데, 그 식은 아래와 같다.

• if(조건, True시 반환값, False시 반환값

다만 AND문의 경우에는 파이썬과 다른 부분이 있다면 파이썬에서는 A and B와 같은 형태로 작성했었지만 엑셀에서는 AND(A, B)와 같은 형태로 작성해야 한다는 것이다. 이는 OR문도 마찬가지로, A 또는 B를 나타내고 싶다면 OR(A, B)를 입력해야 한다. 이 내용을 기반으로 조건문을 제작해보자면, 셀 번호 J2에 위치한 매수 시점의 5일 이동평균선값, 셀 번호 K2에 위치핸 매수 시점의 10일 이동평균선값, L2에 위치한 매수 시점의 20일 이동평균선 값 등등을 바탕으로 AND(J2 >= K2, K2>=L2)처럼 제작해서 대소 비교를 진행하고, 모든 조건이 충족될 경우(반환값이 True)일 경우 "정배열"을 나타내도록 한 것이다. 매도의 경우에도 마찬가지이기 때문에 셀 번호만 바꾸고 나머지 조건이나 형태는 동일하게 제작해주면 된다.

매수 시점의 이동평균선 비교

매도 시점의 이동평균선 비교

 

 

부수적인 데이터 구축하기 - 이격도

이격도는 주가가 이동평균선과 얼만큼 많이 떨어져있는가를 나타내는 값으로, 주가 나누기 이동평균선값 곱하기 100으로 계산한다. 그렇기 때문에 주가와 이동평균선 값이 동일하다면 결과값은 100이 나오게 되며 만약 100보다 크면 해당 이동평균선보다 주가가 높은 위치에, 100보다 작으면 해당 이동평균선보다 주가가 낮은 위치에 놓여 있다는 것을 확인할 수 있는 정보다. 

아래의 사진에서 확인할 수 있듯이, buy_price 값인 D2(292500)을 매수 시점의 5일 이동평균선 값인 J2(286500)으로 나눈 후에 100을 곱해서 계산한다는 것을 알 수 있다. 이 이격도는 왜 계산하는가 하는 의문이 들 수도 있는데, 사실 이격도는 주가가 상승할 때 얼마나 강한 힘으로 상승하기 시작했는지를 확인할 수 있는 좋은 지표 중 하나이기도 하다.

 

 

---

728x90

---

 

 

보기 좋게 꾸며주기

 

이후에는 해당 값이 매수 시점의 값인지 매도 시점의 값인지 확인하기 위해 '매수'라고 표시할 부분을 드래그한 후에 [병합하고 가운데 맞춤]을 눌러 합쳐주고, 오른쪽에는 매도 시점의 값임을 표시하기 위해 똑같이 설정해주자. 그 후에 매수 시점의 정배열 여부와 5일 이동평균선과의 이격도, 10일, 20일 등의 값들을 입력해준 후에, 이전에 했던 것처럼 표의 맨 오른쪽에 있는 점을 눌러서 아래로 쭉 드래그 해주면 결과값이 자동으로 채워지게 된다.

셀 병합 및 자동 채우기

 

결과값

 

만약 수익이 발생한 경우와 손실이 발생한 경우, 어떤 차이로 인해 이러한 결과가 나오게 되었는지를 분석하고자 할 때 '나는 이 정보도 궁금해!'하는 경우에는 추가로 그 데이터를 제작해주면 된다. 다음 포스팅에서는 위의 분석 결과 자료들을 매수 전략과 매도 전략을 약간 수정할 계획이다.

[추신] #DIV/0!라고 표시되는 부분은 데이터가 없어서 계산에 실패한 경우 출력되는 값이다. 즉, 아직 종목을 매수하긴 했으나 매도를 안했기 떄문에 매도 시점의 이동평균선 값이 입력되지 않았고, 그에 따라 계산할 대상이 되는 값이 없기 때문에 출력된다. 

 

 

---