ランダム売買の損益推移についての計算方法について

質問

為替取引で，ランダムな売買を行った場合の損益の推移について調べたいと考えています。
具体的には，ドル円　の1分足の終値で買い，1円の利益が出たときに，売るという単純な取引です。
ただし，この取引は，1分ごとに行うことにします。
もし，この取引での利益が0に近づけば，為替価格の変動がランダムであることがわかります。ただし，ある短い期間を捉えて，利益が出れば，価格変動は必ずしもランダムではないことがわかります。
ですから，価格変動ごとに，確定利益（1円）と含み損（買値と終値との差額）を合計した損益を求めます。以上が，目的です。

さて，

A列に時刻
B列に始値
C列に高値
D列に安値
E列に終値

2009.05.01.0.0 98.33 　99.76 93.79 　95.25
2009.05.01.0.1 95.21 　98.88 94.49 　96.45
2009.05.01.0.2 96.44 　96.98 91.76 　95.21
2009.05.01.0.3 95.25 　97.83 92.59 　92.75
2009.05.01.0.4 92.71 　93.38 91.9 　92.96

を貼りつけることにします。
このような行が，約37万行縦に並びます。
価格変動するたびに，各行ごとに，最新の高値が各行の買値よりも，1円以上高いかどうかを判断して，1円以上高ければ，そこで1円の利益が確定することになります。しかし，そうでなければ，各行ごとに，同様の計算を繰り返すことになります。これに加えて，各行ごとに，含み損（買値と終値の差額）も計算します。

私は，各行ごとに，価格変動するたびに，列を右に伸ばし，計算結果を書きこんでいくという方法を考えたのですが，これだと列の数が多くなりすぎて，列数の限界を簡単に超えてしまいます。

VBAを利用して，何かいい計算方法があればご教示願います。

回答No.3

nag0720 · ベストアンサー

＃２です。

I列の売却日時を求めるユーザー定義関数を作ってみました。

下記のコードを標準モジュールに記述してください。

Function SELLDATE(Kingaku As Single, Range1 As Range, Range2 As Range) As String
Dim i As Integer
Dim Col1 As Integer
Dim Col2 As Integer
Col1 = Range1.Cells(1).Column
Col2 = Range2.Cells(1).Column
SELLDATE = ""
For i = Range1.Cells(1).Row To Range1.Cells(Range1.Cells.Count).Row
If Cells(i, Col1) >= Kingaku Then
SELLDATE = Cells(i, Col2)
Exit For
End If
Next i
End Function

関数の引数は、
１番目：売却したい単価
２番目：比較する単価（高値）のセル範囲
３番目：取引日時のセル範囲

あとは、セルに次の計算式を入れます。（１行目がタイトル行、最後の行が6の場合です）

F列：購入数量（いくつでもＯＫ）
G列：購入単価（=E2）
H列：購入金額（=F2*G2）
I列：売却日時（=SELLDATE(G2+1,C3:C$6,A3:A$6)）
J列：売却数量（=IF(I2<>"",F2,0)）
K列：売却単価（=IF(I2<>"",G2+1,0)）
L列：売却金額（=J2*K2）
M列：売却損益（=IF(I2<>"",L2-H2,0)）
N列：購入数量計（２行目に、=F2　３行目に、=M2+F3）
O列：購入金額計（３行目に、=H2　３行目に、=O2+H3）
P列：売却数量計（=SUMPRODUCT((I$2:I2>"")*(I$2:I2<=A2)*J$2:J2)）
Q列：売却金額計（=SUMPRODUCT((I$2:I2>"")*(I$2:I2<=A2)*L$2:L2)）
R列：売却購入額計（=SUMPRODUCT((I$2:I2>"")*(I$2:I2<=A2)*H$2:H2)）
S列：売却損益計（=SUMPRODUCT((I$2:I2>"")*(I$2:I2<=A2)*M$2:M2)）
T列：保有数量（=N2-P2）
U列：保有購入額（=O2-R2）
V列：含み損益（=T2*E2-U2）
W列：合計損益（=S2+V2）

３行目以降はコピー（N列,O列は４行目以降）

提示された例で、購入数量＝１でやってみたところ、合計損益は次のようになりました。

2009.05.01.0.0　　0
2009.05.01.0.1　　1
2009.05.01.0.2　　-0.24
2009.05.01.0.3　　3
2009.05.01.0.4　　3.21

お礼

詳しい回答ありがとうございます。
昨日，仕事を終え，帰宅後，回答を拝見しました。
現在，回答内容から，エクセルに計算式などを入力しています。
結果がでましたら，また改めてお礼を申し上げます。

補足

ご教示いただいたプログラムをエクセルにコピーして試してみましたが，うまくいきません。
何点か質問があります。
第一点として，
J列：売却数量（=IF(I2<>"",F2,0)）
とありますが，私は，理解できません。
もしかすると
=IF(I2<=A2,F2,0)
ということでしょうか。

第二点として
ユーザー定義関数の
SELLDATE = ""
の行が理解できません。
空白を入れるということなのでしょうか。

以上，よろしくお願いします。

回答No.10

nag0720

＞nag0720さんが，C$6に固定したのは，C6を最終行と想定されたからでしょうか。

そうです。質問のサンプルデータが５件だけでしたから。
＃３にも、（１行目がタイトル行、最後の行が6の場合です）と書いていますので。

＞また，最終行の表現方法として，何かよい方法があれば，いいのですが。

ないこともないですが、今の段階であれこれいじるよりも、まずはデータの数を決めて実際に計算してみてはどうですか。
１万件のデータでどのくらいの計算時間がかかるか、申しわけありませんが全く予想できませんので。

お礼

分かりました。
なお，1万件のデータを計算したところ，約40秒で計算が終了しました。
若干動作が不安定になりますが，固まることはありません。

回答No.9

nag0720

＞列：売却日時（=SELLDATE(G2+1,C3:C$6,A3:A$6)）
＞という計算式ですが，これの第二配列は，C$6に固定していますが，これはなぜなのでしょうか。
＞C3:「最終行」例えば，C1000などとすることではだめなのでしょうか。

=SELLDATE(G2+1,C3:C1000,A3:A1000)
とした場合、それを他の行、例えば、I500にコピーすると、
=SELLDATE(G500+1,C501:C1498,A501:A1498)
となって、「最終行」が変動してしまいます。
それでもいいんでしょうか。

補足

何度も回答していただきまして，ありがとうございます。
　さて，回答で御指摘いただきましたように，C1000として，そのままコピーしていきますと，最終行が変動してしまいます。この点は十分に理解できます。
　nag0720さんが，C$6に固定したのは，C6を最終行と想定されたからでしょうか。
　私は，最低でも1000行の計算を考えていましたので，C1000と書いたのですが，少し考えが足りませんでした。コピーすることを念頭におくと，
C$1000とすればよいのでしょうか。
また，最終行の表現方法として，何かよい方法があれば，いいのですが。
　基本的な知識に欠ける私に対して，本当に，何度も回答をいただきありがとうございます。

回答No.8

nag0720

計算式で""が認識されていないのではなく、たぶんI2に""が入っていないのでしょう。
I2の値に本当に何も入力されていないのか確認してみてください。
何も表示されていない場合でも、何も入力されていないのか、スペースが入力されているのかは、見ただけでは分かりませんので。

J列：売却数量、K列：売却単価の計算結果も未売却の場合は0になるはずですがどうなってますか？

ユーザー定義関数で、売却できない場合、関数の結果は""となるはずです。
ユーザー定義関数を確認してみてください。
特に、
SELLDATE = ""
の部分を。

ユーザー定義関数を変更した場合は、その関数を使っているセルは自動計算されないので、手動でシートの再計算をするか、セルを再入力する必要がありますので注意してください。

P列，Q列，R列は、I列に入っている時刻に対応する行で必ず０以外の値になります。
０以外の値になるはずの行でのP列の計算式はどうなっていますか。

お礼

親切にアドバイスありがとうございます。
　原因が何かは不明なのですが，解決しました。
　なかなかうまく計算できなかったので，最初からやりなおしたところ，うまく計算できるようになりました。
　ありがとうございました。

補足

何度も質問して申し訳ないのですが，I列のユーザー定義関数のことで質問です。
御教示していただいた

I列：売却日時（=SELLDATE(G2+1,C3:C$6,A3:A$6)）

という計算式ですが，これの第二配列は，C$6に固定していますが，これはなぜなのでしょうか。C3:「最終行」例えば，C1000
などとすることではだめなのでしょうか。
売却日時というのは，当然，購入した後の日時ですので，第二配列の範囲も購入後のデータを対象とするのではないかと考えたからです。
　以上，よろしくお願いします。

回答No.7

nag0720

＞M列：売却損益（=IF(I2<>"",L2-H2,0)）
＞の計算式についてですが，
＞半角で""と入力しているつもりですが，うまく計算できていません。
＞P列，Q列，R列も同様です。

=IF(I2<>"",L2-H2,0)
をコピー＆ペーストしてもだめでしょうか？

「うまく計算できません」とは、エラーになるのでしょうか？
それとも、計算結果が正しくないのでしょうか？
計算結果が正しくないのなら、I2,L2,H2には何が入ってて、計算結果はどうなったのでしょうか？

J列、K列は問題ないのでしょうか？

補足

回答，ありがとうございます。
=IF(I2<>"",L2-H2,0)
という計算式の入っているセルでは，
I2のセルの値が""の場合には，M2のセルは，当然，0という値になるはずなのですが，
-80.25というような数字が表示されています。
また，P列，Q列，R列についてもすべて値が0した表示されていません。
ですから，計算式で""が認識されていないのではないかと考えたわけです。
以上，よろしくお願いします。

回答No.6

nag0720

＃２、＃３です。

２番目の質問から。

＞ユーザー定義関数の
＞SELLDATE = ""
＞の行が理解できません。
＞空白を入れるということなのでしょうか。

購入したものが必ず売却できるとは限りません。
売却できたときはその時刻を返しますが、未売却のときは、長さ０の文字列を返すようにしています。

＞J列：売却数量（=IF(I2<>"",F2,0)）
＞とありますが，私は，理解できません。
＞もしかすると
＞=IF(I2<=A2,F2,0)
＞ということでしょうか。

J,K,L,M列は売却したときの売却数量,単価,金額,損益を計算しています。
未売却のときは０とするために、I2<>""という判定をしています。
これはあくまでも売却した時刻での売却数量などであって、その行の時刻での売却数量ではありません。
その行の時刻での売却数量などは、P,Q,R,S列の売却数量計、売却金額計、売却購入額計、売却損益計で累積合計として計算しています。

今にして思えば、J,K,L,M列はその行の時刻での値にしたほうが分かりやすかったかもしれません。
そのように修正するのはそんなに難しいことではないので、余裕があったらやってみてください。

それよりも、＃５さんが書いているように、３７万行というデータ量ではエクセルで計算するのは現実的ではないような気がします。
万一メモリーの問題が解決したとしても、もしかしたら耐えられないくらいの計算時間がかかるかもしれません。

データ量を考えると、アクセスのようなデータベース言語で計算するか、VBなどのプログラミング言語で最小限のデータだけ読み込みながら処理していくような事案だと思います。

お礼

お礼が遅くなりました。
ユーザー定義関数を標準モジュールに登録する作業などに手間取っていました。

分かりやすい回答ありがとうございます。
納得しました。

処理するデータ量等については，まさにご指摘のとおりです。
現在，メモリ不足等で安定して動作していません。
利用方法等については，処理するデータ量を少なくするなどして工夫しようと思います。

補足

助けてください。
ご教示していただいたプログラムですが，トラブル続きです。
M列：売却損益（=IF(I2<>"",L2-H2,0)）
の計算式についてですが，
半角で""と入力しているつもりですが，うまく計算できていません。
P列，Q列，R列も同様です。
入力には，IMEを使用しています。

回答No.5

ToOrisugaru

ｏｆｆｉｃｅのバージョン書いてないとなんとも言えませんが、３７万件といく件数を
計算させるのに必要メモリはどれぐらい必要か考えていますか？
ちなみに、ｏｆｆｉｃｅ２００３までのバージョンでは約６万５千件ぐらいしかセル
貼りつけることができないのはご存じですか？
おそらく、作ったとしても相当いいマシンでないとメモリ不足で終了するのが
おちだと思います。

補足

私が使用しているエクセルのバージョンですが，2010です。
また，使用しているパソコンのメモリですが，4ギガのメモリをつんでいますが，認識されているのは3ギガです。
ご指摘のとおり，データを大きくすると，エクセルが固まってしまいました。
1万行くらいのデータを処理し，これを繰り返すことにします。

回答No.4

noname#196225

#1です。VBAを実際に組むと分かりやすいのですが、
Bunpuを得るためには一行一行について、
実際の株(為替)の売り買いの動作をプログラムにさせる必要があります。
即ちある時点の行に注目すると、「高値に対して条件を満たす株をすべて売り、
終値の株を一株買う」という形になります。
この一行一行の動作後に得られる分布で損益計算をすれば良いかな、と思います。

売りのプログラム例は、例えば次のような計算になります。

' 売り：確定利益
If BunpuMin <= Takane - Thr * 100 Then ' 分布の最安が売り条件を満たす場合。
' 売り条件を満たす株をすべて対象とする。
For j = BunpuMin To Takane - Thr * 100
Income = Income + Bunpu(j) * (km - j) / 100 ' 収益増加
KabuCnt = KabuCnt - Bunpu(j) ' 株総数は減少
Fund = Fund + Bunpu(j) * km / 100 ' 資金増加
Bunpu(j) = 0 ' 売ったので、その買値の持ち株は0
Next j
' 最安値は売られてしまったので、新しい最安を探す。
For j = Takane - Thr * 100 + 1 To k - 1
If Bunpu(j) > 0 Then Exit For
Next j
BunpuMin = j ' 保有する最安値更新
End If
※BunpuMinは演算高速化のために挟んでいますが、分かりやすさを優先するなら
BunpuMin=LBound(Bunpu)でも動きます。
あとは、保有株分布からの損益計算のループ回数が無視できない(37万行それぞれで
計算します）ので、
そこをどうにかして高速化すればOKです。(最安買値と最高買値を両方記録しておき、
その範囲でループを回す、ということでも高速化しますが、もっと高速化できます。）
また、最後に得られた変数を別シートか何かに書き出し保存しておくと、
37万行にさらに1万行追加した場合などに続けて計算できます。
ちなみに使い方は、ユーザー関数としてではなくボタンイベントなどとして実装することを想定しています。

ところで、既にある#2さんの回答は出力結果が見やすく完成度が高そうですので、
こちらは参考までに。

回答No.2

nag0720

為替取引はちょっと分からないので株取引として考えてみます。

セルの各列に次のように登録します。

F列：購入数量
G列：購入単価（＝終値）
H列：購入金額（＝購入数量×購入単価）
I列：売却日時（購入したものを売却したときの日時）
J列：売却数量（＝購入数量）
K列：売却単価（＝購入単価＋１．００　かな？）
L列：売却金額（＝売却数量×売却単価）
M列：売却損益（＝売却金額－購入金額）
N列：購入数量計（＝購入数量の累計）
O列：購入金額計（＝購入金額の累計）
P列：売却数量計（＝「売却日時≦当該行の日時」である行の売却数量の合計）
Q列：売却金額計（＝「売却日時≦当該行の日時」である行の売却金額の合計）
R列：売却購入額計（＝「売却日時≦当該行の日時」である行の購入金額の合計）
S列：売却損益計（＝「売却日時≦当該行の日時」である行の売却損益の合計）
T列：保有数量（＝購入数量計－売却数量計）
U列：保有購入額（＝購入金額計－売却購入額計）
V列：含み損益（＝保有数量×終値－保有購入額）
W列：合計損益（＝売却損益計＋含み損益）

最後の合計損益が目的の損益になります。

P,Q,R,S列はSUMPRODUCTで計算できるでしょう。
問題となるのはI列の売却日時ですが、これだけはVBAを使わなければならないかもしれません。

補足

詳しい回答ありがとうございます。
　nag0720さんが指摘されているように，Ｉ列の売却日時をどのように求めるかという点が一番重要なポイントです。この部分をVBAを利用しようと考えているのですが，本当に難しいですね。
　うまい方法があれば，紹介してください。

回答No.1

noname#196225

為替の買値の分布を0.01円単位でヒストグラム化して保存すると良いと思います。

定義部分のみ（為替ではなく株としていますが）：
Option Explicit

Dim KabuCnt As Long ' 株総数
Dim ExKabuCnt As Long ' 損株総数
Dim Fund As Double ' 資金
Dim Assert As Double ' 資産
Dim Income As Double ' 確定利益
Dim Expence As Double ' 含み損
Dim Bunpu(100000) As Integer ' 保有株の買値分布。精度2桁、1000円以下とする。
Dim BunpuMin As Long ' 保有する株の最安値
Dim Thr As Double ' 最小利益のしきい値

で、ロジックは１行ごとに、「終値をkとするとBunpu(k*100)=Bunpu(k*100)+1をする」
を根底にすると良いでしょう。
プログラムソースを載せると2000字では収まらないので、ご自分で組んでみてください。
（そのまま組むとループ回数が凄いと思いますが、まずはそれを第一目標に。
さらにBunpuMinとExKabuCntを上手に使うとループ回数が減らせます。）

補足

早速の回答ありがとうございます。
定義部分だけですが，具体的なステートメントが示され，参考になります。
さて，私は，この回答をまだ，十分に理解できていないのですが，
「買値の分布をヒストグラム化」するということですが，この点がなかなかイメージできません。というのは，最終的な損益を求めるだけではなく，損益の推移をも明らかにしたいのですが，ヒストグラムにすると時間的な観点をどのように取り込んでいくのか，よく理解出来ないからです。
　この点について，分かりやすく説明していただければ，幸いです。