Converting a Delinquent Reporting File for Bulk Submission in AMN

This job aid will lead you through step-by- step instructions for converting an Excel file to a text format so you can perform a bulk upload to report delinquent loans to Fannie Mae using AMN. You will need to make several edits and rename the file to have a successful upload. Please work though all the steps shown here.

For more information on your Delinquency Reporting Responsibilities, see section F-1-22: Reporting a Delinquent Mortgage Loan via Fannie Mae’s Servicing Solutions System (02/12/2020) in the Servicing Guide.

1. The Excel spreadsheet should be formatted using the guidelines below:
   
   
   • You can download a sample file that shows the column order.
   • The order of the column should not be changed (as per the current design).
   • The first 4 columns are mandatory (Seller/Servicer ID, Loan Number, Status Code, Reason Code).
   • However, if no data is available for a column, enter blank spaces equal to the length of the field specified ( as shown in the Reporting All Your Delinquent Loans at One Time by Uploading a ASCII File job aid, sample file and in the image above) You must enter blank spaces for the system to treat the columns as empty.
   • Column B Status Code requires a 2 digit entry. For example, use "09" instead of "9".
   • Column C Reason Code requires a 3 digit entry. For example, use "002" instead of "02"
   • The total length of all of the columns should be 80 characters.
2. Once all the data is entered, save the document as shown below:
   1. On the main menu of the open document, select File then Save As.
   2. Browse to the location you will save the file.
   3. In the Save as type field, select CSV (Comma delimited) (*.csv).
      
      

Note: The naming convention could be a little different based on the Excel version being used.

3. Name and save the file.

   1. If  the .CSV formatting messages pops up, click Yes or OK.
      
      
      

   2. Close the file and on the Want to save your changes to the {file name} popup, click Don’t Save.
      
      .

    Note: The header row can also be deleted from the Excel file before it’s saved as .csv.

   4. Change the file type to a text file.

      1. Browse to the location where you saved the file.

      2. Right-click on the file name and select Rename.

      3. Change the file type to .txt by removing “csv” and adding “txt” at the end. The file will be renamed as shown below.
         
         

      5. Open and Edit the file
      1. Open the renamed file in Notepad or any other text file editor and format the file before you submit.
      2. Delete the rows at the top of the file that show the column headings and details.  See the highlighted area in the image below.
         
         
      3. Replace all commas (,) with a space by using the Find and Replace functionality (Press Ctrl + H).
         
         
      4. Click Replace All and save the file.
      5. Save the changes. The file is now ready to be uploaded.
         
         
      6. To verify that the file is in the correct format, highlight the text fields from left to right and confirm the highlight end at 81.

   Note: The Total length of each row should be 80 characters ( If the highlight end at 81, the length is 80 characters)

Refer to the Reporting all Your Delinquent Loans at One Time by Uploading a ASCII File job aid for additional information.