※ Elliott Pattern Helper Add In
지난 글에서는 단순한 캔들 차트를 출력해 보았습니다. 그림 1이 그것인데요, '단순'한 차트지만 수작업이 아닌 VBA 프로그래밍으로 구현한 것입니다.
 |
| 그림 1. 캔들 차트 |
그런데, 작은 차트 속에 캔들이 너무 많이 있어 보기가 쉽지 않습니다. 캔들 수를 줄여서 보기 쉽게 하는 대신 스크롤(scroll) 기능을 구현해서 전체 데이터를 볼 수 있도록 한다면 좋지 않을까요?
이 글의 주제는 '스크롤을 할 수 있는 엑셀 차트'입니다. 우선, 엑셀이 제공하는 스크롤 바 콘트롤의 원리를 보겠습니다.
새 시트를 생성한 후 '개발 도구' → '삽입' → '스크롤 막대'를 선택하고 적당한 크기로 스크롤 바를 만듭니다(그림 2, 3).
 |
| 그림 2. 스크롤 바 추가 |
 |
| 그림 3. 컨트롤 서식 |
생성한 스크롤 바의 Context Menu → '컨트롤 서식'을 선택합니다.
그림 5에서 볼 수 있듯이 스크롤 바는 속성 중의 하나로 '연결 셀(LinkedCell)'을 지정해야 합니다. H1셀로 지정합니다.'현재값', '최소값', '최대값', '증분 변경' 등의 속성은 기본값으로 둡니다.
셀 A2에서 F2까지 적당히 입력한 후, 셀 A3에 다음 그림과 같이 수식을 입력합니다.
OFFSET('기아차-5분'!A2,Sheet6!H1,0)은 '기아차-5분' 시트의 셀 A2에서 아래로 'Sheet6!H1' 값만큼 이동한 셀의 값입니다. 위 수식은 셀 A3에 OFFSET('기아차-5분'!A2,Sheet6!H1,0)의 값이 null string이 아닐 경우, 그 값으로 채우는 것입니다.
이와 유사하게 셀 B3에서 F3까지 아래 수식으로 채웁니다.
범위 (A3:F3)를 선택하고 자동채우기(AutoFill) 기능을 사용하여 10줄 정도를 채운 뒤 다음 그림과 같이 OFFSET 함수의 두 번째 인자가 H1이 되도록 합니다.
모두 채워 넣었으면 스크롤 바를 움직여볼까요?
그림 7과 같이 스크롤을 함에 따라 셀 A3에서 F12까지의 값이 변하는 것을 확인할 수 있습니다. 엑셀의 기본 기능을 사용하여 차트를 하나 만들어 보겠습니다. 다음 그림 8과 같이 종가 열까지만 해서 범위 (A2:E12)를 선택한 후 '삽입' → '차트' → '주식형(시가-고가-저가-종가)'을 선택하여 캔들 차트를 생성합니다.
차트를 적절히 위치시킨 후 스크롤 바의 좌우 화살표 버튼을 클릭해 보면 차트가 스크롤 되는 효과를 확인할 수 있습니다.
이제는 그림 9까지 했던 작업을 VBA로 프로그래밍하여 버튼 클릭만으로도 동일한 작동을 하도록 구현하겠습니다.
우선, 시나리오를 정리하겠습니다.
 |
| 그림 4. '컨트롤 서식' 선택 |
그림 5에서 볼 수 있듯이 스크롤 바는 속성 중의 하나로 '연결 셀(LinkedCell)'을 지정해야 합니다. H1셀로 지정합니다.'현재값', '최소값', '최대값', '증분 변경' 등의 속성은 기본값으로 둡니다.
 |
| 그림 5. |
셀 A2에서 F2까지 적당히 입력한 후, 셀 A3에 다음 그림과 같이 수식을 입력합니다.
 |
| 그림 6-1. 수식 입력 1 |
OFFSET('기아차-5분'!A2,Sheet6!H1,0)은 '기아차-5분' 시트의 셀 A2에서 아래로 'Sheet6!H1' 값만큼 이동한 셀의 값입니다. 위 수식은 셀 A3에 OFFSET('기아차-5분'!A2,Sheet6!H1,0)의 값이 null string이 아닐 경우, 그 값으로 채우는 것입니다.
이와 유사하게 셀 B3에서 F3까지 아래 수식으로 채웁니다.
 |
| 그림 6-2, 수식 입력 2 |
범위 (A3:F3)를 선택하고 자동채우기(AutoFill) 기능을 사용하여 10줄 정도를 채운 뒤 다음 그림과 같이 OFFSET 함수의 두 번째 인자가 H1이 되도록 합니다.
 |
| 그림 6-3. 수식 입력 3 |
모두 채워 넣었으면 스크롤 바를 움직여볼까요?
 |
| 그림 7. 스크롤 바 테스트 |
그림 7과 같이 스크롤을 함에 따라 셀 A3에서 F12까지의 값이 변하는 것을 확인할 수 있습니다. 엑셀의 기본 기능을 사용하여 차트를 하나 만들어 보겠습니다. 다음 그림 8과 같이 종가 열까지만 해서 범위 (A2:E12)를 선택한 후 '삽입' → '차트' → '주식형(시가-고가-저가-종가)'을 선택하여 캔들 차트를 생성합니다.
 |
| 그림 8. 차트 생성 |
차트를 적절히 위치시킨 후 스크롤 바의 좌우 화살표 버튼을 클릭해 보면 차트가 스크롤 되는 효과를 확인할 수 있습니다.
 |
| 그림 9. 차트 스크롤 효과 |
이제는 그림 9까지 했던 작업을 VBA로 프로그래밍하여 버튼 클릭만으로도 동일한 작동을 하도록 구현하겠습니다.
우선, 시나리오를 정리하겠습니다.
- 시트 위에서 종목명을 선택하고 'Chart' 버튼을 클릭하면 콤보 박스와 버튼을 포함하는 차트 출력 시트(시트명 'CHARTS')가 생성되면서 선택된 종목의 캔들 차트 하나가 출력된다.
- 콤보 박스는 기존에 생성된 캔들정보 시트(*-5분, *-15분, *-60분, *-일) 메뉴를 포함한다. 메뉴에는 선택된 종목의 시트만 포함된다.
- 메뉴를 선택하고 버튼을 클릭하면 차트는 선택된 메뉴의 캔들 차트로 전환된다('one chart with multiple pages').
- 차트는 스크롤 기능을 갖는다.
먼저 차트 출력 시트를 생성하는 함수 'createChartSheet'를 작성합니다. 시트위에서도 언급했듯이 시트는 메뉴를 선택하기 위한 콤보 박스와 차트 전환을 위한 버튼을 포함해야 합니다. 함수의 인자는 가변 길이의 ParamArray로 넘기는데, 최소한 다음을 포함합니다.
- 시트명, 메뉴 목록(comma-separated string), 차트 유형, 차트 크기(width, height, length)
※ (함수 'createChartSheet' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function createChartSheet(ParamArray varArgs() As Variant) As Boolean
If UBound(varArgs) < 5 Then _
Err.Raise 8902, "createChartSheet", "ParamArray too short"
Dim choices As String
createChartSheet = False
On Error GoTo EH_createChartSheet:
csname = varArgs(0)
If Not sheetExist(csname) Then
Set tmpWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
tmpWS.Name = csname
If UBound(varArgs) < 1 Then GoTo SkipControl:
If varArgs(1) = xlNullString Then GoTo SkipControl:
tmpWS.Cells(2, 1).Value = "Charts:"
tmpWS.Cells(2, 2).Font.Size = 10
tmpWS.Columns(2).ColumnWidth = tmpWS.Columns(1).ColumnWidth * 1.8
tmpWS.Columns(3).ColumnWidth = tmpWS.Columns(1).ColumnWidth * 0.2
Set btn = tmpWS.Buttons.Add(tmpWS.Cells(2, 4).Width * 3, _
tmpWS.Cells(2, 4).Height, _
tmpWS.Cells(2, 4).Width, _
tmpWS.Cells(2, 4).Height)
btn.Name = "ButtonChange"
btn.OnAction = "'onChartChange " & varArgs(2) & ", " _
& varArgs(3) & ", " & varArgs(4) & ", " & varArgs(5) & "'"
btn.Characters.Text = "Change"
With btn.Characters.Font
' .Name = "Times New Roman"
' .FontStyle = "Regular"
.Size = 10
End With
Else
Set tmpWS = sheets(csname)
On Error Resume Next
For Each cht In tmpWS.ChartObjects
cht.Delete
Next cht
For Each bar In tmpWS.ScrollBars
bar.Delete
Next bar
On Error GoTo 0
End If
If UBound(varArgs) < 1 Then GoTo SkipControl:
With tmpWS.Cells(2, 2).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=varArgs(1)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
tmpWS.Cells(2, 2).Font.Size = 9
SkipControl:
createChartSheet = True
MyExit:
Exit Function
EH_createChartSheet:
MsgBox "Error[" & Err.Number & "] " & Err.Description & " [createChartSheet]"
GoTo MyExit:
End Function
Function createChartSheet(ParamArray varArgs() As Variant) As Boolean
If UBound(varArgs) < 5 Then _
Err.Raise 8902, "createChartSheet", "ParamArray too short"
Dim choices As String
createChartSheet = False
On Error GoTo EH_createChartSheet:
csname = varArgs(0)
If Not sheetExist(csname) Then
Set tmpWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
tmpWS.Name = csname
If UBound(varArgs) < 1 Then GoTo SkipControl:
If varArgs(1) = xlNullString Then GoTo SkipControl:
tmpWS.Cells(2, 1).Value = "Charts:"
tmpWS.Cells(2, 2).Font.Size = 10
tmpWS.Columns(2).ColumnWidth = tmpWS.Columns(1).ColumnWidth * 1.8
tmpWS.Columns(3).ColumnWidth = tmpWS.Columns(1).ColumnWidth * 0.2
Set btn = tmpWS.Buttons.Add(tmpWS.Cells(2, 4).Width * 3, _
tmpWS.Cells(2, 4).Height, _
tmpWS.Cells(2, 4).Width, _
tmpWS.Cells(2, 4).Height)
btn.Name = "ButtonChange"
btn.OnAction = "'onChartChange " & varArgs(2) & ", " _
& varArgs(3) & ", " & varArgs(4) & ", " & varArgs(5) & "'"
btn.Characters.Text = "Change"
With btn.Characters.Font
' .Name = "Times New Roman"
' .FontStyle = "Regular"
.Size = 10
End With
Else
Set tmpWS = sheets(csname)
On Error Resume Next
For Each cht In tmpWS.ChartObjects
cht.Delete
Next cht
For Each bar In tmpWS.ScrollBars
bar.Delete
Next bar
On Error GoTo 0
End If
If UBound(varArgs) < 1 Then GoTo SkipControl:
With tmpWS.Cells(2, 2).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=varArgs(1)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
tmpWS.Cells(2, 2).Font.Size = 9
SkipControl:
createChartSheet = True
MyExit:
Exit Function
EH_createChartSheet:
MsgBox "Error[" & Err.Number & "] " & Err.Description & " [createChartSheet]"
GoTo MyExit:
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
☞ 위 소스는 크게 세 부분으로 구성됩니다. 차트 출력 시트를 생성하는 부분, 버튼을 생성하고 이벤트 처리 매크로를 할당하는 부분, 그리고 메뉴 선택을 위한 부분입니다.
☞ 위 소스는 크게 세 부분으로 구성됩니다. 차트 출력 시트를 생성하는 부분, 버튼을 생성하고 이벤트 처리 매크로를 할당하는 부분, 그리고 메뉴 선택을 위한 부분입니다.
☞ 메뉴 선택을 위한 콤보 박스는 UI 콘트롤이 아닌 셀 Validation 기능을 사용했습니다.
다음은 차트 출력 시트의 버튼 이벤트 처리 매크로 'onChartChange'를 작성하겠습니다. 위 'createChartSheet' 소스를 자세히 보면, 'onChartChange'의 인자가 네 개임을 알 수 있습니다. 하지만 출력할 캔들정보 시트를 알려주어야 할 필요도 있으므로 ParamArray로 넘기도록 하겠습니다.
- 차트 유형, 차트 크기(width, height, length), 캔들정보 시트명
※ ('onChartChange' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub onChartChange(ParamArray varArgs() As Variant)
If UBound(varArgs) < 3 Then _
Err.Raise 8902, "onChartChange", "ParamArray too short"
Dim chtRng As String, sn As String
On Error GoTo EH_onChartChange:
sn = Cells(2, 2).Value
If UBound(varArgs) > 3 Then sn = varArgs(4)
If sn = xlNullString Then Exit Sub
csheet = ActiveSheet.Name
chtRng = fillChartRng(csheet & "_AUX", sn, True, 2, varArgs(3))
sheets(csheet).Select
endRow = sheets(sn).Cells(1, 1).End(xlDown).Row
If ActiveSheet.ChartObjects.Count = 0 Then
drawChartTWH sheets(csheet & "_AUX").Range(chtRng), _
varArgs(0), varArgs(1), varArgs(2), csheet, _
csheet & "_AUX" & "!" & Cells(1, 1).Address, _
WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
Else
Dim ch As ChartObject, sbar As ScrollBar
Set ch = sheets(csheet).ChartObjects(1)
ch.Chart.SetSourceData Source:=sheets(csheet & "_AUX").Range(chtRng)
Set sbar = sheets(csheet).ScrollBars(1)
sbar.Max = WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
sbar.Value = WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
End If
Exit Sub
EH_onChartChange:
MsgBox "Error(" & Err.Number & ") " & Err.Description & " [onChartChange]"
End Sub
Sub onChartChange(ParamArray varArgs() As Variant)
If UBound(varArgs) < 3 Then _
Err.Raise 8902, "onChartChange", "ParamArray too short"
Dim chtRng As String, sn As String
On Error GoTo EH_onChartChange:
sn = Cells(2, 2).Value
If UBound(varArgs) > 3 Then sn = varArgs(4)
If sn = xlNullString Then Exit Sub
csheet = ActiveSheet.Name
chtRng = fillChartRng(csheet & "_AUX", sn, True, 2, varArgs(3))
sheets(csheet).Select
endRow = sheets(sn).Cells(1, 1).End(xlDown).Row
If ActiveSheet.ChartObjects.Count = 0 Then
drawChartTWH sheets(csheet & "_AUX").Range(chtRng), _
varArgs(0), varArgs(1), varArgs(2), csheet, _
csheet & "_AUX" & "!" & Cells(1, 1).Address, _
WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
Else
Dim ch As ChartObject, sbar As ScrollBar
Set ch = sheets(csheet).ChartObjects(1)
ch.Chart.SetSourceData Source:=sheets(csheet & "_AUX").Range(chtRng)
Set sbar = sheets(csheet).ScrollBars(1)
sbar.Max = WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
sbar.Value = WorksheetFunction.Max(endRow - varArgs(3) - 1, 0)
End If
Exit Sub
EH_onChartChange:
MsgBox "Error(" & Err.Number & ") " & Err.Description & " [onChartChange]"
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
☞ 위 소스는 선택된 캔들정보 시트로부터 차트의 데이터 소스 범위를 구성하는 부분, 차트를 생성/전환하는 부분으로 구성됩니다.
☞ 차트의 데이터 소스 범위 구성은 'fillChartRng' 함수가 수행하며, 그림 6-1부터 6-3까지의 과정을 자동화하는 것입니다.
☞ 차트 생성은 'drawChartTWH' 함수가 수행하며, 차트 전환은 이미 생성된 차트의 데이터 소스를 변경하는 것입니다.
차트의 데이터 소스 범위는 고정시키고, 셀 함수 OFFSET을 활용하여 스크롤 기능을 구현하는 과정을 위에서 설명했습니다. 이를 위해 지정한 범위에 셀 수식을 자동으로 채우는 함수 'fillChartRng'를 작성하겠습니다. 필요한 인자는 다음과 같습니다.
- 차트의 데이터 소스를 포함하는 시트명, 캔들정보 시트명, 스크롤 바의 LinkedCell 식별을 위한 column number, 차트의 length
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function fillChartRng(ParamArray varArgs() As Variant) As StringIf UBound(varArgs) < 4 Then _
Err.Raise 8902, "fillChartRng", "ParamArray too short"
dsheet = varArgs(0) 'sheet to which the chart pointing
If dsheet = xlNullString Then _
Err.Raise 8902, "fillChartRng", "Sheet name null"
osheet = varArgs(1) 'sheet including original data of the chart
'if xlNullString, = dsheet
If osheet = xlNullString Then osheet = dsheet
If Not sheetExist(osheet) Then _
Err.Raise 8902, "fillChartRng", "Data sheet not exist"
On Error GoTo EH_fillChartRng:
If Not sheetExist(dsheet) Then
Set tmpWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
tmpWS.Name = dsheet
If varArgs(2) Then tmpWS.Visible = xlSheetHidden
Else
Set tmpWS = sheets(dsheet)
If varArgs(3) < 3 Then tmpWS.Cells.Clear 'dsheet contains data
End If
k = varArgs(3) - 1
With sheets(dsheet)
For j = 2 To 6
.Cells(1, j).Value = sheets(osheet).Cells(1, j - 1).Value
.Cells(2, j).FormulaR1C1 = "=IF(OFFSET('" & osheet & "'!RC[" & (-1) & _
"],R1C" & k & ",0)<>"""",OFFSET('" & osheet & _
"'!RC[" & (-1) & "],R1C" & k & ",0),"""")"
Next j
endRow = sheets(osheet).Cells(1, 1).End(xlDown).Row
endRow = WorksheetFunction.Min(endRow, varArgs(4) + 1)
.Cells(2, 2).Resize(1, 5).AutoFill _
Destination:=.Cells(2, 2).Resize(endRow, 5), _
Type:=xlFillDefault
End With
fillChartRng = Cells(1, 2).Address + ":" + Cells(endRow, 6).Address
Exit Function
EH_fillChartRng:
MsgBox "Error(" & Err.Number & ") " & Err.Description & " (on fillChartRng)"
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
☞ 함수의 기능은 그림 6-1부터 6-3까지의 과정을 자동화하는 것입니다.
☞ 세 번째 인자는 차트의 데이터 소스 시트를 감추기 위한 boolean형 인자입니다. 네 번째 인자 varArgs(3)는 스크롤 바의 LinkedCell을 지정하기 위한 것입니다.
신규로 작성해야 할 부분은 완료되었고, 스크롤 바를 추가하기 위해 'drawChartTWH'를 수정하겠습니다. 스크롤 바의 LinkedCell 위치와 초기값을 알려주는 인자를 추가로 넘겨주고, 이를 받아 스크롤 바를 추가하는 로직을 입력합니다.
※ (수정 후 'drawChartTWH' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function drawChartTWH(ParamArray varArgs() As Variant)Dim cht As ChartObject
Dim chtSheet As Worksheet
Set chtSheet = ActiveSheet
If UBound(varArgs) > 3 Then
If varArgs(4) = xlNullString Then GoTo UseActiveSheet:
If Not sheetExist(varArgs(4)) Then
Set chtSheet = Worksheets.Add(After:=Worksheets(Worksheets.Count))
chtSheet.Name = varArgs(4)
Else
Set chtSheet = sheets(varArgs(4))
End If
UseActiveSheet:
End If
po_y = chtSheet.ChartObjects.Count * (2 * Rows("1:1").RowHeight + varArgs(3)) _
+ 2 * Rows("1:1").RowHeight
Set cht = chtSheet.ChartObjects.Add(Left:=0, Width:=varArgs(2), _
Top:=po_y + Rows("1:1").RowHeight, _
Height:=varArgs(3))
cht.Chart.SetSourceData Source:=varArgs(0)
cht.Chart.ChartType = varArgs(1)
If UBound(varArgs) > 4 Then
If varArgs(5) <> xlNullString Then
With chtSheet.ScrollBars.Add(0, po_y, varArgs(2), Rows("1:1").RowHeight)
'.Name = "ChartScroll"
.Min = 0
.Max = varArgs(6)
.Value = varArgs(6)
.SmallChange = 1
.LargeChange = 1
.LinkedCell = varArgs(5)
.Display3DShading = True
End With
End If
End If
Set chtSheet = Nothing
Set cht = Nothing
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
☞ 하이라이트된 부분이 추가된 소스입니다.
마지막으로 추가 및 수정된 부분을 반영하기 위해 'doCharting' Sub모듈을 수정하도록 하겠습니다.
※ (수정 후 'doCharting' 소스)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub doCharting(sn)sn_cs = sheets_cs(sn)
sn_Arr = Split(sn_cs, ",")
If UBound(sn_Arr) < 0 Then Exit Sub
If createChartSheet("CHARTS", sn_cs, xlStockOHLC, 600, 300, 140) Then
sheets("CHARTS").Select
Call onChartChange(xlStockOHLC, 600, 300, 140, sn_Arr(LBound(sn_Arr)))
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
추가된 모듈 및 함수 'createChartSheet', 'onChartChange', 'fillChartRng'를 기존의 'drawChartTWH' 함수 아래에 추가하고, 'drawChartTWH'와 'doCharting'은 위 소스를 참고하여 수정하거나 대체하십시요.
작업 중인 파일을 저장한 후 테스트를 해 보겠습니다. ※ 테스트를 위해 5분, 15분, 일봉 데이터를 미리 충분하게 적재했습니다.
 |
| 그림 10. 테스트 결과 |
처음에 정했던 시나리오 대로 작동하는 것을 확인하셨나요?
※ 다음 글의 주제는 '차트와 DDE 연동'입니다.
댓글 없음:
댓글 쓰기