백테스팅에 필요한 엑셀함수 - Substitute 특정문자 지우기

엑셀에서 특정 문자를 지우고 숫자만 남기고 싶을 때 사용하는 엑셀 함수에 대한 포스팅입니다.

 

특정-문자제거-엑셀함수-Substitute

 

퀀트 투자를 하는 분들은 저처럼 웹 페이지 크롤링을 많이 합니다. 그 이유는 백테스팅할때, 자료를 다 갖춰놓은 상태로 테스팅을해야하는데, 정리된 자료를 무료로 구하는 건 매우 어려운 일이고, 계속 갱신되는 자료를 통해서 종목을 뽑아내야하기 때문에 웹페이지 크롤링을 많이 이용합니다. 저는 구글 스프레드시트로 크롤링하며, 네이버 금융과 fn가이드를 많이 이용합니다. 

 

기업 재무제표 값의 대부분은 플러스로 표시가 되어있지만, 어떤 값들은 마이너스로 적혀있습니다. 여기서 문제점이 하나 생깁니다. 네이버 금융은 크롤링했을때는 마이너스 앞에 * 표시가 붙습니다. 네이버 금융 사이트에서 재무제표를 보면 마이너스 값은 빨간색으로 표시되어있는데, 크롤링해서 엑셀 시트로 끌어오면 마이너스가 ** 사이에 들어가 있습니다.

 

네이버금융-재무제표-크롤링

 

제가 프로그래머가 아니어서 정확히는 모르겠습니다만, 아마 색깔과 관련한 이유 때문에 그런게 아닐까 추측해봅니다. 중요한 건, 숫자 앞, 뒤에 저런게 찍혀있으면 엑셀은 해당 숫자를 텍스트로 인식하고, 계산 수식을 넣어줄 때 오류가 납니다. 

 

엑셀-문자-수식오류

 

위의 그림은 어떤 기업의 재무제표를 크롤링한것입니다. 매출액과 영업이익률의 20년 9월이 마이너스 값을 갖습니다. 근데 숫자 양옆에 * 표시가 있어서 영업이익률이나 영업이익 데이터를 계산에 이용하려면 *를 지워줘야 합니다. 하나하나 *문자를 지울수도 있겠지만, 시간도 오래 걸리고 귀찮으니까 함수를 이용해서 특정 문자를 지워보려합니다.

 

Substitute 함수

Substitute는 '대체'라는 뜻입니다. 사실 이 함수는 어떤 특정 문자를 다른 문자로 바꾸는데 사용하는 함수입니다. 함수의 기본적인 이해를 돕기 위해 '반갑읍니다' 를 '반갑습니다' 로 바꾸는 과정을 예로 들어 설명하겠습니다. 

 

Substitute함수-사용-예제1

 

=Substitute(A1, "읍니다", "습니다") 

• =Substitute : Substitute함수의 사용을 알립니다.
• A1 : 바꿈 당할 셀을 지정합니다.
• "읍니다" : 바꿀 문자를 지정합니다. 문자니까 큰따옴표를 달아줘야 합니다.
• "습니다" : 바꿀 문자를 지정합니다. 문자니까 큰따옴표를 달아줘야 합니다.

 

 

이제 기본 사용을 알았으니, 주 목적인 크롤링한 재무제표의 특정 문자를 제거해보겠습니다.

 

Substitute함수-사용-예제2

 

=Substitute(D4, "*", "") 

• =Substitute : Substitute함수의 사용을 알립니다.
• D4 : 바꿈 당할 셀을 지정합니다.
• "*" : 바꿀 문자를 지정합니다. 문자니까 큰따옴표를 달아줘야 합니다.
• "" : 바꿀 문자를 지정합니다. *를 없애는 거니까 공란 표시인 ""를 넣어줍니다.
• D4의 *-24*가 G4에 -24로 바뀐 것을 확인할 수 있습니다.

 

백테스팅에서 어쩌면 가장 중요한 것이, 신속, 정확, 편리한 데이터 클리닝입니다. 데이터 클리닝은 가장 손이 많이가는 작업이기도 합니다. 엑셀의 함수를 잘 이용해서, 슬기로운 퀀트 투자되시기를 기원합니다.