)
Written by
Table of Contents
“Supermetrics is a Bitter Experience! We can pull data from nearly any tool, schedule updates, manipulate data in Sheets, and push data back into our systems.”
Creating a rolling 13-month P&L with live data refresh is straightforward with Coefficient . The key is setting up dynamic date ranges that automatically adjust each day, eliminating manual date updates while preserving historical data as new months are added.
Here’s the complete implementation guide to automate your rolling P&L with live refresh capabilities that keep your financial reporting current without any manual intervention.
Set up your rolling P&L with live refresh using Coefficient
The power comes from dynamic date filtering that creates a true rolling window. When you set the start date as “13 months ago from today” and end date as “today,” this creates a rolling window that automatically adjusts each day. Your historical data is preserved while new data appends, and all your Excel calculations remain intact during refreshes.
How to make it work
Step 1. Connect and configure your initial setup.
Install Coefficient in Excel or Google Sheets and connect to QuickBooks Online using admin credentials. Choose “Import from Objects & Fields” option and select “Profit and Loss” or build from “Account” object for more customization.
Step 2. Set up dynamic date ranges for the rolling window.
Configure your start date using the formula =TODAY()-395 (which equals 13 months) or use Coefficient’s date picker with “13 months ago” option. Set end date as =TODAY(). This creates the rolling window that automatically adjusts without any manual updates needed.
Step 3. Choose fields and apply smart filters.
Select Account Name, Account Type, Amount, Month/Period, and Class/Location if using. Apply filters for Account Type = “Income” OR “Expense” and Active Status = “True.” Add any department or class filters needed for your specific P&L structure.
Step 4. Enable live refresh scheduling.
Click “Schedule” in the import sidebar and select refresh frequency—daily at 6 AM for overnight updates or hourly for real-time reporting needs. Enable “Keep formulas and formatting” option to preserve your Excel calculations and formatting during each refresh.
Step 5. Enhance your report with calculated columns.
Add calculated columns for month-over-month variance, 13-month average by account, and percentage of revenue calculations. Create summary sections for gross margin trends, operating expense ratios, and rolling EBITDA calculations. Use pivot tables on the imported data for flexible analysis.
Step 6. Set up monitoring and alerts.
Configure email alerts for when refresh completes, set up error notifications for connection issues, and create a dashboard sheet that references the live data. Export the configuration to reuse for other rolling reports like cash flow or balance sheet analysis.
Start your automated rolling P&L today
This solution eliminates the manual export process entirely while providing more flexibility than QBO’s native reporting. Your rolling 13-month P&L will automatically maintain exactly 13 months from today with zero intervention needed. Get started with your automated rolling P&L and transform your financial reporting workflow.
