Yahoo_Stock_crawler.py

# 
# Scrape Yahoo Stock , output to Excel
# 請先新建一個Excel,命名為: stock_price_data.xlsx, 並增加一個sheet, 名稱改為TW2330,TW3711
# 26行請改成自己Excel的路徑
from bs4 import BeautifulSoup
import requests
import time
import os
import openpyxl

def getStockPriceById(stockID, workbook):
    url = 'https://tw.stock.yahoo.com/q/q?s={}'.format(stockID)
    doc = requests.get(url)
    html = BeautifulSoup(doc.text, 'html.parser')
    table = html.findAll(text='個股資料')[0].parent.parent.parent
    dataRow = table.select('tr')[1].select('td')
    date = time.strftime("%Y/%m/%d")
    sheetName = "TW{}".format(stockID) # 找出試算表名稱
    sheet = workbook[sheetName] #sheet = workbook.get_sheet_by_name(sheetName)#deprecated function : get_sheet_names
    numOfRows = len(sheet['A']) # 計算該工作表(sheet)中 A Column有多少筆資料
    sheet.cell(row=numOfRows+1, column=1).value = date
    for i in range(2,12): # 2 開始 11 結束
        sheet.cell(row=numOfRows+1, column=i).value = dataRow[i-1].text
# 主程式從這裡開始
stocks = [2330,3711] #股票代碼 , 2311日月光 找不到 因為2311日月光 與 矽品2325 合併為 日月光投資控股3711
os.chdir(r"D:\YZU\大三下\MIS-2\Excel")#請自行更改成自己Excel的路徑
workbook = openpyxl.load_workbook('stock_price_data.xlsx')
# 用迴圈把每一個 stockID 都跑一次
for stockID in stocks:
    getStockPriceById(stockID, workbook)
workbook.save("stock_price_data.xlsx")