Editing 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 [[Media:RTD Template.xlsx|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 [[Media:RTD - Reporting Technical Specifications (2018) Revised.pdf|RTD - Reporting Technical Specifications (2018) Revised]] for layout. Note - It must be submitted in pipe (|) delimited text (*.txt) or comma separated values (*.csv) format.

'''Note: only Monetary contributions are reported in RTD files.''' Do not include "In Kind" (Goods and Services) donations. 

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

= Extract Donations =

#Select &lt;Donations - Donation Reports&gt; to open the "Contributions Reports" page.
#From the list, click on &lt; Contribution Details for RTD Reporting&gt;. This will produce a listing of all Donations and Member Dues so far this year. (Note that the Party treats member dues as donations.)
#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).
#Open this file. It is the 'raw data' for the RTD Template.
#Scan for province other than Ontario. These should not be identified as Donation but as Other and for a maximum of $10. If the donot has made prior donations in the year, refund the excess. . 

'''Note''': If you are producing a file for the previous year click on &lt;Report Criteria&gt; 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 [[File:RTD Template.xlsx|RTD Template workbook]] or obtain a copy from the previous Treasurer.

#Open the RTD Template.
#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.
#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.
#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.&nbsp; If you want to do it manually, you can use excel's sort function and use&nbsp;'''Donor Name'''&nbsp;as the primary sort key and&nbsp;'''Received Date'''&nbsp;as the secondary key.&nbsp;[[File:Sort-by.png|thumb|center|sort keys]]
#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.
#Select all the rows with actual donation data and all columns but the last one ('''Donor Name''') (&lt;Ctrl-C&gt;).
#Open a new work book (&lt;Ctrl-N&gt;).
#Paste Values and Formatting (&lt;Ctrl-Alt-V A&gt;) to the new sheet.
#Sort the sheet by '''Aggregate Amount''' and delete all rows where '''Aggregate Amount''' is less than or equal to $100.00.
#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.)
#Delete the Comment from cell A1.
#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 file).

= Send to Elections Ontario =

#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.)
#Print a copy of the email.
#Format the RTD file for printing on the same page as the email.
##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.)
##In the Page Setup dialogue (Alt-P-SP), set the Top margin to 6.3" and the Header to 6.0".
##In the Header/Footer tab, set the Header to the File Name.
#In the Print Preview, confirm that the file will print on the lower half of a single page.
#Print the file on the bottom half of the email printout.
#File the page in the donations folder, for reference when producing the next RTD file.