Kospi200 종목별 주가를 JSon 데이터로 파싱해서 가져오기

관련 지식인 질문:

https://kin.naver.com/qna/detail.nhn?d1id=1&dirId=102&docId=313829165&page=1#answer1

작동설명:

매크로 허용하고 파일을 열고

우측상단 새로고침 아이콘을 누르면 

네이버 증권에 접속하여 (PC버전보다 모바일사이트에 접속)

Json 데이터를 수집하고

이를 VBAJSON을 이용하여

파싱하여

엑셀 시트에 입력해준다.

코드는 해당종목으로 하이퍼링크가 추가되고

전일비와 등락률에는 조건부 서식이 적용되고

또한 아이콘 서식이 적용된다.

PC버전 네이버 증권사이트에 접속하여

HTML데이터를 HTML Object Library와 GetElementsByTagId 를 이용하여 파싱할 수도 있겠으나

각 페이지별로 20여페이지를 로딩/파싱해야해서 불편하여

이번에는 웹사이트에서 실시간으로 데이터를 JSon 데이타 구조로 가져오기 때문에

한꺼번에 200여개의 자료를 가져올 수 있는 JSon데이타 파싱을 이용하였습니다.

VBA-JSON라이브러리를 이용하였습니다.

네이버 KOSPI 200자료의 JSON구조는 아래와 같습니다.

    {"resultCode":"success","result":{"totCnt":202,"itemList":[{"cd":"005930","nm":"삼성전자",

     "mt":"0","nv":42400,"cv":-50,"cr":-0.12,"rf":"5","pcv":42450,"mks":2721794,"aq":5178558,"aa":218420},

VBA-JSON을 사용하려면

Microsoft Scripting Engine 을 도구-참조에서 체크해주고

 Dim Json As Object

    Set Json = JsonConverter.ParseJson("JSON문자열")

위와 같이 하면 JSON데이터를 간단히 파싱해줍니다.

Json("resultCode") 는 "success"가 되고,

Json("result")("totoCnt") 값은 202가 되겠습니다.

JSon개체는 Object로 선언하거나 Dictionary 데이터로 선언하면 됩니다.

가장 핵심은 아래 부분입니다.

    'Json 데이터 Parsing    '////// Main Process

    Set Json = JsonConverter.ParseJson(Result)

    

    col = Array("cd", "nm", "mt", "nv", "cv", "cr", "rf", "pcv", "mks", "aq", "aa")

    col2 = Array("코드", "종목", "mt", "현재가", "전일비", "등락률", "rf", "시가", "시가총액(억)", "거래량", "거래대금(백만)")

    colCnt = UBound(col) + 1

    

    total = Json("result")("itemList").Count  ' = Json("result")("totCnt")

    ReDim KOSPI(total, colCnt)

    

    '배열에 입력    '/////// Main Process

    i = 0

    For Each J In Json("result")("itemList")

        For c = 0 To UBound(col)

            KOSPI(i, c) = J(col(c))

            If c = 5 Then KOSPI(i, c) = KOSPI(i, c) & "%"   '등략률은 백분율로 처리

        Next c

        i = i + 1

    Next J

    

    '시트에 출력

    Set sht = ThisWorkbook.ActiveSheet

    With sht

        .Cells.Clear    'Contents

        

        '//////// Main Process: 시트에 배열 내용 출력

        .Range("A1", Cells(1, colCnt)) = col2 '제목 행

        .Range("A2", Cells(total + 1, colCnt)) = KOSPI

        '////////

이하 생략

전체 매크로는 아래와 같습니다.

Option Explicit
 
Sub getKOSPI200()
    
    Dim xmlHttp As Object   'MSXML2.ServerXMLHTTP
    Dim URL As String
    Dim Result As String
    Dim Json As Dictionary, J As Dictionary
    Dim KOSPI As Variant
    Dim i As Integer, c As Integer, total As Long
    Dim col As Variant, colCnt As Integer, col2 As Variant
    Dim sht As Worksheet
    Dim fcs As FormatConditions, fc As FormatCondition
    Dim icset As IconSetCondition
    
    'https://m.stock.naver.com/sise/entryK200Item.nhn?menu=kpi200_market_sum
    'https://finance.naver.com/sise/entryJongmok.nhn?&page=1
    URL = "https://m.stock.naver.com/api/json/sise/enrollItemListJson.nhn?pageSize=202"
    
    '웹접속
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP")
    If xmlHttp Is Nothing Then Exit Sub     'need MSXML 3.0 above
    
    With xmlHttp
        .Open "Get", URL
        .setRequestHeader "User-agent", "Mobile"
        .send
        .waitForResponse
        Result = .responseText   'StrConv(.responseText, vbUnicode)
    End With
    
    Set xmlHttp = Nothing
    
    'MsgBox Result
    '{"resultCode":"success","result":{"totCnt":202,"itemList":[{"cd":"005930","nm":"삼성전자",
    ' "mt":"0","nv":42400,"cv":-50,"cr":-0.12,"rf":"5","pcv":42450,"mks":2721794,"aq":5178558,"aa":218420},
    
    'Json 데이터 Parsing    '////// Main Process
    Set Json = JsonConverter.ParseJson(Result)
    
    col = Array("cd", "nm", "mt", "nv", "cv", "cr", "rf", "pcv", "mks", "aq", "aa")
    col2 = Array("코드", "종목", "mt", "현재가", "전일비", "등락률", "rf", "시가", "시가총액(억)", "거래량", "거래대금(백만)")
    colCnt = UBound(col) + 1
    
    total = Json("result")("itemList").Count  ' = Json("result")("totCnt")
    ReDim KOSPI(total, colCnt)
    
    '배열에 입력    '/////// Main Process
    i = 0
    For Each J In Json("result")("itemList")
        For c = 0 To UBound(col)
            KOSPI(i, c) = J(col(c))
            If c = 5 Then KOSPI(i, c) = KOSPI(i, c) & "%"   '등략률은 백분율로 처리
        Next c
        i = i + 1
    Next J
    
    '시트에 출력
    Set sht = ThisWorkbook.ActiveSheet
    With sht
        .Cells.Clear    'Contents
        
        '//////// Main Process: 시트에 배열 내용 출력
        .Range("A1", Cells(1, colCnt)) = col2 '제목 행
        .Range("A2", Cells(total + 1, colCnt)) = KOSPI
        '////////
        
        '셀 서식들
        .UsedRange.Columns("A:A").NumberFormat = "000000"
        .UsedRange.Columns("C:K").NumberFormat = "###,###,###"
        .UsedRange.Columns("E:E").NumberFormat = "0"
        .UsedRange.Columns("F:F").NumberFormat = "0.00%"
        
        '조건부 서식
        Set fcs = .Range("E2:F" & total + 1).FormatConditions
        fcs.Delete
        fcs.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=0"
        Set fc = fcs.Item(fcs.Count)
        fc.Font.Color = rgbDarkOrange
        fcs.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"  'Type:=xlExpression, Formula1:="=$E1>0"
        Set fc = fcs.Item(fcs.Count)
        fc.Font.Color = rgbNavy
        
        '아이콘 서식1
        Set fcs = .Range("E2:E" & total + 1).FormatConditions
        Set icset = fcs.AddIconSetCondition
        fcs.Item(fcs.Count).SetFirstPriority    '필수
        icset.IconSet = ThisWorkbook.IconSets(xl3Triangles)
        With icset.IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = 0
            .Operator = xlGreaterEqual
        End With
        With icset.IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 0
            .Operator = xlGreater
        End With
        
        '아이콘 서식2
        Set fcs = .Range("F2:F" & total + 1).FormatConditions
        Set icset = fcs.AddIconSetCondition
        fcs.Item(fcs.Count).SetFirstPriority    '필수
        icset.IconSet = ThisWorkbook.IconSets(xl3TrafficLights1)
        With icset.IconCriteria(2)
            .Type = xlConditionValueNumber
            .Value = 0
            .Operator = xlGreaterEqual
        End With
        With icset.IconCriteria(3)
            .Type = xlConditionValueNumber
            .Value = 0
            .Operator = xlGreater
        End With
        
        '하이퍼링크
        .Hyperlinks.Delete
        Dim r As Range
        URL = "https://m.stock.naver.com/item/main.nhn#/stocks/"
        'URL = "https://finance.naver.com/item/main.nhn?code="
        For Each r In sht.Range("A2:a" & total + 1)
            .Hyperlinks.Add Anchor:=r, Address:=URL & Format(r.Value, "000000")
        Next r
        
        '보기 좋게
        .Columns.AutoFit
        .Rows("2:2").Select
        ActiveWindow.FreezePanes = True
        .Range("A1").Select
            
    End With
    
    Erase KOSPI
    
End Sub
 

특히 이번에는 아이콘 서식을 VBA로 적용하여

양수,0,음수에 따라 위아래 화살표, 빨간/파란 동그라미 아이콘이 달리 표시되게 해보았습니다.

매크로 파일 아래에 첨부합니다.

JSON_Parser.xlsm