仮想通貨価格情報サイト「Cryptowatch」から価格と取引高の情報であるOHLCVデータを取得するVBAプログラムを公開したいと思います。
前回は、プログラムの主要な部分である公式APIからのJSON形式データの読み込みの仕方をお伝えしました。
前回の記事はこちら↓
第1回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
今回はプログラム全体をお伝えしたいと思います。
・環境設定
環境設定については以前の記事でまとめています。こちらをご覧ください。
webサイトからExcelにJSON形式データを読み込むための下準備
・VBAコード
流れとしては、任意のAPI URLでJSONデータを読取後に1行分ずつ一括でセルに入れています。
Option Explicit
Dim binsize As String
Dim tmp
Dim CryptoJSON As Object, CryptoNode
Dim HttpReq As Object, CandleJSON As String
Dim cwsheet As Worksheet
Dim js As Object
Dim objJSON As Object
Dim objJSON2 As Object
Dim TradeJSON As String
Dim url As String
Dim MaxRow As Long
Dim periods As Long
Dim Exchange As String
Dim pulldownList
'20180610
'Developed by: https://yare-excel-google-spreadsheet.blogspot.jp/
Sub Cryptowatch_JSON()
Dim i As Long
Dim j As Long
Application.StatusBar = "loading・・・"
Application.ScreenUpdating = False
Application.EnableCancelKey = xlErrorHandler
Range("G:M").Clear
Set cwsheet = ThisWorkbook.Worksheets("crypto_watch")
binsize = Cells(7, 2).Value
If binsize = "1m" Then
periods = 60
ElseIf binsize = "3m" Then
periods = 180
ElseIf binsize = "5m" Then
periods = 300
ElseIf binsize = "15m" Then
periods = 900
ElseIf binsize = "30m" Then
periods = 1800
ElseIf binsize = "1h" Then
periods = 3600
ElseIf binsize = "2h" Then
periods = 7200
ElseIf binsize = "4h" Then
periods = 14400
ElseIf binsize = "6h" Then
periods = 21600
ElseIf binsize = "12h" Then
periods = 43200
ElseIf binsize = "1d" Then
periods = 86400
ElseIf binsize = "3d" Then
periods = 259200
ElseIf binsize = "1w" Then
periods = 604800
Else: End If
Dim Exchange As String
Dim pair As String
Exchange = Cells(5, 2).Value
pair = Cells(6, 2).Value
url = "https://api.cryptowat.ch/markets/" & Exchange & "/" & pair & "/ohlc?periods=" & periods & "&after=1304287200"
'Query Api
Set HttpReq = CreateObject("MSXML2.XMLHTTP")
HttpReq.Open "GET", url, False
HttpReq.send
TradeJSON = HttpReq.responseText
Set HttpReq = Nothing
Set js = CreateObject("ScriptControl")
js.Language = "JScript"
'jsonにパースする関数を追加
js.AddCode "function jsonParse(s) { return eval('(' + s + ')'); }"
'追加した関数を実行して、結果を変数に格納する
Set CryptoJSON = js.CodeObject.jsonParse(TradeJSON)
Set objJSON = CallByName(CryptoJSON.result, periods, VbGet)
tmp = Split(objJSON, ",")
For i = 0 To UBound(tmp) \ 7
Set objJSON2 = CallByName(objJSON, i, VbGet)
tmp = Split(objJSON2, ",")
Range(Cells(1 + i, 7), Cells(1 + i, 13)) = tmp
Next
MaxRow = Cells(Rows.Count, 7).End(xlUp).Row
If cwsheet.Cells(8, 2).Value = "true" Then
cwsheet.Range(Cells(1, 7), Cells(MaxRow, 12)).Sort Key1:=cwsheet.Cells(1, 7), order1:=xlDescending
Else: End If
If cwsheet.Cells(9, 2).Value = "date" Then
MaxRow = Cells(Rows.Count, 7).End(xlUp).Row
For i = 1 To MaxRow
Cells(i, 7).Value = (Cells(i, 7).Value + 32400) / 86400 + 25569
Next
Range("G:G").NumberFormatLocal = "yyyy/mm/dd hh:mm"
Else: End If
’セルにエラーが出るのを無効にする
Dim Rng As Range
For Each Rng In ActiveSheet.Range(Cells(1, 8), Cells(MaxRow, 13))
If Rng.Errors.Item(xlNumberAsText).Value = True Then
Rng.Errors(xlNumberAsText).Ignore = True
End If
Next
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
全部で約100行です。
Cryptowatchのohlcデータは、
時間(TIME)、始値（OPEN)、高値（HIGH)、安値（LOW)、終値（CLOSE)、出来高(VOLUME)の他にもう一つデータが出てきますが、公式サイトではそのもう一つのデータが説明されていないため何のデータかは不明です。当プログラムではセルに表示するようにしています。
https://cryptowatch.jp/docs/api
・終わりに
これで今まで投稿してきた「CryptowatchからOHLCVデータを取得するVBAプログラムシリーズ」は終わりとなります。ここまで読んでいただきありがとうございます。
Bitmexの時とはデータの読み取り方が違うことに少し苦労しました。実際にはもっとシンプルなデータの読み取り方があるかもしれませんのであくまでも参考程度に見ていただければと思います。
また何かプログラムを作成したら公開しようと思っていますので当ブログを時々チェックしていただければ幸いです。
このVBAプログラムを使用したExcelアプリケーションの作り方をnoteにまとめる予定です。こちらも投稿したらお伝えしますので是非ご覧になってください。
