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

最近始めた優待クロス取引は「エクセル」を使って管理していますが、銘柄数が多いと証券コード（大半が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%；配当クロス）なども考慮にいれる必要がありますね・・（遠い目）

 

 

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

 

 

== 25.1.17追記 VBAマクロの有効化 ==

年が変わったので別名で新しいエクセルを作成して同じことをしようとしたのですが、なぜかエラーに。

調べた所、どうやら場合によってはまずは「VBAを有効化する設定」を行う必要がある様です。

 

しかしなぜ別名で保存しただけでVBAが有効から勝手に無効になるのか・・謎すぎるエクセル仕様！なお、VBAマクロはファイルごとに新しく関数を定義する必要があるようです。

 

 

== 26.05.06追記 突然使えなくなったのでAIでコードを修正 ==

突然機能しなくなったものの、参照先のHPが消えてしまい自分ではどうしようも無さそうだったのでClaudeにコードを修正してもらいました。

 

一応動いたようなので、新しいコードを以下に示します。

 

「STOCKNAMEJP1」で「銘柄名」をWebスクレイピングするVBAコード

====================================

Function STOCKNAMEJP1(code As Variant) As String
Dim str_url As String
Dim buf As String
Dim http As Object
Dim stm As Object
Dim s As Long
Dim e As Long
Dim p As Long
Dim title As String
Dim tmp As String

str_url = “https://www.google.com/finance/quote/” & CStr(code) & “:TYO?hl=ja”

On Error GoTo ErrHandler

Set http = CreateObject(“MSXML2.ServerXMLHTTP.6.0”)
http.Open “GET”, str_url, False
http.setRequestHeader “User-Agent”, _
“Mozilla/5.0 (Windows NT 10.0; Win64; x64) ” & _
“AppleWebKit/537.36 (KHTML, like Gecko) ” & _
“Chrome/124.0.0.0 Safari/537.36”
http.setRequestHeader “Accept”, _
“text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8”
http.setRequestHeader “Accept-Language”, “ja,en;q=0.9”
http.setRequestHeader “Accept-Encoding”, “identity”
http.Send

Set stm = CreateObject(“ADODB.Stream”)
stm.Type = 1
stm.Open
stm.Write http.responseBody
stm.Position = 0
stm.Type = 2
stm.Charset = “UTF-8”
buf = stm.ReadText(-1)
stm.Close
Set stm = Nothing
Set http = Nothing

‘ <title> タグを抽出
s = InStr(buf, “<title>”)
If s = 0 Then GoTo Failed
s = s + Len(“<title>”)
e = InStr(s, buf, “</title>”)
If e = 0 Then GoTo Failed
title = Mid(buf, s, e – s)
‘ title の例:「トヨタ自動車（7203）株価、ニュース – Google Finance」

‘ 「株価」の直前までを tmp に取得
‘ 例: tmp =「トヨタ自動車（7203）」
p = InStr(title, “株価”)
If p > 0 Then
tmp = Trim(Left(title, p – 1))

‘ tmp の末尾から「（数字）」を除去するループ
‘ 末尾が「）」で終わっていれば括弧ブロックを削る
Do While Len(tmp) > 0 And Right(tmp, 1) = “）”
Dim openPos As Long
openPos = 0
Dim i As Long
‘ 末尾の「）」に対応する「（」を右から検索
For i = Len(tmp) – 1 To 1 Step -1
If Mid(tmp, i, 1) = “（” Then
openPos = i
Exit For
End If
Next i
If openPos > 0 Then
tmp = Trim(Left(tmp, openPos – 1))
Else
Exit Do
End If
Loop

STOCKNAMEJP1 = tmp
Exit Function
End If

‘ hl=en の英語ページが返った場合: ” (コード)” の直前
p = InStr(title, ” (” & CStr(code) & “)”)
If p > 0 Then
STOCKNAMEJP1 = Trim(Left(title, p – 1))
Exit Function
End If

Failed:
STOCKNAMEJP1 = “失敗|title=” & title
Exit Function

ErrHandler:
STOCKNAMEJP1 = “エラー: ” & Err.Description
Err.Clear
End Function

====================================

 

 

「STOCKPRICEJP1」で「株価」をWebスクレイピングするVBAコード

====================================

Function STOCKPRICEJP1(code As Variant) As Double
Dim str_url As String
Dim buf As String
Dim http As Object
Dim s As Long
Dim e As Long
Dim priceStr As String

str_url = “https://kabutan.jp/stock/?code=” & CStr(code)

On Error GoTo ErrHandler

Set http = CreateObject(“MSXML2.XMLHTTP”)
http.Open “GET”, str_url, False
http.setRequestHeader “User-Agent”, _
“Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36”
http.Send

‘ レスポンスをShift-JISとして取得（株探はShift-JIS）
buf = http.responseText
Set http = Nothing

‘ <span class=”kabuka”> から株価を抽出
‘ 例: <span class=”kabuka”>3,000.0円</span>
Dim marker As String
marker = “class=””kabuka”””
s = InStr(buf, marker)
If s = 0 Then GoTo Failed

s = InStr(s, buf, “>”) + 1
e = InStr(s, buf, “<“)
If e <= s Then GoTo Failed

priceStr = Mid(buf, s, e – s)
priceStr = Replace(priceStr, “円”, “”)
priceStr = Replace(priceStr, “,”, “”)
priceStr = Trim(priceStr)

If IsNumeric(priceStr) Then
STOCKPRICEJP1 = CDbl(priceStr)
Else
GoTo Failed
End If
Exit Function

Failed:
STOCKPRICEJP1 = 0
Exit Function

ErrHandler:
STOCKPRICEJP1 = 0
Err.Clear
End Function

====================================