본 포스팅에 사용되는 함수 :: IMPORTXML, IMPORTRANGE, IMPORTDATA, ARRAYFORMULA, SUBSTITUE, LEFT, RIGHT, CONCAT, IF, IFERROR, VLOOKUP, GOOGLEFINANCE, TODAY, SUM, SUMPRODUCT, UNIQUE, ISBLANK
본 포스팅에 사용되는 Script :: Copy Column
- 데이터 유지시키기[1]
이번에는 개념이 좀 복잡합니다.
이게 하나하나 맞춰가면서 순환식으로 만들다보니 다 만들어놓고 설명하기 상당히 애매하지만...
우선 새로운 시트로 backdata1을 만듭니다.
- 종목명 백데이터
backdata1!C2
=UNIQUE(ARRAYFORMULA(backdata!A2:A))
|
ArrayFormula 의 사용의도는 이제 다들 아시겠죠?
Unique함수는 아래 스크립트로 복사를 매일 하다보면 중복데이터가 생기기 때문에 그걸 방지하기 위해 넣습니다.
그런데 backdata!A2:A 에서 자료를 가져오는 이유가 궁금하시겠죠.
backdata!A2의 함수는 [=ARRAYFORMULA(IF(F2:F="",backdata1!A2:A,F2:F))]입니다.
backdata!F2:F 는 데이터를 받아오자마자 나오는 종목명입니다. 이게 아침마다 사라지거든요. 그리고 평일 오전 9시에 데이터가 다시 나오는데 그 땐, 종목들이 있는게 있고 없는게 없어서, 모든 데이터가 리셋되기 때문에, 잔고에 오류가 납니다.
그래서 backdata!F2:F 값이 없으면, backdata1!A2:A 를 사용하고,
backdata!F2:F 값이 있으면, backdata!F2:F 를 그대로 사용하겠다는 함수입니다.
backdata1!A2:A 는 전일 종가를 백업한 데이터입니다.
- 현재가 백데이터
backdata1!D2
=ARRAYFORMULA(IF(backdata!O2:O="0.00",IFERROR(VLOOKUP(C2:C,A:B,2,0),"0.00"),backdata!O2:O))
|
backdata!O2:O 는 데이터를 받자마자 나오는 현재가입니다. 위와 마찬가지로 매일 오전 사라지기때문에 같은 이치로서,
backdata!O2:O 값이 0이면, IFERROR(VLOOKUP(C2:C,A:B,2,0),"") 함수를 사용하고,
backdata!O2:O 값이 0이 아니면, backdata!O2:O 을 사용한다는거에요.
IFERROR(VLOOKUP(C2:C,A:B,2,0),"") 함수 데이터에서 불러온 현재가가 없으면 백업한 데이터에서 종목명이 맞는것을 찾아 가져오라는 함수로서. Vlookup 함수가 에러가 나면 빈칸으로 놔두라는 함수입니다.
- 그리고 backdata 시트 A와 B를 사용합니다.
A열은 종목명 B열은 현재가로 합니다.
backdata!A2
=ARRAYFORMULA(IF(F2:F="",backdata1!A2:A,F2:F))
|
F2:F 값이 없으면, backdata1!A2:A 값을 가져오고,
F2:F 값이 있으면, F2:F를 그대로 사용한다는 함수입니다.
backdata!B2
=ARRAYFORMULA(backdata1!D2:D)
|
- 데이터 유지시키기[2]
App Script 범위 지정 복사로 종목명과 현재가데이터를 backdata1!A:A, backdata1!B:B 에 매일 장마감 후 복사하도록 설정합니다.
여기까지 하신다면 모든 자료들은 에러없이, 과거 마지막 가격을 계속 유지할 것입니다.
이제 가격이 연결되지 않는 상황은 막았네요~!
이제 진정 필요했던 기능들을 만들러 가볼게요!!
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [Prologue]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(1)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(3)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [주식 리스트]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [전자화폐]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [종합잔고]