본 포스팅에 사용되는 함수 :: IMPORTXML, IMPORTRANGE, IMPORTDATA, ARRAYFORMULA, SUBSTITUE, LEFT, RIGHT, CONCAT, IF, IFERROR, VLOOKUP, GOOGLEFINANCE, TODAY, SUM, SUMPRODUCT, UNIQUE, ISBLANK
본 포스팅에 사용되는 Script :: Copy Column
- 전환가
저와 같이 투자하시는분이시라면 모두 전환사채를 가지고 계실거라고 봅니다.
해서 전환가와 관련된 정보가 없으면 제가 가진 종합잔고는 2%부족해지겠죠…?
우선 전환가를 불러오는 페이지에 대한 URL을 뽑고, 그 URL주소로 전환가를 불러오는 방식으로 합니다.
- 전환가 불러오는 URL
backdata!Q2
=ARRAYFORMULA(IF(RIGHT(A2:A,2)="CB",CONCAT("http://open.shinhaninvest.com/product/bond/popup_issue_if2.jsp?stbd_code=",backdata1!F2:F),""))
|
[GoogleSpreadsheet] - 구글 스프레드시트 :: ArrayFormula
[GoogleSpreadsheet] - 구글 스프레드시트 :: IF, IFERROR
ArrayFormula는 이제 다들 아시겠죠?
IF(RIGHT(A2:A,2)="CB",CONCAT("http://open.shinhaninvest.com/product/bond/popup_issue_if2.jsp?stbd_code=",backdata1!F2:F),"")
위 함수는 backdata!A2:A 우측에 CB가 있으면 위 URL우측에 backdata1!F2:F 값을 불러오고,
backdata!A2:A 우측에 CB가 없으면 칸을 비워놓겠다는 함수입니다.
backdata1!F2:F 값은 아래 설명드리겠습니다.
- 전환가 불러오기
backdata!N2
=IF(RIGHT(A2:A,2)="CB",IMPORTXML(Q2:Q,"//table[1]/tbody/tr[1]/td[2]"),"")
|
위 함수는 backdata!A2:A 우측에 CB가 있으면, backdata!Q2:Q 의 URL에서 //table[1]/tbody/tr[1]/td[2] Xpath-Query에 대한 값을 불러오고,
backdata!A2:A 우측에 CB가 없으면, 빈칸으로 남겨놓겠다는 함수입니다.
- 종목코드 백업 [1]
이 전에 종목명과 현재가를 매번 백업해야했던 이유와 같이 종목코드 또한 백업해야만 합니다.
backdata1!F2
=UNIQUE(ARRAYFORMULA(IF(backdata!G2:G="",IFERROR(VLOOKUP(C2:C,A:E,5,0),""),backdata!G2:G)))
|
Unique함수는 아래 스크립트로 백업을 하다보면 중복되는 데이터를 걸러주기위해 넣습니다.
ArrayFormula는 … 이제 다들 아시겠죠?
=UNIQUE(ARRAYFORMULA(IF(backdata!G2:G="",IFERROR(VLOOKUP(C2:C,A:E,5,0),""),backdata!G2:G)))함수는
종목코드 backdata!G2:G 값이 비어있으면, IFERROR(VLOOKUP(C2:C,A:E,5,0),”") 함수를 사용하고,
backdata!G2:G 값이 있으면 그대로 사용한다는 함수입니다.
IFERROR(VLOOKUP(C2:C,A:E,5,0),"") 함수는 backdata1!C2:C (종목명)값을 백업한 데이터에서 검색 후 그에 해당하는 종목코드를 가져오라는 함수로서, 이게 에러가 나면 비워두라는 함수입니다.
- 종목코드 백업 [2]
스크립트를 하나 더 구성해서 범위를 따로 지정해줘야 합니다.
해서 트리거도 두개가 됩니다.
- 전환가 편히보기
backdata!C2
=ARRAYFORMULA(N2:N)
|
backdata 시트의 맨 앞에 그냥 모아놓습니다.
- 현재가 서식
backdata! B열 H열 O열, backdata1! B열 D열의 (현재가)서식은 아래와 같이 해야 합니다.
참고
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [Prologue]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(1)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(2)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [주식 리스트]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [전자화폐]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [종합잔고]