본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

구글스프레드시트에서 국내주가 가져오기 변경 사항

 

국내 주가 정보 가져오기 문제 해결 @구글스프레드시트로하는 국내 주식 수익률 관리

오늘 오후 부로 전종목 주가를 제공하고 있던 다음의 finance-service.daum.net 이 없어졌습니다. 다음(Daum)이 제공하던 전 종목 주가 제공 페이지 이용하여 엑셀/구글스프레드시트로 자신의 주식을 관�

richardshin.tistory.com

구글 스프레드 시트에서 국내 주가를 가져오는 방법에 대하여 위에 포스팅에서 언급한 적이 있습니다.

두 가지 수정사항이 있습니다.

첫 번째는 수집을 담당하는 IMPORTXML 함수의 갯수와 ARRAYFORMULA 함수를 활용한 수식 수 최소화입니다. 주식 시트에서 실행 시키니 간혹 너무 많은 수식이 있어 가져올 수 없다고 하여 에러가 나곤 합니다.

두 번째는 주가의 업데이트 주기 단축 문제 입니다. 최근 까지 정상적으로 동작중에 있으나 단점으로는 서울경제의 주가의 업데이트 주기가 30분이고 구글의 IMPORTXML 함수의 실행 주기가 30분인 관계로 주가가 업데이트 되려면 1시간 정도가 걸립니다. 어느정도 최신 데이터를 받아 보려면 페이지 새로고침이나 함수를 수기로 업데이트 해줘야 합니다.

이 문제를 해결 하기 위하여 Google Apps Script를 이용하여 해당 함수를 업데이트해주는 간단한 프로그램을 작성하고 스케쥴러에 5분 만에 한번 씩 실행 되도록 하였습니다.

스프레드 시트는 위와 동일한 주소 입니다.

 

국내 주가 가져오기

국내주가 코스피 ,현재가,전일비,임시등락률,종목코드,시장,등락률,총라인수,2994 삼성전자,54,400,200,+0.37%,005930,코스피,0.37%,업데이트 일시,18:07:44 SK하이닉스,75,500,300,+0.40%,000660,코스피,0.40% NAVER,332

docs.google.com

 

파일 메뉴에서 사본 만들기로 개인 구글 드라이브에 복사하여 사용하셔도 좋습니다. 해당 시트는 누구나 볼 수 있으며 편집 가능한 시트라서 직접 실행도 됩니다.

개인 구글 드라이브로 복사해서 사용하기

수식의 갯수를 줄이기 위하여 변경된 부분

서울경제 예제에 적용된 수식에서 변경된 수식

1) 현재가, 전일비, 등락률(B~D 열)  B2 셀 수식

=IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=","//dl[@class='tbody']/dd")이전에는 dd/span[1] , dd/span[2] 와 같이 인덱스를 붙어 일일이 수집 하였으나 위의 수식으로 설정하니 span 의 갯수대로 옆으로 펼쳐지게 되어 3개의 셀(B~D)에 자동으로 채워지게 됩니다.

2) 종목코드 : E열

=ARRAYFORMULA(
   REGEXEXTRACT(
     IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=",
     "//dl[@class='tbody']/dt/a/@href")
   ,"[0-9A-Z]{6}")
)

IMPORTXML 함수의 결과 값은 배열입니다.
ARRAYFORMULA 는 각 배열(혹은 셀) 에 대하여 동일한 함수를 적용하는 함수라고 보시면 됩니다. 그래서 결과 값(종목코드가들어 있는 URL)의 배열에서 6자리 종목코드를 빼내기 위하여 REGEXEXTRACT( 배열, 정규식) 함수를 적용하여 종목코드만 빼내어 E2 부터 표시하게 됩니다. 원래는 IMPORTXML 함수를 사용하고 별도의 열에 REGEXEXTRACT 함수를 2994열에.일일히 수식을 설정 해줘야 하는데 종목의 갯수가 변할 때마다 수정하기는 번거로운 일이라 위와 같이 구현 하는 것이 맞습니다.

3) 시장 구분

=ARRAYFORMULA(IF( 
   IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=",
 "//dl[@class='tbody']/dt/@class")="ico_kospi","코스피","코스닥"))

시장구분에서도 마찬가지 입니다. IF 의 조건문으로 코스피, 코스닥 구분을 하는 기능을 배열 수식으로 적용하였습니다.

 

4) 등락률

=ARRAYFORMULA(IF(
   ISTEXT(INDIRECT("D2:D"&J1)),
     REGEXREPLACE(INDIRECT("D2:D"&J1),"[%]","")/100,
     D2:INDIRECT("D"&J1)
))

등략률을 별도의 열로 제외한 이유는 수집한 데이터에는 숫자가 아니라 문자가 포함되어 사용할 수 없기 때문입니다.

J1 은 =COUNTA(A:A) 제일 끝 라인의 줄 번호 이고 INDIRECT 는 문자를 수식으로 만들어 주는 엑셀 함수 입니다.

J1 에서 1을 빼면 총 종목 수가 되겠죠. 이게 상폐, IPO 등으로 가변이라 주가를 가져오면 자동적으로 구해 지는 숫자 입니다.

즉, INDIRECT("D2:D"&J1) 는 D2:D2994 와 같은 수식이 됩니다.

여기 까지는 스프레드 시트에서 변경된 함수를 설명 드렸습니다.

주가를 업데이트 하는 구글 앱 스크립트 프로그램

메뉴에 주가 업데이트를 만들고 3개의 메뉴를 만들었습니다.

주가 업데이트를 선택하면 실제 updateStockValue() 함수가 실행되어 해당 수식을 새로 설정하고 주가를 가져 옵니다. 나머지 2개는 5분 마다 한 번씩 실행 되도록 예약된 작업에 등록 시키거나 제거하는 기능 입니다.

큼직한 파란 버튼으로 버튼 도 만들어 두었습니다. 쓰리 땡땡땡을 누르면 스크립트 할당이 나오는데 여기다 함수인 updateStockValue 를 지정 할 수 있습니다.

실행 하려면 권한을 등록 시켜 줘야 합니다.

스크립트 소스는 도구 메뉴의 스크립트 편집기에서 볼 수 있습니다..

function updateStockValue() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet= spreadsheet.getSheetByName("국내주가");
     
  // 현재 날짜 
  //var dt = new Date();
  // 주식 시간 에만 
  //if(dt.getHours() <= 9 || dt.getHours() >= 18 ){  return true;}
  
  // 종목수 구하기
  //var n = sheet.getRange("J1").getValue();
  //if(!n) n= 2994;
  
  // 초기화
  sheet.getRange('A2').setValue("");
  sheet.getRange('B2').setValue("");
  sheet.getRange('E2').setValue("");
  sheet.getRange('F2').setValue("");
  sheet.getRange('G2').setValue("");  
 
  sheet.getRange('A2').setValue('=IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=","//dl[@class=\'tbody\']/dt")');
  sheet.getRange('B2').setValue('=IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=","//dl[@class=\'tbody\']/dd")');
  sheet.getRange('E2').setValue('=ARRAYFORMULA(REGEXEXTRACT(IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=","//dl[@class=\'tbody\']/dt/a/@href"),"[0-9A-Z]{6}"))');
  sheet.getRange('F2').setValue('=ARRAYFORMULA(IF(IMPORTXML("https://www.sedaily.com/Stock/Quote/Search?code=&text=&pageCount=","//dl[@class=\'tbody\']/dt/@class")="ico_kospi","코스피","코스닥"))');  
  sheet.getRange('G2').setValue('=ARRAYFORMULA( IF(ISTEXT(INDIRECT("D2:D"&J1)),REGEXREPLACE(INDIRECT("D2:D"&J1),"[%]","")/100,D2:INDIRECT("D"&J1)))');
  
  // 엑셀수식으로 해결하지 않는 경우 
  //sheet.getRange('G2').setValue('=ARRAYFORMULA( IF(ISTEXT(D2:D'+n+'),REGEXREPLACE(D2:D2994,"[\%]","")/100,D2:D'+n+'))');

  // 실행 될 때 까지 기다리기            
  Utilities.sleep(5000);

  // 종료 일시 찍기  
  var toDay = Utilities.formatDate(new Date() ,"GMT+9","HH:mm:ss");
  sheet.getRange('J2').setValue(toDay);  
  
};

위의 스프레드시트 수식과 Google Apps Script 소스를 복사하여 개인적으로 만들어 둔 시트가 있다면 활용하면 됩니다.

전에 포스팅한 국내 주식 수익률 관리 구글 스프레드 시트에도 업데이트를 해 두었으니 그 쪽 관리 시트의 소스를 찾아 봐도 될 듯 합니다.

 

구글 스프레드시트로 하는 주식 수익률 관리

예전에 엑셀로 만든 주식 수익률 관리를 구글 스프레드시트로 옮겨 봤습니다.Excel 소프트웨어가 필요하고...

blog.naver.com

국내 주식 수익률 관리에서와 동일하게 적용 하였습니다. 변경된 사항이 있다면 수정 해 놓겠습니다.

더 실시간 주가를 위해서는 네이버에서 전 종목 시세를 가져올 수 밖에 없는데 이런 경우에는 거래하고 있는 종목 별로 각각 주가를 구하는 수 밖에 없습니다.