VBA를 활용한 엑셀 데이터 변환 방법

VBA를 활용한 엑셀 데이터 변환 방법

엑셀의 대표적인 기능 중 하나가 데이터의 관리와 분석입니다. 하지만 때로는 수 많은 데이터를 일일히 관리하기 어렵고, 원하는 형태로 변환해야 하는 경우가 있습니다. 이때 VBA(Visual Basic for Application)를 활용하면 자동으로 데이터를 변환하여 관리할 수 있습니다.

 

칼퇴키트 바로가기

칼퇴키트, 업무용 도구모음

 

1. VBA란?

VBA는 Microsoft Office Suite에 포함된 프로그래밍 언어 중 하나로, 엑셀, 워드, 액세스 등의 애플리케이션에서 매크로, 함수 및 사용자 양식과 같이 사용될 수 있습니다. VBA를 사용하면 엑셀 VBA 매크로를 만들고 실행할 수 있으며, 매크로를 사용하여 데이터를 대량으로 자동으로 변환하고 관리할 수 있습니다.

---

2. 데이터 변환

데이터를 변환하는 방법은 여러 가지가 있지만, VBA를 통한 데이터 변환은 가장 자주 사용되는 방법 중 하나입니다. VBA를 사용하여 데이터를 변환하면 일반적으로 3단계로 진행됩니다.

2-1. 데이터 수집

데이터 변환을 위해서는 우선 데이터를 수집해야 합니다. 데이터는 다양한 소스(예 : 웹사이트, 데이터베이스, 텍스트 파일 등)에서 가져올 수 있습니다.

2-2. 데이터 가공

수집한 데이터를 원하는 형태로 가공합니다. 이 단계에서는 데이터에 대한 필터링, 정렬, 계산 등을 수행합니다. 엑셀의 내장 기능으로도 가능하지만, VBA를 사용하면 보다 복잡한 계산이나 다양한 조건에 따른 필터링 등의 자동화가 가능합니다.

2-3. 데이터 출력

마지막으로 가공된 데이터를 출력합니다. 이 단계에서는 엑셀 워크시트에 데이터를 목적에 따라 적절한 형식으로 출력하거나, 다른 애플리케이션으로 내보낼 수도 있습니다.

---

3. VBA를 통한 데이터 변환 예시

VBA를 사용하여 데이터를 변환하는 방법은 다양합니다. 이번에는 VBA를 사용하여 특정 기간 동안의 주식 데이터를 수집하고, 이를 통계 데이터로 변환하는 방법을 살펴봅시다.

3-1. 데이터 수집

주식 데이터는 일반적으로 인터넷 상에서 가져옵니다. 이 예시에서는 Yahoo Finance에서 제공하는 무료 API를 사용합니다. API를 사용하면 웹 사이트를 직접 크롤링하여 데이터를 수집하는 것보다 간단하고 빠르게 데이터를 가져올 수 있습니다.

Sub GetStockData()
    ' 변수 정의
    Dim Ticker As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim StockData As Variant
    Dim QueryString As String
    Dim URL As String

    ' 입력값 설정
    Ticker = "AAPL"
    StartDate = #1/1/2021#
    EndDate = #12/31/2021#

    ' 데이터 수집
    QueryString = "SELECT * FROM yahoo.finance.historicaldata WHERE symbol = '" & Ticker & "' AND startDate = '" & Format(StartDate, "yyyy-mm-dd") & "' AND endDate = '" & Format(EndDate, "yyyy-mm-dd") & "'"
    URL = "http://query.yahooapis.com/v1/public/yql?q=" & QueryString & "&format=json&env=store://datatables.org/alltableswithkeys"
    Set StockData = GetJSON(URL)

    ' 결과 출력
    Worksheets("Data").Range("A2").Value = "날짜"
    Worksheets("Data").Range("B1").Value = Ticker

    For i = 1 To UBound(StockData, 2)
        Worksheets("Data").Range("A" & i + 2).Value = StockData(0, i)
        Worksheets("Data").Range("B" & i + 1).Value = StockData(1, i)
    Next i
End Sub

위 코드는 VBA를 사용하여 Yahoo Finance에서 특정 종목(Ticker)의 주식 데이터를 수집하는 방법을 보여줍니다. 입력 값으로는 종목, 시작 날짜, 종료 날짜가 있습니다. 데이터는 JSON 형식으로 반환되며, 반환된 데이터를 파싱하여 엑셀 워크시트에 출력합니다.

3-2. 데이터 가공

수집한 주식 데이터를 통계 데이터로 변환하려면, 이전의 데이터와 비교하여 변화량을 계산해야 합니다. 이 예시에서는 이전 주간 종가와의 비교를 통해 상승/하락률을 계산합니다.

Sub CalculateStockStats()
    ' 변수 정의
    Dim Ticker As String
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim i As Integer
    Dim PreviousClose As Double
    Dim CurrentClose As Double
    Dim Change As Double
    Dim ChangePct As Double

    ' 입력값 설정
    Ticker = "AAPL"
    StartRow = 2
    EndRow = Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row

    ' 데이터 가공
    PreviousClose = Worksheets("Data").Range("B" & StartRow - 1).Value
    For i = StartRow To EndRow
        CurrentClose = Worksheets("Data").Range("B" & i).Value

        Change = CurrentClose - PreviousClose
        ChangePct = Change / PreviousClose

        Worksheets("Data").Range("C" & i).Value = Change
        Worksheets("Data").Range("D" & i).Value = ChangePct

        PreviousClose = CurrentClose
    Next i
End Sub

위 코드는 VBA를 사용하여 주식 데이터를 통계 데이터로 가공하는 방법을 보여줍니다. 입력 값으로는 종목, 시작 행, 끝 행이 있습니다. 주식 데이터에서는 이전 시간과 현재 시간 사이의 상승/하락을 계산하여 통계 데이터를 생성합니다. 이전 종가는 이전 행의 마지막 종가로부터 바로 계산되며, 현재 종가는 현재 행의 종가로부터 계산됩니다. 결과는 엑셀 워크시트에 저장됩다.

3-3. 데이터 출력

이제 가공된 데이터를 출력할 차례입니다. 여기에서는 각 날짜의 종가 변화와 상승/하락률을 엑셀 차트로 시각화합니다.

Sub ChartStockData()
    ' 변수 정의
    Dim Ticker As String
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim ChartTitle As String

    ' 입력값 설정
    Ticker = "AAPL"
    StartRow = 2
    EndRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
    ChartTitle = Ticker & " 주식 분석"

    ' 차트 생성
    Dim ChartRange As Range
    Set ChartRange = Worksheets("Data").Range("A" & StartRow & ":D" & EndRow)
    Worksheets.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=ChartRange
    ActiveChart.ChartTitle.Text = ChartTitle
    ActiveChart.Axes(xlCategory).TickLabels.Orientation = 45
End Sub

위 코드는 VBA를 사용하여 주식 데이터를 차트로 출력하는 방법을 보여줍니다. 입력 값으로는 종목, 시작 행, 끝 행, 그리고 차트 제목이 있습니다. 이 코드에서는 전체 데이터를 차트로 출력합니다. 결과는 새로운 엑셀 워크시트에 차트로 저장됩니다.

---

4. 마무리

위에서 살펴본 것처럼 VBA를 활용하여 데이터 변환을 자동화할 수 있습니다. 데이터의 수집, 가공 및 출력을 자동화할 수 있어 업무 효율성을 크게 향상시킬 수 있습니다. 많은 사용자들이 VBA를 사용하여 다양한 자동화 프로세스를 작성하고 있습니다. 여러분의 칼퇴를 응원합니다!

 

포스팅이 도움이 되셨다면 구독, 공감, 댓글 부탁드려요!

행복한 하루 되세요!