GoogleスプレッドシートでS&P500のリストを取得する方法の色々

Google スプレッドシートでS&P500のリストを取得する方法を説明します。

S&P500の構成銘柄のデータのソース

S&P500の構成銘柄のデータとして以下を利用します。

List of S&P 500 companies - Wikipedia

en.wikipedia.org

もっともシンプルに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スプレッドシートを活用したデータ取得は以下のページでまとめています。

情報収集効率化

わたしがアメリカ株投資をするときに様々な情報をウェブサイトから収集しています。見たい情報が一か所のウェブサイトに集まっている訳ではありません。そこで、わたしは情報収集を効率化しています。情報収集効率化をどのようにしているかまとめていきたいと...

nonbiri-reinvest.net