ic


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억개의 셀에 출력하는 것은 버거운지 약간 시간이 걸리지만 루프를 돌리는 것보단 훨씬 낫다.

부처님과 리스크 증권

부처님의 전생설화, 본생경(本生經)은 바로 부처님의 전생이야기다. 무려 547편의 전생이야기를 모아 된 것으로 다음과 같은 이야기가 있다.

많은 상인들이 무리를 지어 이곳 저곳을 돌며 장사를 하고 있었다. 장사를 하러 돌아다니던 중 멀리 마을이 보이고 그들의 우두머리는 바로 마을에 들어가지 않고 선발대를 먼저 떠나 보내며 당부를 하였다.

과거에 먹어 보지 못한 음식이나 나무 열매 등을 절대로 먹지 마라


선발대가 마을에 도착해보니 큰 나무가 있었는데, 너무나 맛있게 보이는 열매들이 주렁주렁 매달려 있었다. 우두머리의 말을 무시하고 선발대의 몇몇은 열매을 따 먹었다. 나중에 마을에 도착한 우두머리가

그 나무의 열매에는 독이 있다


라고 말했다. 과일을 먹은 일부는 먹은 것을 토해내 겨우 목숨을 건지고 이미 너무 많이 먹은 일부는 죽고 말았다. 사실 마을사람들은 이런 식으로 목숨을 잃은 사람들의 물건을 빼앗고 있었다. 상인의 우두머리에게 ‘어떻게 독이 있는 지 아셨습니까?’ 라고 묻자, 그는

마을 입구 나무에 나지막한 가지에 열매들이 잘 익어 있는데,
아무도 따먹지 않고 있다면 독이 있다고 봐야지


라고 대답하였다.

겉보기에 먹음직 스러워 보이지만 아무도 먹지 않는 과일처럼 누군가가 큰 위험부담없이 고수익을 올릴 수 있는 금융상품을 권유한다면 그 진실성을 의심해봐야 한다. 위험을 감수하지 않으면서 고수익을 올릴 수 는 없다.

리스크의 뜻은 손실에 노출될 가능성을 말한다. 보통 리스크를 위험이라고 번역을 하지만 정확히 말하는 위험(danger)이 아니다.

출처:옵션가격이론:금융시장의 수학적 모델, 카이스트 수학과 최건호



New comment on 'Python as an Excel scripting language' EXCEL/VBA

지난 번 포스팅(뭐? python을 엑셀에서 사용할 수 있다고?)에서 엑셀 스크립트언어로서 파이썬이 채택될거라는 소문을 전해드렸는데, 오늘 오전에 office.com에서 사용자들의 의견을 전해주는 메일을 보내주었더군요.  메일 내용에 따르면 코멘트를 올린 사용자는 대기업에서 일하는데, 예전엔 엑셀로 많은 일을 해왔는데, 지금은 파이썬으로 그 일들을 대체하고 있다고 하네요.  그러나 파이썬이 엑셀 스크립팅 언어가 된다면 그런 추세가 반전될거라는 조언입니다. 그런 상황이 온다면 재미있는 일이 많이 생길 듯합니다. 일단 블로그 포스팅과 책을 써야 할 것 같군요. 그리고 엑셀을 이용한 기계학습/딥러닝도 돌려보고요..





한 번만 실행하는 루틴 EXCEL/VBA

하나의 매매 알고리즘을 만들면서 알고리즘을 하나의 프로시저에 몰아넣다 보니, 곤란한 점이 생긴다. 실시간 시세가 수신될때마다 이 알고리즘을 호출해야 하는 데, 구현된 알고리즘에는 처음 한 번만 실행하는 투자자원배분 루틴이 있다. 그렇다면 알고리즘을 두 개의 프로시저로 분리하는 게 맞다. 하지만 이러다 보니 두 개의 프로시저에서 참조하는 변수를 전역변수로 해야 한다. 전역변수는 되도록 줄이는 게 좋다. 전역변수의 갯수가 많으면 관리하기 어려워 질 수 있다.

이럴 경우 이전의 실행여부를 어디에 저장해두고(보통 워크시트 구석의 어느 셀), 그 정보를 읽어와서 실행여부를 결정할 수 있다. 그런 방법은 이미 흔한 것이라 생략하고 다음과 같이 2가지 방법을 생각해볼 수 있다.

1) Static 사용
보통의 프로시저 수준의 로컬변수는 프로시저 종료와 함께 그 생명을 다하지만 "Static" 변수는 프로젝트가 종료될때 까지 살아남는다,
Sub OnceInSession()
Static blnAlreadyRun As Boolean

If blnAlreadyRun Then
Debug.Print "Already did..."
Exit Sub
Else
Debug.Print "Do something..."
blnAlreadyRun = True
End If
End Sub
Static변수는 런타임에러가 발생해도 값을 여전히 유지한다.
Sub OnceInSession()
Static blnAlreadyRun As Boolean

If blnAlreadyRun Then
Debug.Print "Already did..."
Exit Sub
Else
Debug.Print "Do something..."
blnAlreadyRun = True
On Error Resume Next
Err.Raise 1004

End If
End Sub
그러나 프로젝트가 종료되면 생명이 끝난다.
Sub OnceInSession()
Static blnAlreadyRun As Boolean

If blnAlreadyRun Then
Debug.Print "Already did..."
Exit Sub
Else
Debug.Print "Do something..."
blnAlreadyRun = True
End
End If
End Sub
위의 프로시저에서는 프로젝트를 종료하기 위해 End를 사용한다. 사실 VBA 프로젝트는 오류로 인해 원하지 않는 순간에 종료될 수 있다.

2)엑셀의 [이름]기능
엑셀의 [이름]에 값을 저장해두면 워크북 저장시 같이 저장되어 안정적으로 관리된다. 또 워크시트의 구석탱이 셀에 저장하는 것보다 덜 궁색하다.
Sub OnceWhenOpen()
Dim blnAlreadyRun As Boolean

blnAlreadyRun = Application.Evaluate("ONCE_WHEN_OPEN")

If blnAlreadyRun Then
Debug.Print "Already did..."
Exit Sub
Else
Debug.Print "Do something..."
blnAlreadyRun = True
ThisWorkbook.Names("ONCE_WHEN_OPEN").Value = blnAlreadyRun
End If
End Sub

(2)이항분포 실험기, 갤튼박스(Galton box) 또는 빈 머신(Bean Machine) 금융공학

지난 번에 쓴 포스트, (1)이항분포 실험기, 갤튼박스(Galton box) 또는 빈 머신(Bean Machine)에 이어 쓰는 두 번째 포스팅입니다. 학교에서 배운 정규분포는 종모양이고 무수히 많은 실험을 하게 되면 정규분포를 따른다고 한다. 하지만 기억나는 것은 종이위에 인쇄된 그저 종모양의 그래프이다. 실제 실험해볼 수 있다면 산 교육이 되겠지만 대학입학이라는 목표에 쫓기는 현실상 꽃놀이 같은 그런 실험은 엄두를 낼 수 없다.

이를 잘 알아 둔다면 금융공학에 필요한 확률분포함수를 배우는 셈이 된다. 이항분포실험기는 베르누이 시행을 배울 수 있는 좋은 도구이다. 베르누이 시행의 결과는 단 두 가지이다. 결과가 두 가지만 존재하는 대표적인 실험이 동전 던지기이다. 동전 던지기의 경우 앞면 또는 뒷면만 나오면 각각의 확률은 1/2이다.

앞면이 나올 사건 H가 나올 나올 확률이 p일 때, 앞면이 나오지 않을 사건 T가 나올 확률은 1-p이다. 이항분포 실험기처럼 베르누이 시행을 여러 번 시행해서 얻어진 확률분포를 이항분포라고 한다.

(시행을 거듭할 수록 구슬은 가운데로 모인다)


이항분포 실험기를 갖고 놀다보면 구슬은 가운데로 몰린다. 이제 그 이유를 알아보자. 갈림길이 4번 나오는 이항분포 실험기가 있다. 100개의 구슬을 넣는 시행을 하면 구슬은 왼쪽 또는 오른쪽으로만 이동할 수 있고 각각의 경우는 1/2의 확률을 가진다. 구슬이 이동할 수 있는 여러 가지의 경우중 가장 간단한 경우를 먼저 살펴보자.
(위의 그림은 갈림길이 6개인 경우지만)

시작점에서 구슬이 왼쪽으로만 4번 이동하여 1번 방에 들어가는 경우는 1가지이며 이동할 때마다 각각의 확률인 ½이 네 번 연속으로 나오는 것이므로그 확률은 ½*½*½*½이고 100번을 할 것이므로 100*½*½*½*½=6.25개이다.
2번 방의 경우 4개의 경우의 수가 나오며 100*((1/16)+(1/16)+(1/16)+(1/16)) = 25개이다.
3번 방의 경우 6개의 경우의 수가 나오며 100*((1/16)+(1/16)+(1/16)+(1/16)+(1/16)+(1/16)) = 37.5개이다.
4번 방과 2번 방과 같은 경우이도 5번 방의 경우는 1번 방과 마찬가지이므로 각각 25개, 6.25개이다.

이항분포 실험기를 파이썬으로 만들어 놓은 게 있다.
from collections import defaultdict
from math import sin, cos, radians as rads
from random import choice
from turtle import *

R = 5

def galton(nlevels=11, trace=False, speed=6):
setup(width=500, height=800, startx=0, starty=0)
delay(0)
pen(speed=10, shown=False)
goto(-200, -350)
clear()
goto( 200, -350)
pen(pendown=False)
# color('blue')
for n,y in enumerate(range(nlevels*14, -1, -14), 1):
for x in range(-7*n, 7*n+1, 14):
goto(x, y)
dot(6)
pen(speed=speed)
# color('red')
angles = (rads(d) for d in range(0,361,36))
addshape('marble', tuple((R*sin(r),R*cos(r)) for r in angles))
shape('marble')

hist = defaultdict(lambda:-343)
while True:
pen(shown=False, pendown=False)
x,y = 0, nlevels*14
goto(x, y)
pen(shown=True, pendown=trace)
delay(5)

while y > 0:
if y%14:
x += choice((-7,7))
goto(x, y)
y -= 7
goto(x, y)

delay(2)
while y > hist[x]:
y -= 7
goto(x, y)

stamp()
hist[x] += 7

if hist[x] > 0:
break

if __name__ == "__main__":
galton(4,False,1)


파이썬 따라하기 - 두 개의 배열을 합치기 EXCEL/VBA

두 개의 리스트를 원소별로 붙이는 파이썬 코드를 보니, VBA로 이건 안될 까 하는 생각이 들었다. 미리 결과를 말하자면 ['a1', 'b2', 'c3', 'd4', 'e5'] 이다.
x = ["a", "b", "c", "d", "e"]
y = [1, 2, 3, 4, 5]
z = map(lambda x,y:("%s%d" % (x,y)),x,y)
print(list(z))
그래서 두 가지를 해보았는데, (1)하나는 위의 파이썬 코드처럼 두 리스트의 원소끼리 맞붙여 보는 것이고, (2)나머지 하나는 그냥 기계적으로 직렬로 연결하는 것이다. (1)은 배열수식으로는 가능하지만 VBA구문상 어렵다. 그래서 엑셀의 힘을 빌려 보기로 했다.
    Dim arr1, arr2, arr3

arr1 = Array("a", "b", "c")
arr2 = Array("d", "e", "f")

On Error Resume Next
With ThisWorkbook
.Names("arr_1").Delete
.Names("arr_2").Delete
End With
On Error GoTo 0

With ThisWorkbook.Names
.Add "arr_1", arr1
.Add "arr_2", arr2
End With

arr3 = Application.Evaluate("=arr_1 & arr_2")
배열을 이름으로 지정하여 저장하고 , Evalueate()함수에 배열수식을 만들어 넣는 것이다.

(2)는 (1)보다 VBA로만 가능하여 훨씬 코드가 간략하다
    Dim arr1, arr2, arr3

arr1 = Array("a", "b", "c")
arr2 = Array("d", "e", "f")

arr3 = Split(Join(arr1, Chr(1)) & Chr(1) & Join(arr2, Chr(1)), Chr(1))
(2)은 두 개의 배열을 그냥 서로 이어 붙이는 것인데, Join()함수를 사용한다. Join()함수를 사용하여 배열의 각 원소를 하나의 문자열로 연결하고, 두 개의 배열과 Join(함수를 사용하여 얻은 두 개의 문자열을 다시 이어 붙인다. 그리고 Split()함수를 이용하여 다시 쪼개어 하나의 배열로 만드는 것이다.

Join() 과 Split() 함수 사용시 구분자를 흔히 사용하는 쉼표 등으로 하지 않은 것은, 배열내 원소중 그런 것들이 섞여 있어 잘못된 Split()결과가 나올 걸 대비해서이다. 가령 "a" 가 아니라 "a,"가 있는 데, 구분자를 쉼표(,)로 해버리면 Split() 결과, 빈 원소가 추가된 배열이 나올 수 있다. 그래서 키보드로 입력 가능하지 않은 문자중 아스키코드 1번 문자를 사용한 것이다.


[덧칠하기] 글을 쓰다보니 비슷한 포스팅을 한 적이 있다. [배열=배열+배열]에서 두 개의 1차원 배열을 합쳐 하나의 배열 2차원 배열을 만들었는데, 이번의 것은 1차원 배열의 원소를 합쳐 1차원 배열을 만드는 것이다.

그리고 (2)의 방법도 그 포스팅에서 다루었다. 그 당시(2017.12.07)에는 쉼표(,)를 구분자로 사용하였다. 그 포스팅을 전혀 기억하지 못하고 쓴 글인데, 비슷한 글을 또 쓰고 있다. 대단한 우연이다.


오랜만에 풀어 보는 SQL 문제 Data science

얼마 전 친한 형님이 급히 풀어달라는 과제가 있었는데, 그중에 SQL을 만드는 것이 있었다. https://github.com/rushpartners/data-analyst-challenge/tree/master/part-2
You have been asked to get some answers from a database on a set of data. The database contains an archive of bids made by users for players over the years. The database contains years of data and you will need to write raw SQL queries to answer the questions.

Using the table structure below, provide the SQL queries required to answer as many of the following questions:
1) What is the total number of users who have placed a bid on Wayne Rooney whilst playing for Manchester United?
2) What is the total number of bids for Cristiano Ronaldo placed by users in Spain?
3) What is the total number of users who have never placed a bid?
4) What is the % of bids for each player
5) What is the % of bids for each team
6) Rank teams in order of the # of bids their players have had.

(테이블#1) [Users] - (컬럼) id, username, country
(테이블#2) [Bids] -(컬럼) id, user_id, player_id, value, date
(테이블#3) [Players] - (컬럼) id, name, team


그리 어려운 것은 아닌데, 그중 하나는 인상이 깊었다. 3)문제인데, bid를 하지 않는 사용자의 숫자를 구하는 것인데, 평소에 조건에 해당하는 쿼리를 했는데, 이번에는 조건에 해당하지 않는(NEVER) 사용자 수를 구하는 것이다.
간단히 전체 사용자 숫자에서 비딩에 참여한 사용자숫자를 빼면 된다. 그래서
SELECT COUNT(a.id) - COUNT(DISTINCT b.user_id) FROM Users a, Bids b


그런데 다른 방법(고수처럼 보이는)이 있을 것 같아 자주 가는 포럼에 도움을 구했더니, 다음과 같은 답을 얻었다. 멋진 쿼리이다.
SELECT COUNT(*) FROM Users WHERE users_id NOT IN ( SELECT users_id FROM Bids )



간단한 초보적 질문에 어렵게 답하기 EXCEL/VBA

다 아는 사실이지만 많이 아는 것과 잘 가르치는 것은 별개이다. 눈 높이에 마추어 가르치고 답을 주는 것이 좋은 것이 아닐까? 그러나 뭔가 고수같은 품격을 살리기 위해 일부러 어렵게 답하는 만행을 상대방 앞에선 하지 못하고 이렇게 블로그에 옮겨 본다.

하나의 시트속의 숫자들을 다른 시트로 복사하는 데, 그냥 복사가 아니라 1000으로 나눈 결과를 붙여 넣는 것이다. 손으로 한다면 누구라도 하는 간단한 것이지만, VBA 초보의입장에선 첫 걸음 떼는 아기처럼 어려운 일이다.




FM(교본)같은 답변은 이럴 것이다.
Dim rng As Range  

Worksheets("Sheet1").Range("A2:A7").Copy Worksheets("Sheet2").Range("A2")

For Each rng In Worksheets("Sheet2").Range("A2:A7")

rng.Value = rng.Value / 1000

Next
그러나 코딩골프 놀이를 하고 싶어 코드를 더 줄이기 위해 이전에 내 블로그를 참조(원래 블로깅하는 목적이기도 하다)하여 만든 쓸데없는 잉여짓은 다음과 같다.

1) Sheet2.Range("A2:A7").FormulaArray = "=Sheet1!A2:A7/1000"
위의 방법은 배열수식을 입력하는 방법이다. 수식이 들어가다 보니, 수식/함수가 많다면 연산에 부담이 될 것이다.

2) Sheet2.Range("A2:A7") = Sheet1.Evaluate("Sheet1!A2:A7/1000")
위의 방법은 예전에 포스팅한 Evaluate()메서드를 이용한 것이다. 수식의 결과는 값으로 떨어지고, 속도도 빨라서 만족스럽다. 그러나 "..." 같은 문자열안에 수식을 하드 코딩해 둔 것이라, 셀 주소를 변수로 처리하는 게 실용적이다.

3) 앞의 2)의 하드코딩을 고쳐 쓰자면,
Dim rng As Range

Set rng = Sheet1.Range("A2:A7")

Sheet2.Range("A2:A7") = Sheet1.Evaluate(rng.Address & "/1000")


표준편차를 이용한 등수 구하기 Data science

학생시절에 시험을 치루고 난 뒤 하는 일은 점수를 맞춰 보는 것이다. 기억에 나는 문제를 얘기하며 "그 문제 정답이 뭐냐?" 부터 시작하여 그 과목의 점수가 얼마 나오겠구나 하는 예상을 해보곤 한다. 사실 그거 할 시간에 다음 시험과목 준비를 하는 것이 나을 것이다.
막간을 이용하여 통계학 책에 나온 예제를 VBA로 만들어 보았다. 가령 학교에서 시험을 보고 등수를 추정하고 동점자수를 예상해보는 것이다. 사실 등수를 추정하는 것은 내 점수의 백분위수를 알고 전체 학생수를 안다면 어려운 일은 아니다. 둘이 곱하면 되는 것이므로... 동점자 수를 추정하는 것도 내 점수 인원수에서 내 점수보다 1점 높은 친구들의 숫자를 빼는 것이다.
Sub Get_Rank_Using_Standard_Deviation()
Dim num As Long
Dim score As Long
Dim mean As Double
Dim stdev As Double

Dim pencentile As Double
Dim estimated_rank As Long
Dim estimated_num_of_peer As Long

num = 130
score = 70
mean = 71.6
stdev = 17.1

With WorksheetFunction
pencentile = 1 - .Norm_Dist(score, mean, stdev, 1)
estimated_rank = pencentile * num
estimated_num_of_peer = estimated_rank _
- (1 - .Norm_Dist(score + 1, mean, stdev, 1)) * num
End With

Debug.Print
Debug.Print "<<표준편차를 이용한 등수 구하기>>"
Debug.Print "1. 백분위:"; Format(pencentile, "0.00%")
Debug.Print "2. 추정등수:"; estimated_rank
Debug.Print "3.추정동점자수:"; estimated_num_of_peer
End Sub


증권사 API를 사용하면서 느끼는 여러 생각 증권

대신증권의 사이보스 초기시절부터 증권사API를 이용한 시스템 트레이딩 또는 자동매매 시스템을 만들겠다는 생각만 가지고 있었다. 비교적 최근에는 미국에서 유행한 알고리즘 트레이딩 붐에 힘입어 이베스트증권의 Xing API라던가 키움증권의 OpenAPI가 대중에 인기를 끌고 있다. 두 회사의 API를 만지작 거려 보았지만, 딱히 목적이 없어 본격적인 작업을 해보진 않았다. 그러나 API 문서화, 모의 시스템 지원, 참고자료, 회사의 공개강의 등 두 회사의 API는 가장 대중적이고 인기있다.

회사 일로서 대우증권의 API를 이용하여 선물매매시스템을 만들고 운용한 경험도 있는데, 이번에는 신한증권의 API를 이용한 시스템을 만들고 있다. 신한증권을 선택한 이유는 매매하고 싶은 미국선물(VIX선물때문이다. 그러나 VIX옵션이 없어 아쉽지만) 때문이다. 다행히 신한증권은 해외선물 예제를 엑셀로 제공하고 있어, API를 학습하는 면에선 큰 어려움이 없다.

다만 최근에 엑셀로 파일럿 프로그램을 만들면서 애로사항이 하나 생겼다. 한번에 세 개의 종목 시세를 받기가 힘들다는 점이다. 간혹 세 개의 시세를 받는 데 성공하기도 하지만 말이다. 여러 번 시도를 하면 성공한다. 다만 1번에 성공하지 못하는 것일 뿐. 조회TR을 요청하면 에러없이 조회ID를 얻기는 하지만 꼭 한 종목의 콜백함수가 호출이 안되고 있다.

물론 이건 API문제는 아니고 엑셀의 문제이다. C#으로 작성한 프로그램은 잘 작동하는 걸 보면 그런 생각이 든다. 사실 엑셀이 이런 용도로 만들어진 프로그램이 아니다. 그러나 본격적인 프로그램을 만들기에 앞서 화면구성이나 API학습, 각종 테스트를 위해 엑셀로 파일럿(Pilot)을 만드는 작업이 마냥 시간낭비는 아니다. 전체적인 개발 프로세스를 줄이는 방법이라고 생각한다.

RANK()함수를 VBA에서 사용하려고 보니... EXCEL/VBA

VBA를 강의하거나 관련 글을 쓰면서 자랑스럽게 VBA의 강점으로 소개하는 것이 WorksheetFunction 개체이다. WorksheetFunction은 엑셀의 워크시트함수를 VBA에서 사용할 수 있도록 해주는 고마운 개체이다. 이 덕분에 VLOOKUP() 함수와 같은 기능을 직접 만들 필요가 없다. 오히려 내가 만든 함수보다 더 빠르고 신뢰성이 높다.

그러나 이번에 RANK()함수를 처음 사용하면서 난감하게 되었다. RANK()함수는 일련의 값의 배열에서 특정 값의 순위를 돌려준다. 그러나 RANK()함수에 전달하는 매개변수중 ref 매개변수에 Range개체가 아닌 Variant배열, 수치형 데이터 배열을 주게 되면 오류(컴파일에러와 런타임에러(424))가 난다(다른 워크시트함수에서는 배열을 던져 놓은 것이 가능했는데...)
'// Variant변수를 사용하는 경우 424 런타임 에러 - 개체가 필요합니다
Debug.Print .rank(rng.Value, arrVar, RankOrder.Ascending)

'// 수치형 데이터배열을 사용하는 경우 컴파일 에러 - 형식이 일치하지 않습니다
Debug.Print .rank(rng.Value, arrLng, RankOrder.Ascending)


계산중간에 생기는 값들이라 RANK()함수를 사용하려고 이들를 워크시트에 저장하고 다시 읽는 것은 삽질오브삽질일 것이다. 안정적인 장시간의 실행이 필요한터라 워크시트 입출력을 되도록 줄이고 대부분의 데이터처리를 메모리속에서 실행되도록 만드는 중이다. 그래서 RANK함수와 타협을 할 여지가 없다. 그래서 따로 함수를 만들었다. 굳이 정성을 들일 부분이 아니라서 간단히 LARGE()/SMALL()함수를 이용하였다.
Enum RankOrder
Descending
Ascending
End Enum

Function GetRank(item, itemArray, Order As RankOrder) As Long
Dim i As Long
Dim rnk As Long

If Order = RankOrder.Ascending Then
'// 오름차순
rnk = 1
For i = LBound(itemArray) To UBound(itemArray)
If item = WorksheetFunction.Small(itemArray, )rnk Then
GetRank = rnk
Exit For
End If
rnk = rnk + 1
Next
Else
'// 내림차순
rnk = 1
For i = LBound(itemArray) To UBound(itemArray)
If item = WorksheetFunction.Large(itemArray, rnk) Then
GetRank = rnk
Exit For
End If
rnk = rnk + 1
Next
End If
End Function

따지고 보면 RANK함수와 LARGE()/SMALL()함수는 서로 관계가 있다. RANK함수의 리턴 값은 LARGE()/SMALL()함수의 매개변수가 되며, LARGE()/SMALL()함수의 리턴값은 RANK함수의 매개변수가 될 수 있다.
다음은 사용 예이다.
Sub demoRank()
Dim s As Worksheet, rng As Range
Dim xlrank As Long, myrank As Long
Dim i As Long

Set s = Sheet1

With WorksheetFunction
Dim arrVar

arrVar = .Transpose(s.Range("A1:A20").Value)

Dim arrLng(1 To 20) As Long

i = 1
For Each rng In s.Range("A1:A20")
arrLng(i) = rng.Value
i = i + 1
Next

For Each rng In s.Range("A1:A20")
'// Variant변수를 사용하는 경우 424 런타임 에러 - 개체가 필요합니다
'Debug.Print .rank(rng.Value, arrVar, RankOrder.Ascending)

'// 수치형 데이터배열을 사용하는 경우 컴파일 에러 - 형식이 일치하지 않습니다
'Debug.Print .rank(rng.Value, arrLng, RankOrder.Ascending)

'// 에러가 없지만 Range개체를 사용해야 한다
xlrank = .rank(rng.Value, s.Range("A1:A20"), RankOrder.Ascending)

'// arrVar변수를 사용한다.
myrank = GetRank(rng.Value, arrVar, RankOrder.Ascending)
Debug.Print rng.Value; xlrank; myrank
Next
End With
End Sub

루프를 사용하지 않고 배열을 워크시트에 한번에 출력하는 방법이... EXCEL/VBA

최근 블로깅이 뜸하였는 데, 먹고 사는 일에 바쁘다 보니 블로깅하는 호사를 부릴 수 없었다. 알고트레이딩 프로그램을 작성중인데, 프로토(proto) 타입 삼아 엑셀로 만드는 중이다. 아마도 시세와 이에 반응하는 알고리즘 분석/리뷰용이나 비상시 임시방편 트레이딩으로는 가능할 것이다. 엑셀이 알고트레이딩용 언어는 아니라서 몇 가지 한계를 접하는 중이다(실시간 시세를 받아오는 콜백함수의 피로누적으로 인한 런타임 오류, 저장시 메모리 부족 등등)

그래서 불필요한 워크시트 입출력을 줄이고 메모리상에서 데이터를 처리하는 코드로 바꾸어 놓았지만, 최종의 결과는 워크시트에 출력하는 것이 여러모로 편리하다. 오늘은 배열에 저장해둔 내용을 루프를 사용하지 않고 워크시트로 출력하는 방법이
없을 까 하는 궁리를 해보았다

다음은 테스트 삼아 단순한 1차원 배열을 워크시트에 출력하는 예이다. Transpose()함수를 사용하여 차원을 돌려 출력하는 것이 요령이다.
    Dim arr(5) As String

arr(0) = "Hello"
arr(1) = "World"
arr(2) = "Cancel"
arr(3) = "Absolutely"
arr(4) = "Corporate"
arr(5) = "Duty"

With Sheet1.Range("A1:A6")
.ClearContents
.value = WorksheetFunction.Transpose(arr)
End With
1차원이 아니라 2차원이라면 Tanspose없이 그대로 하면 된다.
    Dim arr(5, 2) As String

arr(0, 0) = "Hello": arr(0, 1) = "Hello": arr(0, 2) = "Hello"
arr(1, 0) = "World": arr(1, 1) = "World": arr(1, 2) = "World"
arr(2, 0) = "Cancel": arr(2, 1) = "Cancel": arr(2, 2) = "Cancel"
arr(3, 0) = "Absolutely": arr(3, 1) = "Absolutely": arr(3, 2) = "Absolutely"
arr(4, 0) = "Corporate": arr(4, 1) = "Corporate": arr(4, 2) = "Corporate"
arr(5, 0) = "Duty": arr(5, 1) = "Duty": arr(5, 2) = "Duty"

With Sheet1.Range("A1:C6")
.ClearContents
.value = arr
End With
그러나 단순한 배열이 아니라 사용자 정의 타입 배열을 처리하는 게 관심사이다.
Public Type udt
tag As String
dte As Date
value As Double
End Type

Sub demoArray2()
Dim arr(5) As udt

arr(0).tag = "Hello": arr(0).dte = #10/27/1979#: arr(0).value = 123
arr(1).tag = "World": arr(1).dte = #9/11/1968#: arr(1).value = 23
arr(2).tag = "Good": arr(2).dte = #3/8/2018#: arr(2).value = 142
arr(3).tag = "Morning": arr(3).dte = #1/27/1981#: arr(3).value = 612
arr(4).tag = "Best": arr(4).dte = #10/21/1974#: arr(4).value = 723
arr(5).tag = "Wish": arr(5).dte = #12/31/2005#: arr(5).value = 456

Sheet1.Range("B1:D6") = Application.Transpose(arr()) '// 에러 작렬
End Sub
오류가 났는데, 각 단어는 알겠는데, 합쳐 놓으니 무슨 소리인지 모르겠다 . 또 다른 삽질의 시작~


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