Excel에서 HTS DDE 활용하기 1

※ Elliott Pattern Helper Add In

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

  Excel에서 증권사 HTS의 DDE 기능을 직접 활용하고자 하시는 분들을 위해, 기초가 될 만한 몇 가지 방법을 소개합니다.  ※ 키움증권 영웅문을 기준으로 하겠습니다.

  추측컨데 첫 번째 단추는, '어떻게 해야 HTS로부터 DDE를 통해 수신하는 데이터를 엑셀 파일로 축적할 수 있을까'하는 의문을 해결해 나가는 것이겠지요? 몇 편에 걸쳐 이 문제를 풀어나가고자 합니다.

  첫 번째 글로서, Excel에서 영웅문 DDE를 연결하여 몇몇 종목의 체결정보를 수신하고, 이를 단순하게 활용하는 아주 간단한 시나리오를 소개하도록 하겠습니다. 

  우선 키움증권 영웅문에서 '기능' → 'DDE서비스 시작...' 메뉴를 실행한 후, 아래 그림 1의 붉은 선으로 표시한 것과 같이 몇 개의 종목과 DDE 아이템(Item)을 선택하고 '엑셀로 보내기' 버튼을 클릭합니다.

그림 1. 영웅문 DDE 연결 1

  엑셀 창이 뜨면서 위에서 선택한 종목과 DDE 아이템이 엑셀 시트(sheet)에 출력되는 것을 다음 그림과 같이 확인할 수 있습니다. 

  ※ (참고) 1행의 셀(cell)을 제외한 나머지 셀은 "=KHRun|xxxxxx!yy" 형태의 수식으로 채워지는데, 'KHRun'은 영웅문의 DDE 프로그램 이름이며 'xxxxxx'는 종목(또는 지수) 코드, 'yy'는 DDE 아이템 코드를 나타냅니다.

  ※ (정의) DDE 셀, DDE client cell: "=KHRun|xxxxxx!yy" 형태의 수식으로 채워져 특정 주식 또는 지수의 DDE Item 값을 수신하여 표시하는 셀

그림 2. 영웅문 DDE 연결 2

  영웅문의 DDE 서비스가 작동 중이라면 그림 2의 DDE 셀들은 그 셀이 수신하는 종목의 체결정보에 따라 값이 순식간에 변하게 됩니다. 
  이 상태에서는 변하는 값을 지켜만 볼 수 있을 뿐이겠지요? 무언가 의미있는 정보를 가공해 내기 위해서는 추가 작업이 필요한데,  그 과정이 매크로를 작성하는 것입니다.

  ※ (참고) 매크로를 작성하지 않고 셀(cell)에 수식을 직접 입력하는 것도 가능하지만 매크로에 비해 얻을 수 있는 정보나 가능한 작업의 범위가 제한적입니다. 

  작업을 더 진행하기 전에 생성된 엑셀 파일을 저장합니다. 매크로를 작성하여 사용할 것이므로 그림 3과 같이 '매크로 사용 통합문서'로 저장해야 합니다.

그림 3. 문서 저장 - 매크로 사용 통합 문서

  다음 단계는 매크로를 작성하여 DDE client cell들의 값이 변경될 때마다 정해진 작업을 수행하도록 하는 것입니다. 그런데, 특정한 시간대나 이벤트(event) 또는 사용자가 필요로 하는 경우에만 매크로가 실행되도록 하는 것이 좋지 않을까요? 

  ※ (그러거나 말거나) 사용자 또는 소유자의 간섭(?)이 전혀 필요없는 프로그램이라면 좀비이거나 evolving object로서 인간을 능가하게 되거나... 후자는 singularity...?

  그래서.. 간단하게 테스트해볼 수 있도록, 위 그림 1~3 과정에서 생성된 Excel 시트 위에 'Start' 버튼과 'Stop' 버튼을 만들어 매크로의 실행을 제어할 수 있도록 하겠습니다.

  작업 순서는 다음과 같습니다.

1. 'Start' 버튼 생성 - 버튼을 클릭하면 DDE 셀의 값이 변할 때 수행될 매크로의 이름이 지정됩니다. 
2. 매크로 작성 - DDE 셀의 값이 변하는 것을 반영하여 실제로 처리되어야 할 작업을 매크로로 작성합니다.
3. 'Stop' 버튼 생성 - 매크로의 작동을 중지하는 버튼을 만듭니다.

  그림 4와 같이 '개발 도구' → '삽입' → '단추'를 클릭한 후 시트 위의 적절한 위치에 버튼을만듭니다.
 

그림 4. 매크로 제어 버튼 만들기 1

  버튼이 만들어지는 동시에 다음 그림과 같이 생성된 버튼의 클릭 이벤트를 처리할 매크로를 지정하도록 팝업 창이 뜹니다. 이름을 'Start_Click'으로 입력하고, 매크로 위치는 '현재 통합 문서'나 작업 중인 파일로 선택한 후 '새로 만들기' 버튼을 클릭합니다.

그림 5. 매크로 제어 버튼 만들기 2 - Start 버튼

  엑셀 VBA 작성 창이 열리면서 지정된 파일(그림 6의 1)에 지정한 이름('Start_Click', 그림 6의 2)으로 프로그램 골격(skeleton)이 생생됩니다.

그림 6. 매크로 제어 버튼 만들기 3 - Start 버튼 이벤트 처리 매크로

  이 글 하단에 있는 소스에서 'Start_Click' Sub모듈을 복사하여 입력합니다.

그림 7. 매크로 제어 버튼 만들기 4

  ※ (소스) 'Start_Click'

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Start_Click()
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            If InStr(aLinks(i), "!13") > 8 Then
                ActiveWorkbook.SetLinkOnData aLinks(i), "'onStart " & i & "'"
            End If
        Next i
    End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞ 그림 7의 붉은 색 선으로 표시된 라인이 DDE 셀 값의 변화에 대응하여 수행될 매크로를 지정하는 부분입니다. ('SetLinkOnData' 함수 참조) 

  각각의 DDE client cell에 대해 서로 다른 매크로를 지정할 수도 있지만, 이 글에서는 하나의 매크로를 사용하는 대신 매크로에 인자(parameter)를 넘겨 줌으로써 종목 코드 별로 차별화된 처리를 할 수 있도록 하겠습니다.

  위 (소스)에서 'SetLinkOnData'로 지정했듯이 'onStart' 매크로를 작성해야 합니다. 이 글의 하단에서 'onStart' Sub모듈을 복사하여 다음 그림과 같이 입력합니다.

그림 8. 매크로 작성 1 - onStart 모듈

  ※ (소스) '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", "")
    t_str = "'" & t_str & "'"
   
    row_n = rowSearch(3, t_str)
    Debug.Print i & ", " & t_str & ", " & row_n
    If row_n > 0 Then
        ActiveSheet.Cells(row_n, 11).Value = ActiveSheet.Cells(row_n, 3).Value
    End If
   
    Exit Sub
EH_onStart:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [onStart]"
    Call Stop_Click
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞ 'onStart' 모듈은 DDE link를 식별할 수 있는 인자를 입력으로 받아 종목코드를 추출한 후, 'rowSearch' 함수를 호출하여 해당 종목의 엑셀 시트 행(row)을 찾아  비어있는 열(column)에 거래량을 출력하도록 작성했습니다.

  다음 그림과 같이 'rowSearch' 함수를 복사하여 입력합니다.

그림 9. 매크로 작성 2 - rowSearch 함수

  ※ (소스) 'rowSearch'

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function rowSearch(col, c_str) As Integer
    Dim found As Range
    On Error GoTo EH_rowSearch:
   
    rowSearch = 0
    With Sheets("Sheet1")
        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

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

  그림 4의 과정을 통해 만든 버튼의 이름을 적절하게 수정합니다.  

그림 10. Start 버튼 이름 수정

  마지막으로, 'Stop' 버튼과 그 이벤트 매크로를 그림 4~6과 동일한 방법으로 생성하도록 하겠습니다. 이벤트 매크로의 이름은 'Stop_Click'으로 지정합니다.

그림 11. Stop 버튼 만들기 1

   이 글 하단에서 'Stop_Click' 모듈을 복사하여 입력함으로써 매크로 작성을 완료합니다.

그림 12. Stop 버튼 만들기 2 - Stop 버튼 이벤트 처리 매크로

  ※ (소스) 'Stop_Click' 

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Stop_Click()
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            ActiveWorkbook.SetLinkOnData aLinks(i), ""
        Next i
    End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  ☞  'Stop_Click'은 이벤트에 할당된 매크로를 해제하는 역할만 수행합니다.

  생성한 'Stop' 버튼의 이름을 적절히 수정한 후 문서를 저장합니다. 

그림 13.

  이제 모든 작업이 끝나고 테스트만 남았습니다. 영웅문 DDE 서비스가 실행 중인 것을 확인한 후 'Start' 버튼을 클릭하십시요. 아래 그림과 같이 거래량이 11열에 에코(echo)되는 것을 확인할 수 있습니다.

그림 14. 테스트

  ※ (소스) 전체

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' 영웅문 DDE 연결 Sample 1
''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Start_Click()
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            If InStr(aLinks(i), "!13") > 8 Then
                ActiveWorkbook.SetLinkOnData aLinks(i), "'onStart " & i & "'"
            End If
        Next i
    End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub Stop_Click()
    aLinks = ActiveWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
        For i = 1 To UBound(aLinks)
            ActiveWorkbook.SetLinkOnData aLinks(i), ""
        Next i
    End If
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private 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", "")
    t_str = "'" & t_str & "'"
   
    row_n = rowSearch(3, t_str)
    Debug.Print i & ", " & t_str & ", " & row_n
    If row_n > 0 Then
        ActiveSheet.Cells(row_n, 11).Value = ActiveSheet.Cells(row_n, 3).Value
    End If
   
    Exit Sub
EH_onStart:
    MsgBox "Error(" & Err.Number & ") " & Err.Description & " [onStart]"
    Call Stop_Click
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function rowSearch(col, c_str) As Integer
    Dim found As Range
    On Error GoTo EH_rowSearch:
   
    rowSearch = 0
    With Sheets("Sheet1")
        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

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''' 영웅문 DDE 연결 Sample
''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''