[bithumb API 2]JSON 데이터 편집
JSON 데이터 구조 파악
[bithumb API]엑셀로 마켓 코드 조회에서 구현한 VBA를 통해 빗썸에서 데이터를 받아보면 아래 그림 1과 같은 복잡한 형식의 데이터가 나온다.
그림 1과 같은 데이터의 표현 방식이 JSON 형식이다. 복잡하게 보이는 그림 1의 데이터를 보기 쉽게 나타내면 아래 그림 2와 같은데, 데이터 전체를 대괄호("[]")로 묶고, 개별 데이터를 중괄호("{}")로 구분한 뒤 중괄호 안에 각각의 데이터를 "키값:데이터"로 입력하는 형식이다.
JSON 형식을 엑셀 딕셔너리 형식으로 변환하기
엑셀에는 위 그림 2와 같은 JSON 데이터를 분석하는 기능이 내장되어 있지 않다. 따라서 데이터 편집을 용이하게 하기 위해서 엑셀의 딕셔너리 형식으로 데이터를 변환해 줄 필요가 있다.(엑셀 VBA 딕셔너리(Dictionary) 기능 사용하기 참조)
변환 작업은 먼저 JSON 형식 데이터에서 대괄호를 제거하는 것부터 시작한다. 그리고 개별 데이터가 중괄호로 구분되어 있기 때문에 개별 데이터는 "},{"를 기준으로 나눌 수 있다. 따라서 Split(JSON데이터, "},{" )를 사용해서 개별 데이터 항목을 만들 수 있는데, 그러면 아래 그림 3의 1단계가 끝이 난다. 개별 데이터 항목에 키값을 0번부터 순서대로 부여하고 각 키 값에 개별데이터를 추가해 주는 작업을 항목별로 반복하면 2단계가 모두 끝나고 JSON 형식은 딕셔너리 형식으로 바뀐다.
위 그림 3의 과정을 코드로 만들어보면 아래와 같다.
' JSON 데이터를 딕셔너리(Dictionary)로 변환하는 함수
Public Function 딕셔너리변환(응답결과 As String, 키값 As Variant) As Object
Dim 딕셔너리 As Object ' 딕셔너리 객체 선언 (Key-Value 저장용)
Dim 항목 As String ' 개별 JSON 객체 저장 변수
Dim Items() As String ' 딕셔너리 value를 배열로 정의
Dim 최대번호 As Long ' 키값 배열의 마지막 인덱스
' Scripting.Dictionary 객체 생성
Set 딕셔너리 = CreateObject("Scripting.Dictionary")
' JSON 배열에서 대괄호 제거 (JSON 시작 "[" 및 끝 "]" 제거)
응답대괄호제거 = Mid(응답결과, 2, Len(응답결과) - 2)
' JSON 배열을 개별 JSON 객체로 분리 (각 객체는 "},{" 로 구분됨)
응답배열분리 = Split(응답대괄호제거, "},{")
' 키값 배열의 크기 계산 (UBound: 마지막 인덱스)
최대번호 = UBound(키값)
' JSON 배열을 순회하면서 딕셔너리에 변환하여 저장
For i = LBound(응답배열분리) To UBound(응답배열분리)
' 개별 JSON 객체의 중괄호 제거
항목 = Replace(응답배열분리(i), "{", "")
항목 = Replace(항목, "}", "")
' 딕셔너리 value 개수를 정의 = 입력된 키 값의 크기
ReDim Items(최대번호)
' 각 키값에 대한 Value 추출
For j = 0 To 최대번호
' JSON 객체에서 해당 키에 해당하는 값 추출
' 아래 JSON 개별 키값에 대한 데이터 추출 목차에서 "아이템추출"함수 설명
Items(j) = 아이템추출(항목, 키값(j))
Next
' 변환된 데이터를 딕셔너리에 추가 (Key: i, Value: Items 배열)
딕셔너리.Add i, Items
Next
' 변환된 딕셔너리를 반환
Set 딕셔너리변환 = 딕셔너리
End Function
JSON 개별 키값에 대한 데이터 추출
JSON 형식 데이터에서 중괄호 속의 데이터는 "키값:데이터" 와 같은 형식으로 되어 있다. 위 그림 3의 2단계에서 최종 딕셔너리 형태는 JSON 형식 데이터에서 개별 키값에 대응하는 데이터로만 이루어진 형태이다. 따라서 JSON 형식 데이터에서 키값에 해당하는 데이터만 뽑아내는 함수가 필요하고 위 코드에서 "아이템추출"이라는 이름의 함수로 표현되었다.
"아이템추출"함수의 구조는 아래 그림 4와 같이 나타낼 수 있다. 예를 들어 함수의 키값으로 "korean_name"을 받았다면, "korean_name"의 위치번호 + "korean_name" 문자열의 수 + 3(":")이 아이템이 시작되는 문자열의 수이고, 이후 콤마(",")가 있는 위치에 1을 빼면 아이템이 끝나는 문자열의 수라는 성질을 이용해서 "비트코인"이라는 아이템을 뽑아낼 수 있다.
위 그림 4의 과정을 코드로 나타내면 아래와 같다.
Public Function 아이템추출(항목 As String, 키값)
' 문자열에서 키값(예: "market")의 시작 위치 찾기
시작점 = InStr(항목, 키값)
' 키값의 길이 (예: "market" → 6)
키값크기 = Len(키값)
' 항목(전체 JSON 문자열)의 길이 계산
항목끝점 = Len(항목)
' 키값 다음에 오는 값의 시작 위치 찾기 (": "를 포함하므로 +3)
' 예: "market":"KRW-BTC" → "KRW-BTC"의 시작 위치 계산
아이템시작점 = 시작점 + 키값크기 + 3
' 아이템 값의 끝을 찾기 위해 다음 콤마(,) 위치 검색
아이템콤마위치 = InStr(아이템시작점, 항목, ",")
' 값의 끝 위치 결정
' 다음 콤마가 없으면 문자열 끝까지 추출, 있으면 콤마 위치가 값의 끝
If 아이템콤마위치 = 0 Then
아이템끝점 = 항목끝점 ' 마지막 요소이면 전체 길이로 설정
Else: 아이템끝점 = 아이템콤마위치 ' 값의 끝을 콤마 위치로 설정
End If
' 아이템 값의 길이 계산
아이템길이 = 아이템끝점 - 아이템시작점 - 1
' 최종적으로 값만 추출하여 반환
아이템추출 = Mid(항목, 아이템시작점, 아이템길이)
End Function
변환된 데이터를 시트에 기록하기
위 그림 3과 같은 딕셔너리 데이터가 만들어지면, 딕셔너리 각각의 키값의 항목들을 엑셀 시트의 컬럼에 기록하는 반복문을 이용에서 데이터를 입력할 수 있다.
Sub 마켓코드조회()
...(생략)...
' 작업시트는 코인시트로 정의
' 응답결과라는 JSON 형식 데이터를 이용하여 작업
' 키값은 JSON 형식의 키값 여기서는 "market", "korean_name", "english_name" 이 됨
' JSON 응답을 딕셔너리로 변환
Set 응답딕셔너리 = 딕셔너리변환(응답결과, 키값)
' 엑셀에서 데이터를 입력할 시작 행 설정
작업ROW = 1
컬럼작업 = 1
' 첫 번째 행에 키값(헤더) 입력
For Each i In 키값
코인시트.Cells(작업ROW, 컬럼작업) = i ' A1, B1, C1 등 헤더 입력
컬럼작업 = 컬럼작업 + 1 ' 다음 열로 이동
Next
' 데이터 입력을 위해 다음 행으로 이동
작업ROW = 작업ROW + 1
' 딕셔너리에서 데이터를 가져와 엑셀에 입력
For Each i In 응답딕셔너리.Keys()
For j = 0 To UBound(키값)
코인시트.Cells(작업ROW, j + 1) = 응답딕셔너리(i)(j) ' 각 행에 데이터 입력
Next
작업ROW = 작업ROW + 1 ' 다음 행으로 이동
Next
' HTTP 요청 객체 해제 (메모리 정리)
Set http요청 = Nothing
빗썸 마켓 코드 조회 API를 통해서 JSON 데이터를 받고, JSON 파일을 엑셀의 딕셔너리 형태로 변환해서 시트에 기록하는 파일은 아래와 같이 구현할 수 있다.