General Info about the NT integration/process

• Northern Trust offers Fundriver to some of its customers via the customer's contract with Northern Trust. We do not have a direct contract with the customer; instead, our contract is with NT. 
• All of our invoices go to NT. The price charged by NT to a customer for access to Fundriver often is not the same as the price that Fundriver charges, so do not discuss contract prices with NT customers. NT charges an additional fee to most customers to cover NT's side of maintaining this integration. 
• If NT customers want to add a module, we can discuss the new module prices with them, but not the base subscription prices.

The NT integration allows a customer's investment activity to be pulled into Fundriver automatically, rather than requiring them to enter it. We have 2 ways this integration can work:

• Pool-level (including Account-level): The totals for the pool are pulled into the "Investment Activity" screen at the pool level, or at the manager level if they have one account per manager. Customers using this type of integration will not use the manager import tool for manager data. The investment data will be sent from NT to Fundriver via FTP, then pulled into the Investment Activity screen via a utility in Fundriver. The only data pulled in by the import are the 5 normal investment activity fields - Ending Market Value, Interest/Dividends, Realized Gains, Unrealized Gains, and Investment Fees. Also pulls in other manager fields - i.e., net cash flow - for managers? Pool-level is the normal process; this article is based around the pool-level process.
• Asset Level: A small number of Northern Trust customers have chosen to use the Investment Portfolio Module and enter all activity at the individual investment/asset level. For these customers, NT doesn't send a file via FTP; instead, the customer downloads a report - "LP/PE Rollforward" - from the NT reporting tool then uploads this file to Fundriver to populate asset-level activity. This process is covered more in the Northern Trust Manager Level Upload article. Parts of this article will be relevant for the asset-level process, but most of this article is focused on the pool-level process.

When we get a new Northern Trust customer, we modify a few steps of the standard implementation process. Detail on the modifications for each step is below.

Ongoing troubleshooting:

1. If the customer has questions on the investment activity pulled in from NT import, please contact NT.  Fundriver can only see what the customer sees, not any detail.
2. If there’s an issue with the file transmission, please contact Fundriver Support first.  Fundriver will elevate to NT if needed.
3. If the customer is in a time-sensitive state, they can manually input the investment activity from the statements or import the manager information via a Standard Manager Import file FD can provide.  Contact FD Support if assistance is needed.

Initial Emails to Customer

Applies to both pool-level and asset-level

The initial email to the customer is 99% the same as for all other customers; however, additional people are copied on it:

• John Sandrick (jjs7@ntrs.com) is our main NT contact for implementations; he's copied on all NT implementations. His work is focused around the data feed from NT to Fundriver.
• The customer's NT contacts are usually copied, too; this is usually 1-3 customer relationship managers; Aaron will provide contact info for any additional people that should be included. These contacts usually have a passive role in the implementation; their involvement is primarily for curiosity or to answer any customer questions related to their NT accounts.
• Greg Johnson (gjj@ntrs.com) may be copied at times; he's Aaron's main contact at NT.

You can use the FreshDesk canned response called "Onboarding - 1 Initial Email - First welcome email (Northern Trust)"

Kick Off Call

Applies to pool-level import; manager-level will not be the same

Prior to the Kick Off call, it's suggested to create a demo DB with the NT utility turned on in order to be able to show customers generally how the investment data import will work. We don't have a way to tie these to a data file, so we can't fully demonstrate the process, but can still give a general idea of how it works. See "DLT Load/Database Setup" section below to set up a demo DB with the NT import utility.

During the Kick Off call you'll want to confirm some additional information to be prepared for the Northern Trust setup. Make sure your discussion covers these questions:

1. Do they have multiple pools, and if so, are all the pools related to the Northern Trust accounts?

Some customers will have one main pool which is held at Northern Trust, as well as outside investments which aren't at Northern Trust; this is OK, just helpful to note.

2. For each pool, are all their pool assets part of their Northern Trust account(s)?

If they have assets that aren't at Northern Trust, then they'll need to add these other assets to the Northern Trust figures; for example, if they keep all their investments at NT but also have an endowment cash account, then the NT account will pull into Investment Activity and they'll need to manually update the Investment Activity figures to include the cash account.

3. Which account(s) at Northern Trust relate to each pool?

Most customers have multiple NT accounts. To set up the account-to-pool relationships, we'll need to know which accounts are part of each pool. John will need this information when programming the feed (he can set multiple accounts to consolidate into one pool listing for Fundriver). NOTE: one account cannot belong to two pools; if they have multiple pools in a single account, the automatic feed won't work properly.

4. Do they want to use the automated feed?

John will want to confirm by the end of the call if/what feed they'll need to use. The prior question will clarify which data needs to be in the feed. The usual assumption is that the customer will want to use the automated feed unless they've told us otherwise. So, it can be helpful to demonstrate for the customer how the feed will work and what data it will pull in.

Some customers will decide that they'd rather review their statements and enter the data themselves; if they decide this, then John won't need to set up a feed, and we won't need to do anything else for the NT setup. The customer will be a normal customer from that step forward.

If they do want to use the feed, then John will begin preparing it, and we'll proceed with the directions below. The feed programming for NT typically takes 6-8 weeks.

DLT Load/Database Setup

Once the database is created, there's a Admin Tool update and a SQL touch to complete.

Admin Tool Update

The NT import utility is turned on by following these steps:

1. Log in to the Admin Tool
2. Go to "Database Admin" then "Add/Edit Client"; select customer in top drop-down
3. Select the checkbox labeled "Northern Trust Import"
4. Save changes 

Checking this feature turns on the Utility in the Customer's database.

Behind-the-scenes information:

If the Northern Trust Import option is selected:

• It adds a record in the Web_Utilities table so that Utility shows in the interface.It also adds a record in the System Parameters table so the cs_NT_LoadConsolidation sp runs when they import Northern Trust records.Also adds the Pending Investment Activity report to their reports list.

If the Northern Trust Import option is turned off:

• It will delete all those records from the tables.

All this is done in a stored procedure called:  Web_NorthTrustImport_AddOn

SQL touch

1.  ALL FEEDS:  
cs_NorthernTrust_ClientMapping - SQL to update table to Import Filename to connect file to customer.

2.  NT CUSTOMERS RECONCILING AT THE MANAGER LEVEL:  
cs_NT_LoadConsolidation - default is pool level.  SQL to update stored proc to the manager level if customer is reconciling at the manager level.

3.  NT CUSTOMERS RECONCILING AT THE POOL LEVEL: 
SQL team to enter the customer’s NT account number in the CustAcctNum field in the InvestmentPool table.
You may need to check with John to get this information, especially if the customer has multiple accounts that are being consolidated into fewer pools.

Here is an example file from Northern Trust for Rutgers, a customer who has multiple accounts in one pool. Column A is NT's indicator for the consolidation (what SQL will enter in the InvestmentPool table); Column B is the actual account number (Customer would enter this in custody account # in Manager profile data on Investment Portfolio tab on the DLT. John would have been told to consolidate RTG01, RTG02, and 4465730; he would let us know that RTLTIP is the "account number" for the consolidation for the pool, and RTLTIP is what we would ask SQL to populate to the CustAcctNum field of the InvestmentPool table:

Before First Posting (or when NT's process is complete)

General Info about the NT integration/process

• Northern Trust offers Fundriver to some of its customers via the customer's contract with Northern Trust. We do not have a direct contract with the customer; instead, our contract is with NT. 
• All of our invoices go to NT. The price charged by NT to a customer for access to Fundriver often is not the same as the price that Fundriver charges, so do not discuss contract prices with NT customers. NT charges an additional fee to most customers to cover NT's side of maintaining this integration. 
• If NT customers want to add a module, we can discuss the new module prices with them, but not the base subscription prices.

 

The NT integration allows a customer's investment activity to be pulled into Fundriver automatically, rather than requiring them to enter it. We have 2 ways this integration can work:

• Pool-level (including Account-level): The totals for the pool are pulled into the "Investment Activity" screen at the pool level, or at the manager level if they have one account per manager. Customers using this type of integration will not use the manager import tool for manager data. The investment data will be sent from NT to Fundriver via FTP, then pulled into the Investment Activity screen via a utility in Fundriver. The only data pulled in by the import are the 5 normal investment activity fields - Ending Market Value, Interest/Dividends, Realized Gains, Unrealized Gains, and Investment Fees. Also pulls in other manager fields - i.e., net cash flow - for managers? Pool-level is the normal process; this article is based around the pool-level process.
• Asset Level: A small number of Northern Trust customers have chosen to use the Investment Portfolio Module and enter all activity at the individual investment/asset level. For these customers, NT doesn't send a file via FTP; instead, the customer downloads a report - "LP/PE Rollforward" - from the NT reporting tool then uploads this file to Fundriver to populate asset-level activity. This process is covered more in the Northern Trust Manager Level Upload article. Parts of this article will be relevant for the asset-level process, but most of this article is focused on the pool-level process.

 

When we get a new Northern Trust customer, we modify a few steps of the standard implementation process. Detail on the modifications for each step is below.

Ongoing troubleshooting:

1. If the customer has questions on the investment activity pulled in from NT import, please contact NT.  Fundriver can only see what the customer sees, not any detail.
2. If there’s an issue with the file transmission, please contact Fundriver Support first.  Fundriver will elevate to NT if needed.
3. If the customer is in a time-sensitive state, they can manually input the investment activity from the statements or import the manager information via a Standard Manager Import file FD can provide.  Contact FD Support if assistance is needed.

 

Initial Emails to Customer

Applies to both pool-level and asset-level

The initial email to the customer is 99% the same as for all other customers; however, additional people are copied on it:

• John Sandrick (jjs7@ntrs.com) is our main NT contact for implementations; he's copied on all NT implementations. His work is focused around the data feed from NT to Fundriver.
• The customer's NT contacts are usually copied, too; this is usually 1-3 customer relationship managers; Aaron will provide contact info for any additional people that should be included. These contacts usually have a passive role in the implementation; their involvement is primarily for curiosity or to answer any customer questions related to their NT accounts.
• Greg Johnson (gjj@ntrs.com) may be copied at times; he's Aaron's main contact at NT.

You can use the FreshDesk canned response called "Onboarding - 1 Initial Email - First welcome email (Northern Trust)"
 

Kick Off Call

Applies to pool-level import; manager-level will not be the same

Prior to the Kick Off call, it's suggested to create a demo DB with the NT utility turned on in order to be able to show customers generally how the investment data import will work. We don't have a way to tie these to a data file, so we can't fully demonstrate the process, but can still give a general idea of how it works. See "DLT Load/Database Setup" section below to set up a demo DB with the NT import utility.

During the Kick Off call you'll want to confirm some additional information to be prepared for the Northern Trust setup. Make sure your discussion covers these questions:

1. Do they have multiple pools, and if so, are all the pools related to the Northern Trust accounts?

Some customers will have one main pool which is held at Northern Trust, as well as outside investments which aren't at Northern Trust; this is OK, just helpful to note.

2. For each pool, are all their pool assets part of their Northern Trust account(s)?

If they have assets that aren't at Northern Trust, then they'll need to add these other assets to the Northern Trust figures; for example, if they keep all their investments at NT but also have an endowment cash account, then the NT account will pull into Investment Activity and they'll need to manually update the Investment Activity figures to include the cash account.

3. Which account(s) at Northern Trust relate to each pool?

Most customers have multiple NT accounts. To set up the account-to-pool relationships, we'll need to know which accounts are part of each pool. John will need this information when programming the feed (he can set multiple accounts to consolidate into one pool listing for Fundriver). NOTE: one account cannot belong to two pools; if they have multiple pools in a single account, the automatic feed won't work properly.

4. Do they want to use the automated feed?

John will want to confirm by the end of the call if/what feed they'll need to use. The prior question will clarify which data needs to be in the feed. The usual assumption is that the customer will want to use the automated feed unless they've told us otherwise. So, it can be helpful to demonstrate for the customer how the feed will work and what data it will pull in.

Some customers will decide that they'd rather review their statements and enter the data themselves; if they decide this, then John won't need to set up a feed, and we won't need to do anything else for the NT setup. The customer will be a normal customer from that step forward.

If they do want to use the feed, then John will begin preparing it, and we'll proceed with the directions below. The feed programming for NT typically takes 6-8 weeks.

 

DLT Load/Database Setup

Once the database is created, there's a Admin Tool update and a SQL touch to complete.

Admin Tool Update

The NT import utility is turned on by following these steps:

1. Log in to the Admin Tool
2. Go to "Database Admin" then "Add/Edit Client"; select customer in top drop-down
3. Select the checkbox labeled "Northern Trust Import"
4. Save changes 

Checking this feature turns on the Utility in the Customer's database.

Behind-the-scenes information:

If the Northern Trust Import option is selected:

• It adds a record in the Web_Utilities table so that Utility shows in the interface.It also adds a record in the System Parameters table so the cs_NT_LoadConsolidation sp runs when they import Northern Trust records.Also adds the Pending Investment Activity report to their reports list.

If the Northern Trust Import option is turned off:

• It will delete all those records from the tables.

All this is done in a stored procedure called:  Web_NorthTrustImport_AddOn

SQL touch

1.  ALL FEEDS:  
cs_NorthernTrust_ClientMapping - SQL to update table to Import Filename to connect file to customer.

2.  NT CUSTOMERS RECONCILING AT THE MANAGER LEVEL:  
cs_NT_LoadConsolidation - default is pool level.  SQL to update stored proc to the manager level if customer is reconciling at the manager level.

3.  NT CUSTOMERS RECONCILING AT THE POOL LEVEL: 
SQL team to enter the customer’s NT account number in the CustAcctNum field in the InvestmentPool table.
You may need to check with John to get this information, especially if the customer has multiple accounts that are being consolidated into fewer pools.

Here is an example file from Northern Trust for Rutgers, a customer who has multiple accounts in one pool. Column A is NT's indicator for the consolidation (what SQL will enter in the InvestmentPool table); Column B is the actual account number (Customer would enter this in custody account # in Manager profile data on Investment Portfolio tab on the DLT. John would have been told to consolidate RTG01, RTG02, and 4465730; he would let us know that RTLTIP is the "account number" for the consolidation for the pool, and RTLTIP is what we would ask SQL to populate to the CustAcctNum field of the InvestmentPool table:

Before First Posting (or when NT's process is complete)

Testing NT's file
After NT has completed the process of preparing for file transmissions, they send a test file to Fundriver.

a) NT typically notifies Don that file is coming through.  They provide customer name in the subject line of the email, but time frame of data isn’t provided.  

b) Don can confirm if transmission was successful.  Ask Don what time frame the data is for (what month data is it).

c) We then can log into the customer’s database and test to see if data is pulled in as long as the customer is in the correct period.  Reopen if data is from a previous period (make a screen shot of current data or run portfolio level report if reopening back to a closed period), zero out all activity if needed, and test the import under Activity/Utilities.  Verify data came through and matches what was out there previously.  If data is correct, we are all set.  If data is not what we expect, follow up with Marcia.  

Finalizing Setup

Once we have a successful transmission, NT and customer need to determine which business day going forward each transmission should take place.

a) Each month NT completes their accounting process and approval process by the agreed upon business day. Once approved, the transmission is sent to Fundriver (typically at midnight).

b) There is no email confirmation when files are sent from NT to FD.  

c) Customers typically know which business day they need to go in & run the utility and they just schedule it on their calendars.

d) Customers can then import the NT data via Activity/Utilities to pull the data into Fundriver on their predetermined day each period.
 

Notes for Communication with Northern Trust

Northern Trust may send us an email with subject like "Northern Trust Communication Survey - Fundriver (Customer Name)" - see ticket #201297 as example. They will ask us to confirm:

Northern Trust and Fundriver have an existing relationship using SSH FTP where we push the file to your server ftp2.fundriver.com using the Login ID: NTRS2018.

Would you like to setup the new data transmission using this existing transmission protocol?
The above information is correct. However, our older setup (which they have occasionally listed in error) was:

Northern Trust and Fundriver have an existing relationship using SSH FTP with PGP encryption where we push the file to your server ftp.fundriver.com using the login ID: NTRS2018
Note the red highlighted portions; these are WRONG. Their files should NOT be using PGP encryption, and should be using "ftp2.fundriver.com" (note the "2"), NOT "ftp.fundriver.com". Confirm that they request matches the 1st sample, then you can confirm this is correct and for them to proceed with the setup process.
Sometimes NTRS is more specific in their request; if you get a request to confirm that looks like this, it is also correct:

This connection is currently pointing to sftp://ftp2.fundriver.com:22/NTRS where we will deliver the file FMC296S_CSTDY_MV_CHG_&CYY&MDD&TIMER6.txt . Let me know if you would like any changes before sending.

The yellow highlight is the full connection address, which still is ftp2 like the correct example above. The additional information added (stfp:// and :22/NTRS) are both correct. The blue highlight is the CustAcctNum to be populated in the Investment Pool table (see "SQL touch" above); this should match what was previously provided or, if NTRS hadn't provided a CustAcctNum previously, then this can be populated at this time.

 

Note for Single Sign On customers: 

In the past Northern Trust customers had access to the NT "Passport Custody System" that the customer would log into.  From this login they had a link and SSO access to the Fundriver website.  This was all set up by Northern Trust  We were previously unaware of this option so some of the earlier Northern Trust customers have been set up using Fundriver's Single Sign On (Rutgers is an example of this). Per Steven, it does not matter which SSO option a customer choses. However, if they chose to go through the NT Passport Custody System, they have to reach out to Northern Trust directly and have them do the setup.

If the customer chooses to use Fundriver's SSO then we will set them up our standard way by providing them the link for them to use. Note, that we should try and encourage the customer to use Fundriver's SSO if able to keep thi
After NT has completed the process of preparing for file transmissions, they send a test file to Fundriver.

a) NT typically notifies Don that file is coming through.  They provide customer name in the subject line of the email, but time frame of data isn’t provided.  

b) Don can confirm if transmission was successful.  Ask Don what time frame the data is for (what month data is it).

c) We then can log into the customer’s database and test to see if data is pulled in as long as the customer is in the correct period.  Reopen if data is from a previous period (make a screen shot of current data or run portfolio level report if reopening back to a closed period), zero out all activity if needed, and test the import under Activity/Utilities.  Verify data came through and matches what was out there previously.  If data is correct, we are all set.  If data is not what we expect, follow up with Marcia.  

Finalizing Setup

Once we have a successful transmission, NT and customer need to determine which business day going forward each transmission should take place.

a) Each month NT completes their accounting process and approval process by the agreed upon business day. Once approved, the transmission is sent to Fundriver (typically at midnight).

b) There is no email confirmation when files are sent from NT to FD.  

c) Customers typically know which business day they need to go in & run the utility and they just schedule it on their calendars.

d) Customers can then import the NT data via Activity/Utilities to pull the data into Fundriver on their predetermined day each period.

Notes for Communication with Northern Trust

Northern Trust may send us an email with subject like "Northern Trust Communication Survey - Fundriver (Customer Name)" - see ticket #201297 as example. They will ask us to confirm:

Northern Trust and Fundriver have an existing relationship using SSH FTP where we push the file to your server ftp2.fundriver.com using the Login ID: NTRS2018.

Would you like to setup the new data transmission using this existing transmission protocol?

The above information is correct. However, our older setup (which they have occasionally listed in error) was:
 

Northern Trust and Fundriver have an existing relationship using SSH FTP with PGP encryption where we push the file to your server ftp.fundriver.com using the login ID: NTRS2018
Note the red highlighted portions; these are WRONG. Their files should NOT be using PGP encryption, and should be using "ftp2.fundriver.com" (note the "2"), NOT "ftp.fundriver.com". Confirm that they request matches the 1st sample, then you can confirm this is correct and for them to proceed with the setup process.
Sometimes NTRS is more specific in their request; if you get a request to confirm that looks like this, it is also correct:

This connection is currently pointing to sftp://ftp2.fundriver.com:22/NTRS where we will deliver the file FMC296S_CSTDY_MV_CHG_&CYY&MDD&TIMER6.txt . Let me know if you would like any changes before sending.

The yellow highlight is the full connection address, which still is ftp2 like the correct example above. The additional information added (stfp:// and :22/NTRS) are both correct. The blue highlight is the CustAcctNum to be populated in the Investment Pool table (see "SQL touch" above); this should match what was previously provided or, if NTRS hadn't provided a CustAcctNum previously, then this can be populated at this time.

 

Note for Single Sign On customers: 

In the past Northern Trust customers had access to the NT "Passport Custody System" that the customer would log into.  From this login they had a link and SSO access to the Fundriver website.  This was all set up by Northern Trust  We were previously unaware of this option so some of the earlier Northern Trust customers have been set up using Fundriver's Single Sign On (Rutgers is an example of this). Per Steven, it does not matter which SSO option a customer choses. However, if they chose to go through the NT Passport Custody System, they have to reach out to Northern Trust directly and have them do the setup.

If the customer chooses to use Fundriver's SSO then we will set them up our standard way by providing them the link for them to use. Note, that we should try and encourage the customer to use Fundriver's SSO if able to keep things simplified.