Home / 유용한 것들 / 구글 스프레드시트 활용팁 (10) – 과거 특정일자의 주가나 환율 등 불러오기

구글 스프레드시트 활용팁 (10) – 과거 특정일자의 주가나 환율 등 불러오기

먼저 항상 저에게 글감을 던져주시는 많은 분들께 감사의 말씀을 드립니다. ^_^ 오늘은 melon님이 댓글로 문의하신 내용을 알려드리고자 합니다.

‘혹시 구글 시트를 통해서 특정 종목에 대한 과거 특정일 종가 자료를 가져올 수 있는 함수가 있는지’를 물어보셨는데요. 방법이 있습니다! googlefinance 함수를 이용해서 종가 뿐만 아니라, 시가, 그날의 저가, 고가, 거래량을 가져올 수 있습니다.

 

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

예) GOOGLEFINANCE(“GOOG”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)

요약) Google Finance에서 현재 또는 과거 유가증권 정보를 가져옵니다.

 

*[attribute]

“open” – 지정한 날짜의 개장가입니다.

“close” – 지정한 날짜의 종가입니다.

“high” – 지정한 날짜의 최고가입니다.

“low” – 지정한 날짜의 최저가입니다.

“volume” – 지정한 날짜의 거래량입니다.

“all” – 위의 모든 속성입니다.

 

*[start_date] – [ 선택사항 ] – 과거 데이터를 가져올 기간의 시작일입니다.

– start_date만 지정하고 end_date|num_days를 지정하지 않을 경우 시작일 하루의 데이터만 반환됩니다.

*[end_date|num_days] – [ 선택사항 ] – 과거 데이터를 가져올 기간의 종료일 또는 데이터를 반환할 start_date로부터의 일수입니다.

 

*[interval] – [ 선택사항 ] – 반환 데이터의 실행 빈도로, “DAILY” 또는 “WEEKLY”입니다.

– interval을 1 또는 7로 지정할 수도 있습니다. 다른 숫자 값은 사용할 수 없습니다.

 

[start_date]나 [end_date] 항목에 대해 조금 추가 설명을 하자면, 일반적으로는 DATE(년도,월,일)과 같은 형식으로 쓸 수 있고, 오늘 날짜를 참조할때는 TODAY() 함수를 사용할 수 있습니다. TODAY()-365라고 쓰면 오늘 기준으로 1년전 날짜라는 뜻이 됩니다.

 

 

=googlefinance(“KRX:005930”,“close”,today()100,today(),“weekly”)

이런식으로 지난 100일간의 삼성전자의 종가를 주 단위로 불러올 수 있는 것이죠.

 

그럼 이제 어느 특정 일자 하루의 종가를 불러와볼까요?

 

A1셀에 있는 날짜를 참조하여 종가를 불러 왔습니다. 12월 31일은 주식 시장이 열리지 않은 날이라서, 그 이후 1번째 마감일의 종가를 보여주고 있습니다.

그런데 실제로 스프레드시트에서 데이터 가공을 많이 해보신 분은 이쯤에서 한가지 의문이 생길 수 있습니다. 나는 값을 1개만 불러오고 싶은데, 이건 2줄 x 2칸의 값이 나와버리니, 여러 종목, 혹은 여러개의 날짜에 대한 정보를 넣어놓고 함수식만 쭈루룩~~ 카피해서 쓰고 싶고, 그 값을 다른 함수식에 이용하고 싶기도 한데, 이렇게 나오면 그게 안되자나!? 라는 것이죠.

 

그래서 이럴때는 INDEX() 함수가 유용하게 쓰이게 됩니다.

 위의 구글파이낸스 함수가 총 불러오는 정보의 범위가 2×2 인데, 거기에서 2번째줄, 2번째칸의 값만 불러오는 index 함수를 사용하면 간단하게 해결됩니다.

 

종목코드 자리에 USDKRW과 같은 환율 코드를 넣으면 과거 특정 시점의 환율 등도 손쉽게 불러올 수 있겠죠?

 

이런 함수를 활용하면 해당 종목이 년초나 월초 같이 특정 시점에 비해서 얼마만큼 올랐거나 떨어졌는지도 쉽게 계산할 수 있습니다.

 

=googlefinance(A2)/index(googlefinance(A2,“close”,date(year(today()),1,1)),2,2)1

위의 함수에서 매년 첫째 일을 계산할때 date(2016,1,1) 라고 쓰면 향후 이 함수를 내년에도 계속 사용하려고 하면 문제가 되겠죠? 그래서 오늘 날짜를 불러오는 today() 함수와 날짜에서 년도만 불러오는 year() 함수를 이용했습니다. ^_^

이제는 과거 주가 정보를 위해서 HTS를 뒤지거나 네이버 금융 페이지를 뒤지는 노가다는 하지 않으셔도 되겠죠? 근데 이게 data가 아주 옛날까지는 없구요. 코스피 지수 기준으로 찾아보니 2003년부터 있답니다. 그보다도 더더욱의 과거 data가 필요하신 분은.. 호옥~시 계시면 제가 또 꿀팁을 풀어보지요. 다음 시간에…… ^-^

Check Also

구글 스프레드시트 활용팁 (9) – 신규상장 주식까지 반영되는 실시간 종목리스트 만들기 (importxml 함수 활용)

벌써 올린지 1년이 되어가는 이 글에서 우리나라 주식 시장에 상장되어있는 전 종목을 엑셀 화일로 다운받아 …

  • melon

    2*2범위를 불러와서 문의드린것인데 인덱스함수로 가능하군요…
    답변 감사합니다…

    • 또 궁금한것 있으시면 언제든 찾아주세요 ^-^

  • 올려주신글 참고하여 저도 제 포트폴리오 관리툴을 만들어봤는데 구글시트 써보니 활용이 여러가지로 가능할듯합니다. 좋은 정리 감사합니다

    • 많은 투자자님들께 유용한 툴을 전파하고 있는 것 같아서 뿌듯하네요! ^ㅁ^/

  • BHAN

    좋은 정보 감사드립니다.

    한가지 질문이 있어 댓글 드립니다.

    특정일 종가를 불러올때 해당 정보를 불러올 수 없다고 나오는 경우는 왜 그런건가요?

    구글파이낸스가 아닌 다른 곳에서도 정보를 불러올 수 있나요?

    궁금하네요..

    • 혹시 특정일이 휴일 등으로 증시가 열리지 않아서 정보가 없는 것일수도 있구요. 아니면 구글의 일시적인 문제일수도 있습니다.

      야후나 네이버/다음 웹사이트 등을 이용해서도 다양한 정보를 불러올 수 있으니, 블로그에 있는 강좌 시리즈를 한번 보시고 더 질문해주세요! ^^

  • 나그네

    etf 주가도 불러올 수 있나요?
    대부분 0으로 불러와지네요ㅠ
    감사합니다~

    • 요즘 etf 관련해서는 data에 문제가 있는 모양입니다.

      제가 예전에 올려놓은 이 글에 링크되어있는 구글 시트에 우리나라 상장기업(ETF/ETN 포함) 전체의 정보와 현재가까지 만들어두었으니 필요하시면 가져다가 쓰세요. 🙂
      http://financialfreedom.kr/979

  • 민서아빠

    일자별로 주가를 불러 올때 우리나라 일자와 차이가 있는 것 같습니다. 예컨데 10월 20일차 현대백화점 시초가를 불러오니 123,000원으로 21일 시초가가 불러 와 지네요. 다른 종목 다른 날짜도 마찬가지로 하루 텀이 있는데, 원래 그런것인지, 아님 제가 잘못한 것인지 궁금합니다.

    • 확인된바는 없습니다만 날짜를 미국 서버 기준으로 하는건 아닐까 하는 생각이 듭니다.

      date(2016,10,20)을 시작하는 날짜로 넣는 경우 미국 종목들은 2016/10/20 데이터부터 나오는데, 우리나라 종목은 2016/10/21 데이터부터 나오네요.

      2016/10/20 데이터부터 필요하시다면 2016/10/19일부터 가져오는 방식으로 하시는게 좋겠습니다.

  • 티거

    항상 좋은 정보 감사합니다 혹시 기간을 설정한 뒤 기간중 최고가도 검색이 가능한가요?
    아니면 특정일 이후 최고가라도 검색이 가능한지 궁금합니다

    • 불러오고자 하는 날짜의 주가를 쭉 불러온뒤 max 함수를 사용하면 되지 않을까요? ^_^

  • 별붕어

    매우매우감사합니다

    • 빈누

      별말씀을요.

  • 안녕하세요?
    질문드리기도 부끄럽지만..
    “=GOOGLEFINANCE(“GOOG”, “price”, DATE(2014,1,1), DATE(2014,12,31), “DAILY”)”
    이렇게 구글스프레드에 붙여넣기 하면 에러라고 나오는데 잘못한건가요?
    구글스프레드를 사용할줄 몰라서요..
    답변 부탁드립니다.
    감사합니다.!!

  • 위에 질문드린 사람입니다.
    “수식파싱오류가 발생하였습니다.”라고 나옵니다.
    어떻게 해결해야 하는지요?

    • 빈누

      붙여주신 수식을 보면 따옴표가 일반 따옴표가 아니고 특수기호(?) 같은 것으로 되어있습니다. 따옴표를 키보드에 있는 따옴표로 다 바꿔주시면 정상 작동할거에요.

      • 답변감사드립니다. 어제 직접 타이핑을 해보니 됐었는데, 그래서 그랬군요^^