Financial Extraction for an ITC

Owned by Michelle Drewes
Last updated: Jul 25, 2024 by Pat Zelei
6 min read

The checklist below may be used for ITCs whose data is included with their fiscal agent’s data and must extract their 025 fund's cash, expenditure, receipt and ITC's specific OPU data for the ITC's financial reporting.  (May also be used for ITCs who are their own COG who have other funds but need to extract just 025 fund).  The checklist will cover:

  • How to import the provided JSON files into USAS-R in order to extract the ITC’s 025 Cash, Expenditure, Revenue and OPU data from their respective grids into spreadsheets

  • Using provided EMISFFE spreadsheets to copy the required ODE columns and insert them into the newly created spreadsheets.

  • Adding/editing any columns in order to prepare the spreadsheets for EMISFFE import

  • Importing into EMISFFE and extract the QC data into a sequential file.  

1. Ensure June of the financial period you are reporting for is marked as 'Current' (NOTE: June does not need to be open, it only needs to be current).
2. Import the ‘Extract’ JSON files (provided below) into the Report Manager grid. 
3. Download the 'EMISFFE First 6 Columns' Excel spreadsheets (provided below) to your computer.
4. Generate and format ITC Cash Extract 
In USAS-R, generate the ITC Cash Extract spreadsheet for the 025 funds
Open the EMISFFE Cash First 6 Columns spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) of the ITC Cash Extract spreadsheet (copying down the data to match the number of cash rows)
Add the IRN in Column B (ensure the column remains in text format in order to include leading zeroes)
Edit the Fund Class in Column K to a one character code (Fund Class codes listed on page 3 of EMIS Cash Record chapter) 
Edit the ODE Brief Description in Column I if it exceeds 21 characters. 
Before saving the file in CSV format, remove Line 1 (header row).  It cannot be included when importing into EMISFFE. (example below)
5. Generate and format ITC Expenditure Extract
In USAS-R, generate the ITC Expenditure Extract spreadsheet for the 025 funds
Open the ‘EMISFFE Expenditure First 6 Columns’ spreadsheet and copy the first 6 columns (A-F) and insert them into the first 6 columns (A-F) of the ITC Expenditure Extract spreadsheet (copying down the data to match the number of expenditure rows)
Add the IRN in Column B (ensure the column remains in text format in order to include leading zeroes)
Add the General Fund Debt-Bond Retire fund to Column S (column after encumbrances). Enter a blank space into the cells in the column by pressing spacebar (and copy down all records)  
Before saving the file in CSV format, remove Line 1 (header row).  It cannot be included when importing into FFE. (example below)

  •  

  •  

  •  

 

EMISFFE Import Troubleshooting

  • CSV Invalid format error, this error will occur when the CSV layout does not match the Chapter 5 layout minus the filler, fiscal year and reporting period fields. 

  • Negative numbers, must be formatted with a minus ( - ) sign.  Negative values that are represented using parenthesis rather than a minus sign will cause an error during the import.

  • Please ensure leading zeroes are included in the account code dimensions before importing into EMISFFE (i.e. 025 fund code contains the leading ‘0’)