Real Time Disclosure

Introduction

The process is substantially automated with an RTD Donations Report in CRM that extracts all donation records for the year (or campaign period) and an RTD Template workbook that takes this data and accumulates the donations by donor. Elections Ontario issues specification for the format of the file required to be submitted. The format was modified in 2017 and 2018, which required revisions to both the CRM extract procedure and the RTD Template spreadsheet.

See RTD - Reporting Technical Specifications for layout. Note - It must be submitted in pipe (|) delimited text (*.txt) or comma separated values (*.csv) format.

Note: only Monetary contributions to the Party and Leadership Candidates are reported in RTD files. Do not include "In Kind" (Goods and Services) donations, or donations to Constituency Associations. (Need to confirm donations to Leadership Contestants are rerported in the same or separate files.)^.

The following instructions assume a working knowledge of Microsoft Excel or compatible spreadsheet application.

Extract Donations

1. Click (here) to open the "Contribution Details for RTD Reporting" page. This will produce a listing of all Donations and Member Dues so far this year. (Note that the Party treats member dues as donations.)
2. Click on [Export to CSV]. This will create a new file in your Downloads folder, with a name like "Report_20180126-1808" (produced 6:08 PM on January 26, 2018).
3. Open this file. It is the 'raw data' for the RTD Template.
4. Scan for province other than Ontario. These should not be identified as Donation but as Other and for a maximum of $20. If the donor has made prior donations in the year, refund the excess.

Note: If you are producing a file for the previous year click on <Report Criteria> scroll down to "Set Filters" and look for "Receive Date." Change it to "Previous Year." (Do not click on [Update Report]).

Create RTD File

Download the Template workbook or obtain a copy from the previous Treasurer.

1. Open the RTD Template.
2. Select the RawData sheet. If this is the first time you are creating an RTD file for the the current year, delete all of the data in this sheet.
3. Copy all of the data from the "Report_20..." file generated above to the RawData sheet in the RTD Template. Scroll to the last record to confirm all rows have been copied.
4. The RawData is sorted in CivicCRM by Donor Name and Receive Date (Check Report Criteria to verify). For the excel RTD template spreadsheet to work properly, it needs the raw data in that order.  If you want to do it manually, you can use Excel's sort function and use Donor Name as the primary sort key and Received Date as the secondary key. 

   

   sort keys

5. Select the RTD sheet. Note it will have over 400 rows, many with mostly zeroes (0). Confirm that the last record in the RawData sheet is included in the RTD sheet. If not, copy the last row enough times to show all donations.
6. Select all the rows with actual donation data (<Ctrl-C>).
7. Open a new work book (<Ctrl-N>).
8. Paste Values and Formatting (<Ctrl-Alt-V A>) to the new sheet. (This copies the values instead of the formulas.)
9. Sort by the "Last" column and deleted all rows with ")" (zero). These are the donations from donors who made aditional donations at a later date.
10. Sort the sheet by Aggregate Amount and delete all rows where Aggregate Amount is less than or equal to $200.00.
11. Sort by Acceptance (Deposit) Date. If this is not the first RTD file to be created in the current year, delete all records reported in previous RTD files. (Refer to the print out of the previous RTD file.)
12. Delete the Comments from the first row.
13. Delete the last two columns (Sort name and Last).
14. Save this workbook as a CSV file using the naming convention specified by Elections Ontario (currently YYYY_RTD_15_MMDDYYYYhhmm) (as shown in the Comment in the RTD Template fil

Send to Elections Ontario

1. Send the RTD file to Elections Ontario Finance Reporting (efincontributions@elections.on.ca). (If any of the records are more than 10 business days old, provide an explanation.)
2. Print a copy of the email.
3. Format the RTD file for printing on the same page as the email.
   1. Wrap (Alt-H-W) the Header line and adjust the columns widths so the headers fit on two lines. (Start by making all columns 13.00.)
   2. In the Page Setup dialogue (Alt-P-SP), set the Top margin to 6.3" and the Header to 6.0".
   3. In the Header/Footer tab, set the Header to the File Name.
4. In the Print Preview, confirm that the file will print on the lower half of a single page.
5. Print the file on the bottom half of the email printout.
6. File the page in the donations folder, for reference when producing the next RTD file.