Why Everyone In My Office Suddenly Started Writing Pandas Scripts Instead Of Living In Excel
It started around 2019 when a new analyst on my desk opened a 4,000-row model that crashed Excel twice before lunch. We spent three hours trying to figure out why a VLOOKUP was returning #N/A across two separate workbooks. The problem turned out to be text-formatted numbers in one sheet and actual numbers in the other. She rebuilt the entire thing in about forty minutes using a Jupyter notebook. That was the moment I realized the industry was quietly moving, whether anyone announced it or not. The shift isn't really about Python replacing Excel. Nobody is replacing Excel. It's about Python solving problems that Excel simply cannot touch without turning into something fragile and slow. When you are pulling data from five different Bloomberg terminals, cleaning it, merging it across tickers and dates, running a backtest, and exporting results to PowerPoint for a client call, doing that in VBA is a recipe for a migraine and a broken model. Python handles it in a script that runs overnight while you sleep. I want to be honest about the learning curve. You will spend your first two weeks fighting with datetime indices and merge conflicts before anything feels natural. I learned this the hard way when I tried to concatenate a panel of stock prices where the date columns had slightly different timezones because one data source was EST and another was UTC. My script ran for forty-five minutes, produced zero errors, and returned completely wrong results. I caught it only because I was printing summary statistics at each step. The workaround was adding a timezone normalization step using pytz before any merge, and switching to pandas.Timestamp with explicit tz parameter. After that, the whole pipeline dropped to about six minutes.
For people who actually want to start doing this, here is what the workflow looks like in practice. You install Python, preferably through Anaconda or Miniconda, because managing package dependencies outside of finance will make your life difficult. Then you load the core stack: pandas for data manipulation, numpy for numerical operations, yfinance or alpaca-py for market data, backtrader or vectorbt for backtesting, and matplotlib or plotly for visualization. Most people stop there and call it a day. You should not. The real value shows up when you start building reusable data pipelines. A typical setup for fundamental analysis involves pulling company financials, calculating ratios, and screening based on custom criteria. I wrote a script that pulls income statements from Yahoo Finance for S&P 500 companies, calculates free cash flow yield, earnings growth over three years, and filters for companies with negative debt-to-equity ratios. The naive approach takes about twelve minutes to run end to end. Optimized with concurrent.futures and request caching, it drops to roughly ninety seconds. The difference matters when you are running this every morning before market open. For algorithmic trading, the story is more complicated and more interesting. Backtesting frameworks like backtrader give you a foundation, but they also hide a lot of assumptions that will bite you. One thing nobody tells you is that most backtests overestimate performance because they do not account for slippage, partial fills, and the fact that your signal might arrive after the price has already moved. I built a mean-reversion strategy that looked like it was making 18 percent annual returns in backtesting. When I added realistic slippage models and latency simulation, the returns dropped to 4.2 percent. Still positive, but completely different risk profile. The lesson is that your backtest is a lower bound on optimism, not an upper bound on reality.
Here is a practical example of a Python script that does something actually useful for financial analysis. This pulls daily price data, calculates rolling volatility, and flags periods where realized volatility deviates more than two standard deviations from the moving average: import pandas as pd
import numpy as np
import yfinance as yf
from datetime import datetime, timedelta
def get_volatility_signals(ticker, lookback=252):
data = yf.download(ticker, period="2y")
returns = data["Close"].pct_change()
rolling_vol = returns.rolling(window=21).std() * np.sqrt(252)
vol_ma = rolling_vol.rolling(window=lookback).mean()
vol_std = rolling_vol.rolling(window=lookback).std()
z_score = (rolling_vol - vol_ma) / vol_std
signals = pd.DataFrame({
"date": data.index,
"close": data["Close"],
"realized_vol": rolling_vol,
"vol_z_score": z_score
})
signals = signals.dropna()
return signals[abs(signals["vol_z_score"]) > 2]
print(get_volatility_signals("SPY").tail(10)) This runs in about two seconds on a normal laptop. You can extend it to scan multiple tickers simultaneously using multiprocessing, but then you need to think about API rate limits. Yahoo Finance will block you if you hammer it. I solved this by adding exponential backoff with jitter, which reduced my failed requests from about thirty percent down to under five percent.
Get the Full Details
For people who want to go further into execution-grade trading, the ecosystem gets more serious. There is ccxt for cryptocurrency exchanges, Interactive Brokers has a proper Python API called ib_insync, and quantconnect offers a cloud-based platform if you do not want to maintain your own infrastructure. Each has tradeoffs. The IB API gives you direct market access but requires careful order management and position tracking. Quantconnect abstracts away a lot of complexity but you are locked into their environment and the data costs add up over time. I recommend starting with local backtesting using free data, then moving to paper trading, and only then considering real capital. One counter-intuitive thing about Python in finance is that the most powerful tool is often the simplest one. People write elaborate machine learning models expecting alpha. What usually works better is clean data, good feature engineering, and understanding the economics behind the signal. A well-specified regression with the right controls will outperform a black-box neural network most of the time, especially in finance where signal-to-noise ratios are abysmal. I once saw a team spend three months building a LSTM model for equity direction that was less accurate than a logistic regression with five factors. The model had too much variance and not enough interpretability, which meant nobody trusted it when it mattered. Another thing that catches people off guard is deployment. Writing a script in a notebook is not the same as running it in production. I have seen good ideas die because someone tried to schedule a notebook execution through cron and the kernel crashed halfway through. The fix is simple but nobody tells you about it upfront: wrap your logic in a proper Python module, use a task scheduler like Airflow or even just a well-written shell script with error handling, log everything, and set up alerts for failures. A backtest that runs silently at 3 AM and produces garbage output because of a missing data file is worse than no backtest at all.
If you want to download starter code and datasets, the GitHub repository that most people in my department end up using is available through standard open-source channels. Search for the main backtesting repos and the data collection scripts that come with them. There is also the Python Package Index where you can install the libraries directly. pip install pandas numpy yfinance backtrader ib_insync plotly covers most of what you need for starting out. The honest assessment is that Python in finance is powerful but noisy. The library ecosystem moves fast, breaking changes happen between versions, and documentation quality varies enormously between packages. You will spend more time reading source code and Stack Overflow answers than following official tutorials. This is normal. The people who get good at this are the ones who accept that half the job is debugging other people's code and the other half is figuring out whether their signal is actually telling them something about the market or just random noise. For what it is worth, I still use Excel for quick ad-hoc analysis and for showing numbers to people who do not read code. But anything that needs to run on a schedule, pull data from multiple sources, or test a hypothesis across hundreds of securities goes into Python. The transition was painful for about six months. Now it feels like the only way I know how to work.
