シストレどうですか

  Algorithmic Trading for Dummies

OANDA API 解説編 第6回 ろうそく足を”Excel”でグラフ化

OANDA APIで取得したろうそく足をplotlyやmplfinanceで単純なグラフを書くくらいならエクセルでいいじゃないか!?と思い初めました。 エクセルにはとっても便利なVBAというプログラミング言語があるので結構なんでもできてしまうのですが、 それではあまり意味がないので今回はノンプログラミングでやってみたいと思います。 だんだんOANDAのAPIの解説から離れていっているなと思いつつ、エクセルでのグラフ化について試したことを記述したいと思います。


OANDAのサーバーに接続してJOSN形式のデータをエクセル取り込む


  1. [データ]タブの[Webから](または[データの取得][その他のデータソースから][データ][Webから])を選択
      
    f:id:jantzen:20200815052635p:plain
  2. [Webから]フォーム内の[詳細設定]ボタンを選ぶとフォームが拡張するので、以下のように入力し[OK]する。

    f:id:jantzen:20200815054105p:plain:h300:w300
    • [URL部分]:取得したいデータのURL名
      今回の例としてデモ口座用のUSD/JPY5分足を10個取得する場合のURLは、
      https://api-fxpractice.oanda.com/v3/instruments/USD_JPY/candles?count=10&price=M&granularity=M5
    • [HTTP要求ヘッダーパラメーターの値の入力または選択]ドロップダウンリスト:「Authorization」を選択
      その右の空欄:API Keyを「Bearer xxxxxxxxxxxxxxxxxxxxxxx」の形式で入力。 (xxxxxは自分のAPI KeyでBearerとキーの間は半角スペースをあける)

  3. 上の例では[URL部分]に入力する際にはキリの良いところで2行に分けていますが、1行目にすべて入力しても大丈夫です。

  4. 接続に成功するとPower Query エディターの画面が表示されます。

    f:id:jantzen:20200815094218p:plain:h150:w400


  5. 取り込んだJSON型式のデータをPower Query エディターで編集する


    1. Power Query エディター上で[テーブルへ変換]を押します。
        
      f:id:jantzen:20200815112738p:plain:w300:h200
    2. candlesの「List」を選んで右クリックし「ドリルダウン」を選択すると、
        
      f:id:jantzen:20200815112757p:plain:w300:h200

      下のようになる。

      f:id:jantzen:20200815113430p:plain:w300:h200

    3. 再度[テーブルへの変換]を押すと「テーブルへの変換」フォームが表示されるのでこのまま[OK]を押す。
        
      f:id:jantzen:20200815115229p:plain:w300:h100

    4. 「Column1」列名の右にあるf:id:jantzen:20200815115458p:plain:w20:h20を押すと下のようなフォームが表示されるので「元の列名のプレフィックスとして使用します。」チェックをはずしてOKを押す。
        
      f:id:jantzen:20200817091427p:plain:w300:h150

      OKすると列が以下のように展開するのを確認。
      f:id:jantzen:20200817092532p:plain:w300:h150

    5. 「mid」列名の右にあるf:id:jantzen:20200815115458p:plain:w20:h20を押すと下のようなフォームが表示されるのでそのまま[OK]する。
        
      f:id:jantzen:20200817093814p:plain:w300:h150
      以下のような形になります。
        
      f:id:jantzen:20200818024321p:plain:w400:h150

    ここまでで取得したデータを展開してエクセルに表示できる形まではできましたが、数値もすべて文字扱いの状態になっていますので、エクセルの株価チャートでこのデータをグラフに表示させるには、さらに編集が必要になります。


    エクセルの株価チャートに表示できるようにPower Query エディターでさらに編集する


    エクセルでろうそく足のグラフを描画する場合は、「株価チャート」グラフを使いますが、列の並び順やセルのフォーマットが決められていますので、 それに合う形にPower Query エディタ上で変更します。

    1. 各列の型を必要に応じて変更する。 各列名の左にあるフォーマットのボタンを押す。または右クリックで「型の変更」を選ぶ。
        
      f:id:jantzen:20200818030120p:plain:w400:h150

      • [complete]:「True/False」または「テキスト」(使う予定がなければ変更しなくてよい)
      • [volume]:「10進数」
      • [time]:「日付/時刻」
      • [o], [h], [l], [c]:「10進数」
      •   
        f:id:jantzen:20200818040500p:plain:w400:h150

    2. 列のならび順を変更する。 エクセルの株価チャートは列の並び順が決められていますので、列の並び順を変える。列名をドラッグすれば列全体を移動できます。

      • 「株価チャート(始値-高値-安値-終値)」作成の時、
        「time」➡「o」➡「h」➡「l」➡「c」➡「volume」➡「complete」の順に並べる。
        (使わない列は削除しても良いですが、あとで再度追加するのが面倒になるので残しておいたほうが良いです。)
          
        f:id:jantzen:20200818041654p:plain:w400:h150
      • 「株価チャート(出来高-始値-高値-安値-終値)」作成の時、
        「time」➡「volume」➡「o」➡「h」➡「l」➡「c」➡「complete」の順に並べる。
          
        f:id:jantzen:20200818042702p:plain:w400:h150
    3. 列名を変更する。 必須ではありませんが、わかりやすいように名前を変えておきます。 各列名をダブルクリック(または右クリックで「名前の変更を選択」)すると編集モードになるので好きな名前に変更する。
      ここでは「時刻」「始値」「高値」「安値」「終値」「出来高」「足完成」のように変更。
        
      f:id:jantzen:20200818044241p:plain:w400:h150
    4. 最後に[閉じて読み込む]を押すとろうそく足の情報がシートに展開されます。
        
      f:id:jantzen:20200818044837p:plain:w300:h120
      f:id:jantzen:20200818050342p:plain:w400:h230


    エクセルの株価チャートを表示させる


    いよいよ最終工程になります。

    1. グラフの範囲を選択して、[挿入]タブのf:id:jantzen:20200818070207p:plain:w20:h20を押し(または[おすすめのグラフ]から「すべてのグラフ」タブを選び)、「株価」の中の「株価チャート(始値-高値-安値-終値)」を選ぶ。
        
      f:id:jantzen:20200818064117p:plain:w400:h300
      (赤破線の枠は「株価チャート(出来高-始値-高値-安値-終値)」の場合)

    2. 日付の列の表示形式を「時刻」に変更する。

      エクセルの株価チャートが表示されていると思いますが、
      f:id:jantzen:20200817100504p:plain:w100:h150
      のような変なチャートになってしまっているはずです。 これは、エクセルの株価チャートが日足用にできているようなので、分足のような同じ日付でいろいろな時刻を含んでいるデータを指定してしまうとこのようなグラフになってしまいます。
      日付/時刻データの入っている列のフォーマットを時刻だけ表示するように変更してあげるとグラフが正しく表示されるようになります。
      (日足以上の長い足の場合は、この手順は必要ありません。時刻だけでなく日付をx軸のラベルとして表示させる方法もあるにはありますが今回は触れません。)


      「時刻」列を選択後、右クリックして「セルの書式設定列」を選び[表示形式]タブの「時刻」から好きな種類を選ぶ。
        
      f:id:jantzen:20200818072002p:plain:w400:h230
      これでろうそく足チャートが正しく表示されるはずです。

    3. さらに完成していない足を表示させたくない場合は、「足完成」にフィルターをかける。
      [足完成]列名の右にある↓ボタンを押し、「FALSE」のチェックを外しておく。
        
      f:id:jantzen:20200818073343p:plain:w400:h400
    4. 最後にろうそく足が表示されているセルのどれかにフォーカスがあると[クエリ]タブが表示されるので、[クエリ]タブを選び[プロパティ]を押し「クエリプロパティ」のフォームを表示させる。
      「定期的に更新する」のチェックボックスをチェックして、更新間隔(分単位)に数字(1以上の整数)を入力する。
      こうすることで、作成したクエリが自動更新するので、それに合わせてグラフも自動更新するようになります。
        
      f:id:jantzen:20200818073408p:plain:w600:h400

    これで一定間隔(最短1分)で自動更新されるろうそく足チャートの完成です。

    f:id:jantzen:20200818080504p:plain:w400:h350
    タイトルやラベルなどの編集は普通のエクセルのグラフと同じなので、好みに合わせて変更してください。


    まとめ


    手順は長いですが、エクセルだけでOANDA APIを使って取得したろうそく足チャートをプログラミングなしでできることがわかりました。
    更に[グラフのデザイン]タグの[グラフ要素を追加]を使えば移動平均線の追加も可能なのでmplfianceと同様に移動平均を同時に表示することもできます。
    また、Power Query エディターでの手順は、すべて画面右側に「マクロの記録」のように手順が順番に記録されているので、失敗したと思っても初めからやりなおさずにx印を押すことによって一歩前に戻れますので、試行錯誤しながら前にすすめるのでとても便利です。

    f:id:jantzen:20200818082543p:plain:w400:h350