未読の方はこちらからご覧ください。 →こちら
今回は、先物・オプション手口の集計(準備編)により、JPXのサイトから取得したデータを加工した「当日のデータ一覧」を元に、取引参加者別にデータを抽出・集計するExcelシートを作成する手順を紹介します。 準備編は→こちら
（細かく書いているとだらだらと長くなってしまうため、だいぶ端折っていますが、JPXのデータは複雑で量も多いため、先物やオプションを「A社 りんご 10 個、B社 りんご 5 個、C社 みかん 20 個･･･」等の簡易データに置き換えて抽出・集計してみるとわかりやすいと思います）
4 取引参加者別のデータ抽出・集計
このシート（データの抽出・集計用シート）は先に作成した「当日のデータ一覧」と同一のExcelファイル内にシートを追加して作成するのが後々の処理のためにも便利である。
4-1 シートのフォーマット
シートは次の３つの部分で構成しています。
「当日のデータ一覧」から取引参加者をキイワードとして手口を抽出した部分が「A」です。
「ナイト・セッション」・「日中取引」に分かれ、その中で「立会取引」と「J-NET取引」の計４種類の表となる。
取引参加者の先物手口の集計が「B」となります。Aにて抽出したデータを元に、商品名をキイワードとして抽出・集計しています。
取引参加者のオプション手口の集計が「C」となります。Aにて抽出したデータを元に、商品名をキイワードとして抽出・集計しています。
4-2 抽出の方法
先に作成した「当日のデータ一覧」には、商品名 - 取引参加者 - 取引枚数 が同一行に記載されている筈である。
したがって、取引参加者（たとえば「ＡＢＮアムロクリア」）が含まれているデータ（行）を捜し、同一行の商品名 と 取引枚数 を抽出する数式を作成入力します。
「日中取引」--「立会取引」「J-NET取引」、「ナイト・セッション」--「立会取引」「J-NET取引」の４つのパートに分けます。
「A」拡大したのが次表で、「日中取引」--「立会取引」の部分になります。
筆者はROW関数を用いて作成しました。
筆者作成のQ列（取引参加者の商品名）を求める数式は以下のとおりです。
=IF(ROW(P1)>COUNTIF(当日データ!$C$2:$C$800,"ＡＢＮアムロクリア"),"",INDEX(当日データ!A$1:A$800,SMALL(INDEX((当日データ!$C$2:$C$800<>"ＡＢＮアムロクリア")*1000+ROW(当日データ!$C$2:$C$800),),ROW(P1))))
この算式がQ列以降にも入力されています。
日中取引の場合、データが700行程度あるため、少し余裕を見て800行で設定しています。
ちなみにR列の及びS列は以下のとおりです。
=IF(ROW(Q1)>COUNTIF(当日データ!$C$2:$C$800,"ＡＢＮアムロクリア"),"",INDEX(当日データ!C$1:C$800,SMALL(INDEX((当日データ!$C$2:$C$800<>"ＡＢＮアムロクリア")*1000+ROW(当日データ!$C$2:$C$800),),ROW(Q1))))
=IF(ROW(D1)>COUNTIF(当日データ!$C$2:$C$800,"ＡＢＮアムロクリア"),"",INDEX(当日データ!E$1:E$800,SMALL(INDEX((当日データ!$C$2:$C$800<>"ＡＢＮアムロクリア")*1000+ROW(当日データ!$C$2:$C$800),),ROW(D1))))
これらが、日中取引・立会・売りの部分であり、T列以降、日中取引・立会・買い、日中取引・立会外・売り･･･と続きます。
4-3 集計
前項で抽出したデータを元に、先物・オプションの商品別の集計表を作成します。
「B」の部分です。
同一シート内にあったほうが効率的と考え、別図のとおり、３分割して同一シートで作成しています。
数式はVLOOP関数を使用しました。
E-4の「日中取引・立会・売り」には次の数式を入力しています。
=IFERROR(VLOOKUP($A4,$Q$4:$S$48,3,FALSE),0)
これらのフォーマットの作成には２週間くらいかかりましたが、毎日の作業は "先物・オプション手口の集計(準備編)" で紹介したJPXデータのコピペだけで、ものの５分もあれば可能ですので、興味のある方は試みられると良いと思います。
なお、限月が変わった際にはデータの限月部分(「B」、「C」の左端、データの抽出の際に、こちらのデータを参照して抽出しています)を変更する必要がありますが、「置換」により、こちらも５分もあれば可能です。
また、週一回の残高報告の集計・反映や「4-3 集計」で作成したデータを基にした参加者別一覧表や先物手口及び残高一覧作成など、細かなテクニックもあります。時間のあるときとなって恐縮ですが、順次、可能な限り紹介してまいります。
