엑셀 VBA 데이터 반복문(for) 사용하기
반복문 만들기(for 사용)
엑셀 VBA의 반복문은 "do... loop"를 사용해서 만들 수도 있고 for 를 사용해서 만들 수도 있다. "do... loop"도 사람들이 많이 쓰는 구조이긴 하지만 작은 데이터 상 오류가 있을 때 무한반복이 자주 발생하기 때문에 최대 작업 수를 지정할 수 있는 for 문이 조금 더 좋다. for 문의 기본적인 구조는 아래와 같다.
Sub 서브루틴명()
for i = 시작숫자 to 마지막숫자
...
next
sub
반복문을 이용한 국내 ETF 종목 찾기
국내 ETF시세는 KRX정보데이터 시스템(http://data.krx.co.kr/)에서 아래 그림과 같이 찾을 수 있다.
해당 데이터를 엑셀로 받은 뒤 xlsm 형식의 다른 이름으로 저장한다. 이제 VBA를 넣기 위해서 엑셀 VBA 사용해서 특정 셀에 값 넣기 에서 살펴본 것처럼 엑셀VBA를 켜고(Alt+F11) module을 삽입한다. 이번에 찾고 싶은 종목들은 ETF등락률이 양수 이면서, 기초지수 등락률 대비 ETF의 등락률이 더 높고, 기초지수가 채권과 관련된 것들이다.
2024.07.25 기준으로 엑셀로 다운을 받으면 총 873개 종목들의 정보가 나오고, row 2부터 874까지 한 row 마다 조건을 충족하는 데이터를 찾을 것이다. 조건을 충족한다면, "result"라는 새로 만든 시트에 종목코드와 종목명을 기록할 것이다. 정리하면 아래 그림 2와 같이 for 문을 통해서 한 row 씩 해당 종목의 등락률, 기초지수의 등락률을 비교하고, 기초지수에 채권이라는 단어가 있는지 판단한 뒤에 모든 조건을 만족하면 result 텝에 넣는 식이다.
Sub ETF종목찾기()
Set 데이터시트명 = Worksheets("Sheet1")
Set 결과시트명 = Worksheets("result")
데이터마지막행 = 데이터시트명.UsedRange.Rows.Count
채권형여부 = "채권"
결과시트명작업행 = 2
For i = 2 To 데이터마지막행
'ETF종가는 5열에 기초자산 종가는 18열에 있고, 15열 기초자산을 기술한 컬럼에 InStr이라는
'내장함수를 통해서 "채권"이라는 단어가 있는지 판단
If 데이터시트명.Cells(i, 5) > 0 And 데이터시트명.Cells(i, 5) > 데이터시트명.Cells(i, 18) And _
InStr(데이터시트명.Cells(i, 15).Value, 채권형여부) > 0 Then
'대상이 되는 데이터가 있을 경우 result시트(결과시트작업명)에 값을 기록하고
'결과시트작업행 값에 +1을 해줌
결과시트명.Cells(결과시트명작업행, 1) = "'" & 데이터시트명.Cells(i, 1)
결과시트명.Cells(결과시트명작업행, 2) = "'" & 데이터시트명.Cells(i, 2)
결과시트명작업행 = 결과시트명작업행 + 1
End If
Next
End Sub
결과적으로 아래와 같은 작업파일을 얻을 수 있다.