ic


coding interview problem PYTHON

Given an array of integers, find the first missing positive integer in linear time and constant space. In other words, find the lowest positive integer that does not exist in the array. The array can contain duplicates and negative numbers as well.

For example, the input [3, 4, -1, 1] should give 2. The input [1, 2, 0] should give 3.

li=[3, 4, -1, 1]
[x+1 for x in sorted(li) if (x+1)>0 and (x+1) not in li][0]

중딩 수학문제 삽질

저녁에 아들내미가 끙끙 앓던 수학문제인데, 나름 재미가 있네요.





아래 28번 문제를 풀다보니 평균/표준편차라는 게 가상의 숫자였구나 하는 늦은 깨달음이...아래 문제의 핵심은 평균은 편차를 최소화하는 것을 이해하는 것입니다.





Joining data in R with dplyr GNU R









[그림출처: TWITTER]https://twitter.com/grrrck/status/1029567123029467136

조인과 관련한 몇 가지 코딩을 하자면;
library(dplyr)

Ldata<-data.frame(ID=c('A', 'B', 'C'), x=c(1, 2, 3))
Rdata<-data.frame(ID=c('A', 'B', 'D'), y=c(T, F, T))

Ldata
Rdata

left_join(Ldata, Rdata, by='ID')
right_join(Ldata, Rdata, by='ID')
inner_join(Ldata, Rdata, by='ID')
full_join(Ldata, Rdata, by='ID')

> Ldata
ID x
1 A 1
2 B 2
3 C 3
> Rdata
ID y
1 A TRUE
2 B FALSE
3 D TRUE

> left_join(Ldata, Rdata, by='ID')
ID x y
1 A 1 TRUE
2 B 2 FALSE
3 C 3 NA
Warning message:
Column `ID` joining factors with different levels, coercing to character vector
> right_join(Ldata, Rdata, by='ID')
ID x y
1 A 1 TRUE
2 B 2 FALSE
3 D NA TRUE
Warning message:
Column `ID` joining factors with different levels, coercing to character vector
> inner_join(Ldata, Rdata, by='ID')
ID x y
1 A 1 TRUE
2 B 2 FALSE
Warning message:
Column `ID` joining factors with different levels, coercing to character vector
> full_join(Ldata, Rdata, by='ID')
ID x y
1 A 1 TRUE
2 B 2 FALSE
3 C 3 NA
4 D NA TRUE
Warning message:
Column `ID` joining factors with different levels, coercing to character vector


자바스크립트를 오랜만에 해봅니다.

예전에 알던 자바스크립트는 기껏 브라우저에서나 돌아가는 그저 그런 언어였는데, 이제는 중요한 스크립트언어로 자리잡고 있군요.

장고, 플라스크 웹프레임워크를 이용한 웹프로그래밍과 쥬피터,텐서플로,넘파이,판다스를 이용한 데이터사이언스 및 딥러닝 언어로 파이썬은 참 매력덩어리인데, 자바스크립트는 그보다 더 대단한 언어더군요.

다시 자바스크립트의 함정에 빠져 볼까합니다. 그래서 오늘 자바스크립트 책을 알라딘 중고서점에서 하나 사서 읽는 중이죠.

사서 바로 읽기 시작한 책의 내용을 정리하자면:

1.2 자바스크립트 프로그래밍의 기초
1.2.1 변수선언과 초기화
* 기본적으로 모든 변수는 전역
* 변수선언 불필요
* 선언않고 초기화된 변수는 전역
* 선언하면 지역변수로 사용 가능
var number;
var name;
var rate = 1.2;
var greeting = "hello, world!";
var flag = false;
 
1.2.2 자바스크립트 산술, 수학 라이브러리 함수
* + - * / %
* 정밀도 지정
var x = 3;
var y = 1.1;
var z = x*y;
print(z.toFixed(2)); // display 3.30 

1.2.3 조건문
* 단순한 if문
* if-else문
* if-else if문
* switch문(추천안함)

1.2.4 반복문
* while문
* for문

1.2.5 함수
* function

1.2.6 변수범위
* var를 사용안하고 사용한 전역변수는 문제
function showScope(){
  scope="local";
  return scope;
}
scope = "global";
print(scope); // "global"출력
print(showScope()); // "local"출력
print(scope); // "local"출력
실제 이런 식으로 잡스럽게 만들진 않겠지만, 황당한 동작이다.
* 이런 혼돈을 피하려면 var를 사용해야 함
* 자바스크립트는 블럭범위를 지원안함
for (
var i = 1; i <= 10; i++) {
  print("Hello World!");
}
마치 블럭범위를 지원하는 것처럼 보이지만 실제로는 아님

1.2.7 재귀

1.3 객체와 객체지향 프로그래밍
....


[Xing API]xingAPI활용교육샘플v1.3.xlsm(7)-[주문&계좌][잔고조회] 증권

[주문&계좌]워크시트는 세 가지의 기능을 가지고 있다. 시트 왼쪽부터 1)잔고를 조회하고, 2)매수/매도주문과 주문정정/취소하는 기능 3)체결내역을 조회하는 기능이 있다.

1)잔고조회
잔고조회 관련 버튼은 [잔고조회]버튼과 [잔고다음조회]버튼이 있다. 잔고를 조회하려면 TR(CSPAQ13700)이 필요한 데, 관련한 전역변수는 다음과 같이 선언되어 있다. 또 출력과 관련하여 nBalanceListNextRow 변수를 하나 두고 있다.
Dim WithEvents XAQuery_CSPAQ12300 As XAQuery ' 잔고
Dim nBalanceListNextRow As Integer ' 잔고 연속조회시 데이터를 표시할 Row 의 시작위치

잔고조회관련 두 개의 버튼은 각각의 프로시저를 가지지만 실제 BalanceList()라는 프로시저를 공통으로 호출한다. 둘의 차이는 이전 조회한 데이터를 지우느냐 마느냐의 정도이다. 따라서 중요한 것은 TR에 값을 주고 리퀘스트를 하는 BalanceList()프로시저와 콜백함수인 XAQuery_CSPAQ12300_ReceiveData() 프로시저이다.

CSPAQ12300 TR의 InBlock1에는 레코드의 갯수, 계좌번호, 비밀번호 등등의 정보를 주고 리퀘스트를 하면 두 개의 OutBlock을 받는다.
CSPAQ12300InBlock1,In(*EMPTY*),input;
begin
레코드갯수, RecCnt, RecCnt, long, 5
계좌번호, AcntNo, AcntNo, char, 20;
비밀번호, Pwd, Pwd, char, 8;
잔고생성구분, BalCreTp, BalCreTp, char, 1;
수수료적용구분, CmsnAppTpCode, CmsnAppTpCode, char, 1;
D2잔고기준조회구분, D2balBaseQryTp, D2balBaseQryTp, char, 1;
단가구분, UprcTpCode, UprcTpCode, char, 1;
end
OutBlock은 세 개인데, 이 예제에서 관심을 가지는 블럭은 OutBlock3이다. OutBlock3에는 어떤 정보가 있을 까? 이 예제에서 가져오는 6개의 필드(종목코드/ 종목명/ 잔고수량/ 평균단가/ 현재가/ 매입금액)는 아래에 굵게 표시해두었는데, 이를 가져와서 출력한다. 그런데 워크시트에 있는 나머지 컬럼 평가금액, 평가손익, 손익률은 출력하지 않고 있다. 이건 알아서 수식으로 계산하면 된다.
CSPAQ12300OutBlock3,ST_OUT(*EMPTY*),output,occurs;
begin
종목번호, IsuNo, IsuNo, char, 12;
종목명, IsuNm, IsuNm, char, 40;

유가증권잔고유형코드, SecBalPtnCode, SecBalPtnCode, char, 2;
유가증권잔고유형명, SecBalPtnNm, SecBalPtnNm, char, 40;
잔고수량, BalQty, BalQty, long, 16;
매매기준잔고수량, BnsBaseBalQty, BnsBaseBalQty, long, 16;
금일매수체결수량, CrdayBuyExecQty, CrdayBuyExecQty, long, 16;
금일매도체결수량, CrdaySellExecQty, CrdaySellExecQty, long, 16;
매도가, SellPrc, SellPrc, double, 21.4;
매수가, BuyPrc, BuyPrc, double, 21.4;
매도손익금액, SellPnlAmt, SellPnlAmt, long, 16;
손익율, PnlRat, PnlRat, double, 18.6;
현재가, NowPrc, NowPrc, double, 15.2;
신용금액, CrdtAmt, CrdtAmt, long, 16;
만기일, DueDt, DueDt, char, 8;
전일매도체결가, PrdaySellExecPrc, PrdaySellExecPrc, double, 13.2;
전일매도수량, PrdaySellQty, PrdaySellQty, long, 16;
전일매수체결가, PrdayBuyExecPrc, PrdayBuyExecPrc, double, 13.2;
전일매수수량, PrdayBuyQty, PrdayBuyQty, long, 16;
대출일, LoanDt, LoanDt, char, 8;
평균단가, AvrUprc, AvrUprc, double, 13.2;
매도가능수량, SellAbleQty, SellAbleQty, long, 16;
매도주문수량, SellOrdQty, SellOrdQty, long, 16;
금일매수체결금액, CrdayBuyExecAmt, CrdayBuyExecAmt, long, 16;
금일매도체결금액, CrdaySellExecAmt, CrdaySellExecAmt, long, 16;
전일매수체결금액, PrdayBuyExecAmt, PrdayBuyExecAmt, long, 16;
전일매도체결금액, PrdaySellExecAmt, PrdaySellExecAmt, long, 16;
잔고평가금액, BalEvalAmt, BalEvalAmt, long, 16;
평가손익, EvalPnl, EvalPnl, long, 16;
현금주문가능금액, MnyOrdAbleAmt, MnyOrdAbleAmt, long, 16;
주문가능금액, OrdAbleAmt, OrdAbleAmt, long, 16;
매도미체결수량, SellUnercQty, SellUnercQty, long, 16;
매도미결제수량, SellUnsttQty, SellUnsttQty, long, 16;
매수미체결수량, BuyUnercQty, BuyUnercQty, long, 16;
매수미결제수량, BuyUnsttQty, BuyUnsttQty, long, 16;
미결제수량, UnsttQty, UnsttQty, long, 16;
미체결수량, UnercQty, UnercQty, long, 16;
전일종가, PrdayCprc, PrdayCprc, double, 15.2;
매입금액, PchsAmt, PchsAmt, long, 16;
등록시장코드, RegMktCode, RegMktCode, char, 2;
대출상세분류코드, LoanDtlClssCode, LoanDtlClssCode, char, 2;
예탁담보대출수량, DpspdgLoanQty, DpspdgLoanQty, long, 16;
end
TR을 호출하려면 해당 TR개체의 Request()를 사용하는데, Request()함수에 True 또는 False값을 주면 최초 조회인데, 다음 조회인지를 구분한다. bNext변수가 False이면 처음 조회이고 , True이면 다음조회가 된다. 종목수가 많으면 다음조회가 필요하다.
XAQuery_CSPAQ12300.Request(bNext)

다음은 조회관련 코드들을 모아두었다.
'--------------
' 잔고조회 버튼클릭시
'--------------
Private Sub btnBalanceList_Click()

' 현재까지의 데이터를 삭제한다.
If nOrderListNextRow <> 0 Then
sRange = "A4" & ":F" & CStr(nBalanceListNextRow + 4)
Range(sRange).ClearContents
End If

' 첫조회이므로 Row를 초기화한다.
nBalanceListNextRow = 0

Call BalanceList(False)
End Sub

'--------------
' 잔고다음조회 버튼클릭시
'--------------
Private Sub btnBalanceListNext_Click()
Call BalanceList(True)
End Sub

'--------------
' 잔고 요청
'--------------
Private Sub BalanceList(bNext)
' 객체 생성
If XAQuery_CSPAQ12300 Is Nothing Then
Set XAQuery_CSPAQ12300 = CreateObject("XA_DataSet.XAQuery")
XAQuery_CSPAQ12300.ResFileName = "\res\CSPAQ12300.res"
End If

' 데이터 전송
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "RecCnt", 0, "00001")
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "AcntNo", 0, cbAccount)
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "Pwd", 0, tbPwd)
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "BalCreTp", 0, "0")
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "CmsnAppTpCode", 0, "0")
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "D2balBaseQryTp", 0, "1")
Call XAQuery_CSPAQ12300.SetFieldData("CSPAQ12300InBlock1", "UprcTpCode", 0, "0")
nSuccess = XAQuery_CSPAQ12300.Request(bNext)
If nSuccess < 0 Then
MsgBox "전송오류 : " & nSuccess
End If
End Sub

'--------------
' 잔고 수신
'--------------
Private Sub XAQuery_CSPAQ12300_ReceiveData(ByVal szTrCode As String)
Dim arrData(50, 10)

nCount = XAQuery_CSPAQ12300.GetBlockCount("CSPAQ12300OutBlock3")
If nCount = 0 Then
Exit Sub
End If
For i = 0 To nCount - 1
arrData(i, 0) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "IsuNo", i) ' 종목
arrData(i, 1) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "IsuNm", i) ' 종목명
arrData(i, 2) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "BnsBaseBalQty", i)' 잔고
arrData(i, 3) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "AvrUprc", i) ' 평균단가
arrData(i, 4) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "NowPrc", i) ' 현재가
arrData(i, 5) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "PchsAmt", i) ' 매입금액
Next

' 위치를 찾아서 데이터를 넣어줌
sRange = "A" & nBalanceListNextRow + 4 & ":F" & CStr(nCount + nBalanceListNextRow + 4)
Range(sRange) = arrData

' 다음에 들어갈 Row를 설정
nBalanceListNextRow = nBalanceListNextRow + nCount
End Sub

'----------------------------------------------------------------
' 잔고 수신
'----------------------------------------------------------------
Private Sub XAQuery_CSPAQ12300_ReceiveData(ByVal szTrCode As String)
Dim arrData(50, 10)

nCount = XAQuery_CSPAQ12300.GetBlockCount("CSPAQ12300OutBlock3")
If nCount = 0 Then
Exit Sub
End If
For i = 0 To nCount - 1
arrData(i, 0) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "IsuNo", i) ' 종목
arrData(i, 1) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "IsuNm", i) ' 종목명
arrData(i, 2) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "BnsBaseBalQty", i) ' 잔고
arrData(i, 3) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "AvrUprc", i) ' 평균단가
arrData(i, 4) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "NowPrc", i) ' 현재가
arrData(i, 5) = XAQuery_CSPAQ12300.GetFieldData("CSPAQ12300OutBlock3", "PchsAmt", i) ' 매입금액
Next

' 위치를 찾아서 데이터를 넣어줌
sRange = "A" & nBalanceListNextRow + 4 & ":F" & CStr(nCount + nBalanceListNextRow + 4)
Range(sRange) = arrData

' 다음에 들어갈 Row를 설정
nBalanceListNextRow = nBalanceListNextRow + nCount
End Sub

망한 알고리즘 삽질



여행가방의 잔해중 비밀번호를 잊어버린 자물쇠가 하나 있다. 세 개의 다이얼을 조합하여 풀어야 하는데, 경우의 수가 1,000개이다.

심심하던 차에 비밀번호를 풀어 보려고 하는데 , 999부터 시작하여 000까지 해보려고 했다. 아무래도 비밀번호가 그 중간인 500이상일 거라는 근거없는 추측탓이다.

운이 좋으면 500번 이내의 시행에서 끝날 것이다. 그러나 운이 나빠서 비밀번호가 000이라면 1/1000의 확률로 1,000번을 돌려야 한다. 손가락이 까질 각오를 해야 한다.

재빠르게 돌리는데 500을 넘겨도 열리지 않는다. 이미 나의 추측을 실패하고 점점 지쳐간다. 이런 다이얼타입의 자물쇠를 푸는 팁이 있지만, 시간 보내기 목적이라서 보질 않았다

 결국 131에 이르러서 마침내 딸깍하고 열린다. 쓸데 없지만 보람이...기뻐야 하는데 씁쓸한 기분은 뭐라고 해야 하나..아이고 의미없다. 이번 이분법 알고리즘은 실패인 셈이다. 똥손인증합니다.



[Xing API]xingAPI활용교육샘플v1.3.xlsm(6)-1302TR 증권

Sheet1에서 사용하는 t1302은 분단위 주가를 돌려주는 TR이다. DevCenter에서 TR의 입력과 출력을 보면, 단축코드(shcode), 작업구분(gubun), 시간(time), 건수(cnt)를 넘겨준다. 출력은 두 개의 블럭이 있는데, 하나는 t1302OutBlock, 나머지 하나는 t1302OutBlock1이다.

t1302OutBlock에서는 시간CTS(cts_time)만 돌려주고, t1302OutBlock1에서는 시간(chetime), 종가(close), 전일대비구분(sign), 전일대비(change), 등락율(diff)...등등을 돌려준다.

vba코드를 보면, 아래와 같이 개체를 생성하고, 필요한 입력필드에 값을 넣고 Request()를 한다.
    ' 주식 분별 주가 조회
If XAQuery_t1302 Is Nothing Then
Set XAQuery_t1302 = CreateObject("XA_DataSet.XAQuery")
XAQuery_t1302.ResFileName = "\res\t1302.res"
End If

' t1302 요청
Call XAQuery_t1302.SetFieldData("t1302InBlock", "shcode", 0, Range("C2").Value)
Call XAQuery_t1302.SetFieldData("t1302InBlock", "gubun", 0, "4") ' 10분 데이터 요청
Call XAQuery_t1302.SetFieldData("t1302InBlock", "time", 0, "")
Call XAQuery_t1302.SetFieldData("t1302InBlock", "cnt", 0, "21") ' 21개까지만 표시
nSuccess = XAQuery_t1302.Request(False)
If nSuccess < 0 Then
MsgBox "전송에러 : " & nSuccess
End If
Request후에는 콜백함수를 통해 결과값을 얻는다. 콜백함수Block은 사용하지 않고(중요한 게 아니라서) ,GetBlockCount()와 GetFieldData()를 사용하여 t1302OutBlock1의 결과만 가져온다.
Private Sub XAQuery_t1302_ReceiveData(ByVal szTrCode As String)
Dim arrData(21, 5)

'-------------------------------------------------------------
' t1301OutBlock1 은 Occurs 이므로 Occurs 갯수를 먼저 알아낸다
nCount = XAQuery_t1302.GetBlockCount("t1302OutBlock1")
' 최대 21개만 표시하므로 그 이상이면 21로 고정
' 물론 Request 할때 21개만 했으므로 그 이상 나오지는 않지만
' 혹시나 하는 마음에 ...
If nCount > 21 Then
nCount = 21
End If
'-------------------------------------------------------------

'-------------------------------------------------------------
' Occurs 이므로 For 문으로 갯수만큼 데이터를 가져와서 표시한다.
For i = 0 To nCount - 1
sTime = XAQuery_t1302.GetFieldData("t1302OutBlock1", "chetime", i) ' 수신시간
arrData(i, 0) = Left(sTime, 2) & ":" & Mid(sTime, 3, 2)
arrData(i, 1) = XAQuery_t1302.GetFieldData("t1302OutBlock1", "open", i)
arrData(i, 2) = XAQuery_t1302.GetFieldData("t1302OutBlock1", "high", i)
arrData(i, 3) = XAQuery_t1302.GetFieldData("t1302OutBlock1", "low", i)
arrData(i, 4) = XAQuery_t1302.GetFieldData("t1302OutBlock1", "close", i)
Next i
Range("L3:P23") = arrData
'-------------------------------------------------------------
End Sub

아이고, 프로그램을 실행했는데, 작업표시줄에만... 삽질

Win10으로 넘어오면서 몇몇 게으른 프로그램들은 그에 맞게 업데이트를 하지 않아, 종종 문제이다. 개인적으로 이런 느낌을 밭는 것이 이베스트증권의 DevCenter이다. 윈도우 화면 해상도와 따로 노는 UI가 맘에 들리가 없다.


사실 오늘 할 얘기는 이게 아니다. 간만에 실행했더니, 작업표시줄에는 프로그램이 정상적으로 표시되는데, 모니터에 프로그램 화면이 나오질 않는 것이다. 전에도 이런 일이 있었는데, 어찌 했는지 기억이 나질 않는다. 이전에 듀얼모니터를 사용하면서 다른 창에 띄었다가 종료하였는데, 이넘이 없는 모니터에 화면이 뜨는 거 아닐까? 가상데스크탑에 떠 있는 거 아닌가?(오늘은 이상하게 단축키도 안먹는다)

그래서 [작업 관리자]를 띄워 보았다. 뭐라도 해결방법이 있나 싶어서...프로세스에서 컨텍스트 메뉴를 열어 보니 , [최대화]라는 게 눈에 띈다. 클릭해보니, 화면에 가득차는 프로그램을 보고 이제 안도의 한숨을...

규격화된 엑셀데이터는 여러모로 편리하다 EXCEL/VBA

사무실에서 흔하게 사용하는 엑셀을 사용하다 보면 , 데이터베이스와 달리 자유롭게 데이터를 입력하고 수정/삭제할 수 있다는 장점이 되려 단점이 되기도 한다. 데이터 정리함에 있어 원칙없이 입력하면, 통계를 내거나 분석을 하기 위한 작업에 시간이 걸릴 수 있다.

쉬운 예를 들어 다음과 같이 월별로 고정된 항목의 월별 합계를 구하는 문제의 경우 , SUM()함수를 고려할 수 있는데, 다음과 같이 시트이름과 각 시트에서 데이터의 위치가 고정되어 쉽게 함수를 적용할 수 있다. [집계]워크시트에서는 함수를 =SUM('1:6'!C3)와 같이 사용하고 있다. 수식에 사용하는 시트참조를 셀 영역처럼 범위로 다루고 있다('1:6')

엑셀의 표기능을 사용하면 더더욱 이런 데이터 관리원칙이 빛을 발할 수 있다.

실수에서 소수점만 남기기(Decimal portion of a number) EXCEL/VBA

예전에 실수값에서 정수부분은 버리고 소수점만 남기는 문제인데, 고민을 한 문제였다. 그런데 그 해답은 무척 싱겁다.
=MOD(PI(),1)
MOD()함수와 1이 중요하다

[Xing API]xingAPI활용교육샘플v1.3.xlsm(5)-1101TR 증권

Sheet1 상단의 전역변수중에는 조회를 위한 XAQuery개체가 선언되어 있다.
Dim WithEvents XAQuery_t1101 As XAQuery ' [조회TR] 주식현재가 호가조회
Dim WithEvents XAQuery_t1302 As XAQuery ' [조회TR] 주식 분별 주가 조회
이와 관련하여 오늘은 TR을 알아보는 시간을 가져보려고 한다. TR에 대한 상세한 스펙(어떤 값을 넘겨야 어떤 값을 받는 지 등) DevCenter 앱을 통해 얻을 수 있다.
먼저 t1101이다. t1101은 데브센터에서 보이는 바와 같이 주식의 현재가 및 호가를 검색하는 TR이다. 데브센터를 실행하고 로그인하여 TR목록의 [찾기]상자에 't1101'을 입력하면 해당 TR에 대한 스펙을 볼 수 있다.

간단한 TR이라 입력한 것은 종목코드(shcode) 하나이지만 돌려주는 값은 무지막지하게 다양한 내용을 돌려준다.

C++헤더파일의 내용을 보면
/////////////////////////////////////////////
// 주식 현재가 호가 조회 ( ATTR,BLOCK,HEADTYPE=A )
#pragma pack( push, 1 )
#define NAME_t1101 "t1101"
// 기본입력
typedef struct _t1101InBlock
{
char shcode [6]; char _shcode; // [string, 6] 단축코드 StartPos 0, Length 6
} t1101InBlock, *LPt1101InBlock;
shcode는 6자리의 문자열을 주면 된다. 그래서 Private Sub DisplayPrice()프로시저에서는 다음과 같이 한다.
' t1101 요청
Call XAQuery_t1101.SetFieldData("t1101InBlock", "shcode", 0, Range("C2").Value)
nSuccess = XAQuery_t1101.Request(False)
SetFieldData()메서드에 Range("C2").Value의 6자리 종목코드를 입력하고 Request()메서드를 호출한다. 에러가 없다면 t1101OutBlock구조체에 한글이름,현재가,전일대비,등락률,매도호가, 매수호가 등등 많은 내용을 담아 콜백함수인 Private Sub XAQuery_t1101_ReceiveData(ByVal szTrCode As String)를 호출하여 돌려준다.

다른 TR 도 마찬가지이지만 SetFielData()메서드를 이용하여 ~InBlock에 정보를 채우고 Request()메서드를 통해 서버에 데이터를 요청한다. 서버는 ~OutBlock구조체에 요청한 데이터를 담아 해당TR의 콜백함수를 호출하여 돌려주는 방식이다. 이러한 메커니즘을 이해하면 나머지 TR도 같은 방식이므로 쉽게 이용할 수 있다.

결과값을 돌려받게 되면 GetFieldData()메서드를 사용하여 GetFieldData("t1101OutBlock", "hname", 0) 같은 식으로 OutBlock명과 필드명을 이용하여 데이터를 가져온다. C++ Res헤더파일을 보면 원하는 정보와 필드명 그리고 데이터타입을 알수 있다. 가령 한글명,hname,hname,char,20;의 경우 필드명이 hname이고 그 의미는 한글로된 종목이름 그리고 데이터 타입은 문자열 이고 길이는 20임을 알 수 있다. 다음은 t1101의 C++ Res헤더파일의 내용인데, 대충 읽어 보면 어떤 값을 주어야 하고, 어떤 값을 얻을 수 있는 지 알 수 있다.
BEGIN_FUNCTION_MAP
.Func,주식현재가호가조회(t1101),t1101,attr,block,headtype=A;
BEGIN_DATA_MAP
t1101InBlock,기본입력,input;
begin
단축코드,shcode,shcode,char,6;
end
t1101OutBlock,출력,output;
begin
한글명,hname,hname,char,20;
현재가,price,price,long,8;
전일대비구분,sign,sign,char,1;
전일대비,change,change,long,8;
등락율,diff,diff,float,6.2;
누적거래량,volume,volume,long,12;
전일종가,jnilclose,jnilclose,long,8;
매도호가1,offerho1,offerho1,long,8;
매수호가1,bidho1,bidho1,long,8;
매도호가수량1,offerrem1,offerrem1,long,12;
매수호가수량1,bidrem1,bidrem1,long,12;
직전매도대비수량1,preoffercha1,preoffercha1,long,12;
직전매수대비수량1,prebidcha1,prebidcha1,long,12;
매도호가2,offerho2,offerho2,long,8;
매수호가2,bidho2,bidho2,long,8;
매도호가수량2,offerrem2,offerrem2,long,12;
매수호가수량2,bidrem2,bidrem2,long,12;
직전매도대비수량2,preoffercha2,preoffercha2,long,12;
직전매수대비수량2,prebidcha2,prebidcha2,long,12;
매도호가3,offerho3,offerho3,long,8;
매수호가3,bidho3,bidho3,long,8;
매도호가수량3,offerrem3,offerrem3,long,12;
매수호가수량3,bidrem3,bidrem3,long,12;
직전매도대비수량3,preoffercha3,preoffercha3,long,12;
직전매수대비수량3,prebidcha3,prebidcha3,long,12;
매도호가4,offerho4,offerho4,long,8;
매수호가4,bidho4,bidho4,long,8;
매도호가수량4,offerrem4,offerrem4,long,12;
매수호가수량4,bidrem4,bidrem4,long,12;
직전매도대비수량4,preoffercha4,preoffercha4,long,12;
직전매수대비수량4,prebidcha4,prebidcha4,long,12;
매도호가5,offerho5,offerho5,long,8;
매수호가5,bidho5,bidho5,long,8;
매도호가수량5,offerrem5,offerrem5,long,12;
매수호가수량5,bidrem5,bidrem5,long,12;
직전매도대비수량5,preoffercha5,preoffercha5,long,12;
직전매수대비수량5,prebidcha5,prebidcha5,long,12;
매도호가6,offerho6,offerho6,long,8;
매수호가6,bidho6,bidho6,long,8;
매도호가수량6,offerrem6,offerrem6,long,12;
매수호가수량6,bidrem6,bidrem6,long,12;
직전매도대비수량6,preoffercha6,preoffercha6,long,12;
직전매수대비수량6,prebidcha6,prebidcha6,long,12;
매도호가7,offerho7,offerho7,long,8;
매수호가7,bidho7,bidho7,long,8;
매도호가수량7,offerrem7,offerrem7,long,12;
매수호가수량7,bidrem7,bidrem7,long,12;
직전매도대비수량7,preoffercha7,preoffercha7,long,12;
직전매수대비수량7,prebidcha7,prebidcha7,long,12;
매도호가8,offerho8,offerho8,long,8;
매수호가8,bidho8,bidho8,long,8;
매도호가수량8,offerrem8,offerrem8,long,12;
매수호가수량8,bidrem8,bidrem8,long,12;
직전매도대비수량8,preoffercha8,preoffercha8,long,12;
직전매수대비수량8,prebidcha8,prebidcha8,long,12;
매도호가9,offerho9,offerho9,long,8;
매수호가9,bidho9,bidho9,long,8;
매도호가수량9,offerrem9,offerrem9,long,12;
매수호가수량9,bidrem9,bidrem9,long,12;
직전매도대비수량9,preoffercha9,preoffercha9,long,12;
직전매수대비수량9,prebidcha9,prebidcha9,long,12;
매도호가10,offerho10,offerho10,long,8;
매수호가10,bidho10,bidho10,long,8;
매도호가수량10,offerrem10,offerrem10,long,12;
매수호가수량10,bidrem10,bidrem10,long,12;
직전매도대비수량10,preoffercha10,preoffercha10,long,12;
직전매수대비수량10,prebidcha10,prebidcha10,long,12;
매도호가수량합,offer,offer,long,12;
매수호가수량합,bid,bid,long,12;
직전매도대비수량합,preoffercha,preoffercha,long,12;
직전매수대비수량합,prebidcha,prebidcha,long,12;
수신시간,hotime,hotime,char,8;
예상체결가격,yeprice,yeprice,long,8;
예상체결수량,yevolume,yevolume,long,12;
예상체결전일구분,yesign,yesign,char,1;
예상체결전일대비,yechange,yechange,long,8;
예상체결등락율,yediff,yediff,float,6.2;
시간외매도잔량,tmoffer,tmoffer,long,12;
시간외매수잔량,tmbid,tmbid,long,12;
동시구분,ho_status,ho_status,char,1;
단축코드,shcode,shcode,char,6;
상한가,uplmtprice,uplmtprice,long,8;
하한가,dnlmtprice,dnlmtprice,long,8;
시가,open,open,long,8;
고가,high,high,long,8;
저가,low,low,long,8;
end
END_DATA_MAP
END_FUNCTION_MAP



잉여짓-간단한 일을 어렵게 해보기 삽질


위의 그림과 같은 행렬(4x3)이 있을 때, 각 행의 합계를 구하는 문제를 생각해보자. 보통은 간단히 SUM()함수나 수식을 이용하여 계산하면 된다. 이번에는 잉여스럽게 행렬곱셈을 이용하여 각 행의 합계를 구하는 삽질을 해보자.

A, B를 각각 m × n, n × 1 행렬이라고 하자. 이때 B 행렬의 원소는 모두 1이다. A와 B의 곱 AB는 m × 1 행렬이 된다. 그리고 곱행렬 AB의 각 행은 A행렬의 각 행 합계가 된다.
가령 다음과 같이 A와 B행렬이 있다면 , 두 개의 행렬의 곱셈 결과, 각 원소는 A행렬의 각 행의 합계이다.


보통 엑셀에서 이런 행렬곱셈은 MMULT()함수를 사용한다.

그런데 원소가 1인 B행렬은 합계를 구하기 위한 용도라서 셀에 둘 필요가 없다. 그래서 셀에 입력할 게 아니라 배열({1;1;1})로 저장하여 연산을 할 수 있다. 행렬 하나를 셀 대신 배열로 처리해서 한결 간결해졌다.


그런데 이 배열도 필요할까? 1로 가득찬 배열을 만드는 방법이 없을 까? 다음은 그런 방법이다.

위의 수식을 분석해보면
(1) =MMULT(
(2)     B2:D5,
(3)         TRANSPOSE(
(4)             COLUMN(B2:D5)^0
(5) ))

(1) 행렬곱셈함수이다.
(2) 행의 합계를 구할 행렬 A이다
(3) 행렬이나 벡터를 전치시키는 함수이다. (4)의 연산 결과를 곱셈연산에 맞춰 n × 1 행렬로 만들어 줄 것이다.
(4) 컬럼번호를 돌려주는 함수인데, A행렬의 컬럼번호를 구하고, 여기에 0승(^0)을 구하면 모두 1이다. 컬럼번호는 중요하지 않다. 어차피 ^0 연산으로 모두 1인 1 x n 인 행렬이 될 것이다.
(5) 수식끝

[Xing API]xingAPI활용교육샘플v1.3.xlsm(4)-시세 워크시트 증권

교육샘플 파일에는 3개의 워크시트가 있는데, 그중에서 [시세]워크시트의 구성과 관련코드를 알아 보도록 한다. [시세]워크시트는 다음 그림과 같이 1개의 종목코드를 입력하고 엔터를 누르면 그날의 가격관련 정보(시고저종 등등)를 보여주고, 호가창, 10분간격 시고저종 그리고 그날의 일봉차트로 구성되어 있다.

이번에는 코드를 살펴보도록 하자. [시세]워크시트는 vba에서 코드명이 Sheet1이다. 따라서 Sheet1을 더블클릭하면 관련된 코드를 볼 수 있다.

코드 상단에는 Sheet1 전역에서 사용하는 Xing API관련 개체변수가 선언되어 있다.
Dim WithEvents XAQuery_t1101 As XAQuery         ' [조회TR] 주식현재가 호가조회
Dim WithEvents XAQuery_t1302 As XAQuery ' [조회TR] 주식 분별 주가 조회
Dim WithEvents XAReal_S3_ As XAReal ' [실시간TR] 코스피체결
Dim WithEvents XAReal_K3_ As XAReal ' [실시간TR] 코스닥체결
Dim WithEvents XAReal_H1_ As XAReal ' [실시간TR] 코스피호가
Dim WithEvents XAReal_HA_ As XAReal ' [실시간TR] 코스닥호가
변수는 두 종류인데, XAQuery는 조회용 데이터를 위한 것이다. 즉 결과를 요청하면 그에 대한 회신을 1번 하고 끝난다. 그리고 XAReal는 실시간 조회용 개체인데, 장운영동안 지속적으로 데이터를 보내준다. 그리고 Dim 다음에 WithEvents라는 키워드를 볼 수 있다.

WithEvents키워드는 선언한 개체변수의 이벤트를 받기 위한 것이라는 정도만 알아 두면 된다. 조회를 하면 콜백함수를 통해 결과를 얻게 된다. 이를 이벤트라고 하며, _ReceiveData, _ReceiveRealData로 끝나는 프로시저가 이벤트에 따라 호출되는 콜백함수이다.
다음은 [시세]워크시트에서 종목코드를 입력하고 엔터를 입력하면 작동하는 이벤트 프로시저(Worksheet_Change())이다.
'------------------------------
' 셀의 값이 변경되면 호출
'----------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'--------------------------
' 종목코드를 변경하였을 경우에 현재가/분별주가를 요청
' 종목코드를 입력한 셀인지 체크
If Target.Address = "$C$2" Then
Call DisplayPrice
End If
'--------------------------
End Sub
Worksheet_Change()는 워크시트의 내용이 변경될 때 실행되는 이벤트 프로시저이다. 해당워크시트가 변경되었다면 구체적으로 어느 셀이 변경되었는 지 알아야 한다. 매개변수 Target은 변경된 셀 또는 셀영역을 가리키는 Range개체변수이다.

그래서 아래와 같이 변경된 셀인 Target의 주소가 종목코드를 받는 [C2]셀인지를 확인(종목코드를 입력하였는지)한다.
If Target.Address = "$C$2" Then

그리고 True이면 DisplayPrice프로시저를 호출한다(Call DisplayPrice)

DisplayPrice()프로시저에서는 Xing API의 XAQuery와 XAReal개체를 이용하여 주가데이터를 요청한다, 코드는 개체를 달리하면서 비슷한 패턴의 작업을 하는 것이다.

해당개체변수가 Nothing인지 확인하고 Nothing이라면 개체를 새로 만들어 할당하고 리소스파일을 연결한다. 그러나 실시간 데이터를 받는 XAReal개체변수의 경우 Nothing이 아니라면 UnadviseRealData메서드를 호출하여 실시간 구독을 해제한다. 그리고 조회용 개체변수인 XAQuery에 조회할 종목코드 등 필요한 정보를 설정하고 Request 메서드를 호출하여 데이터를 요청한다.

아래의 코드에서는 실시간 조회개체 XAReal개체변수를 생성만 하고 아무런 작업을 하지 않는다. XAQuery와 같이 필요한 정보를 설정하고 Request를 해야 하는 데 말이다. 이 작업은 XAQuery의 콜백함수에서 하게 된다. 즉 일단 조회하고 콜백함수로 결과를 받으면 그 콜백함수에서 다시 실시간 조회를 하는 식이다.
'----------------------------
' 현재가/분별주가를 요청
'----------------------------
Private Sub DisplayPrice()
'----------------------------
' 객체 생성 및 Res 할당

' 주식 현재가/호가 조회
If XAQuery_t1101 Is Nothing Then
Set XAQuery_t1101 = CreateObject("XA_DataSet.XAQuery")
XAQuery_t1101.ResFileName = "\res\t1101.res"
End If

' 주식 분별 주가 조회
If XAQuery_t1302 Is Nothing Then
Set XAQuery_t1302 = CreateObject("XA_DataSet.XAQuery")
XAQuery_t1302.ResFileName = "\res\t1302.res"
End If

' 실시간 코스피 체결
If XAReal_S3_ Is Nothing Then
Set XAReal_S3_ = CreateObject("XA_DataSet.XAReal")
XAReal_S3_.ResFileName = "\res\S3_.res"
Else
XAReal_S3_.UnadviseRealData ' 이미 생성이 되어 있다면 이전에 요청했다는 의미이므로 실시간을 취소한다.
End If

' 실시간 코스닥 체결
If XAReal_K3_ Is Nothing Then
Set XAReal_K3_ = CreateObject("XA_DataSet.XAReal")
XAReal_K3_.ResFileName = "\res\K3_.res"
Else
XAReal_K3_.UnadviseRealData ' 이미 생성이 되어 있다면 이전에 요청했다는 의미이므로 실시간을 취소한다.
End If

' 실시간 코스피 호가
If XAReal_H1_ Is Nothing Then
Set XAReal_H1_ = CreateObject("XA_DataSet.XAReal")
XAReal_H1_.ResFileName = "\res\H1_.res"
Else
XAReal_H1_.UnadviseRealData ' 이미 생성이 되어 있다면 이전에 요청했다는 의미이므로 실시간을 취소한다.
End If

' 실시간 코스닥 호가
If XAReal_HA_ Is Nothing Then
Set XAReal_HA_ = CreateObject("XA_DataSet.XAReal")
XAReal_HA_.ResFileName = "\res\HA_.res"
Else
XAReal_HA_.UnadviseRealData ' 이미 생성이 되어 있다면 이전에 요청했다는 의미이므로 실시간을 취소한다.
End If
'----------------------------
'----------------------------
' t1101 요청
Call XAQuery_t1101.SetFieldData("t1101InBlock", "shcode", 0, Range("C2").Value)
nSuccess = XAQuery_t1101.Request(False)
If nSuccess < 0 Then
MsgBox "전송에러 : " & nSuccess
End If
'----------------------------
'----------------------------
' t1302 요청
Call XAQuery_t1302.SetFieldData("t1302InBlock", "shcode", 0, Range("C2").Value)
Call XAQuery_t1302.SetFieldData("t1302InBlock", "gubun", 0, "4") ' 10분 데이터 요청
Call XAQuery_t1302.SetFieldData("t1302InBlock", "time", 0, "")
Call XAQuery_t1302.SetFieldData("t1302InBlock", "cnt", 0, "21") ' 21개까지만 표시
nSuccess = XAQuery_t1302.Request(False)
If nSuccess < 0 Then
MsgBox "전송에러 : " & nSuccess
End If
'----------------------------
End Sub

이거슨 숫자가 아녀~, 날짜로 바꿔 EXCEL/VBA

종종 엑셀이 주인의 말을 못알아 먹는다고 생각되는 경우가 있다. 문자로서 숫자를 입력하지만 엑셀은 덧하기/ 빼기/ 곱하기/ 나누기 와 같은 연산의 대상으로 알아먹는 경우이다. 숫자는 계산하는 용도로 주로 사용하지만, 관리나 식별을 위해 사용하기도 하고(버스 번호, 학급내 번호) 날짜를 표현하기 위해 사용한다.

아래와 같은 숫자를 우리는 숫자가 아닌 날짜로 인식할 수 있다. 아래의 그림에선 월/일/년 순서로 입력되어 있는데, 월/일/년이 쩜으로 구분되어 있기도 하고, 구분없이 붙여 놓은 경우도 있다. 또 월/일/년의 자리수도 1자리, 2자리, 4자리 등등 일관성이 없다.(뒤에 붙인 ; 은 컬럼구분자로 사용하기 위한 것이다. 컬럼구분자와 월일년 구분자가 겹치지 않는다면 없어도 된다) 다만 월일년의 순서대로 표시한 것은 공통이다.

이러한 자유분방한 날짜형식을 아래와 같이 YYYY-MM-DD 형식으로 바꾸는 것을 예전에는 vba로 만든 적이 있다.

다음은 엑셀의 [텍스트 나누기](데이터 탭에 위치) 기능을 이용하여 일관된 날짜 형식으로 바꾸는 것이다.

마지막 단계에서 원본이 월일년 순서대로 되어 있어, [열 데이터 서식]-[날짜] 콤보에서 그에 맞는 서식을 선택하는 것이 중요하다.

엑셀을 잘 표현할 수 있는 노래는... 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...






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


셀 범위가 아닌 배열로 차트 만들기 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

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