실시간 경락 정보 #3 데이터 가공 & 표시

지난 포스팅에서 OpenAPI를 이용해서 실시간 경락 정보를 가져오는 코드를 만들었습니다.

이번 포스팅에서는 불러온 데이터 중에서 필요한 내용만 가공해서 엑셀 시트로 뿌려주는 작업을 하겠습니다.

 

우선 원하는 데이터를 뽑기 위해서 요청변수를 알아야 합니다.

이부분은 공공데이터의 정보에서 확인할수 있습니다.

 

공공데이터의 원천실시간경락가격도매시장목록조회의 상세페이지로 이동한뒤 요청변수 탭에서 확인 가능합니다.

data.mafra.go.kr/opendata/data/indexOpenDataDetail.do?data_id=20151117000000000537&service_ty=O

원천실시간경락가격도매시장목록조회

요청변수 내용은 아래에 있습니다.

여기서 이번에 사용할 요청변수는 경락일자, 구시장명, 구법인명, 구품목명, 구품종명입니다.

그리고 시트에 뿌려줄때 필요한 내용은 출력결과 탭에서 확인할수 있습니다.

출력결과 항목은 아래에서 확인해 주세요.

이번에는 불러온 데이터를 배열에 넣어서 사용할거라 항목명은 사용하지 않고 순서만 확인하면 되겠습니다.

 

데이터를 표시할 시트는 아래와 같이 만들어 주었습니다.

경매일자와 품목은 ActiveX 컨트롤의 텍스트 상자, 도매법인은 ActiveX 컨트롤의 콤보 상자를 사용했습니다.

우선 파일을 열었을때 도매법인 콤보박스에 목록을 추가시켜주었습니다.

Option Explicit

Private Sub Workbook_Open()
    With Sheets("실시간경락정보")
        .TextDate.Value = Date
        .ComboInstt.AddItem "법인전체"
        .ComboInstt.AddItem "동부청과"
        .ComboInstt.AddItem "부산중앙청과"
        .ComboInstt.AddItem "농협반여(공)"
        .ComboInstt.ListIndex = 0
    End With
End Sub

검색 버튼과 초기화 버튼에 각각 매크로를 지정해 주었습니다.

'검색 버튼
Sub SearchProduct()
    Call ClearList
    
    With Sheets("실시간경락정보")
        Call CallOpenAPI(.TextDate.Value, .ComboInstt.Value, .TextSearch.Value)
    End With
End Sub

'초기화 버튼
Sub ClearList()
    '제목줄 제외 삭제
    If Range("B3") <> "" Then
        Range("B3", Cells(Rows.Count, "L")).ClearContents '기존 데이터 삭제
    End If
End Sub

검색 버튼 클릭시 호출되는 코드는 날짜, 도매법인, 품명을 변수로 넘겨주도록 하였습니다.

이부분은 코드가 길어서 접어두니 아래 더보기를 눌러 펼쳐서 확인해 보시기 바랍니다.

Sub CallOpenAPI(mydate As String, instt As String, pn As String)

    Dim strURL As String
    Dim strResult As String
    Dim objHttp As New XMLHTTP60
    Dim apiKey$, sNumber$, eNumber$, searchName$, searchDate$, coName$, productName$
    
    '한글 url 인코딩
    Static objHtmlfile As Object
    If objHtmlfile Is Nothing Then
        Set objHtmlfile = CreateObject("htmlfile")
        With objHtmlfile.parentWindow
        .execScript "function encode(s) {return encodeURIComponent(s)}", "jscript"
        End With
    End If

    apiKey = 인증키
    sNumber = "1"
    eNumber = "50"
    searchName = "부산반여도매시장"
    searchDate = Replace(mydate, "-", "")   '날짜의 -(하이픈)을 제거
    coName = instt
    productName = pn
    strURL = "http://211.237.50.150:7080/openapi/" & apiKey & "/xml/Grid_20151127000000000314_1/" & sNumber & "/" & eNumber
    strURL = strURL + "?DELNG_DE=" & searchDate
    strURL = strURL + "&WHSAL_MRKT_NM=" & objHtmlfile.parentWindow.encode(searchName)
    strURL = strURL + "&STD_PRDLST_NM=" & objHtmlfile.parentWindow.encode(productName)
    If instt <> "법인전체" Then
        strURL = strURL + "&INSTT_NM=" & objHtmlfile.parentWindow.encode(coName)
    End If
    objHttp.Open "GET", strURL, False
    objHttp.Send
    
    If objHttp.Status = 200 Then '성공했을 경우
        strResult = objHttp.ResponseText
        
        'XML로 연결
        Dim objXml As MSXML2.DOMDocument60
        Set objXml = New DOMDocument60
        objXml.LoadXML (strResult)
        
        '노드 연결
        Dim nodeList As IXMLDOMNodeList
        Dim nodeRow As IXMLDOMNode
        Dim nodeCell As IXMLDOMNode
        Dim nRowCount As Integer
        Dim nCellCount As Integer
        Dim xItem() As String
        
        Set nodeList = objXml.SelectNodes("/Grid_20151127000000000314_1/row")

        
        nRowCount = 2
        For Each nodeRow In nodeList
            nRowCount = nRowCount + 1
            
            nCellCount = 0
            ReDim xItem(1 To nodeRow.ChildNodes.Length)
            For Each nodeCell In nodeRow.ChildNodes
                nCellCount = nCellCount + 1
                xItem(nCellCount) = nodeCell.Text
            Next nodeCell
                
            '번호
            Cells(nRowCount, 2).Value = xItem(1)
            '경매시간
            Cells(nRowCount, 3).Value = xItem(3)
            '비고
            Cells(nRowCount, 4).Value = xItem(5)
            '도매시장
            Cells(nRowCount, 5).Value = xItem(7)
            '도매법인
            Cells(nRowCount, 6).Value = xItem(11)
            '품목(품종)
            Cells(nRowCount, 7).Value = xItem(21) + "(" + xItem(25) + ")"
            '단위
            Cells(nRowCount, 8).Value = xItem(28) + xItem(30) + xItem(32)
            '등급
            Cells(nRowCount, 9).Value = xItem(36)
            '경락가
            Cells(nRowCount, 10).Value = xItem(39)
            '거래량
            Cells(nRowCount, 11).Value = xItem(46)
            '산치
            Cells(nRowCount, 12).Value = xItem(43)
        Next nodeRow
    Else
        MsgBox "접속에 에러가 발생했습니다"
    End If

End Sub

지난 포스팅에서 빼먹은 내용인데, 위 코드를 사용하기 위해 몇가지 참조를 해주셔야 합니다.

아래 그림을 참조해 주세요.

위 3가지 항목을 도구>참조에서 선택해 주세요.

 

실시간가격조회.zip

0.40MB

첨부파일은 exe로 변환한 파일입니다. 코드에 인증키 부분이 있어 보안을 위해 변환한 파일입니다.

백신이나 디펜더에서 바이러스로 인식될수 있습니다.

테스트용으로 사용해 보실분들은 사용해 보셔도 됩니다.

단지 테스트용이기 때문에 바이러스로 인식되서 걱정 되시는 분들은 사용 하지 않는걸 추천드립니다.

전체검색은 이상하게 통신 시간이 많이 걸려서 일단 제외시켰습니다.