※2021/08/22に「RssMarket」が更新できない問題の解決を追加
楽天証券のマーケットスピード IIのRSSはExcelのアドオンとして使用できますが、銘柄を自動で変更したり、取得したデータを貯めたりしたい場合には手作業だとかなりの時間がかかってしまいます。
そこでVBAで自動化させようと思ったときに、下記のような問題が発生します。
- RSS関数で取得する値が更新されない
- 「RssMarket」が「1」の方法では更新されない ←(2021/08/22 追加)
- 関数使用上限がある
結構調べて回り、ようやく解決したのでその方法を書いていこうと思います。
1.RSS関数で取得する値が更新されない
※「RssMarket」は使用できないため、「2」を参照
VBAでRSS関数の銘柄コードを変更しても、値を取得することができません。
これはVBA実行中にRSS関数での通信ができないためらしいです。
そのため、RSS関数での通信ができるようにしてあげる必要があります。
使用するのは「DoEvents」です。
例えばRSS関数で銘柄名を取得するとします。
銘柄名が取得できるまでループを回して、その中で「DoEvents」を実行します。
しかし、そのままだと一生ループを抜け出せないため、銘柄名が変更になった場合に脱出するようにします。
コードで書くと下記のような感じです。
※条件のところは面倒くさいので端折ってます。あくまで雰囲気で
Do
DoEvents
If (今の銘柄名) <> (1つ前の銘柄名) Then Exit Do
Loop
これで値は更新されるようになりました。
2.「RssMarket」が「1」の方法では更新されない
「RssTrendSMA」や「RssChart」の場合はDoEventsで行けたのですが、「RssMarket」ではできませんでした。
違いがよくわからないですが、その場合は「RssMarket」で更新されるまで「Application.OnTime」で再帰処理を繰り替えすことで解決できます。
この場合、「1」の「DoEvents」は使用する必要がなくなると思います。
例えばRssMarketで銘柄名を取得して、条件を「銘柄名が前レコードの名称と同じ、あるいは、空欄」場合は再帰処理を実行し、そうではない場合は続行するとします。
下記のような感じです。
※処理や条件のところは面倒くさいので端折ってます。あくまで雰囲気で
Function OnTimeで取得()
‘最後まで実行したら、再帰処理せずに処理を終えるようにする
If (現在の処理行) > (最終行) Then
Exit Function
End If
‘コード変更処理
‘銘柄名が前レコードの名称と同じ、あるいは、空欄の場合は再帰処理
If (前レコードの銘柄名) = (RssMarketで取得した銘柄名) Then
Application.OnTime Now + TimeValue(“00:00:01”), “OnTimeで取得”
Exit Function
End If
‘値をシートに転写するなどの処理
‘再帰処理
Application.OnTime Now + TimeValue(“00:00:01”), “OnTimeで取得”
End Function
3.関数使用上限がある
例えば「RssTrendSMA」という関数の場合、PC1台で1度に表示できる銘柄数は50個です。
これはExcelに入れたRSSのアドオンによって制御されているらしいので、51個以上を1度に表示することはできません。
そのため、51個以上の銘柄のデータを取得するためには、VBAで銘柄コードを50個ずつ書き換えて、取得した値を別シートにコピーするという方法になります。
これ自体は簡単にソースを組めると思います。
しかし、実際に実行してみると銘柄コードの51個目で関数使用上限になります。
どうやらVBA実行中は1度とカウントされ、50個までしか取得できないようです。
そのため、1個の関数で銘柄コードを51回変更した場合でも、VBA実行中は関数を1度に51回関数を使用したと判断されます。
これを回避するため「Application.OnTime」を使用します。
OnTimeは指定した時間後にVBA(プロシージャー)を実行することができます。
1回目実行で50件→1回目終了→OnTimeで2回目実行で50件→2回目終了…………といった感じになり、2回目実行時には1回目が終了しているので、1度とはカウントされず、51個目以降の50個を取得できるようになります。
なので取得したい銘柄数/50回分だけ、OnTimeでVBAを実行します。
コードで書くと下記のような感じです。
※処理や条件のところは面倒くさいので端折ってます。あくまで雰囲気で
Function OnTimeで取得()
for i = (現在の処理行) To (最終行)
‘コード変更処理
‘コード行数カウント ※シート内に値転写
If (コード行数カウント) Mod 49 = 1 Then
Application.OnTime Now + TimeValue(“00:00:01”), “OnTimeで取得”
Exit For
End If
Next i
End Function
※コード行数カウントは2行目からを想定しているため、スタートが2になります。
1からの場合は、カウントが1の場合はIF文内に入らないようにしてください
上記では1秒後に自分を呼び出す形にしました。
自分を呼び出す必要があるため、行数や列数など動的に変わる値はシートに記載してください。
そして、処理内ではシートの値を取得して使用してください。
また、「(コード行数カウント) Mod 49 = 1」という条件でOnTime を実行し、ループを抜けて処理を終わるようにしています。
「(コード行数カウント) Mod 49 = 1」は、50個くらいごとにOnTimeを実行したいため、50を49で割って1だったら実行するようにしてます。
重要なのは50個を処理する前にOnTimeで再帰処理をするということです。
ちなみに再帰処理というのは自分で自分を呼び出す処理になります。
そうすると永遠に自分を呼び出し続けることになりますが、「for i = (処理行数) To (最終行数)」としているので最終行数まで処理したら終わるようになってます。
おわりに
調べ疲れたのでコード細部が適当になりました。ごめんなさい。