Data Retrivial

Data Retrivial#

Importing libraries#

In this section we are two essential libraries for financial data analysis

yfinance (yf):
- Decription: yfinance is a popular library used to fetch historical market data from Yahoo Finance. It allows users to download financial data such as stock prices, dividends, and splits for various companies and time periods.
- Usage: Throughout the notebook, we will use yf to retrieve stock data which will serve as the foundation for our financial analyses.
pandas (pd):
- Description: pandas is a widely-used Python library for data manipulation and analysis. It provides data structures such as DataFrames and Series that make it easy to handle and analyze structured data.
- Usage: As we retrieve financial data, we will use pd to store, manipulate, and analyze this data in tabular form, ensuring a streamlined and efficient data analysis process.

import yfinance as yf
import pandas as pd

Stocks List#

Understanding the stocks_info Dictionary Structure#

The stocks_info dictionary is structured to hold information about various stocks. Here’s a breakdown of its structure and the type of information it contains:

Key: The stock name or ticker symbol. This serves as a unique identifier for each stock in the dictionary.
Value: For each stock, the associated value is another dictionary that contains details about that specific stock. Here’s what each inner key-value pair represents:
- Sector: The industry or sector in which the stock’s company operates. This provides insight into the type of business the company is engaged in.
- Size: Indicates the size of the company, which can often be categorized as small-cap, mid-cap, or large-cap, based on its market capitalization. This gives a sense of the company’s market presence and stability.
- Region: Specifies the geographical region where the company primarily conducts its business. This can help in understanding the company’s market reach and potential external factors influencing its performance.

The stocks_info dictionary, therefore, offers a consolidated view of various stocks, detailing their sector, size, and region, allowing for a quick reference and comparative analysis.

stocks_info = {
    "MSFT":{
        "Sector":"Technology",
        "Size":"Large-cap",
        "Region":"US"
    },
    "AAPL":{
        "Sector":"Technology",
        "Size":"Large-cap",
        "Region":"US"
    },
    "JNJ":{
        "Sector":"Healthcare",
        "Size":"Large-cap",
        "Region":"US"
    },
    "PFE":{
        "Sector":"Healthcare",
        "Size":"Large-cap",
        "Region":"US"
    },
    "GS":{
        "Sector":"Finance",
        "Size":"Large-cap",
        "Region":"US"
    },
    "JPM":{
        "Sector":"Finance",
        "Size":"Large-cap",
        "Region":"US"
    },
    "XOM":{
        "Sector":"Energy",
        "Size":"Large-cap",
        "Region":"US"
    },
    "CVX":{
        "Sector":"Energy",
        "Size":"Large-cap",
        "Region":"US"
    },
    "PG":{
        "Sector":"Consumer Goods",
        "Size":"Large-cap",
        "Region":"US"
    },
    "KO":{
        "Sector":"Consumer Goods",
        "Size":"Large-cap",
        "Region":"US"
    },
    "BA":{
        "Sector":"Industrials",
        "Size":"Large-cap",
        "Region":"US"
    },
    "MMM":{
        "Sector":"Industrials",
        "Size":"Large-cap",
        "Region":"US"
    },
    "T":{
        "Sector":"Telecommunications",
        "Size":"Large-cap",
        "Region":"US"
    },
    "VZ":{
        "Sector":"Telecommunications",
        "Size":"Large-cap",
        "Region":"US"
    },
    "AMZN":{
        "Sector":"Retail",
        "Size":"Large-cap",
        "Region":"US"
    },
    "WMT":{
        "Sector":"Retail",
        "Size":"Large-cap",
        "Region":"US"
    },
    "SEDG":{
        "Sector":"Energy",
        "Size":"Mid-cap",
        "Region":"US"
    },
    
    "EGHT":{
        "Sector":"Technology",
        "Size":"Small-cap",
        "Region":"US"
    },
    "UL":{
        "Sector":"Consumer Goods",
        "Size":"Large-cap",
        "Region":"Europe"
    },
    "TM":{
        "Sector":"Automobile",
        "Size":"Large-cap",
        "Region":"Asia"
    },
    "0700.HK":{
        "Sector":"Technology",
        "Size":"Large-cap",
        "Region":"Asia"
    },
    "DB":{
        "Sector":"Banking",
        "Size":"Large-cap",
        "Region":"Europe"
    },
    "NOVN.SW":{
        "Sector":"Pharmaceutical",
        "Size":"Large-cap",
        "Region":"Europe"
    },
    "005930.KS":{
        "Sector":"Technology",
        "Size":"Large-cap",
        "Region":"Asia"
    },
    "1398.HK":{
        "Sector":"Banking",
        "Size":"Large-cap",
        "Region":"Asia"
    },
}

Extracting and Saving Stock Data to Excel#

This code block is designed to iterate through the stocks_info dictionary, fetch extensive financial data for each stock using the yfinance library, and then save that data into individual Excel files. Here’s a step-by-step explanation:

Looping through Stocks: For each stock (represented by the key, which is the stock’s name or ticker symbol) in the stocks_info dictionary, the following operations are performed:
Fetching Ticker Object: The yfinance.Ticker() function creates a Ticker object for the specified stock. This object is used to fetch various types of financial data related to that stock.
Fetching Historical Data: The history() function retrieves the stock’s historical data. The parameter period="max" ensures that data is fetched for the maximum available time frame. The date index of this historical data is then formatted to a YYYY-MM-DD string format.
Saving Data to Excel: Here, an Excel writer object is created to save the data to an Excel file named after the stock’s ticker symbol inside the data directory.
Populating Excel Sheets: Multiple sheets are populated in the Excel file for each stock, including:
- Info: Contains basic information about the stock from the stocks_info dictionary.
- Historical: Contains the stock’s historical market data.
- Income Statement: Contains the stock’s annual income statement.
- Quarterly Income Statement:
- Cashflow: Contains the stock’s quarterly income statement.
- Institutional Holders: Contains the stock’s cash flow data.
- Mutual Fund Holders: Contains information about mutual funds holding the stock.
- Major Holders: Contains information about major individual or entity holders of the stock.

This code effectively consolidates a wealth of financial information for each stock into structured Excel files, making the data easily accessible and shareable.

for key in stocks_info:
    action = yf.Ticker(key)
    history = action.history(period="max")
    history.index = history.index.strftime('%Y-%m-%d').tolist()
    with pd.ExcelWriter(f'./data/{key}.xlsx', mode='w', engine='openpyxl') as writer:
        pd.DataFrame.from_dict(stocks_info[key], orient='index').transpose().to_excel(writer, sheet_name='Info')
        history.to_excel(writer, sheet_name='Historical')
        action.income_stmt.to_excel(writer, sheet_name='Income Statement')
        action.quarterly_income_stmt.to_excel(writer, sheet_name='Quarterly Income Statement')
        action.cashflow.to_excel(writer, sheet_name='Cashflow')
        action.institutional_holders.to_excel(writer, sheet_name='Institutional Holders')
        action.mutualfund_holders.to_excel(writer, sheet_name='Mutual Fund Holders')
        action.major_holders.to_excel(writer, sheet_name='Major Holders')