「証券コード」から「銘柄名」と「株価」をエクセルに自動取得する方法メモ（優待クロス用）

最近始めた優待クロス取引は「エクセル」を使って管理していますが、銘柄数が多いと証券コード（大半が4桁の数字）や株価、銘柄名の入力がとにかく面倒くさいです！しかもよく間違う！

Webスクレイピング（Webサイトから情報を自動的に抽出する方法）がエクセルでも出来ればいいのですが、「Google スプレッドシート」と違ってエクセルだと何だかやたら難しそう・・（汗）

 

と諦めかけていたのですが、エクセルでも使えるプログラム言語、VBA（Visual Basic for Applications）の関数をコピー＆ペーストで貼り付ければ誰でも簡単に「株価」と「銘柄名」のWebスクレイピングが出来る方法がある事が分りました！

これは超便利！

・・PCを変えたら速攻で忘れて出来なくなりそうなのでメモします！

 

　エクセルでの簡単なWebスクレイピング方法　

「開発」メニューの表示

エクセル > ファイル > オプション > リボンのユーザー設定 > 「開発」にチェックを入れる 

 

「xlsm形式」で保存し直す

エクセル > ファイル > 名前をつけて保存 > 保存場所を選択 > ファイルの種類で「Excel マクロ有効ブック」で保存する

 

VBA関数の入力（詳細は以下のブログを参照）

・「証券コード」から「銘柄名」をWebスクレイピング

・「証券コード」から「株価」

・（おまけ）「証券コード」から「配当利回り」

 

Webスクレイピングの使い方

エクセル > 株価ないしは銘柄名を入力したいセルを選択 > タブの「数式」 > 関数の挿入 > 関数の分類で「ユーザー定義」を選択 > 「STOCKNAMEJP」で「銘柄名」、「STOCKPRICEJP」で「株価」が読み込まれる > 「関数の引数」を聞かれるので、「証券コード」を入力しているセルを選択

 

注意点

・Webスクレイピング処理が結構重いのか、コピペで多数の処理をすると結構時間がかかる

・Webスクレイピングした時点での情報（例えば株価）が入力されるだけなので、情報を更新したい場合は関数のセルを再度コピペする

・「証券コード」のセルが空欄なのに多数の関数だけ貼り付けたままにしていると、たまにエクセルが入力を受け付けなくなる（固まる）が、他のシートを選択してから戻ると何故か治る。同様の症状が頻発するようなら「証券コード」を入力後に関数をコピペする様にするのが良さそう

・存在しないか、参照先の「Google Finance」にまだ登録されていない「証券コード」を参照すると、「銘柄名」が「Direxion Daily 7-10 Year Treasury Bear 3X Shares」となってしまう。IPOに使うのは難しいか

 

　優待クロスでの実例　

エクセルの手入力は最低限にして（それでも結構多い・・）、なるべくミスを防ぎます。

 

 

VBAや数式でエクセルに自動入力する例

 

実際にどの銘柄をクロスするかは、図の右側の「(必要)資金」や「効率」（貰える優待額に対する必要資金％）、「優待（で貰える物）」、「経費率」（貸株料等の優待額に対する比率%）などを見て決めています。

「(必要)資金」は念の為、日興は現物の105%（経費などの分）、SBIは120%（現物の寄成注文の拘束資金が制限値幅の上限になる分）で計算しています。SBIの現物買いは指値注文にすればもう少し拘束額を減らせるはずです（但し約定しなくなるリスクは増える）。

 

現物買いや現引きに使える資金量の確認は、

日興では

Webトップ > 残高詳細 > MRF・お預り金 > 「買付可能額」で確認し、「信用保証金」に多額の資金が入っている場合は「信用保証金振替」で「お預り金」に戻します（即時反映）

 

SBIでは

Webトップ > 口座管理 > 「買付余力」で確認しますが、新NISAの毎日積立をやっている口座なのでいくらか余裕を持たせて使います

 

信用売りも株価の30%相当額が資金拘束（預託保証金、最低30万円）されますが、クロスで持っている現物株価の80%が「代用有価証券」として保証金にカウントされるので、通常は信用売りの必要資金を気にする必要はありません。但しNISA分や米株などは「代用有価証券」としてカウントされないので、（Tochiの様に）それ以外の日本株を全く持っていない場合には特定日に一気にクロス取引をせず、日をずらして徐々に行う方が「預託保証金」で買付余力が削られない分、資金効率が改善します。

なお、現物が約定後、「代用有価証券」として保証金にカウントされるまで数日かかる様です（約定日から2営業日後=受渡日）。

 

もし今後制度信用を使う場合は、貸株料に加え逆日歩や配当の儲け分（最大で配当の15.315%；配当クロス）なども考慮にいれる必要がありますね・・（遠い目）

 

 

あぁっ、資金量と優待品の好みに応じて銘柄選択から発注、現渡まで全自動でやってくれるアプリの登場が待ち遠しいっ！（チラッ・・）