如何計算ETF的PE ratio — SOXX?

之前在如何計算ETF的PE ratio — 00878?中討論到如何使用計算00878的PE以進行對於此ETF的估值，我們發現關鍵在於如何有效撈取一檔ETF的成分和持有股數和其市值(或發行股數)，我們會持續這個系列的原因是每個發行商給的資料略有不同，有些可以直接存成CSV，有些必須下載成Excel在選擇資料範圍。此篇我們繼續探討如何撈取計算iShares Semiconductor ETF-SOXX的本益比。關於SOXX的介紹，可以參考此連結。

You can check this for the English version!

以下為建議步驟:

步驟1: 安裝相關套件。

!pip install openpyxl

!pip install jpype

!pip install yahoo_fin

步驟2: 根據此官方連結獲取QQQ的基本資訊，包含持倉、每檔持有股數、市值等。與之前00878和QQQ不同的是，iShare給的檔案是xls，因此必須先進行轉檔才能把檔案存取。

import urllib
from openpyxl import load_workbook
import pandas as pd
import numpy as np

import  jpype
import  asposecells
jpype.startJVM()
from asposecells.api import Workbook

dls = "https://www.ishares.com/us/products/239705/ishares-phlx-semiconductor-etf/1521942788811.ajax?fileType=xls&fileName=iShares-Semiconductor-ETF_fund&dataType=fund"
urllib.request.urlretrieve(dls, "data.xls")


workbook = Workbook("data.xls");
workbook.save("data.xlsx");
jpype.shutdownJVM()

步驟3:針對目標資料做選取，並存成DataFrame。

wb = load_workbook(filename='data.xlsx',
                   read_only=True)
ws = wb['Holdings']

data_rows = []
for row in ws['A9':'N43']:
    data_cols = []
    for cell in row:
        data_cols.append(cell.value)
    data_rows.append(data_cols)

df_SOXX = pd.DataFrame(data_rows)

步驟4: 定義一個函式，輸入股票代碼後回傳預估EPS。

def get_info_yahooquery(tickers):
    try:
      df_eps = get_analysts_info(str(tickers)).get('Earnings Estimate')
      eps_forward = df_eps.iloc[1,3]

    except:
        pass

    return eps_forward

步驟5:在df_SOXX中，一列一列的將股票代碼丟入步驟4定義的函式，並將得到的EPS存入新欄位。

from yahoo_fin.stock_info import *
i = 0
for row in df_SOXX.to_records(index=False):
  try:
    ticker = str(row[0]).strip()
    print(ticker)
    eps_forward = get_info_yahooquery(ticker)
    df_SOXX.loc[i, 'eps_forward'] = eps_forward
  except:
        pass
  i = i + 1

步驟6:計算總盈餘和總市值，並利用此數字算出本益比。

df_SOXX['Earning_Forward'] = df_SOXX.iloc[:, 7] * df_SOXX['eps_forward'].astype(float)
filt = df_SOXX['eps_forward'].notna()
Earning_Forward_SOXX = df_SOXX['Earning_Forward'].loc[filt,].sum()
MarketValues_SOXX =  df_SOXX.iloc[:, 6].loc[filt,].sum()
PE_Forward_SOXX = MarketValues_SOXX  / Earning_Forward_SOXX

Thank you and more to come! We will talk about this topic for more ETFs, approaches, matrices, etc. Enjoy it :)

If you want to support Informula, you can buy us a coffee here :)

𝗕𝘂𝘆 𝗺𝗲 𝗮 𝗰𝗼𝗳𝗳𝗲𝗲