엑셀 실시간 주식 차트 만들기

실습 가이드

이번 강의에서는 아래 2가지 사용자 추가기능 함수를 사용합니다.

각 함수에 대한 자세한 사용법은 위 링크를 참고하시기 바랍니다. 두 함수와 더불어 네이버 증권에서 제공하는 다양한 주식 정보를 실시간으로 조회할 수 있는 NaverFinanceSearch 함수를 함께 사용하면 주식 분석을 한층 편리하게 진행할 수 있습니다.

예제파일에는 일자별 주가 데이터가 미리 입력되어 있습니다. 이 데이터를 기준으로 5일, 20일, 60일 이동평균선을 추가합니다. 이동평균선은 AVERAGE 함수로 간단히 계산할 수 있습니다.

1. 예제파일의 I4셀을 선택합니다. 가장 최근 날짜를 기준으로 5일치 평균을 계산하기 위해 I4셀에 아래 수식을 입력합니다. 이동평균은 '종가'의 평균을 계산합니다.
   =AVERAGE(F4:F8)

   

2. J4셀과 K4셀에는 각각 20일, 60일치 이동평균을 구하기 위해 아래 수식을 입력합니다.
   J4셀 : =AVERAGE(F4:F23)
   K4셀 : =AVERAGE(F4:F63)

   

3. 입력한 수식을 아래 방향으로 자동 채우기 합니다.

   

4. 5일 이동평균선의 마지막 값을 살펴보면, 데이터의 마지막 날 기준으로 평균을 계산할 범위가 부족하여 이동평균이 정확히 반영되지 않은 것을 확인할 수 있습니다.

   

5. 따라서 5일선은 마지막 4개 항목, 20일선은 마지막 19개 항목, 60일선은 마지막 59개 항목을 삭제하여 올바른 이동평균선만 남겨둡니다.

   

엑셀 주식 차트는 2010 이후 버전 사용자라면 손쉽게 만들 수 있습니다. 다만 주식 차트를 만들기 위해서는 차트에 사용되는 데이터가 다음 2가지 조건을 만족해야 합니다.

• 데이터의 첫 번째 열은 반드시 '날짜'로 구성되어야 합니다.
• 주식 차트에서 요구하는 순서대로 항목을 입력해야 합니다. 주식 차트로 만들 수 있는 데이터 항목 순서는 다음 4가지입니다.
  a. 고가-저가-종가
  b. 시가-고가-저가-종가
  c. 거래량-고가-저가-종가
  d. 거래량-시가-고가-저가-종가

이제 예제파일에 입력된 데이터를 바탕으로 주식 차트를 직접 제작해보겠습니다.

1. 주식 차트로 생성할 데이터 범위를 선택합니다. [B3:F129] 영역의 [날짜|시가|고가|저가|종가] 범위를 모두 선택한 뒤, [삽입] - [추천 차트]로 이동합니다.

   

2. [차트 삽입] 대화상자에서 [모든 차트] 탭을 선택한 뒤, [주식형] - [시가-고가-저가-종가] 차트를 생성합니다.

   

3. 차트의 가로축을 우클릭하여 [축 서식]으로 이동한 뒤, 축 종류를 '텍스트 축'으로 변경합니다. 이어서 [항목을 거꾸로] 옵션 체크박스를 활성화합니다.

   

4. 차트 옵션 중 '양선' 항목으로 이동합니다.

   

5. '양선'의 채우기 색상은 '빨간색', 테두리는 '없음'으로 변경합니다.

   

6. '음선'의 채우기 색상은 '파란색', 테두리는 '없음'으로 변경합니다.

   

7. 계열 항목 중 '시가'를 선택한 뒤, 계열 옵션에서 간격 너비를 30%로 변경합니다.

   

8. 현재 차트 세로축의 최소값이 0으로 설정되어 있어 주가의 변동을 확인하기 어렵습니다. 차트 세로축을 우클릭한 뒤 [축 서식]으로 이동하여 축의 최소값을 실제 데이터의 최소값에 가까운 18,000 정도로 변경합니다.

   

9. 이로써 주식 기본 차트가 깔끔하게 완성되었습니다.

   

1. 차트를 우클릭한 뒤 [데이터 선택]으로 이동하고, 범례 항목(계열)에서 [추가] 버튼을 클릭합니다.
   
2. 아래 표를 참고하여 계열 이름과 계열 값을 각각 입력합니다. 같은 작업을 반복하여 5일선, 20일선, 60일선의 데이터를 모두 추가합니다.

   

   계열이름	계열 값
   =주식차트!$I$3	=주식차트!$I$4:$I$129
   =주식차트!$J$3	=주식차트!$J$4:$J$129
   =주식차트!$K$3	=주식차트!$K$4:$K$129

3. 새로운 데이터 계열이 추가되면서 기존 주식 차트의 모양이 변경됩니다. 다시 차트를 우클릭한 뒤 [데이터 계열 서식]으로 이동합니다.

   

4. 계열 항목 중 '5일선'을 선택한 뒤, [데이터 계열 지정]의 값을 기본축 → 보조축 → 기본축 순서로 변경합니다. '20일선'과 '60일선'도 동일하게 보조축으로 변경했다가 다시 기본축으로 되돌립니다.

   

5. 차트가 원래 모양으로 돌아온 것을 확인했다면 다시 [데이터 계열 서식]으로 이동합니다.

   

6. 계열 항목에서 '5일선'을 선택한 뒤, 선은 '실선', 채우기 색상은 '초록색', 너비는 1pt로 변경합니다. '20일선'과 '60일선'도 동일하게 실선으로 설정하고 색상을 구분해 변경합니다.

   

7. 이로써 주식 차트에 이동평균선이 추가되었습니다.

   

1. 차트를 우클릭한 뒤 [데이터 선택]으로 이동합니다. 범례 항목(계열)에서 [추가] 버튼을 클릭하여 거래량 데이터 계열을 추가합니다.

   

   계열이름	계열 값
   =주식차트!$G$3	=주식차트!$G$4:$G$129

2. 차트를 우클릭한 뒤 [데이터 계열 서식]으로 이동합니다. '계열 옵션'에서 거래량을 선택한 뒤, 데이터 계열 지정 값을 '보조축'으로 변경합니다.

   

3. 차트의 [+] 버튼을 클릭한 뒤, [차트 요소] - [축] - [보조 가로]를 추가합니다.
   
4. 보조 가로축을 우클릭한 뒤 [축 서식]으로 이동합니다. 앞서 진행한 작업과 동일하게 축 종류를 '텍스트 축'으로 변경하고 '항목을 거꾸로' 체크박스를 활성화합니다.

   

5. 보조축 설정을 마쳤다면 [차트 요소]에서 [보조 가로축]을 다시 숨겨줍니다.

   

6. 거래량 차트를 우클릭한 뒤 [데이터 계열 서식]으로 이동하여 거래량 선을 얇은 회색 실선으로 변경합니다.

   

7. 주식 변동량과 거래량 차트가 겹쳐 있어 값을 확인하기 어렵습니다. 거래량을 나타내는 보조 세로축을 우클릭한 뒤 [축 서식]으로 이동하여 최대값을 기존 최대값의 약 2.5배 수준으로 변경합니다.

   

8. 보조 세로축 설정 중 '레이블' 항목을 '없음'으로 변경하여 보조 세로축을 숨겨줍니다.

   

9. 마지막으로 차트 제목과 범례 항목 중 불필요한 항목을 삭제합니다. 범례를 위쪽으로 이동하여 주식 차트 만들기 작업을 마무리합니다.

   

NaverFinanceHistory 함수로 특정 종목의 주식 정보를 실시간으로 받아오는 경우, 종목과 기간에 따라 가격 범위가 매번 달라져 차트 세로축의 최소값과 최대값을 수동으로 조정하기 번거로울 수 있습니다. 이러한 상황에서는 아래 명령문을 엑셀 파일에 추가하면 세로축의 최소값과 최대값이 자동으로 갱신됩니다.

아래 명령문 사용 방법은 영상강의 21:20부터 자세히 설명해드렸습니다.

Sub UpdateChart()
 
On Error Resume Next
 
Dim WS As Worksheet
Dim Cht As ChartObject
Dim i As Long: Dim cEnd As Long: Dim cVol As Long
Dim pMax As Double: Dim pMin As Double: Dim vMax As Double: pMin = 1000000000
Dim sEnd As String: sEnd = "F:F"   '<- 종가가 입력된 열을 입력하세요.
Dim sVolume As String: sVolume = "G:G"   '<- 거래량이 입력된 열을 입력하세요.
 
Set WS = ActiveSheet
Set Cht = WS.ChartObjects(1)
 
WS.Calculate
 
cEnd = WS.Range(sEnd).Column
cVol = WS.Range(sVolume).Column
 
For i = 1 To WS.UsedRange.Row + WS.UsedRange.Rows.Count - 1
    If WS.Cells(i, cEnd) > pMax And IsNumeric(WS.Cells(i, cEnd)) And WS.Cells(i, cEnd) <> "" Then pMax = WS.Cells(i, cEnd)
    If WS.Cells(i, cEnd) < pMin And IsNumeric(WS.Cells(i, cEnd)) And WS.Cells(i, cEnd) <> "" Then pMin = WS.Cells(i, cEnd)
    If WS.Cells(i, cVol) > vMax And IsNumeric(WS.Cells(i, cVol)) And WS.Cells(i, cVol) <> "" Then vMax = WS.Cells(i, cVol)
Next
 
Cht.Chart.Axes(xlValue).MaximumScale = pMax * 1.1
Cht.Chart.Axes(xlValue).MinimumScale = pMin * 0.7
Cht.Chart.Axes(xlValue, xlSecondary).MinimumScale = 0
Cht.Chart.Axes(xlValue, xlSecondary).MaximumScale = vMax * 2.5
 
Application.Calculation = xlCalculationManual
 
End Sub

엑셀 NaverFinanceHistory 함수는 네이버 증권에서 제공하는 주식 정보를 실시간으로 받아오는 함수입니다. 따라서 계산 방식을 자동으로 설정한 상태에서 많은 양의 데이터를 받아오면 주식 정보와 직접 관련이 없는 다른 셀을 변경할 때에도 인터넷에 접속하여 값을 다시 받아오므로 처리 속도가 느려지는 문제가 발생할 수 있습니다.

뿐만 아니라 불필요한 데이터 요청이 지속될 경우 네이버 측에서도 서버 리소스를 과도하게 사용하게 되는 문제가 생길 수 있습니다.

따라서 NaverFinanceHistory 함수로 주식 정보를 실시간으로 받아온다면, 엑셀 환경설정에서 계산 방식을 '수동'으로 변경한 뒤 필요한 시점에만 값을 갱신하는 방식으로 사용하시기를 권장합니다.