두 종목의 주가 움직임이 얼마나 상관관계를 가지고 움직이는지를 나타내는 상관계수를 매우 쉽게 구할 수 있는 방법이 있다. 이번 포스팅에서는 그 방법에 대해서 알아보려고 한다.
상관계수에 대해서는 아래 포스팅을 참고하면 좋을 것이다.
배당주 투자 방법 - 두 종목 사이의 상관관계(Correlation)을 평가하는 상관계수(Correlation Coefficient)!
주식 투자를 하다 보면 A라는 종목과 B라는 종목의 주가가 함께 움직이는 건 아닐까 하는 생각이 들 때가 있다. 예를 들어 같은 섹터에 있거나 아니면 한쪽이 한쪽의 고객사이거나 한 경우이다.
rayshines.tistory.com
1. 최근 1년 치 주가 데이터 가져오기
일단 이번 포스팅은 최근 1년 간의 주가 데이터만을 대상으로 하려고 한다. 그 이상도 시도해보았으나 주가 데이터가 제대로 불려 들여지지 않는 경우가 있었기 때문이다.
=GOOGLEFINANCE("AAPL","price", TODAY()-365, TODAY())
위의 식을 셀에 넣어보자. 그러면 애플의 최근 1년 치 주가 데이터가 아래와 같은 형태로 불려 들여질 것이다.
그리고 아래의 식을 이용하여 그 옆 열에 SPY의 데이터를 불려들였다.
=GOOGLEFINANCE("SPY","price", TODAY()-365, TODAY())
2. 상관계수 구하기
상관계수를 구하는 함수는 아래와 같다.
=CORREL(B2:B253,D2:D253)
B2~B253과 D2~D253의 데이터 사이의 CORRELation을 구하라는 의미이다.
이렇게 하면 위의 이미미처럼 2021년 3월 29일부터 2022년 3월 25일까지 애플과 SPY 주가 사이의 상관계수가 매우 간단하게 구해진다
그러나 이런 식으로는 포트폴리오를 관리하는 시트에 이용할 수가 없다. 그래서 이 데이터를 가공해야 한다.
3. 위의 데이터에서 2열의 데이터만 추출하기
위 이미지에 보이는 것처럼 날짜 데이터를 굳이 가져올 필요가 없다. 테이블에서 특정 셀이나 열의 데이터만 가져올 때는 index 함수를 쓴다.
=index(GOOGLEFINANCE("AAPL","price", TODAY()-365, TODAY()),,2)
위의 식을 적용해보자. 그러면 위의 데이터에서 2열의 데이터만 추출해서 표시된다. 결과는 아래와 같다.
위의 이미지를 보면 애플과 SPY의 주가 데이터만 불려 들여졌다는 것을 알 수 있다. 둘 사이의 상관계수를 구해보자.
위 이미지처럼 동일한 상관계수가 구해졌다.
그런데 이것도 아직 곤란하다. 일목요연하게 보여야 하는 포트폴리오 시트에 저렇게 많은 셀을 사용하는 것은 낭비이다.
4. 아주 간단하게 두 종목 사이의 상관계수 구하기
위의 이미지처럼 굳이 모든 데이터가 셀에 표시되어야 할 필요가 전혀 없다. 간단하게 아래의 식 하나면 모든 것이 끝난다.
=CORREL(index(GOOGLEFINANCE("AAPL","price", TODAY()-365, TODAY()),,2), index(GOOGLEFINANCE("SPY","price", TODAY()-365, TODAY()),,2))
위의 이미지를 보면 알겠지만 제일 오른쪽 위 셀에 들어 있는 값 - 상관계수 - 이 그 왼쪽 셀의 값 - 상관계수 - 과 완전히 동일하다.
즉 표시된 셀에 구역 지정을 하여 구한 값과 완전히 동일하다는 것이다.
5. 포트폴리오 시트에 적용하기
아래와 같이 표를 수정했다.
그리고 상관계수 셀에는 아래 식을 넣었다.
=correl(index(GOOGLEFINANCE(A3,"price", TODAY()-365, TODAY()),,2), index(GOOGLEFINANCE("SPY","price", TODAY()-365, TODAY()),,2))
즉 A3 셀에 들어있는 티커에 해당하는 종목과 SPY와의 1년 치 주가 데이터의 상관계수를 구하라는 의미이다.
6. SPY 외의 다른 종목과도 상관계수를 간단하게 구하기
SPY는 시장 전체를 대변한다고 볼 수 있으니 SPY의 상관관계를 구하는 것을 일단 디폴트라고 할 수 있겠다. 그런데 SPY 외의 다른 종목과의 상관계수를 구하고 싶을 수도 있을 것이다. 그래서 살짝 수정을 했다.
=correl(index(GOOGLEFINANCE(A3,"price", TODAY()-365, TODAY()),,2), index(GOOGLEFINANCE($D$1,"price", TODAY()-365, TODAY()),,2))
위의 식은 A3 셀의 종목과 D2 셀의 종목 - 이 셀은 절대 참조 - 사이의 1년 치 주가 데이터의 상관계수를 구하라는 의미이다.
다시 말해 D2의 티커만 바꾸면 쉽게 상관계수를 구하는 종목을 바꿀 수가 있다!
위의 이미지처럼 될 것이다.
D2 셀의 티커를 다른 것으로 바꿔보았다.
AAPL로 바꿔보았다. 당연히 AAPL과 AAPL의 상관계수는 1이 나와야 한다. 잘 작동하는 것을 알 수 있다.
위의 식을 이용하면 SPY 뿐만 아니라 다양한 종목들 사이의 상관계수를 정말 쉽게 구할 수 있다. 개인투자자분들에게 도움이 되길 빈다.
'재테크 > 구글 스프레드시트' 카테고리의 다른 글
| 구글 스프레드시트 주식 배당 관리 - 20. 주가가 많이 떨어진 종목 강조하기 (2) | 2022.03.30 |
|---|---|
| 구글 스프레드시트 주식 배당 관리 - 19. 2022년 3월 29일 기준 핀비즈 Finviz 배당 불러오기 업데이트 (1) | 2022.03.29 |
| 구글 스프레드시트 주식 배당 관리 - 17. 베타 (Beta) 가져오기 (1) | 2022.03.25 |
| 구글 스프레드시트 주식 배당 관리 - 16. 배당성향(Payout Ratio) 가져오기 (1) | 2022.03.22 |
| 구글 스프레드시트 주식 배당 관리 - 15. 원화 1000단위 콤마 표시 (1) | 2022.03.19 |