close
프로필 배경
프로필 로고

infoportalhub

It · 2025. 4. 13. fullscreen 넓게보기

📘 프로젝트 명:Excel VBA + 영웅문4 DDE 실시간 선물 데이터 수집 및 기술 분석 시스템 1단계 - 직접코딩 같이 해봐요

반응형

🔧 준비물

항목 내용
프로그램 키움증권 영웅문4 설치
Excel 버전 Office 2016 이상 권장 (VBA 매크로 사용 가능해야 함)
참고 종목 "101W6000" (예: 코스피200 선물 근월물)
환경 Windows (DDE 지원 필요)

환경설정 잘 모르시는 분들 참고 하세요 : https://infoportalhub.com/entry/DDEExcelHowtolink

📌 주요 기능 요약

기능 설명
실시간 수집 DDE로 1초마다 주요 선물 데이터 수집
데이터 포인트 일자, 시간, 종목코드, 현재가, 거래량 등 총 30개+ 항목(VBA 코딩안에 항목있음)
기술 분석 1분/5분/10분 봉 생성 → 이평선(MA) + 볼린저밴드 계산
자동화 Excel VBA로 실시간 저장, 분석 자동 실행

 

🧮 1. DDE 실시간 연동 설정

1-1. 엑셀에 다음 수식 입력 (A1~AZ1)

excel(셀 안에 들어가는 수식입니다. - 항목 DDE 수식)

=NKRun|'101W6000'!'22'  '<== 일자
=TEXT(NOW(),"hh:mm:ss") '<== 시간
=NKRun|'101W6000'!'10'  '<== 현재가
=NKRun|'101W6000'!'13'  '<== 거래량
=NKRun|'101W6000'!'16'  '<== 시가
=NKRun|'101W6000'!'17'  '<== 고가
=NKRun|'101W6000'!'18'  '<== 저가
=NKRun|'101W6000'!'10'  '<== 종가
          :
          :

VBA코딩안에 해당 코드 확인 할수 있습니다.(밑에 화면 캡쳐도 있습니다.)

 

📈 2. VBA 코드 (1초 단위 실시간 기록 + 분석 자동화)

엑셀 버튼 추가 방법
메뉴->개발도구->삽입->양식 컨트롤-> 제일 처음이 버튼입니다.->클릭해서 화면에 그려줍니다.
버튼 텍스트 편집
버튼 추가하고 텍스트 편집으로 '시작' 변경
중지 버튼 추가 화면
시작버튼하고 중지버튼 그리는 화면
버튼에 매트로 이름 지정
중지 버튼 화면에 그리고 매크로 이름 지정 -> 새로만들기 누름.
시작,중지,초기화버튼 3개 완성
버튼 3개 매크로 지정 화면
엑셀-&gt;VBA코딩화면 전환
엑셀에서 개발도구->Visual Basic누른화면(Alt+F11)
완성된 화면
완성된 화면

2-1. Excel VBA 코드: 실시간 데이터 저장

vba

' 모듈 전체: 실시간 수집 + 기술분석 계산 + 버튼 제어
Dim isRunning As Boolean
Dim startTime As Double

'시작
Sub 시작버튼_Click()
    If isRunning Then Exit Sub
    isRunning = True
    Application.OnTime Now + TimeValue("00:00:01"), "기록반복"
End Sub

'중지
Sub 중지버튼_Click()
    If isRunning Then
        On Error Resume Next
        Application.OnTime EarliestTime:=nextRunTime, Procedure:="기록반복", Schedule:=False
        isRunning = False
        MsgBox "수집이 중지되었습니다.", vbInformation
    End If
End Sub

'초기화
Sub 초기화버튼_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    If MsgBox("데이터를 초기화할까요?", vbYesNo + vbQuestion, "초기화 확인") = vbYes Then
        ws.Rows("7:" & ws.Rows.Count).ClearContents
        MsgBox "초기화 완료!"
    End If
End Sub

🧠 3. 1분/5분/10분봉 생성 및 기술 분석

3-1. 데이터 집계 (엑셀 수식 or VBA)

vba

    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim r As Long: r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    ' 실시간 수집 (6행 기준 DDE 데이터 사용)
    ws.Cells(r, 1).Value = "'" & Format(Now, "yyyy/mm/dd")         ' 일자
    ws.Cells(r, 2).Value = Format(Now, "hh:nn:ss")                 ' 시간
    ws.Cells(r, 3).Value = ws.Range("C6").Value                    ' 종목코드
    ws.Cells(r, 4).Value = ws.Range("D6").Value                    ' 종목명
    ws.Cells(r, 5).Value = ws.Range("E6").Value                    ' 현재가
    ws.Cells(r, 6).Value = ws.Range("F6").Value                    ' 기준가
    ws.Cells(r, 7).Value = ws.Range("G6").Value                    ' 전일대비
    ws.Cells(r, 8).Value = ws.Range("H6").Value                    ' 등락률
    ws.Cells(r, 9).Value = ws.Range("I6").Value                    ' 거래량
    ws.Cells(r, 10).Value = ws.Range("J6").Value                   ' 거래대금
    ws.Cells(r, 11).Value = ws.Range("K6").Value                   ' 체결량
    ws.Cells(r, 12).Value = ws.Range("L6").Value                   ' 체결강도
    ws.Cells(r, 13).Value = ws.Range("M6").Value                   ' 상한가
    ws.Cells(r, 14).Value = ws.Range("N6").Value                   ' 하한가
    ws.Cells(r, 15).Value = ws.Range("O6").Value                   ' 시가
    ws.Cells(r, 16).Value = ws.Range("P6").Value                   ' 고가
    ws.Cells(r, 17).Value = ws.Range("Q6").Value                   ' 저가
    ws.Cells(r, 18).Value = ws.Range("R6").Value                   ' 종가
    ws.Cells(r, 19).Value = ws.Range("S6").Value                   ' 전일종가
    ws.Cells(r, 20).Value = ws.Range("T6").Value                   ' 우선매도잔량
    ws.Cells(r, 21).Value = ws.Range("U6").Value                   ' 우선매수잔량
    ws.Cells(r, 22).Value = ws.Range("V6").Value                   ' 우선매도건수
    ws.Cells(r, 23).Value = ws.Range("W6").Value                   ' 우선매수건수
    ws.Cells(r, 24).Value = ws.Range("X6").Value                   ' 총매도잔량
    ws.Cells(r, 25).Value = ws.Range("Y6").Value                   ' 총매수잔량
    ws.Cells(r, 26).Value = ws.Range("Z6").Value                   ' 총매도건수
    ws.Cells(r, 27).Value = ws.Range("AA6").Value                  ' 총매수건수
    ws.Cells(r, 28).Value = ws.Range("AB6").Value                  ' 미결제약정
    ws.Cells(r, 28).Value = ws.Range("AC6").Value                  ' 미결제전일대비
    ws.Cells(r, 30).Value = ws.Range("AD6").Value                  ' 이론가
    ws.Cells(r, 31).Value = ws.Range("AE6").Value                  ' 전일시가
    ws.Cells(r, 32).Value = ws.Range("AF6").Value                  ' 전일고가
    ws.Cells(r, 33).Value = ws.Range("AG6").Value                  ' 전일저가

✅ 전체 구문 설명

vba

ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

이 표현은 다음과 같은 의미를 가집니다:

🔍 한 줄씩 완전 분해 설명

파트 의미
ws 현재 작업 중인 Worksheet 객체 (예: Sheet1)
ws.Rows.Count 해당 시트의 최대 행 수 → 1048576 (엑셀 365 기준)
ws.Cells(ws.Rows.Count, 1) A열의 마지막 셀 = A1048576
.End(xlUp) A열 맨 아래에서 위쪽으로 올라가다가 데이터가 있는 셀을 만나면 멈춤
.Row 그 셀의 행 번호 반환 (예: 데이터가 A500까지 있다면 → 500)
+ 1 다음 줄(501)에 데이터를 쓰기 위해 1을 더함

🧠 예시로 쉽게 이해하기

예: A열이 아래와 같이 되어 있다고 가정

A열 값
A1 일자
A2 2025/04/14
A3 2025/04/15
A4 (비어 있음)

이때:

vba

ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

👉 A3까지 데이터가 있으므로 → 3

vba

ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

👉 4 반환 → 다음 데이터를 A4에 추가 가능

💡 실전에서 주로 어떻게 쓰이나?

✅ 실시간 데이터 누적 저장 구조에서

vba

Dim r As Long
r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

ws.Cells(r, 1).Value = Now
ws.Cells(r, 2).Value = 현재가

이렇게 하면, 매번 자동으로 아래 행으로 한 줄씩 누적됩니다.

✨ 응용 팁

목적예시 코드
B열 기준 마지막 행 찾기 ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
비어 있는 셀에 데이터 쓰기 ws.Cells(마지막행 + 1, 열번호).Value = 값
전체 시트 마지막 행 찾기 ws.UsedRange.Rows.Count (단, 정확도 떨어짐)

✅ 결론

vba

ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

이 한 줄은

"A열에서 마지막으로 값이 있는 셀을 찾고, 그 아래 셀의 행 번호를 반환한다."

→ 실시간 데이터 누적 저장, 로그 기록, 거래 내역 저장 등에 필수적인 VBA 패턴입니다.

3-2. 이동평균선 (MA) 수식 (엑셀 수식 or VBA)

vba

    ' 기술 분석 (현재가: 열 6 기준)
    Dim ma5 As Double, ma10 As Double, ma20 As Double, ma60 As Double, ma120 As Double
    Dim bbAvg As Double, bbStd As Double
    Dim hasEnoughData As Boolean: hasEnoughData = False
    
    On Error Resume Next
    If r >= 6 + 120 Then hasEnoughData = True
    If Not hasEnoughData Then Exit Sub
    On Error GoTo 0
    
    ' 이동평균선
    ma5 = WorksheetFunction.Average(ws.Range("E" & r - 4 & ":E" & r))
    ma10 = WorksheetFunction.Average(ws.Range("E" & r - 9 & ":E" & r))
    ma20 = WorksheetFunction.Average(ws.Range("E" & r - 19 & ":E" & r))
    ma60 = WorksheetFunction.Average(ws.Range("E" & r - 59 & ":E" & r))
    ma120 = WorksheetFunction.Average(ws.Range("E" & r - 119 & ":E" & r))

3-3. 볼린저밴드 계산 (엑셀 수식 or VBA)

vba

    ' 볼린저 밴드
    Set last20 = ws.Range("E" & r - 19 & ":E" & r)
    bbStd = WorksheetFunction.StDev(last20)
    bbAvg = ma20

📁 부록: 주요 컬럼명 정리 (필요 시 CSV 헤더용)

일자,시간,종목코드,종목명,현재가,기준가,전일대비,등락률,거래량,거래대금,체결량,체결강도,
상한가,하한가,시가,고가,저가,종가,전일종가,우선매도잔량,우선매수잔량,
우선매도건수,우선매수건수,총매도잔량,총매수잔량,총매도건수,총매수건수,
미결제약정,미결제전일대비,이론가,전일시가,전일저가

✅ 시트 구성:

  • Sheet1, Sheet2, Sheet3 (현재 vba는 Sheet1에 적용했음.)

✅ 데이터 구조 요약 (Sheet1 기준)

  • 5번째 row (엑셀 기준 6행): 타이틀 행 (일자, 시간, 종목코드, 종목명, 현재가, … 1분봉, 5분봉, 10분봉, 이동평균선, 볼린저밴드)
  • 6번째 row (엑셀 기준 7행)부터: 실시간 데이터 누적 기록

이제 요청하신 VBA 구성 요소는 다음과 같이 작성될 예정입니다:

VBA 코딩 완료하고 실행할때 화면
VBA 코딩 완료하고 실행할때 화면에서 업데이트를 눌러줘야지 영웅문4DDE랑 통신이 가능합니다.(영웅문4 DDE가 먼저 실행 되 있어야 합니다.)

 

VBA 코딩 완료후 동영상으로 실행한 상황입니다.(6번행은 현재 데이터를 보여주고 7번부터 순차적으로 밑으로 1초마다 누적으로저장 됩니다.)

9시 장시작 후 엑셀로 저장하셨다가 자동매매,매수,매도 하실때 자료의 데이터를 활용할수 있습니다.

📌 최종 구현 항목 요약

항목 설명
✅ 시작 버튼 실시간 DDE 데이터를 1초마다 Sheet1의 7행부터 누적 저장
✅ 중지 버튼 실시간 저장 중지
✅ 초기화 버튼 Sheet1의 7행 이후 데이터 전체 삭제
✅ 실시간 계산 각 Row 마지막에 1분/5분/10분봉, 이동평균선(5/10/20/60/120), 볼린저밴드(상한/중심/하한) 자동 계산
✅ 버튼 UI 시트에 명시적으로 표시, 실행 가능하게 매핑됨

다음은 업로드하신 Excel 구조를 기준으로 완전 자동화된 실시간 데이터 기록 + 분석 + 버튼 UI까지 포함한 VBA 전체 코드입니다. 아래 내용을 Excel VBA 편집기 (Alt + F11)에 붙여 넣고, Sheet1에 버튼을 연결하면 작동합니다.

✅ 1. VBA 모듈 코드 (통합)

vba

' 모듈 전체: 실시간 수집 + 기술분석 계산 + 버튼 제어
Dim isRunning As Boolean
Dim startTime As Double

'시작
Sub 시작버튼_Click()
    If isRunning Then Exit Sub
    isRunning = True
    Application.OnTime Now + TimeValue("00:00:01"), "기록반복"
End Sub

'중지
Sub 중지버튼_Click()
    If isRunning Then
        On Error Resume Next
        Application.OnTime EarliestTime:=nextRunTime, Procedure:="기록반복", Schedule:=False
        isRunning = False
        MsgBox "수집이 중지되었습니다.", vbInformation
    End If
End Sub

'초기화
Sub 초기화버튼_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    If MsgBox("데이터를 초기화할까요?", vbYesNo + vbQuestion, "초기화 확인") = vbYes Then
        ws.Rows("7:" & ws.Rows.Count).ClearContents
        MsgBox "초기화 완료!"
    End If
End Sub

'반복 호출
Sub 기록반복()
    If Not isRunning Then Exit Sub
    
    기록저장
    
    nextRunTime = Now + TimeValue("00:00:01")
    Application.OnTime EarliestTime:=nextRunTime, Procedure:="기록반복", Schedule:=True
    
End Sub

' 실시간 데이터 저장 + 기술지표 계산(이동평균선,볼린저밴드)
Sub 기록저장()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim r As Long: r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    
    ' 실시간 수집 (6행 기준 DDE 데이터 사용)
    ws.Cells(r, 1).Value = "'" & Format(Now, "yyyy/mm/dd")   ' 일자
    ws.Cells(r, 2).Value = Format(Now, "hh:nn:ss")                ' 시간
    ws.Cells(r, 3).Value = ws.Range("C6").Value                    ' 종목코드
    ws.Cells(r, 4).Value = ws.Range("D6").Value                    ' 종목명
    ws.Cells(r, 5).Value = ws.Range("E6").Value                     ' 현재가
    ws.Cells(r, 6).Value = ws.Range("F6").Value                     ' 기준가
    ws.Cells(r, 7).Value = ws.Range("G6").Value                    ' 전일대비
    ws.Cells(r, 8).Value = ws.Range("H6").Value                    ' 등락률
    ws.Cells(r, 9).Value = ws.Range("I6").Value                     ' 거래량
    ws.Cells(r, 10).Value = ws.Range("J6").Value                   ' 거래대금
    ws.Cells(r, 11).Value = ws.Range("K6").Value                   ' 체결량
    ws.Cells(r, 12).Value = ws.Range("L6").Value                   ' 체결강도
    ws.Cells(r, 13).Value = ws.Range("M6").Value                   ' 상한가
    ws.Cells(r, 14).Value = ws.Range("N6").Value                   ' 하한가
    ws.Cells(r, 15).Value = ws.Range("O6").Value                   ' 시가
    ws.Cells(r, 16).Value = ws.Range("P6").Value                   ' 고가
    ws.Cells(r, 17).Value = ws.Range("Q6").Value                   ' 저가
    ws.Cells(r, 18).Value = ws.Range("R6").Value                   ' 종가
    ws.Cells(r, 19).Value = ws.Range("S6").Value                   ' 전일종가
    ws.Cells(r, 20).Value = ws.Range("T6").Value                   ' 우선매도잔량
    ws.Cells(r, 21).Value = ws.Range("U6").Value                   ' 우선매수잔량
    ws.Cells(r, 22).Value = ws.Range("V6").Value                   ' 우선매도건수
    ws.Cells(r, 23).Value = ws.Range("W6").Value                   ' 우선매수건수
    ws.Cells(r, 24).Value = ws.Range("X6").Value                   ' 총매도잔량
    ws.Cells(r, 25).Value = ws.Range("Y6").Value                   ' 총매수잔량
    ws.Cells(r, 26).Value = ws.Range("Z6").Value                   ' 총매도건수
    ws.Cells(r, 27).Value = ws.Range("AA6").Value                   ' 총매수건수
    ws.Cells(r, 28).Value = ws.Range("AB6").Value                   ' 미결제약정
    ws.Cells(r, 28).Value = ws.Range("AC6").Value                   ' 미결제전일대비
    ws.Cells(r, 30).Value = ws.Range("AD6").Value                   ' 이론가
    ws.Cells(r, 31).Value = ws.Range("AE6").Value                   ' 전일시가
    ws.Cells(r, 32).Value = ws.Range("AF6").Value                   ' 전일고가
    ws.Cells(r, 33).Value = ws.Range("AG6").Value                   ' 전일저가
  
    ' 기술 분석 (현재가: 열 6 기준)
    Dim ma5 As Double, ma10 As Double, ma20 As Double, ma60 As Double, ma120 As Double
    Dim bbAvg As Double, bbStd As Double
    Dim hasEnoughData As Boolean: hasEnoughData = False
    
    On Error Resume Next
    If r >= 6 + 120 Then hasEnoughData = True
    If Not hasEnoughData Then Exit Sub
    On Error GoTo 0
    
    ' 이동평균선
    ma5 = WorksheetFunction.Average(ws.Range("E" & r - 4 & ":E" & r))
    ma10 = WorksheetFunction.Average(ws.Range("E" & r - 9 & ":E" & r))
    ma20 = WorksheetFunction.Average(ws.Range("E" & r - 19 & ":E" & r))
    ma60 = WorksheetFunction.Average(ws.Range("E" & r - 59 & ":E" & r))
    ma120 = WorksheetFunction.Average(ws.Range("E" & r - 119 & ":E" & r))
    
    ' 볼린저 밴드
    Set last20 = ws.Range("E" & r - 19 & ":E" & r)
    bbStd = WorksheetFunction.StDev(last20)
    bbAvg = ma20
    
    ' 결과 화면 디스플레이 (열 순서: 35 ~ 42)
    ws.Cells(r, 35).Value = ma5
    ws.Cells(r, 36).Value = ma10
    ws.Cells(r, 37).Value = ma20
    ws.Cells(r, 38).Value = ma60
    ws.Cells(r, 39).Value = ma120
    ws.Cells(r, 40).Value = bbAvg + 2 * bbStd
    ws.Cells(r, 41).Value = bbAvg
    ws.Cells(r, 42).Value = bbAvg - 2 * bbStd
End Sub

✅ 2. Sheet1 버튼 UI 구성 (엑셀 내부)

  1. Sheet1 열기
  2. [개발도구] 탭 → [삽입] → 버튼(양식 컨트롤) 선택
  3. 시트 상단에 아래 버튼 3개 삽입 후 각각 매크로 연결:
버튼이름 연결할 매크로
▶ 시작 시작버튼
⏹ 중지 중지버튼
🔄 초기화 초기화버튼

📁 저장 시 주의사항

  • 반드시 .xlsm 형식으로 저장해야 VBA가 포함됩니다.
  • 자동 실행 방지를 위해 Application.OnTime 사용

📌 알아 두면 좋은 것들

✅ VBA에서 "일자"를 '2025/04/11 형식으로 기록하는 방법

vba

ws.Cells(r, 1).Value = "'" & Format(Date, "yyyy/mm/dd")
  • 앞에 '를 붙이면 Excel은 해당 값을 문자열로 인식하고, 날짜 자동 변환을 방지합니다.
  • Format(..., "yyyy/mm/dd")는 슬래시 포함 형식

🎯 수정된 코드 (기록저장() 안에 있는 '일자')

vba

ws.Cells(r, 1).Value = Format(Date, "yyyy/mm/dd") ' ← 처음에 사용한것
'결과 : 13/4/25
ws.Cells(r, 1).Value = "'" & Format(Date, "yyyy/mm/dd") ' ← 문자열로 일자 저장
'결과 : '2025/04/13

📝  적용해 보시고 모르는것 있으시면 댓글 달아주세요. 확인해서 알려드릴께요 !!! (실시간 작동 화면은 월요일에 녹화해서 추가해 드릴께요)

반응형
저작자표시 비영리 변경금지
It 관련 글
더 보기

티스토리툴바