[주식] 구글 스프레드시트에 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"))&")")
    )
  )
)

공식 동작 방식

  1. GOOGLEFINANCE: 지정된 티커(B1)의 최근 25일간 데이터를 가져옵니다. "all"은 고가(Col3), 저가(Col4) 등을 포함하며, "close"는 종가를 가져옵니다.
  2. QUERY: 최신 14일 데이터를 선택하고, 고가-종가, 고가-저가, 저가-종가의 차이를 계산합니다.
  3. ABS: 차이의 절대값을 구합니다.
  4. TRANSPOSE & MAX: 각 날짜별 최대 차이를 찾습니다.
  5. AVERAGE: 최대 차이값들의 평균을 계산합니다.

오차 발생 원인

공식을 테스트하면서 결과값이 약간 다르게 나오는 경우가 있을 수 있습니다. 특히, 전일 종가(Closeyest) 데이터에서 오차가 발생할 가능성이 있습니다. 이는 GOOGLEFINANCE 함수가 실시간 데이터를 가져오면서 종가 데이터의 기준 시점이나 업데이트 주기가 미묘하게 달라질 수 있기 때문입니다. 예를 들어:

  • "close"로 가져온 종가는 장 마감 시점의 데이터를 반영하지만, "all"로 가져온 데이터와의 동기화가 완벽하지 않을 수 있습니다.
  • 데이터 조회 시점에 따라 최신 데이터가 반영되지 않을 수도 있습니다.

해결 방법

  1. OFFSET 조정: 종가 데이터를 가져올 때 OFFSET 값을 확인해 최신 데이터를 정확히 매핑하도록 조정하세요.
  2. 데이터 새로고침: Google Sheets의 데이터를 강제로 새로고침(F5 또는 데이터 새로고침)하여 최신 데이터를 반영하세요.
  3. 대체 공식: 종가 데이터를 별도로 계산하지 않고, "all"에서 가져온 종가 데이터를 활용하면 오차를 줄일 수 있습니다.

마치며

이 공식은 주식의 단기 변동성을 간단히 분석하는 데 유용합니다. 다만, GOOGLEFINANCE 함수의 데이터 특성상 약간의 오차가 발생할 수 있으니, 중요한 투자 결정을 내리기 전에는 데이터를 꼼꼼히 검토하세요. 더 궁금한 점이 있다면 언제든 의견을 나눠요!

참고: 이 공식은 Grok의 도움을 받아 작성되었습니다. Grok은 Google Sheets 공식의 동작 원리를 분석하고, 오차 발생 가능성을 설명해 주는 데 큰 도움이 되었습니다!


Comments

Popular posts from this blog

Q > 4M 변경과 ECR 의 차이는?

[Ontology] Protege 의 설치

[리튬 배터리] 공정에 대해서