[구글 파이낸스] 환율과 지수 그래프 그리기 (+상관계수 계산)

이번 포스트에서는 구글 파이낸스 함수를 활용하여 하나의 그래프에 환율과 지수를 표현하고 둘 간의 상관관계(계수)까지 구하는 과정을 정리해보겠습니다. 

0. 목표

1. 구글 파이낸스 함수로 달러-원 환율 정보를 구한다.
2. 구글 파이낸스 함수로 KOSPI200 지수 정보를 구한다.
3. 달러-원 환율과 KOSPI200 지수를 하나의 그래프로 표현한다.
4. Correl 함수를 사용하여 달러-원 환율과 KOSPI200 지수의 상관관계를 구한다.

 

1. 달러-원 환율 정보 구하기 

올 해 1월부터 오늘(11월 16일)까지의 주별 달러-원 환율는 아래와 같이 구글 파이낸스 함수를 호출하여 구할 수 있습니다.

=GOOGLEFINANCE("CURRENCY:USDKRW","close","1/1/2020","11/16/2020", "weekly")

• "CURRENCY:USDKRW": 달러-원 환율을 구한다.
• "close": 종가를 기준으로 한다.
• "1/1/2020": 2020년 1월 1일을 계산 시작 기준일로 한다.
• "11/16/2020": 2020년 11월 16일을 계산 종료 기준일로 한다.
• "weekly": 주 단위로 정보를 구한다.

 

2. KOSPI200 지수 정보 구하기

KOSPI200 지수 정보는 아래와 같이 구글 파이낸스 함수를 호출하여 구할 수 있습니다.

=GOOGLEFINANCE("krx:kospi200", "close", "1/1/2020", "11/16/2020", "weekly")

다른 파라미터들은 앞선 달러-원 환율을 구할 때와 동일하고 종목 코드 부분만 "KRX:KOSPI200"로 적으면 됩니다. 아래는 구글 스프레드 시트에서 달러-원 환율과 KOSPI200 정보를 불러온 결과입니다. B2 셀에 달러-원 환율을 구하는 구글 파이낸스 함수식이 적은 후 엔터를 누르자 주별 달러-원 환율 정보가 나타났습니다. (파란색 네모) 마찬가지로 D2 셀에는 KOSPI200 정보를 구하는 구글 파이낸스 함수식을 적었고, 붉은색 네모와 같이 지수 정보가 구해졌습니다.  

 

3. 달러-원 환율과 KOSPI200 정보를 하나의 그래프로 표현하기 

이제 두 정보를 하나의 그래프에 표현해보겠습니다. 일단 구글 스프레트 시트의 오른쪽에 날짜와 환율, 지수 정보를 복사해보겠습니다. 날짜 정보의 경우, 불필요하게 시간 정보까지 포함되어 있어 시간 부분은 없애는게 좋을것 같습니다. 이 작업은 LEFT 함수와 FIND 함수를 조합하면 간단히 수행할 수 있습니다.

=LEFT(B3, FIND(" ", B3))

위의 함수를 수행하면 B3 셀의 내용(문자열)에서 왼쪽으로부터 공백이 나오기 전까지의 내용만 추출하게 됩니다. FIND(" ", B3)은 B3 셀의 내용에서 공백(" ")이 처음 나오는 위치를 반환합니다. 여기에 LEFT를 적용하면 B3 셀의 내용에서 공백이 나타나기 바로 전까지의 정보(날짜 정보)만 추출하는 것이죠. 예를 들어, 위의 함수식에서 B3대신 "1/10/2020 23:58:00"을 넣으면 "1/10/2020"까지만 남기게 됩니다. 아래는 Date 정보를 날짜 부분만 남기고 환율과 지수 정보를 복사한 결과입니다.

G열을 보면 LEFT, FIND 함수의 결과로 시간 부분이 없어진 것으로 볼 수 있습니다. 그리고 H열, I열에는 달러-원 환율과 KOSPI200 지수를 복사한 것인데, 날짜는 동일하게 1월 10일을 시작 날짜로 맞추었습니다. 이제 그래프를 그리기 위해 G, H, I열의 데이터 부분을 모두 드래그 한 후, 구글 스프레드시트 메뉴의 Insert -> Chart 를 선택합니다. 

그래프가 생성되긴 했는데, 환율과 지수의 등락이 제대로 표현되어 있지 않습니다. 환율 정보와 지수 정보가 하나의 Y축으로 표현하다보니 Y축의 수치 범위가 너무 넓어졌기 때문입니다. Y축을 하나 더 만들어 보겠습니다. 그래프를 선택하면 그래프의 오른쪽 상단에 점 3개가 나옵니다. 여기를 클릭하면 팝업 메뉴가 나타나는데, 여기서 Edit chart를 선택합니다. 

Chart editor가 나타나면 Customize 탭을 선택합니다. Customize 탭 아래 메뉴 중 Series을 선택하고 "Apply to all series"를 "달러-원 환율"로 변경합니다. 그리고 아래 Axis 항목을 Right axis로 변경합니다. 이렇게 하면 달러-원 환율에 대한 Y축이 차트의 오른쪽에 별도로 생성됩니다. 

이제 왼쪽 Y축(KOSPI200)과 오른쪽 Y축(달러-원 환율)의 범위를 조정해보겠습니다. 

Chart editor -> Customize 탭 -> Vertical axis 메뉴로 이동하면 Min (최소값), Max (최대값)을 입력하는 필드가 나타납니다. 여기에 KOSPI200 지수의 표현 범위를 설정해주시면 됩니다. 저는 Min은 200, Max는 350으로 설정하였습니다. 다음 Chart editor -> Customize 탭 -> Right vertical axis 메뉴로 이동하여 같은 방식으로 달러-원 환율의 Y축 표현 범위를 설정해줍니다. 저는 Min은 1100으로 Max는 1300으로 수정하였습니다.

좌우 Y축의 범위를 조정하니 환율과 KOSPI200 지수의 등락을 한 눈에 볼 수 있습니다.

 

4. Correl 함수를 사용하여 달러-원 환율과 KOSPI200 지수의 상관관계 구하기

데이터 간의 상관관계는 상관계수 함수 Correl를 통해 쉽게 계산할 수 있습니다. 

=CORREL(H3:H47, I3:I47)

H3:H47은 달러-월 환율의 데이터 범위이고, I3:I47은 KOSPI200의 데이터 범위입니다. 올해 1월부터 11월 중순까지의 두 데이터의 상관 계수를 계산하면 -0.81이 나옵니다. 이 정도면 올 해 달러-원 환율과 KOSPI200은 완전한 음의 상관관계에 있었다고 봐도 될것 같습니다. 

환율과 지수 외에도 여러분이 관심있는 주식 종목 또는 원자재(원유, 금 등)에 대한 그래프도 비슷한 방식으로 계산이 가능하니 한번 활용해보시기 바랍니다. (원자재의 경우는 선물 가격을 추종하는 ETF의 가격을 활용하는 방법이 있습니다.)

그럼 이만~