엑셀 통계함수 회귀계수 구하기와 주식시장에 어떻게 응용되나

IT 컴퓨터/엑셀

by noonwith 2013. 8. 7. 23:52

돈되는 엑셀 통계함수 회귀계수 구하기와 주식 시장에 응용하기

(KODEX레버리지 회귀모형 구하기,KP선물 회귀모형 구하기)

회귀분석과 회귀모형이 많은 곳에 사용 되는데 주식시장에서 회귀모형을 만들때 사용하기 위한 회귀계수를 엑셀을 이용하여 구하는 방법을 설명드릴께요.

대표적인 가격을 가지거나 종속, 독립 데이터 요소가 많은 즉 주식시장에서 거래신뢰도가 높은 종목에 응용할수 있을 것입니다.

KP선물, KP200지수, 거래 대금과 거래량이 동시에 큰 KODEX레버리지, 이상 3가지를 추종하는 상품이나 주식은 그 특성상 신뢰도가 매우 높습니다.

SLOPE 함수, INTERCEPT 함수, STEYX 함수 가 사용 되는데 엑셀에서 간단하게 구할수 있습니다. 엑셀값만 보는 비전공자라 이론을 설명하기가 힘들거든요..아래 엑셀 도움말을 복사해 놓았으니 읽어보시길 바랍니다.

위사진은 KODEX레버리지 20일간 시가, 고가, 저가 입니다. 목적은 전일까지 발생한 데이터를 회귀분석후 다음날 시가를 기준으로 당일 또는 미래 특정일의 고가 저가 범위를 정해 보겠다는 것입니다. 물론 딱 그만큼 가격 범위에서 멈춰주는 일은 없지만 심리적인 범위와 함께 행동제어에 사용하면 될 것입니다.

C23 셀에 =SLOPE(D2:D21, C2:C21) 를 입력하면 고가기울기 0.7476185 결과를 보여줍니다.

나머지 고가와 저가의 절편과 표준오차 회귀계수도 금방 구할 수 있을 것입니다.

고가기울기	0.7476185	=SLOPE(D2:D21, C2:C21)
고가 절편	2851.9455	=INTERCEPT(D2:D21, C2:C21)
고가 표준오차	98.58046	=STEYX(D2:D21, C2:C21)
저가기울기	1.071579	=SLOPE(E2:E21, C2:C21)
저가 절편	-899.4257	=INTERCEPT(E2:E21, C2:C21)
저가 표준오차	79.05221	=STEYX(E2:E21, C2:C21)

KP선물과 KP200지수도 같은 방법으로 계산하면 됩니다.

아래 내용은 엑셀 통계함수 도움말입니다. 통계 함수 이론이 궁금하신분은 읽어보세요

SLOPE 함수

기울기를 구합니다

known_y's와 known_x's 사이의 데이터 요소에 대한 선형 회귀선의 기울기를 반환합니다. 기울기는 선의 두 점 사이의 수직 거리를 수평 거리로 나눈 회귀선의 변화율입니다.

구문

SLOPE(known_y's,known_x's)

known_y's 종속 데이터 요소의 셀 배열 또는 범위입니다.

known_x's 독립 데이터 요소의 집합입니다

INTERCEPT 함수

절편을 구합니다

기존 x 값과 y 값을 사용하여 한 개의 선이 y 축과 교차하는 지점을 계산합니다. 절편은 known_x's와 known_y's의 값으로 이루어진 가장 적합한 회귀선을 기반으로 합니다. 독립 변수가 0일 때 종속 변수의 값을 확인하려면 INTERCEPT 함수를 사용합니다. 예를 들면 데이터가 상온이나 그 이상의 온도에서 측정된 경우 INTERCEPT 함수를 사용하여 0°C에서의 금속의 전기 저항을 예측할 수 있습니다.

구문

INTERCEPT(known_y's,known_x's)

known_y's 관측값이나 데이터의 종속 변수 집합입니다.

known_x's 관측값이나 데이터의 독립 변수 집합입니다.

주의

인수는 숫자이거나 숫자가 포함된 이름, 배열 또는 참조여야 합니다.

배열 또는 참조 인수에 텍스트, 논리값 또는 빈 셀이 있는 경우 이러한 값은 포함되지 않지만 값이 0인 셀은 포함됩니다.

known_y's와 known_x's에 서로 다른 개수의 데이터 요소가 들어 있거나 데이터 요소가 들어 있지 않으면 #N/A 오류 값이 반환됩니다.

여기에서 x와 y는 표본 평균 AVERAGE(known_x’s)와 AVERAGE(known_y’s)입니다.

INTERCEPT 및 SLOPE 함수에 사용되는 기본 알고리즘은 LINEST 함수에 사용되는 기본 알고리즘과 다릅니다. 이러한 알고리즘의 차이로 인해 데이터가 확정적이지 않고 동일한 선 위에 있는 경우 서로 다른 결과가 반환될 수 있습니다. 예를 들어 known_y's 인수의 데이터 요소가 0이고 known_x's 인수의 데이터 요소가 1인 경우

INTERCEPT 및 SLOPE 함수는 #DIV/0! 오류를 반환합니다. INTERCEPT 및 SLOPE 알고리즘은 오직 하나의 결과만 찾도록 설계되어 있지만 이 경우 답이 여러 개일 수 있기 때문입니다.

LINEST가 값 0을 반환합니다. LINEST 알고리즘은 공선 데이터에 대해 적당한 결과를 반환하도록 디자인되었으므로 이 경우 답을 하나 이상 찾을 수 있습니다.

STEYX 함수

표준오차를 구합니다

회귀 분석에서 각각의 x에 대하여 예측한 y 값의 표준 오차를 반환합니다. 표준 오차는 각각의 x 값에 대한 y 예측값의 오차량을 나타냅니다.

구문

STEYX(known_y's,known_x's)

known_y's 종속 데이터 요소의 배열이나 범위입니다.

known_x's 독립 데이터 요소의 배열이나 범위입니다.

주의

인수는 숫자이거나 숫자를 포함한 이름, 배열 또는 참조 영역일 수 있습니다.

인수 목록에 직접 입력하는 논리값, 텍스트로 나타낸 숫자 등은 계산에 포함됩니다.