Motivation:
1. 자동화(automation).
2. 특정한 조건을 만족하는 cell 이 존재할 경우, 특정 cell의 수식을 수정하여 출력하자.
| 변수 | 수식 적용 전 | 수식 적용 후 | 비고 |
| notional amount in million | 10000000 | 10,000,000 | 천의 자리만큼 쉼표(,) 표시 |
| settlement % | 0.03394734 | 3.394734% | % 표시, 소수점 6자리까지 표시 |
| trade date ("T" day) | 2022-02-01 | 2022.02.01.화 | - 표시를 .로 변경 한글로 요일 표시 |
| settlement date(T+5 day) | 2022-02-08 | 2022.02.08.화 | Excel 함수 중 workday(trade date, 5) 사용 |
사용 language: VBA
사용 platform: Microsoft 365 Excel
사용 개념: Sub(), Dim ... As, For.. Next, If... Then, ElseIf ... Then, Cells, NumberFormatLocal
사용 함수(built-in): Application.WorksheetFunction.Power, Application.WorksheetFunction.Workday
작성일: 2022년 2월 2일 수요일
VBA Macro 적용 전 화면:
VBA Macro 적용 후 결과 화면:
20220202 VBA formatting_example(macro).xlsm
0.03MB
coding 부분:
'For future refernce, code-only parts are written below;
'향후 참고를 위해, 코딩만 된 부분을 적어 놓는다:
Sub format_changer_original()
Dim j As Integer
For j = 2 To 11
Cells(3, j) = Cells(1, j) * Application.WorksheetFunction.Power(10, 6)
Cells(7, j) = Application.WorksheetFunction.workday(Cells(6, j), 5)
If Cells(2, j) = "EUR" Then
Cells(3, j).NumberFormatLocal = """EUR"" 00,000"
Cells(4, j).NumberFormatLocal = "0.000000% ""(EUR)"" "
Cells(5, j).NumberFormatLocal = """EUR"" 00,000"
ElseIf Cells(2, j) = "USD" Then
Cells(3, j).NumberFormatLocal = """USD"" 00,000"
Cells(4, j).NumberFormatLocal = "0.000000% ""(USD)"" "
Cells(5, j).NumberFormatLocal = """USD"" 00,000"
End If
Cells(6, j).NumberFormatLocal = "[$-ko-KR] yyyy.mm.dd.ddd"
Cells(7, j).NumberFormatLocal = "[$-ko-KR] yyyy.mm.dd.ddd"
Next j
End Subcodes 설명
i 번째 row, j 번째 column 을 표현해 보자.
| (1,1) | (1,2) | (1,3) |
| (2,1) | (2,2) | (2,3) |
| (3,1) | (3,2) | (3,3) |
| (4,1) | (4,2) | (4,3) |
주석 부분에 설명을 적어 놓았다.
덧: tistory 블로그의 '코드블럭'에 VBA 언어는 아직 지원이 되지 않아서 다소 아쉽다.
Sub format_changer()
'서식(format)을 미리 정해진 양식대로 바꾸는 procedure이다
'input: notional amount in million, currency, settle %, settlement amount, trade date
'ouptut: notional amount, settlement date (trade date + 3 working days)
'j번째 컬럼을 표시하는 변수 j를 지정한다. 변수 j는 정수(integer)이다.
Dim j As Integer
'For... Next 구문을 이용하여, 같은 작업을 여러 컬럼에 적용한다
'2번째 column부터 11번째 column까지 작업을 한다.
'즉, 10개 자산 거래 내역에 대해 서식을 변경한다.
For j = 2 To 11
' Case 1) 자산이 EURO 표시라면, 서식(format)에 EUR 라고 표기해 준다.
' Cells(i,j) 라는 표시는 i번째 row, j번재 column을 의미한다.
If Cells(2, j) = "EUR" Then
' EUR를 표기해 주기 위해, double 따음표 "" 로 문자 EUR를 감싸준다.
' 천의 자리만큼 쉼표(comma)를 적어준다.
Cells(3, j).NumberFormatLocal = """EUR"" 00,000"
' 거래 금리의 소숫점 여섯자리까지 표시하고, % 기호를 붙여준다.
' EUR를 표기해 주기 위해, double 따음표 "" 로 문자 (EUR)를 감싸준다.
Cells(4, j).NumberFormatLocal = "0.000000% ""(EUR)"" "
Cells(5, j).NumberFormatLocal = """EUR"" 00,000"
' Case 2) 자산이 USD 표시라면, 서식(format)에 USD라고 표기해 준다.
ElseIf Cells(2, j) = "USD" Then
Cells(3, j).NumberFormatLocal = """USD"" 00,000"
Cells(4, j).NumberFormatLocal = "0.000000% ""(USD)"" "
Cells(5, j).NumberFormatLocal = """USD"" 00,000"
End If
' 2^3 = 8 을 표기하기 위해, Power 함수를 불러온다
' Power 함수를 불러오는 법은, Application.WorksheeFunction.Power(a,b)
' a^b 를 출력한다. 예: 2^3 = 8
Cells(3, j) = Cells(1, j) * Application.WorksheetFunction.Power(10, 6)
'Workday 함수를 사용하기 위해, application.worksheetfunction을 불러온다.
'이 예제에서는 거래일 5영업일 후에 결제(settle)이 된다고 가정하자.
Cells(7, j) = Application.WorksheetFunction.workday(Cells(6, j), 5)
'날짜 서식을 바꾸어보자.
'yyyy.mm.dd.ddd의 예시: 2022.02.04.금
'[$-ko-KR] 를 이용하여 한국어로 출력시킬 수 있다.
Cells(6, j).NumberFormatLocal = "[$-ko-KR] yyyy.mm.dd.ddd"
Cells(7, j).NumberFormatLocal = "[$-ko-KR] yyyy.mm.dd.ddd"
'다음 j 컬럼값에서의 작업을 위해 next j를 입력한다.
Next j
End Sub끝
제목: VBA 활용 #1 - 지정된 수식으로 (format)출력하자.
Compute2080의 블로그
https://compute2080.tistory.com
Compute2080: 금융공학, 프로그래밍, 정량화(quantify)
compute2080.tistory.com