마켓 아웃룩 구글 시트

LaserGolf 님이 올리신 글을 읽고서 매일 아침 투자에 뛰어 들기 전에 여러가지 정보를 취합하여 분석하고 시작하는 것의 중요성을 인지 하였습니다. 부족 하지만 이러한 정보를 한 눈에 확인 할 수 있는 구글 시트를 만들어서 수 주 동안 직접 사용하면서 버그를 제거 하였고 몇 가지 기능을 향상 하였습니다. 혹시 다른 분 들께도 도움이 될까 하여 공유합니다. LaserGolf Market Outlook 이라고 이름 지었습니다.

아래의 링크를 클릭 하시고 사본 만들기로 복사해 가시면 됩니다.

https://docs.google.com/spreadsheets/d/1g8s5lV4c75noPp5Xy4k4SZRjpg0FtThzp0yRSSd-0ek/copy

(추가) version 2 updated

https://docs.google.com/spreadsheets/d/1TxVvwPjV8iOwSSAwochEWVFDMrwaLvALrVFJZbf0H6M/copy

Revised on 07/25/2020		- removed barchart API access and use investing.com instead
		- use TYX for 30 years T-Bond yield, TLT was incorect usage
		- added futures' change % for some of macro economy index (pre-market and after-market monitoring)
		- optimized to call minimum API calls or crawlings to speed up updating

첫 번째 구글 시트에는 네 가지 정보를 담고 있습니다.

피봇포인트(Pivot Point) 자동 계산 테이블은 하루 전 거래일 날짜 기준으로 마켓 인덱스, ETF, 선물의 피봇 포인트를 자동으로 계산 해줍니다. 노란색 바탕의 셀에 개별 종목을 입력 하면 해당 종목의 피봇 포인트도 계산 합니다. 

NQ-minis 와 EQ-minis 즉, 선물은 GOOGLEFINANCE() 에서 지원하지 않기 때문에 barchart ondemand (https://www.barchart.com/ondemand) 라는 곳에서 무료로 등록하면 사용할 수 있는 Market Data API 함수를 사용합니다. 선물 피봇 포인트 계산이 필요하지 않은 경우라면 NQ-minis 와 ES-minis 해당 열을 통째로 삭제하셔도 되고 아니면 수동으로 하루 전 거래일의 선물 데이터(시가,고가,저가,종가)를 직접 입력하셔도 됩니다.

선물 피봇 포인트도 자동으로 업데이트 되게 하시려면 barchart ondemand 등록하시고 이메일로 받게 되시는 API key 라는 것을 다섯 번 째 시트에 입력 하시면 됩니다. 뒤에서 다시 간단하게 설명 드립니다.

두번 째 정보는 거시 경제 지표와 11개의 섹터 별 당일/5일/3개월/6개월/1년 변동 상황을 알려주는 표 들 입니다. 이 표 들의 정보는 현재 가격 대비 변동률을 보여주는데 예외로 5일 정보 옆의 미니 그래프만 전 거래일부터 5일 이전 까지의 그래프입니다 (미니 그래프는 현재 가격이랑 무관하다는 얘기입니다). 

세 번째는 개별 종목의 breadth 를 전체 마켓과 섹터 대표 ETF 에 대비하여 확인할 수 있는 그래프입니다. 위의 첫 번째 표에서 노란 셀에 입력한 개별 종목 틱커에 따라서 자동으로 11개 섹터 중에 해당 섹터를 발견하여 그래프를 그려 줍니다. 단 모든 종목에 대해서 섹터를 찾아주지는 않고 XLI, XLF, XLP, XLE, XLB, XLK, XLU, XLY, XLC XLV, XLRE 11개 섹터 ETF에 홀딩으로 포함된 종목들의 섹터 들만 찾아 줍니다. 

첫 번째 구글 시트의 마지막 정보는 첫 번째 표의 노란 셀에 입력한 개별 종목의 마켓에 대한 alpha, beta, r-squared, momentum score 를 계산해 줍니다. 만약 노란 셀에 입력한 개별 종목의 섹터를 발견했다면 개별 종목의 섹터에 대한 alpha, beta, r-squared, momentum score 도 계산해 줍니다.원래 이러한 정보는 년 단위(annualized basis)로 계산 되어야 하는데 여기서는 지난 3개월의 정보만 가지고 계산했기 때문에 지난 1년 단위 데이터로 계산된 값과는 조금 다를 수 있습니다. 오른쪽 그래프는 alpha, beta, r-squared 값 들을 계산하는 데 사용한 회귀(regression) 그래프입니다. 개별 종목으로 첫 번째 표의 노란 셀에 SPY, QQQ, TQQQ, PSQ 등등을 입력해보면 alpha, beta, r-squared 같은 값들이 어떻게 계산되는지 그래프로 쉽게 확인하실 수 있습니다.

두 번째 구글 시트에는 개별 종목의 지난 10년간 재무제표에서 중요한 키 팩트(Key ratios)들을 보기 쉽게 그래프로 그려 줍니다. 빈누님이라는 분이 원래 만드셨고 다른 카페의 정은지님이 수정한 내용에서 힌트를 얻어 만들었습니다. 원래 원본에서는 morningstar 라는 사이트에서 데이터를 자동으로 크롤링해서 업데이트가 되었었는데 morninstar 에서 데이터 크롤링을 블락하면서 메뉴얼로 데이터를 가져와야하는 번거러움이 생겼습니다. 두 번째 구글 시트의 맨 아래 쪽에 어떻게 morningstar 에서 데이터를 가져올 수 있는지에 대한 자세한 설명이 있습니다.






세 번째 구글 시트에는 몇몇 사이트에서 사용할 수 있는 틱커 들을 정리 했습니다. yahoo finance, GOOGLEFINANCE(), treadingview  에서 지수,선물 등등에 사용하는 틱커가 다 조금씩 달라서 정리해 두면 도움이 될 것 같았습니다.

네 번째  구글 시트에는 첫 번째 구글 시트의 첫 번째 표에서 노란 셀에 개별 종목을 입력했을 때 섹터를 자동으로 찾아주기 위해 사용하는 테이블이 있습니다. 또한 각각 섹터에서 탑 10 홀딩 종목을 위에 정리해놨기 때문에 만약 원하는 섹터의 대장 종목을 찾을 때 참고하실 수 있습니다.





다 섯번 째 구글 시트에는 첫 번째 구글 시트에서 표와 그래프를 계산하기 위한 데이터 및 계산식 표 들입니다. 

앞서 언급했던 첫 번째 구글 시트의 첫 번째 표에서 선물의 피봇 포인트가 제대로 동작하기 위해서는 아래 그림과 같이 이 곳에  barchart ondemand 에 등록 하고 받으신 자신의 API key 를 입력하셔야 합니다. 

마지막 여섯 번째 구글 시트는 두 번째 구글 시트에서 10 년간 재무제표 그래프들을 그리기 위해서 monringstar 에서 가져온 해당 종목의 원본 자료들 입니다. 두 번째 구글 시트의 아래 쪽에 제시한 설명서를 따라하시면 반 자동으로 morningstar 데이터를 가져오실 수 있습니다.

그럼 누군가에게는 도움이 되길 바랍니다.