업비트 api 활용 모든 코인 시세 정리하기 - VBA

업비트 api를 보면서 많은 내용이 있었습니다.

 

그중 나는 과연 어떤 코인을 사는 게 좋을지 생각하다가 52주 신고가 , 신저가 전일 종가 , 거래량을 정리하여 선택해보려고 합니다.

 

이건 순전히 저의 생각이지만 비트코인이 오른다면 상대적으로 가격이 떨어진 코인은 조만간 다시 오르는(?) 느낌적인 느낌(?) 이 있어서 정리해보는 엑셀을 만들어 본 것입니다.

 

---

업비트의 마켓코드 불러오기

업비트에 존재하는 모든 마켓 코드를 확인해야 합니다.

 

업비트 OPEN API 사이트에 들어가면 레퍼런스를 확인할 수 있습니다.

REST API를 이용한 업비트 시세 수신 (upbit.com)

업비트 개발자 센터

그중 마켓 리스트를 확인하기 위한 API가 있습니다.

 

https://api.upbit.com/v1/market/all

해당 주소를 입력하였더니 이상한 텍스트만 엄청나게 나옵니다.

 

하지만 거기에 코인 이름과 market이라는 값들이 있습니다.

 

이것들이 결국 현재 업비트에서 거래 가능한 코인 리스트이고 마켓 코드인 것 같습니다.

 

이것을 먼저 엑셀로 정리하겠습니다.

 

해당 주소에서 나오는것을 모두 복사해서 붙여서 텍스트 나누기와 단순 반복작업 매크로를 사용하여 정리하였습니다.

 

 

 

 

 

엄청나게 개수가 많습니다.

 

이렇게 해서 한번 더 정리해서 마켓 코드와 한글 이름만 두고 정리했습니다.

 - 단순 반복 VBA매크로와 글자 찾아 변경하기를 사용하면 쉽게 해결할 수 있습니다.

VBA 배우기 기초 - 상대 참조 및 녹화 코드 분석 (tistory.com)

VBA 배우기 기초 - 상대 참조 및 녹화 코드분석

엑셀 텍스트 변경 방법 모든 것 - 바꾸기 , replace , substitute + 와일드카드 (tistory.com)

엑셀 텍스트 변경 방법 모든것 - 바꾸기 , replace , substitute + 와일드카드

그러면 다 정리가 되었습니다.

 

---

API를 통한 정보 조회

API 레퍼런스에서 시세 Ticker 조회에서 Response를 찾아보면 저에게 필요한 정보들이 존재합니다.

제가 도입부에서 생각했던 52주 신고가 , 날짜 , 52주 신저가 , 날짜 , 전일 종가 , 거래량 정보를 불러오겠습니다.

prev_closing_price	전일 종가	Double
acc_trade_volume	누적 거래량(UTC 0시 기준)	Double
highest_52_week_price	52주 신고가	Double
highest_52_week_date	52주 신고가 달성일	String
lowest_52_week_price	52주 신저가	Double
lowest_52_week_date	52주 신저가 달성일	String

이렇게 데이터 값을 사용하려고 합니다.

 

앞서 만든 코인 market을 입력하여 api 주소로 접속하였습니다.

 

https://api.upbit.com/v1/ticker?markets=KRW-BTC

 

 

 

글자를 보면 제가 필요한 52주 관련된 내용과 모두 존재합니다.

 

그러면 저 많은 것을 복사 붙여 넣기 하면 되겠지만 모든 코인을 다 반복 작업할 수 없기 때문에 VBA로 해결하겠습니다.

 

VBA에서 웹크롤링에 사용되는 winhttprequest를 사용하여 해당 웹페이지를 크롤링하여 원하는 텍스트만 들고 오겠습니다.

 

가장 먼저 VBA에서 참조할 레퍼런스 프로젝트로 Microsoft WinHttp Services , Version 5.1을 참조하겠습니다.

해당 메뉴는 VBA메뉴에서 도구> 참조에 들어가서 추가하시면 됩니다.

 

이렇게 크롤링할 수 있게 추가하였습니다.

 

그러면 코드를 작성하겠습니다. (아래 코드를 복붙 하셔도 됩니다.)

Sub CoinInfo()

Range("a2:g100000").ClearContents

Dim Value(2 To 7)

i = 1

With Worksheets("마켓정보")
    Do Until .Cells(i, 1) = ""
        KoreaName = .Cells(i, 2)
        UpbitCoinSearch .Cells(i, 1), Value
        TextPrint KoreaName, Value, i, Markets
        i = i + 1
    Loop
    
    
End With

End Sub

Sub UpbitCoinSearch(Markets, Value)

Url = "https://api.upbit.com/v1/ticker?markets=" & Markets

Dim WH As New WinHttp.WinHttpRequest

WH.Open "get", Url

WH.SetRequestHeader "User-Agent", "Mozilla/5.0 (Android; Mobile; rv:13.0) Gecko/13.0 Firefox/13.0"

WH.Send

WH.WaitForResponse

If WH.Status <> 200 Then
DoEvents
Application.Wait (Now() + TimeValue("0:00:01"))
UpbitCoinSearch Markets, Value
End If


Result = Split(WH.ResponseText, ",")

Set WH = Nothing

For Each DT In Result
    ColNum = 0
    If InStr(DT, "prev_closing_price" & """") > 0 Then
        ColNum = 2
    ElseIf InStr(DT, "acc_trade_volume" & """") > 0 Then
        ColNum = 3
    ElseIf InStr(DT, "highest_52_week_price" & """") > 0 Then
        ColNum = 4
    ElseIf InStr(DT, "highest_52_week_date" & """") > 0 Then
        ColNum = 5
    ElseIf InStr(DT, "lowest_52_week_price" & """") > 0 Then
        ColNum = 6
    ElseIf InStr(DT, "lowest_52_week_date" & """") > 0 Then
        ColNum = 7
    End If
    
    If ColNum > 0 Then
        RT = Split(DT, ":")
        Value(ColNum) = RT(1)
    End If
    
Next




End Sub

Sub TextPrint(CoinName, Value, RowCnt, Markets)
Cells(RowCnt + 1, 1) = CoinName
ColNum = 1
For Each RV In Value
    ColNum = ColNum + 1
    Cells(RowCnt + 1, ColNum) = RV
Next


End Sub

 

CoinInfo를 매크로 실행하면 실행됩니다.

 

사실 VBA를 배우고 있다 보니 변수 설정이라던지 파싱 하는 방법들이 상당히 서투릅니다.

 

더 잘하는 분들도 많지만 제 스스로 만들어보는 거에 뿌듯함을 느껴봅니다~

 

업비트조회.xlsm

0.03MB

---

2021년 4월 19일 추가 내용

업비트 api를 보던 중 새로운 사실을 알게 되어서 조금 더 최적화되었습니다.

markets이고 쉼표로 구분이 될 수 있습니다.

 

그래서 markets로 쿼리를 바꾸고 한 번에 모든 코인을 불러와 파싱 했습니다.

 

업비트조회_1.xlsm

0.04MB

혹시라도 공부하실 분은 VBA에 모듈 1번은 위 코드 내용이고 모듈 2번은 새로 만든 코드입니다.

 

차이는 하나씩 코인 시세를 확인하다 보니 매번 인터넷 접속으로 딜레이가 많이 생겼습니다.

 

한 번의 인터넷 접속으로 해결할 수 있게 되어서 속도가 엄청나게 증가하게 되었습니다.

 

일정 시간 간격으로 갱신하는 버전도 추가해봤습니다.

 

VBA 일정 시간 간격으로 업비트 코인가격 갱신하기 (tistory.com)

VBA 일정 시간 간격으로 업비트 코인가격 갱신하기