OP取引参加者別（手口）

Pythonでやろうと思っていた1つの日々のOPの手口集計が

コピペでスプレッドシートのクエリでだいたい出来てしまったのでメモ

下準備と関数セットやってしまえば、後は毎回コピペだけでOK
※　新しい価格帯が増えた場合は列をコピペして増やす必要あり

手順

①　新しいスプレッドシートを作成
②　JPXの取引参加者別取引高（手口）の
　　欲しいエクセルデータをダウンロードしてデータ全部コピペ
　　貼り付けるデータの左側を1列か2列空けておく
③　全取引参加者のリストを作成
④　スプレッドシートに関数をちょちょっと設定

ナイト・セッション（立会、J-NET）、日中取引（立会、J-NET）それぞれの
集計はまた別にやる必要がありますが、取り敢えず日中の立会データでやり方説明します

①、②説明省略

③　下準備（最初の１回だけ）
　・張り付けたデータの会社別コードと社名の部分をUNIQUE関数で集計
　　したい会社のリストを作成（下の会社別コードと社名コピペでも可）
　・PUTとCALを125円単位でオートフィルして見出し作成
　　（この見出しをキーで使用してるので関数で作成する文字と合わせる）

④　関数セットしてデータ集計

関数１

=IF(C2<>"instrument",B1,LEFT(D2,3) & RIGHT(D2,5))

張り付けたデータがＣ列の場合はＢ列に関数セット
もしＣ列の文字が"instrument"じゃなかったら上のＢ1の文字をセット
"instrument"だったらD列の文字の左３文字と右５文字をセット

左側1列空けておいた所に上記関数をオートフィルで全行にセット
下の図の様に先物はFUT_年月、OPはPUTかCAL、価格の文字を抽出

関数２

=IFERROR(QUERY($B$3:$J$1000,"SELECT J WHERE B='"& T$2 &"' AND G="& $L3 &""))-IFERROR(QUERY($B$3:$J$1000,"SELECT F WHERE B='"& T$2 &"' AND C="& $L3 &""))

OPの集計する表の所全てにセット
・もしエラーじゃなかったらＢ列からＪ列をクエリ（選択範囲Ｂ列-Ｊ列）
　Ｂ列がＴ２セルと同じでＧ列がＬ３と同じデータのＪ列を選択
・もしエラーじゃなかったらＢ列からＪ列をクエリ（選択範囲Ｂ列-Ｊ列）
　Ｂ列がＴ２セルと同じでＧ列がＬ３と同じデータのＦ列を選択
・選択された（Ｊ列－Ｆ列）を計算してデータセット
※　PUTとCALで減算方向変えた方が良い場合は各自で変えておくれやす

会社別コードと社名、PUTとCALの価格で作成した表の所に
上記関数をオートフィルで全部セット
計算された数字がセットされる

上記の場合、集計する表をＬ２から作成
新しくPUT価格、CAL価格が発生した場合、列コピペして価格帯を追加でOK

※　注意点

同じセクション内（日中取引（立会）とかJ-NETなど）で、
同じキー項目（上図の場合はＣＡＬ２８０００）が複数ある場合は、
最初の方しか選択されないのでキー項目かぶりは別で集計するように
工夫して下さい
※　よく見たら重複キーのデータ1321野村日経平均のETFでした
　　参考程度にしてOPの集計には入れない方が良いかもしれません
　　OPの集計に入れないなら元から計算されていないので
　　重複シートも必要ないと思います
　　一応念の為下図の集計には重複シートにデータとして入れてあります

１枚シート完成させたら、夜間、夜間Ｊ－ＮＥＴ、日中、日中Ｊ－ＮＥＴ、重複分のシートをコピーして作成

最後に合算分のシートを各シートの合計を表示するように計算させて
オートフィルして完成！

='夜間'!V3+'夜間ＪＮ'!V3+'日中'!V3+'日中ＪＮ'!V3+'重複'!V3

トレーダーズWEBと比較するとナイトの分だけ数量違うのが分かります
何でナイト分集計しないんですかね？(°_°)

ちなみに...
こんな感じに変更すれば先物の集計も簡単に出来ます

=IF(C5<>"instrument",B4,MID(D5,5,6) & RIGHT(D5,5))

※　謎現象注意

11/27のデータで先物の手口集計してみたところ、数量が少し変だったので調べてみたら、ナイトＪ－ＮＥＴで本来ＣＳが-88と計算されるべきところ、ゼロと表示されていました

最初はＣＳＶにゴミでもあるのかと思いましたが、そうでもないらしく
データを少しずつ消去していったら正しく計算されました
最初下からＯＰのＣＡＬ２６３７５という１つの塊りずつ消去していったら
ＰＵＴの２６２５０を消去した時に正しく計算されたので、改めてそのデータだけ消去してみても正しく計算されず、今度は上から消去してみたら下から消去した時と同じ12個目で正しく計算されたので、特定の場所のデータという事ではなく、データの数量に問題がありそうです
消去したデータはいづれもＳＢＩの売買同数のデータなのでＱＵＥＲＹで何度も同じ場所の計算をさせるとおかしくなるのかもしれません
バグなのか仕様なのかはわかりません
何も考えずにコピペだけで済むと思っていたので少し注意が必要でした(°_°)

※　謎現象解消法

謎現象の原因は分かってませんが特に夜間のＪ-NETでたまにマイナス表示されるべき所がされない事があるので、解消法としてやっている事は買いと売り同数のデータを邪魔にならない下の方に置いてＱＵＥＲＹ計算されるデータ数を増やしています。それで何故か解消されます(°_°)

会社別コードと社名（20/11/26現在）

12288 フィリップ証券
11635 クレディ・スイス証券
11638 ナティクシス日本証券
11746 ＵＢＳ証券
11788 ソシエテＧ証券
12176 ドイツ証券
12410 バークレイズ証券
12428 ＢＮＰパリバ証券
12479 ＡＢＮクリアリン証券
12724 ＨＳＢＣ証券
11560 ゴールドマン証券
11714 ＪＰモルガン証券
11792 シティグループ証券
12792 BofA証券（ﾒﾘﾙﾘﾝﾁ）
12795 インタラクティブ証券
12800 モルガンスタンレー
11060 ａｕカブコム証券
11256 ＳＢＩ証券
11272 岡三証券
11544 岩井コスモ証券
11727 ＧＭＯクリック証券
12057 楽天証券
12330 マネックス証券
12560 松井証券
11056 安藤証券
11448 エイチ・エス証券
11512 光世証券
11520 三菱ＵＦＪモルガン
11696 みずほ証券
11784 ライブスター証券
11840 立花証券
12000 大和証券
12024 むさし証券
12072 東海東京証券
12328 ＳＭＢＣ日興証券
12336 日産証券
12368 証券ジャパン
12400 野村証券
12464 廣田証券
12888 山和証券
12896 豊証券
12712 三田証券