Check Expert + VBA로 종목별 Intra 데이터 누적쌓기

정말 오랜만에 쓰는 글은 나의 생존 신고 겸, 아이패드를 갖기 위한 몸부림이다.

코스콤의 Check Expert+ 단말기와 엑셀 프리미엄이 데이터는 엄청나게 많다. 종목의 실시간 데이터부터 과거 일별 데이터, 거기에 Intra 데이터까지. Intra 데이터는 쉽게 말해 과거의 기간 데이터이다. Intra 10분 데이터면 10분 간격으로 원하는 데이터 항목의 통계치 (시/고/저/종가, 체결량, 거래대금 등)를 쭉 볼 수 있는 것이다. 이 Intra 데이터가 개인적인 생각으로는 엑셀 프리미엄이 주는 가치 중 가장 큰 비중을 차지하고 있지 않을까 생각해본다. 기본 정보나 실시간 시세와 같은 것들은 굳이 엑셀 프리미엄을 사지 않더라도 DDE나 RTD를 통해서 어느 정도 커버가 되니깐.

무튼 오늘은 엑셀 프리미엄을 사용하면서, 특히 너무 많은 종목의 Intra 데이터를 조회하고 저장할 때 필요한 노가다를 VBA랑 같이 엮어서 원터치로 해결한 방법을 소개할까 한다. 그러면서 엑셀 프리미엄에 대한 소개도 하고 불만도 얘기하고.

 

CIS: Intra 데이터 함수 소개

우선 엑셀 프리미엄을 통해 Intra 데이터를 어떻게 받을 수 있는지 소개부터 하겠다. 간단하다, "함수 빌더"를 써라. 최신 버전의 엑셀 프리미엄에는 "함수 빌더"라는 것이 있어서 내가 엑셀 프리미엄 명령어를 숙달하지 않더라도 GUI를 통해서 좀 더 쉽게 사용할 수 있도록 도와준다. 아래 스크린샷 같은 함수 빌더가 없다면? 연락해서 업그레이드를 받으시길 추천합니다 :>

위 캡처에서 빨간 네모로 표시한 "함수 빌더"를 누르면 함수 빌더 창이 뜬다. 여기서 더 이상 내가 굳이 설명하지 않더라도 하나씩 눌러보면 이해가 될 정도로 GUI를 훌륭하게 만들어주셨다. Check가 꾸준히 업데이트가 되면서 엑셀 프리미엄도 점점 더 사용자가 쓰기 쉽도록 변모해나가는게 보여서 사용자로서 기분이 좋다.

A1에 커서를 두고 함수 빌더를 사용을 완료하면, 함수 빌더를 통해 만들어진 CIS함수가 A1에 박혀있는 것을 볼 수 있고, 그 외의 모든 정보들은 CIS 함수가 알아서 엑셀을 채운 것들이다. 위 캡처에서는 "10M"으로 주기 옵션을 주었기 때문에 시간이 10분 간격으로 Intra 데이터가 나오는 것을 볼 수 있다.

 

여러 종목의 CIS 데이터 재배열

보통 사람이 하고자 하는 일이 함수 한 방으로 끝날 일이 별로 없다. 이제 두 종목의 CIS 데이터를 조회를 해보자.

한국인이라면 삼성전자와 하이닉스...?!

두 종목을 조회하면 그 데이터가 수평으로 나열된다는 점을 깨닫게 된다. 데이터베이스 테이블처럼 엑셀을 만들어놔야 피벗테이블도 쓸 수 있고, 필터도 걸 수 있고, 아니면 간단한 수식이라도 옆 column에 걸어서 쓸 수도 있으므로 나는 위 엑셀을 아래처럼 바꿀 것이다.

이렇게 재정렬해야 필터를 걸어서 시간순으로 볼 수도 있겠지

두 종목이니까 그냥 손으로 했지, 100개 종목을 봐야 한다면? 위 캡처가 주식선물 스프레드니까, 우리나라 150개 주식선물을 다 긁어오고 싶다면? Intra 데이터가 엑셀 프리미엄이 주는 아주 좋은 기능이지만, 슬프게도 원하는 종목이 많아질 수록 수작업이 매우 힘들어진다. 기본적으로 여러 종목의 Intra 데이터를 수평으로 나열해주고, 이를 사용자가 바꿀 수 있는 옵션이 없기 때문이다.

 

재배열을 위한 VBA 코드

이걸 다 조회해서 재정렬하려면 코딩 뿐이 답이 없다. 나는 이렇게 VBA 작업을 했다.

• "코드"라는 Worksheet에 필요한 종목을 리스트업해서 그 범위 이름을 지어준다. 나는 RANGE_CODES 이라고 했다.
• RANGE_CODES 에 있는 종목 별로 반복을 돌면서 Worksheet를 생성시켜 CIS함수를 실행한다. 반복을 다 돌면 종목 수 만큼 Worksheet가 만들어져있겠지. (매우 많다는 말)
• 이렇게 만들어진 Worksheet 별로 반복을 돌면서 Intra 데이터를 긁어서 누적해서 쌓을 Worksheet에다가 column에 맞게 복사해준다.
• 그리고 다시 개별 Worksheet을 싹 지운다.

급하신 분을 위한 바로 코드 공개

Sub GenerateSheetsIntraData()
    Dim range_codes_stock As Range
    Dim n_codes_stock As Long
    Dim i As Long
    Dim sheetname_data As String
    Dim stockcode As String
    Dim formula_CIS As String
    
    n_codes_stock = Range("RANGE_CODES").Count
    
    For i = 1 To n_codes_stock
        stockcode = Sheets("코드").Cells(1 + i, 1).Value
        
        formula_CIS = "=CIS( ""20201123"", ""20201208"", -1, -1, ""1M"", FALSE, ""STKFS" & stockcode & """, ""20008,20010,20011"", ""ASC"", ""withtable=true;"")"
        sheetname_data = "Data" & stockcode
        
        Sheets.Add.Name = sheetname_data
        With Sheets(sheetname_data)
            .Range("A1").formula = formula_CIS
        End With
    Next i
End Sub

Sub RemoveSheets()
    Dim range_codes_stock As Range
    Dim n_codes_stock As Long
    Dim i As Long
    Dim sheetname_data As String
    Dim stockcode As String
    
    n_codes_stock = Range("RANGE_CODES").Count
    
    Application.DisplayAlerts = False
    For i = 1 To n_codes_stock
        stockcode = Sheets("코드").Cells(1 + i, 1).Value
        sheetname_data = "Data" & stockcode
        Sheets(sheetname_data).Delete
    Next i
    Application.DisplayAlerts = True
End Sub

Sub CumulateDataBetweenSheets()
    Dim n_codes_stock As Long
    Dim i As Long
    Dim sheetname_data As String
    Dim stockcode As String
    Dim n_row_prev As Long
    
    Dim n_row_data As Long
    Dim n_col_data As Long
    
    Dim range_test As Range
    
    n_codes_stock = Range("RANGE_CODES").Count
    
    For i = 1 To n_codes_stock
        stockcode = Sheets("코드").Cells(1 + i, 1).Value
        sheetname_data = "Data" & stockcode
        
        Sheets("Data누적").UsedRange.Calculate
        
        With Sheets("Data누적")
            'n_row_prev = Sheets("Data누적").UsedRange.SpecialCells(xlCellTypeLastCell).Rows.Count
            n_row_prev = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        
        With Sheets(sheetname_data)
            .UsedRange.Calculate
            n_row_data = .Cells(.Rows.Count, "A").End(xlUp).Row
            n_col_data = 4
            '.Range(.Cells(2, 1), .Cells(n_row_data, n_col_data)).Copy Sheets("Data누적").Cells(n_row_prev + 1, 2)
            .Range(.Cells(3, 1), .Cells(n_row_data, n_col_data)).Copy
        End With
        
        With Sheets("Data누적")
            .UsedRange.Calculate
            Set range_test = .Range(.Cells(n_row_prev + 1, 2), .Cells(n_row_prev + 1, 2))
            range_test.PasteSpecial
            
            n_row_data = .Cells(.Rows.Count, "B").End(xlUp).Row
            .Range(.Cells(n_row_prev + 1, 1), .Cells(n_row_data, 1)).Value = stockcode
        End With
    Next i
End Sub

코드 이해를 위한 "코드" worksheet의 모습 공개

이렇게 해서 150종목의 재정렬 노가다를 피할 수 있었다고 한다.

조금 시간이 지난 코드이다 보니 정확히 맞는 코드라고 확신이 들지는 않지만, 혹시라도 참조하시는 분이 있다면... 넓은 마음으로 봐주시길 바랍니다. VBA 코딩은 디버깅이 더럽기 때문에 남의 코드를 보는 고통이 더욱 크다는 것을 알기 때문입죠 :(

 

쿠키 단락: Notify?!

CIS 함수빌더의 부가정보에는 "notify"라는 기능이 있다.

오호라

CIS함수가 발동되어 Intra 데이터를 불러와서 엑셀에 쓰고 완료되는 시점에서 내가 준비해놓은 VBA 함수를 발동시키도록 한다는 옵션이다. 얼마나 아름다운가. 그래서 내가 간단히 Subprocedure로 만들어서 Msgbox만 띄워보려고 했는데 아쉽게도 발동하지가 않더라. 혹시라도 사용해보신 분이 계시다면 노하우를 알려주시면 좋겠습니다 :>

 

그럼 다음에 또 엑셀로 지지고 볶는 날이 있다면 다시 돌아올 수 있으면 좋겠다. 오늘은 여기까지!