ビットコインの情報をExcelで収集してみよう

最近Bitcoinをはじめとした仮想通貨がネットなどで話題になり始めていますね。どうやら今年は仮想通貨元年と呼ばれるようです。

その是非はさておき、レートを見て値上がり率の高いコインに投資したり、ポートフォリオを管理し損益額を把握したり、自動売買システムを構築したりなど、Excelで操ることは有益だと考えられます。

coincheckというビットコイン取引所では、APIが公開されていますので、試してみることに。

取引所APIドキュメントのページを見ると、大きく分けて2つのAPIがあることが分かりました。認証の必要ない Public API と必要のある Private API とのこと。

Public API では取引所の注文状況や公開されている取引の履歴、板情報を参照することができる。
Private API では取引所での新規注文やそのキャンセル、自分の残高などを確認することができる。

まずは、認証の要らないPublic APIを使って、各コインのレートをExcelに取り込む部分を作ってみました。
この結果と自分の保有通貨数量をあわせてポートフォリオを作ることが出来そうです。

（※2017/12/24追記　bitFlyer版も作成しました。）

1 使用するAPI
2 シートの準備
3 コマンドボタンのクリックイベントを設定
4 販売レート取得プロシージャを作成
5 実行結果
6 ダウンロード
7 応用例

使用するAPI

Public APIのうち、「販売レート取得」を使用します。公式ドキュメントには以下のように説明があります。

シートの準備

シート名「レート」のシートを作成します。
テーブル名「レート」を作成し、通貨、単位、レートの列を作成します。
「単位」列に通貨の単位を入れます。ビットコインならBTCです。ドキュメントの通貨ペアに対応します。
マクロを起動するためのボタンを設置します。キャプションを適当に設定します。

コマンドボタンのクリックイベントを設定

開発リボンからデザインモードをクリックし、設置したコマンドボタンをダブルクリックして、これから作るサブプロシージャを呼び出すよう記述します。

Option Explicit

Private Sub CommandButton1_Click()

    Call getRate

End Sub

Option Explicit

Private Sub CommandButton1_Click()

Call getRate

End Sub

販売レート取得プロシージャを作成

module1などの標準モジュールに、以下のようにコードを記述します。
コードの説明は文中コメント欄に詳細に記述しました。

Option Explicit

Sub getRate()

    Application.ScreenUpdating = False '画面描画を無効

    Dim url As String       'レートを取得するためのURL
    Dim res As String       '取得するレスポンステキスト
    Dim tbl As ListObject   'レートを記載するテーブルオブジェクト
    Dim Rmax As Long        'テーブルの行数
    Dim R As Long           'テーブルを繰り返すループ用変数

    'シートに作成済みのテーブルをオブジェクト変数にセット
    Set tbl = ThisWorkbook.Worksheets("レート").ListObjects("レート")
    
    Rmax = tbl.ListRows.Count 'テーブルの最終行数を取得
    
    For R = 1 To Rmax 'テーブルの1行目から最終行までループ
    
        '単位に記載された仮想通貨のレートを取得するAPIのURLを設定
        '仮想通貨単位を小文字に変換して _jpy を結合　例）BTC -> btc_jpy
        url = "https://coincheck.com/api/rate/" _
                & LCase(tbl.ListColumns("単位").DataBodyRange(R)) _
                & "_jpy"
    
        With CreateObject("MSXML2.XMLHTTP") 'XMLHTTPオブジェクトを生成
            
            .Open "GET", url, False '非同期モードで通信を開始
            .send 'リクエストを送信
            If .Status <> 200 Then Exit Sub 'リクエスト失敗で終了
            
            res = "[" & .responsetext & "]" 'JSON文字列のレスポンス
            
            'Javascriptオブジェクトを生成
            With CreateObject("ScriptControl"): .Language = "JScript"
                
                'JSON文字列をJSONオブジェクトに変換するJavascript関数
                .Addcode "function getVal(num,key,res){" _
                            & "var json=eval('('+res+')');" _
                            & "return json[num-1][key];}"
                
                'JSONオブジェクトからrateキーの値を取得
                tbl.ListColumns("レート").DataBodyRange(R) = _
                    .CodeObject.getVal(1, "rate", res)
            
            End With
                
        End With
    
    Next 'ループここまで
    
    Application.ScreenUpdating = True '画面描画を有効
    
    Set tbl = Nothing
    
End Sub

Option Explicit

Sub getRate()

Application.ScreenUpdating = False '画面描画を無効

Dim url As String 'レートを取得するためのURL

Dim res As String '取得するレスポンステキスト

Dim tbl As ListObject 'レートを記載するテーブルオブジェクト

Dim Rmax As Long 'テーブルの行数

Dim R As Long 'テーブルを繰り返すループ用変数

'シートに作成済みのテーブルをオブジェクト変数にセット

Set tbl = ThisWorkbook.Worksheets("レート").ListObjects("レート")

Rmax = tbl.ListRows.Count 'テーブルの最終行数を取得

For R = 1 To Rmax 'テーブルの1行目から最終行までループ

'単位に記載された仮想通貨のレートを取得するAPIのURLを設定

'仮想通貨単位を小文字に変換して _jpy を結合　例）BTC -> btc_jpy

url = "https://coincheck.com/api/rate/" _

& LCase(tbl.ListColumns("単位").DataBodyRange(R)) _

& "_jpy"

With CreateObject("MSXML2.XMLHTTP") 'XMLHTTPオブジェクトを生成

.Open "GET", url, False '非同期モードで通信を開始

.send 'リクエストを送信

If .Status <> 200 Then Exit Sub 'リクエスト失敗で終了

res = "[" & .responsetext & "]" 'JSON文字列のレスポンス

'Javascriptオブジェクトを生成

With CreateObject("ScriptControl"): .Language = "JScript"

'JSON文字列をJSONオブジェクトに変換するJavascript関数

.Addcode "function getVal(num,key,res){" _

& "var json=eval('('+res+')');" _

& "return json[num-1][key];}"

'JSONオブジェクトからrateキーの値を取得

tbl.ListColumns("レート").DataBodyRange(R) = _

.CodeObject.getVal(1, "rate", res)

End With

Next 'ループここまで

Application.ScreenUpdating = True '画面描画を有効

Set tbl = Nothing

End Sub

実行結果

マクロを実行すると、次のように販売レートが取得できました。

ダウンロード

ここまでのサンプルファイルは以下の矢印のあるボタンからダウンロードできます。

※下にExcelオンラインのテーブルを埋め込んでいますが、企業や学校のフィルタリングによっては見えないかも知れません。

応用例

自分の保有数量、取得レートなどと組み合わせたポートフォリオを作ってみました。現在販売レートは毎秒自動的に更新しています。
以下の動画は5/7の大体1時ぐらいから（60倍速）
この間に4％ほど上昇しています。
リップルはもうすぐ倍になりそうですね。