ic


엑셀을 잘 표현할 수 있는 노래는... EXCEL/VBA




https://youtu.be/e3-5YC_oHjE
특히 VLOOKUP 함수로 뭔가 찾으려고 했는데, 에러 뜨는 상황이 떠오릅니다.
I have climbed the highest mountains
I have run through the fields
Only to be with you
Only to be with you
I have run I have crawled
I have scaled these city walls
These city walls
Only to be with you
But I still haven't found
What I'm looking for
But I still haven't found
What I'm looking for...






[Xing API]xingAPI활용교육샘플v1.3.xlsm(3)-사용자 정의 폼의 코드 증권

이번에는 사용자 정의 폼안의 코드를 살펴보려고 한다. 다행히 폼의 코드는 다음과 같이 간단하게 세 개의 프로시저가 있다. 프로시저의 이름을 보면 "_"가 가운데 있는 것을 볼 수 있다. "_" 왼쪽은 개체의 이름이고 "_"의 오른쪽은 이벤트(예를 들어 사용자가 버튼을 클릭하는 것이나 폼이 화면에 보이는 것 등등이 이벤트이다)가 발생할 때 해당 이벤트를 가리킨다.
즉 (개체이름) + _ + (이벤트) 와 같은 형식으로 구성되어 있다.
  • Private Sub UserForm_Initialize()
  • Private Sub btnLogin_Click()
  • Private Sub XASession_Login_Login(ByVal szCode As String, ByVal szMsg As String)

  • Private Sub UserForm_Initialize()
    Userform은 화면에 뜨는 전체 대화상자를 말한다. 즉 다음과 같은 것이 Userform이다.

    이런 Userform이 화면에 뜨기 전에 사전에 필요한 준비가 있을 것이다. 그런 작업은 _Initialize에 넣어 두는 것이다.
    Private Sub btnLogin_Click()
    btnLogin은 [로그인]이라는 버튼이다. 이 버튼은 증권사의 서버에 접속을 하는 코드를 갖고 있어, 사용자가 서버,아이디,암호,공인인증서 암호를 입력하고 클릭할 때 실행되는 프로시저이다.

    Private Sub XASession_Login_Login(ByVal szCode As String, ByVal szMsg As String)
    XASession_Login은 지난 시간에 말한 대로 일반적인 컨트롤이 아니라 Xing API가 제공하는 XASession개체이다. btnLogin을 클릭하여 접속을 요청하면 그 결과(접속 성공 또는 실패)를 이 프로시저를 통해 알려준다. 즉 우리가 실행하는 게 아니라 호출을 당하는(말이 이상하지만) 프로시저인데, 이런 류의 프로시저 또는 함수를 콜백함수라고 한다.
    '-------------------------------------
    ' 화면이 초기화될때 호출
    '-------------------------------------
    Private Sub UserForm_Initialize()
    '-------------------------------------
    ' 서버콤보컨트롤에 서버주소를 넣어주고 첫번째 서버를 표시한다.
    ' 서버 추가

    cbServer는 서버주소를 선택할 수 있는 콤보상자의 이름이다. 여기 선택할 목록을 미리 집어 넣어야 할 것이다. 그래서 콤보상자의 AddItem 메서드(일종의 함수)를 이용하여 서버주소("hts.ebestsec.co.kr"와 "demo.ebestsec.co.kr")를 추가하는 것이다.
        cbServer.AddItem "hts.ebestsec.co.kr"         ' 실서버
    cbServer.AddItem "demo.ebestsec.co.kr" ' 모의서버

    ' 처음에는 실서버 선택
    앞서 서버목록상자인 cbServer에 두 개의 아이템을 입력하였다. 이때 목록중 기본값으로 보일 항목을 선택하는 것이 ListIndex 이다(프로퍼티) 0을 주면 가장 먼저 입력한 항목(이 경우엔 "hts.ebestsec.co.kr")이 화면에 보이게 된다.
        cbServer.ListIndex = 0
    '-------------------------------------
    End Sub
    '-------------------------------------
    ' 로그인 버튼을 클릭했을때 호출
    '-------------------------------------
    Private Sub btnLogin_Click()
    '-------------------------------------
    ' 서버 연결중일때에는 서버연결이 실패하므로 먼저 연결을 끊는다.
    XASession는 세션을 관리하는 개체이다. 즉 증권사 서버와의 연결을 유지/관리하는 개체이다. 서버에 연결하기 전에 먼저 확인해야 할 사항은 현재 세션이 연결된 상태인지 아닌지 살펴야 한다. 이미 연결되어 있는 데, 다시 연결할 필요는 없을 것이다. 그런데 이 코드에선 연결되어 있다면 끊어 버린다. 다음은 서버의 연결상태를 확인한다. IsConnected() 프로퍼티는 세션의 연결상태를 True 또는 False값으로 돌려준다. bValue는 연결상태결과를 받아 If문에서 판단을 한다. 즉 연결된 상태라면 DisconnectServer() 메서드를 호출하여 연결을 끊는다. 새로 연결하기 위한 것이다.
        bValue = XASession_Login.IsConnected()
    If bValue = True Then
    XASession_Login.DisconnectServer
    End If
    '-------------------------------------
    '-------------------------------------
    ' 서버 연결
    이번에는 ConnectServer()메서드를 호출하여 선택한 서버(cbServer.Text)로 연결을 시도한다. 연결이 성공하면 True이고 실패하면 False이다. 그 아래 If문에서는 연결에 실패하는 경우 "서버연결실패" 라는 대화상자를 띄운 후 종료(Exit Sub)할 것이다.
        bValue = XASession_Login.ConnectServer(cbServer.Text, 0)
    If bValue = False Then
    MsgBox "서버연결실패"
    Exit Sub
    End If
    '-------------------------------------
    '-------------------------------------
    ' 로그인
    ' 포트는 더이상 사용하지 않으므로 0 을 넣어준다.
    서버와의 연결이 성공했다면 이번에는 아이디, 암호, 공인인증서 번호를 입력하여 로그인한다. 일반인이 생각하는 것과 달리 서버 연결과 로그인은 별개의 문제이다. 로그인은 Login()메서드를 호출한다. 연결과 마찬가지로 그 결과는 True 또는 False로 돌려준다. 그러나 서버 연결 실패와 달리 If문에서 로그인에 실패해도 Exit Sub를 사용하지 않는다. 이것은 사용자의 입력실수로 로그인에 실패해도 다시 입력하도록 배려한 것이다.
        bValue = XASession_Login.Login(txtID, txtPwd, txtCertPwd.Text, 0, False)
    If bValue = False Then
    MsgBox "로그인 전송 실패"
    End If
    '-------------------------------------
    End Sub
    '-------------------------------------
    ' Login Event : 로그인 결과값이 온다.
    '-------------------------------------
    Private Sub XASession_Login_Login(ByVal szCode As String, ByVal szMsg As String)
    앞서 말한 바와 같이 XASession_Login_Login()프로시저는 콜백함수로서 사용자가 호출하는 함수가 아니다. 상대편에서 호출을 하는 것이다. 이때 szCode 와 szMsg는 매개변수인데, 여기에 콜백함수를 통해 결과가 전달된다.
        '-------------------------------------
    ' szCode 가 "0000" 이면 성공 나머지는 실패
    위의 주석에 밝힌 바와 같이 szCode는 로그인 결과가 담겨 있다. 문자열 "0000"이면 로그인에 성공한 것이며 현재의 Userform(Me는 Userform 자신을 말한다. 즉 현재의 코드는 Userform에서 작성된 것이므로 Me는 자기 자신을 가리킨다)을 Hide()메서드를 호출하여 화면에서 감춘다. 종료를 해버리면 세션이 끝나 서버와의 연결이 끊어진다. 그러나 로그인에 실패하면 szMsg를 통해 오류 메시지를 보내고 이를 MsgBox를 통해 알려준다.
        If szCode = "0000" Then
    Me.Hide ' 화면을 숨긴다.
    Else
    MsgBox "[" & szCode & "] " & szMsg ' 메시지 표시
    End If
    '-------------------------------------
    End Sub

    제자가 스승께 물었다 '재귀함수가 무엇입니까?' 삽질


    셀 범위가 아닌 배열로 차트 만들기 EXCEL/VBA

    차트를 그리려면 셀 범위에 데이터가 있어야 하는 게 당연하다. 그러나 반드시 그럴 필요는 없다. 셀 범위 대신 배열을 계열의 데이터로 대신할 수 있다. 다만 엑셀이 아닌 vba가 필요하다. 다음의 코드는 배열을 갖고 차트를 만드는 예이다.
    Sub ChartFilledWithArray()
    Dim i As Long
    Dim x(1000, 0) As Double
    Dim y(1000, 0) As Double

    x(0, 0) = 0
    y(0, 0) = 0

    For i = 1 To 1000
    x(i, 0) = i
    y(i, 0) = y(i - 1, 0) + WorksheetFunction.NormSInv(Rnd())
    Next

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    With ActiveChart.SeriesCollection
    If .Count = 0 Then .NewSeries
    If Val(Application.Version) >= 12 Then
    .Item(1).Values = y
    .Item(1).XValues = x
    Else
    .Item(1).Select
    Names.Add "_", x
    ExecuteExcel4Macro "series.x(!_)"
    Names.Add "_", y
    ExecuteExcel4Macro "series.y(,!_)"
    Names("_").Delete
    End If
    End With
    ActiveChart.ChartArea.Select

    End Sub
    데이터 계열 편집 대화상자를 열어 보면, 셀 범위의 주소 대신 배열의 값이 입력되어 있는 것을 볼 수 있다.

    Lookup()함수의 은밀한 사생활 EXCEL/VBA

    vlookup()함수에 밀려 주목받지 못하는 형제들이 있는데, hlookup(), lookup()이 그들이다. 그중 lookup()에는 이상한 성질이 있는데, 찾으려는 값이 검색범위을 벗어나 큰 값인 경우(가령 찾는 값이 6인데, 검색범위가 1~5사이라면) 마지막 데이터를 돌려준다는 것이다.

    위의 그림에서 1~5사이의 값을 주면 옆 컬럼의 One, Two, ~ Five값을 가져오는 작업을 lookup()함수를 사용하면 다음과 같다.
    =LOOKUP(D8,A8:A12,B8:B12)

    그런데 A8:A12 범위를 벗어난 값 6(=D8)을 주면 B8:B12의 마지막 항목(Five)을 돌려준다. #NA를 돌려줘야 정상인 것 같은 데, 최신버전의 엑셀에서도 여전하다. 그런데 이런 개떡같은 성질을 잘 이용할 수 있다.

    vlookup, lookup 같은 함수들의 특징은 목록에서 먼저 매치되는 값을 돌려준다는 점이다. 그러나 마지막에 매치되는 값을 찾는 경우에는 다른 방법이 없다. 그러나 위의 lookup의 개떡같은 버그를 이용하여 마지막에 매치되는 결과를 돌려주게 할 수 있다.

    위의 그림과 같이 A열에는 ID1ID2가 번갈아 여러 번 나온다. 우리가 찾는 것은 ID2인데, 그중 마지막에 나온 ID2의 오른쪽 열의 값 Data 678이다. 그림에서 보듯이 D열에서는 마지막 ID2와 매치되는 Data 678을 구하였다. 이를 구하려면 수식은 다음과 같다.
    =LOOKUP(2,1/(A1:A6=D1),B1:B6)

    찾는 값을 입력할 위치에 2가 들어가 있고, 찾는 범위는 A1:A6이 아니라 1/(A1:A6=D1)이다. 제대로 입력된 것은 B1:B6이다. 세 개의 인수중 제일 중요한 부분은 이상하게 들어간 21/(A1:A6=D1)이다.

    가장 먼저 이해해야 하는 부분은 1/(A1:A6=D1)이다. (A1:A6=D1)은 찾으려는 값(ID2)과 A열의 목록(ID1, ID2, ID1, ID2, ID1, ID2)이 같은 지 확인하는 논리연산이다. 다르면 False를, 같으면 True을 돌려준다. 연산의 결과는
    {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
    이다. 그리고 True값은 숫자로 따지면 1, False는 0이다.

    이 값을 가지고 1을 나누는 연산을 하게 되면,
    {1/0; 1/1; 1/0; 1/1; 1/0; 1/1}
    이다. 1/0은 컴퓨터에선 무한대를 표시할 수 없으므로 #DIV/0!에러이다.
    {#DIV/0!;1;#DIV/0!;1;#DIV/0!;1}

    이 목록에서 첫 인수인 2를 찾으려는 것이다. {#DIV/0!;1;#DIV/0!;1;#DIV/0!;1}에서 2와 매치되는 값도 없고, 2라는 값은 그 목록 범위를 벗어난 (언제나) 큰 값이다. 따라서 마지막 인수인 B1:B6에서 마지막 값, Data 678을 돌려줄 것이다.

    [Xing API]xingAPI활용교육샘플v1.3.xlsm(2)-참조 및 사용자 정의 폼 증권

    지난 번 포스팅에서는 전체적인 개요를 설명하였다. 예제파일은 비록 하나의 엑셀파일에 불과하지만, 하나의 시스템이고 그 안에는 배울 수 있는 정보가 많이 있다. 그래서 전체적인 시스템의 구조와 코드블럭들이 어느 기능을 하는 지 정도만 살펴보았다. 앞으로는 각 코드블럭을 하나 하나 꺼내어 코드해설을 달아보려고 한다.

    오늘은 [참조]와 [사용자 정의 폼]이다. 참조는 프로젝트레벨에서 외부라이브러리를 가져다 사용할 수 있도록 하는 절차이기도 한다. 사용자 정의 폼은 그럴 듯해보이지만 현재기준에서 보면 원시적인 사용자 인터페이스이다. 여기서 사용자 정의 폼은 로그인을 위한 도구이며 이를 담당할 XASession개체의 컨테이너 역할을 한다.

    [참조]
    애초에 엑셀은 증권사 서버와 시세데이터를 받고 주문을 보내는 등의 통신을 하는 게 목적이 아니다. 그러나 COM(Component Object Model)이라는 MS기술 덕분에 외부의 기능을 가져다 사용할 수 있다. 우리가 API를 이용하여 알고트레이딩 프로그램을 만들 수 있는 것도 COM이라는 기술덕분이다.

    외부의 기능을 가져오기 위해서 먼저 필요한 것은 참조라는 것이다. 기존에 제공된 샘플을 개조해서 만든다면 참조가 필요없다(이미 참조되었겠지만). 그러나 아예 새 파일부터 시작하는 경우에는 참조라는 과정을 해야 한다.
    비주얼베이직 에디터(VBE)의 메인메뉴에서 [도구]-[참조]를 클릭하면 [참조]대화상자가 나온다.
    [그림][참조]대화상자

    이번 샘플의 참조를 확인해보면 [사용 가능한 참조] 목록상자를 보면 eBest Xing DataSet Lib와 eBest Xing Session Lib라는 게 체크되어 있다. eBest Xing DataSet Lib는 조회TR과 실시간TR을 위한 것이다. eBest Xing Session Lib 는 서버연결, 로그인을 위해 필요한 것이다.

    [유저폼]
    Userform 또는 사용자 정의폼이라는 것은 일반적인 엑셀 매크로 프로그래밍에서는 보기와 달리 사용빈도가 높진 않다, 이번 교육샘플 파일에 있는 유저폼은 서버에 접속하여 로그인하는 기능을 제공한다.
    [그림][사용자 정의 폼]

    유저폼의 구성을 보면 [서버주소]를 선택하는 콤보상자가 있고, [아이디], [비번], [공인인증서] 번호를 넣을 수 있는 3개의 텍스트상자가 있다. 그리고 그 아래에는 [로그인]버튼이 있다. 그런데 [로그인]버튼 왼쪽에는 회색의 정사각형 버튼 같은 것이 보인다. 이것은 일반적으로 유저폼에서 제공하는 컨트롤이 아니라, 이베스트 API에서 제공하는 접속을 위한 XASession 컨트롤이다.

    XASession개체는 서버에 접속하여 로그인하고 , 엑셀이나 파일을 종료하지 않는 한 증권사 서버와 계속 통신하기 위해 접속을 유지하는 기능을 제공한다. 따라서 세션이 끊어지면 다시 접속해야 한다.

    새 파일에 유저폼을 추가하려면 [프로젝트 탐색기]에서 [VBAProject] 트리를 선택하고 마우스 오른버튼을 클릭한 후
    [그림]팝업메뉴
    [그림]프로젝트에 추가된 사용자 정의폼

    [삽입]-[사용자 정의 폼]을 클릭한다. 새로운 [UserForm1]이 VBAProject의 폼이라는 폴더 밑에 추가된 것을 볼 수 있다.
    이제 필요한 컨트롤들을 배치해야 하는 데, 가장 필요한 것이 XASession컨트롤이다.
    [그림][도구 상자]

    그러나 컨트롤을 선택하고 배치하기 위한 [도구상자]에는 기본적인 컨트롤만 보일 뿐 XASession컨트롤은 보이질 않는다. 다시 도구상자 빈 곳에 마우스오른버튼을 클릭하면 팝업창이 나오는데 이때 [추가 컨트롤]을 클릭한다.
    [그림][추가]컨트롤 대화상자

    [추가 컨트롤]대화상자의 [사용가능한 컨트롤] 목록의 맨끝으로 스크롤하면 하단에 위치한 [XASession Class]를 체크한다.
    [그림][추가 컨트롤]대화상자
    이제 [도구 상자]컨트롤 대화상자의 끝에 새로운 컨트롤이 보일 것이다. 이것이 XASession 컨트롤이다.
    [그림]XASession컨트롤이 추가된 [도구 상자]

    [Xing API]xingAPI활용교육샘플v1.3.xlsm(1) 증권

    Xing API를 익히는 데 가장 편리한 수단은 예제파일을 보고 따라하는 방식인데, 그중 가장 편리한 방법은 이베스트투자증권에서 제공하는 엑셀예제파일을 분석하는 것이다. 이번에 코드해설을 해볼 요량으로 하나 집어 본 것이 xingAPI활용교육샘플v1.3.xlsm이다.

    예제파일에서 코드는 대략 세 곳에 있다. 하나는 워크시트내, 또 하나는 사용자 정의폼, 마지막 하나는 일반모듈이다.



    (1) 워크시트는 [시세], [주문&계좌], [STOPLOSS]로 구성되어 있다.
  • [시세]워크시트 -
    한 개의 종목에 대한 기본적인 시세정보(현재가, 전일대비, 등락, 거래량 등등), 호가창, 시간별 시고저종, 봉차트로 구성되어 있다
  • [주문&계좌]워크시트 -
    주문기능, 잔고내역 조회, 체결내역 조회 기능이 있다.
  • [STOPLOSS]워크시트 -
    보유종목에 대한 손절매를 걸어두고 , 손절매를 실행하거나 중단하는 기능이 있다.

  • (2) 사용자 정의 폼(유저폼)은 시스템에 접속을 하기 위한 것으로 실매매 서버 또는 데모서버에 접속할 수 있다. 또한 아이디, 비밀번호, 공인인증서 번호 입력을 위한 텍스트박스를 가지고 있다.

    (3) 일반 모듈은 등락에 표시하기 위한 화살표("↑", "▲", " ", "↓", "▼")를 돌려주는 Function 프로시저를 가지고 있다.

    그리고 Workbook개체에도 코드가 하나 있는 데, 엑셀파일을 열 때 자동으로 로그인하기 위한 사용자 정의 폼을 띄우는 역할을 한다.
    Private Sub Workbook_Open()
    ' 로그인 화면 표시
    frmLogin.Show
    End Sub
    Private Sub Workbook_Open()은 프로시저를 호출하여 실행하는 것이 아니라 해당 엑셀파일 즉 Workbook을 여는 경우 자동으로 실행되는 프로시저이다. 엑셀에서 여는 모든 파일마다 실행하는 것이 아니라 이 코드가 담긴 엑셀파일을 열때마다 실행되는 것이다.

    Workbook은 개체의 이름이고 _Open은 열기 이벤트를 가리킨다. VBA에서 특정 엑셀파일을 열 때마다 자동으로 실행되는 동작이 필요하다면 Work_Open() 프로시저를 만들면 된다. 그러나 이 프로시저는 다른 곳(가령 워크시트, 유저폼, 일반모듈 등)에 두어도 소용없다. 오직 [현재_통합_문서]라는 개체안에 있어야 한다. Sub앞에 Private은 프로시저의 범위가 [현재_통합_문서]라는 개별전용공간에 있어 다른 곳에서 호출할 수 없다는 의미이다.



    [지표식]RSI 기준선 긋기 YL

    오랜만에 YL(YesLanguage)를 보고 있는데, 예스스탁 게시판의 올라온 질문과 답변을 보고 공부를 하는 중이다. 앞으로 이런 포스팅을 자주 할 생각인데, 이런 저런 프로그래밍 문법을 보고 하는 것도 좋치만, 실제 코드를 보면서 하는 것이 효과적이다.

    [질문]
    RSI의 기본적인 과매수 기준값인 70 이상에서 RSI값이 70 이하로 내려오는 시점을 기준으로 삼고 그 기준선 이후 이전봉 몸통 이탈시 매도선을 긋고, 동시에 과매수 기준값 30 이하에서 RSI값이 30 이상 올라가는 시점을 기준으로 삼고 그 기준선 이후 이전봉 몸통을 돌파시 매수선을 긋고 싶습니다.

    [답변]
    Input : Period(14),sig(9), LPercent(30), SPercent(70);
    Var : RSIV(0),ps(0),T(0);

    RSIV = RSI(Period);
    ps = PriceScale*20;

    If CrossDown(RSIV,70) Then
    T = 1;

    If T == 1 And C < L[1] Then
    {
    T == 2;
    Var1 = C;
    Var2 = Var1 + ps*1;
    Var3 = Var1 + ps*2;
    Var4 = Var1 + ps*3;
    Var5 = Var1 + ps*4;
    Var6 = Var1 + ps*5;
    }

    If Crossup(RSIV,30) Then
    T = -1;

    If T == -1 And C > H[1] Then
    {
    T = -2;
    Var1 = C;
    Var2 = Var1 - ps*1;
    Var3 = Var1 - ps*2;
    Var4 = Var1 - ps*3;
    Var5 = Var1 - ps*4;
    Var6 = Var1 - ps*5;
    }
    MessageLog("T %f, RSI %f, Var %f, C %f, H[1] %f ", T, RSIV, Var1, C, H[1]);
    Plot1(Var1);
    Plot2(Var2);
    Plot3(Var3);
    Plot4(Var4);
    Plot5(Var5);
    Plot6(Var6);
    처음 두 줄은 변수의 선언이다.
    Input : Period(14),sig(9), LPercent(30), SPercent(70);
    Input 다음에 선언한 변수는 외부에서 설정하는 것으로 변수의 내용은 지극히 일반적인 RSI설정변수이다. 14개의 봉을 계산하고 9개의 봉을 시그널로 사용한다. 그리고 과매도수준은 30, 과매수수준은 70이다. 신기한 것은 ()로 하면 보통 배열을 의미하지만 , 여기선 선언과 동시에 값 할당을 하는 것이다.
    Var : RSIV(0),ps(0),T(0);
    Var 다음에 나오는 변수는 내부적인 변수이다. RSIV는 RSI값을 저장하고 ps는 PriceScale*20을 저장한다. 그리고 T는 아래의 If문에서 True인 경우를 다음 봉까지 정보상태를 유지하기 위한 플래그변수의 역할을 한다.
    RSIV = RSI(Period);
    ps = PriceScale*20;
    앞서 선언한 내부변수용도에 맞게 RSI값을 저장하고 PriceScale*20을 저장한다. RSI()함수와 PriceScale은 예스랭귀지 매뉴얼을 참조하기 바란다.

    위의 프로그램은 각 봉마다 반복되는데, RSI값이 70을 아래로 통과하는 것과 30을 상승하여 통과하는 경우로 나눌 수 있다. 그리고 70을 아래로 통과하는 경우 다시 현재봉의 종가가 이전 봉의 저가를 크거나 작은 경우로 나뉜다. 즉 4가지의 경우를 생각할 수 있다.
    즉 VBA코드로 표현하자면
    If CrossDown(RSI, 70) = True Then
    If C < L[1] Then
    (경우#1)
    Else If C >= L[1] Then
    (경우#2)
    End If
    Else If CrossDown(RSI, 70) = False Then
    If C < L[1] Then
    (경우#3)
    Else If C >= L[1] Then
    (경우#4)
    End If
    End If
    그러나 모두 경우를 다 고려할 필요는 없다.
    If CrossDown(RSIV,70) Then
    T = 1;
    RSI값이 위에서 아래로 향하면서 70선을 통화한다면 플래그변수 T=1로 설정한다. 값 1은 큰 의미는 없다. RSI가 70을 뚫고 내려왔다는 사실(상태)을 이후 봉에서 유지하는 플래그변수이다. 다음에 나오는 If문에서 그 상태(RSI가 70을 뚫고 내려왔다는 사실)하에 종가(C)가 전일저가(L[1])보다 작은 지를 확인한다(위의 경우#2에 해당)
    If T == 1 And C < L[1] Then
    {
    T == 2;
    Var1 = C;
    Var2 = Var1 + ps*1;
    Var3 = Var1 + ps*2;
    Var4 = Var1 + ps*3;
    Var5 = Var1 + ps*4;
    Var6 = Var1 + ps*5;
    }
    처음에는 T라는 변수의 역할이 무척 이상하다. 계산에 사용되는 것도 아니고, If문에서 T=2인지를 확인하지도 않는다. 그러나 이것은 다음 봉을 염두에 둔 상태변수이다. 현재봉에서 70을 뚫고 내려왔어도 종가가 전일저가보다 반드시 작으리란 법은 없다. 그러나 이후 봉에서는 작을 수 있다. 물론 이후봉에서는 70을 뚫고 내려왔다는 전일봉의 상태를 T변수로 확인하고자 하는 것이다.

    위의 코드에서 Var1, Var2 등등의 변수인데, Input절이나 Var절에서 선언한 적은 없다. 이것은 예스랭귀지 특유의 변수이며 자동으로 제공하는 것이다. 이런 면모는 프로그래밍 언어로선 상당히 독특하다.

    이제 RSI값이 70을 뚫고 내려왔다는 상태(T=1)에서 현재봉의 종가가 전봉저가보가 작다(C < L[1])면 T=2가 된다(값이 2라는 것은 중요하지 않다. 그냥 상태를 표시하는 것이라서 다른 것과 구분되는 숫자이어야 한다는 점이 중요하다. 값을 3으로 바꾸어도 결과는 마찬가지이다)

    그리고 그림을 그리기 위한 변수(Var1~Var6)를 설정한다. 아래의 그림은 Plot()함수로 그림을 그린 모습인데(3번째 차트) 하단의 RSI차트와 비교하면 흥미롭다.

    RSI값이 30을 뚫고 상승하는 경우도 마찬가지이다. 고로 이후 생략

    웹사이트에서 데이터 가져오는 방법 - Table Capture Data science

    파이썬이나 R에서 데이터를 가져오는 방법은 책과 각종 블로그에서 많이 소개하여 널리 알려져 있다. 게을러서 그런 걸 하지 않지만 웹사이트를 만들 때 디자인보다는 데이터위주의 HTML(그래서 XML이 나온 것이지만)을 사용하였다면 복잡한 도구가 필요하지 않았을 텐데 하는 생각이 든다.

    크롬브라우저를 주력으로 사용한다면 'Table Capture'라는 애드인-확장프로그램을 추천한다. 웹페이지내 테이블만 골라 선택하고 테이블의 내용을 클립보드에 복사해두거나 구글스프레드시트에 옮길 수 있다(프로버전인 경우 xlsx, csv파일로 저장도 가능) 프로그래밍과 html문서구조분석이 어려운 분이라면 이 방법이 좋다.


    가령 네이버금융의 추천종목 웹페이지를 스크랩핑한다면 다음과 같다. 웹페이지가 로딩된 후 Table Capture아이콘을 클릭하면 선택가능한 테이블이 리스트업된다. 추천종목페이지에는 4개의 테이블이 보인다. 그리고 선택한 테이블은 그림에서 보듯이 주황색 굵은 테두리가 생긴다.

    [URL]크롬 웹스토어-Table Capture



    Option Delta trading for Momentum Trading 금융공학

    트레이딩에서 가장 어려운 것이 모멘텀매매이다. 지나간 차트의 궤적을 돌이켜보면 당연히 큰 수익을 낼 수 있을 것처럼 보인다(실제 그럴 수 있지만) 그러나 현재 시점에서 모멘텀을 확인하기가 매우 어렵다. 그래서 큰 수익만큼이나 리스크도 크다. 모멘텀 트레이딩을 감으로 따라가기는 무서운 것이 사실이다. 선물/옵션 트레이딩의 경우 델타 만큼 롱/숏을 하면서 따라가는 전략이 있다. 다음의 스트랭글 포지션(롱스트랭글)은 일정밴드이상을 벗어나는 경우 수익을 얻을 수 있다.
    아래의 코드에서도 델타를 사용하고 있는데, 델타는 다양한 의미로 사용된다. 델타는 옵션가격과 기초자산가격간의 관계로 함수로 나타내는 곡선의 기울기이다. 옵션가격의 변화속도를 의미한다. 이전 포스팅에서도 말한 바와 같이 델타는 해당 행사가격의 콜옵션이 ITM으로 만기를 맞을 확률로 해석된다. 덽타가 0.5인 ATM콜옵션은 현재 시점에서 만기를 맞을 경우 ITM 이 될 확률이 50%라는 의미이다. 0.1인 OTM 콜옵션은 ITM확률이 10%에 불과하다. 덽타는 기초자산으로 옵션을 헤징할 때 헤지비율로도 사용된다. 현재의 델타수준에서 델타중립, 즉 델타가 0의 값을 가지도록 하려면 기초자산을 얼마나 가져야 하는 지 보여주는 지표이다. 델타가 0.4인 옵션의 경우 0을 만들기 위해 -0.4의 델타값을 갖는 기초자산은 매매해야 한다. 이를 델타헤징이라고 한다.
    #############################
    # Option Delta trading for Momentum Trading
    #############################
    sd=15
    pc=2650
    pt<-2600:2700
    t<- 1/252

    a<-bsoDelta(pt,pc-sd,0.02,0.02,0.12,t,'put')
    b<-bsoDelta(pt,pc+sd,0.02,0.02,0.12,t,'call')
    opt<-bso(pt, pc-sd, 0.02, 0, 0.12, t, 'put')+bso(pt, pc+sd, 0.02, 0, 0.12, t, 'call')

    par(mfrow=c(1,1))
    plot(pt, opt, type='l', bty='n')
    plot(pt, (a+b), ylim=c(-1, 1))
    abline(h=0)
    abline(v=pc-sd)
    abline(v=pc+sd)

    옵션 델타와 행사가격 금융공학

    옵션델타의 의미는 다음의 세가지로 해석된다. 우선 1) 가격변동위험에 대해 중립적인 포트폴리오포지션을 구축하기 위해 필요한 기초자산의 계약수의 옵션계약수에 대한 비율을 의미한다. 이론적으로 볼 때 옵션 포지션의 크기를 기초자산의 크기와 대응시키는 데에 쓰이는 숫자를 의미한다.
    따라서 이러한 해석에 따르면 옵션델타는 기초자산등가비율(헤지비율 h=1/델타)로 해석될 수 있다.
    가령 델타가 0.5인 옵션은 기초자산가격이 1만큼 변할 때 프리미엄이 절반인 0.5만큼 변한다는 의미이다. 따라서 헤지하기 위해 현물 1단위당 옵션 2계약이 필요하게 됩니다. 즉 헤지비율(h)은 델타의 역수(=1/델타)가 됩니다. 만일 델타가 0.25인 콜옵션으로 현물 10단위를 헤지하려면 헤지비율 h = 1/0.25 = 4이므로 40계약의 콜을 매도하면 됩니다.
    2) 기초자산의 가격변동에 대한 옵션가격의 변동비율이다.
    3) 근사치로 볼 때 옵션가격이 만기에 ITM이 될 확률을 의미한다. 델타가 0.7인 옵션은 ITM(현재도 ATM을 지나 ITM상태이므로)으로 끝날 확률이 70%라는 의미이다.
    다음은 ITM-ATM-OTM별 콜과 풋옵션 델타의 모습이다.

    다음은 기초자산가격 흐름에 따른 콜과 풋옵션 델타의 모습이다. 사실 위의 그림에서 풋을 뒤집은 모습이다.

    그런데 보통 옵션그릭스를 구하는 코드는 많이 보았는데, 역으로 가령 델타가 주어졌을 때 행사가격이 얼마인지를 계산하는 코드는 찾아보길 힘들다. 다음은 델타를 행사가격으로 변환해주는 R의 코드이다.
    ###################
    # 옵션 가격 구하기
    ###################
    bso<-function(s,k,r,q,v,t,putcall){
    d1=log(s/k)+(r-q+0.5*v*v)*t
    d1=d1/(v*sqrt(t))
    d2=d1-v*sqrt(t)
    nd1=pnorm(d1)
    nd2=pnorm(d2)
    ans=s*exp(-q*t)*nd1-k*exp(-r*t)*nd2
    if(putcall=='put') {
    nd1=pnorm(-d1)
    nd2=pnorm(-d2)
    ans=k*exp(-r*t)*nd2-s*exp(-q*t)*nd1
    }
    if(t<=0)
    {
    ans=max(s-k,0)
    if(putcall=='put') ans=max(k-s,0)
    }
    return(ans)
    }

    ###################
    # 옵션 민감도
    ###################
    bsoDelta<-function(s,k,r,q,v,t,putcall){
    ds=s/10000
    p0=bso(s,k,r,q,v,t,putcall)
    p1=bso(s+ds,k,r,q,v,t,putcall)
    ans=(p1-p0)/ds
    return(ans)
    }

    bsoGamma<-function(s,k,r,q,v,t,putcall){
    ds=s/10000
    p0=bso(s,k,r,q,v,t,putcall)
    pu=bso(s+ds,k,r,q,v,t,putcall)
    pd=bso(s-ds,k,r,q,v,t,putcall)
    ans=(pu+pd-2*p0)/(ds^2)
    return(ans)
    }

    bsoVega<-function(s,k,r,q,v,t,putcall){
    dv=v/10000
    p0=bso(s,k,r,q,v,t,putcall)
    p1=bso(s,k,r,q,v+dv,t,putcall)
    ans=(p1-p0)/dv
    return(ans)
    }

    bsoTheta<-function(s,k,r,q,v,t,putcall){
    dt=t/10000
    p0=bso(s,k,r,q,v,t,putcall)
    p1=bso(s,k,r,q,v,t+dt,putcall)
    ans=(p1-p0)/dt
    return(ans)
    }

    ###################
    # 델타를 행사가격으로 변환
    # 30회 반복하면서 이분법으로 구하는 알고리즘이다.
    ###################
    bsD2K<-function(delta,s,r,q,v,t,putcall){
    kd=s/100; ku=s*2
    for (i in 1:30){
    km=(kd+ku)/2
    dm=bsoDelta(s,km,r,q,v,t,putcall)
    if(putcall=="call")
    if(dm>delta) kd<-km else ku<-km
    else
    if(abs(dm)>abs(delta)) ku<-km else kd<-km
    }
    return(km)
    }
    몇 가지 테스트를 해보면,
    > bsD2K(0.50,100,0.04, 0.01, 0.15,1,'call')
    [1] 104.0197
    > bsD2K(-0.50,100,0.04, 0.01, 0.15,1,'put')
    [1] 104.4136
    > bsD2K(0.25,100,0.04, 0.01, 0.2,1,'call')
    [1] 120.126
    > bsD2K(-0.25,100,0.04, 0.01, 0.2,1,'put')
    [1] 92.01019
    > bsD2K(0.10,100,0.04, 0.01, 0.2,1,'call')
    [1] 135.6922
    > bsD2K(-0.10,100,0.04, 0.01, 0.2,1,'put')
    [1] 81.45502
    > bsD2K(0.40,100,0.02, 0.00, 0.15,0.1,'call')
    [1] 101.5308
    > bsD2K(-0.40,100,0.02, 0.00, 0.15,0.1,'put')
    [1] 99.11967

    Stored Procedure in VBA EXCEL/VBA

    It have been a long time for me to write a stored procedure. I have already forgotten how to create the stored procedure and make a vba code using that. A week ago I was asked to write a program for the other guys to track and rate their stock-picking performance. I guess that a database would be needed to implement. The database should have the prices and their picking. Before beginning that, I made a demo procedure using the stored procedure.
    Sub demoSp()
    Dim adocmd As New ADODB.Command
    Dim adorst As New ADODB.Recordset

    Dim sDate As Date
    Dim eDate As Date
    Dim sp As String

    sp = "spReturns"

    eDate = #1/3/2014#
    sDate = #1/2/2014#

    If modDbCommon.connect_database <> 0 Then Exit Sub
    With adocmd
    .ActiveConnection = adoconn
    .CommandText = sp
    .CommandType = adCmdStoredProc
    .Parameters.Append .CreateParameter("sDt", adDate, adParamInput)
    .Parameters.Append .CreateParameter("eDt", adDate, adParamInput)
    .Parameters("sDt").Value = sDate
    .Parameters("eDt").Value = eDate
    Set adorst = .Execute
    End With


    With adorst
    Do While Not .EOF
    Debug.Print .Fields(0), .Fields(1), .Fields(2), .Fields(3), .Fields(4), .Fields(5), .Fields(6), .Fields(7), .Fields(8), .Fields(9)
    .MoveNext
    Loop
    End With

    Call modDbCommon.disconnect_database
    End Sub

    [까칠한 에러#3]TypeError: only length-1 arrays can be converted to Python scalars PYTHON

    예전에 겪은 에러인데, 참고삼아 기록을....TypeError: only length-1 arrays can be converted to Python scalars
    [CODE]
    import scipy.stats as sp
    import numpy as np
    import matplotlib.pyplot as plt
    from math import *
    x=np.linspace(0,3,200)
    mu=0
    sigma0=[0.25,0.5,1]
    color=['blue','red','green']
    target=[(1.2,1.3),(1.7,0.4),(0.18,0.7)]
    start=[(1.8,1.4),(1.9,0.6),(0.18,1.6)]
    for i in range(len(sigma0)):
    sigma=sigma0[i]
    y=1/(x*sigma*sqrt(2*pi))*exp(-(log(x)-mu)**2/(2*sigma*sigma))
    plt.annotate('mu='+str(mu)+', sigma='+str(sigma),
    xy=target[i], xytext=start[i],
    arrowprops=dict(facecolor=color[i],shrink=0.01),)
    plt.plot(x,y,color[i])
    plt.title('Lognormal distribution')
    plt.xlabel('x')
    plt.ylabel('lognormal density distribution')
    plt.show()

    [SOLUTION]
    math.abs() 또는 math.log10()과 같은 numpy가 아닌 math 기본 라이브러리의 함수는 numpy arrays와 잘 맞지 않는다. 따라서 math함수는 numpy의 math함수로 바꾸어야 한다.

    [BEFORE]
    y=1/(x*sigma*sqrt(2*pi))*exp(-(log(x)-mu)**2/(2*sigma*sigma))

    [AFTER]
    y=1/(x*sigma*np.sqrt(2*np.pi))*np.exp(-(np.log(x)-mu)**2/(2*sigma*sigma))

    [까칠한 에러#2]13 형식이 일치하지 않습니다 EXCEL/VBA

    아래와 같은 코드를 작성하였는데, 13 형식이 일치하지 않습니다 에러메시지가 나온다. 코드는 워크시트 여러 개를 담을 수 있는 워크시트 컬렉션 개체(shts)를 선언하고 현재 통합문서의 워크시트들을 담으려고 한 것이다.
    [CODE]
    Sub demoWorksheetsCollection()
        Dim shts As Worksheets
       
        Set shts = ThisWorkbook.Worksheets
       
    End Sub
    [SOLUTION]
    위의 에러는 선언한 변수와 할당한 개체의 타입이 서로 맞지 않아 생기는 것인데, ThisWorkbook.Worksheets는 Worksheets 컬렉션 개체를 돌려주는 것이 아니라 Sheets 컬렉션 개체를 돌려준다. Sheets 컬렉션 개체는 워크시트외에 차트시트 등을 포함한다.

    [BEFORE]
    Dim shts As Worksheets

    [AFTER]
    Dim shts As Sheets

    [까칠한 에러#1]'438' 런타임 오류가 발생하였습니다. 개체가 이 속성 또는 메서드를 지원하지 않습니다. EXCEL/VBA

    워크시트를 참조하는 방법중 가장 선호하는 것은 코드명을 이용하는 것이다. 워크시트에는 두 개의 이름을 가진다. 하나는 사용자가 워크시트 탭에서 보는 이름(name), 나머지 하나는 VBA가 내부적으로 사용하는 워크시트이름(codename)이다. 워크시트를 참조하는 경우 이름이 변경되는 것은 그리 바람직하지 않다. 워크시트 탭에서 보는 이름은 사용자가 변경을 할 수 있어 참조하기엔 적합하지 않다. 그래서 코드명을 사용하여 워크시트를 참조한다.

    위에서 [Sheet1], [Sheet2] 와 같이 ()밖에 있는 이름은 코드명이고 ()안의 [차트지표데이터조회] 같은 것이 워크시트 탭의 이름이다.

    그래서 평소처럼 코드명을 가지고 아래와 같이 코드를 해두었더니, '438' 런타임 오류가 발생하였습니다. 개체가 이 속성 또는 메서드를 지원하지 않습니다 와 같은 에러메시지를 만나게 되었다.
        Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = Workbooks.Open("C:\Users\noname.xlsx")
    Set ws = wb.Sheet1
    Sheet1은 워크시트의 코드명인데, 이 속성은 Workbook의 것이 아니라 VBProject의 것이라서 이러한 워크시트 참조는 에러가 난다. 그래서 Sheet1 대신 Worksheets("시트이름") 또는 Sheets(인덱스) 방식으로 수정해야 한다.

    실패경험담-어설픈 비밀번호 감추기 EXCEL/VBA

    엑셀의 셀안에 비밀번호를 넣어두면 어찌될까? 공유하지 않고 혼자 사용하는 것이라면, 누가 열어보지 않는 이상, 찜찜하지만 신경을 쓰지 않을 수도 있다. 그러나 비밀번호가 돈과 관련된 것이라면 신경이 쓰인다. 엑셀로 트레이딩 프로그램 프로토타입을 만들다 보니 계좌번호, 계좌비밀번호 등을 매번 입력하는 것보단 어딘가에 넣어두는 게 좋을 것 같아 일단 워크시트안에 넣어 두었다.

    문제는 이게 보이는 게 영 신경이 거슬린다. 여타 비밀번호 입력하는 창처럼 '*****'로 표시되면 좋을 것이다. 그래서 일단 이를 다음과 같이 셀 서식을 가지고 흉내를 내보았다.

    셀 서식에서 ';;;**'의 의미는 문자열을 * 문자로 표시하라는 의미이다. 만일 숫자까지 *으로 표시하게 하려면 '**;**;**;**'으로 하면 된다. 그러나 이것은 어디까지나 화면에 보이는 셀을 달리 보이게 하는 것일 뿐 [수식 입력줄]에 보이는 것까지 막을 순 없다.

    그래서 이런 식의 비밀번호 감추기는 어설프다. 그래서 이를 해결하는 방법은 워크시트보호를 이용하는 것이다. 워크시트 보호를 걸기 전에 먼저 할 일은 비밀번호 [셀 서식]중 [보호]탭의 내용을 변경하는 것이다.

    [잠금]은 셀의 변경을 막겠다는 의미이다. [잠금]은 [셀 서식]의 기본값이므로 미리 변경을 할 셀은 [잠금]을 체크해제해야 한다. [숨김]은 [수식 입력줄]에서 보여주지 않겠다는 의미이다. 보통 셀안의 수식이나 함수사용을 감추기 위한 것이다.
    이제 [검토]리본에서 [시트보호]를 이용하여 시트를 보호한다. 이제 비밀번호가 있는 셀 E3를 선택해도 [수식 입력줄]에는 아무 값이 보이지 않는다.


    그러나 아직도 어설프다. E3 셀을 참조하면 별 수 없이 비밀번호가 보이기 때문이다. 아직 셀 참조를 막는 기능을 보질 못했는데, 엑셀에 민감한 정보를 넣어두는 것은 하지 말자.

    에러처리전담반, On Error 팀을 찾아 온 낯선 손님 IFERROR()함수 EXCEL/VBA

    엑셀에 새로운 워크시트 함수가 추가되지만, 새로운 함수가 주목받고 애용되기는 힘들다. 새로운 워크시트함수가 아니어도 그 기능을 이미 수식으로 해결해오고 있고, 이미 익숙해져있기 때문일 수 도 있다. 새로운 워크시트함수(이젠 신참이 아니라 중고참급이 되었지만)중 가장 애용하는 것이 IFERROR()함수이다. 간단한 IFERROR()함수 예는 다음과 같다. 가령 A2셀을 B2셀값으로 나누는 연산에서 오류가 생기면 "계산오류"를 대신 출력한다.
    =IFERROR(A2/B2, "계산 오류")

    vba에서 워크시트함수를 사용하는 경우 함수에러를 대비하기 위해 여지껏 On Error문을 사용하였다.
        On Error Resume Next
    sym = "201N6308"
    var = WorksheetFunction.VLookup(sym, rngOpTable, 2, 0)
    If Err.Number <> 0 Then
    MsgBox "Error Raised" & vbNewLine & Err.Description
    Err.Clear
    End If
    그러나 워크시트함수의 IFERROR()함수를 On Error문을 대신하여 다음과 같이 사용할 수 있지 않을 까 싶다. 에러가 나면 상수값 xlErrNa를 돌려준다.
        sym = "201N6308"
    With WorksheetFunction
    Debug.Print .IfError(.VLookup(sym, rngOpTable, 2, 0), XlCVError.xlErrNA)
    End With
    그러나 에러메시지는 IfError가 없을 때와 동일하게 나온다. 이것은 아마도 IfError()가 평가되기전 VLookup()에서 발생한 에러를 IfError()에 전달되지 못하기 때문인 듯하다. WorksheetFunction개체는 에러값을 돌려주지 않고메시지박스로 에러를 뿜어내고 있다.
    그러면 이번에는 Application개체를 이용한 워크시트함수는 어떠한가? 지난 포스팅, [ Application과 WorksheetFunction에 대한 거짓 말 ]에서 밝힌 바와 같이 Application개체를 통한 워크시트함수는 에러를 돌려줄 수 있다.
        sym = "201N6308"
    With Application
    var = .IfError(.VLookup(sym, rngOpTable, 2, 0), XlCVError.xlErrNA)
    Debug.Print var
    End With
    var변수의 값 '2042'는 에러상수 XlCVError.xlErrNA의 값이다. 즉 Application.VLookup()은 WorksheetFunction.VLookup()과 달리 에러값을 돌려준다. 그러면 다음과 같은 코드는 어떨 까? Application.VLookup()과 WorksheetFunction.IfError()를 혼용해서 쓰는 막장같은 코드는? 우리가 원하는 대로 에러를 처리해줄 것인가?
        sym = "201N6308"
    var = WorksheetFunction.IfError(Application.VLookup(sym, rngOpTable, 2, 0), XlCVError.xlErrNA)
    Debug.Print var

    Application과 WorksheetFunction에 대한 거짓 말 EXCEL/VBA

    어쩌다 하는 강의에서 나름 중요하고 엑셀VBA의 강점중 하나로 소개하는 것이 VBA에서 엑셀의 워크시트함수를 가져다 쓸 수 있다는 점이다. 워크시트의 함수를 가져다 쓸 수 있게 되어 (1)어려운 알고리즘을 간단히 구현하고 , (2)속도도 훨씬 빠르고 (3)신뢰성 있는 코드를 만들 수 있는 것이다.

    워크시트함수를 사용하려면 WorksheetFunction 개체를 통해야 한다. 그러나 Application 개체를 통해서도 사용할 수 있다. 이런 설명을 하다 보면 둘 사이에 무슨 차이가 있는 가 하는 질문이 당연히 나온다.

    물론 둘 간의 차이는 없다. VBA 초창기에는 Application 개체를 사용하여 워크시트함수를 사용하는 것만 있었다. 그러나 이후 새로운 버전을 내놓으면서 기존의 VBA개체를 정비하면서 워크시트함수를 전담하기 위한 WorksheetFunction이 생겨났다. 그러나 집요하게 하위 호환성을 지원하는 MS답게 Application 개체도 계속 워크시트함수를 사용할 수 있도록 하였다. 그렇치 않으면 기존의 코드를 전부 고쳐야 하는 대란(대란이라는 표현이 과장일 수 도 있지만)이 일어 날 수 있다. 즉 둘 간의 차이는 세상에 등장한 시기의 차이 정도이다.

    그러나 거짓말을 하였다. 둘 간의 차이는 그게 전부가 아니다. WorksheetFunction이 아닌 Application으로 워크시트함수를 사용하면, (1)워크시트함수의 연산 에러값를 수용하고(WorksheetFunction은 걍 에러를 뱉어내서 당황하게 만들지만), (2)워크시트함수에 전달하는 매개변수를 배열로 하고 여러 개의 결과 값을 얻을 수 있다.

    예를 들어 VLookup()함수를 보자. VLookup()함수의 인수중 Lookup_value에는 보통 하나의 값이나 셀을 사용한다. 그러나 Application.Vlookup()에서는 여러 개의 값을 한번에 찾을 수 있다. 그러면 결국 VLookup()함수는 배열을 돌려준다. 여러 번의 VLookup()을 사용하지 않고 한번에 처리할 수 있다. 그냥 워크시트에서 배열수식으로 VLOOKUP()함수를 사용하는 것과 같다.

    이제 코드를 함 보자. 위의 그림과 같은 코드표에서 두 개의 종목코드 "201N6307", "301N6307"를 Array()함수로 묶어 배열로 넘겨 두 번째 컬럼에 있는 종목이름을 찾는다. 결과 두 개의 일치하는 값(C 1806 307.5 와 P 1806 307.5)이 있으므로 var에는 배열이 담길 것이다.
        sym = Array("201N6307", "301N6307")
    With Application
    var = .VLookup(sym, rngOpTable, 2, 0)
    Debug.Print var(1), var(2)
    End With
    그려면 테이블에서 없는 값을 찾는 경우에는 어떤 결과가 나올까? "301N6307" 을 살짝 고쳐 "301N6308"을 찾게 해보자.
        sym = Array("201N6307", "301N6308")
    With Application
    var = .VLookup(sym, rngOpTable, 2, 0)
    Debug.Print var(1), var(2)
    End With
    조용히 배열에 오류임을 표시해준다.
    WorksheetFunction의 지랄 맞은 에러창보다 낫다(이런 오류메시지는 디버깅에 도움이 되질 않는다)

    이런 코드는 VLookup()와 같은 종류인 Match()함수도 이런 활용이 가능하다.
        sym = Array("201N6307", "301N6307")
    With Application
    var = .Match(sym, rngOpTable.Columns(1), 0)
    Debug.Print var(1), var(2)
    End With

    오늘 증권시장은 놀고 있네요 증권

    오전 상황을 그려본 것인데(등가격 콜옵션 델타와 선물가격) 오후에도 잠잠하네요. 지루한 시간입니다.  누군가가 "315를 지켜야 한다"고 말하는 것 같습니다.

    그래서 저도 놉니다.


    DDE 클라이언트를 다시 만들어 보다 EXCEL/VBA

    예전에 DDE를 이용하여 선물 틱데이터를 모으는 포스팅(간단한 DDE프로그래밍)을 한 적이 있었는데, 그때에는 DDE로 수집하는 데이터가 선물 하나라서 별 불편은 없었다. 그런데 이번에는 여러 가지 금융상품(선물과 옵션)의 틱데이터를 쌓는 걸 해보았다. API를 하려다가 귀찮아서 그냥 DDE를 해보는 것이다. 일단 HTS(DDE서버 역할을 한다)에서 DDE로 끌어다 쓸 금융상품과 항목을 선택하여 엑셀로 DDE클라이언트 워크북을 만든다.


    DDE 항목중 시간이 변경될 때마다, 어느 상품의 시간항목이 변경되는 지 확인하여 해당 프로시저를 실행하도록 하는 것이다. 즉 각각의 항목마다 같은 일을 하는, 그러나 이름만 다른 프로시저를 걸어 두는 것이다. 프로시저는 해당 워크시트의 출력위치를 확인하고 DDE 업데이트 되는 항목을 복사-붙여넣는 것이다.
    Sub Start_DDE()
    Dim s As Worksheet
    Dim aLinks
    Dim i As Long

    '// 선물과 옵션의 DDE 시간항목
    Const ITEM_101N6000 As String = "EtkDS|eds!FUTURE.101N6000;시간"
    Const ITEM_201N6305 As String = "EtkDS|eds!OPTION.201N6305;시간"
    Const ITEM_201N6307 As String = "EtkDS|eds!OPTION.201N6307;시간"
    Const ITEM_201N6310 As String = "EtkDS|eds!OPTION.201N6310;시간"
    Const ITEM_201N6312 As String = "EtkDS|eds!OPTION.201N6312;시간"
    Const ITEM_201N6315 As String = "EtkDS|eds!OPTION.201N6315;시간"
    Const ITEM_201N6317 As String = "EtkDS|eds!OPTION.201N6317;시간"
    Const ITEM_201N6320 As String = "EtkDS|eds!OPTION.201N6320;시간"
    Const ITEM_201N6322 As String = "EtkDS|eds!OPTION.201N6322;시간"
    Const ITEM_201N6325 As String = "EtkDS|eds!OPTION.201N6325;시간"
    Const ITEM_201N6327 As String = "EtkDS|eds!OPTION.201N6327;시간"
    Const ITEM_301N6305 As String = "EtkDS|eds!OPTION.301N6305;시간"
    Const ITEM_301N6307 As String = "EtkDS|eds!OPTION.301N6307;시간"
    Const ITEM_301N6310 As String = "EtkDS|eds!OPTION.301N6310;시간"
    Const ITEM_301N6312 As String = "EtkDS|eds!OPTION.301N6312;시간"
    Const ITEM_301N6315 As String = "EtkDS|eds!OPTION.301N6315;시간"
    Const ITEM_301N6317 As String = "EtkDS|eds!OPTION.301N6317;시간"
    Const ITEM_301N6320 As String = "EtkDS|eds!OPTION.301N6320;시간"
    Const ITEM_301N6322 As String = "EtkDS|eds!OPTION.301N6322;시간"
    Const ITEM_301N6325 As String = "EtkDS|eds!OPTION.301N6325;시간"
    Const ITEM_301N6327 As String = "EtkDS|eds!OPTION.301N6327;시간"

    Call ClearContents '// 예전 기록을 지운다

    aLinks = ThisWorkbook.LinkSources(xlOLELinks)
    If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
    Select Case True
    Case aLinks(i) = ITEM_101N6000: ThisWorkbook.SetLinkOnData aLinks(i), "'On101N6000'"
    Case aLinks(i) = ITEM_201N6305: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6305'"
    Case aLinks(i) = ITEM_201N6307: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6307'"
    Case aLinks(i) = ITEM_201N6310: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6310'"
    Case aLinks(i) = ITEM_201N6312: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6312'"
    Case aLinks(i) = ITEM_201N6315: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6315'"
    Case aLinks(i) = ITEM_201N6317: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6317'"
    Case aLinks(i) = ITEM_201N6320: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6320'"
    Case aLinks(i) = ITEM_201N6322: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6322'"
    Case aLinks(i) = ITEM_201N6325: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6325'"
    Case aLinks(i) = ITEM_201N6327: ThisWorkbook.SetLinkOnData aLinks(i), "'On201N6327'"
    Case aLinks(i) = ITEM_301N6305: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6305'"
    Case aLinks(i) = ITEM_301N6307: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6307'"
    Case aLinks(i) = ITEM_301N6310: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6310'"
    Case aLinks(i) = ITEM_301N6312: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6312'"
    Case aLinks(i) = ITEM_301N6315: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6315'"
    Case aLinks(i) = ITEM_301N6317: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6317'"
    Case aLinks(i) = ITEM_301N6320: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6320'"
    Case aLinks(i) = ITEM_301N6322: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6322'"
    Case aLinks(i) = ITEM_301N6325: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6325'"
    Case aLinks(i) = ITEM_301N6327: ThisWorkbook.SetLinkOnData aLinks(i), "'On301N6327'"
    End Select
    Next
    End If
    Debug.Print "Starting DDE Recording..."
    End Sub

    Sub CopyToSheet(Item As String)
    '// 상품별로 워크시트(워크시트이름이 종목코드임)가 구성되어 있는데,
    '// 종목코드를 입력받아 Main워크시트에서 복사하여 붙여넣는다.
    '// 모든 항목이 공통된 작업을 하므로 다음과 같이 같이 사용하도록 한다
    Dim r1 As Long, r2 As Long, s As Worksheet, rng As Range

    Set s = ThisWorkbook.Worksheets(Item)
    r1 = WorksheetFunction.Match(Item, Main.Range("B:B"), 0)
    r2 = s.Range("B1048576").End(xlUp).Row + 1

    With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    Main.Range(Main.Cells(r1, 2), Main.Cells(r1, 15)).Copy
    s.Range(s.Cells(r2, 2), s.Cells(r2, 15)).PasteSpecial xlValues

    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .CutCopyMode = False
    End With
    End Sub

    '// 아래와 같은 프로시저를 종목수만큼 둔다.
    Sub On101N6000()
    '// 선물 데이터를 복사하도록 한다
    Call CopyToSheet("101N6000")
    End Sub
    (이하 생략)
    그런데 문제가 좀 있다. DDE 하는 동안은 복사하기-붙여넣기가 안된다.

    Xing API-선물옵션 과거 데이터 조회(2) 증권

    지난 번 포스팅에서 선물옵션의 과거데이터조회를 t8427 TR을 이용하였는데, 선물이 조회안되는 문제를 이베스트증권에 문의하였다. 담당자의 답은 조회한 선물이 현재물이라 안된다는 것이다. 이상하게 옵션은 되는 것 같은데, 암튼 당장 중요한 것은 아니라서 다른 TR을 찾아 보았다. 그래서 찾은 것이 t8416이다.

    t8416의 스펙에서 입력매개변수는 다음과 같은데, 중요한 건 단축코드, 주기구분(일간:2, 주간:3, 월간:4), 요청건수, 시작일자(YYYMMDD형식), 종료일자(YYYMMDD형식)이다(일자중 둘중 하나는 생략가능하다)
    ' t8416InBlock,기본입력,input;
    ' begin
    ' 단축코드,shcode,shcode,char,8;
    ' 주기구분(2:일3:주4:월),gubun,gubun,char,1;
    ' 요청건수(최대-압축:2000비압축:500),qrycnt,qrycnt,long,4;
    ' 시작일자,sdate,sdate,char,8;
    ' 종료일자,edate,edate,char,8;
    ' 연속일자,cts_date,cts_date,char,8;
    ' 압축여부(Y:압축N:비압축),comp_yn,comp_yn,char,1;
    ' End

    콜백함수를 통해 돌려받는 블럭은 t8416OutBlockt8416OutBlock1 두 가지이다. t8416OutBlock에서는 다음과 같다.
    ' t8416OutBlock,출력,output;
    ' begin
    ' 단축코드,shcode,shcode,char,8;
    ' 전일시가,jisiga,jisiga,float,6.2;
    ' 전일고가,jihigh,jihigh,float,6.2;
    ' 전일저가,jilow,jilow,float,6.2;
    ' 전일종가,jiclose,jiclose,float,6.2;
    ' 전일거래량,jivolume,jivolume,long,12;
    ' 당일시가,disiga,disiga,float,6.2;
    ' 당일고가,dihigh,dihigh,float,6.2;
    ' 당일저가,dilow,dilow,float,6.2;
    ' 당일종가,diclose,diclose,float,6.2;
    ' 상한가,highend,highend,float,6.2;
    ' 하한가,lowend,lowend,float,6.2;
    ' 연속일자,cts_date,cts_date,char,8;
    ' 장시작시간(HHMMSS),s_time,s_time,char,6;
    ' 장종료시간(HHMMSS),e_time,e_time,char,6;
    ' 동시호가처리시간(MM:분),dshmin,dshmin,char,2;
    ' 레코드카운트,rec_count,rec_count,long,7;
    ' End
    t8416OutBlock에서 나오는 시고저종과 거래량등 마지막 레코드의 그것이다. 중요한 것은 레코드카운트이다. 레코드카운트로 t8416OutBlock1의 레코드갯수를 알 수 있다. 물론 t8416InBlock의 요청건수가 레코드카운트와 일치할 수 있지만 자료가 요청한 만큼 존재하리라는 법은 없으므로 레코드카운트로 확인하는 것이 좋다.

    t8416OutBlock1의 출력은 다음과 같다. 일자,시고저종,거래량,거래대금,미결제약정 등을 돌려준다. 옵션그릭스까지 제공해주면 좋을텐데 그건 아쉽게 제공해주지 않는다.
    ' t8416OutBlock1,출력1,output,occurs;
    ' begin
    ' 날짜,date,date,char,8;
    ' 시가,open,open,float,6.2;
    ' 고가,high,high,float,6.2;
    ' 저가,low,low,float,6.2;
    ' 종가,close,close,float,6.2;
    ' 누적거래량,jdiff_vol,jdiff_vol,long,12;
    ' 거래대금,value,value,long,12;
    ' 미결제약정,openyak,openyak,long,12;
    ' End
    Dim WithEvents T8416 As XAQuery

    Sub RequestT8416()
    If T8416 Is Nothing Then Set T8416 = New XAQuery
    Dim szshcode As String, szgubun As String, szqrycnt As String
    Dim sdate As String, edate As String

    szshcode = [B5]: szgubun = [C5]: szqrycnt = [D5]
    sdate = [E5]: edate = [F5]

    With T8416
    .ResFileName = "\res\t8416.res"
    Call .SetFieldData("t8416InBlock", "shcode", 0, szshcode)
    Call .SetFieldData("t8416InBlock", "gubun", 0, szgubun)
    Call .SetFieldData("t8416InBlock", "qrycnt", 0, szqrycnt)
    Call .SetFieldData("t8416InBlock", "sdate", 0, sdate)
    Call .SetFieldData("t8416InBlock", "edate", 0, edate)
    Call .SetFieldData("t8416InBlock", "cts_date", 0, "")
    Call .SetFieldData("t8416InBlock", "comp_yn", 0, "N")
    If .Request(False) < 0 Then MsgBox "선물 전송오류"
    End With
    End Sub

    Private Sub T8416_ReceiveData(ByVal szTrCode As String)
    Dim szshcode As String, rec_count As Long, i As Long
    Dim row As Long

    With T8416
    szshcode = .GetFieldData("t8416OutBlock", "shcode", 0)
    rec_count = .GetFieldData("t8416OutBlock", "rec_count", 0)

    If rec_count > 1 Then [B8:I1048576].ClearContents
    row = 8
    For i = 0 To rec_count - 1
    Cells(row, 2) = .GetFieldData("t8416OutBlock1", "date", i)
    Cells(row, 3) = .GetFieldData("t8416OutBlock1", "open", i)
    Cells(row, 4) = .GetFieldData("t8416OutBlock1", "high", i)
    Cells(row, 5) = .GetFieldData("t8416OutBlock1", "low", i)
    Cells(row, 6) = .GetFieldData("t8416OutBlock1", "close", i)
    Cells(row, 7) = .GetFieldData("t8416OutBlock1", "jdiff_vol", i)
    Cells(row, 8) = .GetFieldData("t8416OutBlock1", "value", i)
    Cells(row, 9) = .GetFieldData("t8416OutBlock1", "openyak", i)
    row = row + 1
    Next
    End With
    End Sub

    Xing API-선물옵션 과거 데이터 조회 증권

    선물/옵션 데이터가 필요하여 이베스트증권의 Xing API를 사용하려고 하는데, 이베스트증권은 DevCenter같은 훌륭한 개발지원도구가 있지만 아주 만족스럽지는 않다. 제공해주는 샘플프로그램은 사람들이 많이 찾는 주식이나 선물호가/체결같은 것이 많은 데, 의외로 선물옵션의 과거 데이터를 조회하는 샘플이 없다. 아예 신한증권처럼 워드파일로 만든 API매뉴얼을 제공해주면 읽어보면 참고할텐데...

    DevCenter를 보면 조회용 TR중 t8427이 눈에 띈다. 오호라~ 이걸로 조회하는 구만. 그러나 막상 TR8427의 스펙을 보면 필드에 대한 설명이 따로 없어 필드에 어떤 값을 넣어야 할지 막막하다. 가령 t8427InBlockfo_gbn 필드는 선물을 조회하려는 지, 옵션을 조회하려는 지 구분하는 것인데, 뭘 넣어야 할지 모르겠다. 따로 설명이 없다.

    그래서 이베스트증권 게시판에는 이에 대한 질문과 답변이 있었는데,
    [질문]
    DevCenter에 기본입력의 코드설명이 없습니다. 입력코드 설명 부탁드립니다.

    [답변]
    안녕하세요. 관리자입니다.
    문의하신 8427 TR의 입력값 필드 정보입니다.
    fo_gbn : 선물옵션 구분 F:선물, O:옵션
    cp_gbn : 옵션콜풋 구분 2:콜, 3:풋
    dt_gbn : 일분구분 D:일, M:분
    date : 날짜 처음조회시 space, 다음조회시 Outblock의 date값 입력
    time : 시간 처음조회시 space, 다음조회시 Outblock의 time값 입력
    이용에 참고하시기 바랍니다. 감사합니다.
    다음은 6월만기 행사가격320.0인 콜옵션의 과거데이터를 조회하는 예이다.
    Dim WithEvents T8427 As XAQuery         '선물옵션과거데이터 시간대별 조회

    Sub RequestT8427()
    If T8427 Is Nothing Then Set T8427 = New XAQuery

    T8427.ResFileName = "\res\t8427.res"
    Call T8427.SetFieldData("t8427InBlock", "fo_gbn", 0, "O") '// 선물은 F, 옵션은 O
    Call T8427.SetFieldData("t8427InBlock", "yyyy", 0, "2018") '// 2018년
    Call T8427.SetFieldData("t8427InBlock", "mm", 0, "05") '// 5월
    Call T8427.SetFieldData("t8427InBlock", "cp_gbn", 0, "2") '// 콜옵션
    Call T8427.SetFieldData("t8427InBlock", "actprice", 0, "320") '// 행사가격
    Call T8427.SetFieldData("t8427InBlock", "focode", 0, "201N6320") '// 종목코드
    Call T8427.SetFieldData("t8427InBlock", "dt_gbn", 0, "D") '// 일간단위 데이터
    Call T8427.SetFieldData("t8427InBlock", "min_term", 0, "") '// 생략
    Call T8427.SetFieldData("t8427InBlock", "date", 0, "") '// 생략
    Call T8427.SetFieldData("t8427InBlock", "time", 0, "") '// 생략
    If T8427.Request(False) < 0 Then MsgBox "선물옵션 가격조회오류"
    End Sub

    Private Sub T8427_ReceiveData(ByVal szTrCode As String)
    Dim szfocode As String, szdate As String, sztime As String, i As Long
    Dim szpx As String

    szfocode = T8427.GetFieldData("t8427OutBlock", "focode", 0)
    szdate = T8427.GetFieldData("t8427OutBlock", "date", 0)
    sztime = T8427.GetFieldData("t8427OutBlock", "time", 0)

    Debug.Print szfocode, szdate; sztime; T8427.GetBlockCount("t8427OutBlock1")

    For i = 0 To 19
    szdate = T8427.GetFieldData("t8427OutBlock1", "date", i)
    szpx = T8427.GetFieldData("t8427OutBlock1", "close", i)
    Debug.Print i, szdate, szpx
    Next
    End Sub
    이렇게 조회를 하면 20개의 데이터를 얻을 수 있다. 물론 GetBlockCount(블럭명) 함수를 이용하면 데이터의 갯수를 알 수 있다. GetBlockCount("t8427OutBlock1")라고 하면 t8427OutBlock1 블럭의 데이터갯수를 돌려준다.

    SetFieldData()함수에 필요한 값을 적재해두고 Request()함수로 조회요청을 한다. 그러면 서버는 데이터를 조회하여 T8427_ReceiveData() 함수(vba에서 Sub프로시저이지만)를 호출하고 여기에서 값을 찾아 가도록 해준다(그래서 이를 콜백함수라고 한다. 내가 호출하는 게 아니라 반대편에서 콜하는 함수)
    여기서는 조회결과가 두 가지인데, 하나(블럭명)는 t8427OutBlock이고 또 하나는 t8427OutBlock1이다. t8427OutBlock은 종목코드와 날짜, 시간을 돌려주고 , t8427OutBlock1는 위의 목적인 과거 데이터(날짜,시간,시고저종 등등)이다.

    추가로 더 조회하려면 최초 조회시 얻은 날짜 값(szdate) T8427.GetFieldData("t8427OutBlock", "date", 0)의 값을 T8427.SetFieldData("t8427InBlock", "date", 0, szdate)처럼 변경하여 다시 조회한다.

    게시판에 참고할 만한 질문/답변이 하나 더 있는데, 참고삼아 기록을...
    [질문#1]
    1. 일별 분봉데이터 조회가 안됩니다.
    - 예를들어 2014년 07월 행사가260, 콜, 분(M), 1 로 조회를 하면 7월 만기일인 20140710일의 분봉데이터가 조회가 됩니다. 하지만 연속조회를 계속 해도 20140710일의 데이터만 조회되고 date항목에 20140709를 넣어도 그 전날 데이터가 조회가 되지 않습니다. 일별 분봉 데이터 조회 방법은 없나요?
    => 당사에서 옵션 분봉 데이터를 보관하는 주기는 현재 시점 기준으로 40영업일 입니다. 이는 현재 거래되는 종목이나 과거 종목이나 동일하게 적용되고 있습니다.

    [질문#2]
    2. 분봉 데이터의 경우 3개월 이내의 것만 되나요?
    - 2014, 07월의 경우 위에서 말한대로 만기일날만 나오지만 분봉데이터가 나오는데 06월 이전 부터는 분봉 데이터가 나오지 않습니다. 일봉(D)로 세팅하고 조회를 하면 과거 데이터가 모두 조회되는데 분봉은 되지 않네요. 분봉 데이터 제공은 3개월 이내의 것만 가능한가요?
    => 1번 질문에서 답변드렸듯이 분데이터는 현재 시점 기준으로 40영업일만 제공됩니다.

    [질문#3]
    3. 옵션 과거데이터 조회 TR은 t8427밖에 없나요? t8414나 t8415는 현재 월물 밖에 조회가 안되나요?
    => 과거 거래 종목 데이터는 t8427 TR로만 조회하실수 있습니다. t8414, t8415는 현재 거래중인 종목만 조회됩니다.
    그나저나 정작 내가 하고 싶은 선물데이터가 조회가 안되고 있다. 뭐가 부족한 건지...

    비베 고수님이 돌아가셨네요




    아주 고급진 비베정보를 이 분 덕에 많이 배웠는데, 얼마 전 교통사고로 돌아가셨다고 합니다. 부고기사를 보면서 칩 피어슨의 약력을 몰랐는데, 더욱 더 안타깝습니다.

    칩 피어슨 부고



    워크시트 출력을 빠르게 하는 방법 EXCEL/VBA

    vba의 속도를 높이는 방법중 가장 잘 알려진 것은 1)화면출력을 일시 정지하는 방법, 2)재계산을 잠시 자동에서 수동으로 변경하는 방법, 3)이벤트을 임시로 막는 방법이 있다. 1)번은 vba로는 화면입출력이 느려 부담스럽다는 것을 의미한다. 화면입출 횟수를 되도록 줄이는 것이 좋다. 2)를 하는 이유는 워크시트의 내용을 변경하면 모든 셀의 수식/함수가 이를 반영하느라 다시 계산한다(변경된 부분이 수식/함수와 전혀 무관한 곳이라도 말이다) 또한 3)의 경우 이벤트프로시저를 활용하는 경우가 많치 않아 그닥 필요하진 않지만, 워크시트에 출력하는 경우라면 _Change()이벤트가 있으면 매번 호출하게 된다. 그래서 아래와 같은 코드를 출력에 앞서 흔히 사용하게 된다.
        With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
    {화면출력하는 코드}
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True

    End With
    그러나 이것만 가지고는 부족할 수 있다. 가령 다음과 같은 반복문이 있다고 하자.

    For i = 0 To MultiRowCnt - 1
    If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
    Me.Cells(6 + i, 2).Value = getPx.GetMultiData(i, 0) '일자
    Me.Cells(6 + i, 3).Value = getPx.GetMultiData(i, 3) '시가
    Me.Cells(6 + i, 4).Value = getPx.GetMultiData(i, 4) '고가
    Me.Cells(6 + i, 5).Value = getPx.GetMultiData(i, 5) '저가
    Me.Cells(6 + i, 6).Value = getPx.GetMultiData(i, 6) '현재가
    Me.Cells(6 + i, 7).Value = getPx.GetMultiData(i, 7) '체결수량

    Next
    위의 코드는 500개의 시계열데이터를 요청받아 화면에 출력하는 것이다. 매 반복마다 워크시트의 특정 셀을 액세스하다보면 느려지기 마련이다. 특히 현재 작성중인 알고리즘트레이딩에서 1초에 시세가 1~3개씩 들어오는 콜백함수에 이런 부분이 포함되면 당연히 응답없음을 만나게 된다. 그래서 아래와 같이 배열을 미리 만들어 저장하고 한번에 출력하는 방법을 사용하게 된다. 배열 Data()는 500x6의 크기를 가지는 데, 그만큼에 해당하는 셀 영역 [B6:G505]에 출력한다.

    For i = 0 To MultiRowCnt - 1
    If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
    Data(i, 0) = getPx.GetMultiData(i, 0) '일자
    Data(i, 1) = getPx.GetMultiData(i, 3) '시가
    Data(i, 2) = getPx.GetMultiData(i, 4) '고가
    Data(i, 3) = getPx.GetMultiData(i, 5) '저가
    Data(i, 4) = getPx.GetMultiData(i, 6) '현재가
    Data(i, 5) = getPx.GetMultiData(i, 7) '체결수량

    Next
    [B6:G505] = Data
    참고로 엑셀을 빠르게 사용하는 팁 75 Excel Speeding up Tips을 소개한다.

    의미가 없어져 버린 IIf()함수 EXCEL/VBA

    코드를 한창 만들고 난 후 리뷰를 하는 과정이 있는데, 그 과정에서 알고리즘을 개선하거나 오류를 잡거나 코드를 줄이는 최적화를 하곤 한다. 얼마 전 아래와 같은 코드가 있었다.
    glDemoRun = IIf([C1].Value = "DEMO", True, False)

    이 코드에선 IIf() 함수(인라인 IF함수)를 이용하여 한 줄로 C1셀의 값이 DEMO인지 아닌지에 따라 논리값을 돌려준다. 그러나 따지고 보면 의미가 없다. 이미 [C1].Value = "DEMO" 연산의 결과 논리값이 나오기 때문이다. 그래서 코드는 다음과 같이 줄어 든다.
    glDemoRun = [C1].Value = "DEMO"

    또 다른 예를 들어 본다면, 가령 두 가지 이상의 조건을 만족하는 레코드의 갯수를 찾는 코드가 아래와 같이 있다.
        cnt = WorksheetFunction.CountIfs(rngBS, bs, rngPx, orderpx)
    If cnt >= 1 Then
    FindOrderFill = True
    Else
    FindOrderFill = False
    End If
    위의 If블럭은 간단히 다음과 같이 한 줄로 줄일 수 있다.
    FindOrderFill = cnt >= 1

    흔히 보는 특별하지 않은 엑셀 팁 EXCEL/VBA

    흔하게 만나는 엑셀팁인데, 지난 번 모대학교 파생상품금융 강의에서 지루한 시간을 때우기 위해 준비한 것입니다. 지루한 강의실습중 단비같은 팁을 만나면 눈이 반짝 반짝거리더군요. (움직이는 GIF(일명 움짤)을 만들었는데, 이글루스 모바일 앱에선 첫 장만 보입니다)

    [DOWNLOAD]timebird_exccel_tips.pdf

    믿을 수 없는 숫자놀음 증권

    xing API를 사용하고 있는 데, 기술적 지표를 따로 제공해준다. 사실 기술적 지표 공식들이 그리 어려운 수준은 아니지만 일일이 만들기엔 귀찮다. 수고를 덜어 준다니 얼마나 반가운 일인가? 만든 프로그램으로 지표를 화면에 띄워 놓고, 동시에 스마트폰앱으로 지표를 보니 뭔가 이상하다. 동일종목, 동일지표, 동일한 지표매개변수, 동일한 시간주기인데, 값이 큰 차이가 난다. 귀찮아서 작은 차이라면 무시할텐데..증권사 홈페이지에 이런 문의가 있으리라 생각되어 게시판을 보니, 역시 여러 사람들이 그런 문제를 질의한 모양이다.

    안녕하세요. 관리자입니다.
    당사 API의 차트는 xing차트(4201,4301) 과 동일한 지표계산을 합니다. STAR 차트 지표와는 계산에서 차이가 있을 수 있습니다. 비교하신 차트가 STAR차트라면, xing차트와 같은 지표로 확인 부탁드립니다. 이용에 참고하시기 바랍니다.

    그러나 답변을 한 관리자의 답변과 달리 4201/4301차트 역시 샘플로 준 엑셀파일과 다른 결과를 보여준다. 아무래도 계산을 직접 해야겠다. 현재 30분봉은 아직 미완성이라 그러다고 쳐도 지난 봉의 결과가 다르다는 사실은 현재의 분봉이 결국 API와 다를 것이다. 그런데 xing API가 리눅스를 지원하는 듯하다. 리눅스 API를 신청한다는 게시물이 보인다. 공지사항에서 보질 못했는데...

    우리가 몰랐던, 아니 자주 사용하지 않던 데이터구조체 EXCEL/VBA

    vba에서 자주 사용하는 데이터구조체는 배열, 딕셔너리, 컬렉션 등이다. 하지만 너무 단순한 것들이라 가끔 불편하지만, 익숙해서 그냥 쓰고 있다. 간만에 vba에서 큐, 스택 같은 구닥다리부터 최신식 구조체(사실 Object Container) 몇 가지를 해시테이블만 빼고 대거 방출하고자 한다.
    해시테이블 예제를 만들려니 왕귀찬...
    Sub demoArrayList() 
    Dim arrList As Object
    Dim item

    'Create the ArrayList
    Set arrList = CreateObject("System.Collections.ArrayList")

    arrList.Add "Hello"
    arrList.Add "You"
    arrList.Add "There"
    arrList.Add "Man"
    arrList.Remove "Man"

    'Get number of items
    Debug.Print arrList.Count 'Result: 3

    For Each item In arrList
    Debug.Print item
    Next

    End Sub

    Sub demoSortedList()
    Dim sortedList As Object

    ' Create the SortedList
    Set sortedList = CreateObject("System.Collections.SortedList")

    sortedList.Add "ThisortedListrd", "!"
    sortedList.Add "Second", "World"
    sortedList.Add "First", "Hello"

    ' Displays the properties and values of the SortedList.
    Debug.Print "Count:"; sortedList.Count
    Debug.Print "Capacity:"; sortedList.Capacity

    Dim i As Long

    For i = 0 To sortedList.Count - 1
    Debug.Print sortedList.GetKey(i), sortedList.GetByIndex(i)
    Next
    End Sub

    Sub demoQueue()
    Dim queue As Object
    Dim peekAtFirst, doesContain, firstInQueue

    'Create the Queue
    Set queue = CreateObject("System.Collections.Queue")

    queue.Enqueue "Hello"
    queue.Enqueue "There"
    queue.Enqueue "Mr"
    queue.Enqueue "Smith"

    peekAtFirst = queue.Peek() 'Result" "Hello"
    Debug.Print peekAtFirst

    doesContain = queue.Contains("htrh") 'Result: False
    Debug.Print doesContain

    doesContain = queue.Contains("Hello") 'Result: True
    Debug.Print doesContain

    'Get first item in Queue and remove it from the Queue
    firstInQueue = queue.Dequeue() '"Hello"
    Debug.Print firstInQueue

    'Count items
    Debug.Print queue.Count 'Result: 3

    'Clear the Queue
    queue.Clear

    Set queue = Nothing
    End Sub

    Sub demoStack()
    Dim stack As Object
    Dim peekAtTopOfStack, doesContain, topStack

    'Create Stack
    Set stack = CreateObject("System.Collections.Stack")

    stack.Push "Hello"
    stack.Push "There"
    stack.Push "Mr"
    stack.Push "Smith"

    peekAtTopOfStack = stack.Peek()
    Debug.Print peekAtTopOfStack

    doesContain = stack.Contains("htrh") 'Result: False
    Debug.Print doesContain

    doesContain = stack.Contains("Hello") 'Result: True
    Debug.Print doesContain

    'Get item from the top of the stack (LIFO)
    topStack = stack.Pop() 'Result: "Smith"
    Debug.Print topStack

    'Clear the Stack
    stack.Clear

    Set stack = Nothing
    End Sub
    [참고]MSDN-Early Binding이 아니라서(못해서) VBE에서 편리하게 클래스의 멤버함수나 속성을 알 수 없으니 MSDN을 참조해가며 익히는 게 좋을 것이다.
    1. ArrayList
    2. SortedList
    3. Hashtable
    4. Stack
    5. Queue

    변동성을 이용한 페어 트레이딩 전략 증권

    페어트레이딩전략은 평균회귀(Mean-Reversion)의 성질을 이용한 매매전략이다. 주식의 경우 동종업종내 종목을 가지고 하는 경우가 많은 데, 주식은 공매도가 까다로워 그리 재미있진 못하다. 그런데 페어트레이딩전략에는 그게 전부는 아니다. 변동성을 이용한 페어트레이딩 전략도 있는데, 단기변동성이 장기변동성보다 커서 차익거래의 기회가 존재한다(Lo and MacKinlay 1988).


    [DOWNLOAD]VolArb_Summary.pdf

    워크시트 출력에 대한 고민-배열의 출력 EXCEL/VBA

    vba를 이용하여 워크시트에 출력하는 일은 거의 필수적인 작업이다. 그런데 출력하는 작업은 vba의 속도를 떨어 뜨리는 주범중 하나이다. 그래서 추천하는 방법이 하나의 루프에서 100번을 반복하면서 100번을 출력하는 것보다는 배열에 저장하고, 루프를 나오면 한번에 출력하는 것이다.

    그런데 10,000개의 데이터를 워크시트에 한번에 출력하는 게 문제이다. Range("B2") = mat()는 B2셀에 배열의 (1,1)의 값을 출력한다.
    Sub CopyMatrixToRange()
    Dim mat(10, 10) As Double
    Dim i As Long, j As Long

    Randomize
    For i = LBound(mat) To UBound(mat)
    For j = LBound(mat, 2) To UBound(mat, 2)
    mat(i, j) = Rnd
    Next
    Next
    Sheet1.Range("B2") = mat()
    End Sub
    이런 오류를 해결하고 아래와 같이 출력하려면

    아래와 같이 출력하려는 배열의 크기만큼 셀의 범위를 맞춰야 한다. 아래의 코드에서 10x10의 배열을 출력하기 위해 셀의 범위를 B2:K11 로 맞춰야 한다.
    Sub CopyMatrixToRange()
    Dim mat(10, 10) As Double
    Dim i As Long, j As Long

    Randomize
    For i = LBound(mat) To UBound(mat)
    For j = LBound(mat, 2) To UBound(mat, 2)
    mat(i, j) = Rnd
    Next
    Next
    Sheet1.Range("B2:K11") = mat()
    End Sub
    그런데 배열의 크기에 맞춰 셀 범위를 하드코딩해두는 건 그리 아름다운 모습은 아니다. 그래서 배열의 크기만큼 셀 범위를 자동으로 맞출 필요가 있고, 크기를 계산할 필요가 있다. 아래의 코드에서 Range개체의 Resize()메서드와 UBound()/LBound()함수를 이용하여 행과 열의 크기를 계산하였다.
    Sub CopyMatrixToRange()
    Dim mat(10, 10) As Double
    Dim i As Long, j As Long

    Randomize
    For i = LBound(mat) To UBound(mat)
    For j = LBound(mat, 2) To UBound(mat, 2)
    mat(i, j) = Rnd
    Next
    Next

    Dim cntRow As Long, cntCol As Long

    cntRow = UBound(mat) - LBound(mat) + 1
    cntCol = UBound(mat, 2) - LBound(mat, 2) + 1


    Sheet1.Range("B2").Resize(cntRow, cntCol) = mat()
    End Sub
    하지만 1억개의 셀에 출력하는 것은 버거운지 약간 시간이 걸리지만 루프를 돌리는 것보단 훨씬 낫다.

    1 2 3 4 5 6 7 8 9 10 다음