고점대비 하락률 차트 만들기 (구글 스프레드 시트)

몇주 전, 소몽님 라이브 방송에서 고점대비 하락률 차트가 나왔습니다. 해당 주식이 고점 대비 얼마나 하락했는지 알려주는 차트인데요, 저점매수를 좋아하는 저에게는 꽤 매력적인 차트였습니다. 소몽님은 ycharts.com에 있는 차트를 아용하셔서 가보았는데 유료더군요 ㅠㅠ

 

>>ycharts.com

 

유용할거 같아서 구독하려 했지만, 고점대비 하락률 보려고 돈내고 구독까지 하는 건 아깝다는 생각이 들어서 ㅠ 직접! 만들어 보았습니다. 구글 스프레드 시트에서는 금융 관련 함수들이 있기 때문에(Googlefinance) 이걸 활용하면 될 거라 생각했습니다.

 

제작 과정

 

고점대비 하락률 차트를 만들기 위해선 2개의 정보만 있으면 됩니다.

• 52주 최고가
• 현재 주가

 

현재 주가는 티커만 알면 만들 수 있습니다. 적용할 함수는 아래와 같습니다.

 

=GOOGLEFINANCE("티커","price",eomonth(today(),-120),today(),7)

 

하나씩 설명드리겠습니다. 티커는 미국 주식들이 가지는 고유 문자입니다. price는 주식의 현재 가격을 알 수 있습니다. eomonth는 특정 날짜를 계산하는 함수입니다. 저는 실시간 정보를 반영하고 싶어서 today 함수와 섞었고, 근 10년간 정보를 보기 위해서 -120을 입력했습니다. 주가를 일 단위로 보면 너무 많기 때문에 주 단위인 7을 입력했습니다.

 

이렇게 하고 엔터를 치면 2011년부터의 주가 데이터가 쭉 나옵니다. 제가 원하는 대로 주 단위로 나오는 걸 볼 수 있죠. 좋습니다. 이제 52주 최고가만 찾으면 됩니다.

 

52주 최고가를 구하는 식은 아래와 같습니다.

 

=GOOGLEFINANCE("티커","high52")

 

그런데 문제가 생겼습니다. high52는 특정 날짜의 고점을 구할 수 없습니다. 오늘의 52주 최고가를 구할 수는 있지만, 2011년의 최고가를 구할 수는 없었죠. 그래서 직접 52주 최고가를 찾아야 했습니다. 시트를 하나 더 만들어서 10년 전 이전의 당일 고점 주가 표를 만듭니다.

 

=GOOGLEFINANCE('티커,"high",eomonth(today(),-172),today(),7)

 

-172는 -120보다 더 이전의 값이 필요해서 넣었습니다. 그랬더니 너무 뒤로 갔는지 2007년 데이터가 나오더군요. 어쩔 수 없죠. 그냥 사용했습니다. 이제 52주 최고가를 구할 차례입니다.

 

그런데 문제가 하나 더 생겼습니다. 애플, 마이크로소프트 같은 오래된 회사들은 10년 전 주가 데이터가 있지만, 질로우, 에어비엔비같이 최근에 상장된 회사들은 10년전 데이터가 없습니다. 거기에다 최근 날짜순으로 정렬되는 함수의 특성 때문에, 최근 상장 주식들은 차트가 생성되지 않는 오류가 나왔습니다. 그래서 만든 데이터들을 역순으로 다시 배열해야 했습니다.

 

=offset($B$2:$B$521,counta($B2:B521)-1,0,1,1)

 

값들을 역순으로 다시 배열해주는 함수입니다. 복잡해 보이는데, 복잡한 것 맞습니다. 저도 이해가 안되서 그냥 구글링해서 찾은 데이터 약간 수정해서 넣었습니다.다행이 원하는 대로 작동해서 역순 배열하는 데 성공했습니다. 이제 고점 대비 하락률을 구할 차례입니다.

 

= (52주 최고가 - 현재가) / 52주 최고가

 

이 식으로 10년치 데이터를 정리했습니다. 그리고 이걸 이용해서 차트를 만들면...

 

이런 식의 차트를 볼 수 있습니다. 배포해드리는 차트는 10년 기준, 52주 고점 대비 하락률 차트인데요, 위에서 알려드린 함수들을 응용하면 20년 기준, 30년 기준도 만들 수 있고, 52주 고점 뿐만 아니라, 최고점 대비 하락률 차트도 만들 수 있습니다.

 

아래 링크를 통해서 차트를 볼 수 있습니다. 원본 파일의 수정을 막기 위해서 보기만 가능합니다.(구글 계정 필요) 사용하시려면 링크 들어가셔서 파일 -> 사본 만들기 를 누르면 파일 복사 및 수정이 가능하며 티커 입력이 가능합니다.

 

 

>>고점대비 하락률 차트 다운받기

52주 고점대비 하락률 차트