How to combine daily POS CSV files into a monthly sales report
Combine daily POS files by defining transaction and payment grains, mapping gross sales, discounts, refunds, tax, and tips separately, and retaining business date and source file. Reconcile each day to the POS close before calculating monthly sales, and flag any missing or duplicate export date.
From the field · Independent café owner
Build a café sales report from 30 daily exports
The POS produces one item file and one payment file per day. A refund posts the day after purchase, card tips settle separately, and one file is downloaded twice after a laptop restart.
Columns used in the example
transaction_id · business_date · item · quantity · gross_sales · discount · refund · tax · tip · tender
Follow it with your own file
- Create an expected-date list and identify missing or duplicate source files.
- Map item and payment exports at their own grains.
- Calculate net item sales separately from tax, tip, and payment settlement.
- Reconcile each business day to the POS close and retain exception notes.
Check: Every open day must be present once, daily item and tender controls must reconcile to source reports under documented rules, and monthly totals must equal the sum of approved daily controls.
A useful mental model
- Separate item sales, discounts, refunds, tax, tips, tender, and net sales with documented signs.
- Compare exported business dates with the trading calendar and identify duplicate files.
- make consistent schemas and retain store, business_date, source_file, and transaction ID.
- Match accepted transactions to POS close totals before monthly aggregation.
Why the obvious method fails
A café can accidentally count tax and tips as revenue, append the same day twice, or miss a closed file after a system outage. The monthly total may still look plausible because daily volume varies.
Daily controls make monthly reporting trustworthy
Small businesses often discover a monthly discrepancy too late to trace it. A control by business date isolates missed files, reversed signs, and source changes while the operational context is still available.
Where this usually goes wrong
Do not equate card settlement with sales when timing, fees, chargebacks, or cash tender differ. Reconcile settlement separately from the POS revenue definition.
Patterns that need context
- The file count does not match open business days.
- Refunds are positive in some exports and negative in others.
- Gross receipts, net sales, tax, and bank deposits are treated as one metric.
A method you can follow
- Define POS measures: Separate item sales, discounts, refunds, tax, tips, tender, and net sales with documented signs.
- Control expected days: Compare exported business dates with the trading calendar and identify duplicate files.
- Map and append: make consistent schemas and retain store, business_date, source_file, and transaction ID.
- Reconcile daily: Match accepted transactions to POS close totals before monthly aggregation.
Choose the approach that matches the evidence and repetition
| Approach | When it works | What to verify |
|---|---|---|
| Manual daily copy | Few quiet trading days | Duplicate and missed files |
| Folder append | Stable POS exports | Schema and sign drift |
| Controlled POS workflow | Recurring management reporting | Daily reconciliation effort |
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Combine daily point-of-sale CSV files into a verified monthly report. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Data profiling · Natural Language analysis · Charts · Reusable workflows
What to verify before acting
- Expected days complete
- Duplicate files removed with evidence
- Measures and signs defined
- Daily closes reconciled
- Monthly total traced to days
A monthly café report that distinguishes revenue, refunds, tax, tips, and payments and can be refreshed from the next set of daily POS files.
Questions readers usually ask next
Should tips and tax count as sales revenue?
Usually they should remain separate, but use the business's accounting definition and reconcile each component explicitly.
How can I detect a missing POS file?
Compare imported business dates and source filenames with an expected trading calendar before calculating the month.