Excelマクロの使い方

やっと、正確なデータが出てくるマクロが出来上がりました。

これまでブログで載せたとおり、

・絶対値を使って8通りを検証すればいいこと

1.BBBB:SSSS　　2.BBBS:SSSB
3.BBSB:SSBS　　4.BBSS:SSBB
5.BSBB:SBSS　　6.BSBS:SBSB
7.BSSB:SBBS　　8.BSSS:SBBB

・ワークシートとマクロとが同じ解を出すこと

前回載せたとおり、Excelマクロで計算したデータをワークシートに載せて確認する。

・サブルーチンを使う

これも過去のブログに載せたないようですが、結果言うと、最大の絶対値と、４通りの売買の組み合わせを記録する、部分のみサブルーチンにしています。

その結果、

2010/1/4 - 2023/12/29 までのデータからは、

最大の絶対値が

4,000

売買の組み合わせは、（-1＝売り、1＝買い）

-1、1、-1、-1

となってます。

ここで疑問に感じるとは思われます。

数値は、売り、買い、売り、売り

のように見えます。

これは、４で割った余りの順番です。

なので、一番初めのデータは、あまりが「１」となります。上の

-1、1、-1、-1

の結果は、余りが、0、1、2、3　の順序で計算しています。

その結果、

買い、売り、売り、売り

の絶対値、4000が最大値となります。

次に、

1.BBBB:SSSS　　2.BBBS:SSSB
3.BBSB:SSBS　　4.BBSS:SSBB
5.BSBB:SBSS　　6.BSBS:SBSB
7.BSSB:SBBS　　8.BSSS:SBBB

の８番目の組み合わせが該当するので、この組み合わせをExcelシートに落とします。その結果、

買い、売り、売り、売り

では、-4000

結果として、

売り、買い、買い、買いが+4000となり、ワークシートで確認が取れました。

Excelマクロで要した時間は、2.56秒でした。

今回作成したマクロで大きく前進したと思っています。

まあ、今回の記録はそれだけなんですが・・

Excelマクロの使い方（ごまかし記事）

先週は、すっかりブログの記事を書くのをすっかり忘れていました。

曜日感覚がないわけではないのですが、木曜日に記事を掲載するため、土日を使い、修正・校正を木曜日までに行うというルーチンでやっています。

Excelでのマクロを書いているのですが、前にも載せましたが、正確なデータが計算されているかを検証する作業に時間がかかっていました。

MsgBoxを使ってデータを検証する人が多いらしいですが、前々回に掲載した記事で

ブレークポイントを使って検証していました。

ブレークポイントを使った場合、MsgBoxを使った検証よりもデータの検証がしやすかったのですが、これもまた、時間のかかる作業です。

今回は、検証しやすい方法を探していました。

簡単な方法がありました。

これまでは、Excelマクロを書いてから、マクロを実行して、Excelシートにデータを移してから結果を検証するというやり方をしていました。

わかりやすく

論理工程を考える

論理工程をそのままExcelマクロに直打ちする

作ったExcelマクロを実行する

結果を保存する（覚えておくとか、メモしておくとか）

同じ論理工程をExcelシートに関数などを使って、セル上に表示し、結果を出す

この時に、結果が異なることが往々にしてあるのです。

そのため、原因を見つけるためのExcelマクロの検証作業があります。

この時に、MsgBoxを使うと、約3000行のデータから一部を抽出して検証することになります。では、いくつ、どのあたりを抽出すればいいのでしょうか、という難問があります。

恐らく、100～300くらいのデータを検証する必要があると思います（経験則）。

ブレークポイントを使った場合

これは、単純にブレークポイントを表示させた例です。

Excelマクロを十数行あった場合、ブレークポイントまでの各数値は、マウスのポインタを置くことでその数値が表示されますので、算出結果の経過が分かり、Excelマクロの間違いを探すのに便利です。ただし、この方法は、順番に検証していくことになり、私の場合、約４０巡したくらいでめげます。

ただ、単純なExcelマクロの間違いを探すのには便利です。

今回試したのは、Excelマクロで計算したデータの一部、例えば、終値ー始値、だけをExcelシートに表示させる方法です。

この方法だと一つの項目に対し、全数検証できます。

すべてのデータを表示すれば？

と思う方もいるとは思いますが、次の数値を見てもらうと作業時間の差が大きいことが分かると思います。

ここでは、

・2010/1/4～2023/12/29日までの、項番、日付、始値、高値、安値、終値のデータをExcelシートからExcelマクロの配列に読み込む

・曜日を算出する

・終値ー始値を算出する

・売り買い４通り区分するための割り算の余りを算出する

をExcelマクロを作成します。

上の、0.0390625がマクロのみで計算された場合の時間になります。約0.04秒ですね。

下の、0.1171875が、割り算の余りをワークシートに載せた場合の時間です。約0.12秒です。

一か所だけを表示させるには、Excelマクロのみで算出される場合の３倍の時間がかかるのが分かります。

データの配列への保存と３つの算出だけなので、１秒もかかっていませんが、これがもっと複雑なExcelマクロになると時間がかかることが想像できます。

下が割り算の余りを算出するマクロです。

 For row01 = 2 To 最終行
    マーケットデータ(row01, 9) = (マーケットデータ(row01, 1) Mod 4)
    Cells(row01, 9) = マーケットデータ(row01, 31)
    Next

Cells(row01, 9) = マーケットデータ(row01, 31)の部分が、ワークシートにデータを表示させる部分です。

これを無効にしておく場合、 'Cells(row01, 9) = マーケットデータ(row01, 31)すればExcelマクロ上、無効になります。

今は、こんな形で、手の空いた時間に検証していっています。

Excelマクロの使い方

この表題でいいのか。

ちょっとだけExcelマクロでの進展がありました。

これまで、配列を使ったマクロで売買の最適化を探ってきました。

ところが、配列を使った場合、配列データがサブルーチンに飛ばせないため、プログラムが冗長になることが分かっていました。

そのため、サブルーチンで利用可能なプログラムを同じメインに記載する必要がありました。

ちなみに、メインとは、主となるプログラムで、サブルーチンとは、メインから分離したプログラムで、メインに計算結果を返すプログラムのことです。

言葉では、わかりにくいと思いますので今まで計算を使用としていたプログラム例を紹介します。

相場データを読み込む

4通りの売買の組み合わせを計算する

その結果を記録する

記録した結果が以前の結果と比べる

数値が大きいほうの売買データとその結果を記録する

上の４通りの売買は、１６通りあるため４回繰り返す

最終的に数値が大きくなった売買データおよび最大値の結果を表示する

とこのように売買の最適化の流れを分解したものです。

ここで配列データを利用する箇所は次のとおりです。

相場データを読み込む

4通りの売買の組み合わせを計算する

その結果を記録する

記録した結果が以前の結果と比べる

数値が大きいほうの売買データとその結果を記録する

上の４通りの売買は、１６通りあるため４回繰り返す

最終的に数値が大きくなった売買データおよび最大値の結果を表示する

赤字が配列を使用するところで、その他がサブルーチンとしてプログラムを作成できる箇所です。

これは、あくまで私が考えている計算方法なので、ほかの計算方法により、解決することがあります。

私のとって、サブルーチンをメインから独立させてプログラムを作成するには大きなメリットがあります。これは、各サブルーチンが正確に動作していることを確認する作業がメイン内を確認する作業と比べて検証が楽だからです。

上のとおり配列データがプログラムの間に使用するため、サブルーチン化することが出来ず、プログラムが冗長になり、どの部分で計算違いが起きているのか？を調べる作業に時間がかかります。

今回、ネット上に見つけたのは、この配列データをサブルーチンに受け渡す方法です。

どこかにあるはずだ、と思い本屋やネット上を探していたのですが、配列を利用した方法は載っているものの、配列を上のような工程で利用できる記載はありませんでした。あったとしても、冗長なものだったり、「答え」あっての制作なので、私のニーズに合いませんでした。

まあ、Google検索利用のコツを知らないという点は否めません。また、最近のGoogle検索では、上位に掲載されるのが「スポンサー」が多く、検索の意味をないしていないことを感じており、ネット上の検索は、忍耐が必要になってきている感があります。

言い訳のようになりますが、これはGoogleのビジネスモデル上仕方がないことなので受け入れています。決してGoogleのせいではありません。

本題に戻ります。

当たり前といえば、当たり前の話なのですが、「Function」をサブルーチンとして使えば、うまくできそうです。

もちろん、「Function」でなく、普通の「Sub」でもできるのでないかとも思いました。

プログラムを分離して検証できる見込みが立ちそうなので、もう少し良い話題を提供できるかな？と私自身が期待しています。

ちなみにこんな形です。

Sub main()

    Dim pl01()
    pl01() = test01()

    
    For row01=2 to endrow
        test01
    Next

End Sub

Function test01()

    Dim pl(2 to endrow)
    For row01 = 2 to endrow
    pl01(row01)

    test01 = pl01()

End Function

mainは、かなり端折っています。また、まだ完全に検証できていませんが、一部のデータでこの方法で作成したマクロがうまく機能していることが分かっています。

希望は見えてきているのだが。

次回に期待したい。
 

半分はごまかしと思ってください

さらっと読む程度の記事です。

今年に入ってからの日経平均はすごいですね。４万２千円を超えていますね。

ということで、２０２４年１月から６月末までの日経２２５先物取引のデータを見てみます。データは、期近のデータになります。

ローソク足を見てみます。

グラフの軸が、３万２千円から４万２千円でグラフを作りました。パッと見た目では、大きな変化がないように見えますが、１万円の幅があることを考えれば、大相場ではなかったのではないでしょうか。

これまで載せた売買法則、「勝率がおよそ５０％になる」についてみてみました。

取引数：121

終値-始値を基準にして次のとおり、

プラス：６６

マイナス：５１

引き分け：４

勝率：５４．５５％

始値で買い、終値で売った場合、

２５５万円の益となりました。

１日の最大益：８２万円

１日の最大損：８６万円

（ラージ取引で換算）

証拠金の必要額が２５０万円くらい必要なので、証拠金５００万円を元金と考えると、年利約２００％なのでしょうか。

これまで、ブログで載せたとおり、宵越しの建玉を持たないという、リスクを少なくした取引で、年利約２００％は、安全な投資ではないでしょうか。

まあ、後付けですがね。

どちらが本題なのか？という内容になります。

検証するデータは、直近半年のデータではなく、これまで検証してきた２０２３年末までのデータです。

このブログで

1.BBBB:SSSS　　2.BBBS:SSSB
3.BBSB:SSBS　　4.BBSS:SSBB
5.BSBB:SBSS　　6.BSBS:SBSB
7.BSSB:SBBS　　8.BSSS:SBBB

と８通りの組み合わせで得られた絶対値が大きい４つの売買の組み合わせを見つければよいということを載せました。

これに沿って、Excelのワークシート上の数字をマクロで変え、検証してみました。

答えから。おそらくマクロの計算速度のほうが、ワークシート上の関数計算速度よりも早いため、正確な計算が出来なかったようです。

マクロの「If文」に来た時に、ワークシートのデータを読み込むところの変数がない状態でした。

ワークシート上のセルの計算式は、ABS関数（絶対値）にSUM関数を入れたものです。

=ABS(SUM(x....xx))

この関数が計算される前に、マクロ上の変数に代入されているようで、「If文」を加えるとプログラム的に正しく（この言葉でよいか？）動きません。

あとは、８通りの結果を羅列して、人間の目で見つければ・・・

あまり、意味がないので、マクロのみで結果を出したいと思いました。

ちなみに、マクロが正常な値を出しているか否かを確認する場合、多くの人は、

MsgBox(xxx）

で数値を確認しているそうですが、私は面倒なので、

下のように、マクロ文の横にある余白のところに「赤丸」を付け、変数データをマウスのポインタで確認しています。

ちなみに「ブレークポイント」というそうです。

余白にブレークポイントをいくつかつけて、データを確認していくわけです。

残念ながら何もしていません

書く記事がない状態。また、木曜日の更新も忘れ。

ボケてはいないのだが、夏バテはしている。また、日経平均が４万を超え、喜んでいる人が多いかと思います。（この文そのものがボケているのか、夏バテしているのか。あえてそのままにしています）

ただ、これは幻想です。

前にも載せたとは思いますが、売る人がいるから買えるのです。株価が上がったからと言って、皆がハッピーではありません。

タラレバの人も多いと思います。

遠足は帰るまで。

株は売るまで。

-・-・-・-・-・-・-・-・-

突然ですが、エコノミストの話。

いわゆる、エコノミストは、今後の相場について予測しますが、当たるとは限りません。

当たらずとも遠からずというところですかね

邪推でしかありませんが、エコノミストは、バーナム効果を使っています。もちろん、予測を書いているエコノミスト本人は、無自覚で使用しているのでしょうが。

バーナム効果とは、誰にでも当てはまるような曖昧で一般的な記述を、自分だけに当てはまると思い込んでしまう心理的現象です。

本当にかっくじつな予測ができるなら、記事を載せず、黙って自分で売ったり買ったりするはずです。

「株で勝利する方法」これは幻想です。なのに本を売ろうとしていて、買う人もいるとは、不思議なものです。

もちろん、私も同じで、「これは勝てる」というモデル等を見つければ、載せません。

ここに載せているのは、

１．日経２２５先物取引はニッチな取引なので、あまり興味を持つ人はいないだろうと高をくくっているいます。

２．載せている情報は、あくまでも統計データの処理による考察でしかない。

３．先物取引をするまでのハードルが高い

４．そもそもビュー数が少ない。

そんな感じなので、載せています。

「あ～また、ビュー数が一桁か」という状態なので、気にせず記事を載せられるのです。

ビュー数が増えたら、バーナム効果を使おうかな。