To check a month of sales against UPI collections, download the month's history from your merchant app or the bank statement for the account that receives the money, clean it into dates and amounts, total it by day, and put those totals next to your day-sheet figures. The differences are small and mostly predictable, and finding them is the point.
Where do I download the history?
The route differs by provider and bank, so look in your own merchant app or net-banking for a history, report or statement option and choose the date range for the month. Pick a CSV or Excel format if offered, since a PDF has to be converted first and may not convert cleanly. If you only have a PDF, copy it into Excel and check that the numbers have landed in the right columns. Do not use anyone else's help that needs your login details.
How do I clean the columns?
- Keep only the columns you need: date and time, amount, reference number, status, and the payer if shown.
- Remove header rows that repeat on each page, and any totals row at the bottom.
- Make dates real dates. If a column is left-aligned, it is probably text. Use Data, Text to Columns, or re-type one cell and see how it behaves.
- Make amounts real numbers. Remove commas, rupee signs and the letters Cr or Dr into a separate column.
- Keep successful credits only. Put failed, pending or reversed rows on a separate tab to look at later.
- If date and time are in one cell, add a helper column with =INT(A2) to get the date alone, and format it as a date.
How do I total by day?
Say the cleaned data runs from row 2 to row 800, with the date in B and the amount in D. List the dates of the month in column F starting at F2. In G2 enter =SUMIFS($D$2:$D$800,$B$2:$B$800,F2) and fill down. Next to it, in H, enter your day-sheet UPI figure for that date, and in I enter =G2-H2. A pivot table grouped by date does the same job if you prefer clicking to formulas.
To catch duplicate rows from a clumsy copy, add =COUNTIF($C$2:$C$800,C2)>1 against the reference column, and look at any row that says TRUE.
What differences should I expect?
| Difference | Usual reason | What to do |
|---|---|---|
| Sale on one day, credit the next | Settlement timing or a holiday | Compare by sale date if your history has it, or shift by a day |
| Credit lower than sales | A refund, reversal or any charge deducted | Find the refund or deduction line |
| Credit higher than sales | A personal transfer or an old payment received on the QR | Match the reference to a person |
| Same amount twice | A real second sale, or a duplicate row | Check the reference number |
| Day sheet higher | UPI sale written but paid in cash, or not credited yet | Check the app history for that bill |
Because different providers describe their history differently, use your own export to see which columns exist. If the export gives settlement date as well as transaction date, you can compare by either, and using both is best.
Is it worth keeping the export?
Yes. Save the cleaned file for the month with your day sheets. If a customer or your provider queries a payment later, you will find it in seconds. If you pay for the shop's own spending from a bank account or card, SpendixAI can take that account's statement as a PDF, CSV or Excel file and show the rows for you to review before anything is saved, which helps for the spending side. It does not replace the sales comparison above.
Month-end checklist
- Export covers the full month, first to last day.
- Dates and amounts are real dates and numbers.
- Failed and pending rows are separated.
- Daily totals compare with the day sheet, with every difference above a rupee or two explained.
- Month total of the export compared with the month total of your sheet.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

