Mindstorm
ALL BUSINESS DATA ANALYSIS MICROSOFT EXCEL TIPS PYTHON

How to Automate TSLA Stock Data Analysis in Excel Using Python

Python and VBA Tutorials

How to Automate TSLA (Tesla) Stock Data Analysis in Excel Using Python

If you’re looking to automate Tesla (TSLA) stock data reporting in Excel, this Python script is a game-changer. With just one script, you can pull stock prices from Yahoo Finance, generate a chart, and seamlessly export everything into a live Excel workbook.

✅ What This Script Does

This powerful Python script automates the following tasks:

1. Download TSLA Stock Data from Yahoo Finance

  • Retrieves historical stock data from January 2020 to February 2021.

  • Uses the yfinance library for reliable data access.

2. Create a Dynamic Excel Report

  • Writes data into a structured Excel sheet using openpyxl.

  • Automatically adjusts column widths for better readability.

3. Generate a TSLA Closing Price Chart

  • Uses matplotlib to create a clean, professional line chart of Tesla’s closing prices.

  • Saves and inserts the chart into the Excel file at a specified location.

4. Integrate with Your Open Excel Workbook

  • Leverages xlwings to access your active Excel instance.

  • Lists all currently open .xlsx or .xlsm files.

  • Allows you to select the target workbook where the stock data and chart will be pasted.

Python Libraries Used:

  • yfinance for stock data retrieval

  • pandas for data manipulation

  • matplotlib for charting

  • openpyxl for Excel file creation

  • xlwings for live Excel interaction

Perfect For:

  • Financial analysts

  • Excel power users

  • Python developers working with market data

  • Anyone automating stock price reports

Final Output:

A well-formatted Excel sheet with:

  • Tesla’s stock data starting from cell A6

  • A dynamic closing price chart inserted in the sheet

  • Live integration with your currently open Excel workbooks

Want to Save Time on Your Financial Reports?

Automate your TSLA stock price tracking and Excel reporting with this Python script — no manual copy-pasting, no clutter, just clean, accurate data and visualizations.

#Python script starts here

import yfinance as yf
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
from openpyxl import Workbook
from openpyxl.drawing.image import Image as ExcelImage
from openpyxl.utils import get_column_letter
import tempfile
import os
import xlwings as xw
# === Step 1: Download stock data ===
start = datetime(2020, 1, 1)
end = datetime(2021, 2, 28)
ticker = ‘TSLA’
df = yf.download(ticker, start=start, end=end)
data = df.reset_index()
# === Step 2: Create temporary files ===
temp_dir = tempfile.gettempdir()
temp_excel = os.path.join(temp_dir, ‘tsla_temp_data.xlsx’)
temp_plot = os.path.join(temp_dir, ‘tsla_temp_plot.png’)
# === Step 3: Write data and chart to temp Excel ===
wb = Workbook()
ws = wb.active
ws.title = “Stock Data”
# Write headers and data
for col_idx, col in enumerate(data.columns, start=1):
    ws.cell(row=6, column=col_idx, value=str(col))
for row_idx, row in enumerate(data.values, start=7):
    for col_idx, value in enumerate(row, start=1):
        ws.cell(row=row_idx, column=col_idx, value=value)
# Auto-adjust column widths
for col_idx, column in enumerate(data.columns, start=1):
    max_len = len(str(column))
    for row in data.values:
        val = row[col_idx – 1]
        if val is not None:
            max_len = max(max_len, len(str(val)))
    col_letter = get_column_letter(col_idx)
    ws.column_dimensions[col_letter].width = max_len + 2
# Create and save plot
plt.figure(figsize=(12, 6))
plt.plot(df.index, df[‘Close’], label=’Close Price’)
plt.title(f'{ticker} Closing Price’)
plt.xlabel(‘Date’)
plt.ylabel(‘Price (USD)’)
plt.grid(True)
plt.legend()
plt.tight_layout()
plt.savefig(temp_plot)
plt.close()
# Insert image
img = ExcelImage(temp_plot)
img.anchor = ‘H2’
ws.add_image(img)
# Save temp file
wb.save(temp_excel)
# === Step 4: Select target workbook from open files ===
# Connect to Excel
app = xw.apps.active
# Open temp file
temp_book = app.books.open(temp_excel)
temp_sheet = temp_book.sheets[0]
temp_range = temp_sheet.range(“A6”).expand()
# List all open .xlsx/.xlsm workbooks except the temp one
available_books = [
    book for book in app.books
    if book.fullname.lower().endswith((‘.xlsx’, ‘.xlsm’)) and book.fullname != temp_book.fullname
]
# If none found, exit
if not available_books:
    print(“❌ No open target .xlsx or .xlsm workbooks found.”)
    temp_book.close()
    exit()
# Display list for selection
print(“\nSelect the target workbook:”)
for idx, book in enumerate(available_books, start=1):
    print(f”{idx}. {os.path.basename(book.fullname)}”)
# Prompt for selection
while True:
    try:
        selection = int(input(“Enter the number of the target workbook: “))
        if 1 <= selection <= len(available_books):
            target_book = available_books[selection – 1]
            break
        else:
            print(“Please enter a valid number from the list.”)
    except ValueError:
        print(“Invalid input. Please enter a number.”)
# === Step 5: Copy data and image to target ===
target_sheet = target_book.sheets.active
target_sheet.range(“A6”).value = temp_range.value
target_sheet.pictures.add(temp_plot,
                          left=target_sheet.range(“H9”).left,
                          top=target_sheet.range(“H9”).top)
# Close temp file
temp_book.close()
print(f”✅ Data and chart copied into ‘{os.path.basename(target_book.fullname)}’ (active sheet).”)
#Python script ends here
Views: 3

Related Posts - Σχετικά Άρθρα

Comments are closed.

Pin It