わたくし千鳥足は、毎週末・毎月末にリスク資産の増減を確認しています。
リスク資産の増減を確認する際、保有している株やETFの株価も記録して、その騰落状況をウォッチできるようにしています。
この作業、銘柄数が多くなると株価を記録するのがチョット手間。そして諸般の事情で株価の記録を漏らしてしまい、「先月末の株価が欲しい…!!」と云う具合になると、各銘柄の時系列データを開いて該当日付の終値を探しそれを転くぁwせdrftgyふじこlp!!! …途端に面倒になって白目を剥きます。
そんなチョットした手間と面倒をやわらげるため、銘柄と日付を指定すると株価を取得してくれるEXCELのツールを作成しました。もしよろしければお試しください。
国内株・米国株 株価取得ツール for EXCEL の特徴
ツールの特徴を簡単に整理しておきますね。
- 任意の日付を指定して過去の株価も取得できる。
（標準で30日ほど前迄。設定変更で1年ほど前迄） - 毎週・毎月など定期＆長期に保有株の株価を記録する用途に向いている。
- 国内株・米国株の両方に対応している。
- EXCELの操作が分かれば誰でも使える。
騰落具合を確認しているので、
自分自身が使い易いツールにしてみました。
国内株・米国株 株価取得ツール for EXCEL の使い方
まずはツールをダウンロードしてください。若干VBAマクロを使用しているため ZIP 形式にしてアップしています。
国内株・米国株 株価取得ツール v1.10（2021年7月22日版）
修正履歴
- 2021年07月22日 以下3点修正し、バージョンを1.10としました。 New!
- 米国株の株価は、Yahoo Finance US の時系列データのダウンロード機能から取得するよう変更しました。
- 米国株の株価は、データ取得日数を指定して過去データを取得できるようにしました。（以前は100日程度）
- 国内株のデータは、「株探」さんの時系列データ1～10ページより取得できるようにしました。（以前は1～4ページ）
- 2021年07月20日 Yahoo Finance US の変更により米国株の株価取得が動作しなくなっていたので、差し替えました。（2020年12月26日版に戻っています。）
- 2021年06月12日 Yahoo Finance US の変更により米国株の株価取得が動作しなくなっていたので、差し替えました。
- 2020年12月26日 以下2点、修正しました。
- 対象銘柄が上場廃止等の理由で取得できない場合、エラーで終了していましたが、株価0として処理を続行するよう修正しました。
- 米国株で直前の日付で配当金が出ている場合に株価が取得できなくなる不備を修正しました。
- 2020年10月02日 「株探の過去データ取得頁数」に3以上の頁数を指定した場合の国内株の株価取得に不備がありましたので、修正版に差し替えました。
- 2020年07月26日 株探の変更により国内株の株価取得が動作しなくなっていたので、修正版に差し替えました。
- 2020年02月01日 国内株の直近開場日の株価取得が正しくない問題がありましたので、修正版に差し替えました。
- 2020年01月26日 証券番号9000番台の国内株の株価取得が正しくない問題がありましたので、修正版に差し替えました。
ダウンロードが完了したらZIPを展開して、EXCELで起動してください。
なお、本ツールの動作確認は Office365 付帯の EXCEL で行っています。古いEXCELではWEBクエリやマクロが正常に動作しないかも知れません。ごめんなさい。
ツールを起動する（EXCELで開く）
初めてEXCELで起動するとき、セキュリティの警告が表示されます。VBAマクロやWEBクエリを含んでいるためです。
「編集を有効にする」、および「コンテンツの有効化」を選択して進めてください。
また、ツールを開いた際、あるいは株価取得を実行した際、以下のような警告が表示される場合があります。
Webコンテンツへのアクセスに関する警告
WEBクエリを使用して、国内株は「東証」および「株探」から、米国株は「Yahoo Finance US」から情報を取得しています。
上記の画面が表示されたら「接続」を選択してください。
プライバシーレベルに関する警告
WEBクエリを使用して株価を取得する際に、以下のような警告が表示される場合があります。
株価を取得するとき、指定された銘柄のコードや取得対象の日付を取得先のサイトに送信しています。
送信しなければ必要な株価が取得できませんので、下図のように「このファイルのプライバシーレベルのチェックを無視します。」を選択して「保存」ボタンをクリックしてください。
株価を取得したい銘柄を設定する
EXCELで編集可能な状態になったら、まずは株価を取得したい銘柄を設定しましょう。試しにもとから設定されている銘柄に加えて [8411]みずほFG、[V]ビザを追加してみましょう。
銘柄を追加するのに特別な手順はありません。EXCELでの通常の編集のとおり、行を選んで右クリックから「挿入」を選択してください。
当たり前ですけど、新たに行が追加されました。
EXCELのテーブルの操作に慣れない方向けに。行の挿入は以下手順でもできます。
- 表上の任意の位置で右クリックして「挿入」⇒「テーブルの行(上)」／「テーブルの行(下)」。
- 最終行の最右端の列で「TAB」を押すと最終行の下に一行追加。
「ticker」の列に日本株の場合は証券番号、米国株の場合はティッカーを入力してください。下の画像は [8411]みずほFG、[V]ビザ をそれぞれ追加した状態です。
もちろん、新たに行を追加しなくても、もともとある行の「ticker」列を書き換えることもアリです。また、不要な行は削除したほうが株価取得速度が速くなります。
株価を取得したい日付を設定する
次に株価を取得したい日付を設定します。試しに、いちばん最近の金曜日の日付を追加してみましょう。
まずは任意の位置に列を挿入します。列を選び右クリックからの「挿入」ですね。
当たり前ですけど、列が1つ追加されました。EXCELの機能で自動で日付が設定されますが、このあと上書きします。
この記事を作成している時点では、直近の金曜日の日付は2020年1月24日なので、そのとおり書き換えました。
なお、基本的には指定した日付の株価を取得しますが、指定した日が閉場日などの理由でデータ取得先サイトに株価が記録されていない場合、初期設定では、指定した日以前の最終開場日の株価を取得するようにしてあります。この設定は変更できます。
準備OK!! いざ株価を取得する
銘柄を指定し、株価を取得したい日付も指定しました。いざ、株価を取得しましょう！
株価の取得は、取得したい日付の列を選択して、「選択列を更新」ボタンをクリックします。
株価を取得したい日付の列を選択する
下図のように株価を取得したい日付の列を選択します。
上の図では1列だけ選択していますが、複数列（複数日付）を選択して実行することも可能です。
また、上の図では列全体を選択していますが、セルの選択範囲に対象列が含まれていれば更新対象になります。
追加した [8411]みずほFG、[V]ビザ の前年末の株価も同時に取得したい場合には、以下のように列を選択します。
上記例のように2019年12月31日も選択した場合、この日付がすでに「過去すぎる」場合には、株価は取得できません。取得できなかった日付の株価は「-」で表されます。
すでに記載しているとおり、過去に遡れる期間は有限だからです。米国株は指定日数分、国内株は初期設定では30日程度前まで、設定を変えると1年ほど前まで過去に遡ることができます（ただし処理に時間がかかるようになります）。
「過去すぎる」日付の株価はこのツールでは取得できませんので、本ツールの該当列に手動で株価を入力してください。ごめんなさい。
「選択列を更新」ボタンをクリック
左上にある「選択列を更新」ボタンをクリックすると株価の取得処理が始まります。
処理時間は銘柄数によってかなり変わります。またサイトの混み具合の影響も受けるようです。初期状態の銘柄数に [8411]みずほFG、[V]ビザ の2銘柄を加えた状態で、我が家の環境では速いときで30秒、遅いときにはおよそ4分くらい時間がかかりました。いずれにせよまあまあ時間がかかるので、珈琲を飲んでゆったり構えて待つか、何かの用事を片付けている間に実行しておくとか、そういう使い方になりそうです。
時間がかかりすぎてちゃんと動いているのか不安…という方は、EXCELの「データ」-「クエリと接続」をクリックして、クエリの実行状況が分かる状態で実行すると安心できると思います。
また、クエリが動いてなさそうだな？というときは「ESC」キーを押して実行をいったん停止し、再度実行してみてください。いつ停止しても壊れるようなことはありませんから、安心してください。
処理が完了すると以下のようにメッセージが表示されます。赤枠のところ、株価が埋まっているのが分かりますね？
本ツールは特定の株（保有株）について、毎日・毎週・毎月など定期＆長期に株価をコツコツと記録していく用途を想定しています。少しずつ列の日付を追加していって、横にながーい表になっていく、そんな使い方です。
指定期間のパフォーマンスを確認する
上部にある「比較」のFROMとTOの日付（下図の赤枠）を指定すると、その期間の各銘柄のパフォーマンスを算出します（下図の青枠）。
2020年の1月前半は、米国のハイテク銘柄が力強く伸び、全体に堅調な相場でありました。
あとで懐かしいイベントとして思い出すことになるかも知れませんが、そんな堅調な相場に武漢発の新型コロナウイルスが冷や水を浴びせている今日この頃であります。
国内株・米国株 株価取得ツール for EXCELの詳細設定
EXCELファイルの「設定」シートに4点ほどツールの挙動を変える設定項目を用意しています。
同シートに各設定項目の説明を記載していますので、詳細はそちらをご確認ください。ここでは簡単に説明しますね。
株価を値で記録するか数式で記録するか
初期設定は「TRUE」です。基本的には「TRUE」のままお使いください。
「FALSE」を設定すると、取得した株価データをVLOOKUPで引きにいく数式を残したまま処理が終了するように変わります。残った数式をコピーして列を増殖させたい場合に便利です。
記録が無い場合に直近の開場日から取得するか
初期設定は「TRUE」です。
「TRUE」にしておくと、閉場日の場合にも直近の開場日の株価を取得します。市場の開閉を気にせず「年末」「月末」の日付をプロットできます。
「FALSE」を設定すると、閉場日には単に「-」が記録されます。
株探の株価データをどこまで遡って取得するか
国内株は「株探」から株価データを頂戴しています。
「株探」の時系列データは30件ずつページに分けて整理されていて、その何ページ目まで遡って取得しにいきますか？と云う設定値です。
初期設定は処理速度優先の「1」です。
「株探」では10ページ目まで遡れます。このツールでも1～10ページ目までを設定できるようにしました。おおよそ1年ほど前まで遡ることができます。
ちなみに、この設定をおおきな数字にすればするほど処理時間が長くなることに注意してください。WEBクエリでお手軽に作成したツールなので、融通が利かずすみません。
Yahoo Finance US の株価データを何日前まで遡って取得するか
米国株は「Yahoo Finance US」から株価データを取得しています。
現在から何日分遡って株価を取得するかを指定できます。デフォルトは「365」日、つまり1年前まで遡って取得する設定としています。
国内株・米国株 株価取得ツール for EXCEL の仕組み
WEBクエリとVBAマクロで動いてます
このツールは、WEBクエリとほんの少しのVBAマクロで動作しています。株価取得のメインはWEBクエリです。
VBAでガリガリに作り込めばもっと便利なものに出来るのだけれども、そこまではしない、WEBクエリの積み木で遊ぶような作りになってます。
どちらも処理の中身を見ることができるので、興味のある方は覗いてみてください。
- WEBクエリ等のPowerQueryの処理は「データ」リボン⇒「クエリと接続」でクエリと接続の一覧を表示して、そこから右クリックの「編集」です。
- VBAマクロは「開発」リボン⇒「Visual Basic」を選択。
VBAマクロは「マクロの記録」を使って記録したものをそのまま残していますし、PowerQueryはまるで素人です。汚いですがご容赦ください。
WEBクエリの簡単な説明
クエリがどんな処理をしているか記載しておきますね。
| クエリ | 処理 |
|---|---|
| 株探取得 | 日本株の証券番号を引数に受け取り、株探の時系列データから1～10ページ分の株価を取得するWEBクエリです。コードを見ていただくと分かりますが、ページ分の処理をとても可愛らしく処理していますｗ |
| YFUS取得 | 米国株のティッカーを引数に受け取り、Yahoo Finance US の時系列データから株価を取得するWEBクエリです。HTMLのページはたびたび仕様が変わるのと、2021年7月22日現在、不安定な動きを確認したため、CSVダウンロードのインタフェースを使用して取得するように変更しました。 |
| 国内株ticker | 株価シートに指定した銘柄から国内株の証券番号を抽出するクエリです。数字であるかどうかで判定しています。 |
| 米国株ticker | 株価シートに指定した銘柄から米国株のティッカーを抽出するクエリです。 |
| 国内株銘柄名 | 東証のサイトから東証上場の銘柄の証券番号と銘柄名の一覧を取得し、国内株銘柄名シートのデータを更新するWEBクエリです。 |
| 銘柄名一覧 | 指数系・国内株銘柄名・米国株銘柄名の各シートをがっちゃんこして1つのテーブルにするクエリです。 |
| 国内株株価取得 | 国内株tickerクエリをベースに、行ごとに株探取得クエリを呼び出し、全証券番号の株価を取得するクエリです。 |
| 米国株株価取得 | 米国株tickerクエリをベースに、行ごとにYFUS取得クエリを呼び出し、全ティッカーの株価を取得するクエリです。 |
| 株価取得 | 国内株株価取得クエリの結果と米国株株価取得クエリの結果をがっちゃんこして戻すクエリです。ほんとうはここで株価データを並び替えるべきなのですが、VLOOKUPの「検索方法=TRUE」が求める並びと、PowerQueryが返す「昇順」が異なるようなので諦めました。VBAマクロの側で並び替えています。 |
VBAマクロの簡単な説明
VBAマクロはたいしたことはしていません。
- 日付が指定された列が選択されていることのチェック
- クエリの順次実行、VLOOKUP向けの並び替え
- 取得した株価をVLOOKUPする数式を埋め込む
- 埋め込んだ数式をコピーして値複写で値にする
こんな程度のことです。コード汚くてまじごめんなさい。
関連記事
本ツールの前身と言える国内株限の株価取得ツールです。データ取得先に「株式投資メモ」を使っているのですが、2019年12月20日以降同サイトのデータが更新されておらず、現時点では古い株価しか取得できません。
【EXCELで株価取得】マクロ要らず！銘柄・日付指定で株価を取得するツール
はじめてのWEBクエリ。
【ETF構成確認ツール】複数の米国ETFを合わせたときのセクター比率を確認するのだ
日本株以外であれば、Office365 でやるともっと簡単です。
【Office 365】Excel に株価を取得する機能がリリースされてた
EXCELでヒートマップを作る子。
【EXCELでヒートマップ】リスク資産の増減率をヒートマップで可視化するツール
マネーフォワードの「資産内訳」をぺろんと貼り付けて分類・整形する子
【EXCELツール】マネーフォワードMEの「資産内訳」を分類・整形して使いやすくする
日本株の連続増配年数を確認する子
【EXCELで配当実績取得】東証上場全銘柄の配当実績・連続増配年数を確認する！
米国株の配当記録を確認する子
