If your firm custodies at Fidelity, two Wealthscape reports give SurgeTK the data it needs for the Beneficiary and Guardrails Value Adds. This article walks through both exports, from the Wealthscape menu to the finished SurgeTK import.
You want | Fidelity report | SurgeTK import | Time |
The Beneficiary report | Beneficiaries | Beneficiaries | About 7 minutes |
Guardrails with the client's distributions | Systematic Withdrawal Plan | Guardrails | About 12 minutes |
Do the Beneficiaries export first. It is shorter. The Guardrails export repeats the same Wealthscape steps and adds one Excel formula.
At a glance
Both exports start in Wealthscape: Menu › Intelligence, Reports & Alerts › Reports.
You edit each report once, add one filter, and click Save As to keep a personal copy. Next time you open the copy.
Export with the Excel icon in the report header, then download the file from Recent Reports.
Every Fidelity file has five title rows at the top and a block of disclaimer rows at the bottom. Delete both before you import.
The Systematic Withdrawal Plan file needs one extra column, Frequency, filled by a formula. The formula is in this article.
The accounts must already exist in SurgeTK with the same account numbers. See Before you start.
Before you start
A Wealthscape login. Wealthscape is Fidelity's advisor platform. Your firm may call it WealthCentral. The address is wealthcentral.advisorchannel.com.
Your accounts are already in SurgeTK. Both imports match each row to an account by Account Number. The match is exact, including dashes and spaces. Before you import, open one Fidelity account in SurgeTK and compare its account number to the same account in the file. If the formats differ, the import cannot match them.
Excel for Microsoft 365, or Excel 2021 or newer. The Guardrails formula uses LET, FILTER, SEQUENCE and UNIQUE. Older versions of Excel show #NAME? instead of a result. If that happens, type the frequency into each row by hand.
Download the same day you export. The Recent Reports list in Wealthscape resets every night.
Allow pop-ups for wealthcentral.advisorchannel.com. Reports opens in a new browser window.
Part 1: Beneficiaries
Step 1: Open Reports in Wealthscape
Log in to Wealthscape.
Click Menu at the top of the page.
Scroll down about two thirds of the page to Intelligence, Reports & Alerts.
Under Reports, click Reports.
A new window opens. It lists every report you have access to, about 200. Your count depends on your permissions.
Step 2: Open the Beneficiaries report
In the Report Name box, type
bene.Click Beneficiaries. Its Subject Area is Beneficiaries.
The report runs and lists every account that has a beneficiary on file, open and closed.
Tip: the second result, Beneficiaries not on File, lists accounts with no beneficiary. Run it once a quarter to find gaps.
Step 3: Filter to open accounts and save your copy
Closed accounts do not need to come into SurgeTK. Filter them out once, then save the filtered report so you never do this again.
Click Edit Report at the top right of the report.
Under Define Criteria, scroll down to Open/Closed Indicator.
Set its Conditions box to Includes.
Open the Criteria Value list and tick Y-Open only. Leave N-Closed unticked.
Leave Manage Display (the column list) as it is. The columns do not need to change.
Scroll to the bottom of the page and click Save As.
Name the report
Beneficiaries OPENand click Save.You are back on the edit screen. Scroll down and click View.
The report opens again with the name Beneficiaries OPEN and shows open accounts only. From now on it is listed under All Reports with the Type Personal.
Step 4: Export to Excel and download the file
In the report header, to the right of Run Date and Data as of, click the Excel icon. It is the green X icon next to the CSV icon.
A message reads "This report is being prepared and may be downloaded from the Recent Reports page." Click View Recent Reports. You can also reach this page with the View Recent Reports button at the top of the Reports window.
Find Beneficiaries OPEN in the list. If its Export Status is not yet Completed, click Refresh. It usually takes a few seconds.
Click Download. The file saves to your Downloads folder.
Note: the Recent Reports list resets every night. Download the file the same day.
Step 5: Clean the file in Excel
Every Fidelity export has rows that SurgeTK cannot read. Delete them.
Open the downloaded file in Excel.
Delete rows 1 to 5. They hold the report name, the run date, the data-as-of date and two blank rows. Click row number 1, shift-click row number 5, right-click the selection and choose Delete. Do not use Cut or Clear Contents. The rows must disappear so that the column headers move up to row 1.
Delete the disclaimer rows at the bottom. Scroll past the last account row. Every row from "For investment professional use only" down to the last line is a disclaimer. Click the first disclaimer row number, shift-click the last row number, right-click and choose Delete.
Check the result. Row 1 is the header row. Every other row is one beneficiary on one account. Nothing else.
Save the file. Keep the .xlsx format.
The columns in the Fidelity file are: Account #, Primary Account Holder, FBSI Short Name, Custom Short Name, Reg Type, Reg Type Code, Beneficiary Name, Beneficiary Relationship, Beneficiary Type, Beneficiary Status, Beneficiary Percentage, Beneficiary DOB and Beneficiary Count. Do not rename or move them. You map them in the next step.
Step 6: Import into SurgeTK
In SurgeTK, go to Imports and click Import Data. You can also start from Households › Add Household › Import Data.
On Pick Your Data Type, click Import Account Data.
On Choose Your Account Import Type, click Beneficiaries, then Next.
The Prepare Your Spreadsheet screen lists the fields. Click Next.
Upload your cleaned file and click Next. The As-of Date on this screen is not used by the Beneficiaries import.
Map the columns as in the table below.
Click Import. Do not refresh the page until the progress panel finishes.
Go to Imports to open the import report. It lists every created, updated and failed row with the reason.
SurgeTK field | Fidelity column | What to know |
Client ID | Leave blank | Not used by this import. |
Account Number | Account # | Required. Must match the account number in SurgeTK exactly. |
Beneficiary Name (Last, First) | Beneficiary Name | Required. Fidelity writes names as FIRST LAST with no comma. SurgeTK reads the first word as the first name and the rest as the last name. That works for people. Check trusts and charities with long names after the import. |
Beneficiary Type (Primary / Contingent) | Beneficiary Type | Required. Fidelity uses Primary and Secondary. Secondary imports as Contingent. |
Beneficiary Allocation (%) | Beneficiary Percentage | Required. Fidelity writes 100.0000 or 50.0000. Both are read correctly. |
Beneficiary Relationship | Beneficiary Relationship | Optional. Spouse or Non Spouse, as Fidelity writes it. |
Per Stirpes (Yes / No) | Leave blank | The Fidelity file has no per stirpes column. |
Date of Birth | Beneficiary DOB | Optional. Blank cells are skipped. |
What the import does
Creates new beneficiaries and updates the allocation of beneficiaries already on the account.
Never removes a beneficiary that is on the account but not in your file. Remove those on the account itself.
A row whose Account # matches nothing in SurgeTK is saved as an unlinked beneficiary. The Households page then shows a Fix Beneficiaries button. Link or delete those before you import the same file again.
Marks the Beneficiary report of each affected household for refresh.
The full rules are in Importing Beneficiaries.
Check your work. Open a household, go to Value Adds, open Beneficiary and click Refresh. The primary and contingent beneficiaries from Fidelity are on the report.
Part 2: Guardrails (systematic withdrawals)
The Guardrails report needs two things on every account: the current value and the recurring distribution. Fidelity's Systematic Withdrawal Plan report has both. Fidelity does not give a frequency, though. It marks which months a plan pays, with Yes or No in twelve month columns. You add a Frequency column and let a formula read the months. That is the only extra work.
Step 1: Open Reports in Wealthscape
Same as Part 1, Step 1: Menu › Intelligence, Reports & Alerts › Reports.
Step 2: Open the Systematic Withdrawal Plan report
In the Report Name box, type
sys.Click Systematic Withdrawal Plan. Its Subject Area is Plan Instructions.
If you already see Systematic Withdrawal Plan ACTIVE in the results, that is your saved copy from an earlier visit. Open it and go to Step 4.
The report lists every plan, including plans that are closed, ended, deleted or on hold. SurgeTK must not get those.
Step 3: Filter to active plans and save your copy
Click Edit Report at the top right of the report.
Under Define Criteria, scroll down to SWP Status. SWP stands for Systematic Withdrawal Plan.
Set its Conditions box to Includes.
Open the Criteria Value list and tick A-Active only. Leave C-Closed, D-Deleted, E-Ended and H-On Hold unticked.
Leave Manage Display (the column list) as it is.
Scroll to the bottom of the page and click Save As.
Name the report
Systematic Withdrawal Plan ACTIVEand click Save.Scroll down and click View.
The report opens again as Systematic Withdrawal Plan ACTIVE and lists active plans only.
Step 4: Export to Excel and download the file
Same as Part 1, Step 4.
Click the Excel icon in the report header.
Click View Recent Reports.
Click Refresh until Export Status reads Completed.
Click Download.
Step 5: Clean the file in Excel
First, write down the date in row 3. It reads "Data as of" and a date. You enter that date as the As-of Date when you import into SurgeTK.
Delete rows 1 to 5, the same way as in Part 1. Click row number 1, shift-click row number 5, right-click, Delete.
Delete the disclaimer rows at the bottom. In the example they start at row 120, after the last plan. Yours start wherever your plans end. Select every row from "For investment professional use only" to the end, right-click, Delete.
Check that row 1 is the header row and every other row is one plan.
Step 6: Add the Frequency column
Scroll to the right until you see the month columns, January to December. Each cell reads Yes or No. In the example January is column AM.
Click the column letter above January to select the whole column.
Right-click the selected column and choose Insert. A blank column appears to the left of January.
Click the first cell of the new column (row 1) and type
Frequency.Click the cell below it, in row 2.
Click in the formula bar at the top of Excel, paste the formula below, and press Enter. The cell now reads Monthly, Quarterly, Semi-Annual, Annual, None or Custom.
Click that cell again. Grab the small square at its bottom right corner (the fill handle) and drag down to the last plan. Or double-click the square to fill the whole column at once.
=LET(r,AN2:AY2,n,COUNTIF(r,"Yes"),pos,IFERROR(FILTER(SEQUENCE(12),r="Yes"),""),IF(n=0,"None",IF(n=12,"Monthly",IF(n=4,IF(COUNTA(UNIQUE(MOD(pos-1,3)))=1,"Quarterly","Custom"),IF(n=2,IF(MOD(INDEX(pos,2)-INDEX(pos,1),12)=6,"Semi-Annual","Custom"),IF(n=1,"Annual","Custom"))))))
What the formula does. It counts the Yes cells in the twelve month columns of that row. Twelve is Monthly. Four, three months apart, is Quarterly. Two, six months apart, is Semi-Annual. One is Annual. None means no month is marked. Any other pattern is Custom.
If the months are not in columns AN to AY. The formula expects January in column AN and December in column AY. That is where they sit after you insert the Frequency column at AM, as in the example. If your months sit elsewhere, change AN2:AY2 to your first and last month columns on row 2, then fill down again.
Step 7: Fix the Custom and None rows
SurgeTK accepts Monthly, Quarterly, Semi-Annual and Annual. A row that reads Custom or None does not import its withdrawal. SurgeTK still updates that account's value, but it writes no distribution, and the report shows no message. So fix those rows now.
Custom. The plan pays on a pattern SurgeTK cannot store, for example every other month. Type the closest frequency over the formula, or leave the row and add the withdrawal by hand in SurgeTK after the import: open the account, go to the Withdrawals tab and click Edit.
None. No month is marked, so the plan pays nothing this year. Leave it. The row changes nothing.
Two plans on one account. The file may have two rows with the same Account #, one per plan. That is fine. SurgeTK stores both as separate withdrawals. Two rows with the same amount and the same frequency are stored once.
Then Save the file in Excel and close it. Saving in Excel stores the formula results in the file. SurgeTK reads those results.
Step 8: Import into SurgeTK
In SurgeTK, go to Imports and click Import Data.
On Pick Your Data Type, click Import Account Data.
On Choose Your Account Import Type, click Guardrails, then Next.
The Prepare Your Spreadsheet screen lists the fields. Click Next.
Upload your file. Set the As-of Date to the "Data as of" date you wrote down in Step 5. Click Next.
Map the columns as in the table below.
Click Import. Do not refresh the page until the progress panel finishes.
Go to Imports to open the import report.
SurgeTK field | Fidelity column | What to know |
Client ID | Leave blank | Your accounts already exist. Account Number alone updates them. |
Account Number | Account # | Required. Must match the account number in SurgeTK exactly. |
Account Value | Total Account Value | Optional, but map it. It updates the account balance as of the As-of Date. |
Systematic Withdraw Amount | The dollar amount of each payment | The Fidelity column that holds the amount the plan pays each time. A number. |
Systematic Withdraw Frequency | Frequency | The column you added in Step 6. |
What the import does
A row with an amount and a recognized frequency replaces that account's list of systematic withdrawals with the rows in the file. Withdrawals that came from a Zoho sync are kept.
Several rows with the same Account # become several withdrawals on that account.
A row with Custom, None or a blank frequency leaves the account's withdrawals unchanged.
An Account # that matches nothing in SurgeTK creates a new, unlinked account. The Households page then shows a Fix Accounts button. If you see many of those, the account numbers in the file do not match SurgeTK. Use Undo on the Imports page, fix the numbers, and import again.
Marks the Guardrails and Buckets reports of each affected household for refresh.
The full rules are in Importing Account Data. The Guardrails import uses the same engine with a shorter template.
Check your work. Open a household, go to Value Adds, open Guardrails and click Refresh. The Current Distribution box and the Monthly Income row now show the client's systematic distributions. If Monthly Income is $0, read Where Monthly Income Comes From in Guardrails and Buckets.
Next time
You do not repeat the filter steps. Your two saved reports stay in Wealthscape.
Menu › Intelligence, Reports & Alerts › Reports.
Type
beneorsysand open Beneficiaries OPEN or Systematic Withdrawal Plan ACTIVE. Both are listed with the Type Personal.Click the Excel icon, open Recent Reports, click Download.
Delete the top rows and the disclaimer rows.
For the Systematic Withdrawal Plan file, insert the Frequency column and fill the formula down.
Import into SurgeTK.
Importing the same data again is safe. The Beneficiaries import updates allocations and adds nothing twice. The Guardrails import replaces each account's withdrawal list with the current file.
Troubleshooting
The report is not in the search results.
Your Wealthscape permissions do not include it. Ask your firm's Wealthscape administrator or Fidelity.
Recent Reports is empty.
The list resets every night. Click the Excel icon on the report again and download the new file.
The import says "Missing required mapping(s)".
A required field has no column. Map Account Number, Beneficiary Name, Beneficiary Type and Beneficiary Allocation for Beneficiaries. Map Account Number for Guardrails.
Beneficiary rows say "No matching account".
The Account # in the file does not match the account number in SurgeTK. Compare one account. Dashes and spaces count. Those rows are saved as unlinked beneficiaries. Use Fix Beneficiaries on the Households page to link each one, or delete them and import again after you fix the numbers.
New unlinked accounts appeared after the Guardrails import.
Same cause. The account numbers did not match. Click Undo on the Imports page, fix the account numbers, and import again.
Monthly Income on the Guardrails report is $0.
The withdrawal's frequency is blank, because the row read Custom or None, or the account is excluded from the report. Open the account, go to the Withdrawals tab and set the frequency. Then click Refresh on the report.
Every Frequency cell reads #NAME?.
Your Excel is older than Excel 2021 and does not have the LET or FILTER functions. Type the frequency into each row by hand: Monthly, Quarterly, Semi-Annual or Annual.
Every Frequency cell reads Custom or None.
The months are not in columns AN to AY. Change AN2:AY2 in the formula to your first and last month columns on row 2 and fill down again.
A beneficiary with 1% imported as 100%.
SurgeTK reads a bare 1 as 100%. Open the account and set that beneficiary's allocation by hand.
Need help?
Click the help bubble at the bottom right of any SurgeTK page. Our team will get on a call with you and walk through each step until it works.