Google スプレッドシートでS&P500のリストを取得する方法を説明します。
S&P500の構成銘柄のデータのソース
S&P500の構成銘柄のデータとして以下を利用します。
もっともシンプルにS&P500のリストを取得する方法
以下のようにセルに入力します。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1 OFFSET 1",0)
そうすると以下のように取得することができます。
こうしてみると不要な列もいっぱいあります。例えば、「SEC filings」、「Address of Headquarters」、「 Date first added[3][4]」 「CIK」などです。
それ以外の「Ticker symbol」、「Security SEC」(会社名)、「GICS Sector」(セクター名)、「GICS Sub Industry(産業名)」あたりはほしい情報かもしれません。
S&P500のティッカーシンボル、会社名、セクタ名、産業名を取得する方法(ヘッダーあり)
S&P500のティッカーシンボル、会社名、セクタ名、産業名のみを取得する場合は以下のようにセルに入力します。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1,Col2,Col4,Col5",0)
そうすると以下のように取得できます。
計算式のCol1,Col2,Col4,Col5がそれぞれティッカーシンボル、社名、セクター名、産業名に対応しています。ですので、不要な列があったら、削除すればいいだけです。
例えば産業名まではいらない場合は、「,Col5」を削除して以下のようにすれば、産業名のないリストを取得できます。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1,Col2,Col4",0)
S&P500のティッカーシンボル、会社名、セクタ名、産業名を取得する方法(自分の好きなヘッダーの名前で)
Google スプレッドシートでポートフォリオ等を管理する場合、ヘッダーは自分の好きなものを付けたいですよね。そんなときはセルに以下のように入力します。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1,Col2,Col4,Col5 OFFSET 1 label Col1 'ティッカーシンボル',Col2 '社名', Col4 'セクター名', Col5 '産業名'",0)
そうすると以下のように表示されます。
ヘッダーの赤枠の部分が日本語に変わっていると思います。当該日本語の箇所を好きなものに変えれば、お好みな名前に変更できます。
S&P500のティッカーシンボル、会社名、セクタ名、産業名を取得する方法(ヘッダーなし)
上記の方法だとヘッダーに「Ticker symbol」、「Security」、「GICS Sector」、「GICS Sub Industry」と決められたヘッダーが入ってしまいます。
ヘッダーの名前は自分で決めるから、ヘッダーなしにしたいということもあると思います。
その場合は以下のようにします。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1,Col2,Col4,Col5 OFFSET 1",0)
そうするとヘッダーなしで表示されます。
ヘッダーありのS&P500のリストのシンボルのみの取得
以下のようにセルに入力します。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1 OFFSET 1 label Col1 'Ticker Symbol'",0)
そうすると以下のようにシンボルのリストを取得することができます。
ヘッダーの「Ticker Symbol」の文字を変更したい場合は、上述した数式の’Tiker Symbol’を変更するだけです。
ヘッダーなしのS&P500のリストのシンボルのみの取得
ヘッダーが不要だという方はセルに以下のように入力するとシンボルのみを取得できます。
=QUERY(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_S%26P_500_companies","table"),"SELECT Col1 OFFSET 1 label Col1 'Ticker Symbol'",0)
以上、S&P500のリストの取得方法でした。
他にGoogleスプレッドシートを活用したデータ取得は以下のページでまとめています。
コメント