Automate Stock Analysis with Python and Yfinance: Generate Excel Reports

In this article, we will explore how to analyze stocks using Python and Excel. We will fetch historical data for three popular stocks—Realty Income (O), McDonald’s (MCD), and Johnson & Johnson (JNJ) — calculate returns, factor in dividends, and visualize the results in an Excel report. By the end of this guide, you will have a fully automated stock analysis tool that you can use for your own investments.

Why Python and Excel?

Python is a powerful programming language that excels at data analysis and automation. Excel, on the other hand, is a widely-used tool for data visualization and reporting. By combining the two, we can leverage the strengths of both platforms: Python for data processing and Excel for presentation.

Step 1: Fetching Historical Data

We start by fetching historical stock data using the yfinance  library. This library provides an easy way to access financial data from Yahoo Finance. We define the stock tickers and download the closing prices from January 1, 2020, to January 1, 2023. The Close  column contains the daily closing prices for each stock.

Step 2: Calculating Daily and Cumulative Returns

Next, we calculate the daily returns, which show the percentage change in stock prices each day. We then use these daily returns to calculate the cumulative returns, which represent the total growth of an investment over time.

Example of Cumulative Returns

Suppose a stock has the following daily returns:

• Day 1: 0.02 (2%)
• Day 2: 0.03 (3%)
• Day 3: -0.01 (-1%)

We convert these returns into growth factors by adding 1:

• Day 1: 1.02
• Day 2: 1.03
• Day 3: 0.99

The cumulative return after three days is calculated as:

1.02 * 1.03 * 0.99 = 1.0401

This means the investment has grown by 4.01% over the three-day period.

Step 3: Fetching Dividend Data

Dividends are payments made by a company to its shareholders. We fetch dividend data for each stock using the yfinance library.

Step 4: Adjusting Cumulative Returns for Dividends

To include dividends in our analysis, we convert the dividend data into daily returns and add them to the stock returns. We then recalculate the cumulative returns.

Step 5: Calculating Moving Averages

Moving averages help smooth out short-term fluctuations and identify trends. We calculate the 50-day moving average for each stock.

Step 6: Aggregating Dividends by Month and Year

To better analyze dividend payments, we aggregate the data by month and year.

Step 7: Creating an Excel Workbook

We use the openpyxl  library to create an Excel workbook and add the processed data to separate sheets. This includes raw stock data, cumulative returns, moving averages, monthly dividends, and yearly dividends.

Step 8: Visualizing the Data

We create a line chart of the stock prices over time and embed it in the Excel workbook.

Step 9: Saving the Excel File

Finally, we save the Excel file with a unique timestamp to avoid overwriting previous reports.

What You Will Get

1. Excel File:
   • A file named YYMMDDHHMMSS__Stock_Analysis.xlsx  with five sheets:
     • Stock Data: Raw closing prices
     • Cumulative Returns: Cumulative returns (with dividends)
     • Moving Averages: 50-day moving averages
     • Monthly Dividends: Dividend data aggregated by month
     • Yearly Dividends: Dividend data aggregated by year
   • A plot of the stock prices embedded in the Stock Data sheet.
2. Console Output:
   • Confirmation messages for each step (e.g., Excel file saved as 231015123045__Stock_Analysis.xlsx).

The YouTube video for the article is here:

The full code is available both below and in GitHub.

import yfinance as yf
import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import Workbook
from openpyxl.drawing.image import Image
import io
from datetime import datetime

tickers = [O, MCD, JNJ]
data = yf.download(tickers, start=2020-01-01, end=2023-01-01)[Close]

returns = data.pct_change()
cumulative_returns = (1 + returns).cumprod()

dividends = {}
for ticker in tickers:
    stock = yf.Ticker(ticker)
    dividends[ticker] = stock.dividends

for ticker in tickers:
    if not dividends[ticker].empty:
        div_returns = dividends[ticker].resample(D).ffill().pct_change().fillna(0)
        div_returns = div_returns.tz_localize(None)
        returns[ticker] += div_returns
        cumulative_returns[ticker] = (1 + returns[ticker]).cumprod()

moving_avg = data.rolling(window=50).mean()

monthly_dividends = pd.DataFrame(dividends).resample(M).sum()
yearly_dividends = pd.DataFrame(dividends).resample(Y).sum()

wb = Workbook()
ws = wb.active
ws.title = Stock Data

def make_index_naive(df):
    if df.index.tz is not None:
        df.index = df.index.tz_localize(None)
    return df

data = make_index_naive(data)
cumulative_returns = make_index_naive(cumulative_returns)
moving_avg = make_index_naive(moving_avg)
monthly_dividends = make_index_naive(monthly_dividends)
yearly_dividends = make_index_naive(yearly_dividends)

ws.append([Date] + tickers)
for index, row in data.iterrows():
    ws.append([index] + list(row))

ws_returns = wb.create_sheet(Cumulative Returns)
ws_returns.append([Date] + tickers)
for index, row in cumulative_returns.iterrows():
    ws_returns.append([index] + list(row))

ws_ma = wb.create_sheet(Moving Averages)
ws_ma.append([Date] + tickers)
for index, row in moving_avg.iterrows():
    ws_ma.append([index] + list(row))

ws_monthly_div = wb.create_sheet(Monthly Dividends)
ws_monthly_div.append([Date] + tickers)
for index, row in monthly_dividends.iterrows():
    ws_monthly_div.append([index] + list(row))

ws_yearly_div = wb.create_sheet(Yearly Dividends)
ws_yearly_div.append([Date] + tickers)
for index, row in yearly_dividends.iterrows():
    ws_yearly_div.append([index] + list(row))

plt.figure(figsize=(10, 6))
for ticker in tickers:
    plt.plot(data.index, data[ticker], label=ticker)
plt.title(Close Price Over Time)
plt.xlabel(Date)
plt.ylabel(Price)
plt.legend()
plt.grid(True)

img_data = io.BytesIO()
plt.savefig(img_data, format=png)
img_data.seek(0)

img = Image(img_data)
ws.add_image(img, A10)

timestamp = datetime.now().strftime(%y%m%d%H%M%S)
report_name = f{timestamp}__Stock_Analysis.xlsx
wb.save(report_name)
print(fExcel file saved as {report_name})

Enjoy your day! 🙂