애들센스


알고리즘 트레이딩의 호가창 VWAP 증권

매수 또는 매도 주문을 낼 때, 단타매매가 아니거나, 뇌피셜로 급등/급락을 예상하는 경우 어떤 가격에 내는 것이 좋을 지 궁리할 수 있다. 좋은 가격에 주문을 내더라도 장 막판에 시장은 날 똥손으로 만들기도 한다. '매수/매도 호가의 평균 값이 시장참여자가 생각하는 이상적인 가격일 것이다'는 가정이 출발점이다. 그런데 가격만 평균내는 것은 불공평하다. 수량이 많은 호가는 평균에 더 많은 기여를 해야 하고, 반대로 수량이 적은 호가는 상대적으로 작은 기여를 해야 한다. 그래서 평균에 수량을 가중하여 평균되는 것, 이것이 VWAP이다(VWAP이 많이 알려져 있지만, 유일한 방법은 아니다)
특히 알고리즘 트레이딩에서 선물 매매의 경우 호가창의 데이터(호가와 잔량)는 중요한 요소이다. VWAP에 대해 좋은 설명중 하나는 아마추어 퀀트의 블로그에 잘 나와 있는 데,

VWAP (Volume Weighted Average Price)은 거래량 가중평균으로 표시한 가격으로 주로 알고리즘 트레이딩 (시스템 트레이딩)에 사용된다. 알고리즘 트레이딩은 다량의 주문을 체결할 때 시장에 충격을 주지 않으면서 가급적 저가 매수나 고가 매도를 목표로 한다. 그러나 어느 정도가 저가인지 고가인지가 불분명하므로 평균적인 단가로 주문을 체결하여 주문 의뢰자나 주문 대행자 모두 불만이 없는 정도의 수준에서 거래를 성사시키게 된다. 이 때 사용되는 평균적인 단가로 VWAP이 많이 사용된다. VWAP은 거래량 가중평균 가격인데, 비록 높은 가격에 매수했다 하더라도 매수 거래량이 많았다면 다른 주문자도 높은 가격에 매수한 것이 되므로 크게 불만이 없을 것이다.

아마추어퀀트: 23. VWAP 스프레드


다음은 파이썬으로 만든 VWAP이다. 호가창의 데이터를 통째로 던져 버리면 된다.
def calculate_vwap(order_book):
bids = order_book['bids']
asks = order_book['asks']

bid_idx = 0
ask_idx = 0
AskVWAP = 0
BidVWAP = 0
sumAsks = 0
sumBids = 0

while True:
try:
bid_price = bids[bid_idx][0]
bid_qty = bids[bid_idx][1]

ask_price = asks[ask_idx][0]
ask_qty = asks[ask_idx][1]
AskVWAP = AskVWAP + ask_price * ask_qty
BidVWAP = BidVWAP + bid_price * bid_qty
sumAsks = sumAsks + ask_qty
sumBids = sumBids + bid_qty
bid_idx = bid_idx + 1
ask_idx = ask_idx + 1
except IndexError:
break
TotalVWAP = (AskVWAP + BidVWAP) / (sumAsks + sumBids)
return TotalVWAP

if __name__ == "__main__":

# 1호가 [ 321.7 , 165.0 ] [ 321.75 , 5.0 ]
# 2호가 [ 321.65 , 128.0] [ 321.8 , 100.0 ]
# 3호가 [ 321.6 , 134.0 ] [ 321.85 , 109.0]
# 4호가 [ 321.55 , 109.0] [ 321.9 , 110.0 ]
# 5호가 [ 321.5 , 134.0 ] [ 321.95 , 119.0]

order_book = {
'bids': [
[ 321.7 , 165.0 ],
[ 321.65 , 128.0],
[ 321.6 , 134.0 ],
[ 321.55 , 109.0],
[ 321.5 , 134.0 ]
],
'asks': [
[ 321.75 , 5.0 ],
[ 321.8 , 100.0 ],
[ 321.85 , 109.0],
[ 321.9 , 110.0 ],
[ 321.95 , 119.0]
]
}
vwap = calculate_vwap(order_book)
print('VWAP :', vwap)

SetTimer와 Application.OnTime 오피스/VBA/Office.JS

비슷한 기능을 제공하는 API 함수와 VBA개체중 하나를 고르라면 VBA개체를 사용하는 편인데(안정성을 중시하므로), 이번에는 일정시간마다 뭔 작업을 일정 시간마다 수행하는 두 개의 방법을 보여주고자 한다.

다음은 API함수인 SetTimer와 KillTimer이다. SetTimer는 Sub TimerProc(ByVal hwnd As LongPtr, ByVal uMsg As LongPtr, ByVal idEvent As LongPtr, ByVal dwTime As LongPtr)를 실행설정하고 KillTimer는 반대의 역할, 실행설정을 해제한다.

SetTimer의 매개변수에서 hwnd는 핸들번호이다. 핸들이란 Win32 API프로그래밍을 해본 분은 알겠지만, 개체의 주민등록번호에 해당한다. Win32 API프로그래밍에서 뭔가 작업을 한다면 꼭 핸들이 필요한 경우가 많다. 즉 뭔가 대상이 있어야 뭔 일을 할 수 있기 때문이다. Application.hwnd는 Application 즉 지금 엑셀에서 작업하고 있다면 엑셀, 만일 워드에서 한다면 워드를 의미한다.

nIDEvent는 타이머의 아이디라고 하는 데, 큰 의미는 없다. uElapse는 다음에 지정할 프로시저의 실행간격인데, 1/1000초 단위이므로 1000을 주면 1초 간격이다. lpTimerFunc는 수행할 프로시저의 포인터이다. VBA에서는 AddressOf 연산자가 프로시저의 주소를 돌려준다.
' Win32 Library for VBA (32bit/64bit)
#If VBA7 Then '// 64 bit 오피스인 경우
Public Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Declare PtrSafe Function GetQueueStatus Lib "user32" (ByVal fuFlags As Long) As Long
#Else '// 32 bit 오피스인 경우
Public Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Public Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Public Declare Function GetQueueStatus Lib "user32" (ByVal fuFlags As Long) As Long
#End If

'// time interval(1초=1000 이므로 100은 100/1000초가 됨)
Public Const TIMER_INTERVAL As Long = 1000

'// Timer ID(아무 숫자나 상관없음 타이머를 여러개 사용할때 아이디만 다르게 하면 됨)
Public Const TIMER_ID As Long = 3100

Dim n As Long

'// 타이머 시작, 1000& = 1초
Sub StartTimer()
n = 1
StopTimer
SetTimer Application.hwnd, TIMER_ID, TIMER_INTERVAL, AddressOf TimerProc
End Sub

'// 타이머 종료
Sub StopTimer()
KillTimer Application.hwnd, TIMER_ID
End Sub

Sub TimerProc(ByVal hwnd As LongPtr, ByVal uMsg As LongPtr, _
ByVal idEvent As LongPtr, ByVal dwTime As LongPtr)
If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
Debug.Print n; Now
If n >= 8 Then StopTimer
n = n + 1
End Sub
Application.OnTime을 가지고 일정시각마다 수행하려면 아래의 코드와 같이 작성한다. 눈에 띄는 부분은 수행할 프로시저 이름을 문자열로 주는 것이며, Application.OnTime을 사용하는 프로시저가 자기 자신을 호출한다는 점이다. 그래서 원하는 작업은 그 이하에 적어 두어야 한다. 그리고 API함수와 달리 OnTime 하나로 설정과 해제를 같이 한다는 점이다. 그리고 해제시 Schedule 매개변수를 False로 둔다
Sub StartOnTime()
Dim TimeInterval As Date

TimeInterval = TimeValue("00:00:01")
Application.OnTime Now + TimeInterval, "StartOnTime"
n = n + 1
Debug.Print n; Now
If n >= 8 Then StopOnTime
End Sub

Sub StopOnTime()
Dim TimeInterval As Date

TimeInterval = TimeValue("00:00:01")

On Error Resume Next
Application.OnTime Now + TimeInterval, "StartOnTime", Schedule:=False
End Sub

JSON 만들고 자료 뽑아내기 오피스/VBA/Office.JS

VBA-JSON 덕분에 JSON을 처리하는 게 손쉬워졌는데, 깃허브에 특별히 친절한 코드 예가 부족해서 , 트레이딩 프로그램을 만들면서 테스트 코드를 만들어 보았다.

일단 JSON을 만드는 것부터...
Sub ConvertingJson()
Dim dic As New Dictionary
Dim Json As New Collection

dic.Add "BS", "BUY"
dic.Add "DATE", "20190515"
dic.Add "price", 57800
dic.Add "RSI", 0.5219
dic.Add "SIGNAL", 0.3978
Json.Add dic
Set dic = Nothing

dic.Add "BS", "SELL"
dic.Add "DATE", "20190519"
dic.Add "price", 59800
dic.Add "RSI", 0.5219
dic.Add "SIGNAL", 0.3978
Json.Add dic

Debug.Print JsonConverter.ConvertToJson(Json, 1, 0)

End Sub
실행을 하면 다음과 같은 JSON 데이터를 만들어 준다.
[
{
"BS": "BUY",
"DATE": "20190515",
"price": 57800,
"RSI": 0.5219,
"SIGNAL": 0.3978
},
{
"BS": "SELL",
"DATE": "20190519",
"price": 59800,
"RSI": 0.5219,
"SIGNAL": 0.3978
}
]
이번에는 JSON을 읽고 루프를 돌리거나 , 특정 항목을 읽어보는 예이다.
Sub ParsingJson()
Dim szJson As String
Dim Json As Collection
Dim jsonItem As Dictionary
Dim i As Long

szJson = "[ _
{""BS"":""BUY_"",""DATE"":""20190814"",""PRICE"":39950,""RSI"":0.3658,""SIGNAL"":0.3183},
{""BS"":""SELL"",""DATE"":""20190826"",""PRICE"":40750,""RSI"":0.4214,""SIGNAL"":0.4512},
{""BS"":""BUY_"",""DATE"":""20200317"",""PRICE"":41200,""RSI"":0.4206,""SIGNAL"":0.4142},
{""BS"":""SELL"",""DATE"":""20200318"",""PRICE"":38100,""RSI"":0.3722,""SIGNAL"":0.4058},
{""BS"":""LAST"",""DATE"":""20201028"",""PRICE"":54100,""RSI"":0.4589,""SIGNAL"":0.4687}
]"
Set Json = JsonConverter.ParseJson(szJson)

' 항목을 루프돈다
For i = 1 To Json.Count
Set jsonItem = Json.Item(i)
Debug.Print jsonItem("BS"), jsonItem("DATE"), jsonItem("PRICE"), jsonItem("RSI"), jsonItem("SIGNAL")
Next

' JSON의 마지막 항목을 읽는다
Debug.Print Json(Json.Count)("BS"), Json(Json.Count)("DATE"), Json(Json.Count)("PRICE"), _
Json(Json.Count)("RSI"), Json(Json.Count)("SIGNAL")
End Sub
[URL] VBA-JSON


나만 이렇게 나오나요? 삽질의 추억


언제부터 인지 모르지만 항상 이렇게 나오는 데, 다른 분들도 이렇게 나오나요? 이글루스가 망쪼가 들었나요? 이걸 해결 안하고 수 일동안 이러고 있다면,,

50290 런타임 오류 - 삽질로 갈 뻔한 에러 오피스/VBA/Office.JS

증권사 API를 이용하여 기술적 지표를 조회하고 백테스팅하는 것 만드는 데, 50290 런타임 에러가 난다. 한 종목을 하는 경우에 상관없지만, 여러 종목을 조회하는 경우 콜백함수가 마지막 종목조회시 실행되면서 이런 에러가 난다.

이것은 엑셀을 백그라운드로 돌릴 때 나오는 오래된 에러라고 한다.
Private Sub btnQuery_Click()
Dim rng As Range
Dim szSym As String

For Each rng In [B23:B30]
If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents
szSym = rng.Value
Set rngWrite = rng.Offset(0, 2)
Debug.Print rngWrite.Address
Call reqChartIndex(szSym)
Call API.Halt(3)
' Application.Wait (Now + TimeValue("0:00:03"))
Next

End Sub
...
...
...
Private Sub xaQ_ReceiveData(ByVal szTrCode As String)
Dim i As Long, j As Long, cnt As Long
Dim szFld As String
Dim fldsChartIndex

' 지표의indexid (지표별 구분키로 사용할 수 있다)
g_indexId = xaQ.GetFieldData("ChartIndexOutBlock", "indexid", 0)
' 데이터 갯수
cnt = xaQ.GetFieldData("ChartIndexOutBlock", "rec_cnt", 0): If cnt = 0 Then Exit Sub
cnt = cnt - 1
' 최근일자의 OHLCV와 지표 출력
fldsChartIndex = Array("date", "open", "high", "low", _
"close", "volume", "value1", "value2")
ReDim OHLC(UBound(fldsChartIndex))
For j = LBound(fldsChartIndex) To UBound(fldsChartIndex)
szFld = fldsChartIndex(j)
OHLC(j) = xaQ.GetFieldData("ChartIndexOutBlock1", szFld, cnt)
Next
Me.Range(rngWrite, rngWrite.Offset(0, 7)) = OHLC
End Sub
...
...
...
Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

Sub Halt(Finish As Long)
'Finish in seconds
Dim NowTick As Long
Dim EndTick As Long
EndTick = GetTickCount + (Finish * 1000)
Do
NowTick = GetTickCount
DoEvents
Loop Until NowTick >= EndTick
End Sub

특히 Me.Range(rngWrite, rngWrite.Offset(0, 7)) = OHLC에서 런타임 에러가 나오는 데, 처음엔 출력할 Range개체가 Nothing인가 싶어 의심했다.

그러나 Range, OHLC 배열 모두 정상이었다. 다행히
Stackoverflow.com
스택오버플로(Run-time error '50290': Application-defined or object-defined error when Excel runs in the background)에 해답이 있었다. 물론 질문한 사람은 비동기 WinHTTP를 사용하면서 발생한 에러인데, 모두 엑셀이 백그라운드로 돌면서 발생하는 에러이다. 백그라운드로 도는 중인데, Application 을 콜하는 것이 엑셀에서 안되는 듯...

처음 만들 때 콜백함수 대기시간을 주기 위해 다음과 같이 Wait으로 3초 정도 주었는 데,
Application.Wait (Now + TimeValue("0:00:03"))

Sergei Trunov 의 답변대로 API 함수 GetTickCount 로 변경하니 제대로 돌아가는 것 같다.


JSON읽는 방법 오피스/VBA/Office.JS


JSON을 VBA에서 사용할 일이 그리 없는 데, JSON 을 만들지는 않지만 읽고 그속의 데이터를 뽑는 방법이 있다.
Public Function JsonGet(eKey$, eJsonString$, Optional eDlim$ = ".") As String
Dim tmp$()
Static sJsonString$
If Trim(eKey$) = "" Or Trim(eJsonString$) = "" Then Exit Function
If sJsonString <> eJsonString Then
sJsonString = eJsonString
oScriptEngine.Language = "JScript"
Set objJSON = oScriptEngine.Eval("(" + eJsonString + ")")
End If
tmp = Split(eKey, eDlim)
If UBound(tmp) = 0 Then JsonGet = VBA.CallByName(objJSON, eKey, VbGet): Exit Function

Dim i&, o As Object
Set o = objJSON
For i = 0 To UBound(tmp) - 1
Set o = VBA.CallByName(o, tmp(i), VbGet)
Next i
JsonGet = VBA.CallByName(o, tmp(i), VbGet)
Set o = Nothing
End Function

'Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
' Set objJSON = Nothing
'End Sub
JsonGet() 함수는 기본적으로 다음과 같이 키를 주면 해당 값을 돌려준다.
Debug.Print JsonGet("key1", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")

두 번째 예는 중첩된 키-값이 있는 경우인데, 중첩속 키를 순서대로 접근하여 얻을 수 있다.
Debug.Print JsonGet("key2.key3", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")
key2의 값은 중첩된 새로운 key3와 값 value3가 있다.
Debug.Print JsonGet("mykey2.keyinternal1", "{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}")
mykey2.keyinternal1는 mykey2속에서 keyinternal1의 값 돌려 준다.

세 번째 예는 여러 개의 중첩된 JSON이 있고 key가 중복된 경우인데, "result.0.Ask"의 0번째 JSON에 키 Ask를 얻는 것이다. "result.0.Ask"을 준다면 0.00000074를 돌려준다.
Debug.Print JsonGet("result.0.Ask", "{'result':[{'MarketName':'BTC-1ST','Bid':0.00004718,'Ask':0.00004799},{'MarketName':'BTC-2GIVE','Bid':0.00000073,'Ask':0.00000074}]}")


'in vb6 click "Tools"->"References" then
'check the box "Microsoft Script Control 1.0";
Dim oScriptEngine As New ScriptControl
Dim objJSON As Object

''to use it
Private Sub Command1_Click()
''returns "value1"
Debug.Print JsonGet("key1", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")

''returns "value3"
Debug.Print JsonGet("key2.key3", "{'key1': 'value1' ,'key2': { 'key3': 'value3' } }")

''returns "0.00004799"
Debug.Print JsonGet("result.0.Ask", "{'result':[{'MarketName':'BTC-1ST','Bid':0.00004718,'Ask':0.00004799},{'MarketName':'BTC-2GIVE','Bid':0.00000073,'Ask':0.00000074}]}")

''returns "22.1"
Debug.Print JsonGet("mykey2.keyinternal1", "{mykey:1111, mykey2:{keyinternal1:22.1,keyinternal2:22.2}, mykey3:3333}")

End Sub
(보고 싶어하시는 찐팬분이 있어 업데이트합니다.)

ETF 정보 가져오기 펀드

https://finance.naver.com/api/sise/etfItemList.nhn 에 접속하면 평소 익숙한 웹페이지가 아닌 이상한 글자로 가득찬 내용을 보게 될 것이다.

이것은 네이버 파이낸스에서 제공해주는 ETF 정보이다. ETF 종가, 기준가, 거래금액, 시가총액 등등 JSON 형식으로 보여주는 것이다. 간단한 정보 하나 보자고, 힘들게 증권사 API나 크롤링하는 것보단 훨씬 간편하다. JSON을 우리가 쉽게 볼 수 있는 형식으로 변환만 해주면 되기 때문이다.

다음은 JSON을 받아 파이썬의 딕셔너리로 받고 , 다시 리스트로 변환하고, 다시 Pandas 데이터프레임으로 변환하는 예이다. 그리고 엑셀파일로 변환도 가능하다
import requests
import json
from pandas import json_normalize

url = 'https://finance.naver.com/api/sise/etfItemList.nhn'
etf_dict = json.loads( requests.get( url ).text )
etf_list = etf_dict[ 'result' ][ 'etfItemList' ]
df = json_normalize( etf_list[ 'result' ][ 'etfItemList' ] )
df.to_excel( 'etf_list.xlsx' )


분할선형회귀(Segmented Linear Regression) C/C++

회귀분석은 데이터 분석을 위한 고전적인 수학적 방법중 하나인데, 교과서와 많은 문헌에서 꼭 나오는 것이다. 선형회귀분석은 주어진 데이터에 대한 최적의 근사를 제공한다. 데이터의 값은 선형근사값과 통계적으로 난수로서 노이즈(noise)의 합계라고 볼 수 있다.

선형회귀는 수 많은 사업과 과학에서 사용된다. 선형회귀가 인기있는 이유중 하나는 예측이다. 눈에 띄는 아웃라이어(outlier)는 일종의 특이점으로 볼 수 있다. 그리고 절편과 기울기로 추세를 표현할 수 있는 간단함도 하나의 장점이기도 한다. 그러나 노이즈가 많으면 부정확할 수 있다. 그래서 한계를 벗어나기 위한 것중 하나가 Segmented Linear Regression(분할선형회귀)이다.

머리 아프게 공부하면서 분할선형회귀를 이해하는 것보단 한 장의 그림이 분할선형회귀를 바로 이해하게 해준다(아하~ 이런 거구만)

이걸 활용하면 주식투자에 뭔가 도움이 되지 않을 까 싶다. 추세를 알 수 있어 보인다. 이 글은 코드프로젝트에 실린 Vadim Stadnik의 Segmented Linear Regression 포스팅을 보고 적는 것이다. 공부하기 싫어 다 읽어 보진 않았지만 알고리즘은 구간을 만들고 그사이의 중간 값을 만들고, 다시 중간값과 주변값을 범위로 잡고 다시 중간값을 구한다. 마치 세포분열을 하는 모습을 상상하면 될 듯… 아래의 그림을 보면 쉽게 그 알고리즘을 알 수 있을 것이다.

다음은 포스팅에서 제공하는 샘플데이터와 소스코드를 사용하여 만들어본 예이다 (별도의 사용 예를 제공하지 않는다. 헤더파일(SegmentedRegression.h)을 따로 만들었다)
#include <iostream>
#include <fstream>
#include <cstdlib> // for exit function
#include <vector>
#include "SegmentedRegression.h"

using namespace std;

int main(int argc, char** argv)
{
ifstream indata; // indata is like cin
double num1, num2; // variables for input value

// input dataset
std::vector<double> data_x_user ;
std::vector<double> data_y_user ;

// result of algorithm
std::vector<double> data_x_slr ;
std::vector<double> data_y_slr ;

// user specified approximation accuracy
const double devn_max = 0.5 ;

// half length of smoothing window ( h_len+1+h_len )
// for simple moving average
const int half_len = 10 ;

indata.open("sample_data\\real_data.txt"); // opens the file
if(!indata) { // file couldn't be opened
cerr << "Error: file could not be opened" << endl;
exit(1);
}

while ( !indata.eof() )
{
indata >> num1 >> num2;
data_x_user.emplace_back(num1);data_y_user.emplace_back(num2);
}
indata.close();
cout << "End-of-file reached.." << endl;

//// fast algorithm for segmented linear regression
if (SegmentedRegressionFast( data_x_user, data_y_user, devn_max, half_len, data_x_slr, data_y_slr))
{
ofstream myfile ("SegmentedFAST.csv");
if (myfile.is_open())
{
for(unsigned i = 0; i < data_x_slr.size(); ++i) {
myfile<< data_x_slr[i] << "," << data_y_slr[i] << "\n";
}
myfile.close();
cout << "just saved SegmentedFAST.csv for fast algorithm";
}
else cout << "Unable to open file for fast algorithm";
}

//// slow algorithm for segmented linear regression
if (SegmentedRegressionThorough( data_x_user, data_y_user, devn_max, data_x_slr, data_y_slr))
{
ofstream myfile ("SegmentedThorough.csv");
if (myfile.is_open())
{
for(unsigned i = 0; i < data_x_slr.size(); ++i) {
myfile<< data_x_slr[i] << "," << data_y_slr[i] << "\n";
}
myfile.close();
cout << "just saved SegmentedThorough.csv for slow algorithm";
}
else cout << "Unable to open file for slow algorithm";
}
return 0;
}

출력된 결과를 엑셀로 읽어 차트를 그려 보았다. 정말 되는 거니?


DDE라는 고인물을 아직도 쓰네~ 오피스/VBA/Office.JS


DDE 라는 물건은 증권사 HTS를 사용하다 보면 만나는 건데, 상당히 오래된 기술이다. 하지만 간편히 실시간 데이터를 받을 수 있는 방법이라 아직도 애용한다. 중간에 RTD라는 DDE 대체물이 생기긴 했는 데, 어찌 된 일인지 외국에 비해 한국에서는 이것을 잘 사용하지 않는다.

예전 포스팅에서 DDE를 이용하여 실시간 데이터를 쌓는 방법(간단한 DDE 프로그래밍, DDE 클라이언트를 다시 만들어 보다)을 소개한 적이 있는데, 이번에 소개하는 방법은 조금 더 완성된 방법(기본적인 건 같지만) 이기도 하다.

이전의 포스팅, "DDE 클라이언트를 다시 만들어 보다"에서 하나의 종목이 아닌 여러 종목개의 종목의 실시간 데이터를 쌓는 방법을 보였는 데, 당시 아쉬운 점중 하나는 커버할 종목 갯수만큼 프로시저를 만들다 보니, 코드가 너무 길고 갯수의 증감에 따라 그에 상응하는 프로시저를 다시 만들거나 지우거나 하는 등 코드관리가 불편하다는 것이다.

이번에는 그러한 단점을 해결하고, 데이터를 워크시트에 쌓지 않고 CSV파일로 저장하도록 만들었다.
Sub startDDE()
Dim aLinks, i As Long, strCode As String, Procedure As String

aLinks = ThisWorkbook.LinkSources(xlOLELinks)
aLinks = Filter(aLinks, ";시간", True)

If IsEmpty(aLinks) Then
Debug.Print "Error: DDE LinkSource Empty"
Exit Sub
End If
For i = LBound(aLinks) To UBound(aLinks)
strCode = Right(split(aLinks(i), ";")(0), 8)
Procedure = "'onData " & Chr(34) & strCode & Chr(34) & "'"
ThisWorkbook.SetLinkOnData aLinks(i), Procedure
Next
Call openFiles
Debug.Print "Starting DDE Recording..."
End Sub
프로시저의 갯수를 줄일 수 있는 비결은 DDE링크에 거는 프로시저를 정의하는 방법을 개선한 덕분이다.

Procedure = "'onData " & Chr(34) & strCode & Chr(34) & "'"

onData()프로시저에 종목코드를 매개변수로 넘겨주도록 코딩하였다. 그래서 DDE링크가 갱신될때마다 onData()프로시저 하나로 통일한 것이다. startDDE()프로시저가 처음 한번만 실행하고 onData()프로시저는 수 천번의 DDE링크 갱신때마다 실행될 것이다. 그래서 코드가 되도록 짧고 빠르게 실행되기 위해 워크시트함수를 자주 사용하였다.

다음은 onData()프로시저이다.
Sub onData(strCode As String)
Dim rng As Range
Dim str As String
Dim idx As Long

Call CheckFutures
On Error GoTo ErrHandler

If GetQueueStatus(QS_INPUT) <> 0 Then DoEvents

idx = WorksheetFunction.Match(strCode, Main.Range(Main.[C1], Main.[C1].End(xlDown)), 0)
Set rng = Main.Range(Main.Cells(idx, 2), Main.Cells(idx, 2).End(xlToRight))
str = Join(Application.Transpose(Application.Transpose(rng.Value)), ",")

Select Case Left(strCode, 3)
Case "101": flFutures.WriteLine str: Debug.Print "onData: " & str
Case "201": flCallOptions.WriteLine str
Case "301": flPutoptions.WriteLine str
End Select
flAll.WriteLine str


Set rng = Nothing
Exit Sub

ErrHandler:
Debug.Print "Error: " & Err.Description
Err.Clear
Call closeFiles
Call stopDDE
End Sub
onData()프로시저에 들어오는 매개변수 strCode는 막 갱신된 DDE링크의 항목인데, 이 항목을 워크시트에서 찾아서 , 그 행의 전부를 CSV로 저장하는 것이다.
워크시트는 선물과 콜옵션/풋옵션이 같이 있는 데, 선물과 콜옵션, 풋옵션별로 3개의 CSV파일을 저장할 것이다. 선물, 콜옵션, 풋옵션은 종목코드가 101~, 201~, 301~등으로 시작하므로 이를 가지고 셋을 구분할 수 있다. Select Case 구문은 이러한 작업을 하는 것이다.
  Select Case Left(strCode, 3)
Case "101": flFutures.WriteLine str
Case "201": flCallOptions.WriteLine str
Case "301": flPutoptions.WriteLine str
End Select
파일로 출력할 문자열(str)에서 맨 앞 세글자만 추출하고 구분하여 저장한다. 그러나 세 가지를 별로 CSV 로 저장하는 게 불편하여 선물, 콜옵션, 풋옵션 구분없이 flAll.WriteLine str 를 사용하여 모두 저장한다.

stopDDE()프로시저는 DDE링크에 걸린 프로시저를 해제하고, 기록하던 CSV파일을 닫는 일을 한다.
Sub stopDDE()
Dim aLinks, i As Long

aLinks = ThisWorkbook.LinkSources(xlOLELinks)
If IsEmpty(aLinks) Then Exit Sub
For i = LBound(aLinks) To UBound(aLinks)
ThisWorkbook.SetLinkOnData aLinks(i), ""
Next
Call closeFiles
Debug.Print "Ending DDE Recording..."
End
End Sub
이번에는 CSV파일을 열고 닫는 프로시저이다. VB의 레거시 구문대신 스크립팅 라이브러리를 사용하였다. 파일핸들링은 아래의 두개의 프로시저 뿐만 아니라 onData() 에서도 하므로 다음과 같은 파일개체를 전역변수로 사용한다.

Dim fso As Object, flCallOptions, flPutoptions, flFutures, flAll
Sub openFiles()
Dim fnCallOptions As String, fnPutOptions As String, fnFutures As String, fnAll As String
Dim str As String

fnFutures = ThisWorkbook.Path & "\" & Date & "_futures.csv"
fnCallOptions = ThisWorkbook.Path & "\" & Date & "_calloptions.csv"
fnPutOptions = ThisWorkbook.Path & "\" & Date & "_putoptions.csv"
fnAll = ThisWorkbook.Path & "\" & Date & "_all.csv"

Set fso = CreateObject("scripting.filesystemobject")

'신규파일이면 컬럼명 추가, 기존파일이면 생략
If Len(Dir(fnFutures)) = 0 Then str = "종목명,종목코드,시간,현재가,잔존일"
Set flFutures = fso.OpenTextFile(fnFutures, 8, True)
If Len(str) <> 0 Then flFutures.WriteLine str: str = ""

If Len(Dir(fnCallOptions)) = 0 Then str = "종목명,종목코드,시간,현재가,잔존일,미결제약정,이론가,행사가격,델타,감마,베가,세타,로,내재변동성,역사적변동성,CD금리"
Set flCallOptions = fso.OpenTextFile(fnCallOptions, 8, True)
If Len(str) <> 0 Then flCallOptions.WriteLine str: str = ""

If Len(Dir(fnPutOptions)) = 0 Then str = "종목명,종목코드,시간,현재가,잔존일,미결제약정,이론가,행사가격,델타,감마,베가,세타,로,내재변동성,역사적변동성,CD금리"
Set flPutoptions = fso.OpenTextFile(fnPutOptions, 8, True)
If Len(str) <> 0 Then flPutoptions.WriteLine str: str = ""

If Len(Dir(fnAll)) = 0 Then str = "종목명,종목코드,시간,현재가,잔존일,미결제약정,이론가,행사가격,델타,감마,베가,세타,로,내재변동성,역사적변동성,CD금리"
Set flAll = fso.OpenTextFile(fnAll, 8, True)
If Len(str) <> 0 Then flAll.WriteLine str
End Sub

Sub closeFiles()
On Error Resume Next
flFutures.Close
flCallOptions.Close
flPutoptions.Close
flAll.Close
Set flFutures = Nothing
Set flCallOptions = Nothing
Set flPutoptions = Nothing
Set flAll = Nothing
Set fso = Nothing
End Sub
장 마감하고, 그날의 결과물인 CSV파일을 메일로 전송할 필요도 있다. 다음은 검색하면 흔하게 볼 수 있는 메일전송 프로시저이다. 메일전송방법은 여러 가지가 있는 데, 가장 익숙한 방법이라 이걸 골랐다. 미리 아웃룩에 메일설정을 해두어야 한다.

(아직 해결하지 못한 문제인데, [허용]버튼을 눌러주어야 한다. 아웃룩의 보안문제쪽에서 해결해야 할 듯한데...)
(업데이트) 예상한 대로 아웃룩 옵션에 아래와 같은 해결책이 있었다.


Sub sendMail()
Dim OutApp As Object, OutMail As Object, BodyText As String
Dim file_fut As String, file_cal As String, file_put As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

BodyText = _
"Please do not reply to this email, as this inbox is not monitored." & Chr(13) & _
"Best," & Chr(13) & _
"Seungjoo from Seoul"

On Error Resume Next
With OutMail
.To = "********@gmail.com"
.CC = ""
.BCC = ""
.Subject = "KOSPI200 Futures and Options(" & Date & ")"
.Body = BodyText
file_fut = ThisWorkbook.Path & "\" & Date & "_futures.csv"
file_cal = ThisWorkbook.Path & "\" & Date & "_calloptions.csv"
file_put = ThisWorkbook.Path & "\" & Date & "_putoptions.csv"
If Len(Dir(file_fut)) <> 0 Then .Attachments.Add file_fut
If Len(Dir(file_cal)) <> 0 Then .Attachments.Add file_cal
If Len(Dir(file_put)) <> 0 Then .Attachments.Add file_put
.Send 'or use .Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Debug.Print "Sent the data using e-mail..."
End Sub


그런데 종목 하나만 다룰 때 보지 못한 문제가 하나 생겼는데, DDE 링크갱신이 많다보니 메모리누수가 생기는 건지 모르겠지만 선물의 DDE링크 갱신이 멈추는 경우가 생긴다(OTM옵션의 경우 거의 거래가 없어 호가가 그럴 수 있지만 선물 호가가 없을리는 없다)

경험상 오전에 한번, 오후에 한번 이런 현상이 발생한다. 처음에는 엑셀을 종료하고 파일을 다시 여는 극처방을 사용했지만, 멈춘 DDE링크가 있는 셀에 F2를 눌러 셀 편집모드로 들어갔다가 아무것도 수정하지 않고 그냥 ENTER 를 눌러 빠져 나오면 정상작동을 한다.

그래서 고육지책으로 선물의 DDE링크중 시간과 컴퓨터의 시간을 비교하여 3분이상 차이가 나면 선물의 DDE링크가 멈춘것으로 판단하고 F2와 ENTER키를 전송하도록 하였다. 그러나 한번에 DDE링크가 살아나지 않아서 3분이상의 시간차이가 그대로 남아있어 수십 번 F2와 ENTER키 전송이 발생하는 일이 생긴다.

당연히 화면이 번쩍 번쩍 거리니, 보는 나의 심장이 쫄깃해진다. 그래서 한번만 실행하도록 보장하기 위해 레지스트리에 멈춘 DDE링크 시간항목을 저장하고 , 실행시 레지스트리에 저장한 고장난 시간을 꺼내 현재 DDE링크 시간과 비교하도록 하였다(If CDate(GetSetting("DDE20", "FUT", "F2")) = CDate(ts1) Then Exit Sub)

이러한 일을 하는 프로시저가 CheckFutures()이다.
Sub CheckFutures()
Dim ts0 As Date, ts1 As Date

On Error Resume Next
ts1 = RetTime(Main.[D2].Value2): ts0 = Time()
If Err.Number <> 0 Then Exit Sub
If CDate(GetSetting("DDE20", "FUT", "F2")) = CDate(ts1) Then Exit Sub

If Abs(DateDiff("n", ts0, ts1)) > 3 Then
Main.[D2].Activate
With Application
AppActivate .Caption: .SendKeys "{F2}": .SendKeys "{ENTER}": End With
Call SaveSetting("DDE20", "FUT", "F2", ts1)
End If
End Sub

'// DDE링크 시간포맷이 hhmmss이므로 이를 오늘자 시간으로 만들어주는 함수
Function RetTime(IntTime As Long) As Date
RetTime = TimeSerial(Int(IntTime / 10000), Int((IntTime Mod 10000) / 100), (IntTime Mod 100))
End Function


마지막으로 오전 장개시와 오후 장마감에 맞춰 자동으로 실행되도록 할 필요가 생긴다. 장개시때 startDDE를 실행하고 , 장마감하면 stopDDE를 실행하여 DDE기록을 중단하고 , 이후 1분뒤에 CSV파일을 메일전송하고 , 다시 1분후에 엑셀을 종료하도록 하였다.

이것은 워크북을 열때 발생하는 _Open() 이벤트 프로시저에 넣어두면 된다
Private Sub Workbook_Open()
With Application
On Error Resume Next
.OnTime TimeValue("09:00:00"), "startDDE", , False
.OnTime TimeValue("15:40:00"), "stopDDE", , False
.OnTime TimeValue("15:41:00"), "sendMail", , False
.OnTime TimeValue("15:42:00"), "shutDownThis", , False

.OnTime TimeValue("09:00:00"), "startDDE"
.OnTime TimeValue("15:40:00"), "stopDDE"
.OnTime TimeValue("15:41:00"), "sendMail"
.OnTime TimeValue("15:42:00"), "shutDownThis"
End With
End Sub
다음은 엑셀을 종료하는 프로시저이다. 종료시 저장을 하도록 하였다.
Sub shutDownThis()
ThisWorkbook.Save
Application.Quit
End Sub


다음은 이렇게 긁어 모은 틱데이터이다.
[DOWNLOAD] 2020-10-08_.zip
이번에 이 파일을 올리면서 처음 알았는 데, 첨부파일의 최대크기가 5MB이고, CSV는 허용하지 않는다고 한다.
( 하악~이글루스...할말은 많지만 하지 않겠다~ )

다음은 전체소스이다.

곧 나올 책에 사용할 커버 이미지를 파이썬으로 만들어 보다 삽질의 추억

조만간 출간할 책에 사용할 커버이미지를 만들어 보았다. 책의 내용이 포트폴리오 이론을 파이썬으로 구현하면서 그 이론들을 다루는 만큼 효율적 포트폴리오 그림을 그 그림을 만드는 파이썬 코드로 채운 그림이다(이것도 일종의 recursive?)

이것은 ASCII Art 라고 불리우는 데, https://manytools.org/hacker-tools/convert-images-to-ascii-art/의 도움을 받았다. 그리고 텍스트파일을 다운받아 텍스트의 각 글자를 파이썬의 소스코드로 변환하는 것이다.
ascii_txt = r'C:\Users\Jim\Documents\portfolio.txt'
cover_txt = r'C:\Users\Jim\coverconda.py'
new_txt = r'C:\Users\Jim\Documents\bookportfolio.txt'

file_asc = open(ascii_txt, 'rt', encoding="utf-8") # ASCII Art 텍스트파일
file_cov = open(cover_txt, 'rt', encoding="utf-8") # 치환할 글자가 담긴 파일
file_new = open(new_txt, 'wt', encoding="utf-8") # 치환하여 새로 만들어질 파일

while 1:
    char = file_asc.read(1) # 한 글자를 읽는다
    if not char: # 다 읽었다면 종료
        break
    if not char.isspace(): #  치환대상이 공백문자가 아니라면
        code = file_cov.read(1) # 치환할 글자 읽기
        while code.isspace() or code=='\t' : # 치환할 글자가 공백 또는 탭문자라면
            code = file_cov.read(1)   #공백이 아닐때까지 계속 치환용 글자 읽기    
        char = code # 글자 바꿔치기
    file_new.write(char) # 새 파일에 쓰기

file_new.close()
file_cov.close()
file_asc.close()
소스코드는 아직 더 다듬을 여지가 있는 데, 캐리지-리턴을 공백으로 바꾸어 치환할 내용이 서로 달라붙지 않고 공백으로 구분되게 만들 필요가 있다. 그리고 소스코드 길이를 더 줄이는 최적화도 생각해볼 필요도 있다. 그리고 ASCII Art를 외부 서비스를 이용하지 않고 직접 만들어 작업 절차를 간단히 하면 더 좋을 듯한데...굳이 이런 삽질에 시간을 낭비할 필요는 없을 것 같다.

같은 내용을 c언어로 만들면:
#include <stdio.h>
#include <stdlib.h>

/* run this program using the console pauser or add your own getch, system("pause") or input loop */

int main(int argc, char *argv[]) {
char c1, c2;
FILE *f1, *f2, *f3;
f1 = fopen("ddangul.txt","rt"); if(f1==NULL) { perror("Ascii art file not found\n"); return 1; }
f2 = fopen("fin_recipes.h","rt"); if(f2==NULL) { perror("Source file not found\n"); return 2; }
f3 = fopen("source-art.txt","wt"); if(f3==NULL) { perror("Can not create target file\n"); return 3; }

for(;;)
{
c1 = fgetc(f1);
if(feof(f1)) break;

if(!(c1==' '||c1=='\n'||c1=='\t')) {
while(1)
{
c2 = fgetc(f2);
if(c2==' '||c2=='\n'||c2=='\t')
fseek(f2, 2, SEEK_CUR);
else
break;
}
c1 = c2;
}
fputc(c1, f3);
}

fclose(f1);
fclose(f2);
fclose(f3);
return 0;
}

어쩌다보니 파이썬으로 우연히 엑셀의 셀 주소 문자열을 만들다 파이썬/쟁고

두 개의 리스트내 각 원소를 뽑아서 합치다 보니 엑셀의 셀 주소처럼 보인다(이 정도면 중증이다)
x = [ "A", "B", "C", "D", "D" ]
y = [ 1, 2, 3, 4, 5 ]

z = ['A1', 'B2', 'C3', 'D4', 'D5']

위의 z처럼 값을 만들려고 하고 있습니다.

x = [ "A", "B", "C", "D", "D" ]
y = [ 1, 2, 3, 4, 5 ]
z = map( lambda x,y : ( "%s%d" % (x, y) ), x, y )
print(list(z))

하나 건너 또는 둘 건너 루프 돌기 오피스/VBA/Office.JS

For~Next 루프를 배울 때 흥미로운 것중 하나는 Step이다.
For counter [ As datatype ] = start To end [ Step step ]
    [ statements ]
    [ Continue For ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]

기본적으로 1씩 증가하면서 루프를 돌지만,
For index As Integer = 1 To 5
    Debug.Write(index.ToString & " ")
Next
Debug.WriteLine("")
' Output: 1 2 3 4 5

Step 을 사용하면 주어진 값만큼 건너 뛰면서 루프를 돌게 된다. Step 2라고 하면 카운터 변수가 2씩 증가하면서 루프가 실행되고 Step -1하면 역방향으로 1씩 감소하면서 루프를 돌게 된다.
For number As Double = 2 To 0 Step -0.25
    Debug.Write(number.ToString & " ")
Next
Debug.WriteLine("")
' Output: 2 1.75 1.5 1.25 1 0.75 0.5 0.25 0

그런데, 평소 가지는 불만중 하나는 For Each구문이다. 이넘은 Step이라는 개념이 없다. 물론 개체를 루핑하는 것인데, 개체를 서수화하는 기준이 없으니 For Each가 할 수 없는 영역이다. 하지만 For Each 대신 Do Loop와 Offset 메서드를 가지고 이런 루프를 만들 수 있다. 다음은 A6~ A22 사이를 반복하면서 짝수 행의 주소만 찍는 것이다.
  Set rngBegin = Sheet1.Range("A6")
  Set rngEnd = Sheet1.Range("A22")
  Set rng = rngBegin
 
  Debug.Print rngBegin.Address
  Debug.Print rngEnd.Address
  step = 2
  Do
    Debug.Print rng.Address
    Set rng = rng.Offset(step, 0)
    If rng.Address = rngEnd.Address Then Exit Do
  Loop

앞서의 주제와 별개의 얘기이지만 예제를 만들면서 If rng.Address = rngEnd.Address Then Exit Do을 처음에는 If rng Is rngEnd Then Exit Do으로 사용하였다. 런타임에러는 없지만 예상과는 달리 rngEnd, 즉 A22에서 종료되지 않는 문제가 있었다.

가만히 생각해보니 If rng Is rngEnd 는 문제가 있다. A22를 둘 다 가리키지만 사실 이 개체변수는 같은 것은 아니다. 개체변수를 선언할 때
  Dim rng As Range
  Dim rngEnd As Range
 이 개체변수들의 메모리 주소는 스택에 있을 거구, 각각 다른 메모리 주소를 가질 것이다. 그러므로 A22라는 같은 값을 가질 수 있지만 , 주소는 달라서 같은 개체일 수는 없을 것이다.

다운로드후 폴더를 만들고 그안에 압축풀기 파이썬/쟁고

분석을 하는 도구로는 주피터 노트북이 가장 애용하는 도구인데, 다음은 압축된 분석 데이터를 다운로드하고 폴더를 만들고 그 안에 압축을 풀어 놓은 예이다.
import os
import tarfile
import urllib.request

DOWNLOAD_ROOT = "https://raw.githubusercontent.com/ageron/handson-ml2/master/"
HOUSING_PATH = os.path.join("datasets", "housing")
HOUSING_URL = DOWNLOAD_ROOT + "datasets/housing/housing.tgz"

def fetch_housing_data(housing_url=HOUSING_URL, housing_path=HOUSING_PATH):
    if not os.path.isdir(housing_path):
        os.makedirs(housing_path)
    tgz_path = os.path.join(housing_path, "housing.tgz")
    urllib.request.urlretrieve(housing_url, tgz_path)
    housing_tgz = tarfile.open(tgz_path)
    housing_tgz.extractall(path=housing_path)
    housing_tgz.close()

fetch_housing_data()
개인적으론 PC에 설치한 주피터 노트북보단 구글 코랩을 선호하는 편이다. 구글 코랩에서 실행하면 다음과 같다.

정확한 폴더위치는 구글드라이브의 /content/폴더 밑에 설치된다.
import pandas as pd

housing=pd.read_csv('/content/datasets/housing/housing.csv')

housing.head()



루프를 없애는 또 하나의 잡설

워크시트에 테이블 형태의 자료를 만드는 경우 , 미리 컬럼명을 만들어둔 템플릿 시트를 사용할 수 있는데, 때론 컬럼명을 동적으로 만들어 생성하는 경우도 있다. 컬럼명을 VBA코드로 출력하는 경우 , 각각의 셀 마다 컬럼명을 일일이 찍어줄 수도 있고, 코드가 너무 많아 스마트하게 배열에 미리 넣어두고 루프를 돌려 출력하기도 한다.

다음은 루프를 사용하지 않고 셀 범위에 여러 개의 값을 출력하는 예인데, 하나의 값을 출력하는 경우, VBA배열을 사용하는 경우, 엑셀 배열리터럴을 사용하는 경우, 두 배열을 혼합하고, 워크시트 함수 TODAY(), NOW()를 사용하는 경우를 보여준다.
' http://timebird.egloos.com/7455699
' Resize로 셀 영역을 확장하고 값을 출력
Dim myStr As String

myStr = "a string from vba"
With Sheet1.Range("D1")
.Offset(0, 1).Resize(1, 2) = "단일값"
.Offset(1, 1).Resize(1, 2) = Array("Price", "Delta")
.Offset(2, 1).Resize(3, 2) = [{"Zip", "22150";"City", "Springfield"; "State", "VA"}]
.Offset(5, 1).Resize(1, 3) = Array([Today()], [Now()], myStr)
End With

루프없애는 게 대세라던데... 오피스/VBA/Office.JS

파이썬이나 함수형 프로그래밍 덕분에 당연히 사용하던 루프를 쓰면 웬지 촌스러운 느낌이 든다. 루프없이 쓴다면 웬지 멋있어 보인다. 얼마 전 셀 영역을 대상으로 조건부 최소값, 조건부 최대값, 조건부 표준편차 등이 필요했었다. 당연히 이를 계산하려면 필터함수 또는 루프를 사용해야 한다. 루프를 사용하지 않으려면 엑셀의 워크시트함수를 이용해야 한다.

엑셀의 워크시트함수중 조건관련함수로는 AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS 등이 있다. 그러나 MINIF, MAXIF와 같은 함수는 아직 없다. 그런데 VBA의 WorksheetFunction개체에는 MinIf, MaxIf함수가 보인다. 그런데 사용해보니 에러가 난다.
흠, 이넘들이 시험적으로 해보았다가, 현재는 함수를 삭제했구만~



그래서 이를 수식으로 만들어 계산하도록 하였다. 다음은 A열의 값을 가져와서 B열에 1000을 곱한 결과를 출력하는 예이다.
Set rng = Sheet1.Range(Sheet1.Range("A6"), Sheet1.Range("A6").End(xlDown))
'// A열 값에 1000을 곱하여 B열에 출력
Sheet1.Range("B6").Resize(rng.Rows.Count, 1) = Sheet1.Evaluate(rng.Address & "*1000")
이때 계산은 Evaluate 함수를 사용한다 ( 참고 : http://timebird.egloos.com/7380115) 여기서 Evaluate 함수는 엑셀의 배열수식연산을 해준다.
간단한 예인데, 보다 실용적인 수식을 소개하자면 다음과 같다.
' 지원하지 않는 함수를 생성가능 - 조건부Min,Max,Stdev
'min = .Evaluate("MIN(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")
'max = .Evaluate("MAX(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")
'std = .Evaluate("STDEV(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")


파일이 없으면 새로 만들고, 있으면 그냥 사용하기 오피스/VBA/Office.JS

엑셀 프로그래밍을 하다보면 외부파일(텍스트이건 바이너리이건)을 다룰 일이 그리 많치 않다. 다만 VBA의 조상님인 VB가 가진 위대한 유산을 물려받아 사용가능하다.
(Open) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/open-statement
(Input) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/inputstatement
(Line Input) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/line-inputstatement
(Get) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/get-statement
(Put) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/put-statement
(Print) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/printstatement
(Write) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/writestatement
(Close) https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/close-statement

오랜만에 텍스트 파일을 열어 작업을 하려는 데, 만일 파일이 없다면 새로 만들지만, 이미 파일이 있다면 오버라이트하지 말고 내용만 추가하려고 한다. 따라서 파일이 있는 지 없는 지 알아보는 절차를 줄여야 한다. 레거시인 Open문은 이게 가능한데, Append, Binary, Output, Random 모드중 하나로 파일을 오픈하면 된다.

하지만 레거시만 있는 것은 아니다. VBA는 외부라이브러리를 사용할 수 있다. 다음은 스크립팅 라이브러리를 이용하여 파일을 만들거나 여는 예이다. 매개변수중 Create를 True로 주면 앞서 말한 레거시 오픈문처럼 파일이 없으면 새로 만들고, 있으면 내용만 추가한다.
Sub fsoOpenTextFile()
'object.OpenTextFile(filename[, iomode[, create[, format]]])
' Arguments
' [...]
' Create
' Optional. Boolean value that indicates whether a new file can be created
' if the specified filename doesn't exist.
' The value is True if a new file is created, False if it isn't created.
' If omitted, a new file isn't created.
' Call OpenTextFile with the 3rd parameter set to True
' in order to create the file if it doesn't exist:


Dim fso, f
Dim fileName As String

fileName = "fsoOpenTextFile.txt"
Set fso = CreateObject("scripting.filesystemobject")
Set f = fso.OpenTextFile(fileName, 8, True)

f.WriteLine "TE+St"

f.Close
Set f = Nothing
Set fso = Nothing
End Sub

참고로 파일의 존재여부를 확인하려면
If Len(Dir(ThisWorkbook.Path & "\" & Date & "_calloptions.csv")) = 0 Then '파일이 없군~블라블라'
If Len(Dir(ThisWorkbook.Path & "\" & Date & "_calloptions.csv")) <> 0 Then '파일이 있네~블라블라'

INDEX(...MATCH...)(2) 오피스/VBA/Office.JS

Index(…Match…)는 의외로 쓸모가 많은 수식이다. 일단 첫 번째 컬럼에서만 검색이 가능한 VLOOKUP()의 단점을 극복하고, 직전 포스팅에서 모든 컬럼의 내용을 검색할 수 있다. 이번에는 검색결과가 여러 개 존재하는 경우 마지막 결과에 해당하는 데이터를 돌려주는 수식이다. 이것은 Oscar Cronquist가 쓴 Index Macth-Last – Last value포스팅을 옮긴 것이다.



위의 그림에서 찾는 값 'BB'에 해당하는 데이터는 네 가지이다(50, 100, 70, 10) 그중에서 가장 마지막에 나오는 '10'을 찾으려는 것이다.

수식의 핵심은 역시 MATCH()함수의 성질을 이용한 것이다.

MATCH(lookup_value, lookup_array, [match_type])


첫 번째 성질은 MATCH() 함수는 검색영역(lookup_array)중 에러부분은 무시한다는 것이다.
= MATCH(2, 1/(B3:B12=E3))
= MATCH(2, 1/({"AA";"BB";"CC";"BB";"DD";"BB";"EE";"GG";"VV";"BB"}="BB"))
= MATCH(2, 1/({FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE}))
= MATCH(2, 1/({0; 1; 0; 1; 0; 1; 0; 0; 0; 1}))
= MATCH(2, {#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1})

위의 식에서 '2'를 찾는 데, 에러 #DIV/0!은 무시한다는 것이다. (B3:B12=E3)의 결과는 비교연산인데, 그 결과는 TRUE 또는 FALSE를 돌려준다. TRUE는 1, FALSE는 0으로 TRUE인 경우 1/1이므로 결과는 1이 된다. FALSE인 경우 1/0으로 그 결과는 에러 #DIV/0!으로 표시된다. 전체적인 결과는 {#DIV/0!; 1; #DIV/0!; 1; #DIV/0!; 1; #DIV/0!; #DIV/0!; #DIV/0!; 1} 배열이 된다.

두 번째 성질은 ‘2’에 해당하는 데이터가 없다면 ‘2’보다 작은 값중 마지막 값의 위치(위의 수식에선 10번째에 위치한 ‘1’)를 돌려준다는 것이다.

INDEX함수는 MATCH함수가 돌려주는 위치값(10)에 따라 10번째 위치한 '10'을 돌려주게 된다.

INDEX(...MATCH...)(1) 오피스/VBA/Office.JS

엑셀이 많은 워크시트 함수를 제공하고, 우리는 VLOOKUP과 같은 함수 하나로 해결하면 좋겠지만 반드시 그리 쉽게 해결하지 못하는 경우가 생긴다. 이런 경우 내가 해결할 수 있는 수준으로 데이터의 구조를 바꾸거나 불가피하게 컬럼을 추가해서 보조적인 정보를 담아 그걸로 해결해야만 한다.

VLOOKUP함수의 경우 매우 편리한 함수이지만, 찾으려는 데이터가 반드시 첫 번째 컬럼에 있어야 한다는 점이 문제이다. 그래서 INDEX(...MATCH...)와 같은 형식으로 수식을 만든다.

다음의 문제는 VLOOKUP이나 기존의 INDEX(...MATCH...)로 해결하기 곤란한 문제이다. 요약하면 구성원을 입력하면 구성원이 어느 그룹소속인지를 표시하는 것이다(다만 같은 이름의 구성원은 아직 해결못한다)


F컬럼의 'C'를 입력하면 'C'는 'Group AC'소속임을 알려준다

다음은 G1에 입력한 수식이다. 수식은 행렬연산을 하고 그 결과는 A열 그룹중 하나를 가리킨다. 그리고 배열수식이므로 입력하고 CTRL+SHIFT+ENTER로 마무리한다

=INDEX(A1:A4,MATCH(1,MMULT(--(B1:D4=F1),TRANSPOSE(COLUMN(B1:D4)^0)),0))

다음은 'C'를 입력할 때 행렬연산에 대한 개요이다.

(B1:D4=F1)의 결과는 TRUE/FALSE 결과를 돌려주는데, --를 붙여 결과를 1과 0으로 바꾼다. 그리고 COLUMN()^0은 값을 모두 1로 바꾼다. 이를 TRANSPOSE()하여 MMULT()를 사용하기 위한 차원으로 바꾼다.

다음은 앞서와 비슷하지만 찾으려는 데이터 위치가 가로방향이다.

E컬럼에 'E'를 입력하면 'E'가 속한 'BEHK'를 돌려준다
=INDEX(A1:C1,1,MATCH(1,MMULT(TRANSPOSE(--(A2:C5=E1)),ROW(A2:C5)^0),0))


나만의 이벤트 정의하기 오피스/VBA/Office.JS

프로그래밍을 하다보면 개체가 제공해주는 이벤트가 불만족스런 경우가 생긴다. 혹시 나만의 이벤트를 만들 수 있지 않을 까… 물론 가능하다. 다음은 Userform의 콤보상자를 바탕으로 여기에 내 이벤트를 만드는 예이다. 콤보상자에 데이터를 입력하면 새로 입려된 데이터는 콤보상자의 리스트로 들어간다. 그런데 이미 입력한 데이터라면 입력을 취소하는 예이다.

[DataComboBox.cls]
Option Explicit

'// define DataComboBox's an event, 'ItemAdded'
Public Event ItemAdded(strValue As String, blnCancel As Boolean)

'// p_cboCombo is a variable that refers to Userform's combobox
Private p_cboCombo As ComboBox

Public Property Set ComboBox(cboCombo As ComboBox)
'// reference to Userform's combobox
If p_cboCombo Is Nothing Then
Set p_cboCombo = cboCombo
End If
End Property

Public Property Get ComboBox() As ComboBox
'// return combox
Set ComboBox = p_cboCombo
End Property

Public Function AddDataItem(strValue As String)
'// blnCancel is a variable that determine
'// whether strValue can be added

Dim blnCancel As Boolean

blnCancel = False

'// Trigger User-defined Event, ItemAdded
RaiseEvent ItemAdded(strValue, blnCancel)

'// if blnCancel is True, strValue not added
If blnCancel = False Then
Me.ComboBox.AddItem strValue
End If
End Function
다음은 Userform코드이다
[frmDataCombo.frm]
Option Explicit

'// declare DataComboBox with its event
Private WithEvents mdcbCombo As DataComboBox

Private Sub cmdAdd_Click()
Dim strValue As String

'// save data of combobox to strValue
strValue = Me.cboData.Text

'// call DataComboBox's AddDataItem method
mdcbCombo.AddDataItem strValue
End Sub

Private Sub UserForm_Initialize()
'// when this form begins, make DataComboBox into instance
Set mdcbCombo = New DataComboBox

'// connect object to frmDataCombo's combobox
Set mdcbCombo.ComboBox = Me.cboData
End Sub

Private Sub mdcbCombo_ItemAdded(strValue As String, blnCancel As Boolean)
Dim lngCount As Long
Dim lngLoop As Long
Dim strItem As String
Dim varItem As Variant

'// count of list
lngCount = mdcbCombo.ComboBox.ListCount

'// while the loop, detemine if new Item already added.
'// if blnCancel is True, new item already exists
For lngLoop = 1 To lngCount
strItem = mdcbCombo.ComboBox.List(lngLoop - 1)
If strItem = strValue Then
blnCancel = True
Exit For
End If
Next
End Sub

사용자를 배려한 한/영 입력 오피스/VBA/Office.JS

옛날 얘기인데, 각종 업무용 프로그램을 액세스로 개발하여 밥먹고 사는 사람이 있었는 데, 개발해놓은 걸 보니 날짜입력을 그냥 텍스트박스로 처리해두고 있었다. Calendar컨트롤을 사용하면 입력도 편리하고 제대로 일자 포맷에 맞게 입력되었는 지 체크하는 수고도 덜 수 있을 텐데 그리 하지 않았다.

이유를 물어보니 , 본인도 그런 점을 알지만 다년 간의 경험상 그런 컨트롤을 사용하다보면 프로그램도 무거워지고(액세스는 디비를 같이 담고 있으니 더욱 그럴 만하다) 결정적으로 불안정해져 실행중 프로그램이 죽는 경우가 있다고 한다. 잔뜩 입력하다가 프로그램이 죽는 것보단 불편한게 나을 것이다. 게다가 달력컨트롤이 기본이 아니어서 PC마다 없는 경우가 생길 수 있다. '가지 많은 나무에 바람 잘 날 없다'는 속담이 통하는 대목이다.

입력중 불편한 사항중 하나는 화면을 안보고 입력하다보니, IME가 한글이 아니라 영문상태 또는 그 반대의 경우이다. 한글입력이 필요하거나 영문입력이 필요한 시점에서 IME가 자동으로 입력모드를 변경해주는 배려를 해줄 수 있다. 다음은 API를 사용하여 입력모드를 바꾸는 예이다.
Private Declare Function ImmGetContext _
Lib "imm32.dll" (ByVal hwnd As Long) As Long
Private Declare Function ImmSetConversionStatus _
Lib "imm32.dll" (ByVal himc As Long, _
ByVal dw1 As Long, ByVal dw2 As Long) As Long
Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Const IME_CMODE_NATIVE = &H1
Private Const IME_CMODE_HANGEUL = IME_CMODE_NATIVE
Private Const IME_CMODE_ALPHANUMERIC = &H0
Private Const IME_SMODE_NONE = &H0

Private Function GetAppHandle() As Long
Dim dVersion As Double

dVersion = Application.Version

If dVersion < 10 Then
GetAppHandle = FindWindow("XLMAIN", _
Application.Caption)
Else
GetAppHandle = Application.hwnd
End If
End Function

Private Sub demoChangeIME()
Dim hWndIME As Long
Dim hWndApp As Long

'// 엑셀의 핸들을 구함
hWndApp = GetAppHandle

'// 입력모드의 핸들을 구한다
hWndIME = ImmGetContext(hWndApp)

'// 영어 모드로 변환
Call ImmSetConversionStatus(hWndIME, _
IME_CMODE_ALPHANUMERIC, IME_SMODE_NONE)
InputBox "English 입력모드"

'// 한글 모드로 변환
Call ImmSetConversionStatus(hWndIME, _
IME_CMODE_HANGEUL, IME_SMODE_NONE)
InputBox "한국어 입력모드"

End Sub

Collection의 Key는 어디서 찾아야 하나? 오피스/VBA/Office.JS

이런 경우 VBA는 한심한 언어이다. 무슨 얘기인가 하면 Collection개체의 Add메서드를 사용할 때 딕셔너리처럼 {Key, Item}와 같이 Key를 같이 입력할 수 있다. 물론 Key는 유일해야 하므로 중복된 Key값은 받아주질 않는다.
그런데 Key를 입력하고 나면 Key값을 알 수 있는 방법이 없다. 도대체 Key를 만들어 입력해서 Key를 갖고 Item을 찾을 수 있게 해주면 뭐하나? Key을 돌려주는 프로퍼티나 메서드가 없는데…
이런 문제점에 대한 대안으로 Item에 Key를 포함해서 넣어주는 방법으로 해결한다. 즉 프로그래밍언어 차원이 아닌 개발방법으로서 몸으로 때우는 것이다.
Dim col As New Collection

col.Add Array("key#1", "first string"), "key#1"
col.Add Array("key#2", "second string"), "key#2"
col.Add Array("key#3", "third string"), "key#3"

'// print item's value in the collection
Dim item As Variant

For Each item In col
Debug.Print item(1)
Next

'// print item's key in the collection
Dim key As Variant

For Each key In col
Debug.Print key(0)
Next
위의 코드에서 Key#1, Key#2, Key#3는 종전처럼 입력하지만 이와 동시에 아이템 선두에 키값을 한번 더 입력하고 아이템 조회시 0번 인덱스를 Key로 삼아 사용하려는 것이다.

이런 VBA를 계속 쓰는 오피스개발자로서 눈물이 앞을 가린다.

[관련 포스팅]: VBA Collection

VBA로 만드는 알고리즘-리스트 오피스/VBA/Office.JS

데이터를 다룰 때 가장 많이 사용하는 것이 배열과 리스트인데, 오늘은 리스트 관련한 얘기를 하려고 한다. 리스트는 가장 인기있는 데이터구조인데, 배열과 달리 미리 크기가 정해져 있지 않고, 필요에 따라 데이터를 추가/삭제가 가능한 기능을 가지고 있다. VBA는 컬렉션(Collecton)개체라는 리스트를 제공하는 데(제공하는 것만도 감지덕지 하다), 그외 외부참조를 통해 딕셔너리나 닷넷의 ArrayList들이 사용가능하다.
프로그래밍하면서 가장 편리하다고 생각하는 것이 컬렉션개체인데, 역설적으로 기능이 정말 단촐해서 맘에 든다. 기능은 많은 데, 자주 사용하지 않는 것보단 단순하게 배우기 쉽고 활용도 좋다. 그렇지만 사람 욕심이라는 게, 내가 쓸 연장은 내가 만들고 싶은 욕구라는 게 있다.
다음은 별 기능은 없는 리스트 클래스이다. 내부구조를 보면 어차피 배열인데, 다만 배열보다 사용하기 편리한 메서드를 가지고 있다. 사실 언제 만들었는 지 모르겠다. 내가 만든 게 맞나 싶기도...(나의 기억은 조작되었을 지도...)
[CList.cls]
Option Explicit
Option Base 1

'// 사용자 정의 에러
'// 리스트의 상하한을 넘어 액세스하려는 경우 에러
Const ERR_LIST_BEYOND_BOUNDS As Long = 999

'// 리스트가 초기화되지 않은 상태에서 리스트를 사용하려는 경우 에러
Const ERR_LIST_NOT_INITIALIZED As Long = 998

Private Item_Data() As Variant
Private Item_Pointer As Long
Private Item_Init As Boolean

Sub Add(vNewValue As Variant)
Item_Pointer = Item_Pointer + 1
If Item_Init Then
ReDim Preserve Item_Data(Item_Pointer)
Else
ReDim Item_Data(Item_Pointer)
Item_Init = True
End If
Item_Data(Item_Pointer) = vNewValue
End Sub

Sub Remove(Index As Long)
Dim Imsi_Data() As Variant
Dim i As Long
Dim j As Long

If Item_Init Then
ReDim Imsi_Data(Item_Pointer - 1)
j = 1
For i = 1 To UBound(Item_Data)
If i <> Index Then
Imsi_Data(j) = Item_Data(i)
j = j + 1
End If
Next

Item_Pointer = Item_Pointer - 1
ReDim Item_Data(1 To Item_Pointer)

For i = 1 To UBound(Imsi_Data)
Item_Data(i) = Imsi_Data(i)
Next
End If
End Sub

Function Count() As Long
Count = Item_Pointer
End Function

Function Item(Index As Long) As Variant
If Item_Init Then
If Index < 1 Or Index > Item_Pointer Then
Err.Raise ERR_LIST_BEYOND_BOUNDS, "CList", "범위를 벗어났습니다"
Else
Item = Item_Data(Index)
End If
Else
Err.Raise ERR_LIST_NOT_INITIALIZED, "CList", "List()가 초기화되지 않았습니다"
End If
End Function

Private Sub Class_Initialize()
Item_Pointer = 0
Item_Init = False
Erase Item_Data()
End Sub

Private Sub Class_Terminate()
Erase Item_Data()
End Sub

다음은 위의 클래스모듈을 사용한 예이다.
Option Explicit

Sub demo_CListClass()
Dim lstDemo As New CList

'// 6개의 데이터를 입력한다
lstDemo.Add "Tumin"
lstDemo.Add "Lenski"
lstDemo.Add "Davis"
lstDemo.Add "Moore"
lstDemo.Add "Ogburn"
lstDemo.Add "Simmel"

'// 2번째 입력한 데이터를 삭제한다
lstDemo.Remove 2

Dim i As Long

'// 리스트의 처음부터 끝까지 보여준다
For i = 1 To lstDemo.Count
Debug.Print lstDemo.Item(i)
Next

Set lstDemo = Nothing
End Sub


[관련포스팅]없으면 빌려쓰는 ArrayList

구글앱스크립트로 실시간 데이터 업데이트하기 오피스/VBA/Office.JS

이번에는 구글 앱스크립트로 실시간 페이지를 업데이트하는 예를 소개하고자 하는 데, 직접 만든 건 아니고(아직 그럴 짠밥이 못된다) 깃헙(Github)에 좋은 프로젝트가 하나 있어 소개한다.
이 프로젝트는 일정간격으로 TheSimpsons Quote API(심슨이 들려주는 격언, https://thesimpsonsquoteapi.glitch.me/)에서 격언과 이미지를 가져오는 것이다. 그 결과물은 다음과 같다.

깃허브에 가면 소스코드를 볼 수 있는 데, 프로젝트는 매우 단촐하다. 서버사이드 코드 Code.gs와 클라이언트 사이드 Index.html(자바스크립트 포함)가 전부이다(이런 간단명료한 거 좋아한다) 클라이언트 사이드 소스코드에서
  • google.script.run.withSuccessHandler는 비동기 클라이언트 사이드 API인데, 서버 사이드 함수를 사용가능하게 해준다.
  • setInterval()함수는 업데이트 주기를 설정하는 클라이언트 사이드 API이다.

  • 그런데 직접 코드를 가져와 몇 번 돌려보고, 저자의 데모를 돌려보니 심슨격언을 가져오지 못한다. 이는 구글웹앱에 많은 HTTP 리퀘스트를 날리기 때문이다. 무료의 한계이다. (참고: Quotas for Google Services, https://developers.google.com/apps-script/guides/services/quotas)

    [Code.gs 서버사이드]
    function doGet(e) {
    return HtmlService.createHtmlOutputFromFile('Index').setTitle('Realtime Data');
    }

    function randomQuotes() {
    var baseURL = 'https://thesimpsonsquoteapi.glitch.me/quotes';
    var quotesData = UrlFetchApp.fetch(baseURL, { muteHttpExceptions: true });
    var quote;
    var imageURL;
    if (quotesData.getResponseCode() == 200 || quotesData.getResponseCode() == 201) {
    var response = quotesData.getContentText();
    var data = JSON.parse(response)[0];
    quote = data["quote"];
    imageURL = data["image"];
    } else {
    quote = 'Random Quote Generator is broken!';
    imageURL = 'https://cdn.shopify.com/s/files/1/1061/1924/products/Sad_Face_Emoji_large.png?v=1480481055';
    }
    var randomQuote = {
    "quote": quote,
    "imageTag": '<img class="responsive-img" src="' + imageURL + '">'
    }
    return randomQuote;
    }

    function getTime() {
    var now = new Date();
    return now;
    }

    [Index.html내 자바스크립트 클라이언트 사이드]
    <script>
    // 격언 업데이트
    function onSuccess1(quotedata) {
    var quoteactual = document.getElementById('quote');
    quoteactual.innerhtml = quotedata.quote;
    var quoteimg = document.getElementById('quoteImage');
    quoteimg.innerhtml = quotedata.imagetag;
    }

    setInterval(function() {
    console.log("getting quote...")
    google.script.run.withSuccessHandler(onsuccess1).randomQuotes();
    }, 10000);
    </script>

    <script>
    // 시간 업데이트
    function onSuccess2(now) {
    var div = document.getElementById('time');
    var today = new Date();
    var time = today.getHours() + " : " + today.getMinutes() + " : " + today.getSeconds();
    div.innerhtml = time;
    }

    setInterval(function() {
    console.log("getting time...")
    google.script.run.withSuccessHandler(onsuccess2).getTime();
    }, 1000);
    </script>

    VBA로 만드는 알고리즘-큐 오피스/VBA/Office.JS

    큐는 선입선출형 알고리즘이다. 즉 먼저 들어온 넘이 먼저 나가는 구조이다. 극장이든, 식당이든, 코스트코이든 줄을 서서 기다리다 순서대로 입장하는 구조이다.
    _
    매일 우리가 만나는 큐는 키보드나 프로그램큐이다. 예전에 컴퓨터성능이 거지같았을 때 급한 마음에 입력한 입력한 글자들이 나중에 한꺼번에 입력되거나, 이것저것 클릭한 프로그램들이 당장 안 보이고 대충 실행한 순서대로 화면에 뜨는 기적을 보았을 것이다. 사용자의 입력을 기억해두는 일을 하는 것이 큐이다. 키보드에 글자를 입력하면 키값이 큐에 저장되고 윈도는 여유있을 때마다 키값을 읽어와 화면에 렌더링하는 것이다.
    _
    보통 큐에 데이터를 입력하는 것을 'Enqueue'라고 하고, 데이터를 꺼내는 것을 'Dequeue'라고 한다. 다음은 VBA로 만들어 본 큐이다. 두 개의 클래스모듈을 사용하는 데, 하나는 큐에 들어갈 데이터를 정의한 클래스 QueueItem, 나머지 하나는 큐를 구현한 Queue클래스이다.
    [QueueItem.cls]
    Public nItem As QueueItem
    Public Value As Variant

    Private Sub Class_Initialize()
    Set nItem = Nothing
    End Sub

    Private Sub Class_Terminate()
    Set nItem = Nothing
    End Sub
    [Queue.cls]
    '// 큐의 머리와 꼬리를 가리키는 멤버변수 선언
    Dim qFront As QueueItem
    Dim qRear As QueueItem

    Public Sub Enqueue(newItem As Variant)
    '// 큐에 입력하는 메서드
    Dim qNew As New QueueItem

    qNew.Value = newItem

    '// 큐가 비어있다면 qNew는 '머리=꼬리'이고
    '// 비어 있지 않다면 이전 꼬리에 qNew를 입력하고
    '// qRear에 qNew를 지정한다
    If IsEmpty Then
    Set qFront = qNew
    Set qRear = qNew
    Else
    Set qRear.nItem = qNew
    Set qRear = qNew
    End If
    End Sub

    Public Function Dequeue() As Variant
    '// 먼저 큐가 비어있는 지 확인하고
    If IsEmpty Then
    Dequeue = Null
    Else '// 비어 있지 않다면 일단 큐의 머리값을 꺼낸다
    Dequeue = qFront.Value

    '// 그러나 큐에 데이터가 1개여서
    '// 머리=꼬리인 상태라면 앞서 데이터를 꺼낸 상황이라
    '// 이제 큐가 빈 상태가 된다.
    '// 그래서 머리,꼬리는 Nothing으로 만든다
    If qFront Is qRear Then
    Set qFront = Nothing
    Set qRear = Nothing
    Else '// 그러나 큐에 데이터가 1개 이상이어서
    '// 머리 != 꼬리인 상황이라면
    '// 머리의 다음 데이터가 머리가 된다
    Set qFront = qFront.pItem
    End If
    End If
    End Function

    Property Get IsEmpty() As Boolean
    '// 머리와 꼬리가 모두 Nothing 이면
    '// 비어 있다는 의미이다
    IsEmpty = ((qFront Is Nothing) And (qRear Is Nothing))
    End Property

    Property Get Peek() As Variant
    '// Dequeue를 하지 않고 머리 값을 얻는다
    If Not IsEmpty Then Peek = qFront.Value
    End Property

    Private Sub Class_Initialize()
    Set qFront = Nothing
    Set qRear = Nothing
    End Sub

    Private Sub Class_Terminate()
    Set qFront = Nothing
    Set qRear = Nothing
    End Sub

    다음은 큐를 사용하는 데모이다.
    [QueueTest]
    Dim myQueue As New Queue

    With myQueue
    .Enqueue "A queue is "
    .Enqueue "a useful data structure "
    .Enqueue "in programming. "
    .Enqueue "It is similar to the ticket queue "
    .Enqueue "outside a cinema hall, "
    .Enqueue "where the first person "
    .Enqueue "entering the queue "
    .Enqueue "is the first person "
    .Enqueue "who gets the ticket."

    Debug.Print ">> Get the value of the front of queue :", .Peek

    Do While Not .IsEmpty
    Debug.Print .Dequeue()
    Loop

    Debug.Print ">> Is myQueue empty?", .IsEmpty
    End With

    신입직원 인사기록을 위한 작은 예제 오피스/VBA/Office.JS

    직원을 채용하면 인사담당자는 신입직원에 대한 기록을 만들게 된다. 이번에는 구글앱스크립트와 구글시트를 이용하여 이름, 직위, 아이디(#사번)를 워크시트에 입력하는 앱스크립트이다. 사실 이건 전혀 실무에 도움이 되지 않는 아주 초보적인 수준으로 학습을 위한 용도이다(그냥 시트에 손으로 입력하는 게 더 낫다)

    function addEmployee() {

    var ui = SpreadsheetApp.getUi();
    var ss = SpreadsheetApp.getActiveSpreadsheet();

    ss.setActiveSheet(ss.getSheetByName("Employee"));

    var answer1 = ui.prompt(
    '신규직원추가',
    '이름을 입력하세요',
    ui.ButtonSet.OK);
    var btnEmployeeName = answer1.getSelectedButton();
    var txtEmployeeName = answer1.getResponseText();

    if (btnEmployeeName == ui.Button.OK) {
    var answer2 = ui.prompt(
    '직책을 입력하세요',
    '직책:',
    ui.ButtonSet.OK_CANCEL);
    var btnEmployeePosition = answer2.getSelectedButton();
    var txtEmployeePosition = answer2.getResponseText();

    if (btnEmployeePosition == ui.Button.OK) {
    var answer3 = ui.prompt(
    '사번을 입력하세요',
    '사번(ID#):',
    ui.ButtonSet.OK_CANCEL);
    var btnEmployeeId = answer3.getSelectedButton();
    var txtEmployeeId = answer3.getResponseText();
    }

    if (btnEmployeeId == ui.Button.OK) {
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.appendRow([txtEmployeeName, txtEmployeePosition, txtEmployeeId]);

    } else if (btnEmployeeId == ui.Button.CANCEL) {
    ui.alert('작업 취소');
    }

    } else if (btnEmployeePosition == ui.Button.CANCEL) {
    ui.alert('작업 취소');

    } else if (btnEmployeeName == ui.Button.CLOSE) {
    ui.alert('작업 취소');
    }
    }

    VBA로 만드는 알고리즘-스택 오피스/VBA/Office.JS

    토지라는 물건을 하나 두고, 상업용,공업용,주거용,농업용 토지 등 우리는 여러 가지로 구분하여 사용하듯이, '메모리'라는 공간을 두고 필요에 따라 여러 알고리즘으로 활용한다. 스택,큐,리스트(링크드),트리 등등. 현대의 프로그래밍언어는 이런 알고리즘을 아예 지원을 해주지만, 예전에는 직접 만들어 사용하였다. 그래서 이런 알고리즘은 학교에서 배우거나 프로그래머 면접용으로 사용되거나 한다. 이런 알고리즘을 직접 만들 필요는 많이 줄었지만, 기초에 해당하는 것이라 알아둘 필요는 있어 보인다.

    스택(Stack)은 끝이 막혀있는 구조로 나중에 입력(push)된 항목이 먼저 나오는(pop) 후입선출형 알고리즘이다. 데이터를 입력하는 것을 PUSH라고 하고, 데이터를 꺼내는 것을 POP이라고 한다. POP을 하면 해당 항목은 스택에서 제거된다. 위의 그림에 '1'을 PUSH하고 , 다음에 '2'를 PUSH한다. 이제 POP을 하면 '2'가 스택에서 나오고 '1'만 남는다.

    프로그래밍을 하면서 만나는 스택은 재귀적 호출 함수를 만드는 경우이다. 함수가 자신을 다시 호출하는 구조인데, 호출할때마자 메모리주소를 스택에 쌓아두었다가, 연산이 끝나면 스택에 저장된 함수의 주소를 메모리에서 풀어주게 된다. 즉 마지막에 입력된 함수주소가 먼저 꺼내지는 것이다.


    VBA에서 스택을 구현하려면 클래스모듈을 사용하는 데, 이번 예에서는 두 개의 클래스를 만든다. 하나는 스택에 들어갈 항목을 정의하는 StackItem클래스이고, 나머지 하나는 스택의 메서드 PUSH, POP을 구현한 Stack클래스이다.
    [StackItem.cls]
    Public Value As Variant '// 항목을 저장할 멤버변수
    Public pItem As StackItem '// 이전항목을 위한 변수

    Private Sub Class_Initialize()
    '// 클래스 생성자로서 처음에 빈 스택이므로 Nothing이다
    '// PUSH를 할때 실행된다
    Set pItem = Nothing
    End Sub

    Private Sub Class_Terminate()
    '// 클래스 소멸자, POP을 할 때 실행된다
    Set pItem = Nothing
    End Sub
    다음은 본격적인 스택클래스이다
    [Stack.cls]
    Dim Top As StackItem '// 마지막 입력된 항목을 가리키는 StackItem 이다.

    Public Function Pop() As Variant
    '// 스택에서 마지막 입력된 항목을 꺼내는 메서드함수이다
    If Not IsEmpty Then
    '// 스택이 비어 있지 않다면 마지막 항목을 돌려준다
    '// 그리고 다음항목을 Top으로 지정한다
    Pop = Top.Value
    Set Top = Top.pItem
    End If
    End Function

    Public Sub Push(ByVal nItem As Variant)
    '// 스택에 데이터를 입력하는 PUSH 메서드이다.
    Dim NewTop As New StackItem

    NewTop.Value = nItem
    '// 기존 TOP을 예전항목으로 돌리고, 새 항목을
    '// TOP으로 설정한다
    Set NewTop.pItem = Top
    Set Top = NewTop
    End Sub

    '// 클래스모듈에서만 작성하는 프로퍼티 메서드이다
    '// 멤버변수값을 돌려주는 프로퍼티 메서드는 Get이고
    '// 멤버변수값을 설정하는 프로퍼티 메서드는 Set이다.
    Property Get GetTop() As Variant
    '// TOP을 돌려준다
    If IsEmpty Then
    GetTop = Null
    Else
    GetTop = Top.Value
    End If
    End Property

    Property Get IsEmpty() As Boolean
    '// 스택이 비어 있는 지 여부를 알려준다
    IsEmpty = (Top Is Nothing)
    End Property

    Private Sub Class_Initialize()
    Set Top = Nothing
    End Sub

    Private Sub Class_Terminate()
    Set Top = Nothing
    End Sub
    다음에는 이를 사용하는 데모이다.

    A stack is a useful data structure in programming. It is just like a pile of plates kept on top of each other.
        Dim myStack As New Stack

    myStack.Push "kept on top of each other."
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "a pile of plates"
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "It is just like"
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "in programming."
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "a useful data structure"
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "is"
    Debug.Print "Current Top Item :", myStack.GetTop

    myStack.Push "A stack"
    Debug.Print "Current Top Item :", myStack.GetTop

    Debug.Print
    Debug.Print "--------------------------------------"
    Debug.Print "It's time to pop out"
    Do While Not myStack.IsEmpty
    Debug.Print "Item just popped :", myStack.Pop()
    Debug.Print "Current Top Item :", myStack.GetTop
    Debug.Print
    Loop

    환율, 구글시트 그리고 앱스크립트 오피스/VBA/Office.JS

    사람이 아닌 소프트웨어를 이용하여 각종 정보를 가져오는 방법중 가장 쉬운 방법은 구글시트 함수를 이용하는 것이다. 그리고 이렇게 얻어온 정보를 단순히 시트를 열어보는 것으로 끝나는 게 아니라, 일정 간격으로 메일로 보내주는 작업까지 생각해볼 수 있다.

    이번 예에서는 구글시트에서 importxml(url, xpath_query)함수를 사용하여 네이버 환율정보를 가져오는 문서를 구성하고 정기적으로 메일로 전송하는 것이다.

    첫 번째 단계는 환율정보를 가져오는 시트를 구성하는 것인데, importxml()를 사용한다. importxml()에 넘겨줄 정보는 url과 xpath_query이다. 네이버 환율정보를 가져올 url과 xpath는 다음과 같다.
    가령 미국달러환율이라면 'http://finance~FX_' 뒤에 'USDKRW'를 붙이면 된다.

    http://finance.naver.com/marketindex/exchangeDetail.nhn?marketindexCd=FX_USDKRW

    그리고 xpath는 다음과 같다.

    '//*[@id="content"]/div[2]/table[1]/tbody/tr/td[1]'


    이 정보는 크롬브라우저나 파이어폭스브라우저의 개발자도구에서 알아낼 수 있다. 이렇게 간단하게 시트를 구성하면 열어볼때 마다 최근 환율을 가져오게 된다.

    두 번째 단계는 시트의 환율정보를 읽어와서 메일로 보내주는 것이다. 시트의 메뉴 [Tools]-[<>Script editor]를 클릭하여 다음과 같은 앱스크립트를 작성할 것이다.
    function myFunction() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('FX');
    var rng = sheet.getRange(6, 1, sheet.getLastRow(), 2).getValues();
    var date_today = new Date();

    var email_body = "날짜: "
    + Utilities.formatDate(date_today, Session.getScriptTimeZone(), "yyyy-MM-dd");

    for(var r=0;r<rng.length;r++){
    if(rng[r][0]!=='')
    email_body = email_body + '<br>'+rng[r][0]+' : '+ rng[r][1];
    }

    var email_to = "your mail@address";
    var email_subject = "NAVER Finance 환율";
    MailApp.sendEmail({
    to: email_to,
    subject: email_subject,
    htmlBody: email_body,
    });
    }
    세 번째 단계는 정기적으로 메일을 보낼 수 있도록 이벤트를 만든다. [Edit]-[Current project's triggers] 또는 도구모음의 아이콘을 클릭하여 이벤트 트리거를 만든다. 즉 예약작업을 걸어 두 번째 단계의 코드를 정기적으로 실행하는 것이다.


    Google Apps Script와 OfficeJs 오피스/VBA/Office.JS



    Google Apps Script라는 물건이 있는 데, 게을러서 북마크만 해두고 있다가 묵은지마냥 꺼내본다. 예전에는 단순한 VBA 대용품 정도로만 생각했는데, G Suite에 속한 제품을 위한 강력한 업무애플리케이션 개발환경이다.
    다음은 Google Apps Script 예이다.
    여기서도 Hello, World는 계속된다.
    /**
    * Creates a Google Doc and sends an email to the current user with a link to the doc.
    */
    function createAndSendDocument() {
    // Create a new Google Doc named 'Hello, world!'
    var doc = DocumentApp.create('Hello, world!');

    // Access the body of the document, then add a paragraph.
    doc.getBody().appendParagraph('This document was created by Google Apps Script.');

    // Get the URL of the document.
    var url = doc.getUrl();

    // Get the email address of the active user - that's you.
    var email = Session.getActiveUser().getEmail();

    // Get the name of the document to use as an email subject line.
    var subject = doc.getName();

    // Append a new string to the "url" variable to use as an email body.
    var body = 'Link to your doc: ' + url;

    // Send yourself an email with a link to the document.
    GmailApp.sendEmail(email, subject, body);
    }
    G Suite에 해당하는 것이 MS-Office365인데, MS의 현금상자인 만큼 MS도 머신러닝을 이용한 기능을 추가하는 등 많은 공을 들이고 있다. 그런데 Google Apps Script에 해당하는 업무애플리케이션 개발환경은 VBA가 아니다. OfficeJS API라는 물건인데, 이것 역시 자바스크립트 기반이다.



    호기심으로 몇 개의 토이수준 OfficeJS 앱을 만들어 보았는 데, 하다보면 자괴감이 든다. 'VBA라는 편리한 물건을 놔두고 왜 이 짓을 하는 거지?' 복잡한 개발환경 셋팅을 마치고 고인물 VBE 같은 전문개발툴도 없어 vscode와 같은 텍스트에디터로 작업을 한다. VBA로 할 수 있는 작업을 자바스크립트로 만드는 게 의미가 있나 싶었다(기껏 제공하는 게 'Script Lab'정도이다)

    '중앙집중적인 배포'라는 대의명분외에는 아무 장점이 안보인다. 그냥 자바스크립트를 이용한 웹개발을 오피스용으로 돌려놓은 것이다. OfficeJS에서 앵귤러,리액트,뷰,제이쿼리등을 사용할 수 있는 걸 보면, 전혀 새로운 것이 아닌 웹개발을 위한 자원을 오피스개발로 돌려놓은 것임을 알 수 있다. 웹보다 짠밥이 높은 오피스에 대세인 웹개발을 갖다 붙인 것이다. 덕분에 웹용으로 개발된 여러 소스를 응용하여 사용할 수 있을 것으로 보인다. 가령 웹크롤링으로 데이터를 가져오는 등...

    Google Apps Script은 MS의 OfficeJS보단 나아 보인다. 구글답게 공돌이스런 미적감각(비하아님)을 가진 개발환경(script.google.com) 을 제공해준다. 이것 역시 구글오피스의 제품을 커스터마이징하여 비지니스 애플리케이션으로 이용하는 게 주된 목적이다. 그런데 오피스제품말고 웹사이트도 만들 수 있다. 특히 sites.google.com에 붙이면 더욱 그럴 듯하다. 별도의 웹서버를 장만할 필요없이 웹프로그램을 돌릴 수 있다. 웹프로그래밍을 위해 호스팅등을 사용할 필요가 없어 보인다(쟁고django로 웹프로그램 만들려다가 웹서버세팅에 진을 빼버린 걸 생각하면 이건 신세계이다. 오로지 프로그래밍만 신경쓰면 되는 것이니...)


    Google Apps Script와 OfficeJs간의 몇 가지 공통점을 나름 느낀대로 써보았는데, 가장 큰 공통점은 둘다 그닥 인기가 없어 보인다. RPA 시대에 나름 중요한 업무용 앱 개발수단으로 보이는데, 많은 전문개발자들의 관심을 받지 못하는 듯 하다. 이럴 바엔 MS는 오피스 스크립팅 언어로 파이썬이나 돌아가게 해주는 게 나아 보이는 데..아니면 쓸데없는 OfficeJs말고 일렉트론같은 라이브러리를 이용한 스탠드얼론 제품을 만들 수 있는 자바스크립팅을 해주는 게 나을 것 같다.

    망해라 django, 아니지 흥해라 django 파이썬/쟁고

    프로그래밍이 생업이 아니다보니까 뭘 새로 해볼려면 항상 초보자이다. django를 새로 셋팅하려는 데, 까먹은 게 넘 많다. 설치는 그나마 쉽다. 각종 웹서버 설정이 골치 아프다. 개발서버는 그나마 나은데, 실제 서버에서 하면 부닺치는 문제가 많다.

    병아리반 학생이 중딩반가서 고생하는 꼴이다. 이러다 보니 django말고 flask, fastapi 등을 해볼까 하는 생각도 든다. 경험상 안된다고 돌아가려고 해봐야 좋을 꼴을 본 적이 없는 것 같다. 희망사항이지만 워드프레스와 같은 CMS에 필요한 앱을 생성하고 코딩하는 호스팅이 어디 있을 것 같은데... 그래서 장고 호스팅 업체를 검색해보았다.
    Top 6 Django Compatible Hosting Services를 읽어보니 디지털오션이 괜찮아 보인다. 다음 번에 이짓을 또한다면 여기로 해봐야 겠다.

    호스팅은 나중에 하고 어드민 화면의 CSS를 못읽어들이는 문제가 발생하였다. 일단 다음 파일에서 static에 대한 alias와 디렉토리를 만든다
    [/etc/httpd/conf.d/vhost.conf]
    <VirtualHost *:80>
    WSGIScriptAlias / /opt/rpa/rpa/wsgi.py
    WSGIDaemonProcess rpa python-path=/opt/rpa/rpa
    <Directory /opt/rpa/rpa>
    <Files wsgi.py>
    #Order allow, deny
    #Allow from all
    Require all granted
    </Files>
    </Directory>
    Alias /static/ /opt/rpa/static/
    <Directory /opt/rpa/static>
    #Order allow,deny
    #Allow from all
    Require all granted
    </Directory>

    </VirtualHost>

    [etc/httpd/conf/httpd.conf]
    Alias /static/ /opt/rpa/static

    [settings.py]
    ...
    ALLOWED_HOSTS = ['*']
    ...
    STATIC_ROOT = os.path.join(BASE_DIR, "static")

    [wsgi.py]
    import os
    import sys

    from django.core.wsgi import get_wsgi_application

    path='/opt/rpa'
    if path not in sys.path:
    sys.path.append(path)

    os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'rpa.settings')
    application = get_wsgi_application()
    그리고 settings.py에서 STATIC_ROOT 설정하고 , manage.py collectstatic를 실행한다. 자세한 내용은 How to setup static files in Django에서 볼 수 있다.

    기타 실서버에서 설정하다보니 걸리는 몇 가지 애로사항은 슈퍼유저를 만들려니 sqlite디비 권한문제가 있다. attempt to write a readonly database, unable to open database file같은 에러 메시지를 만나기도 한다. 파일이나 폴더에 대한 권한 문제인데, 사용자 apache에게 권한을 부여해야 한다.
    chown apache:apache /opt/rpa
    chown apache:apache /opt/rpa/db.sqlite3


    웹 개발이 대세인 시대라 어쩔 수 없이 내가 필요해서 하지만, 데스크탑 앱 개발이 훨씬 좋았다.

    ndarray의 head, tail 구하기 파이썬/쟁고


    데이터프레임을 사용하는 경우 데이터의 앞뒤를 head와 tail을 사용하여 구하는 데, ndarray타입인 경우 head, tail이 따로 없다. 이런 경우 슬라이싱 연산자를 사용한다.
    import numpy as np

    # 데이터 머리부터 두 개의 데이터를 가져온다
    head = np.array((1,2,3,4,5))[:2]

    # 중간 3,4번째 데이터를 가져온다
    body = np.array((1,2,3,4,5))[2:4]

    # 꼬리에서 두 개의 데이터를 가져온다
    tail = np.array((1,2,3,4,5))[-2:]
    print(head)
    print(body)
    print(tail)

    # 슬라이싱 말고 아래와 같이 제한적이긴 하지만 일부 데이터를 가져 오는 데
    # 아래의 *는 나머지를 의미한다
    head, body, *tail = np.array((1,2,3,4,5))
    print(head)
    print(body)
    print(tail)


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