상관관계 행렬 만들기

이번 호의 주제는 지원 문의에서 영감을 얻은 것입니다. N개의 자산 수익률에 대한 상관관계 행렬을 효율적으로 구성하려면 어떻게 해야 하나요?

NumXL은 두 시간 시리즈 간의 교차 상관 관계를 계산하는 다양한 기능을 제공하지만, 중간 계산 없이 20개의 변수에 대해 이를 어떻게 수행할 수 있을까요? 걱정 마세요. Excel에는 MATCH(.) 및 INDEX(.) 함수가 있으며, 이 함수를 공식을 통해 사용하면 상관 행렬을 쉽게 빠르게 구성할 수 있습니다. 바로 이 내용이 이번 호에서 다룰 주제입니다.

요약하면, 다음 입력 데이터 테이블을 변환하고자 합니다:

이 상관관계 매트릭스에 넣습니다:

관심이 있으신가요? 시작해보자!

데이터 준비

이 튜토리얼에서는 2015년 9월 28일부터 2020년 2월 17일(코로나19 확산으로 시장 변동성이 커지기 직전) 기간 동안 16개 ETF의 로그 주간 수익률을 사용합니다. 미국 주식 시장(예: S&P 500, 다우존스 산업평균지수, 러셀 1000/2000/3000 등), 미국 에너지 시장(석유 및 천연가스), 금속(금, 은 및 산업용 기초 금속), 마지막으로 초단기 채권 시장(현금)을 커버하기 위해 16개의 ETF를 선택했습니다.

다음으로 로그 주간 수익을 별도의 워크시트에 각각 별도의 열에 복사하되 공통 날짜 필드(열 A)를 사용하여 모든 자산을 정렬했습니다.

이제 데이터 테이블의 셀 범위 이름을 정의해 보겠습니다:

• 수식 탭을 선택하거나 전환합니다.
• 입력 셀 범위를 선택한 다음 "이름 정의"를 클릭합니다.

• '이름 정의' 대화 상자가 나타나고 몇 가지 필드가 미리 채워져 있습니다(예: '참조'에는 데이터 테이블 셀 범위가 있고 범위는 '통합 문서'로 설정되어 있음).
• "이름 정의" 대화 상자에서 이름을 의미 있는 이름으로 변경하여 "RETURNS"를 선택했습니다.

• 확인을 클릭합니다.

자산 심볼 티커(2행)에 대해 또 다른 정의된 이름(예: SYMBOLS")을 만들어 보겠습니다. 위와 동일한 단계를 반복하되 시세 심볼이 있는 맨 위 행에 대해 반복합니다.

이제 이름 관리자를 살펴보면 다음과 같은 내용이 표시됩니다:

상관관계 매트릭스

상관 행렬의 경우 각 행 또는 각 열은 단일 자산에 해당하므로 시세 기호를 상관 행렬의 열 및 행 헤더로 설정합니다.

힌트: '붙여넣기 옵션'의 '바꾸기' 기능을 사용하여 복사한 행(예: 티커 심볼 행)을 열에 붙여넣을 수 있습니다.

시계열 참조

상관관계 표에서 각 셀은 두 자산 수익률 간의 열과 행의 교차 상관관계를 나타냅니다. 예를 들어 아래 그림에서 회색 셀은 IWB와 DIA의 상관관계입니다.

이제 데이터 테이블에서 시계열을 참조하기 위해 티커 기호를 사용해야 합니다:

단계 1: 티커 기호를 숫자 오프셋으로 숨깁니다. MATCH(.) 함수를 사용하여 SYMBOLS 정의 이름에서 티커의 인덱스를 평가합니다. 열과 행 헤더 모두에 대해 이 작업을 수행합니다. 예시

단계 2: 해당 시계열을 참조하세요.

INDEX(.) 함수를 사용하여 RETURNS 정의 이름에서 하나의 열을 참조하되 모든 행을 참조합니다. 열과 행 헤더 모두에 대해 이 작업을 수행하되 오프셋을 사용합니다.

단계 3: MATCH(.) 및 INDEX(.) 함수 결합하기

단계 4: 원하는 상관관계 함수를 선택하고 두 시계열을 전달한 다음 반환된 값을 테이블에 저장합니다.

단계 5: 위의 수식을 다른 셀(행과 열)에 복사하여 전체 상관관계 행렬을 계산합니다.

결론

이 튜토리얼에서는 기본 제공되는 엑셀 함수인 MATCH(.) 및 INDEX(.)를 사용하여 몇 가지 간단한 단계만으로 중간 계산 없이 상관관계 행렬을 만들었습니다. 또한 '정의된 이름' 기능을 사용하여 데이터 집합을 캡슐화하고, 수식뿐만 아니라 관찰 및/또는 자산을 추가하는 등 입력 데이터의 유지 관리도 간소화했습니다.