VBA
[VBA] 일자별 환율 크롤링하기
일등미노왕국
2022. 10. 13. 17:06
오늘은 크게 두가지로 분류하여 작업하였다.
첫째는 체크박스의 선택값과 월을 변경할 때마다 주말의 색상을 변경하는 조건부 서식과
두번째는 대신 증권에서 일자별 환율을 가지고 온후 공휴일에는 값이 없기 때문에 그 빈 값을 이전 값으로
채워주는 코드를 진행하였다.
체크박스의 연동값은 체크박스의 영역에 흰색처리하여 코드를 진행하였다.
여기에 환율과 매출을 연동하여 일 매출을 구하는 구문이지만 여기에서는 조건부서식과 환율정도만 다루려고 한다.
더보기
Sub 체크박스_연결()
Dim chkBox As Excel.CheckBox
Const r& = 1
Const c& = 1
For Each chkBox In ActiveSheet.CheckBoxes
With chkBox
.LinkedCell = .TopLeftCell.Address
End With
Next
End Sub
Sub 환율()
Dim strUrl$
Dim rngA As Range
Dim rngX As Range
Dim rngAll As Range: Set rngAll = Range([d4], Cells(Rows.Count, "d").End(3))
Dim UsaRange As Range: Set UsaRange = [m3:m33]
Dim ChiRange As Range: Set ChiRange = [n3:n33]
Dim data
Dim Temp
Dim Obj_table As Object
Dim xmlHttp As Object: Set xmlHttp = CreateObject("msxml2.xmlhttp")
Dim Html As Object: Set Html = CreateObject("HtmlFile")
Dim i&, N&
Application.ScreenUpdating = False
Sheets("달러").[a1].CurrentRegion.Offset(1).ClearContents '= 영역 초기화
Sheets("위안화").[a1].CurrentRegion.Offset(1).ClearContents
Sheets("쇼핑몰").[m3:n33].ClearContents
For i = 0 To 1
strUrl = "https://www.daishin.com/g.ds?m=1071&p=2536&v=1875" '= 대신증권 일자별 고시
If i = 0 Then
N = 0 '= 달러
Else
N = 14 '= 위안화
End If
data = "base_nation=" & N & "&exch_nation=1" '= Payload Post방식
With xmlHttp '= xmlHttp를 통해서
.Open "Post", strUrl, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.Send (data)
Html.body.innerhtml = .responsetext '= Html.body.innerhtml에 담아라
End With
Set Obj_table = Html.getElementByid("excel").getElementsByTagName("tbody")(0).getElementsByTagName("tr")
'= 고시 환율 테이블을 담아라
For Each Obj In Obj_table '= 고시환율 테이블을 순환해라
Temp = Split(Obj.innertext, vbCrLf) '= 고시환율 테이블에서 날짜 / 환율 / 전일가를 분리해라
If N = 0 Then '= 달러일 경우
Sheets("달러").Cells(Rows.Count, "a").End(3)(2) = Temp(1)
Sheets("달러").Cells(Rows.Count, "b").End(3)(2) = Temp(3)
Else '= 위안화일 경우
Sheets("위안화").Cells(Rows.Count, "a").End(3)(2) = Temp(1)
Sheets("위안화").Cells(Rows.Count, "b").End(3)(2) = Temp(3)
End If
Next Obj
If N = 0 Then '= 달러일 경우
UsaRange = Application.VLookup(Sheets("쇼핑몰").[k3:k33], Sheets("달러").[A2:B500], 2, 0)
'= 달러시트에서 해당 환율을 가져와라
For Each rngA In UsaRange '= 달러 영역을 순환해라
If rngA.Formula = "#N/A" Then rngA = "=R[-1]C" '= 달러값이 에러일 경우 바로 위의 값을 참조해라
If rngA = "달러" Or rngA = "위안화" Then rngA = M_chk(rngA, N)
'= 환율을 참조한 값이 달러 / 위안화 일경우 지난 환율을 조회해라
Next rngA
Else '= 위안화 일우
ChiRange = Application.VLookup(Sheets("쇼핑몰").[k3:k33], Sheets("위안화").[A2:B500], 2, 0)
'= 위안화 시트에서 해당 환율을 가져와라
For Each rngA In ChiRange '= 위안화 영역을 순환해라
If rngA.Formula = "#N/A" Then rngA = "=R[-1]C" '= 위안화 값이 에러일 경우 바로 위의 값을 참조해라
If rngA.Formula = "달러" Or rngA = "위안화" Then rngA = M_chk(rngA, N)
'= 환율을 참조한 값이 달러 / 위안화 일경우 지난 환율을 조회해라
Next rngA
End If
Next i
Application.ScreenUpdating = True
End Sub
Function M_chk(rngA As Range, N&)
Dim sDate As Range
Dim Temp As Range
Set sDate = [k3] '= 매월의 첫날을 sDate로 선언
Set Temp = [o3] '= 환율이 있는 날까지 날짜를 이전으호 회기해야 하는 임시 장소
Temp = sDate - 1
Do
If N = 0 Then '= 달러일경우
rngA = Application.VLookup(Temp, Sheets("달러").[A2:B500], 2, 0) '= 환율값에서 환율값을 가져와라
Else '= 위안화일 겨우
rngA = Application.VLookup(Temp, Sheets("위안화").[A2:B500], 2, 0) '= 환율값에서 환율값을 가져와라
End If
If rngA.Formula = "#N/A" Then '= 환율값이 에러이면 / 환율값이 없다면
Temp = Temp - 1 '= 날짜를 하루 이전으로 수정해라
ElseIf rngA <> "달러" Or rngA <> "위안화" Then '= 환율이 표시된다면
Exit Do '= Do 문을 탈출해라
End If
Loop
M_chk = rngA '= 함수값을 반환해라
End Function
