株主優待の権利日と概要を表示するスプレッドシートを作りました。
それぞれの関数について説明します。
参考サイト
こちらのサイトの説明通り関数を書くだけです。
とはいえ参考サイトや関数の書き方（結合の仕方）等の書き方を変更した点があるのでそちらについて記載しておきます。
使っている関数
各列に記載した関数の説明です。読んでいただければ、自分でカスタマイズする際に役に立ちます！
スプレッドシートでは、下の３種類の関数を組み合わせて使用しています。
- CONCATENATE:文字列を結合する関数▶︎CONCATENATE(文字列1, [文字列2, …])
- ImportXML:HTMLなどの構造化データからデータをインポートする関数▶︎IMPORTXML(URL, XPath クエリ)
- LEFT:指定した文字列の先頭から部分文字列を返す関数▶︎LEFT(文字列, [文字数])
簡単なものから順番に説明していきますね！
CONCATENATE関数で文字を結合→HPアドレス取得
スプレッドシートG列の「優待内容詳細」では株式投資メモさんのHPを表示しています。
例えばマクドナルドの優待ページは
https://kabuoji3.com/stockholder/2702/
です。2702の部分は銘柄コードなのでここにセルの値を代入できればいいわけです。
そこで、CONCATENATE関数を使い、下のように記載します↓
=CONCATENATE("https://kabuoji3.com/stockholder/",A2,"/")
CONCATENATE関数は=CONCATENATE(文字列1, [文字列2, …])と記入すると文字が結合される関数です
=CONCATENATE(“https://kabuoji3.com/stockholder/“,A2（銘柄コードを記載するセル番号）,”/“)と書いておき、
A2セルには「2702」(日本マクドナルドホールディングス)を書いておくと、
セルには
https://kabuoji3.com/stockholder/２７０２/
と表示されます。
IMPORTXMLでHP上の特定場所のデータを取得→銘柄名、株価、優待内容取得
B列銘柄名に「日本マクドナルドホールディングス」と表示したい時の関数です。
IMPORTXML(URL, XPath クエリ)で
=ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/?scode=",A2),"//*[@id='CONTENTS_MAIN']/div[4]/div/div/h1/a")と入力しています。
まず、URLは先ほど説明したCONCATENATE関数で日経新聞のURLを取得しています。
https://www.nikkei.com/nkd/company/yutai/?scode=2702
このページから「銘柄名」の部分だけ抜き出してB2セルに表示させたいので、
銘柄名のXPathを取得します。
XPath中の”(ダブルクォーテーション）を'(シングルクォーテーション）に変換する
//*[@id="CONTENTS_MAIN"]/div[4]/div/div/h1/aコピーしたXPathの“を‘に変換します。
//*[@id='CONTENTS_MAIN']/div[4]/div/div/h1/aこれをIMPORTXML関数に組み込みます。
=ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/?scode=",A2),"//*[@id='CONTENTS_MAIN']/div[4]/div/div/h1/a")これで銘柄名が取得できるようになりました！
権利確定日も同様にXPathを取得し、記入します。
=ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/yutai/?scode=",A2,),"//*[@id='CONTENTS_MAIN']/div[7]/div[2]/div[2]/div/table/tbody/tr[2]/td")=CONCATENATE関数で２行以上の優待内容を１行にまとめる
優待内容を取得する関数は
=CONCATENATE(ImportXML(CONCATENATE("https://kabuoji3.com/stockholder/",A2,"/"),"//*[@id='base_box']/div/div[5]/h3"))としています。
上で紹介した
=ImportXML(CONCATENATE("https://kabuoji3.com/stockholder/",A19,"/"),"//*[@id='base_box']/div/div[5]/h3")をCONCATENATE関数で囲っています。これは、優待内容が２行に以上になることがあるからです。
例えばこの銘柄では、優待が２種類あります。
これをそのまま取得すると、２行にわたって表示されてしまいます。
=CONCATENATEで全体を囲うと、１行で表示されます。
LEFT関数→最新株価の数値のみを取得
最新株価をそのまま取得すると隣の列に「円」が入ってしまいます。
表として扱いづらいので、上のCONCATENATEでセル内に表示させます。
=CONCATENATE(ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/yutai/?scode=",A2,),"//*[@id='CONTENTS_MAIN']/div[5]/dl[1]/dd"))
「円」が入っていると数値として扱えないので、「円」を取り除きます。
=LEFT(CONCATENATE(ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/yutai/?scode=",A2,),"//*[@id='CONTENTS_MAIN']/div[4]/dl[1]/dd")),LEN(CONCATENATE(ImportXML(CONCATENATE("https://www.nikkei.com/nkd/company/yutai/?scode=",A2,),"//*[@id='CONTENTS_MAIN']/div[5]/dl[1]/dd")))-1)
これで完成です！