Using XLWings, BeautifulSoup, Python 3.9 and Microsoft Excel 365 VBA To Web Scrape Market Index Data and Historical Company Earnings

Jan 01, 2021, Update

1. Added section 7.3, Dow Jones Industrial 2021 Performance and Components
2. Added section 17, Russell 3000 Index

Dec 18, 2021, Update
Added section 16, High dividends stock.

In this book, we will use Python to retrieve market index data and historical company earnings We will use Microsoft Excel 365 to report the market index and company earnings. We will use a Python library called Xlwings so Excel 365 can communicate with Python.

The target audience for this book is for people who are interested in getting Dow Jones Industrial Average, S&P 500, Nasdaq and historical company earnings data from the internet. People who read this book will need a good working knowledge of Microsoft Excel, Excel VBA and Python. This book will use the case study approach. This book will not explain Microsoft Excel or Python; instead, readers will study the spreadsheet or Python examples. Like experience Microsoft Excel or Python users, the reader will google any Microsoft Excel or Python concept that they need more clarification.

1.2 Python 3.9 7
1.3 Visual Studio Code 7
3 Three Standard Web Technologies 11
3.1.1 HTML 11
3.1.1.1 Element Syntax 12
3.1.1.2 Attributes 12
3.1.1.3 General Purpose Attributes 12
3.1.1. id Attribute 12
3.1.1.3 class Attribute 13
3.1.1 HTML Tables 13
3.1 CSS 13
3.1 JavaScript 14
4 Google Chrome “Inspect” Tool 15
5 XPath 15
6 XLWings UDF and BeautifulSoup 16
6.1 XLwings Version 16
6.2 Excel XLwings Addin 17
6.3 Creating a New XlWings Microsoft Excel Workbook Project 18
6.3.1 Template Python Code 19
6.3.2 Template Microsoft Workbook 19
6.3.3 _xlwings.conf Worksheet 20
6.3.4 Import Function Errors 21
7 Dow Jones Industrial Average — Excel UDF 23
7.1 Yahoo! Finance 24
7.1.1 Ticker Price 24
7.1.1.1 Python Code 24
7.1.1.2 yahoo_CurrentPrice User Defined Function (UDF) 25
7.1.1.3 Web Browser Developer Tools 26
7.1.1.4 Ticker Description 27
7.1.1.5 Python Code 27
7.1.1.6 yahoo_CurrentPrice User Defined Function (UDF) 27
7.1.2 Ticker Time 28
7.1.2.1 BeautifulSoup HTML 28
7.1.2.2 Python Code 30
7.1.2.3 yahoo_timestamp User Defined Function (UDF) 30
7.1.3 Ticker Date 31
7.1.3.1 Python Code 31
7.1.3.2 yahoo_timestamp User Defined Function (UDF) 32
7.2 Dow Jones Industrial Average 30 Components 32
7.2.1 HTML Table 34
7.2.2 Python Code 35
7.2.3 Excel VBA 35
8 S&P 500 Index – Excel UDF 37
8.1 Yahoo! Finance 37
8.2 S&P 500 Components 39
8.2 Python Code 40
8.2 Microsoft Excel Workbook Output 42
8.3 S&P 500 Total Returns from 1941 to 2021 43
8.3 Python Code 44
8.3 Microsoft Excel Workbook Output 45
9 NASDAQ Index – Excel UDF 45
9.1 Yahoo! Finance 46
9.2 NASDAQ Components 47
9.2 Python Code 47
10 The Three Market Indexes 50
11 Historical Market Index Data 50
11.1 Dow Jones Industrial Average 50
11.2 S&P 500 51
11.3 NASDAQ 51
11.4 Yahoo_Fin Python Library 52
11.6 XlWings Excel Workbook 53
12 Debugging XLwings Python Code 54
13 Credit Rating Agencies and the SEC 55
14 Yahoo! Finance API 56
14.1 Dow Jones Industrial Average 56
14.1 Price 57
14.1 Time Stamp. 58
14.1 Epoch Time 58
14.2 S&P 500
14.2 Price
14.2 Time Stamp.
14.3 NASDAQ 62
14.3 Price 63
14.3 Time Stamp.
15 Stock Earnings