A Microsoft Excel spreadsheet can be added to the list of NLS reports under either the Contact or Loan sections. These documents are created using a copy of the Excel spreadsheet Excel Report Info and List of NLS Fields.xls and customizing Sheet1 to utilize the data NLS creates on Sheet2 at the time the individual report is run.

The Excel Report Info and List of NLS Fields.xls file can be downloaded from the Nortridge Software Community in the API, Scripting and Developer Questions category. The file is included in the Office Integration.zip file located in the Office Integration Files topic.

Excel reports can only be run individually and can be added to the Contact or Loan Correspondence for later review.

Step by Step

Step 1: Create the Spreadsheet

1. Make a copy of Excel Report Info and List of NLS Fields.xls and name it myreport.xls.
2. Open myreport.xls

Step 2: Make a Reference Page

1. Click on the tab NLSDATA.
2. To provide a handy reference, print this sheet (it is already formatted to print the row and column headers). This will give you a list of all the NLS fields that will be entered into Column B when the report is run.

Step 3: Add Your Customization

1. On Sheet1, enter your customizations. You may add a logo, create an invoice, or make any other type of Excel report that will pull data from the NLS database. When you want to grab NLS data, use the VLOOKUP function as follows:
   =VLOOKUP(“name in column A”,NLS,2,FALSE)
   

   Example

   To get the Loan Number into cell A1 on Sheet1, enter the function:
   =VLOOKUP(“loan_number”,NLS,2,FALSE)
   (This is equivalent to entering =NLSDATA!B122, but it is easier to debug).
   
   The parameter in quotes must be exactly as shown in Column A on the sheet NLSDATA. Column A is overwritten by NLS each time the report is generated, so you cannot change the names. NLS only writes in columns A and B on sheet NLSDATA. You can customize all other columns on the sheet NLSDATA and they will not be disturbed.
   There are currently 120 Contact fields to chose from and an additional 127 Loan fields. If the report is a contact report, only the contact information will be filled in by NLS. If the report is a loan report, all the fields will be filled in.
   You can also reference the data in NLSDATA directly by cell reference. VLOOKUP makes this easier. Refer to Excel Help for more information on VLOOKUP and Named Ranges.
2. Save the document. Be sure your cursor is in cell Sheet1!A1. This way, when you preview your report in NLS you will see the upper left corner of your report. It is a feature of Excel that all spreadsheets automatically open to the place where the cursor was last left.

Step 4: Install the Document into NLS

1. Click File > Setup.
2. Click System.
3. Click Report Manager.
4. Click Add Report.
5. Fill in the following fields:
   Type: Select CIF or Loan.
   Category: Enter an existing category or create a new one. For example, you might want all of your spreadsheets to be in a category called Spreadsheets.
   Format Title: Enter a title as you want it to appear in the Report tree (e.g. LOAN BALANCE SPREADSHEET LETTER).
   Description: Optional.
   Path: Use the picker. If this is a contact report, select the contact folder. If it is a loan report, select the loan folder.
   Format File: Click to locate and select your Excel file.
6. Click OK.
7. Click OK again to exit system setup.
8. Click Yes to restart NLS so that you can access your new Excel report.

Running an Excel Report

Excel reports are individual reports only. They are accessed from the Contact or Loan query screens. Click Individual Report and select the Excel report from the list of individual reports. Click Display Report to display the report. on the screen. From there, the report may be closed, printed, or saved to the Contact or Loan Correspondence History.