トレードの成績をExcelで作成するその2〜月次分析〜

前回、トレードの記録を付けていき、日ごとや週ごとの簡単な損益を計算するシートを作成しました。
前回の記事↓

トレードの成績をExcelで作成する


今回は前回作ったシートのデータを元に月次のデータを計算してくれるシートを作成します。
今回のサンプルファイル↓
成績計算_サンプル2

RRRとPFと勝率を出すために必要なデータを作る

リスクリワードレシオとプロフィットファクターと勝率を出すために月次のデータを作成していきます。
必要なデータ以下の4つ。

  • 平均利益
  • 平均損失
  • 合計利益
  • 合計損失

金額ごとのデータとPipsごとのデータをそれぞれ用意すればそれぞれのRRRとPFが計算出来ます。

別シートからデータを参照して平均利益と平均損失を算出する

まずは関数

=IF(勝率セル=””,0,AVERAGEIF(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”>0″))

めちゃくちゃわかりづらい感じになってますが使ってる関数はそこまで難しくないです。(全てはINDIRECTのごちゃごちゃ感のせい)
if関数で勝率のセルに数値が入っていない場合は計算をしないという分岐です。勝率が計算出来ないのに平均のデータを作るとバグるので。
AVERAGEIF関数で別シートのデータ範囲のデータが0より大きい数値のみの平均を算出します。つまり、獲得PIpsのセルのデータ範囲の中から利益になっている=0より大きいデータで平均を求めるということです。
平均利益は上記の通りですが、平均損失はこんな感じです。

=IF(勝率セル=””,0,ABS(AVERAGEIF(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”<0″)))

ABS関数で絶対値を求めます。絶対値ってあれです。マイナスを取り除いた数値です。-1の絶対値は1です。
それ以外は最後の0の前の不等号が違うだけです。
これでデータ範囲の中から損失になっているデータで平均を算出します。

別のシートからデータを参照して合計利益と合計損失を算出する

まずは関数。先程の平均を求める関数と違ってわかりやすいです。

=SUMIF(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”>0″)

データ範囲の中で0より大きいデータを合計しているだけなので簡単です。
同じく合計の損失も不等号の向きを変えるだけで算出出来ます。ABS関数で絶対値をだします。

=ABS(SUMIF(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”<0″))

RRRとPFを算出する

RRRは平均利益/平均損失なので簡単に出せます。

=ABS(平均利益/平均損失)

PFは合計利益/合計損失です。こちらも簡単に出せます。

=ABS(合計利益/合計損失)

ロング勝率とショート勝率と両方の勝率を算出する

まずはロングの勝率を求める関数です。今までで最強のややこしさです。

=IF(COUNTIF(INDIRECT(“別シート!ロングorショートのセルのアルファベット”&開始行&”:ロングorショートのセルのアルファベット”&終了行),”ロング”)=0,0,COUNTIFS(INDIRECT(“別シート!ロングorショートのセルのアルファベット”&開始行&”:ロングorショートのセルのアルファベット”&終了行),”ロング”,INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”>0″)/COUNTIF(INDIRECT(“別シート!ロングorショートのセルのアルファベット”&開始行&”:ロングorショートのセルのアルファベット”&終了行),”ロング”))

たぶん日本語にしてもあんまりわからないかもしれませんが、簡単に言うと、ロングって書いてある行で利益になってるセルの割合を算出してます。
ショートはコレの逆で”ロング”の所をショートに書き換えるだけです。勝った回数を求めるので”>0″を”<0″に書き換えると負けた回数を求めることになるので注意です。
ロングショート分けずに両方を合わせた勝率も算出します。

=COUNTIF(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行),”>0″)/COUNTA(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行))

こちらはデータ範囲の利益になっている割合を算出してるだけなので複雑さは無いです。

データ範囲を決めるセル

上の関数の中で”開始行”と”終了行”ってのがあると思います。それはデータ範囲の指定です。
例えば2月のデータは13行目から23行目までなので開始行は13で終了行は23です。
ここはわざと毎月手入力するようにしています。こうすれば嫌でもデータを見ることになります。
それと、人によっては15日開始の30日間を一ヶ月とする人もいるかもしれないのでそういうニーズに合わせて柔軟にできるようにしています。

おまけのデータ

月間の最大利益と最大損失を算出する

=IF(MAX(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行))<0,0,MAX(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行)))

=IF(MIN(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行))>0,0,ABS(MIN(INDIRECT(“別シート!Pipsの行のアルファベット”&開始行&”:Pipsの行のアルファベット”&終了行))))

月間のトレード回数を算出する

=終了行-開始行+1

 

最後に

ごっちゃごちゃです 笑
ここに書いた内容を読むよりもサンプルをダウンロードしていただいて中にある関数をそれぞれいじってもらった方が理解できると思います。
月次テータが作れたので関数を応用するだけで年次のデータや全トレードの勝率などを算出することもできると思います。
次回で最後です。
内容は通貨ごとの勝率やRRRやPFを算出するシートの作成です。
次の記事↓

トレードの成績をExcelで作成するその3〜通貨ごとのデータ〜

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください