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. 테스트 결과

Excel에서 HTS DDE 활용하기 3 - 데이터 축적 I

※ Elliott Pattern Helper Add In

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

  이번 글은 'Excel에서 HTS DDE 활용하기'의 세 번째 주제로서, 지수 또는 종목의 체결정보를 축적하는 문제를 풀어나가도록 하겠습니다.

  문제를 정확히 정의하자면, 원하는 종목이나 지수에 대해 세 개의 DDE Item을 실시간으로 받아 원하는 시간 단위의 캔들(candle) 정보(시가, 고가, 저가, 종가 및 거래량)를 축적하는 것입니다(그림 1 참조).

그림 1. 60분 Candle

  우선, 필요한 세 개의 DDE Item은 무엇일까요? 체결가는 당연히 필요하고, 거래량 또한 문제에서 요구한 것이니 받아야 하겠지요. 나머지 하나는 바로 체결시간입니다. DDE를 통해 수신한 체결정보의 체결시간을 모른다면 캔들을 구분할 수 없을 것입니다.

  시나리오는 최대한 간단히 하겠습니다. ※ 지난 글에서 완성한 엑셀 파일을 사용하여 진행합니다.

1. 사용자가 'Schedule' 버튼을 클릭하면, 종목과 캔들의 단위시간 및 스케줄을 선택할 수 있는 폼(Form) 팝업된다.
2. 모든 항목을 선택하고 'OK' 버튼을 클릭하면, 선택된 종목에 대해 스케줄된 시간 동안 선택된 단위의 캔들정보가 축적된다.

  위 시나리오의 1번 항목을 구현하려면 '사용자 정의 폼'을 수정해야 하겠군요. 현재까지 완성된 폼을 볼까요?

그림 2. DDE Schedule 폼

  위 그림처럼, 다중 페이지의 두 번째 페이지가 비어 있으므로 그곳('Page2')에 종목과 캔들 단위시간을 선택할 수 있는 UI(사용자 인터페이스) 항목을 추가하면 되겠군요.

  바로 시작하지요. 그림 3과 같이 '개발 도구' → 'Visual Basic'을 선택하거나 'Alt+F11'을 눌러 VBA 편집창을 띄웁니다.

그림 3. VBA 편집창 실행

  VBAProject 트리 목록에서 'FSchedule' 폼을 더블클릭(그림 4의 1)한 뒤 'Page2'를 선택합니다(그림 4의 2).

그림 4. 사용자 정의 폼 'FSchedule' 선택

  종목 선택을 위한 콤보 상자와 레이블을 각각 생성합니다(그림 5의 1). 콤보 상자 이름은 'ComboBoxSN'으로 합니다(그림 5의 2).

그림 5. 종목 선택을 위한 콤보 상자 생성

  마찬가지 방법으로 다음 그림과 같이 캔들 단위시간을 선택하기 위한 콤보 상자를 생성하고 그 이름을 'ComboBoxCT'으로 합니다.

그림 6. 캔들 단위시간 선택을 위한 콤보 상자 생성

  ※ (참고) 이 시점에서 구현 범위에 제한을 조금 두겠습니다. 선택 가능한 종목은 작업 중인 엑셀 파일에서 DDE로 연결된 종목들로, 캔들의 시간 단위는 5분, 15분, 60분 및 1일입니다.

  먼저 캔들선택 콤보 상자 'ComboBoxCT'의 초기화를 위한 소스코드를 입력하겠습니다. 그림 7-1과 같이 VBAProject 트리 목록에서 'FSchedule' 폼의 Context 메뉴 → '코드 보기'를 선택하여 소스코드 편집 모드로 들어갑니다.

그림 7-1. 'ComboBoxCT' 초기화 소스 입력 1

  'UserForm_Initialize' sub모듈 내부에 하단의 소스를 복사하여 다음 그림과 같이 입력합니다.

그림 7-2. 'ComboBoxCT' 초기화 소스 입력 2

  ※ (소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ComboBoxCT.AddItem "5분", 0
    ComboBoxCT.AddItem "15분", 1
    ComboBoxCT.AddItem "60분", 2
    ComboBoxCT.AddItem "일", 3
    ComboBoxCT.ListIndex = 0

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

  다음 과정은 종목선택 콤보 상자 'ComboBoxSN'을 초기화하기 위한 소스를 입력하는 것인데, 두 단계를 거치도록 합니다. 

  첫째 단계는 엑셀 시트에서 DDE 셀을 검색하여 종목코드와 종목명으로 구성된 2차원 배열(array)를 출력하는 함수를 만드는 것이고, 두 번째 단계는 폼 초기화 모듈에서 이 함수를 호출한 후 종목선택 콤보 상자를 채우도록 하는 것입니다.

  ☞ 물론 폼에서 두 단계를 모두 처리할 수도 있겠지만, UI와 로직(logic)을 분리한다는 측면과 함수의 재활용 측면을 고려한 것입니다.

  다음 그림 8과 같이 하단의 소스를 복사하여 시트에 포함된 DDE 연결 종목의 배열을 출력하는 함수 'getStockList'를 작성합니다. 폼이 아니라 모듈에 작성합니다.

그림 8. 시트에 포함된 DDE 연결 종목의 배열을 출력하는 함수

  ※ ('getStockList' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function getStockList() As Variant
    Dim s_list As Variant
    On Error GoTo EH_getStockList:
  
    With ActiveSheet.UsedRange
        r_from = .Cells.Row + 1
        r_to = .Cells(.Cells.Count).Row
        ReDim s_list(1 To r_to - r_from + 1, 1 To 2)
      
        For i = r_from To r_to
            s_code = .Cells(i, 1).Formula
            If Left(s_code, 7) <> "=KHRun|" Then GoTo Continue:
            idx_from = InStr(s_code, "|") + 1
            idx_to = InStr(s_code, "!") - 1
            s_code = Mid(s_code, idx_from, idx_to - idx_from + 1)
            s_code = Replace(s_code, "'", "")
          
            s_list(i - r_from + 1, 1) = s_code
            s_list(i - r_from + 1, 2) = .Cells(i, 1).Value 'getStockName(s_code)
Continue:
        Next i
    End With
  
    getStockList = s_list
    Exit Function
  
EH_getStockList:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [getStockList]"
    Err.Raise Err.Number, "getStockList", Err.Description
End Function

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

  ☞ 위 소스는 시트의 1열에서 DDE 셀을 찾아 셀의 수식에서 종목코드를 추출하고 종목명은 셀의 값으로 하여 배열을 채웁니다.

  이제 폼의 초기화 모듈에서 위 함수를 호출하도록 폼의 소스를 수정하겠습니다. VBAProject 트리 목록에서 'FSchedule' 폼의 Context 메뉴 → '코드 보기'를 선택하여 폼의 소스 편집 모드로 들어간 후, 하단에서 소스를 복사하여 다음 그림과 같이 입력합니다.

그림 9. '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

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

  ☞ 위 소스는 종목 선택을 위한 콤보 상자 'ComboBoxSN'를 '종목명(종목코드)' 형태의 목록으로  초기화합니다.

  폼 개체 편집 모드로 돌아가 'Page2'의 Caption을 적절히 수정하십시요. 일단 파일을 저장하고, 완성한 폼을 확인해 볼까요? 시트 위의 'Schedule' 버튼을 클릭하면 다음 그림과 같이 폼이 팝업되는 것을 볼 수 있을 것입니다.
  

그림 10. 완성된 폼

  이렇게 해서 시나리오의 1번 항목을 완료했습니다. 2번 항목은, 폼에서 'OK' 버튼을 클릭하면 선택된 종목에 대해 스케줄된 시간 동안 선택된 시간단위의 캔들정보가 축적되도록 하는 것이었습니다. 

  '캔들정보를 축적한다'는 것을 좀 더 명확히 하겠습니다. 현재 작업 중인 파일에 새로운 시트를 생성하여 그림 1과 같은 모양으로 일자/시간, 시가, 고가, 저가, 종가 및 거래량을 기록해 나가는 것입니다. 물론, 이전에 생성된 시트가 있다면 행을 추가해 나가는 방식입니다.

  캔들정보를 축적하기 위해 생성하는 시트의 명명규칙은 '종목명-기간' 형태로 하겠습니다. 위 그림 10의 경우라면 'OK' 버튼을 클릭했을 때 'SK하이닉스-60분'의 이름으로 시트가 생성되겠지요?

  지난 글에서 작성했던 폼의 'OK' 버튼 클릭 이벤트 처리 매크로를 상기해 보겠습니다.
  

그림 11. 'ButtonOK_Click' 매크로

  'onSchedule' 모듈을 호출하는 것으로 되어 있군요. 호출 인자(parameter)는 스케줄 시작 시각과 종료 시각 두 개이구요. 'onSchedule' 모듈을 볼까요?
  

그림 12. 'onSchedule' 모듈

  흐음.. 'onSchedule'의 인자를 두 개 더 추가해야겠습니다. 선택된 종목과 캔들의 시간단위를 추가로 넘겨주어야 하니까요. 그리고, 캔들정보를 축적할 시트는 스케줄 시각과 상관없이 생성해도 되기 때문에, 스케줄 처리를 하는 'Application.OnTime' 문장 앞에서 시트를 생성하도록 하겠습니다.

  그 전에 현재 작업 중인 파일에 특정 이름의 시트가 존재하는지 확인하는 함수 'sheetExist'를 작성해보도록 하겠습니다. 다음 그림 13과 같이 하단에서 소스를 복사하여 위에서 작성한 'getStockList' 함수 아래 입력합니다.
  

그림 13. 시트 존재 여부를 확인하는 함수 작성

  ※ ('sheetExist' 소스)

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

Function sheetExist(sh_name) As Boolean

    sheetExist = False

    

    On Error Resume Next

    Dim tmpWS As Worksheet

    Set tmpWS = Sheets(sh_name)

    On Error GoTo 0

    If Not tmpWS Is Nothing Then

        sheetExist = True

    End If

    Set tmpWS = Nothing

End Function

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

  이젠 선택된 종목과 시간단위에 대해서 기존에 생성된 캔들정보 축적 시트가 존재하지 않을 경우 신규로 생성하는 함수 'prepareSheet'를 작성하도록 하겠습니다. 하단에서 소스를 복사하여 다음 그림과 같이 입력합니다.

그림 14. 'prepareSheet' 함수 작성

  ※ ('prepareSheet' 소스)

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

Function prepareSheet(sn, cu)
    Dim xlSheet As Worksheet
    On Error GoTo EH_prepareSheet:
    dde_sheet = ActiveSheet.Name
  
    s_code = Right(sn, Len(sn) - InStr(sn, "("))
    s_code = Replace(s_code, ")", "")
    If s_code = Empty Or s_code = "" Then
        MsgBox "Selected stock code null or empty"
        Exit Function
    End If
  
    sh_name = Left(sn, InStr(sn, "(") - 1)
    If sh_name = Empty Or sh_name = "" Then
        MsgBox "Selected stock name null or empty"
        Exit Function
    End If
  
    sh_name = sh_name & "-" & cu
    ReDim ss_list(1 To 1, 1 To 2)
    ss_list(1, 1) = s_code
    ss_list(1, 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
  
    Set xlSheet = Nothing
    Exit Function
  
EH_prepareSheet:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [prepareSheet]"
    Err.Raise Err.Number, "prepareSheet", Err.Description
End Function

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

  ☞ 위 소스는 DDE 셀들이 있는 시트의 이름을 전역변수에 저정하는 과정(그림 14의 1), 입력받은 인자로부터 위에서 언급했던 명명규칙에 따라 시트명을 조합하는 과정(그림 14의 2), 종목코드와 시트명을 전역변수 'ss_list'에 저장하는 과정(그림 14의 3), 시트 존재 여부를 확인하여 없을 경우 신규로 생성하는 과정(그림 14의 4)으로 구성됩니다.

  ☞  'dde_sheet'와 'ss_list'는 다음 과정에서 작성할 캔들정보 축적 모듈에서 사용됩니다. 위 소스만 보면 변수 선언 과정 없이 사용되고 있는데요, 위 소스가 존재하는 모듈의 상단에 'Private dde_sheet As String'과 'Private ss_list As Variant'라고 두 줄에 걸쳐 선언해 주시면 됩니다.

  'onSchedule' 모듈을 수정할 차례입니다. 다음 그림 15와 같이 수정하십시요. 
  

그림 15. 'onSchedule' 모듈 수정

  ※ (수정된 'onSchedule' 소스)

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

Sub onSchedule(ts, te, sc, cu)

    Call prepareSheet(sc, cu)

    

    If Time > TimeValue(ts) And Time < TimeValue(te) Then

        Application.OnTime Now, "Start_Click"

    Else

        Application.OnTime TimeValue(ts), "Start_Click"

    End If

    Application.OnTime TimeValue(te), "Stop_Click"

End Sub

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

  ☞ 인자 두 개와 'Call prepareSheet(sc, cu)' 라인이 추가된 것을 확인하십시요.

  폼 'FSchedule'의 'ButtonOK_Click' 모듈이 'onSchedule'을 호출하므로, 수정사항을 반영해야합니다. VBAProject 트리 목록에서 'FSchedule' 폼의 Context 메뉴 → '코드 보기'를 선택하여 폼의 소스 편집 모드로 들어간 후,'ButtonOK_Click'을 아래 소스로 대체하십시요.

  ※ (수정된 'ButtonOK_Click' 소스)

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

Private Sub ButtonOK_Click()
    Call onSchedule(ComboBoxTS.Text, ComboBoxTE.Text, _
                    ComboBoxSN.Text, ComboBoxCT.Text)
    Me.Hide
End Sub

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

 
  테스트를 해 볼까요? 시트 위의 'Schedule' 버튼을 클릭하여 폼을 팝업시킨 후(그림 10 참조) 'OK' 버튼을 클릭하면 다음 그림과 같이 시트가 생성되는 것을 확인할 수 있습니다. 
  

그림 16. 캔들정보 축적 시트 생성 테스트

  마지막 남은 것은 사용자가 지정한 스케줄 시간 동안 캔들정보가 축적되도록 하는 작업입니다.

  그림 15에서도 알 수 있지만, 정해진 시각에 실행되는 모듈은 'Start_Click'과 'Stop_Click'입니다. 'Stop_Click'은 DDE 셀의 change 이벤트에 대해 'SetLinkOnData'로 지정된 매크로를 해제하는 역할만 하도록 했었죠. 따라서 'Stop_Click'은 수정할 것이 전혀 없고, 'Start_Click'만을 우리의 시나리오에 맞도록 변경하면  될 것입니다.

  우선 지난 글에서 완성한 'Start_Click' 소스를 보겠습니다. 
  

그림 17. 'Start_Click' 모듈

  'Start_Click'은 위 그림 17의 1과 같이 DDE 셀에 대해 이벤트 처리 매크로로 'onStart' 모듈을 지정할 뿐이며, 현재의 'onStart' 모듈은 시트의 11열에 변하는 거래량을 에코(echo)하는 단순한 기능만 하는 상태입니다(그림 17의 2).  이 부분을 수정해야 하겠군요. 새로운 Sub모듈 'storeCandleInfo'를 만들고, 'onStart'에서 호출하는 것으로 하겠습니다.

  'storeCandleInfo'는 선택된 종목의 체결정보(현재가, 거래량, 체결량, 체결시간)를 조합하여 캔들정보로 축적하는데, 그 기능을 간략하게 정의해 보면 다음과 같습니다.

• 종목 코드를 input으로 받는다.
• 전역변수 'ss_list'에 저장된 종목에 한해서만 캔들정보를 축적한다. 
• 체결정보는 전역변수 'dde_sheet'에 저장된 이름의 시트에서 해당 행(row)을 검색하여 읽어들인다.
• 캔들정보를 축적할 시트명은 전역변수 'ss_list'에서 참조한다.
• 위에서 참조한 시트명으로 캔들의 시간단위를 파악한다.
• 체결시간이 마지막으로 축적된 캔들에 포함되는 경우, 캔들의 고가/저가/종가 및 거래량을 update한다. 그렇지 않을 경우, 새로운 캔들정보(시트의 새로운 행)를 생성하는데, 시가/고가/저가/종가는 체결가(=현재가)로 채우고, 거래량은 체결량으로 채운다.

  위 세 번째 항목을 위해 이전 글에서 작성했던 'rowSearch' 함수를 사용할텐데, 함수의 범용성을 위해 조금 수정하겠습니다. 아래 소스로 대체하시기 바랍니다.

  ※ (수정된 'rowSearch' 함수 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Function rowSearch(s_name, col, c_str) As Integer
    Dim found As Range
    On Error GoTo EH_rowSearch:
   
    rowSearch = 0
    With Sheets(s_name)
        Set found = .Columns(col).Find(What:=c_str, After:=.Cells(1, col), _
                                       LookIn:=xlFormulas, LookAt:=xlPart, _
                                       SearchOrder:=xlByRows, SearchDirection:=xlNext)
    End With
   
    If Not found Is Nothing Then rowSearch = found.Row
    Exit Function
EH_rowSearch:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [rowSearch]"
    Call Stop_Click
    Err.Raise Err.Number, Err.Description, "rowSearch"
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞ 하이라이트 된 부분에서 볼 수 있듯이, 검색할 시트명을 인자로 추가했습니다.

  위 기능 중 다섯 번째와 마지막 항목은 소스 파악을 쉽게 하기 위해 별도의 함수로 작성하겠습니다( 'candlesPerHour', 'updateCandle'과 'fillCandle'). 하단에서 소스를 복사하여 앞에서 작성했던 'getStockList' 위쪽에 다음 그림과 같이 입력합니다. ※ (참고) 그림 18 상단에 포함된 라인 'Private dde_sheet As String'과 'Private ss_list As Variant'의 추가는 앞에서 언급했었습니다.

그림 18. 'candlesPerHour', 'fillCandle', 'updateCandle' 함수

  ※ (소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function candlesPerHour(sh_name) As Integer
    If InStr(sh_name, "-일") > 1 Then
        candlesPerHour = -1
    ElseIf InStr(sh_name, "-60분") > 1 Then
        candlesPerHour = 1
    ElseIf InStr(sh_name, "-15분") > 1 Then
        candlesPerHour = 4
    Else
        candlesPerHour = 12
    End If
End Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 
  이제 'storeCandleInfo' Sub모듈을 작성하겠습니다. 하단에서 소스를 복사하여 다음 그림과 같이 전역변수 'ss_list' 선언부 아래에 입력합니다.

그림 19. 'StoreCandleInfo' Sub모듈 작성

  ※ (소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub storeCandleInfo(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 = ss_list(1, 1) Then
        sh_name = ss_list(1, 2)
        If dde_cell = 0 Then dde_cell = rowSearch(dde_sheet, 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(dde_sheet).Cells(dde_cell, 2).Value      '현재가
            cv = Sheets(dde_sheet).Cells(dde_cell, 3).Value      '거래량
            dv = Abs(Sheets(dde_sheet).Cells(dde_cell, 4).Value) '체결량
            bt = Sheets(dde_sheet).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"
                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, 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
                    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 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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞ 위 소스에서 하이라이트 된 라인들이 위에서 정의한 'storeCandleInfo'의 기능들과 연관된 것들입니다.

  'onStart' Sub모듈에서 'storeCandleInfo'를 호출하게만 하면 모든 작업이 끝나겠군요. 그림 20을 참조하여 수정하시기 바랍니다. 'rowSearch' 함수가 수정된 것 또한 확인하십시요.

그림 20. 'onStart' Sub모듈 수정

  ※ (수정된 'onStart' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub onStart(i)
    On Error GoTo EH_onStart:
   
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    c_str = aLinks(i)
    t_str = Replace(c_str, "KHRun|", "")
    t_str = Replace(t_str, "!13", "")
    Call storeCandleInfo(t_str)
   
    Exit Sub
EH_onStart:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & Err.Source '" [onStart]"
    Call Stop_Click
End Sub

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

  먼 길을 지루하게 온 듯한 기분입니다. 작업 중인 파일을 저장하신 후, 영웅문의 DDE  서비스를 실행시키고 작업하신 파일을 테스트해 보시기 바랍니다. 모든 것이 정상이라면, 다음 그림과 같이 캔들정보가 축적되는 것을 확인할 수 있을 것입니다.

그림 21. 테스트