之前在如何計算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 :)
