It’s been nearly forty years since the first spreadsheet, VisiCalc, launched on the Apple II in 1979. Today, whilst VisiCalc has long since disappeared, spreadsheets remain one of the most important tools for traders — indispensable for most. But as markets generate more data than ever, as speeds increase, and as alternative data sources proliferate, the spreadsheet is showing its age in ways that matter to your bottom line.
You have a spreadsheet that takes five minutes to recalculate every morning. Your tick data fills up Excel’s rows before noon. Your team shares numbers by emailing each other updated versions of the same file. There is a better tool for professional trading workflows — and most traders have never seen it used properly.
Page Contents
What You’ll Learn
- Why spreadsheets hit their limits with real-world trading data
- How to load and analyse financial data with pandas
- How to create interactive charts with Plotly that are impossible in Excel
- How to automate repetitive trading workflows with Python scripts
- How to combine Python with your existing Excel workflows using xlwings
- Which libraries and tools to start with as a trader with no coding background
Why Spreadsheets Stop Working for Traders
Excel is exceptional for structured, relatively small datasets. If you’re working with end-of-day price data across a handful of instruments, a well-maintained spreadsheet is hard to beat. But trading data has a habit of growing in ways that expose the limits of rows-and-columns thinking:
- Tick-level data: A single stock trading 10,000 times a day generates 10,000 rows. Over a year with 500 stocks, that’s 5 million rows — well beyond what Excel handles gracefully. Python’s pandas can process millions of rows on a laptop without breaking a sweat.
- Multiple data sources: Real trading involves price feeds, order book data, news sentiment, economic calendars, and alternative data. Combining these in a spreadsheet is manual and error-prone. Python can ingest, clean, and merge them programmatically.
- Repetitive workflows: If you find yourself doing the same sequence of steps every morning — pulling data, running calculations, generating a report — that’s a workflow that should be automated. Python scripts can do in seconds what takes 30 minutes manually.
- Non-standard data: Scraped web data, JSON APIs, FIX protocol messages — trading increasingly involves data that doesn’t fit neatly into an Excel grid. Python handles all of it.
The 5-minute wait: If pressing F9 or running a macro means waiting minutes for a result, the tool has stopped working for you. Python trades speed for capability — and for serious data work, that trade is worth making.
The good news: you don’t need to abandon Excel. Libraries like xlwings let you call Python directly from Excel, getting the best of both worlds.
Prerequisites
This guide assumes no programming experience. If you’re comfortable navigating folders, installing software, and running a terminal command, you have everything you need. If you’d like a Python refresher first, start with our Introduction to Python Programming guide.
The examples below use pandas for data manipulation, Plotly for interactive charts, and yfinance to fetch real market data — all freely available and beginner-friendly.
Getting Market Data with Python
The first step in any trading analysis is getting the data. Python makes this remarkably straightforward. The yfinance library lets you download historical price data from Yahoo Finance with a single function call:
# pip install yfinance pandas
import yfinance as yf
import pandas as pd
# Download 1 year of daily price data for Apple
ticker = yf.Ticker("AAPL")
df = ticker.history(period="1y")
print(f"Downloaded {len(df)} rows of data")
print(df.tail())
# df now contains: Open, High, Low, Close, Volume, Dividends, Stock Splits
The resulting DataFrame is a table with rows indexed by date. From here, calculating daily returns, moving averages, or volatility takes a single line of pandas:
# Calculate daily returns df["Daily Return"] = df["Close"].pct_change() # Calculate 20-day moving average df["MA20"] = df["Close"].rolling(window=20).mean() # Calculate rolling 20-day volatility (annualised) df["Volatility"] = df["Daily Return"].rolling(window=20).std() * (252 ** 0.5) print(df[["Close", "MA20", "Volatility"]].tail())
Doing the same in Excel requires multiple columns, array formulas, and a lot of copying. In Python it’s three lines — and it scales to thousands of tickers without modification.
Interactive Charts with Plotly
One of Excel’s best features is how quickly you can visualise data. Python does everything Excel can do and more — particularly with Plotly, which produces interactive, web-ready charts that are genuinely difficult to replicate in a spreadsheet.
# pip install plotly
import plotly.graph_objects as go
# Create a candlestick chart
fig = go.Figure(data=[
go.Candlestick(
x=df.index,
open=df["Open"],
high=df["High"],
low=df["Low"],
close=df["Close"],
)
])
fig.update_layout(
title="AAPL Daily Candlestick Chart",
yaxis_title="Price (USD)",
xaxis_rangeslider_visible=False,
)
fig.show() # Opens in browser as an interactive chart
The result is a fully interactive chart — zoom, pan, hover for tooltips, export as PNG — built from data that came directly from an API. You can embed this in a report, share it as a link, or host it on a dashboard.
Excel can produce static candlestick charts, but the interactivity requires third-party plugins. Plotly’s charts work in any browser, any device, and can be updated automatically when the data changes.
Automating Your Morning Workflow
Trading involves significant repetitive work: pulling the previous day’s data, running calculations, updating a risk dashboard, and distributing a morning note. Python is excellent for automating exactly this kind of workflow. Once written, a script runs in seconds and produces consistent, error-free output every time.
import yfinance as yf
import pandas as pd
from datetime import date, timedelta
# ── Config ──────────────────────────────────────────────────────
tickers = ["AAPL", "MSFT", "SPY"]
lookback = 7 # days
# ── Pull latest data ───────────────────────────────────────────
end_date = date.today()
start_date = end_date - timedelta(days=lookback)
print(f"Generating morning brief for {end_date}")
print("=" * 50)
for ticker_sym in tickers:
ticker = yf.Ticker(ticker_sym)
df = ticker.history(start=start_date, end=end_date)
if df.empty:
print(f"{ticker_sym}: No data available")
continue
close = df["Close"].iloc[-1]
ret_7d = (close / df["Close"].iloc[0] - 1) * 100
vol = df["Volume"].mean()
print(f"{ticker_sym}: ${close:.2f} | 7d return: {ret_7d:+.1f}% | Avg vol: {vol:,.0f}")
print("=" * 50)
print("Morning brief complete.")
Run this script at 7am every morning and your briefing is ready before the market opens. You can extend it to save the output to a CSV, email it to your team using Python’s smtplib, or push it to a shared Google Sheet — all programmatically, no copy-pasting.
BeautifulSoup for alternative data: If you need to pull a live value from a web page — a commodity price, an economic indicator, a sentiment score — Python’s BeautifulSoup library can scrape it cleanly and bring it into your analysis pipeline.
Combining Python with Excel Using xlwings
One of the most practical entry points for traders is xlwings — a library that lets Python and Excel work together. You handle the number crunching in Python (where it’s fast and flexible) but keep charts, inputs, and outputs in Excel (where your team already knows how to work).
# pip install xlwings
import xlwings as xw
import yfinance as yf
import pandas as pd
# Connect to an open Excel workbook
wb = xw.Book("Trading_Dashboard.xlsx")
sheet = wb.sheets["Daily"]
# Pull live data
ticker = yf.Ticker("AAPL")
df = ticker.history(period="1mo")
# Write prices to specific cells in Excel
sheet["B2"].value = df["Close"].iloc[-1] # Latest close
sheet["B3"].value = df["Close"].pct_change().iloc[-1] # Daily return
sheet["B4"].value = df["Volume"].mean() # Average volume
# Run any Python calculation and put the result in Excel
sheet["D2"].value = df["Close"].rolling(20).mean().iloc[-1]
# Save — all changes reflected in the workbook immediately
wb.save()
With xlwings you can even write custom Excel functions in Python that you call directly from a cell formula — =PY(STOCK_VOLATILITY("AAPL")) pulling live Python logic into a spreadsheet your whole desk uses.
Common Mistakes and Gotchas
- Installing too many packages at once: Start with pandas, yfinance, and plotly. The Python ecosystem for finance is vast — resist the urge to install 20 libraries on day one. Learn each one before adding the next.
- Ignoring data types: Pandas DataFrames are flexible but can silently promote integers to floats or strings to dates. Always check
df.dtypesafter loading data and convert columns explicitly where needed. - Forgetting the index in pandas: When you slice a DataFrame by date, the result keeps the date as the index. Many beginners lose rows by forgetting this and writing
df["2024-01-01"]instead ofdf.loc["2024-01-01"]. - Survivorship bias: If you’re backtesting a strategy using only currently-traded stocks, you’re implicitly excluding delisted companies — which overstates returns. Use a data provider that includes delisted tickers for serious backtesting.
- Plotly charts not updating: Plotly charts in Jupyter notebooks require
fig.show()to render. In scripts, usefig.write_html("chart.html")to save as a file you can open in a browser.
Summary
Python doesn’t replace Excel — it extends it. For traders who work with large datasets, need custom analytics, build automated workflows, or want interactive charts, Python is a genuine competitive advantage. The libraries are free, the community is large, and the learning curve for the practical subset you need is gentler than most people expect.
The most important thing to start with is getting data into a pandas DataFrame and doing one calculation you currently do in Excel. Once that’s working, the rest follows quickly.
- Start with our Introduction to Python Programming — no prior experience needed, sets you up with the fundamentals
- Learn pandas for financial data analysis — the core library for all data work in Python trading applications
- Practice by replicating one calculation you currently do in Excel with a pandas one-liner — that’s the fastest way to see the value
- Explore Plotly for interactive charts — the visualisation library that makes Python charts worth sharing
- Check out xlwings to combine Python with your existing Excel workflows — the most practical entry point for trading desks already on Excel
This article was originally published in June 2019 and updated in April 2026 to reflect current Python libraries and best practices for financial data analysis.
