백테스트에 필요한 엑셀 Vlookup 함수 활용

엑셀에서 Vlookup 수식을 활용하여 다른 시트에서 동일한 이름의 다른 항목을 추출하는 방법입니다. 

엑셀 기능에 관해서는 이미 잘 정리된 블로그들이 많으니까, 저는 엑셀의 기능에 중점을 두기보다 제가 주로 하는 작업에 필요한 기능들만 정리해서 올리려 합니다. 엑셀의 모든 함수를 다 알면 좋겠지만, 그럴 수는 없으니 꼭 필요한 함수만 알아두면 좋을 것 같습니다.

 

일단 저는 자산관리와, 퀀트투자에 관심이 많은 1인입니다. 자산관리를 위해서는 엑셀 가계부를 활용하며, 퀀트 투자 시에는 백테스트를 해야 하기 때문에 엑셀을 사용합니다. 이에 자산관리와, 백테스트 영역에서 쓰이는 엑셀 함수를 집중해서 다뤄보려 합니다.

 

백테스트에-필요한-Vlookup함수

 

Vlookup 함수란?

Vlookup 함수는 엑셀 시트내에서, 혹은 다른 시트에서 사용자가 원하는 정보를 찾고, 그걸 반환해주는 (셀에 뿌려주는) 함수입니다. 백테스트를 하려면 데이터가 필요한데, 완벽히 우리가 원하는 데이터가 모조리 들어가 있는 엑셀을 모으는 게 쉬운 일이 아닙니다. 그리고 다운로드할 수 있는 데이터마다 기입되어있는 항목이 다를 수 있습니다. 그래서 Sheet1에서 Sheet2에 있는 동일 기업의 다른 팩터를 모으는데 Vlookup 함수를 사용합니다. 

 

백테스트시 Vlookup 함수 사용 설명

엑셀-Vlookup함수-활용

 

Sheet1에서 3S 라는 기업의 소속부가 뭔지 알고 싶은데, Sheet2에 모든 기업의 소속부 기록이 있습니다. 이런 상황일 때, Sheet2에 있는 소속부 자료를 Sheet1에 기록된 기업과 매칭 해서 자동으로 끌어올 수 있습니다. 이럴 때 저는 Vlookup 함수를 사용합니다. 이런 상황일 때, 잠깐 생각해보면 "그냥 열을 복사/붙여넣기 하면 되지 뭣하러 함수까지 사용하지?"라는 의문이 들수도 있습니다. 

 

그냥 복사/붙여넣기하지 않는 이유는, 3S 밑으로 2000여 개의 기업들이 있는데, 이들의 순서와, Sheet2에 있는 기업의 순서와 개수가 다르기 때문입니다. 그래서 열을 붙여 넣기 할 수가 없는 상황입니다. 그렇다고 2000개가 넘는 기업을 하나하나 대조해가면서 붙여 넣기를 하려면 시간이 너무 오래 걸릴 것입니다.

 

각 시트에 필터를 이용해서 이름순으로 나열한다고해도, Sheet1에 있는 기업과, Sheet2에 있는 기업의 개수가 다르다면 단순히 소속부 열을 복사해서 붙여 넣기 하는 방법은 통하지 않습니다. 정확히 매칭이 안되기 때문에 Vlookup 함수를 이용하는 것입니다. 

 

Vlookup 함수 구조

시트 1에 사용된 함수의 전체를 보면 다음과 같습니다. 

• =Vlookup(A2, Sheet2!$A$2:$D$2000,3, false) 

 

각 항목별로 보면 다음과 같습니다. 

 

1. =Vlookup // 함수의 시작을 알립니다. 
2. A2 // 기준이 되는 셀을 지정합니다. 여기서는 시트1의 종목코드가 기준입니다. 
3. Sheet2!$A$2:$D$2000 // 대상이되는 행을 지정합니다. 시트 2의 A2부터 D2000셀을 영역 지정한 것입니다.
4. 3  : 대상 영역에서 선택된 행의 3번째 열 값을 가져옵니다. 
5. False : 기준셀과 정확히 일치하는 행을 선택합니다. True로 하면 기준 셀의 이름과 유사한 행을 지정합니다.

 

이렇게 해서 Sheet1에 종목코드와 일치하는 Sheet2의 행을 찾고, 그 행의 3번째 열에 있는 값을 반환하는 것입니다. 대상이 되는 행 수식에서 달러표시를 넣은 것을 볼 수 있습니다. 달러 표시는 엑셀 함수에서  '고정'을 의미합니다.

 

Sheet1에서 함수를 작성하고, 아래 2000개의 행으로 모두 복사해줄것인데, 고정이 되어있지 않으면 대상이 되는 행의 행 번호, 열 번호가 하나씩 밀리게 될 것입니다. 그래서 기준이 되는 A2셀 번호는 아래로 복사되면서 자연스럽게 1씩 늘어나게 되고(A3, A4, A5 각 기업의 종목코드로 지정), 대상이 되는 영역의 행, 열 값은 고정을 시켜주는 것입니다. 

 

Vlookup 함수를 활용해서 각기 다른 시트에서 매칭되는 값을 쉽고 빠르게, 가져오는데 활용해보시기 바랍니다.