[심화방] VBA_심화_100제 #21 [ 서울시 상권분석 크롤링 ]

 

 

회사 업무중에 서울시 상권분석을 하기 위해 참조한 사이트를 소개하려고 한다.

서울시 상권분석 서비스 (seoul.go.kr)

서울시 상권분석 서비스

 

 

이 사이트에서 [지역·상권별 현황] 탭에 대해서 보려고 한다.

 

 

이 사이트는 몇개의 조건 검색으로 Json형태로 사이트에서 뿌려주는데 반응도 빠르다.

허나  한번에 하위메뉴를 여는 방법이 없고, 엑셀이나 다른방식으로 데이터를 다운받을 수 없어 이것을 재 가공하기 위해서는 일일이 긁어 올수밖에는 없어  오랜만에 Json파서를 통해서 엑셀 365 와 함께 나름 멋진(?) 분석 크롤링을 해보려한다.

 

 

뭔가 거창하지만, 솔직히 코드를 보면 너무 단순해서 이글을 읽는 누군가에게 창피하기도 한다.

Option Explicit
Sub Haja_상권분석()

    Dim rngx As Range: Set rngx = [a7]
    Dim html As Object: Set html = CreateObject("htmlfile")
    Dim xmlHttp As Object: Set xmlHttp = CreateObject("msxml2.xmlhttp")
    Dim data$
    Dim strUrl$
    Dim Al As Object: Set Al = CreateObject("system.collections.arraylist")  '= ArrayList 선언
    Dim Alkey                                                                '= 어레이리스트 키값 순환
    Dim Json As Object
    
        With rngx.CurrentRegion                                              '= 영역 초기화
            .Borders.LineStyle = xlNone
            .ClearContents
        End With
        
        Application.ScreenUpdating = True
        
        strUrl = "https://golmok.seoul.go.kr/region/selectStoreCount.json"
              
        '= Payload 값을 data에 담아라
        data = "stdrYyCd=" & [a2] & "&stdrSlctQu=sameQu&stdrQuCd=" & [b2] & "&stdrMnCd=202309&selectTerm=quarter&svcIndutyCdL=CS000000&svcIndutyCdM=all&stdrSigngu=11&selectInduty=1&infoCategory=store"
        
        With xmlHttp
            .Open "POST", strUrl, False
            .setRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
            .Send (data)
                    
            Set Json = JsonConverter.ParseJson(.responseText)
        
        End With
        
           Al.Add Json                                                      '= 어레이리스트에 json값들을 추가
        
           For Each Alkey In Al.Item(0)                                     '= 어레이리스트의 키값들을 순환하면서 각 키값에 해당하는 아이템을 출력하시오
           
                rngx(1, 1) = Alkey("NM")
                rngx(1, 2) = sigu(rngx, CStr(Alkey("CD")))                  '= 시·구 구별 함수를 통해 값을 반환
                rngx(1, 3) = Alkey("FIRST_TOT")
                rngx(1, 4) = Alkey("FIRST_FRC")
                rngx(1, 5) = Alkey("FIRST_NOR")
                rngx(1, 6) = Alkey("SECOND_TOT")
                rngx(1, 7) = Alkey("SECOND_FRC")
                rngx(1, 8) = Alkey("SECOND_NOR")
                rngx(1, 9) = Alkey("THIRD_TOT")
                rngx(1, 10) = Alkey("THIRD_FRC")
                rngx(1, 11) = Alkey("THIRD_NOR")
                rngx(1, 12) = gubun(CStr(Alkey("GUBUN")))                   '= 시·구·동 구분자 함수
                
                Set rngx = rngx.Offset(1)
               
           Next Alkey
           
           [a7].CurrentRegion.Borders.LineStyle = 1
           
           MsgBox "분석이 완료되었습니다."
   
End Sub

Function gubun(str$)

    Select Case str
        Case "si"
            gubun = "시"
        Case "gu"
            gubun = "구"
        Case Else
            gubun = "동"
    End Select

End Function

Function sigu(rngx, str$)

    Select Case Len(str)
        Case Is = 2
            sigu = "서울시"
        Case Is = 5
            sigu = rngx.Value
        Case Else
            sigu = rngx(0, 2).Value
    End Select

End Function

 

코드진행에 앞서 json형태로 값을 어떻게 가져올 수 있는지 url을 확보해야한다. 

개발자도구인 F12키를 누른 후 네트워크탭을 이동후 검색후 가장먼저 보이는 [종로구]를 검색해보도록 하겠다.

 

총 3개가 보이게 되는데, 혹시 검색이 안되는 분들은 crtl + r 키를 눌러서 새로고침 하거나 F5키를 눌러서 새로고침하게 되면 검색이 보이게 될것이다.

 

검색된 3개중에 2번째는 시구 구분 코드이고 / 3번째가 사진에도 보이듯이 각 행정구역에 관련된 값들을 출력됨을 확인 할 수 있다.

 

우리는 저 값들을 가져와서 이쁘게 가공만 하면 되는것이다.

 

크롬개발자도구를 주로 사용하는데 Edge 개발자도구를 사용하였더니 글 중간중간의 개발자도구 페이지가 한글로 되어 있는데, 가급적 영어로 사용하시길 추천한다.

 

 

 

Headers 탭을 확인하니 Request Url 이 있고, Request Method가 POST 방식으로 되어 있다.

Post방식과 Get 방식의 차이는 인터넷 검색으로 그 정의를 본인 스스로 정리해보길 바란다.

 

 

무튼 POST 방식은 네트워크 통신을 할때, 여러정보들을 일정 파라미터의 길이안에 넣어서 검색내용이 보이지 않게 하는 방식으로 검색할 내용이 Paylord 에 담겨 정보를 요청하게 된다.

 

 

뭔가 복잡해 보이지만 우리가 검색을 통해서 선택한 내용들이 사이트개발자가 원하는 형태로 변경된 형태일 뿐이다.

이 상태로는 바로 사이트에 요청할 수 없어 view source 탭을 클릭해 보길 바란다.

 

 

그럼 이런형태로 사람이 식별하기 좋은 형태에서 컴퓨터가 인식할 수 있는 형태로 변경이 된다.

 

preview 탭에 가게 되면, 이런식의 Json 형태의 데이터를 얻을 수 있게 된다.

 

 

이를 우리는 변수에 넣고 사이트에 요청하면 된다.

 

코드 진행을 보자.

 

1. 우리는 개발자도구를 통해서 데이터를 뿌려줄 Url을 확인하게 된다.

2. 페이로드를 통해서 우리가 검색할 검색식을 받아서 약간의 데이터 값들을 변수값으로 변경하여 유기적인 검색이 가능하게 수정하게 된다.

3. post 방식으로 받아온 값들을 Json파서를 통해서 값들을 분리시킨다.

 

4. 파서된 Json값을 어레이리스트에 추가한다.

 

Json값들은 보통  Json 형태의 배열도 담겨서 넘어 올때 변수에 담겨서 넣어와서 어레이리스트에 Json의 키값들을 추가할때는

 

Al.Add Json("응답변수")

 

 

식으로 하게 되는데, 이사이트에서 신기하게 변수에 담지않고 바로 값들을 넘겨주기에

 

 

Al.Add Json

 

이렇게만 해도 된다.

 

5. 어레이리스트의 키값들을 순환하면서 셀에 하나씩 해당되는 위치에 값들을 출력한다.

 

여기서 참고로 어레이리스트의 값들은 Object값이기에 이를 함수의 파라미터로 넘길때는 Cstr로  String형태로 변경하거나 함수에서 문자형태가 아닌 오브젝트 형태로 선언해줘야 한다.

 

6. 시 · 구 구분

시는 2자리

구는 5자리

동은 8자리로 행정구역을 구분짓기에 Json값의 [CD] 값에 따라 시·구를 구별지어 주면 크롤링이 완성이 된다.

 

서울시는 CD값이 11 / 종로구는 CD값이 11110 / 청운효자동은 CD값이 111110515,

이걸 분석하면 11[서울시]  / 11[서울시]110[종로구] / 11[서울시]110[종로구]515[청운효자동] 형태의 값을 표현함을 알 수 있다.  이것을 원래는 표나 리스트 형태로 만들어놓고 값을 비교하면서 시·구를 구분짓는 것이 정석이지만, 어차피 구 및에 하위 트리로 [동]이 표기 되기에 단순하게 글자수로만 [구]를 가져오게 된다.

Function sigu(rngx, str$)

    Select Case Len(str)
        Case Is = 2
            sigu = "서울시"
        Case Is = 5
            sigu = rngx.Value
        Case Else
            sigu = rngx(0, 2).Value
    End Select

End Function

 

 

7. 마지막은  365 Filter함수를 통해서 크롤링된 값들을 실제 점포수의 내림차순으로 데이터를 가공하게 되는데, 365가 아닌 분들은 이벤트를 걸어서 재가공해야 할것이다. 이것들은 본인 티스토리에 여러번 했기 때문에 참고하길 바란다.

 

=IFERROR(LET(
     list, SORT(CHOOSECOLS(FILTER(A7:L457,L7:L457=N2,"일치하는값이 없습니다."),2,1,9,10,11,12),3,-1),
     HSTACK(SEQUENCE(ROWS(list)),list)),"분석중")

 

 Filter함수를 통해서 [N2] 의 값이 [구/동] 인지 확인 해서 필터링된 값을 가져오게 된다.

가져온 값들은 CHOOSECOLS을 통해서 선택된 열의 값들만 가져오게 된다.

선택한 열중 3번째 값을 기준으로 내림차순하여 list 변수에 담게 된다.

Hstack으로 순번이 될 SEQUENCE 함수와 list 값을 나란히 출력하게 된다.

분석하는 처음에는 값이 없음으로 잠깐 에러가 발생하게 되는데, 미관상(?) 보기싫어서 iferror를 통해서 "분석중"이 표기 되도록 하였다.

 

크롤링을 자세하게 설명하다 보니까 글이 길어지긴 했지만, 코드만 보면 그리긴 코드가 아니니 본인 스타일로 다시 만져보길 바란다.

 

영업점포.xlsm

0.12MB