Handling large volumes of financial data for your trading strategy can be a pain. If not done right, it’s hard to generate trading ideas and quickly test them. Tools like Excel are ok for small data sets, but often fail to provide the speed and accuracy needed in today’s data rich markets.

This is where Pandas in Python can help.

Pandas offers powerful tools to preprocess, analyze, and visualize financial data efficiently. Pandas allows you to handle time-series data, compute technical indicators, and backtest a trading strategy all in one place.

I first learned about Pandas in April 2011. Before this, dealing with large datasets was a nightmare. Pandas really transformed my workflow, making data analysis faster and more reliable.

By reading today’s newsletter, you'll quickly test a trading idea in Pandas.

Let’s go!

Quickly make a trading strategy with a 82% win rate

Pandas is a Python library used for financial data analysis. It offers high-performance data manipulation and analysis tools. This makes it ideal for handling time-series data, historical prices, and market indices.

It’s great for quickly testing trading ideas—a process I call Minimum Viable Python (MVP).

Pandas can preprocess financial data, handle missing values, and resample data frequencies. It supports exploratory data analysis through descriptive statistics and visualization.

Today, we’ll look at a trading idea published on March 17 by Rob Hanna on the Quantifiable Edges blog. He noted the 3-day pullback was the first since early January. SPY had gone 48 days since the last 3-day pullback. He looked at other times SPY went at least two months without a 3-day pullback and estimated performance after it arrived.

Let’s see how it works with Python.

Download Historical SPY Data

We will start by downloading historical data for SPY from Yahoo Finance for our trading strategy. This data includes the stock prices we need to analyze.

1import pandas as pd
2import numpy as np
3import yfinance as yf
4
5ticker = "SPY"
6data = yf.download(ticker)

This code uses the yfinance library to download historical stock data for SPY. The data includes price data like open, high, low, close prices, volume, and adjusted close.

Calculate Daily Returns and Identify Down Days

Next, we will calculate the daily returns and identify the days when the stock price went down.

1# Calculate daily returns
2data["return"] = data["Close"].diff()
3
4# Identify days with negative returns
5data["down"] = data["return"] < 0

We calculate the daily returns by finding the difference between the closing prices of consecutive days. If the return is negative, it means the stock price went down that day. We create a new column to mark these down days.

Identify 3-Day Losing Streaks

Now, we will identify the periods when the stock price went down for three consecutive days.

1# Identify 3-day losing streaks
2data["3_day_losing_streak"] = (
3    data["down"] & data["down"].shift(1) & data["down"].shift(2)
4)
5
6# Initialize a column to keep track of days since last 3-day losing streak
7data["days_since_last_streak"] = np.nan
8
9# Iterate over the data to calculate the days since the last streak
10last_streak_day = -np.inf  # Initialize with a very large negative value
11
12for i in range(len(data)):
13    if data["3_day_losing_streak"].iloc[i]:
14        if i - last_streak_day >= 42:  # Check if it's been at least 42 trading days
15            data.loc[data.index[i], "days_since_last_streak"] = i - last_streak_day
16        last_streak_day = i

We check if the stock price went down for three consecutive days by looking at the down days column. If there are three down days in a row, we mark it as a 3-day losing streak. We then calculate how many days have passed since the last 3-day losing streak.

Calculate Future Returns

Finally, we calculate the returns for the next 1, 5, 10, and 21 days after each identified 3-day losing streak.

1# Filter the data to show only the occurrences that meet the criteria
2result = data.dropna(subset=["days_since_last_streak"]).copy()
3
4# Calculate future returns
5result["next_1_day_return"] = data["Close"].shift(-1) / data["Close"] - 1
6result["next_5_day_return"] = data["Close"].shift(-5) / data["Close"] - 1
7result["next_10_day_return"] = data["Close"].shift(-10) / data["Close"] - 1
8result["next_21_day_return"] = data["Close"].shift(-21) / data["Close"] - 1
9
10# Print the results
11cols = [
12    "next_1_day_return",
13    "next_5_day_return",
14    "next_10_day_return",
15    "next_21_day_return"
16]
17print(result[cols].mean())
18result[cols].gt(0).mean().plot.bar()

We filter the data to include only the rows where a 3-day losing streak occurred after at least 42 trading days without such a streak. We then calculate the future returns for the next 1, 5, 10, and 21 days. We can take the mean to determine the average holding period return and the percentage winners.

Plotting the win rate results in the following chart.

Results over the next 5 and 10 days are compelling, and suggest a strong bullish tendency.

Your Next Steps

Now that you have identified significant 3-day losing streaks and calculated future returns, try changing the criteria. For example, adjust the number of days without a losing streak or calculate returns for different future periods. You can also check other stocks for similar patterns.