仮想通貨取引所「Bitmex」から価格情報であるOHLCVデータをExcelで取得するExcelアプリケーション「メクセル君」の作り方を公開したいと思います。

・環境設定とVBAコード

OHLCVデータを取得するVBAプログラムについては既に私のブログで公開しています。Excelの設定の仕方も書いてますのでご覧ください。

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

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

今回の作成例ではclearボタンとjsonプリントボタンを使用します。コードはこちらです。

・clearボタン

Sub clearbutton()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Range("F:K").Clear
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

・jsonボタン

Sub JSON_Print()
Dim arrs
Dim len_row
Dim len_col
Dim c
Dim r
Dim json
Dim fnsave
    arrs = Cells(1, 6).CurrentRegion
    len_row = UBound(arrs, 1) '行数
    len_col = UBound(arrs, 2) '列数
    ReDim heads(len_col - 1)
    ReDim recs(len_row - 1)
   
    For r = 1 To len_row
        ReDim temps(len_col - 1)
        For c = 1 To len_col
            If c <> 6 Then
                temps(c - 1) = arrs(r, c)
            ElseIf c = 6 Then
                temps(c - 1) = Chr(34) & arrs(r, c) & Chr(34)
            Else: End If
        Next
        recs(r - 1) = "[" & Join(temps, ",") & "]"
    Next r
    json = "[" & Join(recs, ",") & "]"
   
    'ファイルの出力
    fnsave = Application.GetSaveAsFilename("ohlc_store.json", "JSON(*.json),*.json")
    If fnsave = False Then Exit Sub
    With CreateObject("ADODB.Stream")
        .Type = 2
        .Charset = "utf-8"
        .Open
        .WriteText json, 1
        .SaveToFile fnsave, 2
        .Close
    End With
End Sub

環境設定を終えたら、VBAプログラムをVBEのMicrosoft Excel Objectsのシートをクリックしてコード画面にプログラムをペーストします。

（処理速度は環境によるかと思いますが、私が計ったところ1分足70万本を約2分で取得しました。）

・シート作成

Excelのシートを操作しやすいように作成します。

私がデザインしたシートを例に説明します。

私は下の画像のようにデザインしました。できるだけシンプルにすることを心がけて、入力欄を少なくし、色は白、グレー、黒のグラデーションとしました。マクロのトリガーとなるボタンを上部に設置し、botトレードのバックテスト用にjsonボタンを付けました。B6～B8セルにプルダウンメニューを付けました。

開発タブからボタンを設置し、右クリックでマクロ登録をします。プルダウンメニューのセルとunixtimeのstart, endのセルがプログラムの変数と対応するようにします。

コマンドボタンにマクロを登録する方法　Excel VBA

・操作方法

入力欄はbinsize, reverse, time, Japan timeのstart, endのセルとなります。B6,B7,B8セルとB11,C11セルです。

1・binsize, reverse, timeをB6,B7,B8セルのプロダウンメニューから選択

2・時間はJapan timeのstart, endを入力

worksheet関数でUTC timeとunixtimeが自動的に算出されるようになっています。

3・入力が終わったらupdateボタンを押す。するとOHLCVデータがF～K列に表示される。

clearボタンでOHLCVデータのF～K列が削除されます。

jsonボタンでOHLCVデータのF～K列がjsonファイルとしてexportされます。

以上です。

最後に

最後まで読んでいただきありがとうございます。

botトレードをするにあたり、プログラミング上級者にとってはわざわざExcelでデータを取得する必要は無く、既にOHLCVデータを取得する手段を持っていると思いますが、プログラミング初心者にとっては環境構築から始まり、やることがたくさんありなかなか前に進まず、色々と大変なことが多いと思います。私自身はPython初心者で環境構築や構文に慣れるのに苦労したこともあり、慣れていたVBAでOHLCVデータくらいは取得してみようと思ったのが当Excelツールを作るきっかけとなりました。

当Excelツールで皆様のbacktest用のjsonファイル作成の手間が省ければ幸いです。

投稿者　プログラム作成者

VB-エイ

twitter

https://twitter.com/VBAExcel

ブログ

https://yare-excel-google-spreadsheet.blogspot.jp/

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

どんなに少額であってもサポートをいただけるとうれしいです！