엑셀 VBA 사용해서 조건부 항목 구하기

조건문 만들기(if 사용)

엑셀 VBA의 조건문은 "if...Then... End if" 형식을 사용해서 만들 수 있다. 여러 가지 조건을 걸어서 결과를 만들고 싶으면, "if... Then... Elseif... Then... Else... End if" 형태를 사용하면 된다. 

Sub 서브루틴명()

    If (조건문1) Then
		(원하는 결과)
    ElseIf (조건문2) Then
        (원하는 결과)
    Else
		(그 외의 경우 원하는 결과)
    End If

End Sub

 

조건문을 이용한 수익률이 높은 종목 찾기

데이터 입수(KRX정보데이터 시스템)

국내 ETF시세는 KRX정보데이터 시스템(http://data.krx.co.kr/)에서 아래 그림과 같이 찾을 수 있다. 

그림1: KRX 에서 ETF 종가 입수하기

2023년 7월 25일 ETF 데이터와 2024년 7월 25일 ETF 데이터를 각각 뽑아서 240725 시트, 230725 시트를 만든다.

조건문을 이용해서 수익률이 높은 종목 찾기

먼저, 2023년 7월 25일 데이터를 기준으로 (종목코드: 종가)로 이루어진 딕셔너리 데이터를 만든다.( 엑셀 VBA 딕셔너리(Dictionary) 기능 사용하기) for 문을 사용하여 240725 시트를 행마다 확인하면서 (엑셀 VBA 데이터 반복문(for) 사용하기) 앞서 만든 딕셔너리에서 동일한 종목코드를 가진 데이터가 있는지 확인한다. 동일한 종목코드를 가진 데이터가 있다면, 2023년 종가와 2024년 종가 데이터가 모두 있다는 뜻이므로 "(2024년 종가 - 작년 종가) / 작년 종가"를 이용해서 수익률을 구한다. 만약 수익률이 기준 수익률보다 크다면, "result" 시트에 종목코드, 종목명, 2024년 종가, 수익률을 기록한다. 

Sub ETF수익률비교()

    ' Scripting.Dictionary 객체 생성하여 딕셔너리로 활용
    Dim 딕셔너리 As Object
    Set 딕셔너리 = CreateObject("Scripting.Dictionary")
    
    ' 기준 수익률을 result 시트의 셀 F1에서 가져옴
    기준수익률 = Worksheets("result").Cells(1, 6)
    
    ' 첫 번째 데이터의 최대 행 수와 두 번째 데이터의 최대 행 수 지정
    dictmaxrow = 745
    datamaxrow = 874
    
    ' result 시트에서 기존 데이터를 초기화 (A2:D 범위의 내용을 지움)
    Worksheets("result").Range("A2:D1048576").ClearContents
    
    ' 첫 번째 데이터(230725 시트)를 딕셔너리에 추가 (코드: 과거가격)
    For i = 2 To dictmaxrow
        과거코드 = Worksheets("230725").Cells(i, 1) ' ETF 코드
        과거가격 = Worksheets("230725").Cells(i, 3) ' 과거 ETF 가격
        
        ' 딕셔너리에 과거 코드가 없으면 추가 (코드, 가격)
        If 딕셔너리.exists(과거코드) Then
            ' 이미 존재할 경우 아무 작업도 하지 않음
        Else
            딕셔너리.Add 과거코드, 과거가격
        End If
    Next

    ' 결과를 기록할 행 번호 초기화
    writerow = 2
    
    ' 두 번째 데이터(240725 시트)를 순회하여 수익률을 계산하고, 기준 수익률을 초과하는 종목을 기록
    For j = 2 To datamaxrow
        코드 = Worksheets("240725").Cells(j, 1) ' 현재 ETF 코드
        종목명 = Worksheets("240725").Cells(j, 2) ' 현재 ETF 종목명
        가격 = Worksheets("240725").Cells(j, 3) ' 현재 ETF 가격
        
        ' 과거 데이터에 해당 코드가 있는 경우 수익률 계산
        If 딕셔너리.exists(코드) Then
            ' 수익률 계산: (현재 가격 - 과거 가격) / 과거 가격
            수익률 = (가격 - 딕셔너리(코드)) / 딕셔너리(코드)
            
            ' 수익률이 기준 수익률보다 높으면 result 시트에 기록
            If 수익률 > 기준수익률 Then
                Worksheets("result").Cells(writerow, 1) = 코드       ' 코드
                Worksheets("result").Cells(writerow, 2) = 종목명     ' 종목명
                Worksheets("result").Cells(writerow, 3) = 가격       ' 현재 가격
                Worksheets("result").Cells(writerow, 4) = 수익률     ' 수익률
                writerow = writerow + 1                              ' 다음 기록 행 번호 증가
            Else
				'수익률이 기준 수익률보다 낮으면 아무 작업도 하지 않음
            End If
        Else
            '과거 데이터에 코드가 없으면 아무 작업도 하지 않음
        End If
    Next

End Sub

 

위 코드에서 조건문(if)는 아래와 같이 세 번 사용되었다. 코드에서 확인할 수 있듯이 조건문(if)은 반드시 End if로 종결시켜야 하며, 그렇지 않을 경우 코드 실행 시 에러가 발생한다.

1. 2023년 데이터로 딕셔너리를 만들 때 중복이 있는지 판단하기 위해서 딕셔너리에 데이터가 존재하면 아무 결과도 안 일어나고, 존재하지 않으면 딕셔너리에 추가하는 조건문

2. 2024년 데이터의 종목코드가 앞서 만든 딕셔너리에 존재한다면, 수익률을 구한 뒤 추가적인 로직을 실행시키고 그 외에는 아무 일도 일어나지 않는 조건문

3. 수익률이 기준수익률보다 높으면 result 시트에 결과를 기록하고 그렇지 않을 경우 아무 작업도 하지 않는 조건문

아래는 위 작업이 모두 들어있는 결과파일이다.

ETF_수익률비교_연습파일.xlsm

0.25MB