Posts

Margill Loan Manager – Update of Borrower’s data with Global Changes and an Excel sheet

/in /by

Margill Loan Manager – Update of Borrower’s data with Global Changes and an Excel sheet

Question: I must make sure that all my loans include a name, address, city, province (or state), postal code (or Zip) and country for the Borrowers in a specific format.  How can I do that in Margill?

Answer: This operation can be done quickly with Global Changes via an Excel sheet.

 Steps:

  • Create a personalized template in Reports > Record List with all the data that needs to be verified and updated, making sure to include the Unique ID for the Borrower in the first column:

2) Create the report for all Records (you can exclude the archived or closed loans – Use Advanced Queries to include the desired loans in order to reduce the size of the report if you have thousands of loans.). Produce the report.

Export data to Excel:

3) Clean up the Excel sheet.

  • Lines 5 and 6 do not have Borrowers. Either add a Borrower or eliminate the lines.
  • Duplicated lines (we can easily see them in column A) can all be eliminated – these people have many loans – we only want Borrowers, not loans.
  • Here is the result after the clean-up including the State and Country that now have 2 standard letters:

In my situation, I only need to update the State/Province, the Country and the Postal Code. I therefore eliminate everything that should not be updated in my Excel sheet while absolutely keeping the Borrower Unique Identifier which is the “key”, allowing me to link my Excel sheet to the correct Borrower.

You would have tens, hundreds or thousands of Borrowers for real:

4) Update the columns one by one via Global Changes for Borrowers:

In the Main Margill window, go to Borrower under File > right click on the mouse > Global Changes:

Click on the Excel icon. The ? gives you additional instructions about the simple Excel sheet required.

Column A is the Borrower Unique Identifier (not the loan) and Column B is the data to be imported or updated. In the first import, we want to change the State/Province and therefore in the right menu, choose “Borrower State, Province”. Then choose the Excel file by clicking on the orange file icon.

Note that for Borrower 10001, no update is required since the data is unchanged (the “Submit” column is therefore not checked).

Click on “Save” and the data will be updated.

Afterwards, we want to update the Country.  In Excel, copy the Country data into column B, save the sheet, then import (I simply deleted the State/Province column but it is wise not to destroy the columns or make a copy of the Excel sheet before deleting data). Do the same for the Postal Code/ZIP and other data as needed, one by one.

Note that the “Automated Imports” (API) would allow you to update all this data in one operation. There is also a Salesorce API available (not covered here):

Mass data entry / Global database changes / Adding new data in the database in bulk / Mass database changes in Margill Loan Manager

/in /by

Q:  We have added some custom fields for additional loan information.  Is there a way we can mass import only those specific custom fields in Loan Manager?

A: Yes you can mass import data into Margill Loan Manager. This is with what we call “Global changes”.

This can be done for the loan, mortgage, line of credit, lease, etc. (the Record) or for the Borrower.

For adding new information or changing data in many Records at once, sort these in the Main window, choose the desired Records, highlight these and right click with the mouse. Choose Global changes:

This window will appear showing the various fields that can be changed.

There are over 30 fields that can be changed plus all Custom fields.

Select the field you wish to add data to (or change data) and press on Refresh. You can only add data to one field at a time.

Your can then highlight the Records and with the right mouse click add/change the data in bulk. Case being, you will see existing data and can replace these or not. Use the Ctrl or Shift key and mouse to pick and choose the desired lines.

Below is the option when a scroll menu exists for the Custom field. If the field was a Text field for example, you would simply enter any text (no menu).

If the data is never the same, for example, adding the date of birth for Borrowers, you can add the data line by line.

Once the data is entered or changed, press on Save (bottom right). The changes will be made.

Adding data via spreadsheet (Sorry not yet… but coming soon):

  • In version 5.0.x coming up in a few weeks, you will be able to make these Global changes with a spreadsheet (Excel). All you will need are two columns (a loan Identifier – our “MLM Record ID” or one of the two “Unique Identifiers”. “File”, “File Number” and “Accounting ID” are not allowed since these may not be unique identifiers). It is strongly recommended to start using the Unique Identifiers offering much more versatility.

This is not the same as adding a new loan or Borrower in the database – this can be done through Tools, Settings, Special and:

See http://www.margill.com/en/mass-importing-existing-loans-and-borrowers-in-margill-loan-manager/

You can also use the Global changes for these practical changes:

  • Change Active Records to Closed after your fiscal year end
  • Activate Automatic fees
  • Enable or disable the sending of email reminders to your Borrowers
  • Activate the Electronic Funds Transfer for a bunch of Records at once
  • Add banking data to your Borrowers
  • Make corrections in bulk
  • Add Metro 2 credit reporting compulsory data to the loans and Borrowers
  • Update and change most Borrower data and their Custom fields