The Problem
Your organization uses a June 30 fiscal year-end, but pandas assumes January-December.
You need:
- Q1 = July-September
- Q2 = October-December
- Q3 = January-March
- Q4 = April-June
Excel requires complex nested IFs. Python? One line.
The Solution
import pandas as pd
# Sample data
df = pd.DataFrame({
'Date': ['2024-07-15', '2024-10-20', '2025-01-10', '2025-05-30'],
'Amount': [10000, 15000, 12000, 18000]
})
df['Date'] = pd.to_datetime(df['Date'])
# Calculate fiscal quarter (June 30 year-end)
df['Fiscal_Quarter'] = pd.PeriodIndex(df['Date'], freq='Q-JUN').strftime('Q%q')
# Calculate fiscal year
df['Fiscal_Year'] = df['Date'].dt.to_period('A-JUN').dt.year
print(df)
Output:
Date Amount Fiscal_Quarter Fiscal_Year
0 2024-07-15 10000 Q1 2025
1 2024-10-20 15000 Q2 2025
2 2025-01-10 12000 Q3 2025
3 2025-05-30 18000 Q4 2025
That’s it! One line per calculation.
Other Fiscal Year-Ends
October 31 (Federal Government)
df['Fiscal_Quarter'] = pd.PeriodIndex(df['Date'], freq='Q-OCT').strftime('Q%q')
df['Fiscal_Year'] = df['Date'].dt.to_period('A-OCT').dt.year
March 31 (UK, Japan, India)
df['Fiscal_Quarter'] = pd.PeriodIndex(df['Date'], freq='Q-MAR').strftime('Q%q')
df['Fiscal_Year'] = df['Date'].dt.to_period('A-MAR').dt.year
September 30 (Many Nonprofits)
df['Fiscal_Quarter'] = pd.PeriodIndex(df['Date'], freq='Q-SEP').strftime('Q%q')
df['Fiscal_Year'] = df['Date'].dt.to_period('A-SEP').dt.year
Aggregate by Fiscal Quarter
# Group by fiscal quarter
quarterly_totals = df.groupby('Fiscal_Quarter')['Amount'].sum()
print(quarterly_totals)
Output:
Fiscal_Quarter
Q1 10000
Q2 15000
Q3 12000
Q4 18000
Excel Comparison
Excel (June 30 fiscal year):
=IF(MONTH(A2)<=6, "Q"&CEILING.MATH((MONTH(A2)+6)/3,1),
"Q"&CEILING.MATH((MONTH(A2)-6)/3,1))
Python:
df['Q'] = pd.PeriodIndex(df['Date'], freq='Q-JUN').strftime('Q%q')
No contest. -
Try It!
import pandas as pd
# Create sample dates
dates = pd.date_range('2024-07-01', '2025-06-30', freq='M')
df = pd.DataFrame({'Date': dates})
# Add fiscal quarters
df['FQ'] = pd.PeriodIndex(df['Date'], freq='Q-JUN').strftime('Q%q')
print(df)
Tags: #Python #QuickTip #FiscalYear #FiscalQuarter #GovernmentAccounting #Dates
One line solves fiscal quarter headaches. Bookmark this!