본문 바로가기

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

기업 분석 보고서 등 몇가지 기능 추가 @구글스프레드시트 주식 수익률 관리

이전에 포스팅한 

구글스프레드시트 주식 수익률 관리 - 수정사항 

http://richardshin.tistory.com/60?category=769676


에서 몇가지 기능을 추가하여 재 정리하여 포스팅 합니다. 
 

1) 기업 보고서 체크 기능 

   한국경제에서 제공하는 컨센서스 부분의 URL 이 변경되어 수정
   새 기업 보고서가 나오면 컨센서스 보고서에 날짜가 나오고 상향/하향 인지 표시 

2) 기업분석관련 FN가이드 연결

 
3) 초기화면 기간전체손익( 기간 별 손익 분석) 오동작으로 잘못된 참조 수정
 

4) 네이버 개별종목 보고서 추가 = 2018-08-13 추가
 



컨센서스 쉬트 구현 원리 


컨센서스 쉬트의 AB2 ~ AE2 까지의 수식을 설명 드리자면
AB2 : AC2 에서 정규식으로 종목코드를 빼냄 =REGEXEXTRACT(AC2,"[0-9]{6}") AC2 : Xpath 명령어로 종목코드 URL 을 가져와 나열 =IMPORTXML("http://finance.naver.com/research/company_list.nhn", "//table[@class='type_1']/tr/td[1]/a/@href") AD2 : 레포트 URL 가져와 나열 =IMPORTXML("http://finance.naver.com/research/company_list.nhn", "//table[@class='type_1']/tr/td[2]/a/@href") AE2 =IMPORTXML("http://finance.naver.com/research/company_list.nhn", "//table[@class='type_1']/tr/td[5]")
XML 의 Xpath 명령어로 해당 조건에 맞는 데이터를 쏙 빼내와서 나열 합니다.
AB2에 종목코드를 추출하고 추출된 종목코드를 기반으로 종합 시트에서 참조 합니다.
AD2 는 레포트 URL 만 쏙 빼옵니다
 
가져오는 XPath 명령어를 설명 드리면 
importhtml 을 웹페이지에 적용 하면 HTML 코드를 XML 코드로 인식 하여 동작 합니다. 
 
HTML 코드 상에 table 의 속성이 type_1 인 테이블 밑에 TR tag 밑에 TD tag 중 2번째 TD tag 밑에 Ahtml tag 를 선택 하면 @href 속성을 취하겠다는 소리 입니다. 
 
종합 탭의 수식은 다음과 같아서 종목 리포트가 네이버에서 발생하면 
 
=IFERROR(IF(MATCH(TRIM(AE2),'컨센서스'!AB:AB,0)>0,HYPERLINK("https://finance.naver.com/research/"&INDEX('컨센서스'!AD:AD,MATCH(TRIM(AE2),'컨센서스'!AB:AB,0)),INDEX('컨센서스'!AE:AE,MATCH(TRIM(AE2),'컨센서스'!AB:AB,0))),"") ,"")
클릭 하면 해당 리포트의 창이 열립니다. 
 
한경 컨센서스와 함께 참조 하면 편하게 종목 리포트를 볼 수 있겠지요.
 
 

2018년 9월 22일 수정사항

매수/매도 내역 입력 기능에서 매수 매도 날짜를 변경할 수 있도록 수정
 

2018-10-10 일 수정사항

● 영역이 설정 되지 않는 문제점 발견!
1) 종합 탭은  마지막 행(합계) 2 행 앞에 삽입하는 것으로 일단 조치 함.  
2) 매수 및 매도 탭 문제  
매수 및 매도 탭에서는 라인 수가 부족한 경우 AppendRow로 입력된 부분에 영역이 적용 안되는 문제가 있어 다음과 같은 코드로 영역을 임의로 변경. 

// 매수시트에 추가 
buySheet.appendRow(newLine); 
// 영역 수정 
var namedRg = ss.getNamedRanges(); for(var i=0; i< namedRg.length ; i++){ var nm = namedRg[i].getName(); var rg = namedRg[i].getRange(); if(nm.indexOf("매수")==0) { var c = rg.getColumn(); // 컬럼 
ss.setNamedRange(nm, buySheet.getRange(2,c, lastRow-1,1) ); } }


● "다음증권" 전 종목 시세 페이지 변경으로 인한 수정

 다음주가 탭에 종목명만 남기고  모두 구글 시세로 수정 
 
종합 탭  D열 : 등락률
=GoogleFinance(AE2, "changepct")/100
종합 탭  E열 : 주가 
=GoogleFinance(AE2, "price")
 
2018-10-16일 수정사항
코스피 지수 관련 사항 수정

국내 제공 "다음"의 페이지가 변경 되는 바람에 구글 파이낸스로 변경 하게 되었습니다.
일별 기록은 네이버 증권에서 가져오고 있습니다.
 
 

2018-11-01 

다시 주가 데이터 다음 증권의 데이터로 수집


서버 주소가 finance.daum.net 에서 finance-service.daum.net 으로 변경 
임시 방편임 다시 변경되면 구글 시세를 이용할 수 밖에 없음




52주 최대가 구하기 - 종합 탭에 추가 - 2018-11-27 

=QUERY(googlefinance(AJ2,"close",today()-365,today(),"DAILY"), "select Col2 Order by Col2 DESC limit 1 label Col2''",1)
엑셀에는 없는 구글스프레드 만의 함수 입니다. 데이타 베이스를 다루는 SQL 언어를 접목시킨 건데요.
select 는 열을 선택하다  
order by 는 col2 를 정렬 하는데 
desc 역순으로 정렬하고  
limit 1개의 값만 취합니다. 
Label Col2 
    열이름이 Col2 인
    Col2하고 '' 를 붙여 줘야 합니다.  
마지막 1은 헤더의 수입니다 2 를 지정하면서 3행 부터가 데이터로 잡힙니다.
 
※참고 
 
 

주식/펀드 관리 쉬트로 가기

 
 
 

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