자동 업데이트 기능이 있는 예측 모델

이 뉴스레터의 주제는 새로운 데이터 요소를 사용할 수 있게 되고 예측이 자동 업데이트될 때 사용자(또는 다른 사람)가 쉽게 유지할 수 있도록 예측 모델(예: ARMA, GARCH, 회귀)을 설정하는 방법에 관한 것입니다.

이를 증명하기 위해 S&P 500 ETF 월간 수익률에 대한 GARCH 모델을 정의하고, 매개변수 값을 보정하고, 다음 36개월 동안의 변동성 예측 기간 구조를 구성했습니다. 그런 다음 새 데이터 포인트를 연결하고 예측 시작일을 앞당기고 예측 값을 업데이트하는 모델을 관찰했습니다.

왜 신경 써야 할까요?

실제로 우리는 엑셀 기반 모델을 많이 만들며, 그중 일부는 일회용으로 사용하지만 많은 스프레드시트는 동료와 공유하고 가끔씩 수정하며 오랫동안 보관하는 경우가 많습니다. 자동 업데이트를 위해 워크시트를 준비하는 것은 비교적 간단합니다. 준비가 완료되면 새 데이터 요소를 쉽게 추가하거나 기존 데이터 요소를 수정하고 최소한의 노력으로 계산이 업데이트되는 것을 확인할 수 있습니다.

준비됐으니 시작해 보겠습니다!

데이터 준비

이 튜토리얼에서는 2010년 1월부터 2019년 11월까지 S&P 500 ETF(SPY)의 월간 로그 수익률을 사용합니다:

먼저, 현재 데이터 집합에 자리 표시자를 추가해야 합니다. 날짜(예: 2019년 12월, 2019년 1월)를 앞당기고 그 값으로 "#N/A"를 입력하면 됩니다.

모델링

이 데이터 세트의 시간에 따라 변화하는 월별 변동성 동학을 포착하기 위해 학생의 혁신이 포함된 GARCH(2,2)를 선택했습니다. 모델을 정의하려면 워크시트에서 빈 셀을 찾은 다음 'NumXL' 툴바에서 'GARCH' 아이콘을 클릭하면 GARCH 마법사가 나타납니다.

'입력 데이터' 섹션에서 앞서 추가한 자리 표시자를 포함하여 월별 수익률의 셀 범위를 선택합니다. NumXL 함수는 입력 셀 범위의 양쪽 끝에 "#N/A" 값이 있는 관측값을 삭제하므로 걱정하지 않아도 됩니다.

모델 사양 섹션에서 ARCH 및 GARCH를 선택하고 순서를 2로 설정합니다. 혁신 섹션에서 "학생 t-분포"를 클릭합니다. 이제 '확인' 버튼을 클릭하여 선택을 확인합니다.

GARCH 마법사는 모델 테이블을 생성하고 해당 매개변수 값을 유효하지만 최적이 아닌 값으로 초기화합니다. GARCH 매개변수에 대한 최적의 값 집합을 찾으려면 모델 테이블의 헤더 셀을 선택하고 'NumXL' 도구 모음에서 '보정' 아이콘을 클릭합니다. 모든 값이 설정된 Microsoft 솔버가 나타납니다. 풀기를 클릭합니다.

Microsoft 솔버는 GARCH 프로세스를 위한 최적의 솔루션을 검색합니다.

데이터 탐색

앞서 NumXL 함수는 입력 셀 범위에서 "#N/A"와 같은 값을 가진 모든 관측값을 버린다고 언급했습니다. 데이터 집합이 어디에서 끝나는지 어떻게 알 수 있을까요?

위의 질문에 답하려면 먼저 두 가지 함수를 사용하여 데이터 집합의 크기를 계산해야 합니다: RMNA(.)와 COUNT(.). RMNA(.)는 "#N/A"인 데이터 요소를 삭제하고 누락되지 않은 모든 관측값의 배열을 반환하고, COUNT(.)는 이 배열의 크기를 반환합니다.

Excel에 내장된 INDEX(.) 함수와 위의 데이터 집합 크기를 사용하여 데이터 집합의 종료 날짜를 계산할 수도 있습니다.

요약하면, 누락되지 않은 119개의 값으로 구성된 데이터 집합이 있으며, 가장 최근 관측값은 2019년 11월에 떨어졌습니다.

예측

이 튜토리얼에서는 다음 36개월 동안의 월별 변동성 예측을 작성해야 합니다. 이렇게 하려면 모델 테이블의 헤더 행을 선택하고 NumXL 도구 모음에서 '예측' 아이콘을 클릭합니다.

'최신 관측값' 섹션에서 'B' 열의 셀 범위(결측값이 있는 관측값(예: #N/A)을 포함)를 선택합니다.

변동성의 경우 비워두면 NumXL은 GARCH 모델 자체에서 계산한 샘플 내 변동성을 사용합니다. "확인" 버튼을 클릭합니다.

예측 마법사는 입력된 데이터 집합이 끝난 후 측정된 시간 단위를 단계(즉, 월)로 표현하는 예측 테이블을 생성합니다.

시간이 지남에 따라 새 데이터 요소를 추가하면 마지막 관측(누락되지 않은 값 포함)의 날짜가 앞당겨져 예측 테이블의 단계 단위 해석에 영향을 미칩니다. 이 문제를 해결하기 위해 예측 테이블의 왼쪽에 열을 추가하여 해당 달력 날짜를 지정했습니다.

각 단계에 해당하는 날짜를 계산하기 위해 아래와 같이 NxEDATE(.) 및 CONCAT(.) 함수를 사용합니다:

데이터 집합 종료 날짜를 사용하여 NxEDATE(.)는 N개월 이후에 해당하는 데이터를 반환합니다. 여기서 N은 예측 테이블의 단계 수입니다.

새 데이터가 주말이나 공휴일인 경우 어떻게 되나요? 데이터 집합은 재무 시계열이므로 날짜가 근무일에 속해야 하며, 그렇지 않으면 가장 가까운 다음 근무일로 날짜를 이동합니다(NxAdjust(.)).

그거예요! 예측 테이블과 월별 변동성 기간 구조 곡선을 살펴 보겠습니다.

GARCH 모델은 과거 수준(즉, 3.8%)에 비해 낮은 변동성 환경을 보여주지만, 내년에는 변동성이 꾸준히 상승할 것으로 예측합니다.

데이터 포인트 추가

2019년 12월의 월간 수익률이 -0.6%라고 가정했을 때 어떤 일이 발생하는지 살펴봅시다.

단계 1: 데이터 집합에 새 값을 입력합니다.

단계 2: 관찰

입력 데이터 세트 크기가 120으로 증가했으며 종료 날짜는 2019년 12월입니다.

예측 표에서 1단계의 해당 날짜는 이제 2020년 1월(2019년 12월)이며 변동성 기간 구조가 달라졌습니다(덜 부드럽습니다).

단계 3: 고급

위의 예측 표는 앞서 보정한 것과 동일한 GARCH 프로세스, 특히 매개변수 값을 사용합니다.

논의를 위해 모델을 다시 보정하되 ARCH/GARCH 순서(즉, P 및 Q)와 혁신 분포(예: 학생 t)는 동일하게 유지한다고 가정해 보겠습니다.

앞서 한 것처럼 GARCH 모델 테이블의 머리글 행을 찾아서 선택한 다음 NumXL 도구 모음에서 '보정' 아이콘을 클릭합니다.

솔버가 팝업되지만 이번에는 마지막 실행의 매개변수 최적값으로 최적화가 시작됩니다(즉, 초기값). 따라서 빠른 전환을 기대할 수 있습니다.

최적화 프로세스를 시작하려면 '솔버' 버튼을 클릭합니다.

완료되면 솔버에 "솔루션 찾기" 대화 상자가 표시됩니다. 확인 버튼을 클릭하면 새 매개변수의 값이 모델 테이블에 복사됩니다.

예측에 대한 재보정이 예측에 미치는 영향을 살펴보려면 표(및 플롯)에서 예측 값을 아래에서 살펴보세요.

결론

이 튜토리얼에서는 자동 업데이트를 위해 모델을 설정하는 두 단계를 살펴봤습니다:

• 입력 데이터 집합의 끝에 자리 표시자 데이터 포인트 추가,
• 모델 및 예측에 플레이스홀더 데이터 포인트가 있는 데이터 집합을 선택합니다.

시간이 경과하고 새로운 관찰이 실현되면 데이터 집합에 실제 값을 입력(#N/A 대신)하고 Excel은 모든 참조 셀(예: 예측 테이블)의 계산을 업데이트합니다.