'project'에 해당되는 글 3건

어떤 증권사의 DDE를 사용하는 것이 좋은가에 대해서는 개인차가 있을 수 있다.

대부분의 증권사에서는 기본적으로 코스피, 코스닥, ETF, 국내 선물, 옵션 항목들을 제공하므로 

이들만 이용해도 충분한 경우에는 자신이 주로 사용하는 증권사에서 제공하는 DDE를 사용해도 된다. 

수집하려는 데이터가 많고 기본적인 항목들 외에도 해외선물, 환율, 채권 등과 같은 다양한 정보 또한 필요할 수도 있다. 

현재는 어떤지 모르겠으나 2013, 14년 당시에는 해외선물, 환율과 같은 항목들을 제공하는 증권사가 이트레이드 증권사 뿐이었다. 

이 때문에 제작한 이후 쭉 이트레이드 증권사(현 이베스트 증권사)에서 제공하는 서비스만을 이용하고 있다.

DDE를 이용하는 가장 큰 이유 중 하나는 사용이 편리하기 때문이다. 즉, 복잡하게 인터페이스를 스스로 다 만들고 구현할 필요가 없이 

엑셀에서 제공하는 스프레드시트 기능과 엑셀 자체에서 제공하는 프로그래밍 언어인 VBA를 묶어서 사용함으로써 

손쉽게 원하는 조건에 해당하는 데이터들을 처리한다던지 디자인을 구현할 수가 있다. 

앞에서 DDE를 이용해 데이터를 수집하기 위한 큰 틀을 살펴보았다. 사실 코드 부분의 경우 앞에서 설명한 부분이 거의 대부분을 차지한다. 

만일 더 필요한 기능이 있다면 해당하는 기능을 찾거나 만들어서 추가해주면 된다. 

예를 들어, 축적된 데이터를 다른 파일로 저장하고 시트에 기록된 데이터를 삭제한 후 다시 사용할 수 있게 하고자 하는 경우,

아래와 같이 새로운 모듈을 만들어서 기존의 코드 내 적절한 위치에서 이용할 수 있도록 수정해주면 된다.

' 축적된 데이터를 새로운 파일로 저장

Sub Save_Data()

Dim strPath As String

Dim i As Byte

rightnow = Format(Time, "hhmmss")                                ' 현재 파일을 저장하는 시각

strPath = ThisWorkbook.Path & "\"                                ' 현 파일이 있는 폴더에 새로운 파일을 생성할 저장 경로

ChDir strPath

    ThisWorkbook.SaveCopyAs Filename:=strPath & "Day " & Date & " " & rightnow & ".xlsm"

End Sub

' 기록된 데이터를 모두 삭제

Sub Auto_ClearData()

    Application.Calculation = xlCalculationManual

ThisWorkbook.Worksheets("Sheet2").Range("A14:CCC10000").Delete shift:=xlUp        ' 범위 임의 지정.

    Application.Calculation = xlCalculationAutomatic

End Sub

이와 같은 코드 관련 부분 외 스프레드시트의 세부적인 사항들도 하나씩 살펴보고자한다.

이번 글에서는 현재 만들어진 수집파일의 대략적인 틀을 살펴본다. 

기본적인 틀은 다음과 같다.

[ DDE로 데이터를 수신할 항목들을 나열해놓은 Sheet ]

[ 데이터를 저장하는 Sheet ]

DDE 항목 Sheet와 저장하는 sheet를 나누어놓은 이유는 간단하다.

이것을 제작할 초기만해도 수신할 데이터 항목이 현재와 같이 많지 않았다. 

그래서 원래는 DDE 항목과 데이터저장 sheet는 같이 묶여 있었다. 

하지만 옵션 데이터를 추가하면서부터 상황이 조금 바뀌었다.

매달 몇몇 옵션 행사가가 추가되거나 삭제되면서 전체적인 옵션 전광판의 틀이 바뀌었고, 

이 때문에 매번 sheet에 새로이 생성된 행사가를 추가해야하거나 삭제해야하는 일이 빈번해졌으며 

매달 이를 수정하느라 적잖은 시간을 소비하는 것이 매우 번거로웠다. 

그래서 아예 DDE sheet와 데이터저장 sheet를 분리하였고, 데이터를 저장하는 sheet는 그대로 둔채 

DDE sheet의 값만 조정하도록 변경하여 유지, 보수를 좀 더 편리하도록 점차적으로 개량해나갔다.

(최종적으로 만들어진 엑셀 파일은 단지 월물 코드만 변경하면 된다)

엑셀 sheet 부분은 대략적으로 위와 같이 나뉘어져있다. 

이를 자동으로 저장하게 해주는 VBA 코드의 틀은 다음과 같다.

시장 운영 시간(일별, 요일별)을 세분화하면서 변수의 종류가 조금 늘어났지만 기본적인 틀은 위와 같이 간단하다.

auto_open()과 pMain() 함수 두 부분으로 나눌 수 있다.

auto_open()에서는 pMain() 함수가 실행되기 전에 필요한 변수들을 선언하고 초기화한다.

pMain() 함수는 실질적으로 데이터를 엑셀 시트 내부에 일정 시간 간격으로 기록하도록 하는 함수다.

pMain() 함수 내부의 코드 몇 줄을 살펴보면 , 

        On Error Resume Next

위 코드는 실행 오류가 발생했을 때, 에러 메세지를 출력하지 않고 데이터 수집을 계속하도록 하는 코드다.

장 중에 에러 메세지로 인해서 수집이 멈추는 경우가 발생하면 안되므로 위 메세지를 추가했다.

        Application.Calculation = xlCalculationManual

        Application.ScreenUpdating = False

  ' **** 데이터 기록 Loop  ****

  Application.Calculation = xlCalculationAutomatic

  Application.ScreenUpdating = True

위 코드는 데이터 기록 시에 발생하는 delay 현상을 최소화하기 위해서 삽입한 코드다.

Application.Calculation = xlCalculationManual 

이 코드로 인해서 엑셀이 자동 새로고침(모든 수식 재연산)을 하지 않도록 한다.

DDE로 데이터를 수신하는 경우, 수신 데이터가 있을때 마다 엑셀은 자동으로 모든 수식을 계산한다.

이 때, 수식이 많을 경우 그만큼 처리 속도는 늦어지게 된다. 

이 코드가 없다면 엑셀 내부에 수식을 사용하기가 어려워진다.

Application.Calculation = xlCalculationAutomatic

이 코드를 사용해서 다시 수식 재연산을 하도록 바꾼다.

즉, 데이터를 기록할 때만 잠깐 모든 작업을 중단하게 하고 빠르게 데이터를 기록한 후, 

다시 DDE를 통해 새로운 데이터를 받아들이도록 한다.

Application.ScreenUpdating = False

말 그대로 수식 재연산 후 바뀐 데이터를 화면에 업데이트할지 여부를 결정하는 코드다.

이 코드와 Application.Calculation = xlCalculationManual 코드를 함께 사용해서 데이터를 기록할 때만큼은 모든 작업을 중지하도록 한다.

Delay_Moment() 함수는 일정 시간동안 멈춰있도록 하는 함수다.

종목의 수가 많은 경우, 엑셀 파일을 엶과 동시에 데이터가 화면에 표시되지 않고 수신하는데 어느 정도 시간이 소요되는데, 

이 때 수신할 동안은 아무 것도 하지 않도록 하기 위해서 넣어 놓았다.

(코드 일부분을 아래와 같이 첨부합니다..)

Option Explicit

Dim cnt As Long            ' 엑셀 저장 Sheet가 현재 몇 행에 저장되고 있는지 나타내는 변수(행 변수)

Dim B_cnt As Long         ' 엑셀 저장 Sheet가 현재 몇 행에 저장되고 있는지 나타내는 변수(행 변수)

Dim Date_T As Date        ' pMain 함수를 몇 초 간격으로 재귀 호출할지 지정하는 변수

Public Repeat As Integer    ' 저장할 종목들의 개수를 지정하는 변수(장 시작 이후/ 열 변수)

Dim B_Repeat As Integer    ' 저장할 종목들의 개수를 지정하는 변수(동시호가/ 열 변수)

Public start_Time As Date   ' 시작 시간을 정하는 변수

Public end_Time As Date    ' 종료 시간을 정하는 변수

Public Today_Date As Variant    ' 오늘 날짜를 지정하는 변수

Dim Weekday_Now As Integer    ' 오늘 요일을 정수로 표현한 변수

   ' ****************************************

   ' 자동 실행 부분

   ' ****************************************

Sub auto_open()

   ' 자동 실행 부분에서 변수들의 값을 초기화 시켜준다.

   ' 또한, 자동 실행 부분에서 Call 함수를 통해 pMain 함수를 불러온다.

   ' pMain 함수는 현재 시간을 체크하여 지정된 시간(08:30~ 15:15) 안에 있는 경우

   ' 함수 내부의 조건문들을 실행한 이후 매 10초 간격으로 자기 자신을 호출하여 계속 반복적인 작업을 수행하게 한다.

    Date_T = TimeValue("00:00:10")             ' 시간 간격 5초/딜레이 시간 고려하여 4초로 변경

 ' 현재는 10초 간격으로 변경

    start_Time = TimeValue("08:58:59")        ' 시작 시간과 끝나는 시간 정하는 변수. ("08:59:59")

    end_Time  = TimeValue("15:16:30")        ' ("15:15:05")

    B_cnt     = ThisWorkbook.Worksheets("Sheet3").Cells(2, 5).Value       ' 저장이 시작될 행 수 초기화(동시호가 sheet)

    cnt        = ThisWorkbook.Worksheets("Sheet2").Cells(6, 3).Value       ' 저장이 시작될 행 수 초기화(정규장 sheet)

    Repeat = ThisWorkbook.Worksheets("Sheet2").Cells(6, 2).Value        ' 저장할 종목수를 나타내는 변수(동시호가 sheet)

    B_Repeat = ThisWorkbook.Worksheets("Sheet3").Cells(2, 4).Value     ' 저장할 종목수를 나타내는 변수(정규장 sheet)

    Today_Date = Format(Now(), "yyyymmdd")

    Weekday_Now = Weekday(Now)    

        If Weekday_Now = 7 Then Exit Sub      ' 오늘이 토요일인 경우, 코드를 더 이상 실행하지 않고 종료

        If Weekday_Now = 1 Then Exit Sub      ' 오늘이 일요일인 경우, 코드를 더 이상 실행하지 않고 종료

    Call Delay_Moment(3) ' 데이터 로딩까지 시간을 벌기 위한 딜레이

    Call pMain

End Sub

Sub pMain()

    cnt = ThisWorkbook.Worksheets("Sheet2").Cells(6, 3).Value

    Static D_Tc(1300) As String       ' 데이터를 시간별 저장할 정적변수

   ' ****************************************

   ' 장 마감(축적된 데이터를 새로운 파일로 저장하고 파일 닫기)

   ' ****************************************

   If Time > end_Time Then       

       If Time < TimeValue("15:18:30") Then

        Delay_Moment (5)

        Application.OnTime Now + Date_T, "History_Data" ' 일일 히스토리 데이터 저장

                If Weekday_Now = 6 Then

' 오늘이 금요일인 경우, 한 주동안 모아놓은 1분 데이터 파일을 저장

                End If

        Delay_Moment (5)

        ThisWorkbook.Save               

        ThisWorkbook.Close False    ' 엑셀 파일을 저장하지 않고(false) 닫기

        End If

        Exit Sub '15:15:05 이후에는 더이상 행추가없음

    End If

    ' ****************************************

    ' 장 시작 전(sheet3에 저장)

    ' ****************************************

    If Time < TimeValue("09:05:05") Then  '08:59:58 부터 시작

        If Time > TimeValue("08:30:00") Then

        On Error Resume Next

            Application.Calculation = xlCalculationManual

            Application.ScreenUpdating = False

        For B_Repeat = 0 To B_Repeat ' 데이터를 Repeat을 이용해서 순차적으로 저장

            D_Tc(B_Repeat) = ThisWorkbook.Worksheets("Sheet3").Cells(6, B_Repeat + 1).Value  

 ThisWorkbook.Worksheets("Sheet3").Cells(B_cnt + 10, B_Repeat + 1).Value = D_Tc(B_Repeat)

   ' 행 변수, 열 변수

  ' B_cnt + 10과 같이 더한 숫자는 저장될 sheet 내의 배열에 따라서 얼마든지 달라질 수 있다.

        Next B_Repeat

Application.Calculation = xlCalculationAutomatic

      Application.ScreenUpdating = True

        End If

        GoTo Before_Time      

    End If

    ' ****************************************

    ' 장 시작(sheet2에 저장)

    ' ****************************************

        ThisWorkbook.Worksheets("Sheet2").Cells(cnt + 14, 1).Value = Format(Time, "hhmmss") ' 시간 입력.

        On Error Resume Next

            Application.Calculation = xlCalculationManual

            Application.ScreenUpdating = False

    For Repeat = 0 To Repeat - 1 ' 데이터를 Repeat을 이용해서 순차적으로 저장

                D_Tc(Repeat) = ThisWorkbook.Worksheets("Sheet2").Cells(12, Repeat + 2).Value   

                ThisWorkbook.Worksheets("Sheet2").Cells(cnt + 14, Repeat + 2).Value = D_Tc(Repeat)

            Next Repeat

    ' 순매수를 계산해서 데이터값으로 저장. 다른 추가 분석 데이터가 필요하면 여기에 입력.

    ' 차트에서 이동평균 추세선을 지원하므로 데이터 축적은 무의미- 삭제함

            Application.Calculation = xlCalculationAutomatic

            Application.ScreenUpdating = True

    ' ****************************************

    ' 장 마감 동시호가(sheet4에 저장)

    ' ****************************************

    If Time < TimeValue("15:15:05") Then

        If Time > TimeValue("14:50:00") Then    '14:50:00 부터 시작

            B_cnt = ThisWorkbook.Worksheets("Sheet4").Cells(2, 5).Value

            B_Repeat = ThisWorkbook.Worksheets("Sheet4").Cells(2, 4).Value

            On Error Resume Next

            Application.Calculation = xlCalculationManual

            Application.ScreenUpdating = False

                For B_Repeat = 0 To B_Repeat ' 데이터를 Repeat을 이용해서 순차적으로 저장

                    D_Tc(B_Repeat) = ThisWorkbook.Worksheets("Sheet4").Cells(6, B_Repeat + 1).Value

                    ThisWorkbook.Worksheets("Sheet4").Cells(B_cnt + 10, B_Repeat + 1).Value = D_Tc(B_Repeat)

                Next B_Repeat

            Application.Calculation = xlCalculationAutomatic

            Application.ScreenUpdating = True

        End If

        GoTo Before_Time

    End If

Before_Time:

    Application.OnTime Now + Date_T, "pMain"    ' pMain 함수의 재귀 호출

  ' 이 코드에 의해서 pMain이 일정 시간 간격으로 반복적으로 호출된다.

End Sub

Function Delay_Moment(delaytime As Integer)     ' 시간 지연 사용자 정의 함수

Dim csTime

csTime = Timer

Do While Timer < csTime + delaytime    ' delaytime 후 이벤트 발생

    DoEvents

Loop

End Function

http://3x3x3.tistory.com/

'3x3x3의 블로그'에서 기본적인 틀을 배워서 나름대로 수정 및 보완했다.

DDE를 이용하여 매매하고 데이터를 수집하는 것은 세밀함에 있어서 COM 방식과 비교해 많이 부족하지만

그럼에도 불구하고 비교적 간단하게 코드를 작성해서 사용할 수 있다는 것 덕분에 

입문하려고 하는 사람들에게 있어서 진입장벽이 낮으므로 여전히 매력적이다.

시간이 허락하는대로 'DDE를 이용한 데이터 수집'을 주제로 연재글을 올리려한다.

2013년, 초기에 사용했던 버전의 엑셀 파일. 

실시간으로 데이터를 시각화해서 볼 수 있다는 장점이 있지만, 

수집하려고 하는 데이터 수가 많고(300개 이상) 이와 같이 많은 차트를 만들어서 사용할 경우, 

컴퓨터의 성능이 좋다고 하더라도 데이터의 발생 시점과 엑셀 파일에 기록되는 시점에 있어서 차이가 생긴다. 

이는 DDE를 통해 데이터를 수신할 때마다 엑셀의 자동 계산 기능에 의해서 모든 차트를 다시 한번 더 화면에 업데이트하기 때문이다. 

이를 방지하는 코드를 삽입해서 시차를 최소화할 수 있지만 여전히 차이가 발생하였다. 

때문에 최근에는 이런 식으로 실시간 데이터를 표시하지 않고 있다.