엑세스(VBA)로 국내주식, 해외주식, 코인 현재가, 등락율 가져오기

안녕하세요. VBA로 크롤링이나 파싱을 여러가지 방법으로 하는데,

오늘은 방법을 알려드리는게 아니고, 코드 공개 위주로 하겠습니다. 원리까지 설명하자면 시간이 좀 길어질것 같군요.

올려드리는 코드 전문을 보시고 수정해서 사용하셔도 좋을듯 합니다.

 

폼은 이렇게 생겼습니다.

하단 동그라미 종목코드 부분에 코드를 넣고 현재가 버튼을 누르면 가격정보와 등락이 나옵니다.

우선 "VB 에디터 참조" 찾다가 한세월이니 레퍼런스를 보여드리겠습니다. 

우선 참조 목록에 있는 모든 걸 참조하세요.

저도 이게 다 필요한지는 모르겠지만, 하다 보니 이렇게 되더군요. 수정하면서 참조를 풀지 않은 것도 있을 수 있습니다. 전 인간이니까요 ㅎㅎ ^__^

 

가장 중요한 현재가 버튼의 코드를 아래와 같이 공개합니다.

Private Sub 버현재가_Click()

크롤링XML_OpenApi
Form_종목_s1.Requery

End Sub

그리고 크롤링XML_OpenApi 코드

Private Sub 크롤링XML_OpenApi()

Dim http As New XMLHTTP60
Dim html As New HTMLDocument
Dim rs As DAO.Recordset
Dim strURL As String
Dim rTxt As String, crtxt As String
Dim stNow As Long, stRate As Double, stBef As Double, stNow2 As Double
Dim 환율a As Double

Dim i As Integer, A As String
'주요지수
'환율--KRW=X
strURL = " https://query1.finance.yahoo.com/v8/finance/chart/KRW=X"
                http.Open "GET", strURL
                http.send
                rTxt = http.responseText
                ' ["34],[:58],[.46],[,44]
                stNow2 = CDbl(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "regularMarketPrice") + 18, 10), Chr(34), ""), Chr(58), ""), Chr(44), ""))
                stBef = CDbl(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "chartPreviousClose") + 18, 10), Chr(34), ""), Chr(58), ""), Chr(44), ""))
                stRate = CDbl(Format((stNow2 - stBef) / stNow2, "0.0000"))
                CurrentDb.Execute "update 환율 set 환율 = " & stNow2 & ", 등락퍼 = " & stRate & " where id = 1", dbSeeChanges
환율a = stNow2
'환율--KRW=X(끝)

Set rs = CurrentDb.OpenRecordset("select 종목코드 from 종목", dbOpenDynaset, dbSeeChanges)
     If rs.RecordCount Then
        rs.MoveFirst
                Do
                
                A = rs!종목코드
                For i = 0 To 9
                A = Replace(A, i, "")
                Next i
                
                If Nz(A, "") = "" Then
                strURL = "https://api.finance.naver.com/service/itemSummary.nhn?itemcode=" & "" & rs!종목코드 & ""
                http.Open "GET", strURL
                http.send
                rTxt = http.responseText
                stNow = CLng(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "now") + 5, 8), "," & Chr(34), ""), "d", ""), "i", ""))
                stRate = CDbl(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "rate") + 6, 8), "," & Chr(34) & "q", ""), "u", ""), "a", "")) / 100
                CurrentDb.Execute "update 종목 set 가격KRW = " & stNow & ", 등락퍼 = " & stRate & ", 환율적용 = " & stNow & ", 분류='국내' where 종목코드 = '" & rs!종목코드 & "'", dbSeeChanges
                ElseIf InStr(rs!종목코드, "KRW-") <> 0 Then
                strURL = "https://api.upbit.com/v1/ticker?markets=" & "" & rs!종목코드 & ""
                http.Open "GET", strURL
                http.send
                rTxt = http.responseText
                stNow = CDbl(Split(Split(rTxt, """trade_price"":")(1), ",")(0))
                stRate = CDbl(Mid(rTxt, InStr(rTxt, "signed_change_rate") + 20, 7))
                CurrentDb.Execute "update 종목 set 가격KRW = " & stNow & ", 등락퍼 = " & stRate & ", 환율적용 = " & stNow & ", 분류='Coin' where 종목코드 = '" & rs!종목코드 & "'", dbSeeChanges
                Else '해외
                strURL = " https://query1.finance.yahoo.com/v8/finance/chart/" & "" & rs!종목코드 & ""
                http.Open "GET", strURL
                http.send
                rTxt = http.responseText
                ' ["34],[:58],[.46],[,44]
                stNow2 = CDbl(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "regularMarketPrice") + 18, 10), Chr(34), ""), Chr(58), ""), Chr(44), ""))
                stBef = CDbl(Replace(Replace(Replace(Mid(rTxt, InStr(rTxt, "chartPreviousClose") + 18, 10), Chr(34), ""), Chr(58), ""), Chr(44), ""))
                stRate = CDbl(Format((stNow2 - stBef) / stNow2, "0.0000"))
                CurrentDb.Execute "update 종목 set 가격USD = " & stNow2 & ", 등락퍼 = " & stRate & ", 환율적용 = " & stNow2 * 환율a & ", 분류='해외' where 종목코드 = '" & rs!종목코드 & "'", dbSeeChanges
                End If
        rs.MoveNext
        Loop Until rs.EOF
    End If
    

End Sub

많은 것들이 들어 있는 코드라 연구해보실만 할겁니다.

특히나 국내주식, 해외주식, 코인 URL은 오랜시간 동안 찾은 URL이라 VBA사용자 분들께 도움이 될듯 싶습니다.

대부분 엑셀에서 바로가져오는 함수나, 파이썬으로 찾아 오는 방법은 많지만 엑세스를 이용해서 가져오는 부분은 레퍼런스를 찾기 어렵더군요. 

 

테이블 구성입니다.

숫자형은 소숫점이 필요없는 가격KRW, 환율적용은 Long으로 나머지는 소숫점 2~4자리 적용이라 Double로 설정했습니다.

 

하위 폼의 구조는

이렇게 되어 있습니다.

그리고 하위폼의 코드는 종목코드와 종목명에 아래와 같이 되어있습니다.

Option Compare Database
Option Explicit

Private Sub 종목명_AfterUpdate()
DoCmd.RunCommand acCmdSave
End Sub

Private Sub 종목코드_AfterUpdate()
DoCmd.RunCommand acCmdSave
End Sub

 

어떻게 설계되었는지 모두 알려드렸고,

 

어떻게 작동하는지 스크린샷으로 보여드리겠습니다.

여기에 없는 코드로 입력을 해야겠지요?

코인 1개, 국내주식 1개, 해외주식 1개의 코드가 필요합니다.

 

코인: 솔라나 KRW-SOL

국내주식: 카카오뱅크 323410

해외주식: 테슬라 TSLA

 

입력을 하고, 이름은 정보를 가져오는데 필요한게 아니고, 내가 알아보기 좋게 하려고 적어 놓는것이니 코드만 넣고 실행하는걸 보여드리겠습니다. (입력은 추가를 하고 싶으면 하단 빈 동그라미 부분에 , 수정을 하고 싶으면 원래 이름이 있던 자리에 입력하시면 됩니다.)

이제 현재가 버튼을 클릭해보겠습니다.

제가 정렬을 분류기준으로 해놔서 V체크 한것 같이 빈곳이 보입니다.

 

그냥 두기 뭐하니까 이름을 입력해서 마무리 하겠습니다. 끝!