The following instructions and workbooks help with reconciling card and ACH payments in CivicPlus Payments and Recreation Management. You can choose from a Gross or Net reconciliation workbook option.
Important Notes
This tool only works if you are using CivicPlus Payments in Recreation Management.
This tool will not reconcile refunds from before December 16, 2025 due to a technical issue with the Transaction ID displaying.
This tool is intended to help with reconciliation for Card and ACH payments only. Including Cash/Check Items will cause mismatches.
You must "enable content" for this tool to function. This workbook does not use any external references.
Utilizing additional data or tabs in the workbooks other than what is specified below is allowed, however, it is not supported by CivicPlus.
Reconciliation Workbooks
Instructions
In the Payments Portal, navigate to the Gateway Configurations tab
Click the three dots action menu on your configuration, and select Reports from the menu that pops up
Select the Deposit Report, and then click on the Export icon
Set your Date Range as appropriate and click Export
Note:
You may export a maximum of 30 days at a time. You may copy multiple exports into the reconciliation workbook table. Just ensure the table encompasses all transactions.
Select the Payments Report, and then click on the Export icon
Set your Date Range as appropriate and click Export
Note:
This information can be determined by the First and Last dates for a Card transaction. The Payments report exports in UTC so we recommend adding one day to your end date to get all the in-scope transactions.
Copy and paste your Deposit Report's data into the Deposits Report tab of the Reconciliation Workbook (will say either Gross or Net, depending on the option selected)
Note:
It is recommended that you do not include the header line, and paste your information into A2.
Copy and paste your Payments Report's data into the Payments Report tab of the Reconciliation Workbook
Note:
It is recommended that you do not include the header line, and paste your information into A2.
In Recreation Management, click Reports, select Transaction Reports, and select the Transaction Detail Report
Ensure your Transaction Report is configured for the appropriate Date and Time Ranges, and that the following filters and fields are added:
Date: Remember to include the hours of the day before that were after the cutoff time.
Filters: On the Payment Types section, select Credit/Debit
Transaction Fields: Select Receipt #, Transaction Date/Time, CC Last Four, CC Type, Merchant Code, Payment Gateway, Receipt Location, Transaction Detail GL Code, Transaction Detail GL Description, Payment Types (Will show as Credit/Debit)
Payer Fields: Select User Full Name
After verifying that you have the correct settings, Export the Transaction Report as normal
Note:
If you save this report the fields will stay and you will only need to verify the date and time.
Copy and paste your report's data into the GL Recon Report tab of the Reconciliation Workbook
Note:
It is recommended that you do not include the header line or the bottom totals, and paste your information into A2.
In the Customer Configuration tab of the spreadsheet, enter the First and Last deposit dates, and select your Time Zone
Note:
The first and last date of the deposits that are being audited allows the workbook to define scope. Time Zone allows the sheet to convert UTC time to your local time.
In the Excel ribbon toolbar, click on Data, and in the Queries & Connections section, click Refresh All
Note:
If you add or remove data in the Deposits Raw Data or Recreation Management Transaction Report tabs after this step, you will need to perform this step again.
Click the Deposits Aggregator tab to review the totals for each deposit
Note:
This will show you the Total Amount Processed, the Total Fees Assessed (If on Net), and Total Deposited for each deposit.
Review the additional tabs of the workbook:
Notes:
These tabs are intended to show you the transactions that need further review at the Transaction Level.
The Carry Overs tab is designed to have the same format as the Transaction Detail Report. It is recommended that you Copy/Paste from C2 to the end of the table data, and paste that data into your next reconciliation. This is intended to assist in reconciliation.
If you paste in the cell directly underneath the last cell in the GL Recon Report, the table should extend to include these automatically.
The Carry Overs tab will provide any transactions that are expected to be deposited in a future deposit (Often ACH).
The Reconciler tab will allow you to see all transactions (Reconciled or Not)
The Items to Review tab will provide a list of transactions that should be reviewed. (These will also be listed in the Reconciler tab.)
The Chargebacks & ACH Returns tab is informational, to ensure that these have been seen.
The Billing Data (Fees) tab will provide a breakdown of all fees that were charged if a Billing file is present. (This tab is only available on the Gross Reconciliation workbook.)
Perform further research to address any unreconciled transactions listed and re-run any reports needed
Review the additional tabs:
The GL Pivot Magic tab will show every transaction summarized by GL Code and Description. It will also indicate how much and what has not been reconciled.
The Reconciliation by GL tab provides this same data in an itemized format.