やれExcel&#12289;やれGoogle スプレッドシート

5月のビットコインのデータ 2013～2017年の5月のビットコイン(BTC)の収益率と標準偏差をエクセルで求める

どんなに小さな額でもチップをいただけると嬉しいです！

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

当エクセルツールから表示される情報は、

内容の正確性、信頼性等を保証するものではありません。

これらの情報に基づいて被ったいかなる損害についても、当サイトは一切の責任を負いません。

投資・投機に関するすべての決定は、利用者ご自身の判断でなさるようお願いいたします。

2018/05/31

6月のビットコインのデータ 2013～2017年の6月のビットコイン(BTC)の収益率と標準偏差をエクセルで求める

今回はビットコイン(BTC)の2013年から2017年の6月の終値価格データから価格推移グラフを作成し日次収益率とSD(標準偏差)を求めたいと思います。

データはbitbankのAPIを利用してエクセルに読み込みました。

チャートは日足表示です。

・2013年6月のビットコイン(BTC)の価格推移チャート

・2014年6月のビットコイン(BTC)の価格推移チャート

・2015年6月のビットコイン(BTC)の価格推移チャート

・2016年6月のビットコイン(BTC)の価格推移チャート

・2017年6月のビットコイン(BTC)の価格推移チャート

過去の価格推移の傾向を見てみると、どの年もバラバラな感じがします。

・日次収益率と標準偏差

日次平均収益率は日次収益率の平均値、
maxは日次収益率の最大値、
minは日次収益率の最小値、
sdは標準偏差です。

4月のビットコインのデータ！2013～2017年の4月のビットコイン(BTC)の収益率と標準偏差をエクセルで求める

3月のビットコインのデータ！2013～2017年の3月のビットコイン(BTC)の日次収益率と標準偏差をエクセルで求める

2018/05/30

メクセル君の作成の仕方をnoteに投稿しました。

こんにちは。

仮想通貨取引所、Bitmexから価格と取引高の情報であるOHLCVデータをExcelで取得するVBAプログラムを全4回にわたって公開してきました。

そして、そのVBAプログラムを使用してExcelアプリケーションを作成する方法をnoteにまとめました。このnoteの通りにすればBitmexからOHLCVデータを読み込むExcelツール「メクセル君」を作成できます。「メクセル君」とは当Excelツールの名称となります。

シンプルなデザインにしてあり、操作方法も記述しておきました。JSONデータを読み込むExcelアプリケーションを作成する際の参考にしていただければと思います。

[メクセル君] BitmexからOHLCVデータを取得するExcelアプリケーションの作り方

前回までの記事はこちら↓
第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第3回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第4回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

Bitmexへの登録はこちらからどうぞ！1分で登録できます！
https://www.bitmex.com/register/oDtFNI

2018/05/28

第4回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

仮想通貨取引所、Bitmexから価格と取引高の情報であるOHLCVデータをExcelで取得するVBAプログラムを公開したいと思います。

前回までは、価格情報のJSON形式データを取得し、Excelシート上にデータを表示するプログラムについてお伝えしました。

前回までの記事はこちら↓
第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第3回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

Bitmexへの登録はこちらからどうぞ！1分で登録できます！
https://www.bitmex.com/register/oDtFNI

今回は前回までのプログラムをつなげたものとなります。

・環境構築

環境設定については以前の記事でまとめています。こちらをご覧ください。

webサイトからExcelにJSON形式データを読み込むための下準備

・VBAコード

公式APIから公開されているベース時間足(1分足、5分足、1時間足、日足)だけでなく、それを利用して集計時間足（3分足、15分足、30分足、2時間足、週足など）のデータを集計しています。

流れとしては、ベース時間足の時は取得したJSONデータをセルに出力します。
集計時間足の時は、取得したJSONデータをセルに出力し、そのセルから配列変数に格納し集計してまたセルに出力します。どうしてこうするかというと、同一シート上の同じ場所しか使用したくないからです。また、なぜ一度セルに出力するのかというと、試した結果一度セルに出力しないよりも処理速度が速くなったからです。他にもっと簡潔で処理が速くなる記述の仕方があるかもしれないので、参考程度に見て下さい。

このプログラムは約70万行の1分足のOHLCVデータを、計測したところ約2分程度で取得して表示しました。努力次第でまだ速くなるかもしれません。

Option Explicit
Sub Bitmex_JSON()
'20180528
'Developed by: https://yare-excel-google-spreadsheet.blogspot.jp/
'twitter: https://twitter.com/VBAExcel

Dim CryptoJSON As Object, CryptoNode
Dim HttpReq As Object, CandleJSON As String
Dim bmsheet As Worksheet
Dim js As Object
Dim objJSON As Object
Dim TradeJSON As String
Dim url As String
Dim MaxRow As Long
Dim binsize As String
Dim utm_arr() As Long
Dim opn_arr() As Double
Dim cls_arr() As Double
Dim max_arr() As Double
Dim min_arr() As Double
Dim vol_arr() As Double
Dim tmpT() As Long
Dim tmpO() As Double
Dim tmpH() As Double
Dim tmpL() As Double
Dim tmpC() As Double
Dim tmpV() As Double
Dim Region()
Dim tmp1
Dim tmp2
Dim tmp3
Dim tmp4
Dim tmp5
Dim tmp6
Dim stick As Long
Dim resolution As Long
Dim startnum As Long
Dim from As Long
Dim toend As Long
Dim endnum As Long
Dim num As Long
Dim a As Long
Dim i As Long
Dim j As Long
Dim c As Long
Dim Varr As Long

Application.StatusBar = "loading・・・"
Application.ScreenUpdating = False
Application.EnableCancelKey = xlErrorHandler
Set bmsheet = ThisWorkbook.Worksheets("bitmex")
bmsheet.Range("F:K").Clear
binsize = bmsheet.Cells(6, 2).Value

If binsize = "1m" Or binsize = "5m" Or binsize = "1h" Or binsize = "1d" Then
stick = 1
ElseIf binsize = "3m" Or binsize = "15m" Then
stick = 3
ElseIf binsize = "30m" Or binsize = "6h" Then
stick = 6
ElseIf binsize = "2h" Then
stick = 2
ElseIf binsize = "4h" Then
stick = 4
ElseIf binsize = "12h" Then
stick = 12
ElseIf binsize = "1w" Then
stick = 7
Else: End If

If binsize = "1m" Or binsize = "3m" Then
resolution = 1
ElseIf binsize = "5m" Or binsize = "15m" Or binsize = "30m" Then
resolution = 5
ElseIf binsize = "1h" Or binsize = "2h" Or binsize = "4h" Or binsize = "6h" Or binsize = "12h" Then
resolution = 60
ElseIf binsize = "1d" Or binsize = "1w" Then
resolution = 1440
Else: End If
a = 0
i = 0

startnum = bmsheet.Cells(13, 2).Value + (resolution * 60)
endnum = bmsheet.Cells(13, 3).Value + (resolution * stick * 60)
num = (endnum - startnum) / (resolution * 60) + 1
c = CLng(10000) * (resolution * 60)

If binsize <> "1m" And binsize <> "5m" And binsize <> "1h" And binsize <> "1d" Then
Varr = num / stick + 1
ReDim utm_arr(Varr) As Long
ReDim opn_arr(Varr) As Double
ReDim cls_arr(Varr) As Double
ReDim max_arr(Varr) As Double
ReDim min_arr(Varr) As Double
ReDim vol_arr(Varr) As Double
ReDim Region(1 To Varr, 1 To 6)
Else: End If

Do While toend < endnum

If num > 10000 Then
from = (startnum + c * i)
toend = startnum + c * (i + 1)
num = num - 10000
ElseIf num <= 10000 Then
from = startnum + c * i
toend = endnum
End If

url = "https://www.bitmex.com/api/udf/history?symbol=XBTUSD&resolution=" & resolution & "&from=" & from & "&to=" & toend
Debug.Print url
'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"

js.AddCode "function jsonParse(s) { return eval('(' + s + ')'); }"
'追加した関数を実行して、結果を変数に格納する
Set CryptoJSON = js.CodeObject.jsonParse(TradeJSON)

tmp1 = Split(CryptoJSON.t, ",")
tmp2 = Split(CryptoJSON.o, ",")
tmp3 = Split(CryptoJSON.h, ",")
tmp4 = Split(CryptoJSON.l, ",")
tmp5 = Split(CryptoJSON.c, ",")
tmp6 = Split(CryptoJSON.v, ",")
'ベース時間足
With bmsheet
.Range(.Cells(1 + i * 10000, 11), .Cells(i * 10000 + UBound(tmp1), 11)) = WorksheetFunction.Transpose(tmp1)
.Range(.Cells(1 + i * 10000, 6), .Cells(i * 10000 + UBound(tmp2), 6)) = WorksheetFunction.Transpose(tmp2)
.Range(.Cells(1 + i * 10000, 7), .Cells(i * 10000 + UBound(tmp3), 7)) = WorksheetFunction.Transpose(tmp3)
.Range(.Cells(1 + i * 10000, 8), .Cells(i * 10000 + UBound(tmp4), 8)) = WorksheetFunction.Transpose(tmp4)
.Range(.Cells(1 + i * 10000, 9), .Cells(i * 10000 + UBound(tmp5), 9)) = WorksheetFunction.Transpose(tmp5)
.Range(.Cells(1 + i * 10000, 10), .Cells(i * 10000 + UBound(tmp6), 10)) = WorksheetFunction.Transpose(tmp6)
End With
i = i + 1
a = 0
Loop
'ここからベース時間足以外の時間足を集計
With bmsheet
MaxRow = .Cells(Rows.Count, 6).End(xlUp).Row

If binsize <> "1m" And binsize <> "5m" And binsize <> "1h" And binsize <> "1d" Then
For j = 0 To MaxRow - stick Step stick
utm_arr(a) = .Cells(1 + j, 11).Value
opn_arr(a) = .Cells(1 + j, 6).Value
cls_arr(a) = .Cells(j + stick, 9).Value
max_arr(a) = WorksheetFunction.max(.Range(.Cells(1 + j, 7), .Cells(stick + j, 7)))
min_arr(a) = WorksheetFunction.min(.Range(.Cells(1 + j, 8), .Cells(stick + j, 8)))
vol_arr(a) = WorksheetFunction.Sum(.Range(.Cells(1 + j, 10), .Cells(stick + j, 10)))
a = a + 1
Next

For j = 0 To a - 1
Region(j + 1, 6) = utm_arr(j)
Region(j + 1, 1) = opn_arr(j)
Region(j + 1, 2) = max_arr(j)
Region(j + 1, 3) = min_arr(j)
Region(j + 1, 4) = cls_arr(j)
Region(j + 1, 5) = vol_arr(j)
Next

.Range("F:K").Clear

Dim RowMax As Long
'１次元目の要素数を取得
RowMax = UBound(Region, 1) - LBound(Region, 1) + 1

.Range("F1").Resize(RowMax, 6).Value = Region
Else: End If

If .Cells(7, 2).Value = "true" Then
.Range(.Cells(1, 6), .Cells(MaxRow, 11)).Sort Key1:=.Cells(1, 11), order1:=xlDescending
Else: End If

If .Cells(8, 2).Value = "date" Then
MaxRow = .Cells(Rows.Count, 7).End(xlUp).Row
For i = 1 To MaxRow
.Cells(i, 11).Value = (.Cells(i, 11).Value + 32400) / 86400 + 25569
Next
.Range("K:K").NumberFormatLocal = "yyyy/mm/dd hh:mm"
Else: End If
End With
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

全部で約190行です。

・終わりに

これで第4回まで投稿してきた「仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラムシリーズ」は終わりとなります。ここまで読んでいただきありがとうございます。
また何かプログラムを作成したら公開しようと思っていますので当ブログを時々チェックしていただければ幸いです。

このVBAプログラムを使用したExcelアプリケーションの作り方をnoteにまとめました。こちらもご覧ください。
[メクセル君] BitmexからOHLCVデータを取得するExcelアプリケーションの作り方

第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第3回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

他にもエクセルツールを作成しています！

どんなに小さな額でもチップをいただけると嬉しいです！

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

当エクセルツールから表示される情報は、

内容の正確性、信頼性等を保証するものではありません。

これらの情報に基づいて被ったいかなる損害についても、当サイトは一切の責任を負いません。

投資・投機に関するすべての決定は、利用者ご自身の判断でなさるようお願いいたします。

2018/05/27

第3回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

仮想通貨取引所、Bitmexから価格と取引高の情報であるOHLCVデータをExcelで取得するVBAプログラムを公開したいと思います。

前回は、JSON形式データ取得後のExcelシート上にデータを表示する配列処理のプログラムについてお伝えします。

前回までのプログラムはこちら↓
第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

今回は前回のプログラムの改良版となります。前回はベースとなる時間足データ取得から3分足などの任意の時間足を集計するところまでセルに出さずに配列変数内で処理していましたが、それだと処理速度が遅くなってしまったため、今回は時間足データ取得後に一旦セルに出力し、worksheetfunctionを使用して集計し、集計結果をまたセルに出力したいと思います。

ちなみに、このプログラムは約70万行の1分足のOHLCVデータを約2分程度で取得して表示します。環境は私個人の普通のPCです。努力次第でまだ速くなるかもしれません。

それでは早速VBAコードを見てみましょう。

・VBAコード

流れとしては、取得したJSONデータをセルに出力し、配列変数に格納し集計してまたセルに出力します。

公式APIから公開されているベース時間足(1分足、5分足、1時間足、日足)だけでなく、それを利用して3分足、15分足、30分足、2時間足、週足などのデータを集計しています。

同じシートの同じ場所に集計したデータを出すため、シート上に出力したベース時間足を集計したデータを一旦配列に入れて、シート上にあるベース時間足削除して、配列をその場所に貼り付けるようにしています。

tmp1 = Split(CryptoJSON.t, ",")
tmp2 = Split(CryptoJSON.o, ",")
tmp3 = Split(CryptoJSON.h, ",")
tmp4 = Split(CryptoJSON.l, ",")
tmp5 = Split(CryptoJSON.c, ",")
tmp6 = Split(CryptoJSON.v, ",")
’ベース時間足
Range(Cells(1 + i * 10000, 12), Cells(i * 10000 + UBound(tmp1), 12)) = WorksheetFunction.Transpose(tmp1)
Range(Cells(1 + i * 10000, 7), Cells(i * 10000 + UBound(tmp2), 7)) = WorksheetFunction.Transpose(tmp2)
Range(Cells(1 + i * 10000, 8), Cells(i * 10000 + UBound(tmp3), 8)) = WorksheetFunction.Transpose(tmp3)
Range(Cells(1 + i * 10000, 9), Cells(i * 10000 + UBound(tmp4), 9)) = WorksheetFunction.Transpose(tmp4)
Range(Cells(1 + i * 10000, 10), Cells(i * 10000 + UBound(tmp5), 10)) = WorksheetFunction.Transpose(tmp5)
Range(Cells(1 + i * 10000, 11), Cells(i * 10000 + UBound(tmp6), 11)) = WorksheetFunction.Transpose(tmp6)

i = i + 1
a = 0
Loop
’ここからベース時間足以外の時間足を集計
MaxRow = Cells(Rows.Count, 7).End(xlUp).Row

If binsize <> "1m" And binsize <> "5m" And binsize <> "1h" And binsize <> "1d" Then

For j = 0 To MaxRow - 1 Step stick

utm_arr(a) = Cells(1 + j, 12).Value
opn_arr(a) = Cells(1 + j, 7).Value
cls_arr(a) = Cells(1 + j, 10).Value
max_arr(a) = WorksheetFunction.max(Range(Cells(1 + j, 8), Cells(stick + j, 8)))
min_arr(a) = WorksheetFunction.min(Range(Cells(1 + j, 9), Cells(stick + j, 9)))
vol_arr(a) = WorksheetFunction.Sum(Range(Cells(1 + j, 11), Cells(stick + j, 11)))
a = a + 1
Next

For j = 0 To a - 1
Region(j + 1, 6) = utm_arr(j)
Region(j + 1, 1) = opn_arr(j)
Region(j + 1, 2) = max_arr(j)
Region(j + 1, 3) = min_arr(j)
Region(j + 1, 4) = cls_arr(j)
Region(j + 1, 5) = vol_arr(j)
Next

Range("G:T").Clear

Dim RowMax As Long
'１次元目の要素数を取得
RowMax = UBound(Region, 1) - LBound(Region, 1) + 1
'Rangeで開始セルから貼り付ける
Range("G1").Resize(RowMax, 6).Value = Region

Else: End If

このプログラムよりも良い記述の仕方があるかもしれません。今回は大量のデータを扱いますので、for文を1個追加しただけで速度が大分落ちてしまいます。そのためできるだけfor文を使用しないように一括処理を考えて記述しました。

WorksheetFunctionやRangeをうまく使えば、コードが少なくて済み、処理速度が向上します。前回の第2回のプログラムでは20、30分くらいかかる処理が今回のプログラムでは2，3分程度になりました。ポイントは一括処理をできるだけ増やすことです。

さて、次回はプログラムの最初から最後までの全体についてお伝えします。

次回に続く

第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

他にもエクセルツールを作成しています！

どんなに小さな額でもチップをいただけると嬉しいです！

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

当エクセルツールから表示される情報は、

内容の正確性、信頼性等を保証するものではありません。

これらの情報に基づいて被ったいかなる損害についても、当サイトは一切の責任を負いません。

投資・投機に関するすべての決定は、利用者ご自身の判断でなさるようお願いいたします。

2018/05/23

第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

仮想通貨取引所、Bitmexから価格と取引高の情報であるOHLCVデータをExcelで取得するVBAプログラムを公開したいと思います。

前回は、プログラムの主要な部分である公式APIからのJSON形式データの読み込みの仕方をお伝えしました。

今回は、JSON形式データ取得後のExcelシート上にデータを表示するプログラムについてお伝えします。

前回はAPIを利用してJSONデータを取得したところまでをお伝えしました。前回のプログラムはこちら↓
仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

今回は前回からの続きのプログラムとなります。

それでは早速VBAコードを見てみましょう。

・VBAコード

まず、全体のプログラムです。
流れとしては、取得したJSONデータを配列変数に格納し、セルに出力します。

今回は公式APIから公開されている時間足(1分足、5分足、1時間足、日足)だけでなく、それを利用して3分足、15分足、30分足、2時間足、週足などのデータを集計しています。

変数名の付け方など細かいところで微妙な部分があるかもしれませんが、そのへんはスルーでお願いします。

tmp1 = Split(CryptoJSON.t, ",")
tmp2 = Split(CryptoJSON.o, ",")
tmp3 = Split(CryptoJSON.h, ",")
tmp4 = Split(CryptoJSON.l, ",")
tmp5 = Split(CryptoJSON.c, ",")
tmp6 = Split(CryptoJSON.v, ",")

If binsize = "1m" Or binsize = "5m" Or binsize = "1h" Or binsize = "1d" Then

Range(Cells(1 + i * 10000, 12), Cells(i * 10000 + UBound(tmp1), 12)) = WorksheetFunction.Transpose(tmp1)
Range(Cells(1 + i * 10000, 7), Cells(i * 10000 + UBound(tmp2), 7)) = WorksheetFunction.Transpose(tmp2)
Range(Cells(1 + i * 10000, 8), Cells(i * 10000 + UBound(tmp3), 8)) = WorksheetFunction.Transpose(tmp3)
Range(Cells(1 + i * 10000, 9), Cells(i * 10000 + UBound(tmp4), 9)) = WorksheetFunction.Transpose(tmp4)
Range(Cells(1 + i * 10000, 10), Cells(i * 10000 + UBound(tmp5), 10)) = WorksheetFunction.Transpose(tmp5)
Range(Cells(1 + i * 10000, 11), Cells(i * 10000 + UBound(tmp6), 11)) = WorksheetFunction.Transpose(tmp6)

ElseIf binsize <> "1m" Or binsize <> "5m" Or binsize <> "1h" Or binsize <> "1d" Then

ReDim tmpT(stick - 1) As Long
ReDim tmpO(stick - 1) As Double
ReDim tmpH(stick - 1) As Double
ReDim tmpL(stick - 1) As Double
ReDim tmpC(stick - 1) As Double
ReDim tmpV(stick - 1) As Double
ReDim utm_arr((UBound(tmp1) \ stick) - 1) As Long
ReDim opn_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim cls_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim max_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim min_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim vol_arr((UBound(tmp1) \ stick) - 1) As Double

For j = 0 To (UBound(tmp1) \ stick) - 1
For i = 0 To stick - 1
tmpT(i) = Join(WorksheetFunction.Index(tmp1, 0, i + 1 + (j * stick)), ",")
tmpO(i) = Join(WorksheetFunction.Index(tmp2, 0, i + 1 + (j * stick)), ",")
tmpH(i) = Join(WorksheetFunction.Index(tmp3, 0, i + 1 + (j * stick)), ",")
tmpL(i) = Join(WorksheetFunction.Index(tmp4, 0, i + 1 + (j * stick)), ",")
tmpC(i) = Join(WorksheetFunction.Index(tmp5, 0, i + 1 + (j * stick)), ",")
tmpV(i) = Join(WorksheetFunction.Index(tmp6, 0, i + 1 + (j * stick)), ",")
Next
utm_arr(a) = tmpT(0)
opn_arr(a) = tmpO(0)
cls_arr(a) = tmpC(stick - 1)
max_arr(a) = WorksheetFunction.max(tmpH)
min_arr(a) = WorksheetFunction.min(tmpL)
vol_arr(a) = WorksheetFunction.Sum(tmpV)

a = a + 1
Next

For j = 0 To a - 1
Cells(j + 1, 12) = utm_arr(j)
Cells(j + 1, 7) = opn_arr(j)
Cells(j + 1, 8) = max_arr(j)
Cells(j + 1, 9) = min_arr(j)
Cells(j + 1, 10) = cls_arr(j)
Cells(j + 1, 11) = vol_arr(j)
Next

それでは部分ごとに見ていきましょう。
オブジェクトCryptoJSONからそれぞれt, o, h, l , c, vのデータを配列にしてtmp1～6のvariant型変数に格納しています。JSONデータはカンマ区切りになっているのでsplit関数で配列にします。

tmp1 = Split(CryptoJSON.t, ",")
tmp2 = Split(CryptoJSON.o, ",")
tmp3 = Split(CryptoJSON.h, ",")
tmp4 = Split(CryptoJSON.l, ",")
tmp5 = Split(CryptoJSON.c, ",")
tmp6 = Split(CryptoJSON.v, ",")

時間足が1分足、5分足、1時間足、日足の場合、先程配列にしたデータをそのままシート上のセルに入力します。縦向き（行方向）にデータを入力するためWorksheetFunction.Transpose()を使用します。細かいですが1次元から2次元配列にしています。

If binsize = "1m" Or binsize = "5m" Or binsize = "1h" Or binsize = "1d" Then

Range(Cells(1 + i * 10000, 12), Cells(i * 10000 + UBound(tmp1), 12)) = WorksheetFunction.Transpose(tmp1)
Range(Cells(1 + i * 10000, 7), Cells(i * 10000 + UBound(tmp2), 7)) = WorksheetFunction.Transpose(tmp2)
Range(Cells(1 + i * 10000, 8), Cells(i * 10000 + UBound(tmp3), 8)) = WorksheetFunction.Transpose(tmp3)
Range(Cells(1 + i * 10000, 9), Cells(i * 10000 + UBound(tmp4), 9)) = WorksheetFunction.Transpose(tmp4)
Range(Cells(1 + i * 10000, 10), Cells(i * 10000 + UBound(tmp5), 10)) = WorksheetFunction.Transpose(tmp5)
Range(Cells(1 + i * 10000, 11), Cells(i * 10000 + UBound(tmp6), 11)) = WorksheetFunction.Transpose(tmp6)

3分足、15分足、30分足、2時間足、週足のデータを集計します。
可変配列変数により、足数を変更しています。stick変数が足数です。

ElseIf binsize <> "1m" Or binsize <> "5m" Or binsize <> "1h" Or binsize <> "1d" Then

ReDim tmpT(stick - 1) As Long
ReDim tmpO(stick - 1) As Double
ReDim tmpH(stick - 1) As Double
ReDim tmpL(stick - 1) As Double
ReDim tmpC(stick - 1) As Double
ReDim tmpV(stick - 1) As Double
ReDim utm_arr((UBound(tmp1) \ stick) - 1) As Long
ReDim opn_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim cls_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim max_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim min_arr((UBound(tmp1) \ stick) - 1) As Double
ReDim vol_arr((UBound(tmp1) \ stick) - 1) As Double

WorksheetFunction.Indexでt, o, h, l , c, vの配列tmp1～6から集計に必要な足数だけtmpT～Vに移します。stickには3分足の場合、3が入ります。

その後、高値ならばmax関数、出来高ならsum関数を使用して集計して配列に格納します。

For j = 0 To (UBound(tmp1) \ stick) - 1
For i = 0 To stick - 1
tmpT(i) = Join(WorksheetFunction.Index(tmp1, 0, i + 1 + (j * stick)), ",")
tmpO(i) = Join(WorksheetFunction.Index(tmp2, 0, i + 1 + (j * stick)), ",")
tmpH(i) = Join(WorksheetFunction.Index(tmp3, 0, i + 1 + (j * stick)), ",")
tmpL(i) = Join(WorksheetFunction.Index(tmp4, 0, i + 1 + (j * stick)), ",")
tmpC(i) = Join(WorksheetFunction.Index(tmp5, 0, i + 1 + (j * stick)), ",")
tmpV(i) = Join(WorksheetFunction.Index(tmp6, 0, i + 1 + (j * stick)), ",")
Next
utm_arr(a) = tmpT(0)
opn_arr(a) = tmpO(0)
cls_arr(a) = tmpC(stick - 1)
max_arr(a) = WorksheetFunction.max(tmpH)
min_arr(a) = WorksheetFunction.min(tmpL)
vol_arr(a) = WorksheetFunction.Sum(tmpV)
a = a + 1

Next

集計したデータをセルに入力します。

For j = 0 To a - 1
Cells(j + 1, 12) = utm_arr(j)
Cells(j + 1, 7) = opn_arr(j)
Cells(j + 1, 8) = max_arr(j)
Cells(j + 1, 9) = min_arr(j)
Cells(j + 1, 10) = cls_arr(j)
Cells(j + 1, 11) = vol_arr(j)
Next

今回は慣れているという理由で1次元、2次元配列を使用しましたが、別の方法として連想配列を使用するやり方もあると思います。また機会があれば連想配列についても記事にできればと思います。

~~次回はプログラムの最初から最後までの全体についてお伝えします。~~

次回は処理速度を上げるために改良したプログラムをお伝えします。

次回に続く

第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

他にもエクセルツールを作成しています！

どんなに小さな額でもチップをいただけると嬉しいです！

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

当エクセルツールから表示される情報は、

内容の正確性、信頼性等を保証するものではありません。

これらの情報に基づいて被ったいかなる損害についても、当サイトは一切の責任を負いません。

投資・投機に関するすべての決定は、利用者ご自身の判断でなさるようお願いいたします。

2018/05/22

第1回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

仮想通貨取引所、Bitmexから価格と取引高の情報であるOHLCVデータを取得するVBAプログラムを公開したいと思います。
他のプログラミング言語でもデータを取得できますが、このブログでは硬派にVBAを使っていきたいと思います。

・Bitmexについて

BitMEX(ビットメックス)とは？

BitMEXはどこの国にあって／誰が運営していて／安全なのか

【レバレッジ最大100倍】BitMEX(ビットメックス)の使い方

登録はこちらからどうぞ！1分で登録できます！
https://www.bitmex.com/register/oDtFNI

・OHLCVとは

・Excel VBAでAPIからExcelにデータを読み込む

多くの仮想通貨取引所でAPIを公開しています。当ブログでは今まではbitbankからデータを読み込んできましたが、今回は人気のBitmexを利用したいと思います。

VBAではありませんが、こちらのnoteにBitmexからOHLCVデータの取得の仕方について詳しく書かれてます。色々と勉強になると思います。
APIで取得したOHLCVデータから任意の時間足を作成する

それでは、Bitmexの公式APIからOHLCVデータを取得するプログラムをお伝えします。

VBAコード

まずは、心臓部分である1番大事なデータ取得処理のところからいきましょう。

url = "https://www.bitmex.com/api/udf/history?symbol=XBTUSD&resolution=" & resolution & "&from=" & from & "&to=" & toend

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"

js.AddCode "function jsonParse(s) { return eval('(' + s + ')'); }"

Set CryptoJSON = js.CodeObject.jsonParse(TradeJSON)

それでは部分ごとに見ていきましょう。

まず、任意のデータを取得するためのAPIのURLを指定します。
リクエストURLは、trade/bucketedからもデータを受け取れますが、Excel VBAだと長い期間のデータを取得する時に途中でエラーが出ます。下記のudf/historyからだとエラーが出ずにスムーズに処理が進みます。

url = "https://www.bitmex.com/api/udf/history?symbol=XBTUSD&resolution=" & resolution & "&from=" & from & "&to=" & toend

sendメソッドでリクエストを送信し、responseTextでレスポンスを受け取ります。CreateObjectでオブジェクトを作成します。

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)

BitmexからVBAでOHLCVデータを取得する方法をググっても出てきませんでした。そのため、この短いプログラムでもいくらか苦労したところがあります。しかし、データを取得さえできれば後は普通にVBAを記述するだけです。
次回は配列を使ってセルにデータを格納するプログラムをお伝えします。

次回に続く
第2回　仮想通貨取引所BitmexからOHLCVデータを取得するVBAプログラム　－Excel VBA

他にもエクセルツールを作成しています！