[주식] 구글 스프레드시트에 googlefinance()로 ATR 을 한 셀에서 계산하는 방법
Google Finance 데이터를 활용한 주식 데이터 분석 공식
Google Sheets에서 주식 데이터를 분석하려고 GOOGLEFINANCE 함수를 사용해 본 적이 있나요? 이번 포스트에서는 특정 주식의 최근 14일간 데이터를 기반으로 고가, 저가, 종가 간 차이를 계산하고, 그 최대값의 평균을 구하는 공식을 소개합니다. 이 공식은 주식의 변동성을 간단히 파악하는 데 유용합니다.
공식 설명
다음은 Google Sheets의 C1 셀에 입력할 수 있는 공식입니다. B1 셀에는 분석하려는 주식의 티커(Ticker, 예: AAPL, GOOGL)를 입력하면 됩니다.
=AVERAGE(
ARRAYFORMULA(
QUERY(
TRANSPOSE(
ABS(
QUERY(
QUERY(
{
QUERY(
GOOGLEFINANCE(B1, "all", TODAY()-25, TODAY()),
"SELECT Col3, Col4 ORDER BY Col1 DESC LIMIT 14"
),
QUERY(
GOOGLEFINANCE(B1, "close", TODAY()-25, TODAY()),
"SELECT Col2 ORDER BY Col1 DESC LIMIT 14 OFFSET 1 LABEL Col2 'Closeyest'"
)
},
"SELECT Col1-Col2, Col1-Col3, Col3-Col2"
)
)
)
),
"SELECT max(Col"&JOIN(",max(Col", ROW(INDIRECT("A1:A14"))&")")
)
)
)
공식 동작 방식
- GOOGLEFINANCE: 지정된 티커(B1)의 최근 25일간 데이터를 가져옵니다. "all"은 고가(Col3), 저가(Col4) 등을 포함하며, "close"는 종가를 가져옵니다.
- QUERY: 최신 14일 데이터를 선택하고, 고가-종가, 고가-저가, 저가-종가의 차이를 계산합니다.
- ABS: 차이의 절대값을 구합니다.
- TRANSPOSE & MAX: 각 날짜별 최대 차이를 찾습니다.
- AVERAGE: 최대 차이값들의 평균을 계산합니다.
오차 발생 원인
공식을 테스트하면서 결과값이 약간 다르게 나오는 경우가 있을 수 있습니다. 특히, 전일 종가(Closeyest) 데이터에서 오차가 발생할 가능성이 있습니다. 이는 GOOGLEFINANCE 함수가 실시간 데이터를 가져오면서 종가 데이터의 기준 시점이나 업데이트 주기가 미묘하게 달라질 수 있기 때문입니다. 예를 들어:
- "close"로 가져온 종가는 장 마감 시점의 데이터를 반영하지만, "all"로 가져온 데이터와의 동기화가 완벽하지 않을 수 있습니다.
- 데이터 조회 시점에 따라 최신 데이터가 반영되지 않을 수도 있습니다.
해결 방법
- OFFSET 조정: 종가 데이터를 가져올 때 OFFSET 값을 확인해 최신 데이터를 정확히 매핑하도록 조정하세요.
- 데이터 새로고침: Google Sheets의 데이터를 강제로 새로고침(F5 또는 데이터 새로고침)하여 최신 데이터를 반영하세요.
- 대체 공식: 종가 데이터를 별도로 계산하지 않고, "all"에서 가져온 종가 데이터를 활용하면 오차를 줄일 수 있습니다.
마치며
이 공식은 주식의 단기 변동성을 간단히 분석하는 데 유용합니다. 다만, GOOGLEFINANCE 함수의 데이터 특성상 약간의 오차가 발생할 수 있으니, 중요한 투자 결정을 내리기 전에는 데이터를 꼼꼼히 검토하세요. 더 궁금한 점이 있다면 언제든 의견을 나눠요!
참고: 이 공식은 Grok의 도움을 받아 작성되었습니다. Grok은 Google Sheets 공식의 동작 원리를 분석하고, 오차 발생 가능성을 설명해 주는 데 큰 도움이 되었습니다!
Comments
Post a Comment