Daily Razorpay settlement reconciliation for finance

Every weekday at 9am, reconcile yesterday's Razorpay settlements against the payments they cover, append them to a running Google Sheets ledger, and post a clean morning summary to Slack for the finance team. Use a cron trigger on a weekday schedule.

First work out the reporting window: the previous calendar day from 00:00:00 to 23:59:59 in the business's local timezone, expressed as Unix epoch seconds. Use that same from and to window on every Razorpay call so the three data sets line up.

Pull the data from Razorpay. Use Fetch All Settlements for the window to get each settlement's id, UTR, amount, fees, tax and status. Use Fetch Settlement Recon Report for the same period to get the per-transaction breakdown behind each payout, which is where the individual payments, refunds and adjustments that make up each settlement live. Use Fetch All Payments for the same window to get the payments captured that day. Razorpay list endpoints return an envelope shaped {entity, count, items} and paginate with count (max 100) and skip, so page through until you have every record rather than stopping at the default first page.

Two formatting rules matter throughout. Razorpay returns every amount as an integer in paise, so divide by 100 and present rupees with two decimals for anything a person reads, in both the sheet and the Slack post. Timestamps are Unix epoch seconds, so format them as readable dates. Settlement ids are prefixed setl_ and payment ids pay_.

Append one row per settlement to the ledger spreadsheet using Google Sheets Append Values, with these columns in a stable order: settlement date, settlement id, UTR, gross amount, fees, tax, net amount, and the count of payments included in that settlement. Derive the payment count from the recon report breakdown. Always append rather than overwrite, and check the rows already in the sheet before writing so that a re-run on the same day does not duplicate a settlement.

Then post a single Slack message with Send a Message. Lead with the headline numbers: total settled in rupees, how many settlements landed, total fees paid, and total tax. Those numbers should be readable in the first two lines without opening a thread.

After the headline, itemise only the exceptions that deserve a human. Flag any settlement whose net does not equal gross minus fees and tax, and show the difference. Flag payments that were captured but are still unsettled well beyond the normal settlement window, defaulting to anything older than two business days, since a captured payment that never settles is the failure mode that costs real money. Flag refunds or adjustments in the recon report that quietly reduced a payout, naming the settlement they hit and the amount. For each exception give enough context to act: the settlement id or payment id, the amount in rupees, and one short line on why it was flagged.

If nothing is out of place, say so in one line rather than padding the message. Never list every settlement or every payment in Slack. The spreadsheet is the complete record and the Slack post is the exception report. If there were no settlements at all yesterday, still post a short note saying so, so an empty channel is never mistaken for a broken workflow.