Excel에서 HTS DDE 활용하기 4 - 데이터 축적 II

※ Elliott Pattern Helper Add In

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

  지난 글('Excel에서 HTS DDE 활용하기 4 - 데이터 축적 I)에서는 선택된 종목 하나에 대해서만 캔들정보를 축적하도록 했었습니다. 여러 종목에 대해 동일한 작업을 하도록 하는 것도 당연히 가능하겠지요?

  이번 글의 목표는 현재까지 완성된 파일을 수정하여 다수 종목의 데이터를 축적하게끔 하는 것입니다. 즉, 시나리오는 다음과 같습니다.

• 사용자가 UserForm('사용자 정의 폼')을 통해 다수의 종목을 선택하고, 스케줄 시각을 지정하면, 지정된 시간 동안 선택된 종목들의 캔들정보가 축적된다. 

  한 개의 종목에 한해서만 수행되던 폼과 Sub모듈 및 함수들을 어떻게 수정하면 될까요? 우선, 폼이 여러 개의 종목을 선택할 수 있게끔 변경되어야 겠군요. 지금까지 우리가 작업해 왔던 폼을 보겠습니다.

  

그림 1. 'DDE Schedule' 폼

  스케줄 시작 및 종료 시각을 지정하는 콤보 상자 두 개와 캔들의 시간단위를 선택하는 콤보 상자는 수정하지 않고 그대로 사용해도 될 듯합니다. 

  종목 선택을 위한 콤보 상자는, 단일 종목만을 선택할 수 있으므로, 다중선택(multiselection)이 가능한 목록 상자(ListBox)로 수정해야 하겠습니다.

  '개발 도구' → 'Visual Basic'을 선택하거나 'Alt + F11'을 눌러 VBA 편집창을 활성화시킨 후 폼의 '개체 보기' 모드로 들어갑니다. 다음 그림과 같이 다중 페이지의 Context 메뉴 → '새 페이지'를 선택하여 페이지를 추가합니다.

  

그림 2. 'DDE Schedule' 폼의 페이지 추가

  다음 그림 3과 같이 새 페이지에 목록 상자(ListBox)를 추가하고 이름을 'ListBoxSN'으로 합니다. 목록 상자 앞의 레이블은 적당히 입력하십시요.

  

그림 3. 목록 상자 'ListBoxSN' 추가

  다수의 항목을 선택할 수 있어야 하므로, 새로 추가한 목록 상자 'ListBoxSN'의 속성 중 MultiSelect 항목을 다음 그림과 같이 '1'로 설정하십시요.

  

그림 4. 'ListBoxSN'의 MultiSelect 속성 선택

  VBAProject 트리 목록에서 'FSchedule' 폼의 Context 메뉴 → '코드 보기'를 선택하여 소스코드 편집 모드로 들어갑니다. 기존에 있던 종목 선택 콤보 상자의 초기화 로직을 새로 추가한 목록 상자를 초기화하기 위한 것으로 수정합니다. 그림 5에서 붉은 색 선으로 표시된 부분을 하단의 소스 중 '(변경 후 - ListBoxSN 초기화)' 소스로 대체합니다.

 

그림 5. 폼의 초기화('UserForm_Initialize') Sub모듈 수정

   ※ (변경 전 - ComboBoxSN 초기화)

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

    s_list = getStockList()
    For i = LBound(s_list, 1) To UBound(s_list, 1)
        If s_list(i, 1) <> "" Then _
            ComboBoxSN.AddItem s_list(i, 2) & "(" & s_list(i, 1) & ")", i - 1
    Next i
    ComboBoxSN.ListIndex = i - 2

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

  ※ (변경 후 - ListBoxSN 초기화)

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

    s_list = getStockList()
    For i = LBound(s_list, 1) To UBound(s_list, 1)
        If s_list(i, 1) <> "" Then _
            ListBoxSN.AddItem s_list(i, 2) & "(" & s_list(i, 1) & ")" ', i - 1
    Next i

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

  위 그림 5의 상단에 'ButtonOK_Click' Sub모듈이 보입니다. 폼에서 'OK' 버튼을 클릭했을 때 수행되는데, 기존에 있던 콤보 상자 'ComboBoxSN'에서 선택된 종목을 인자로 넘겨주면서   'onSchedule' 모듈을 호출합니다. 이 부분을 수정해야겠지요? 하단의 소스를 복사하여 다음 그림과 같이 대체하십시요.

  

그림 6. 'ButtonOK_Click' 이벤트 처리 모듈 수정

  ※ (변경 후 'ButtonOK_Click' 소스)

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

Private Sub ButtonOK_Click()
    Dim s_list As String    'comma separated selected items list
    For i = 0 To ListBoxSN.ListCount - 1
        If ListBoxSN.Selected(i) = True Then
            s_list = s_list & ListBoxSN.List(i) & ","
        End If
    Next i
    If Len(s_list) > 0 Then
        s_list = Left(s_list, Len(s_list) - 1)
        Call onSchedule(ComboBoxTS.Text, ComboBoxTE.Text, _
                        s_list, ComboBoxCT.Text)
    End If
   
    Me.Hide
End Sub

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

  ☞ 'onSchedule'로 넘겨주는 인자가 단일 종목을 넘겨주던 것에서 (comma-separated) 복수 종목으로 변경됩니다.

  폼의 다중 페이지 중 새로 생성한 'Page3'의 Caption을 적절히 수정하고, 기존 콤보 상자 'ComboBoxSN'이 있는 페이지로 이동합니다. 

  캔들의 시간단위를 선택하기 위한 콤보 상자 'ComboBoxCT'는 그대로 사용할 것이므로 다중 페이지의 첫 번째 페이지('Time')로 이동시키고, 나머지는 페이지와 함께 삭제하도록 하겠습니다. 완성된 폼은 다음 그림과 같습니다.

  

그림 7. 완성된 폼

  폼의 'ButtonOK_Click'이 호출하는 'onSchedule' Sub모듈의 소스를 보겠습니다.

  

그림 8. 'onSchedule' 소스

  'prepareSheet'와 'Application.OnTime'으로 인자(parameter)들을 중계하는 역할만 수행하므로 'onSchedule' 소스를 수정할 필요는 없겠군요. 그림 8의 하단에서 찾을 수 있는 'Stop_Click'은 지난 세 편의 글에서도 그랬듯이 그대로 사용하면 될 것입니다.

  따라서, 남은 작업은 'prepareSheet'와 'Start_Click'에서 처리되는 로직을 수정하는 것입니다. 

  먼저 'prepareSheet' 함수부터 수정하겠습니다. 기존의 'prepareSheet'는, 선택된 한 개의 종목에 대해,  종목코드와 종목명을 전역변수에 저장한 후 캔들정보 축적 시트를 생성합니다. 이것을 다수의 종목에 대해 동일한 작업을 하도록 수정하기만 하면 됩니다. 기존 함수를 아래 소스로 대체하십시요.

  ※ (변경 후 'prepareSheet' 소스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function prepareSheet(slist, cu)
    Dim xlSheet As Worksheet
    Dim s_arr As Variant
    On Error GoTo EH_prepareSheet:
    dde_sheet = ActiveSheet.Name
   
    s_arr = Split(slist, ",")
    s_cnt = UBound(s_arr) - LBound(s_arr) + 1
    ReDim ss_list(1 To s_cnt, 1 To 2)
   
    For i = 1 To s_cnt
        sn = s_arr(LBound(s_arr) + i - 1)
        s_code = Right(sn, Len(sn) - InStr(sn, "("))
        s_code = Replace(s_code, ")", "")
        If s_code = Empty Or s_code = "" Then GoTo NextLoop:
       
        sh_name = Left(sn, InStr(sn, "(") - 1)
        If sh_name = Empty Or sh_name = "" Then sh_name = s_code
        sh_name = sh_name & "-" & cu
   
        ss_list(i, 1) = s_code
        ss_list(i, 2) = sh_name
   
        If Not sheetExist(sh_name) Then
            Set xlSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
            xlSheet.Name = sh_name
           
            With xlSheet
                .Cells(1, 1).Value = "일자 / 시간"
                .Cells(1, 2).Value = "시가"
                .Cells(1, 3).Value = "고가"
                .Cells(1, 4).Value = "저가"
                .Cells(1, 5).Value = "종가"
                .Cells(1, 6).Value = "거래량"
            End With
        End If
NextLoop:
    Next i
   
    Set xlSheet = Nothing
    Exit Function
   
EH_prepareSheet:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [prepareSheet]"
    Err.Raise Err.Number, "prepareSheet", Err.Description
End Function

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

  ☞ 'slist' 인자가 comma로 분리된 '종목명(종목코드)' 리스트입니다.

  다음은 'Start_Click'을  통해 수행되는 로직을 수정할 차례입니다. 다음 그림 9에서 알 수 있듯이 'storeCandleInfo' 모듈만 수행하면 되겠군요.

 

그림 9. 'Start_Click' 소스

  'storeCandleInfo' Sub모듈은 처리속도 향상을 위해 마지막으로 저장한 캔들정보의 상태를 유지하면서 새로 수신한 체결정보에 대해 기존 캔들을 수정('updateCandle')해야 할지 아니면 새로운 캔들을 생성('fillCandle')해야 할지 판단합니다.

  그런데, 다수의 종목에 대해 캔들정보의 상태를 유지하면서 독립적인 실행이 가능하도록 해야 하므로 클래스를 사용하는 것이 바람직할 듯 합니다. 종목코드와 종목명(실제로는 시트명)을 멤버(member)로 하는 클래스 모듈을 생성하여 기존의 'storeCandleInfo'와 'fillCandle' 및 'updateCandle'을 클래스의 멤버 함수로 이동하도록 하겠습니다.

  아래 그림과 같이 VBAProject 트리목록에서 작업 중인 파일의 Context 메뉴 → '삽입' → '클래스 모듈'을 선택하여 클래스 모듈을 생성합니다.

  

그림 10. 클래스 모듈 추가하기

  그림 11과 같이 클래스 이름을 'CCandleFeeder'로 수정하고, 아래 클래스 소스를 복사하여 입력합니다. 

  

그림 11. 'CClassFeeder' 클래스 소스 입력

  ※ (소스 - CCandleFeeder 클래스)

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private pStockCode As String
Private pCandleSheet As String
Private pDDEsheet As String
Property Let StockCode(v As String)
    If v = Empty Or v = "" Then _
        Err.Raise 8901, "CCandleFeeder.Let StockCode", "Invalid parameter"
    pStockCode = v
End Property

Property Let CandleSheet(v As String)
    If v = Empty Or v = "" Then _
        Err.Raise 8901, "CCandleFeeder.Let CandleSheet", "Invalid parameter"
    pCandleSheet = v
End Property

Property Let DDEsheet(v As String)
    If v = Empty Or v = "" Then _
        Err.Raise 8901, "CCandleFeeder.Let DDEsheet", "Invalid parameter"
    pDDEsheet = v
End Property

Sub store(s_code)
    Static dde_cell As Integer 'the row of DDE cells of selected stock
    Static prev_dt As String   'previous date
    Static prev_tu As Integer  'previous time unit
    Static prev_li As Integer  'row number of the row being updated
    Static prev_vo As Double   'sum of volumes of prev candles of the same day
    Static c_p_hr  As Integer  'the number of candles per hour
    On Error GoTo EH_storeCandleInfo:
   
    If s_code = pStockCode Then
        sh_name = pCandleSheet
        Debug.Print "sh_name " & sh_name
        If dde_cell = 0 Then dde_cell = rowSearch(pDDEsheet, 2, "'" & s_code & "'")
        If c_p_hr = 0 Then c_p_hr = candlesPerHour(sh_name)
        'Debug.Print "c_p_hr " & c_p_hr
       
        With Sheets(sh_name)
            'Debug.Print "prev_li " & prev_li
            If prev_li = 0 Then
                If .Cells(2, 1).Value = "" Then
                    prev_li = 1
                Else
                    'prev_li = .UsedRange.Cells(.Cells.Count).Row '===> Overflow
                    prev_li = .Cells(1, 1).End(xlDown).Row
                End If
            End If
            'Debug.Print "prev_li " & prev_li
           
            cp = Sheets(pDDEsheet).Cells(dde_cell, 2).Value      '현재가
            cv = Sheets(pDDEsheet).Cells(dde_cell, 3).Value      '거래량
            dv = Abs(Sheets(pDDEsheet).Cells(dde_cell, 4).Value) '체결량
            bt = Sheets(pDDEsheet).Cells(dde_cell, 5).Value      '체결시간
           
            If prev_li = 1 Then
                If c_p_hr = -1 Then GoTo DailyNewCandle:
NewCandle:
                prev_tu = WorksheetFunction.Floor(TimeValue(Right(bt, 8)) _
                          * 24 * c_p_hr, 1)
                Debug.Print "prev_tu " & prev_tu
               
HourlyNewCandle:
                Dim hr As Integer, hrstr As String
                hr = WorksheetFunction.Floor(prev_tu / c_p_hr, 1) 'prev_tu / c_p_hr
                hrstr = hr
                If hr < 10 Then hrstr = "0" & hr
               
                If c_p_hr > 1 Then
                    mt = (prev_tu Mod c_p_hr) * (60 / c_p_hr)
                    If Len(mt) = 1 Then mt = "0" & mt
                    hr_mt = "-" & hrstr & ":" & mt & ":00"
                ElseIf c_p_hr > 0 Then 
                    'hr_mt = "-" & prev_tu & ":00:00" 
                    hr_mt = "-" & hrstr & ":00:00"        ' ◀ 버그 수정
                End If
               
DailyNewCandle:
                prev_dt = Replace(Format(Date, "yyyy/mm/dd"), "-", "/")
                Debug.Print "prev_dt & hr_mt " & prev_dt & hr_mt
                prev_li = prev_li + 1
                Call fillCandle(sh_name, prev_li, prev_dt & hr_mt, cp, cv - prev_vo) 'dv)
                Exit Sub
            End If
           
            If prev_dt = "" Then prev_dt = Left(.Cells(prev_li, 1).Value, 10)
            If c_p_hr = -1 Then
                If DateValue(prev_dt) <> Date Then
                    GoTo DailyNewCandle:
                Else
                    Call updateCandle(sh_name, prev_li, cp, cv)
                End If
               
            Else
                If prev_tu = 0 Then
                    Debug.Print "prev_li " & prev_li
                    Debug.Print Right(.Cells(prev_li, 1).Value, 8)
                    prev_tu = WorksheetFunction.Floor(TimeValue( _
                              Right(.Cells(prev_li, 1).Value, 8) _
                              ) * 24 * c_p_hr, 1)
                End If
                         
                If DateValue(prev_dt) <> Date Then
                    Debug.Print "Going to NewCandle"
                    GoTo NewCandle:
                Else
                    cur_tu = WorksheetFunction.Floor(TimeValue(Right(bt, 8)) _
                             * 24 * c_p_hr, 1)
                            
                    If prev_tu <> cur_tu Then
                        Debug.Print Right(bt, 8)
                        Debug.Print prev_tu & "," & cur_tu
                        prev_vo = cv - dv
                        prev_tu = cur_tu
                        GoTo HourlyNewCandle:
                    Else
                        If prev_vo = 0 Then prev_vo = cv - dv - .Cells(prev_li, 6).Value
                        Call updateCandle(sh_name, prev_li, cp, cv - prev_vo)
                    End If
                End If
               
            End If
        End With
    End If
   
    Exit Sub
EH_storeCandleInfo:
    'MsgBox "Error(" & Err.Number & ") " & Err.Description & " [storeCandleInfo]"
    Err.Raise Err.Number, "storeCandleInfo", Err.Description
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function fillCandle(sh_name, l, d, p, v)
    With Sheets(sh_name)
        .Cells(l, 1).Value = d
        .Cells(l, 2).Value = p
        .Cells(l, 3).Value = p
        .Cells(l, 4).Value = p
        .Cells(l, 5).Value = p
        .Cells(l, 6).Value = v
    End With
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function updateCandle(sh_name, l, p, v)
    With Sheets(sh_name)
        If p > .Cells(l, 3).Value Then .Cells(l, 3).Value = p
        If p < .Cells(l, 4).Value Then .Cells(l, 4).Value = p
        .Cells(l, 5).Value = p
        .Cells(l, 6).Value = v
    End With
End Function

 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ☞ 클래스는 종목코드와 시트명(종목명), DDE 시트명을 멤버로 갖으며, 기존 'storeCandleInfo' Sub모듈을 'CCandleFeeder.store'로 활용합니다. 마찬가지로 'fillCandle'과 'updateCandle'도 클래스의 멤버 함수가 됩니다.

  기존 'storeCandleInfo'는 선택된 각각의 종목에 대응하는 CCandleFeeder 오브젝트를 생성하여 오브젝트의 멤버 함수인 'store' Sub모듈을 호출하도록 수정합니다. 아래 소스로 대체하십시요.

그림 12. 'storeCandleInfo' 수정

  ※ (소스 - 변경 후 'storeCandleInfo')

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub storeCandleInfo(s_code)
    Static feeders As Collection
    'On Error GoTo EH_storeCandleInfo:
   
    If feeders Is Nothing Then Set feeders = New Collection
   
    For i = LBound(ss_list) To UBound(ss_list)
        Dim cfeeder As CCandleFeeder
        On Error Resume Next
        Set cfeeder = feeders.Item(ss_list(i, 1))
        If Err.Number = 5 Then GoTo RegisterFeeder:
        On Error GoTo 0
        If Not cfeeder Is Nothing Then GoTo DoFeed:
       
        On Error GoTo EH_storeCandleInfo:
       
RegisterFeeder:
        Set cfeeder = New CCandleFeeder
        cfeeder.StockCode = ss_list(i, 1)
        cfeeder.CandleSheet = ss_list(i, 2)
        cfeeder.DDEsheet = dde_sheet
        feeders.Add cfeeder, ss_list(i, 1)
       
DoFeed:
        cfeeder.store (s_code)
NextLoop:
    Next i
   
    Exit Sub
EH_storeCandleInfo:
    If Err.Number = 8901 Then
        Debug.Print "Error(8901) " & Err.Source
        Err.Clear
        GoTo NextLoop:
    End If
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [storeCandleInfo]"
    Err.Raise Err.Number, "storeCandleInfo", Err.Description
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞  종목별로 'CCandleFeeder' 오브젝트를 하나씩 할당하고 DDE 셀 변화에 따라 해당하는 종목의 오브젝트를 찾아(그림 12의 1과 2) 'store'를 호출(그림 12의 3)합니다.

  모든 작업이 끝났습니다. 문서를 저장하고, 영웅문의 DDE 서비스가 활성화 상태인지 확인하신 후 테스트해 보시기 바랍니다.

그림 13. 테스트 결과