こんにちは。
仮想通貨価格情報サイト、Cryptowatchから価格と取引高の情報であるOHLCVデータをExcelで取得するVBAプログラムを全2回にわたって公開してきました。
用途は主にbotトレードに使用するbacktest用jsonファイルの作成と、エクセル上でのデータ分析です。
VBAプログラムを使用してExcelアプリケーションを作成する方法をnoteにまとめました。このnoteの通りに作成すればCryptowatchからOHLCVデータを読み込むExcelツール「エクセルウォッチ君」を作成できます。「エクセルウォッチ君」とは当Excelツールの名称となります。
シンプルなデザインにしてあり、操作方法も記述しておきました。JSONデータを読み込むExcelアプリケーションを作成する際の参考にしていただければと思います。
[エクセルウォッチ君] CryptowatchからOHLCVデータを取得するExcelアプリケーションの作り方
前回までの記事はこちら↓
第1回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
第2回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
2018/06/11
2018/06/10
第2回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
仮想通貨価格情報サイト「Cryptowatch」から価格と取引高の情報であるOHLCVデータを取得するVBAプログラムを公開したいと思います。
前回は、プログラムの主要な部分である公式APIからのJSON形式データの読み込みの仕方をお伝えしました。
前回の記事はこちら↓
第1回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
今回はプログラム全体をお伝えしたいと思います。
webサイトからExcelにJSON形式データを読み込むための下準備
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にまとめる予定です。こちらも投稿したらお伝えしますので是非ご覧になってください。
前回は、プログラムの主要な部分である公式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にまとめる予定です。こちらも投稿したらお伝えしますので是非ご覧になってください。
2018/06/09
第1回 CryptowatchからOHLCVデータを取得するVBAプログラム －Excel VBA
仮想通貨価格情報サイト「Cryptowatch」から価格と取引高の情報であるOHLCVデータを取得するVBAプログラムを公開したいと思います。
前回はBitmexからOHLCVデータを読み込むVBAプログラムを公開し、noteでメクセル君というExcelアプリケーションの作り方をお伝えしました。
Pythonなど他のプログラミング言語でもデータを取得できますが、今回もこのブログでは硬派にVBAにこだわっていきたいと思います。
About Cryptowatch
Cryptowatch is a cryptocurrency charting and trading platform owned by Kraken. Our system serves live data on 400+ markets to clients around the world.
CryptowatchはKrakenが所有する暗号化チャートと取引プラットフォームです。当社のシステムは世界中の顧客に400以上の市場に関するライブデータを提供しています。
bitflyer、bitfinex, bithumb, binance, Kraken, poloniex, mtgox, quoineなど他多数の仮想通貨取引所のローソク足のOHLCVデータを取得できます。
当VBAプログラムでもCryptowatchから取得できる全取引所のOHLCVデータを取得できます。
始値（OPEN)、高値（HIGH)、安値（LOW)、終値（CLOSE)、出来高(VOLUME)の頭文字をつなげたものです。
ローソク足では、１日、１週間、１ヶ月などの期間を定め、期間の開始時に付いた値段を始値、期間中の最も高い値段を高値、最も安い値段を安値、期間の最後に付いた値段を終値としています。
株式投資や、暗号通貨投資などで過去のOHLCVデータから将来の価格を予想するために使われたりします。
それでは、早速Cryptowatchの公式APIからOHLCVデータを取得するプログラムをお伝えします。
url = "https://api.cryptowat.ch/markets/" & Exchanges & "/" & pair & "/ohlc?periods=" & periods
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)
・API URL
(urlの例)
url = "https://api.cryptowat.ch/markets/binance/btcusdt/ohlc?periods=3600&after=1304287200"
APIのURLについてですが、periods=のあとに時間足の分数を入れます。1分足なら60, 1時間足なら3600といった感じです。
periodsの後ろにafterかbeforeを加えることもできます。=のあとにunixtimeを入れます。
afterとbeforeの両方を入れて試みましたが、エラーが出てしまいダメでした。ここからここまでという期間指定はできないようです。
JSON形式データを読み込む処理については、Bitmexの時に読み込んだ方法と同じになります。解説も書きましたので今回は省略します。
こちらの記事です。↓
第1回 仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム －Excel VBA
次回は当プログラムのVBAコード全体をお伝えします。
次回に続く
ビットバンクからOHLCVデータを取得＆表示するExcelツール 読取太郎
前回はBitmexからOHLCVデータを読み込むVBAプログラムを公開し、noteでメクセル君というExcelアプリケーションの作り方をお伝えしました。
Pythonなど他のプログラミング言語でもデータを取得できますが、今回もこのブログでは硬派にVBAにこだわっていきたいと思います。
・Cryptowatchとは
About Cryptowatch
Cryptowatch is a cryptocurrency charting and trading platform owned by Kraken. Our system serves live data on 400+ markets to clients around the world.
CryptowatchはKrakenが所有する暗号化チャートと取引プラットフォームです。当社のシステムは世界中の顧客に400以上の市場に関するライブデータを提供しています。
bitflyer、bitfinex, bithumb, binance, Kraken, poloniex, mtgox, quoineなど他多数の仮想通貨取引所のローソク足のOHLCVデータを取得できます。
当VBAプログラムでもCryptowatchから取得できる全取引所のOHLCVデータを取得できます。
・今回利用するAPI
Market Data REST API・OHLCVとは
始値（OPEN)、高値（HIGH)、安値（LOW)、終値（CLOSE)、出来高(VOLUME)の頭文字をつなげたものです。
ローソク足では、１日、１週間、１ヶ月などの期間を定め、期間の開始時に付いた値段を始値、期間中の最も高い値段を高値、最も安い値段を安値、期間の最後に付いた値段を終値としています。
株式投資や、暗号通貨投資などで過去のOHLCVデータから将来の価格を予想するために使われたりします。
それでは、早速Cryptowatchの公式APIからOHLCVデータを取得するプログラムをお伝えします。
VBAコード
JSON形式データを取得する処理のところから見ていきましょう。url = "https://api.cryptowat.ch/markets/" & Exchanges & "/" & pair & "/ohlc?periods=" & periods
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)
・API URL
(urlの例)
url = "https://api.cryptowat.ch/markets/binance/btcusdt/ohlc?periods=3600&after=1304287200"
APIのURLについてですが、periods=のあとに時間足の分数を入れます。1分足なら60, 1時間足なら3600といった感じです。
periodsの後ろにafterかbeforeを加えることもできます。=のあとにunixtimeを入れます。
afterとbeforeの両方を入れて試みましたが、エラーが出てしまいダメでした。ここからここまでという期間指定はできないようです。
JSON形式データを読み込む処理については、Bitmexの時に読み込んだ方法と同じになります。解説も書きましたので今回は省略します。
こちらの記事です。↓
第1回 仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム －Excel VBA
次回は当プログラムのVBAコード全体をお伝えします。
次回に続く
他にもエクセルツールを作成しています！
どんなに小さな額でもチップをいただけると嬉しいです！
Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU
Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f
当エクセルツールから表示される情報は、
内容の正確性、信頼性等を保証するものではありません。
これらの情報に基づいて被ったいかなる損害についても、当サイトは一切の責任を負いません。
投資・投機に関するすべての決定は、利用者ご自身の判断でなさるようお願いいたします。
登録:
投稿 (Atom)