5-Minute Fix: Stop Manually Counting Trading Days

The Task That Shouldn’t Be Hard (But Is)

Manager: “I need the number of trading days between these 500 buy/sell transactions.”

Me (looking at calendar): “Um…”

Manager: “Just count the weekdays and exclude holidays.”

Me: “Which holidays?”

Manager: “Stock market holidays.”

Me: “So… New Year’s Day, MLK Day, Presidents Day, Good Friday, Memorial Day, Juneteenth, Independence Day, Labor Day, Thanksgiving, Christmas…”

Manager: “Yes. All of them. For the past 7 years.”

Me: internal screaming

The Excel Way: A Descent Into Madness

Attempt #1: NETWORKDAYS()

=NETWORKDAYS(A2, B2)

What It Does: Counts weekdays (Mon-Fri) between two dates

What It Doesn’t Do: Exclude stock market holidays

Result: Off by 9-10 days per year

Attempt #2: NETWORKDAYS() with Holiday List

=NETWORKDAYS(A2, B2, Holidays!$A$2:$A$100)

What You Need:

Create “Holidays” sheet
Manually list all stock market holidays
For multiple years (2018-2025 = 8 years × 10 holidays = 80 dates)
Make sure dates are formatted correctly
Use absolute references ($A$2:$A$100)
Pray nothing breaks

Problems:

Holiday dates change every year (e.g., Thanksgiving = 4th Thursday in November)
If a holiday falls on Saturday, market closes on Friday
If a holiday falls on Sunday, market closes on Monday
One typo in the holiday list = wrong counts for ALL transactions
Different markets = different holidays (NYSE ≠ NASDAQ ≠ London ≠ Tokyo)

Time to build: 2-3 hours

Maintenance: Every year (add new holidays)

Reliability: Low

Attempt #3: Give Up and Count Manually

For 500 transactions?

No thanks.

The Python Way: One Line of Code

import pandas as pd

# Read transactions
df = pd.read_excel('transactions.xlsx')
df['Buy_Date'] = pd.to_datetime(df['Buy_Date'])
df['Sell_Date'] = pd.to_datetime(df['Sell_Date'])

# Count trading days (NYSE calendar)
df['Trading_Days'] = df.apply(
 lambda row: len(pd.bdate_range(
 start=row['Buy_Date'],
 end=row['Sell_Date'],
 freq='C', # Custom business day (US stock market)
 holidays=pd.tseries.holiday.USFederalHolidayCalendar().holidays(
 start=row['Buy_Date'],
 end=row['Sell_Date']
 )
 )),
 axis=1
)

# Done! 

Wait, it gets better…

Even Easier: Use `numpy.busday_count()`

import pandas as pd
import numpy as np

# Read transactions
df = pd.read_excel('transactions.xlsx')
df['Buy_Date'] = pd.to_datetime(df['Buy_Date'])
df['Sell_Date'] = pd.to_datetime(df['Sell_Date'])

# Get US stock market holidays
from pandas.tseries.holiday import USFederalHolidayCalendar
cal = USFederalHolidayCalendar()
holidays = cal.holidays(
 start=df['Buy_Date'].min(),
 end=df['Sell_Date'].max()
)

# Count trading days
df['Trading_Days'] = np.busday_count(
 df['Buy_Date'].values.astype('datetime64[D]'),
 df['Sell_Date'].values.astype('datetime64[D]'),
 holidays=holidays.values.astype('datetime64[D]')
)

print(df[['Security', 'Buy_Date', 'Sell_Date', 'Trading_Days']])

Result:

 Security Buy_Date Sell_Date Trading_Days
AAPL 2024-01-05 2024-03-15 51
MSFT 2024-02-12 2024-06-28 100
GOOGL 2023-11-20 2024-01-25 46

Time: 5 seconds

Accuracy: Perfect (uses official holiday calendar)

Maintenance: Zero (calendar updates automatically)

How It Works

Step 1: Import Holiday Calendar

from pandas.tseries.holiday import USFederalHolidayCalendar
cal = USFederalHolidayCalendar()

Available Calendars:

USFederalHolidayCalendar → Federal holidays (close match for NYSE/NASDAQ)
Custom calendars (we’ll build one below)

Step 2: Get Holiday Dates

holidays = cal.holidays(
 start='2023-01-01',
 end='2025-12-31'
)

print(holidays)

Output:

DatetimeIndex([
 '2023-01-02', # New Year's Day (observed - fell on Sunday)
 '2023-01-16', # Martin Luther King Jr. Day
 '2023-02-20', # Presidents Day
 '2023-05-29', # Memorial Day
 '2023-06-19', # Juneteenth
 '2023-07-04', # Independence Day
 '2023-09-04', # Labor Day
 '2023-11-23', # Thanksgiving
 '2023-12-25', # Christmas
 '2024-01-01', # New Year's Day
 ...
])

Automatically handles:

Holidays that fall on weekends (observance rules)
Floating holidays (e.g., Thanksgiving = 4th Thursday in November)
Multi-year ranges

Step 3: Count Business Days

import numpy as np

# Count business days between two dates
trading_days = np.busday_count(
 '2024-01-05', # Start date
 '2024-03-15', # End date
 holidays=['2024-01-15', '2024-02-19'] # Holidays to exclude
)

print(trading_days) # 51

Custom Stock Market Calendar

The US Federal Holiday Calendar is close but not exact for stock markets.

Stock markets close for:

New Year’s Day
Martin Luther King Jr. Day
Presidents Day (Washington’s Birthday)
Good Friday ⬅️ NOT a federal holiday!
Memorial Day
Juneteenth
Independence Day
Labor Day
Thanksgiving
Christmas

Building a Custom NYSE Calendar

from pandas.tseries.holiday import (
 Holiday,
 USFederalHolidayCalendar,
 GoodFriday,
 nearest_workday
)

class NYSECalendar(USFederalHolidayCalendar):
 """NYSE Stock Market Holiday Calendar"""
 rules = [
 Holiday('New Year\'s Day', month=1, day=1, observance=nearest_workday),
 Holiday('Martin Luther King Jr. Day', month=1, day=1, offset=pd.DateOffset(weekday=MO(3))),
 Holiday('Presidents Day', month=2, day=1, offset=pd.DateOffset(weekday=MO(3))),
 GoodFriday, # Unique to stock market!
 Holiday('Memorial Day', month=5, day=31, offset=pd.DateOffset(weekday=MO(-1))),
 Holiday('Juneteenth', month=6, day=19, observance=nearest_workday),
 Holiday('Independence Day', month=7, day=4, observance=nearest_workday),
 Holiday('Labor Day', month=9, day=1, offset=pd.DateOffset(weekday=MO(1))),
 Holiday('Thanksgiving', month=11, day=1, offset=pd.DateOffset(weekday=TH(4))),
 Holiday('Christmas', month=12, day=25, observance=nearest_workday)
 ]

# Use custom calendar
cal = NYSECalendar()
nyse_holidays = cal.holidays(start='2023-01-01', end='2025-12-31')

# Count trading days
df['Trading_Days'] = np.busday_count(
 df['Buy_Date'].values.astype('datetime64[D]'),
 df['Sell_Date'].values.astype('datetime64[D]'),
 holidays=nyse_holidays.values.astype('datetime64[D]')
)

Now 100% accurate for NYSE/NASDAQ! -

Real-World Example: Investment Holding Periods

The Scenario

Goal: Calculate holding periods for 500 investment transactions

Why?

Short-term capital gains (≤ 1 year) taxed at ordinary income rates
Long-term capital gains (> 1 year) taxed at lower rates
Need EXACT trading days for compliance

Data:

Security Buy_Date Sell_Date Cost_Basis Proceeds
AAPL 2023-01-15 2024-03-20 10,000 12,500
MSFT 2023-06-10 2024-08-25 15,000 18,200
GOOGL 2024-02-05 2024-11-15 8,000 9,100

The Solution

import pandas as pd
import numpy as np
from pandas.tseries.holiday import USFederalHolidayCalendar

# Read transactions
df = pd.read_excel('transactions.xlsx')
df['Buy_Date'] = pd.to_datetime(df['Buy_Date'])
df['Sell_Date'] = pd.to_datetime(df['Sell_Date'])

# Get holidays
cal = USFederalHolidayCalendar()
holidays = cal.holidays(
 start=df['Buy_Date'].min(),
 end=df['Sell_Date'].max()
)

# Count trading days
df['Trading_Days'] = np.busday_count(
 df['Buy_Date'].values.astype('datetime64[D]'),
 df['Sell_Date'].values.astype('datetime64[D]'),
 holidays=holidays.values.astype('datetime64[D]')
)

# Calculate holding period in years
df['Holding_Period_Years'] = df['Trading_Days'] / 252 # ~252 trading days/year

# Classify as short-term or long-term
df['Capital_Gain_Type'] = df['Holding_Period_Years'].apply(
 lambda x: 'Long-Term' if x > 1 else 'Short-Term'
)

# Calculate gains
df['Gain'] = df['Proceeds'] - df['Cost_Basis']

print(df[['Security', 'Trading_Days', 'Holding_Period_Years', 'Capital_Gain_Type', 'Gain']])

Output:

 Security Trading_Days Holding_Period_Years Capital_Gain_Type Gain
AAPL 289 1.15 Long-Term 2,500
MSFT 320 1.27 Long-Term 3,200
GOOGL 198 0.79 Short-Term 1,100

Perfect for tax reporting!

Bonus: Count Business Days (Not Just Trading Days)

Scenario: You need business days (Mon-Fri, excluding holidays) but NOT stock market trading days.

Example: Accounts payable aging (“Net 30” payment terms)

import pandas as pd

# Simple business day count (weekdays only, no holidays)
df['Business_Days_Simple'] = df.apply(
 lambda row: len(pd.bdate_range(
 start=row['Invoice_Date'],
 end=row['Payment_Date']
 )),
 axis=1
)

# Business days with federal holidays excluded
from pandas.tseries.holiday import USFederalHolidayCalendar
import numpy as np

cal = USFederalHolidayCalendar()
holidays = cal.holidays(start=df['Invoice_Date'].min(), end=df['Payment_Date'].max())

df['Business_Days_WithHolidays'] = np.busday_count(
 df['Invoice_Date'].values.astype('datetime64[D]'),
 df['Payment_Date'].values.astype('datetime64[D]'),
 holidays=holidays.values.astype('datetime64[D]')
)

print(df[['Invoice_Date', 'Payment_Date', 'Business_Days_Simple', 'Business_Days_WithHolidays']])

Other Country Calendars

Working with international markets?

# UK holidays
from pandas.tseries.holiday import UKHolidayCalendar
uk_cal = UKHolidayCalendar()
uk_holidays = uk_cal.holidays(start='2024-01-01', end='2024-12-31')

# Custom calendar for other countries
from pandas.tseries.holiday import Holiday, AbstractHolidayCalendar

class JapanStockMarketCalendar(AbstractHolidayCalendar):
 rules = [
 Holiday('New Year\'s Day', month=1, day=1),
 Holiday('Coming of Age Day', month=1, day=1, offset=pd.DateOffset(weekday=MO(2))),
 Holiday('National Foundation Day', month=2, day=11),
 # ... add all Japanese holidays
 ]

Common Pitfalls

Pitfall #1: Inclusive vs. Exclusive Counting

# Question: How many trading days from Jan 2 to Jan 5?

# Jan 2 (Mon), Jan 3 (Tue), Jan 4 (Wed), Jan 5 (Thu) = 4 days? Or 3?

# numpy.busday_count() is EXCLUSIVE of end date
trading_days = np.busday_count('2024-01-02', '2024-01-05')
print(trading_days) # 3 (excludes Jan 5)

# To include end date, add 1 day
trading_days_inclusive = np.busday_count('2024-01-02', '2024-01-06')
print(trading_days_inclusive) # 4 (includes Jan 5)

Pitfall #2: Date Format Issues

# numpy.busday_count() requires 'datetime64[D]' format

# WRONG:
trading_days = np.busday_count(df['Buy_Date'], df['Sell_Date']) # Error

# RIGHT:
trading_days = np.busday_count(
 df['Buy_Date'].values.astype('datetime64[D]'),
 df['Sell_Date'].values.astype('datetime64[D]')
) # Works

Pitfall #3: Forgetting Good Friday

US Federal Holidays do NOT include Good Friday, but stock markets close!

Solution: Use custom NYSE calendar (shown above)

The Bottom Line

Excel:

Manual holiday list
Error-prone
Requires yearly maintenance
Different for each market

Python:

Built-in holiday calendars
Accurate
Zero maintenance
Easy to customize

Time Saved: Hours → Seconds

Your Turn

Need to count trading days?

import numpy as np
from pandas.tseries.holiday import USFederalHolidayCalendar

cal = USFederalHolidayCalendar()
holidays = cal.holidays(start='2024-01-01', end='2024-12-31')

trading_days = np.busday_count(
 '2024-01-15',
 '2024-03-20',
 holidays=holidays.values.astype('datetime64[D]')
)

print(trading_days)

That’s it. You’re done.

Try It Yourself

Want the complete working examples? Download from GitHub

Need help with international market calendars? Drop a comment!

Join the Discussion on Discord! -

Working with international markets? Need custom calendars? Join our Discord!

Join PANDAUDIT Discord Server

Next time: “The One-Liner That Saved Me 10 Hours a Week” →