本文我們將會探討如何使用Python於Markets Insider撈取債券資料,存成資料表並錄入SQLite資料庫。
You can check this for the English version!
首先,我們要先理解網址的資料結構,本文會以公司短債為例。
https://markets.businessinsider.com/bonds/finder
?p=1 (頁數)
&borrower= (發行者/公司)
&maturity= (shortterm: 0-3 yers / midterm: 3-10 years/ longterm: > 10 years)
&yield= (0: < 5% / 5:5%-10% / 10: 10%-20%)
&bondtype=(6%2c7%2c8%2c19: Corporate/ 2%2C3%2C4%2C16: Government, etc)
&coupon= (0: < 5% / 5:5%-10% / 10: >10%)
¤cy= (333: USD / 534: JPY/ 846: SGD, etc)
&rating= (Moody's rating)
&country= (18: USA/ 33: China/ 27/ Singapore, etc)
Step 0: 匯入模組
from datetime import date
from datetime import timedelta
import pandas as pd
import requests
import sqlite3
from google.colab import drive
drive.mount('/content/drive')
today = date.today()
con = sqlite3.connect('/content/drive/MyDrive/data/Stock.db')
Step 1: 定義迴圈。
for i in range(1, 100):
url = 'https://markets.businessinsider.com/bonds/finder?p='+ str(i) +'&borrower=&maturity=shortterm&yield=5&bondtype=6%2c7%2c8%2c19&coupon=0¤cy=333&rating=&country=18'
Step 2: 將url送出獲取每個頁面的資訊。
res = requests.get(url)
res.encoding = 'big5'
html_df = pd.read_html(res.text)
df = html_df [0]
df['As_Of'] = today
Step 3: 將獲取的資料表存入資料庫。
df.to_sql('Coporate_Bond_Markets_Insider', con, if_exists='append')
此資訊並不包含債券的ISIN/ Coupon Payment Date等資訊,必須點擊每個項目的連結進入細項網頁,因此我們下一篇會討論如何結合BeautifulSoup獲取超連結。
謝謝您,如果覺得此篇文章有幫助到您,歡迎透過此連結贊助我們。