エクセルで株価を自動取得するVBAコードを紹介します。(日本株・米国株・投信対応版)

スポンサーリンク



株価や投信の基準価額を自動取得できます。


 僕は日本株の個別銘柄を、富士宮応援ファンドで15銘柄、好配当目的で29銘柄、合わせて44銘柄を保有しております。いずれも単元未満株ですが・・・

 証券会社の一覧だと少々見づらいので、自分用にエクセルシートで管理しています。
 最新株価は、ボタンをクリックすると、ヤフーファイナンスから自動取得できるようにしています。

 以前、紹介していたVBAコードは、東証上場の株式・ETFの株価だけで、動作時間も1銘柄あたり数秒かかっておりました。

 今回紹介するものは、米国上場している株式・ETFの株価や、国内投資信託の基準価額も自動取得することができます。
 また動作時間も1銘柄あたり0.5秒程度で取得することができます。(米国上場銘柄の場合は1.5秒程度かかります)

 Image20160120.jpg

 VBAコードの内容としては、上のサンプルシートに記載してある銘柄コードを元に、日米のヤフーファイナンスのHTMLソースを読み込んで、株価情報を取得しています。

2016.7.10 VersionUP:
 国内株式の地方証券取引所(札証・名証・福証)の対応をしました。

 地方証券取引所の銘柄の場合は、株式コード末尾に、
 ◆名古屋証券取引所(セントレックス含む)は.N
 ◆札幌証券取引所(アンビシャス含む)は.S
 ◆福岡証券取引所(Q-Board含む)は.F
 をそれぞれ追加して下さい。

 (例) 名証の【1413】桧家ホールディングスなら 1413.N



VBAコードです。



Sub ボタン1_Click()
Dim url As String
Dim code As String
Dim sosa_sh As Worksheet
Dim check_row, code_column As Integer
Dim now_price_column, old_price_column As Integer
Dim org_color As Integer
Dim http As Object
Dim check_txt, get_txt As String
Dim st_point, ed_point As Long
Dim price_txt, old_price_txt, tmp_txt As String

Set sosa_sh = ActiveSheet ' 現在のシートを退避
check_row = 8 ' 8行目から株価取得開始
code_column = 4 ' 識別コードの列
now_price_column = 5 ' 現在株価/基準価額の列
old_price_column = 6 ' 前日株価/基準価額の列

Set http = CreateObject("MSXML2.XMLHTTP")


' コード記載がなくなるまで繰り返す
Do While sosa_sh.Cells(check_row, code_column) <> ""
' コード取得してURL設定
code = sosa_sh.Cells(check_row, code_column)
org_color = sosa_sh.Cells(check_row, now_price_column).Interior.ColorIndex ' セルの色番号を一時退避
sosa_sh.Cells(check_row, now_price_column).Interior.ColorIndex = 6 ' セルを黄色に塗る

If Len(code) <> 8 Then 'コードが8桁以外の時(日本株式の時)
If IsNumeric(Left(code, 4)) Then ' コード左4桁が数値の時
If IsNumeric(code) Then ' コードが数値の時
url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code & ".T"
' ヤフーファイナンスの株価情報を東証コードをつけて読み込み開始
Else ' コードに市場コードが付与されている時
url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code
' ヤフーファイナンスの株価情報を読み込み開始
End If
Else ' コードが数値でない時(米国株式の時)
url = "http://finance.yahoo.com/q?s=" & code
End If
Else 'コードが8桁の時(投資信託の時)
url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code
' ヤフーファイナンスの投信情報を読み込み開始
End If

' ヤフーファイナンスをHTML形式で読み込む
http.Open "GET", url, False
http.setRequestHeader "If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT"
http.Send

If Len(code) <> 8 Then 'コードが8桁以外の時(株式の時)
If IsNumeric(Left(code, 4)) Then ' コード左4桁が数値の時
' 目印の"stoksPrice"">"の位置から現在株価を取得
check_txt = "stoksPrice"">"
st_point = InStr(1, http.responseText, check_txt) + Len(check_txt)
ed_point = InStr(st_point, http.responseText, "</td>")
price_txt = Mid(http.responseText, st_point, ed_point - st_point)

' 目印の"innerDate"と"前日終値"の位置から前日株価を取得
st_point = InStr(1, http.responseText, "innerDate")
ed_point = InStr(st_point, http.responseText, "前日終値")
tmp_txt = Mid(http.responseText, st_point, ed_point - st_point)

check_txt = "<strong>"
st_point = InStr(1, tmp_txt, check_txt) + Len(check_txt)
ed_point = InStr(st_point, tmp_txt, "</")
old_price_txt = Mid(tmp_txt, st_point, ed_point - st_point)
Else ' コードが数値でない時(米国株式の時)
' 目印の"yfs_l84_"の位置から現在株価を取得
check_txt = "yfs_l84_"
st_point = InStr(1, http.responseText, check_txt) + Len(check_txt)
st_point = InStr(st_point, http.responseText, ">") + 1
ed_point = InStr(st_point, http.responseText, "<")
price_txt = Mid(http.responseText, st_point, ed_point - st_point)

' 目印の">Prev Close:</th>"の位置から前日株価を取得
check_txt = ">Prev Close:</th>"
st_point = InStr(1, http.responseText, check_txt) + Len(check_txt)
st_point = InStr(st_point, http.responseText, ">") + 1
ed_point = InStr(st_point, http.responseText, "<")
old_price_txt = Mid(http.responseText, st_point, ed_point - st_point)
End If

' 取引があった時、当日終値を取得
If price_txt <> "---" Then
sosa_sh.Cells(check_row, now_price_column) = CDbl(price_txt)
Else ' 取引がなかった時
sosa_sh.Cells(check_row, now_price_column) = CDbl(old_price_txt)
End If
sosa_sh.Cells(check_row, old_price_column) = CDbl(old_price_txt)
Else 'コードが8桁の時(投資信託の時)
' 目印の"stoksPrice"">"の位置から基準価額を取得
check_txt = "stoksPrice"">"
st_point = InStr(1, http.responseText, check_txt) + Len(check_txt)
ed_point = InStr(st_point, http.responseText, "</td>")
price_txt = Mid(http.responseText, st_point, ed_point - st_point)

' 目印の"前日比</span>"の位置から前日比を取得
check_txt = "前日比</span>"
st_point = InStr(1, http.responseText, check_txt) + Len(check_txt)
check_txt = "<span"
If Mid(http.responseText, st_point, Len(check_txt)) = check_txt Then
st_point = InStr(st_point, http.responseText, ">") + 1
End If
ed_point = InStr(st_point, http.responseText, "(")
old_price_txt = Mid(http.responseText, st_point, ed_point - st_point)

sosa_sh.Cells(check_row, now_price_column) = CDbl(price_txt)
'現在基準価額と前日比から前日基準価額を算出
sosa_sh.Cells(check_row, old_price_column) = CDbl(price_txt) - CDbl(old_price_txt)
End If

sosa_sh.Cells(check_row, now_price_column).Interior.ColorIndex = org_color ' 退避していたセルの色を戻す
check_row = check_row + 1 ' 次の行の株価取得へ
Loop
Set oHttp = Nothing
End Sub



エクセルシートの様式に伴うカスタマイズ。


ここでシートに合わせて、カスタマイズする必要があるポイントは4つ。
上のVBAコードの14~17行目にある以下の部分です。

check_row = 8 ' 8行目から株価取得開始
code_column = 4 ' 識別コードの列
now_price_column = 5 ' 現在株価/基準価額の列
old_price_column = 6 ' 前日株価/基準価額の列

【check_row = 8】
 これはエクセルシートで、識別コードの記載が始まっている行を設定します。
 上のエクセルシートでいうと、先頭銘柄のアマゾンのティッカーAMZNが、
 シートの8行目から書かれているので8を設定してます。

【code_column = 4】
 これはエクセルシートで、識別コードの記載が始まっている列を設定します。
 列の場合は、A列=1,B列=2,C列=3・・・というように数えます。
 上のエクセルシートでいうと、
 銘柄コードが書かれているのがD列なので4を設定しています。

【now_price_column = 5】
 これは取得した最新株価や最新基準価額を書き込む列を設定します。
 A列=1,B列=2,C列=3・・・というように数えますので、
 上のエクセルシートでいうと、
 最新株価はE列に書き込むので、5を設定しています。

【old_price_column = 6】
 これは取得した前日株価や前日基準価額を書き込む列を設定します。
 A列=1,B列=2,C列=3・・・というように数えますので、
 上のエクセルシートでいうと、
 前日株価はF列に書き込むので、6を設定しています。


使用にあたり注意点です。

<注意1>
 銘柄コードが空白になっていることを条件にして、株価取得を終了するようになっています。
 売却したりして銘柄を削除する場合は、空白行にせずに、行削除等で上に詰めるようにしてください。

<注意2>
 ヤフーファイナンスのフォーム自体がいつか変わった場合、このVBAコードでは使えなくなってしまうかもしれません。

 その時は、新フォームに対応したものを紹介するつもりですが、なお、僕自身は米国株や投信の管理をしているわけではないので、 障害発生に気付かない場合がありますので、コメント等で教えていただけるとありがたいです。

 また、障害発生時にすぐには対応できない場合があります。
 米国株や投信には対応できておりませんが、こちらの旧記事で紹介している旧VBAコードでは動作するかもしれません。
 (1銘柄あたり5~10秒かかってしまいますが・・・)

 僕自身は今回紹介したコードが起動するボタンを【Quick Mode】、旧コードが起動するボタンを【Safety Mode】として、シート上に2つのボタンを用意して使っています。

<注意3>
 マクロ釦の作り方や、VBAコード記述のやり方など、エクセル操作については書籍・ネット等で調べるようにして下さい。
 また、PER等など他項目を取得したいなどのご要望に都度対応することはできませんが、いずれ上記プログラムでの株価取得の解説記事を書くつもりではおります。


サンプルシートとVBAコードがダウンロードできます。


 サンプルシートとVBAコードが記載されたエクセルファイルは、こちらのダウンロード釦をクリックすると、ダウンロードできます。

 

 なお、前日比に応じて画像や音声を出力するコードも含まれております。
 それらの設定については下記記事を参考にして下さい。

 最狂の株式・投信管理ツール誕生!! 画像と音声で楽しめる。エクセルVBAで最新株価&投信基準価額を自動取得Ver2


<ダウンロード手順>


<手順1>
 「ダウンロード」ボタンをクリックすると、
 ダウンロード方法を選択するウィンドゥが表示されるので、
 名前を付けて保存(A)をクリックして下さい。

ダウンロード手順1

<手順2>
 保存する場所、ファイル名を指定するウィンドウがでます。
ダウンロード手順2

 ファイル名の拡張子をxlsmに変更して保存して下さい。
 保存する場所は適当で構いません。

ダウンロード手順3


<初回起動のときに・・・>


 ダウンロード後、このエクセルファイルを開いたときに、次のようなメッセージが表示されると思いますが、それぞれ以下のように対応して下さい。

<保護されたビュー>
 編集を有効にする(E)をクリックして下さい。
ダウンロード手順4

<セキュリティの警告>
 コンテンツの有効化をクリックして下さい。
ダウンロード手順6

 

関連記事
スポンサーサイト

Comment

たむら #-

質問

mochi様

非常に有益なマクロ情報ありがとうございました。

ただ、データを読み込む度にwindowsセキュリティ入力ボックスが出てしまい、いちいち入力しなくてはならないのですが・・・そういうものなのでしょうか

2014/12/06 (Sat) 14:48 | URL | 編集 | 返信
mochi #-

windowsセキュリティのダイアログ対策

こんにちは。たむらさん。

> ただ、データを読み込む度にwindowsセキュリティ入力ボックスが出てしまい、いちいち入力しなくてはならないのですが・・・そういうものなのでしょうか

僕はwindows7とIE11とexcel2010の組み合わせなのですが、
今年の春頃から同じ状態になって、
しばらくは都度キャンセルで対応していました。

IEとexcel2010を以下のように設定することで解消していますが、
セキュリティに関することなので、自己責任で行って下さい。


1.IEの設定
(1)[ツール]→[インターネットオプション]を選んで、
   [セキュリティ]タグをクリックして下さい。

(2)[信頼済みサイト]を選んで、[サイト[S]]ボタンをクリックして下さい。
(3) 上の入力ボックスに、http://stocks.finance.yahoo.co.jp/を入力して 
    [追加]ボタンをクリックして下さい。
(4)このゾーンのセキュリティレベルを低(一番下)に設定して下さい。

2.Excelの設定
  ※IEの設定を行ってからでないとできません。
(1)[開発]タグを選んで、[マクロのセキュリティ]をクリックして下さい。
(2)[外部コンテンツ]のデータ接続のセキュリティ設定で、
   [すべてのデータ接続を有効にする(推奨しません)]をチェックして下さい。
(3)[信頼できる場所]で選んで下さい。
(4)[プライベートネットワーク上にある・・・]をチェックして下さい。
(5)[新しい場所の追加]ボタンをクリックして下さい。
(6)パスにhttp://stocks.finance.yahoo.co.jp/を入力して、
    OKボタンで登録して下さい。
(7)再度EXCELを立ち上げ直して下さい。

(推奨しません)と書かれている所をチェックすることになるので、
あまりお勧めできる方法ではありませんが、
今のところ、僕が確認できている方法はコレだけです。

セキュリティの部分で不安があるのであれば、
国内株式とETFだけなら、うっどさんの株ポートフォリオもお勧めです。
コピーしてExcelに貼り付けすることもできますので。

少しでも参考になれば幸いです。


2014/12/06 (Sat) 19:18 | URL | 編集 | 返信
ど素人 #SFo5/nok

教えて下さい

はじめまして。
エクセルで株を管理できないかと探していたところココに辿りつきました。

突然の質問で申し訳ないのですが、
株価だけでなく、PER・PBR・単元株数・年初来高値・年初来安値等も
自動取得したいのですがどうすればよいのでしょうか?

また、銘柄コードを入力すれば銘柄名が自動で反映されるようにしたいのですが…

VBAやマクロが全く分からない素人で申し訳ございませんが、
教えて頂けるとありがたいです。

追記)
色々調べても難しくて分からなかったので、無理を承知でお尋ねしてしまいました。
すみませんでした。

2015/02/17 (Tue) 17:52 | URL | 編集 | 返信
mochi #-

それはね・・・

禁則事項です

2015/02/17 (Tue) 20:31 | URL | 編集 | 返信
たに #-

ありがとうございました。

かなり幅広く株価、基準価格を調べられるExcelのVBAを探して、ここにたどり着きました。使い勝手のよいものを教えていただき、ありがとうございました。色々な金融機関と取引していると、こういったものが大変役に立ちます。これからも色々と教えてください。

2016/02/21 (Sun) 16:31 | URL | 編集 | 返信
mochi #-

ご丁寧にありがとうございます。

こんにちは。たにさん。

お役に立てそうで良かったです。
ご丁寧なコメントありがとうございます。
ブログ運営の励みになります。

また時々遊びに来てもらえると嬉しいです。

2016/02/21 (Sun) 21:56 | URL | 編集 | 返信
ゆゆゆ #-

はじめまして、自動取得のVBA、大変便利に使わせていただいております。

ただ、一部の証券コードでエラーが出てしまうようですので、ご報告します。
また、エラー解消が自力ではなかなか難しいので、解決方法をご存知でしたらご教示いただきたく存じます。
<エラー内容>
・実行時エラー5
・「ed_point = InStr(st_point, http.responseText, "前日終値")」部分が黄色で表示
・証券コード1413で発生(その他、3384でも同様のエラーが発生)
・VBAコードの黄色部分は上記のとおりだが、エクセル上では当日終値が黄色で表示(金額はブランクのまま)

2016/07/03 (Sun) 19:18 | URL | 編集 | 返信
mochi #-

東証上場銘柄しか対応していません

はじめまして。ゆゆゆさん。

> ・証券コード1413で発生(その他、3384でも同様のエラーが発生)

【1413】桧家ホールディングス、【3384】アークコアは、
いずれも名古屋証券取引所に上場している銘柄ですね。

あのVBAコードではアクセスする際に
市場コードとしてURL末尾に".T"を付けているので、
東証上場銘柄(マザーズ、JASDAQ含む)しか対応しておりません。

ひとまず地方の証券取引所に対応するための変更点を下記に示します。

変更箇所1(URL設定のところを変更)
If Len(code) <> 8 Then 'コードが8桁以外の時(日本株式の時)
If IsNumeric(code) Then ' コードが数値の時
url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code & ".T"
' ヤフーファイナンスの株価情報を読み込み開始
Else ' コードが数値でない時(米国株式の時)

   ↓ 上記コードを下のように変更

 If Len(code) <> 8 Then 'コードが8桁以外の時(日本株式の時)
 If IsNumeric(Left(code, 4)) Then ' コード左4桁が数値の時
 If IsNumeric(code) Then ' コードが数値のみ
 url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code & ".T"
' ヤフーファイナンスの株価情報を読み込み開始
Else ' コードに市場コードが付与されている時
url = "http://stocks.finance.yahoo.co.jp/stocks/detail/?code=" & code
   ' ヤフーファイナンスの株価情報を読み込み開始
End If
Else ' コードが数値でない時(米国株式の時)


変更箇所2(株価取得のところを変更)
 If Len(code) <> 8 Then 'コードが8桁以外の時(株式の時)
 If IsNumeric(code) Then ' コードが数値の時

   ↓ 上記コードを下のように変更

If Len(code) <> 8 Then 'コードが8桁以外の時(株式の時)
 If IsNumeric(Left(code, 4)) Then ' コード左4桁が数値の時


以上の変更を行った上で、エクセル表のコードのところに、
【1413】桧家ホールディングスなら 1413.N
【3384】アークコアなら 3384.N
というように銘柄コードに.Nを追加して入力して下さい。

名古屋証券取引所(セントレックス含む)は.N
札幌証券取引所(アンビシャス含む)は.S
福岡証券取引所(Q-Board含む)は.F
を銘柄コード末尾に追加すれば株価取得できます。

いずれ記事中のVBAコードを変更するようにしますが、
取り急ぎ、変更点のみ紹介しました。


2016/07/03 (Sun) 20:57 | URL | 編集 | 返信
ゆゆゆ #-

さっそくのご返信ありがとうございます。

HTMLソースのほうにばかり気を取られていて、URLの「T」はすっかり見逃してしまっていました。

お示しいただいたコードに変更したところうまくいきました。
これで保有銘柄の管理が効率よく行えます、ありがとうございました。

2016/07/04 (Mon) 22:33 | URL | 編集 | 返信
mochi #-

お役にたてたようでなによりです。 ^^

こんにちは。ゆゆゆさん。

> HTMLソースのほうにばかり気を取られていて、URLの「T」はすっかり見逃してしまっていました。
> お示しいただいたコードに変更したところうまくいきました。
> これで保有銘柄の管理が効率よく行えます、ありがとうございました。

 早速試していただけたみたいで、動作報告ありがとうございます。
 一応動作確認したコードではありますが、
 うまく伝えられたか心配だったので、安心しました。

 地方の取引所については、今まで特にそのような要望がなかったので、
 ほったらかしにしたままでいました。 ^^;

 せっかくなので、近いうちに記事中のVBAコードと
 ダウンロードファイルを更新しておこうと思います。
 
 コメントありがとうございました。


2016/07/05 (Tue) 21:34 | URL | 編集 | 返信
Kiki #8wAYtEcg

ドル円レートを知るには

こんなすごいことができるとはびっくりしてます。
初歩的な質問で恐縮ですが、ドル円レートを出すには、どうしたらいいのでしょうか?

2016/12/12 (Mon) 21:34 | URL | 編集 | 返信
mochi #-

Re: ドル円レートを知るには

Excelシートに取り込みたいってことですよね?

VBAではないですが、このサイトにWebクエリを使って、
為替レートを取得する方法が紹介されていますので、
参考にされてみてはいかがでしょうか?
http://www.hello-pc.net/howto-excel/exc_rate/


2016/12/12 (Mon) 23:37 | URL | 編集 | 返信

Post Comment

非公開コメント

Trackback


この記事にトラックバックする(FC2ブログユーザー)
この記事へのトラックバック
  •  VBAで「PER取得マクロ」を作成しました
  • 株価指標のPERをYahoo!ファイナンスから取得し、Excelシートにコピーするマクロを作成しました。ソースを公開します。 1. 動作と仕組み 1-1. 動作 マクロを実行すれば、下図のように、エクセルシートにPER等の情報が一覧で取得できます。 ※クリックで拡大します。 1-2. 仕組み Yahoo!ファイナンスのページから、データを取得します。 順...
  • 2013.10.24 (Thu) 01:12 | Kapok の資産運用

Latest posts