[심화방] VBA_심화_100제 #9 [ JsonConverter : 환율불러오기 ]

 

예전에 크롤링을 배우기 위해서

https://www.youtube.com/watch?v=4hoXQMi2SSk&t=40s 

한때 엑셀장인 촬스님채널에서 크롤링을 단계적으로 공부하였다. 당시 촬스님은 구독자분들의 요청이나 질문사항을 정리해서 당신의 크롤링 기법으로 강의를 하시곤 했다. 그중 하나인 환율을 가져오는 구문을 당시 촬스님이 하신 익스플로러 기반의 크롤링이 아닌 셀레니움 기반으로 환율을 가져오는 것을 이벤트로 진행하는 구문을 만들어 보았다.

이번 구문은 xmlHttp나 winHttp 방식으로 비브라우저 방식으로 하는것이 정석이고 정답이다. 허나 지금 우리가 셀레니움을 계속 공부하고 있어 셀레니움과 JsonConverter 조합으로 풀어보려고 한다.

 

여기에 비하인드 스토리가 있는데 본인이 가지고 있던 JsonConverter가 무슨 일인지 오류가 발생하여 급하게 Json2Array라는 모듈을 담아 해결하였다. 해결하고 나니 다시 묻게 되었다. 왜 오류가 났는가...이유를 알아야 적지적소에 사용하는 모듈을 달리해야 하기 때문이었다...

 

 

알고보니...본인이 가지고 있던 컨버터가 문제였던걸로 .....

 

Url:  https://www.oanda.com/currency-converter/en/?from=USD&to=EUR&amount=1

여기서 평균환율을 가져와야 한다.

 

F12 개발자 도구로 들어가서 Ctrl + F 를 눌러서 환율 0.92499 을 검색을 해보자

 

 

그리고 헤더값을 보자

 

리퀘스트 URL을 보면

https://fxds-public-exchange-rates-api.oanda.com/cc-api/currencies?base=USD"e=EUR&data_type=general_currency_pair&start_date=2023-01-18&end_date=2023-01-19

 

이렇게 나오는데 기준통화 / 변동통화 / 시작날짜 / 종료날짜를 넣으면 Json 형태로 값을 반환해준다.

저 Url을 주소창에 넣어보자

 

그럼 이렇게 역시 Json구조의 데이터를 반환하는것을 볼 수 있는데 이걸 JsonConverter로 파싱해서 가져오면 된다.

 

자 그러면 Json 구조를 살펴보자

이구조는 

{ A : [ { a : "1",b:"2"} ] } 이런식으로 Json 의 배열 형태를 띄고 있다.

 

 

strUrl = "https://fxds-public-exchange-rates-api.oanda.com/cc-api/currencies?base=" _
        & [D2] & "&quote=" & [D3] & "&data_type=chart&start_date=" & DateAdd("m", -3, CDate([C4])) + 2 & "&end_date=" & [C4]

Sel.AddArgument "--headless"                                      
Sel.Start "chrome"                                                 
Sel.Get strUrl                                                   

Str = Sel.FindElementByCss("body").Text

debug.print Str

직접실행창에서 찍어보면

이러한 값을 출력하게 된다

 

지역창을 보면 추출된Json은 "response" 라는 키를 갖게 되고

이것을 어레이리스트에 담으면

 Set Json = JsonConverter.ParseJson(Str)
   
 Al.Add Json("response")

각 어레이리스트에 값과 아이템이 담기게 된다.

 

여기서 우리는 필요한 값만 파싱해서 출력을 하면된다.

For Each Alkey In Al.Item(0)                                     
            
     rngX(1, 1) = CDate(Left(Alkey("close_time"), 10)) + 1      
     rngX(1, 2) = Alkey("base_currency")                         
     rngX(1, 3) = Alkey("quote_currency")                       
     rngX(1, 4) = Format(Alkey("average_bid"), "0.00000")        

     Set rngX = rngX.Offset(1)

Next Alkey

Sub 환율()

    Dim Sel As New Selenium.WebDriver
    Dim rngX As Range: Set rngX = [b7]
    Dim rngAll As Range: Set rngAll = [b6].CurrentRegion
    Dim Json As Object
    Dim Al As Object: Set Al = CreateObject("system.collections.arraylist")  '= ArrayList 선언
    Dim Alkey                                                                '= 어레이리스트 키값 순환
    Dim strUrl$, Str$
    Dim bln As Boolean
        
    Application.ScreenUpdating = False
    
        Haja_Format rngAll.Offset(1), bln
        
        strUrl = "https://fxds-public-exchange-rates-api.oanda.com/cc-api/currencies?base=" _
        & [D2] & "&quote=" & [D3] & "&data_type=chart&start_date=" & DateAdd("m", -3, CDate([C4])) + 2 & "&end_date=" & [C4]

         Sel.AddArgument "--headless"                                        '= 헤드리스 모드
         Sel.Start "chrome"                                                  '= 크롬으로 진행
         Sel.Get strUrl                                                      '= Url 이동
        
         Str = Sel.FindElementByCss("body").Text                             '= 셀레니움으로 Body태그의 텍스를 모두 Str 에 담아라
        
         Set Json = JsonConverter.ParseJson(Str)                             '= Json.Converter로 파싱해라
   
         Al.Add Json("response")                                             '= Json 컨버터로 파싱한 키값을 어레이리스트에 추가해라
         
            For Each Alkey In Al.Item(0)                                     '= 어레이리스트의 키값들을 순환하면서 각 키값에 해당하는 아이템을 출력하시오
            
                 rngX(1, 1) = CDate(Left(Alkey("close_time"), 10)) + 1       '= 날짜 출력
                 rngX(1, 2) = Alkey("base_currency")                         '= 기준통화
                 rngX(1, 3) = Alkey("quote_currency")                        '= 변동통화
                 rngX(1, 4) = Format(Alkey("average_bid"), "0.00000")        '= 평균환율
            
                 Set rngX = rngX.Offset(1)
                
            Next Alkey
         
            Haja_Format rngAll, bln
   
    Application.ScreenUpdating = True
    Sel.Close
    MsgBox "3개월간의 환율을 모두 출력하였습니다."
         
End Sub

생각보다 간단하지만 Jsonconverter있기에 가능한 이야기이다. 

Json은 객체지향 프로그램을 사용하고 있는 우리들에게 반드시 정복해야 하는 녀석이다. 

JsonConverter를 뜯어보려하지 않아도 된다. 다른언어들도 외부라이브러리를 가져와서 사용하는데 우린 그들이 처리할 수 있도록 Raw 데이터만 정확하게 넣어주면 된다.

Json 그까이꺼 대충......

몇번 Json 으로 하다보면 야도 그냥 쭈글이가 될테니 그때까지 개고생하자

 

환율(컨버터).xlsm

0.30MB