週に1回および毎月末に1回、資産状況を確認するとともに、保有している株の騰落を整理しています。
たいした銘柄数ではないし、騰落を確認するのは楽しいので、基本的には全然苦ではありません。
でも、忙しくしていて確認タイミングを逸してしまうと、一銘柄ずつ時系列の情報から過去の株価を拾わなければいけなくなって、途端に苦痛になります。
この苦痛を緩和すべく（あと覚えたてのWEBクエリをいぢり倒してみたくて）、複数の任意の東証上場銘柄の株価（過去～現在）を取得するツールを作りましたよ。
なお、本ツールでは、https://kabuoji3.com/ さんのデータを使用させていただいておりますが、2020年1月26日現在、、https://kabuoji3.com/ さんが更新を停止されているようです。いつの間にか更新が再開されて（！！）、停止していた期間のぶんの株価も記録されているようです😆😆😆。
…2021年7月現在、再び停止されているようですね。2021年3月頃から株価が記録されていない銘柄もあるようです。
国内株の株価を古い時期まで遡って取得したい場合は本記事のツールをお使いください。
また、1年ほど前までの期間に限られますが、国内株・米国株の両方に対応したツールも別に用意してありますので、よろしければこちらもお使いください。
【EXCELで株価取得】国内株・米国株の株価を銘柄・日付指定で取得するツール
国内株（東証上場銘柄）の株価取得ツール for EXCEL
まずはダウンロードしてください。マクロ要らずなのでZIPにせずアップできるの嬉しい。
国内株の株価取得ツールをダウンロードする（2019年5月21日版）
ファイルを開くと、「セキュリティの警告 外部データ接続が無効になっています。」というメッセージが出ます。有効にしないことには株価をネットから持ってこれないので、「コンテンツの有効化」を選んでください。
起動すると東証上場銘柄の一覧を更新
ファイルを開くと「銘柄一覧」シートの情報を更新するように設定してあります。
JPXのサイトに置いてある「東証上場銘柄一覧（XXXX年XX月末）」のファイルから更新しています。
このシートは何もいじる必要はありません。
JPXのサイトでこのファイルのURLが変わってしまうようだと、メンテナンスが必要になります。2019年7月時点では月が替わってもURLには変更は無いようです。
株価を取得したい銘柄を設定する
株価を取得したい銘柄を編集します。「対象銘柄」シートを表示してください。
対象銘柄のテーブルがあるので、コードを入力して編集してください。サンプルでは、わたくし千鳥足が保有する国内株の一部を設定してあります。
サンプルで入れてある情報は上書きしていただいて構いません。
行を削除したいときは、対象の情報を選択して、右クリックからの「削除」⇒「テーブルの行」です。
行を追加したいときは、最下行にコードを入力してください。
株価を取得したい日付を設定する
次に株価を取得したい日付を編集します。同時に複数の日付の株価が取得できます。「対象日付」シートを表示してください。
対象日付のテーブルがあるので、日付を入力して編集してください。サンプルでは、2018年12月末以降、4月までの月末、および5月20日を設定してあります。
上書きの仕方、削除・追加の仕方は「対象銘柄」と一緒です。
ちなみに、土日祝日等市場が開いていない日を指定した場合、指定した日の前営業日を探しにいくような気の利いたことはしていないので、そのままズバリ開場日を指定してください。
準備OK、いざ株価を取得する
以上で準備はOKです。「対象銘柄・日付の株価」シートを表示してください。
株価のテーブルがありますので、テーブルの上のどこでも良いので右クリックをして「更新」を選択します。
初めて実行したときには、以下のような警告が出るかも知れません。
送信している情報は、対象銘柄のコードと対象日付の年のみです。それを送信せずに株価を取得することはできないので、「このファイルのプライバシーレベルのチェックを無視します。」を選択して「保存」をクリックしてください。
設定した対象銘柄・対象日付の数によって、ちょっと時間がかかります。しばらく待つと、対象銘柄×対象日付の数のぶんだけ、行ができているはずです。
ちなみに株価の情報は、https://kabuoji3.com/ から取得させていただいています。
基本的には、このツールの本体はここまでです。あとはこの情報をみなさんの都合に合わせてうまく使ってください。このテーブルを直接参照して株価にアクセスしやすいように、いちばん左の列に「ID」という列名で銘柄のコード＋日付の文字列を配置しているので、VLOOKUP で容易に引けますね。
サンプルまでに縦軸銘柄、横軸日付の整理表
サンプルには「加工例」シートを付けています。こちらもテーブルの上で右クリックをして「更新」してください。
A列～C列は「対象銘柄」シートの情報をそのまま持ってきています。
D列以降は、サンプルとして株価を取得したり、前月末比や前年末比を計算する数式を入れてあります。
いちおう数式の意味を説明しておきますね。
＜株価の取得＞
=VLOOKUP($A4&”_”&TEXT(D$3,”yyyy/mm/dd”),対象銘柄_日付の株価,6,FALSE)
- $A4 でA列の銘柄のコードを参照しています。
- D$3 で3行目（表頭）の日付を参照しています。TEXT関数で日付を「2019/05/21」形式の文字列に変換しています。
- 上記2つの情報を「_」（アンダースコア）で結合した文字列で「対象銘柄・日付の株価」シートの情報を VLOOKUP で検索して、6列目の「終値調整」を取得すると云う数式です。
- ちなみに「調整終値」とは株式分割があった場合に分割後の株価に換算した株価になります。
＜騰落率の算出＞
=[@[2019/05/20]]/[@[2019/04/26]]-1
- 5月20日の列の株価を4月26日の列の株価で割り、1を引いています。
- サンプルではテーブルを使用しているので見慣れない数式になりますが、要するに「=I4/H4-1」と何ら変わりません。
気を付けて欲しいこと
対象銘柄・対象日付を大量に指定すると、情報の取得先である https://kabuoji3.com/ 様に負荷とご迷惑をかけてしまいます。手元に時系列で株価を保管しておきたい場合には、過去の情報は値複写する等して保管し、対象日付を絞って実行してください。
国内株（東証上場銘柄）の株価取得ツールの仕組み
仕組みとしては、EXCELに標準で付いている「クエリ」、中でも「WEBクエリ」を使用して情報を収集しています。
VBAのコーディング無しでここまでお手軽に情報収集ができるのは中々凄い。EXCELって優秀。なかんずく今回はEXCELに付帯する Power Query の機能をガシガシいぢってみたわけですが、これ奥が深くて楽しかったです。
今回のツールが情報を作り上げるまでの流れを図にしてみました。WEBへのアクセスはもちろん、クエリの関数化やクエリ同士の結合までほとんどノー・コーディングでいけるんです。
仕組み的には、対象銘柄と対象日付をクロス結合してできた表の各行から、銘柄コード・日付をパラメータにして https://kabuoji3.com/ のサイトに情報を取りに行きます。
いろいろと応用がききそうなので、後日、作り方を手順で整理して記事にしようと思います。
＜関連情報＞
国内株・米国株の両方の株価取得に対応したツールです。
【EXCELで株価取得】国内株・米国株の株価を銘柄・日付指定で取得するツール
はじめてのWEBクエリ。
【ETF構成確認ツール】複数の米国ETFを合わせたときのセクター比率を確認するのだ
日本株以外であれば、Office365 でやるともっと簡単です。
【Office 365】Excel に株価を取得する機能がリリースされてた
EXCELでヒートマップを作る子。
【EXCELでヒートマップ】リスク資産の増減率をヒートマップで可視化するツール
マネーフォワードの「資産内訳」をぺろんと貼り付けて分類・整形する子
【EXCELツール】マネーフォワードMEの「資産内訳」を分類・整形して使いやすくする
日本株の連続増配年数を確認する子
【EXCELで配当実績取得】東証上場全銘柄の配当実績・連続増配年数を確認する！
米国株の配当記録を確認する子
