こんにちは。当noteをクリックいただきありがとうございます。

仮想通貨価格情報サイト「Cryptowatch」からOHLCVデータをExcelで取得するExcelアプリケーション「エクセルウォッチ君」の作り方を公開したいと思います。

概要

CryptowatchからOHLCVデータを取得しExcelのシートに表示します。

CryptowatchのAPIから取得できる30以上の暗号通貨取引所の各通貨ペアのOHLCVデータをExcelのシートに表示します。

各時間単位のローソク足(1分、3分、5分、15分、30分、1h、2h、4h、6h、12h、1d、3d, 1w)を選択して表示できます。(APIから取得したデータをそのまま利用しており、プログラムでは集計しておりません。)

昇順、降順に並び替えできます。

APIから取得したデータでは時間はunixtimeですが、日付形式に変換して表示可能です。

データが取得可能な取引所：

796,quadriga,kraken,btc-china,poloniex,bitfinex,okcoin,mexbt,okex,bitz,gdax,mtgox,hitbtc,gateio,bitmex,qryptos,bitflyer,bitsquare,bittrex,binance,cryptsy,cexio,gemini,bitvc,huobi,luno,bitbay,coinone,bitstamp,quoine,vault-of-satoshi,bithumb,btce

(確認したところ、bitz,gateioはJSONデータがレスポンスでエラーとなり、取得できませんでした。その他の取引所のJSONデータは取得できました。)

・環境設定とVBAコード

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

-環境設定

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

-VBAコード

第2回　CryptowatchからOHLCVデータを取得するVBAプログラム　－Excel VBA

今回もメクセル君の時と同様に作成例ではclearボタンとjsonプリントボタンを使用します。コードはこちらです。

・clearボタン

Sub clearbutton()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

    Range("G:L").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 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のシートをクリックしてコード画面にプログラムをペーストします。

・シート作成

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

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

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

開発タブからボタンを設置し、右クリックでマクロ登録をします。プルダウンメニューのセルがプログラムの変数と対応するようにします。(この対応が合っていないとエラーが出るので注意してください。)

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

・操作方法

入力欄はexchange (取引所), pair (通貨ペア), binsize, reverse, timeのセルで、B5～B9セルです。

1. exchange, pair, binsize, reverse, timeをB5～B9セルのプロダウンメニューから選択します。

2. 入力が終わったらupdateボタンを押す。するとOHLCVデータがG～L列に表示されます。

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

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

以上です。

(記事投稿から数日間は誤植、不備の修正、更新をさせていただくかもしれませんのでご了承ください。)

最後に

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

当Excelツールが皆様のbacktestやデータ解析のお役に立てれば幸いです。

投稿者　プログラム作成者

VB-エイ

twitter

https://twitter.com/VBAExcel

ブログ

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

Donate BTC: 1LcULwCQPjxLGXdpEJkhNkPpqCwG5csasU

Donate LTC: LSXEG7tjeCCGWuzvf5eFvJUZM6Xpvsdc4f

どんなに少額であってもチップをいただけるとうれしいです！