과거 시장 수익률 계산하는 방법 (코스피, 나스닥 시장 수익률 산출하기)

과거 시장 수익률 산출하기

 

과거 시장 지수 데이터를 구했다는(준비했다는) 가정하에, 매년 각 시장 수익률을 산출해보도록 하겠습니다. 시장 지수 데이터 다운로드 방법은 아래 포스팅을 참고하시기 바랍니다.

 

: 코스피, 코스닥, S&P500, 나스닥, 다우존스 수익률 산출

 

 

★ 시장지수 과거 데이터 다운로드 방법

 

시장 지수 과거 데이터 한번에 다운로드 받는 방법 (코스피, S&P500, 나스닥 등)

 

확보된 시장지수 데이터

 

 

 

 

 

1. 확보 데이터 살펴보기

 

매년 각 시장 수익률을 산출하기 위해선 시작일 종가 / 마지막일 종가를 추출하여야 합니다. 먼저 확보된 데이터를 살펴보도록 하겠습니다.

 

 

★시장 수익률 = (마지막 일 종가 - 시작일 종가) / 시작일 종가 

 

 

 

 코스피 - 좌) 2000년 1월 4일, 우) 2000년 12월 26일

 

1) 2000년을 먼저 살펴보면, 코스피 지수의 시작일은 2000년 1월 4일입니다. 반면 S&P500 지수의 시작일은 2000년 1월 3일입니다.

 

 

2) 2000년 코스피 지수의 마지막 일은 2000년 12월 26일입니다. S&P500 지수의 마지막 일은 2000년 12월 29일임을 확인할 수 있습니다.

 

 

위와 같이 각 시장 지수마다 각 해마다 시작일 / 마지막 일이 다르다는 것을 알 수 있습니다. 그렇다면 매년 시작일 / 마지막 일 지수 값(종가)을 어떻게 추출할 수 있을까요?

 

 

노가다로 필요 없는 데이터를 지워가는 방법은 제외하구요!

 

 

 

 

 

2. 데이터 추출하기 : 필요 함수

 

구글 스프레드시트의 함수를 활용하면 쉽게 구할 수 있습니다. 사용할 함수 종류는 딱 4개뿐입니다.

 

 

1) filter 함수

 

=filter(범위, 조건 1, 조건 2, ...)

 

 

2) year 함수

 

=year("2001년 1월 4일")

 

→ 년도 "2001" 값을 추출합니다.

 

 

3) min/max 함수

 

=min(F:F)

 

→ F열 중 가장 작은 값을 가져옵니다.

 

 

=max(F:F)

 

→ F열 중 가장 작은 값을 가져옵니다.

 

 

4) vlookup 함수

 

=vlookup(찾는 값, 범위, 열 번호, 옵션)

 

 

 

 

 

3. 예제 : 코스피 2001년 수익률 구하기

 

코스피 지수의 2001년 시작일 / 마지막일의 값을 가져오도록 하겠습니다.

 

 

 

step 1. 코스피 raw data 전체를 F:G 셀에 입력합니다.

 

 

 

filter 함수 결과 값

 

step 2. B3 셀에 filter 함수를 입력하여 2001년 날짜 값을 가져옵니다.

 

filter 함수는 원본 데이터에서 조건에 맞는 데이터만 가져올 수 있는 함수입니다. 

 

 

=filter(F:F, year(F:F)=2001)

 

▶ F열 데이터 중 2001년도 날짜 값만 가져옵니다.

 

 

 

시작일 값 추출

마지막일 추출

 

step 3. min / max 함수를 사용하여 시작일, 마지막일 날짜 값을 추출합니다.

 

 

=min(filter(F:F, year(F:F)=2001))

 

▶ 2001년도 중 가장 작은 값인 시작일 "2001년 1월 2일"을 추출합니다.

 

 

=max(filter(F:F, year(F:F)=2001))

 

▶ 2001년도 중 가장 작은 값인 시작일 "2001년 12월 28일"을 추출합니다.

 

 

 

시작일 종가 추출

마지막일 종가 추출

 

step 4. vlookup 함수를 활용하여 2001년 시작일 / 마지막일 종가 값을 구합니다.

 

 

1) 2001년 시작일 종가 추출 : 520.95

 

=vlookup(min(filter(F:F, year(F:F)=2001)), F:G, 2, 0)

 

 

2) 2001년 마지막일 종가 추출 : 693.70

 

=vlookup(max(filter(F:F, year(F:F)=2001)), F:G, 2, 0)

 

 

 

 

step 5. 2001년 시작일 / 마지막일 종가 값을 활용하여 시장 수익률을 구합니다.

 

 

★시장 수익률 = (마지막일 종가 - 시작일 종가) / 시작일 종가 = 33.2%

 

 

 

 

 

3. 마무리하며

 

위 방법을 통해 2001년 ~ 2021년까지의 각 시장 수익률을 아래와 같이 산출하였습니다. 글 맨 아래 다운로드 링크를 두었으니, 필요하신 분들은 활용하시기 바랍니다. 

 

 

★다운로드 방법 : 글 아래 링크 클릭 → 파일 → 사본 만들기

 

 

결과값

 

 

시장지수 수익률 구하기