[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シミュレーションのしかた』（toto）
他サイトでの回答が期待できないのと、これから先エクセルを使用することになるためここに参りました。
※他サイトへの投稿は「解決済み」としましたが、ご不快ならお許しください
※投稿内容は若干変えています
質問）
ある投資額に対する日毎の「回収率」データが数千件あります。
（概ねその値は「0〜10」の範囲内にあります）
このデータを基に、投資パターンを決めるシミュレーションを検討しています。
・シミュレーションは200個のデータで行いたいと考えています
・過去の回収率の統計量（平均、分散等）はできる限り再現したいです
・日付は回収率とは無関係とします
・度数グラフから、正規分布や指数分布といった理論分布を当てはめるのは
適当ではないと考えています
以上の制約下で次の方法のどちらがより「数学的」に妥当と考えられるでしょうか？
方法１
過去データをランダムに並び替え、そこから200個のデータを取出す
方法２
過去データを例えば「0.1」刻みの度数表に表し、そのカウント数を換算した度数表を作成（＊）、
その度数表からランダムに200個のデータを作成する
＊過去データが1000個で「1.4-1.5」の範囲に150個ある場合、換算度数表では
30個となる（=150/1000*200）
＊度数の「まるめ」が避けられず、統計量が変わってしまうのは想定内です
どちらも過去データを忠実に再現しているわけでもなく、シミュレーションとしてどちらが
“よりふさわしいか”という問いかけになります。
あるいは、別案をご助言いただければ幸いです。
よろしくお願いします。
＜ 使用 Excel：Excel2007、使用 OS：Windows10 ＞
データを200個に絞るだけですよね？
方法1しかないと思えるのですが。
方法2は、刻み幅が0.1なら、階級数は100程度ですよね？ そこからどう200個抽出するのか、ご説明からはよく分からないです。
一般論として、どんな具体的データがあって、どんな分析をしたいのか分からなければ 適切な抽出法はこれだ、などとは言えないと思います。
（半平太） 2019/06/26(水) 12:45
A列 B列 190626 1.33 190625 0 190618 5.41 190617 0.75 … …
抽出数や刻み幅は（仮）で書きましたが、階級に分けると同じ階級に複数個存在し得ますし、当然「0」個の場合もあり得ます。
投資パターンの意味は、例えば直前の投資額に常に一定の比率を掛けていくとか、回収率が「1」以下（100%以下）の次回は○万円に固定するとか、実データと類似の統計的な特徴をもった仮想データでいろいろシミュレートしたい、ということです。
詳しくは知りませんがマーチンゲール法とかと発想は同じです。
質問は、方法１，２どちらが「実データと類似の統計的な特徴をもった」データ抽出方法といえるか、
と言換えてもいいかも知れません。
このサイトの運営主旨とは外れているかなと思っていますが、数学的センスがあり博識な方も多くおられるようなので、頼りにしました。
（toto） 2019/06/26(水) 13:53
ようやく方法2が解りました。
方法1だと、チャンとランダムになってくれるか不安がありますからねぇ。 ※まぁ、200個もあればそんなに偏ることもないでしょうが・・
それより、データの全貌が分かっているので、 偏りの心配をしないでいい抽出方法を使う、と言うことですか。
ただ、方法2だと、階級数の数しかバリエーションがないですから、 200個と言っても、200種類じゃないことになります。 ※シミュレーションの計算負担は減りますが、 それがメリットなのかこちらでは分からないですけど。 200個選ぶという意味が何なのかに照らし合わせる必要があります。
そうなると、データを昇順に並べて、 一定の間隔で200個摘み取って行くらいでよさそうな気がします。
（半平太） 2019/06/26(水) 15:24
階級 度数 0 3 0.1 1 0.2 2
下記のようにデータ化⇒ランダム化するイメージです。
データ化 0 0 0 0.1 0.2 0.2 ランダム化 0 0.2 0 0.1 0.2 0
先にも書きましたように、実データを度数表化→換算度数表の作成過程で「まるめ」が避けられません。
が、統計量はそれなりに近似できるのではないかと思うのですが、やはり実データからの抽出の方が
理にかなっているのでしょうか．．．
（toto） 2019/06/26(水) 17:11
まだ、数学的センスがあり博識な人の目に留まってないみたいですね。
＞やはり実データからの抽出の方が理にかなっているのでしょうか．．．
そんなことはないです。 シミュレーションですから、理想的な架空データが最善と言えるんじゃないですか。
＞データ化⇒ランダム化するイメージです。
ここがよく分からないです。 なぜランダム化する必要があるんですか？
順番が大事な要素なら、並べ替えの類は一切やってはいけないと思うんですが。
とにかく、シミュレーションの詳細が何か分からなければ、 何が適切なサンプリングなのか、他人には判断つかない・・(のではないかと思う。）
（半平太） 2019/06/27(木) 10:29
「データを昇順に並べて、一定の間隔で200個摘み取って行く」
半平太様のこの方法がいいのでは、と思い始めています。
（より過去データの統計量を引継げそうに思えます）
「ランダムにする必要性」
過去の統計量はそのままにいろいろな出現パターンで投資案を検証したいからです。
（出現パターンの発生は乱数で行う予定です）
例えば、最初からいきなり回収率「0」が10回も連続して発生すれば、私の資金力では即パンクしてしまい意味ないですから。
（もちろんその発生リスクはゼロではないわけですが）
未だ明確な方針が決まっているわけでもないですが、先ずは半平太様の方法でシミュレーションできるように手段を模索中です…
（他の作業との兼ね合いで、覚えたてのマクロを予定しています）
（toto） 2019/06/27(木) 13:44
素人案だけ書きますと、過去の実データが複数あるなら平均を求めて、平均値でデータを作るとか、完全ランダムではなく、過去値を少し上下させるだけのランダムにするとか。
あとはExcelらしさを出すなら、過去データで折れ線グラフを描いてから、系列を右クリックして「近似曲線の追加」をしてみるとか。 うねうねと上下するようなデータでも、多項式近似にして次数を増やすと、それっぽい曲線を描いてくれますよ。 良さそうならば、近似曲線のオプションで数式表示し、この式を元に、計算式で1点毎の値を求めて表にすれば良いです。
（???） 2019/06/27(木) 14:03
慣れてくると、変動具合からパターンを読まれる可能性はありますけど。
（???） 2019/06/27(木) 14:15
統計学の知識がないとキビしそうなので、ご助言をもとにいろいろ試してみたいと思います。
ブートストラップ法は初めて目にしましたが、何となく私がやろうとしているのに近いのかな、
との印象を受けました。
（浅はかなことは承知での、あくまでも現時点での印象です）
で、半平太様の方法を実現すべくコードを作成中なんですが、早くも躓きまして
下記の「data(k, 2) = tg(r, 1)」のエラー（応答なしになります）を回避したく、
ご教授願えないでしょうか？
Sub test()
Rem 予めＤ列にデータをコピーしておき、100個分のデータを選択してから実行します
Rem Ｄ列を昇順並び替えした後に50個を等間隔で抽出します
Rem あとでランダム化するために、乱数で整数値を50個に割振っています
Rem 結果をＦ，Ｇ列に出力します
Dim RNG As Range
Dim tg(), data()
Dim r As Long, i As Long, k As Long
Dim cnt As Long 'データ数
Dim num As Long '割振り数
Dim flg() As Boolean '乱数発生用
Const ct As Long = 50 '抽出数
Dim stp As Long '間隔
'// 前処理（未）；データコピー、出力先クリアなど
'........
Set RNG = Selection
'// 昇順
RNG.Sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlYes
'// 選択範囲の変更
Dim cntRow As Long
Dim cntCol As Long
cntRow = RNG.Rows.Count - 1
cntCol = RNG.Columns.Count
Set RNG = RNG.Offset(1, 0) '1行下にずらす
Set RNG = RNG.Resize(cntRow, cntCol) '1行削る
'// 抽出
tg = RNG.Value '格納
cnt = UBound(tg) 'データ数
ReDim data(1 To ct, 1 To 2)
ReDim flg(1 To ct)
Randomize
k = 1
stp = WorksheetFunction.RoundUp(cnt / ct, 0)
For r = 1 To cnt Step stp
For i = 1 To ct
Do '重複しない乱数（整数）を生成
num = Int(Rnd * ct) + 1
If flg(num) = False Then
flg(num) = True
Exit Do
End If
Loop
data(k, 1) = num
data(k, 2) = tg(r, 1) '--->Error
k = k + 1
Next i
Next r
'// 出力
Range("F2:G" & ct + 1).Value = data
'// 昇順並び替え（未） '........ End Sub （toto） 2019/06/28(金) 10:38
rのループ(またはiのループ）は必要ないです。
どっちか一方があれば、相手方の位置を計算できるので。
Sub test()
Rem 予めＤ列にデータをコピーしておき、100個分のデータを選択してから実行します
Rem Ｄ列を昇順並び替えした後に50個を等間隔で抽出します
Rem あとでランダム化するために、乱数で整数値を50個に割振っています
Rem 結果をＦ，Ｇ列に出力します
Dim RNG As Range
Dim tg(), data()
Dim r As Long, i As Long, k As Long
Dim cnt As Long 'データ数
Dim num As Long '割振り数
Dim flg() As Boolean '乱数発生用
Const ct As Long = 50 '抽出数
Dim stp As Long '間隔
'// 前処理（未）；データコピー、出力先クリアなど
'........
Set RNG = Selection
'// 昇順
RNG.Sort Key1:=Range("D1"), _
Order1:=xlAscending, Header:=xlYes
'// 選択範囲の変更
Dim cntRow As Long
Dim cntCol As Long
cntRow = RNG.Rows.Count - 1
cntCol = RNG.Columns.Count
Set RNG = RNG.Offset(1, 0) '1行下にずらす
Set RNG = RNG.Resize(cntRow, cntCol) '1行削る
'// 抽出
tg = RNG.Value '格納
cnt = UBound(tg) 'データ数
ReDim data(1 To ct, 1 To 2)
ReDim flg(1 To ct)
Randomize
k = 1
' stp = WorksheetFunction.RoundUp(cnt / ct, 0) ←不要★1/3
' For r = 1 To cnt Step stp ←不要★2/3
For i = 1 To ct
Do '重複しない乱数（整数）を生成
num = Int(Rnd * ct) + 1
If flg(num) = False Then
flg(num) = True
Exit Do
End If
DoEvents
Loop
data(k, 1) = num
r = WorksheetFunction.RoundUp(cnt * k / ct, 0) '挿入★1/1（rの位置を特定する）
data(k, 2) = tg(r, 1) '--->Error
k = k + 1
Next i
' Next r ←不要★3/3
'// 出力
Range("F2:G" & ct + 1).Value = data
'// 昇順並び替え（未） '........ End Sub
（半平太） 2019/06/28(金) 14:06
が、ご提示コードを見てもエラー原因がわかりません。
挿入コードを含めて「解説」願えないでしょうか？
（toto） 2019/06/28(金) 15:55
＞どれも差異2%未満で満足のいくものでした。
2%もあったですか・・・
2,4,6,8,・・,100 だから、プラス方向に少しずれますかね。
1,3,5,7,・・,99 も求めて、足して2で割った「架空値」にすれば、ほぼピタリのハズです。
＞、ご提示コードを見てもエラー原因がわかりません。
iはDATAの位置番号です。 その番号に対応するtgtの番号を求めれば終わる話ですよね？
iが1,2,3・・なら、tgtのkは2,4,6・・
そういう対応関係を求める式が r = WorksheetFunction.RoundUp(cnt * k / ct, 0) '挿入★1/1（rの位置を特定する） ※割り切れない場合も想定して、そんな数式になっています。(kのMaxがctと一致する必要がある）
なので data(k, 2) = tg(r, 1) ← これでDATA配列は求まります。
そのループの外側にrのループがあったら、折角求めたDATA配列をまた一からrで書き換えてしまうことになります。
エラーになるかどうかとは直接関係ないです。rのループは有害無益なんです。
あと、何故kとiを二つも使っているのか、こちらでは分からないです。 同じ値ですよね？
（半平太） 2019/06/28(金) 17:15
エラーになるかどうかとは直接関係ない・・・rのループは有害無益 あと、何故kとiを二つも使っているのか、・・・同じ値
無意味なループで「応答なし」になってたんですかね．．．
初歩的なことでご丁寧な説明、ありがとうございました。
抽出データの差異が５％未満なら想定内のつもりでしたが、学問的？にはどうなんでしょう。
???様やγ様のご意見も踏まえつつ、抽出のしかたやその後のランダム化（並べ替え）について
もっと検討したいと思います。
他にもエクセルの初歩的と思われる事でつまずいており、なかなか前へ進めませんが、
これは別途アップさせていただきます。
これからもよろしくお願いします。
（toto） 2019/06/30(日) 12:17
（γ） 2019/06/30(日) 23:36
未だ構想が固まっていないのでうまく説明できませんが、
将来の投資に対して今までのリターンをベースとした場合に、今考えている投資法が通用するかどうか、
をシミュレートしようとしています。
そのために基本統計量はそのままに、出現パターンをいろいろ変えてみたらどうなるかをみてみようと思っています。
（リターンやヒット率はある意味現状の自分の能力なので大きく変化はしないが、出現パターンは“波”のようなもので変わり得るもの→どのようなパターンでも破産しない投資法をつくりたい）
当然ながら「将来のリターンやヒット率が今までと同じとはいえない」という当たり前のリスクは想定内です。
実データとシミュレートに使うデータがどこまで近似できるか、基本統計量だけに注目していていいのか、等々疑問はあります。
目下のところ、手段で使うエクセルの作業に戸惑りなかなか前に進めないのが最大の“障害”で．．
これからもよろしくお願いします。
（toto） 2019/07/01(月) 10:19
（γ） 2019/07/01(月) 23:23
最初はこんな内容に回答いただけるか不安でしたが、いろいろ気付かせていただいて
本当によかったと本心から思っています。
皆様ありがとうございました!!
（toto） 2019/07/02(火) 09:13
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.