Excel에서 HTS DDE 활용하기 5 - Chart와 지표 I

※ Elliott Pattern Helper Add In

• Download Add In for Excel 2007 ☞
• Download Add In for Excel 2003 ☞

  'Excel에서 HTS DDE 활용하기' 두 번째 시리즈를 시작합니다. 지난 글까지는 HTS의 DDE를 통해 수신한 체결정보를 5분, 15분, 60분 또는 일봉 캔들로 축적하기 위한 작업이었습니다.

  이번 시리즈에서는 축적한 캔들정보로부터 차트(chart)를 생성하고, 기술적 지표(technical indicator)를 계산하여 활용하는 과정을 몇 편에 걸쳐 진행하겠습니다. ※ 지난 글까지 완성한 파일을 그대로 사용합니다.

  Excel은 다양한 종류의 차트 기능을 제공하지만, 동적인 데이터를 처리하기에는 부족한 측면이 많으며, DDE를 통해 수신하는 데이터를 사용자가 수작업을 통해 차트에 반영하는 것은 무모할 뿐만 아니라 불가능에 가까울 것입니다.

  하지만, 약간의 Excel VBA 프로그래밍을 통해 많은 수고를 덜 수는 있습니다. 먼저 사용자가 버튼을 클릭하면 캔들 차트가 생성되는 간단한 시나리오를 테스트 해 보겠습니다. 

• (시나리오) 시트 위에서 종목명을 선택하고 'Chart' 버튼을 클릭하면 차트 출력 시트가 생성되면서 해당 종목의 캔들 차트가 표시된다.

  우선 다음 그림과 같이 '개발 도구' → '삽입' → '단추'를 클릭하여 적절한 위치에 버튼을 생성합니다. 

 

그림 1. 차트 생성을 위한 버튼 만들기

  버튼 이벤트에 대한 매크로 지정 창에서 매크로 이름을 'Chart_Click'으로, 위치를 현재 작업 중인 파일로 지정한 후, '새로 만들기'를 클릭합니다.

그림 2. 버튼 매크로 지정

  VBA 편집창이 뜨면 위에서 지정한 'Chart_Click' Sub모듈을 다음 그림과 같이 입력합니다.
 

그림 3. 'Chart_Click' 입력

  ※ ('Chart_Click' 소스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Chart_Click()
    Call doCharting(Selection.Cells(1, 1).Value)
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  ☞  'Selection.Cells(1, 1)'은 선택된 셀 또는 선택범위의 (첫째 행, 첫째 열) 셀입니다.

  'doCharting' Sub모듈을 호출만 하도록 했습니다. 그렇다면 'doCharting'을 작성했야 할텐데요, 그 전에 앞으로 사용할 차트 출력용 함수를 먼저 정의해 보겠습니다. 함수에 넘겨줄 인자를 정의한다고 하는 것이 옳은 표현이겠네요.

• 차트가 출력할 데이터는 반드시 지정해야 하겠지요. → Range
• 차트의 유형 또한 지정해야 합니다. → ChartType flag
• 차트의 크기는 사전에 설정한 값을 읽어들이도록 할 수도 있지만, 우선은 인자로 넘겨주는 것이 편할 듯 합니다. → Integer 값 (Width, Height)
• 차트는 ActiveSheet에 출력되는데 기본인데, 이를 변경해야할 필요가 있을지도 모르겠습니다. → String값

  우선 위의 다섯 개 항목을 인자로 받아 차트를 출력하는 함수 'drawChartTWH'를 작성하겠습니다. 혹시 인자를 늘려야 할지도 모르니 가변길이의 'ParamArray'를 사용하도록 합니다.

  ※ ('drawChartTWH' 함수 소스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function drawChartTWH(ParamArray varArgs() As Variant)
    Dim cht As ChartObject
    Dim chtSheet As Worksheet
   
    Set chtSheet = ActiveSheet
    If UBound(varArgs) > 3 Then
        If varArgs(4) = xlNullString Then GoTo UseActiveSheet:
        If Not sheetExist(varArgs(4)) Then
            Set chtSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            chtSheet.Name = varArgs(4)
        Else
            Set chtSheet = Sheets(varArgs(4))
        End If
UseActiveSheet:
    End If

    po_y = chtSheet.ChartObjects.Count * (2 * Rows("1:1").RowHeight + varArgs(3)) _
           + 2 * Rows("1:1").RowHeight
    Set cht = chtSheet.ChartObjects.Add(Left:=0, Width:=varArgs(2), _
                                        Top:=po_y + Rows("1:1").RowHeight, _
                                        Height:=varArgs(3))
   
    cht.Chart.SetSourceData Source:=varArgs(0)
    cht.Chart.ChartType = varArgs(1)
   
    Set chtSheet = Nothing
    Set cht = Nothing
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  ☞ 하이라이트 된 부분이 넘겨준 인자를 사용하는 곳입니다. (참고: 'drawChartTWH'의 TWH는 type, width, hight를 기본 인자로 받음을 의미합니다.)

  'doCharting' 모듈이  'drawChartTWH' 함수를 호출하도록 작성합니다. 그런데, 선택된 종목에 대해 여러 개의 캔들정보 시트가 존재할 수도 있을 텐데, 어느 시트의 데이터를 차트로 표시해야 할까요? 우선 선택된 종목의 캔들정보 시트명 목록(comma-separated)을 찾기 위해 다음 함수 'sheets_cs'를 사용하겠습니다.

  ※ (함수 'sheets_cs' 소스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Function sheets_cs(sn) As String
    For Each sh In ActiveWorkbook.sheets
        If sh.Name = sn & "-5분" Or sh.Name = sn & "-15분" _
            Or sh.Name = sn & "-60분" Or sh.Name = sn & "-일" Then _
            sheets_cs = sheets_cs & sh.Name & ","
    Next sh
   
    If Not (sheets_cs = Empty Or sheets_cs = "") Then _
        sheets_cs = Left(sheets_cs, Len(sheets_cs) - 1)
End Function
 
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  ☞ 선택된 종목의 캔들정보 시트명 목록을 함수값으로 리턴합니다.

  아래 소스는 'sheets_cs' 함수의 리턴값에 포함된 모든 시트에 대해 차트를 출력하도록 작성된 'doCharting' 모듈입니다.

  ※ (소스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub doCharting(sn)
    snArr = Split(sheets_cs(sn), ",")
    For i = LBound(snArr) To UBound(snArr)
        endRow = Sheets(snArr(i)).Cells(1, 1).End(xlDown).Row
        chtRng = Cells(1, 1).Address + ":" + Cells(endRow, 5).Address
        drawChartTWH Sheets(snArr(i)).Range(chtRng), xlStockOHLC, _
                    500, 300, "CHARTS"
    Next i
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

  ☞ 선택된 종목의 캔들정보 시트에 대해 'drawChartTWH'를 호출합니다.

  위 소스들을 복사하여 다음 그림과 같이 'Chart_Click' 모듈 아래에 입력합니다. 

그림 4. 'sheets_cs', 'doCharting', 'drawChartTWH' 작성

  이 시점에서 파일을 저장하고 일단 테스트를 해 보겠습니다. 아주 단순한 캔들 차트가 출력되는 것을 확인할 수 있습니다.

그림 5. 테스트

  ※ 다음 글은 차트 스크롤(scroll) 기능과 'one chart with multiple pages' 기능을 다룹니다.