CI_User_Guide REF BAC CI User Guide
User Manual: Pdf
Open the PDF directly: View PDF 
.
Page Count: 74
| Download | |
| Open PDF In Browser | View PDF |
Custodial Integrator
User Guide
© October 2009, ByAllAccounts, Inc.
All Rights Reserved
Custodial Integrator Product Version:
V3.1
WebPortfolio Product Version:
V3.15
Document Version:
14
Document Issue Date:
July 20, 2010
ByAllAccounts, Inc.
10 State Street
Woburn, MA 01801-6377
USA
Technical Support:
(866) 856-4951
Office:
(781) 376-0801
Fax:
(781) 376-8040
Web:
www.byallaccounts.com
Table of Figures
USING THE MANUAL ...............................................................................................................1
AUDIENCE AND F ORMAT ......................................................................................................1
RELATED DOCUMENTS ........................................................................................................1
T ERMINOLOGY .................................................................................................................1
CUSTODIAL INTEGRATOR OVERVIEW .............................................................................................2
APPLICATION OVERVIEW .....................................................................................................2
APPLICATION USER INTERFACE ..............................................................................................3
MAIN VIEW ...............................................................................................................3
USER INTERFACE F EATURES .............................................................................................5
CI CONFIGURATION......................................................................................................6
DATA T RANSLATION ..........................................................................................................7
OUTPUT F ILES ............................................................................................................8
ADDITIONAL DATA STANDARDIZATION.................................................................................27
DATA CUSTOMIZATION ..................................................................................................28
CUSTOMIZED T RANSACTION T RANSLATION............................................................................32
SECURITY IDENTIFICATION..............................................................................................32
USING CUSTODIAL INTEGRATOR .................................................................................................34
STARTING CUSTODIAL INTEGRATOR.........................................................................................34
CI CONFIGURATION ..........................................................................................................34
CI ACCESS ...............................................................................................................35
OUTPUT CONFIGURATION................................................................................................36
IMPORT /EXPORT DEFAULTS .............................................................................................42
ACCOUNT TRANSLATION.................................................................................................44
SECURITY T RANSLATIONS ...............................................................................................52
DATA DOWNLOAD .............................................................................................................59
IMPORT ....................................................................................................................60
EXPORT ...................................................................................................................66
ACCEPT EXPORTED DATA ................................................................................................68
ADDITIONAL TRANSLATION INFORMATION .............................................................................69
APPENDIX A - ADMINISTERING CUSTODIAL INTEGRATOR ......................................................................70
APPENDIX B – MULTIPLE CI USERS .............................................................................................71
JOB SHARING CONFIGURATION..............................................................................................71
INDEPENDENT USER CONFIGURATION.......................................................................................71
ByAllAccounts, Inc.
i
Custodial Integrator User Guide
USING THE M ANUAL
Audience and Format
This manual describes how to use the Custodial Integrator application to transfer data from
WebPortfolio to delimited file format. The manual is written for the Data Administrator or
Programmer who is responsible for interpreting these delimited files.
Related Documents
The following related documents are available from ByAllAccounts:
-
WebPortfolio for Custodial Integrator Users: This manual documents the WebPortfolio features
that are needed for users of Custodial Integrator including configuring accounts and reviewing
account data.
-
Custodial Integrator Validation FAQ: provides an overview of the account validation process
-
The Custodial Integrator Solution: provides an overview of the Custodial Integrator solution
-
Custodial Integrator Installation Guide: describes the system requirements, installation
procedure, and initial configuration for the Custodial Integrator product
Terminology
The following are some terms used in this manual:
•
CI – the acronym for Custodial Integrator
•
WebPortfolio - The data aggregation service and application provided by ByAllAccounts
•
WP – an abbreviation for WebPortfolio
ByAllAccounts, Inc.
1
Custodial Integrator User Guide
CUSTODIAL INTEGRATOR OVERVIEW
This chapter will familiarize you with the Custodial Integrator (“CI”) application. It includes
conceptual information on the data translation process and an overview of the application’s user
interface. The Application User Interface section provides information on CI user interface
components – the main application view and the CI Configuration dialog. The Data Translation
section provides details on how data translation is accomplished and what files are generated.
Application Overview
Custodial Integrator is an application that you install and run on a computer within your intranet. It
creates delimited output files and deposits them into a directory that you specify. Custodial
Integrator delivers data from WebPortfolio by utilizing the BAA DataConnect programming
interface. CI provides the several capabilities that would otherwise require custom software
development if you chose to use DataConnect alone, including the following:
•
Provides data in easy-to-use delimited file format (DataConnect provides XML)
•
Handles communication with WebPortfolio service via DataConnect
•
Provides additional standardization of the data for uniformity across custodians
•
De-normalizes security references in holding and transaction objects that would otherwise
require custom business logic
•
Provides graphical interface for control of download
•
Provides ability to view account update status, configure custom security translations, and
provide user-specific identification string for each account
Figure 1 illustrates the overall flow of data from the Financial Institution to your local file system.
The WebPortfolio service gathers data each morning and stores it in the WebPortfolio repository.
CI downloads data over the Internet from the WebPortfolio service, and stores it on your computer.
CI then processes this data to produce delimited files. This entire process is referred to as data
translation.
Figure 1 - Custodial Integrator overview
You perform the CI data translation process by running the CI application. The time required to
complete the data translation process will vary depending on the volume of positions and
transactions that are downloaded from WebPortfolio.
ByAllAccounts, Inc.
2
Custodial Integrator User Guide
Application User Interface
Main View
The CI application main view, shown in Figure 2, is the main work area for data translation from
WebPortfolio to files.
Figure 2 - CI Main View
ByAllAccounts, Inc.
3
Custodial Integrator User Guide
The four steps in the data translation process as shown in the main view are:
1. Setup
This section handles the CI configuration that is required for data translation. You must
provide information about your desired output location and define at least one WebPortfolio
account for translation. The Setup section of the main view shows the status of the
required and optional setup segments along with the date when the corresponding
configuration information was last updated.
2. Import WebPo rtfolio data
This section defines the parameters for importing data from WebPortfolio into the CI local
data store. Use the Import button to start the import process. Account translation errors
must be resolved before you can complete the Import process. CI will alert you to several
conditions including stale data and missing security symbols. You may wish to take action
to resolve these issues prior to the Export step.
3. Export to flat file format
Once WebPortfolio data has been imported into CI, CI is able to generate files into a
designated folder. Use the Export button to run the export process.
4. Accept exported data
CI maintains state information about your transaction downloads. You manually accept a
download to enable CI to update its state information. Use the Accept button to accept the
most recent download. We recommend that you perform this step after you have verified
that CI properly produced your output files.
The CI main view includes an Instructions window at the bottom of the view. T his window
provides instructions and other information relevant to the current state of the main view. If you
are unsure as to what to do next in the main view, check the Instructions window.
The CI main view provides the following menu:
-
-
-
File
o
Exit - exits the CI application
o
Configuration - invokes the CI Configuration dialog, Configuration tab
o
Account Translation – invokes the CI Configuration dialog, Account Translation tab
o
Security Translation – invokes the CI Configuration dialog, Security Translation tab
o
About CI – shows CI product information, system information, and service
agreement.
Edit
Help
ByAllAccounts, Inc.
4
Custodial Integrator User Guide
User Interface Features
The following features are common to more than one area of the user interface:
-
Tables – Tables of information such as those used in multiple tabs of the CI Configuration
dialog have the following common features:
Sorting
o
You may sort a table’s content using any one column in the table. The table’s column
header indicates which column is the current sort key by including an up arrow (^) (for
sort ascending) or a down arrow (v) (for sort descending) in the label. To sort on a new
column or to change the sort order for the current sort key click the column header.
Searching
o
Many tables will include controls to search through the table. These controls include a
column name selector, a choice of “contains” or “starts with”, and a field in which to
enter a comparison value. There will also be a Search button and a List All button. You
can restrict the set of items displayed in the table by completing a search:
i.
choose a column to search on from the column name selector
ii.
choose “contains” or “starts with”
iii.
enter the comparison text into the text field
iv. click the Search
The table now displays only the rows that match your search criteria. Click List All to
cause the table to list all of its rows.
o
Delete button
Use Delete to delete one or more rows from a table. First select the rows to be deleted
using a mouse click. You can use a single mouse click to select one row, then optionally
shift-click for range select and ctrl-click for single row extended select. Use Delete to
delete the selected rows. You will then be prompted to confirm the deletion. Click Yes
to perform the delete, No to cancel the delete.
-
Print button
Print is available for tables and dialog boxes that include significant content such as the View
Data Issues dialog. Clicking Print will bring up the standard Windows Print dialog to allow you
to choose a Printer and other print options.
ByAllAccounts, Inc.
5
Custodial Integrator User Guide
CI Configuration
The CI Configuration dialog is accessible via the Setup… button or the CI Edit menu and contains
the following tabs:
Configuration
This section defines CI product configuration information such as CI access credentials and
the types of files to be generated.
Account Translation
Defines which WebPortfolio accounts you want to download. Also shows the update status
of configured accounts (when the account was last updated with data from its Financial
Institution) and when those accounts were last exported to delimited files and allows you to
request an on-demand update of accounts.
Security Translation
Defines translations from WebPortfolio securities to your security symbols.
More information on each of these configuration areas is available in subsequent sections of this
document.
ByAllAccounts, Inc.
6
Custodial Integrator User Guide
Data Translation
In the data translation process, data flows from the Financial Institution through to delimited files.
This process consists of the following major steps:
1. A Financial Institution makes data available online.
2. WebPortfolio gathers data from Financial Institutions each day and then interprets,
normalizes, and stores it in the WebPortfolio repository.
3. CI retrieves data from the WebPortfolio Repository and stores it locally.
4. CI performs additional standardization steps and outputs the data to delimited files.
Figure 3 - Data Flow
You may view the data at the Financial Institution, in the WebPortfolio Repository via the
WebPortfolio application, or in the CI output files. The CI application provides no general capability
for viewing the information prior to generating the output files.
It is important to note that the quality and completeness of data made available by different
Financial Institutions varies widely. For this reason, accounts at each Financial Institution must
undergo a validation process that includes thorough review of the account data and adjustment to
the translation process. For more information on data validation, please read the companion
document Custodial Integrator Validation FAQ.
After validation is complete, a Financial Institution may present some new data that was not
observed during the validation process and thus does not translate correctly for your target
system. Such issues are considered bugs and you should report them to ByAllAccounts technical
support for resolution.
ByAllAccounts, Inc.
7
Custodial Integrator User Guide
Output Files
Custodial Integrator has the ability to deliver up to five types of files in a single session. CI can
produce each of the following delimited files (yyyymmdd is replaced with the prior business day’s
date). If prices for business days before the prior business day are requested then CI will generate
a Security and Price file for each of those prior business days and these files will be named with the
corresponding business date.
The columns to be included in each output file can be customized in the Setup->Configuration,
Output Configuration section, Advanced button. Additional customizations of the output data are
also available in this area. Please refer to section Output Configuration on page 36 for more
information.
File Type
File Name
Account
BAAACT_yyyymmdd.csv
Transaction
BAATRX_yyyymmdd.csv
Position
BAAREC_yyyymmdd.csv
Security
BAASEC_yyyymmdd.csv
Security Price
BAAPRI_yyyymmdd.csv
CI optionally creates the files listed in the following table. Position and Transaction filters cause the
omission of matching objects from the main file for that object type. Filters can be used to omit
unmanaged assets (Position) or to omit certain types of transactions (e.g. Trades) from being
delivered in the CI output files. Other options include
File Type
File Name
Purpose
Transaction
BAAEXCLUDEDTX_yyyymmdd.csv
If a transaction or position filter
is in use then filtered
transactions are written to this
file.
Transaction
BAAUNSETTLEDTX_yyyymmdd.csv
If the “Back out unsettled
transactions” option is selected
then unsettled transactions will
be written to this file.
Position
BAAEXCLUDEDREC_yyyymmdd.csv
If a position filter is in use then
filtered positions are written to
this file.
All files are written to the output folder that you have configured in CI. If any file of the same name
already exists in the output folder, CI will display a warning and give you the option to overwrite or
append to the existing file. Additional details for each file are included in the following sections.
ByAllAccounts, Inc.
8
Custodial Integrator User Guide
The following table defines the Data Types for the columns included in the output files.
Description
Data Type
BOOLEAN
Value is either 1 (indicating TRUE) or 0 (indicating FALSE).
CHARn
Alphanumeric string containing up to n characters.
DATE
Date in the form YYYYMMDD, where YYYY is a 4-digit year (e.g., 2003), MM is a
2-digit month code from 01 (January) through 12 (December), and DD is a 2-digit
day code from 01 to 31.
NUMBER
Amounts, quantities, and prices use this numeric data type that can contain up to
39 numeric characters including the decimal point. Negative values are indicated by
a leading minus sign (-). Values that do not represent whole numbers (e.g.,
1.3504) include a decimal point to indicate the start of the fractional amount.
Values gathered directly from a Financial Institution may have six or more digits to
the right of the decimal point. No punctuation is used to separate thousands,
millions, etc.
TIMESTAMP
Provides a full time stamp, including time zone of the WebPortfolio server. The
timestamp uses the form: YYYYMMDDHHmmSS [{gmt-offset}:{tz-name}]
where:
YYYY is a 4-digit year (e.g., 2003),
MM is a 2-digit month code from 01 (January) through 12 (December),
DD is a 2-digit day code from 01 to 31,
HH is a 2-digit hour code in 24-hour format (00 through 23),
mm is a 2-digit minute code (00 through 59),
SS is a 2-digit seconds code (00 through 59)
gmt-offset is the number of hours that the time zone is offset from GMT; has a
leading + or tz-name is the name of the time zone (e.g., EST).
Example: 20030721143522 [-5:EST] is July 21, 2003 2:35:22 PM, Eastern Standard Time
ByAllAccounts, Inc.
9
Custodial Integrator User Guide
Account File
CI generates an entry in the Account file for each account listed in the Account translations
configuration section of CI. The following table defines the columns of output that CI creates for
the Account file:
Col
#
Column Header
Data Type
Description
1
ACCOUNT_IDENTIFIER
v
CHAR64
Unique alphanumeric string that
identifies this account within the
set of accounts configured within
CI. This will contain the
ACCOUNT_NUMBER if you have not
configured an alternate identifier
when you set up the account in the
Account Translation tab of Setup.
2
NAME
v
CHAR64
User-assigned nickname for the
Account from WebPortfolio.
3
ONLINE_ACCESS_ENABLED
v
BOOLEAN
TRUE (1) if this Account has
sufficient online access credentials
for WebPortfolio to gather data
from the Financial Service identified
for this Account; FALSE (0) in all
other cases.
4
LAST_UPDATED
TIMESTAMP
Date/time on which data for this
Account was (successfully) last
retrieved from the associated
Financial Institution (may not be
available for the off-line accounts).
5
ACCOUNT_NUMBER
CHAR64
Alphanumeric string used to
identify this Account at the
Financial Service where this
Account is held.
6
UPDATE_STATUS_INFO
CHAR1024
The status (textual description) of
the last attempt to download this
Account from the Financial
Institution.
7
UPDATE_STATUS_ERRCODE
CHAR6
Error code for the result of the last
attempt to download this Account
from the Financial Institution.
8
LAST_UPDATE_ATTEMPT
TIMESTAMP
Date/time of the last access test or
update attempt for this Account.
9
CAPTIVE
v
BOOLEAN
If TRUE, then the Advisor
managing this Account for the
Investor considers this Account to
contain captive assets (assets
under management).
10
DATA_BASIS
v
CHAR12
States whether the data in the
account is SETTLEMENT (value
“SETTLEMENT”) basis or TRADE
ByAllAccounts, Inc.
Value
Required
10
Custodial Integrator User Guide
basis (value “TRADE”).
11
FINANCIAL_INSTITUTION_NAME
CHAR256
Name of the financial institution in
which the account is held.
12
PRIOR_BUSINESS_DAY_DATE
DATE
The date of the business day prior
to the day that the file was
generated.
13
ADVISOR_IDENTIFIER
CHAR64
Unique internal identifier for the
WebPortfolio Advisor user
associated with this account.
14
ADVISOR_FIRST_NAME
CHAR64
First name of advisor user
associated with this account.
15
ADVISOR_MIDDLE_NAME
CHAR64
Middle name of advisor user
associated with this account.
16
ADVISOR_LAST_NAME
CHAR64
Last name of advisor user
associated with this account.
17
CLIENT_IDENTIFIER
v
CHAR64
Unique internal identifier for the
WebPortfolio Investor user (also
called Client) associated with this
account.
18
CLIENT_FIRST_NAME
v
CHAR64
First name of investor user
associated with this account.
19
CLIENT_MIDDLE_NAME
CHAR64
Middle name of investor user
associated with this account.
20
CLIENT_LAST_NAME
CHAR64
Last name of investor user
associated with this account.
v
Transaction File
CI generates an entry in the Transaction file for each transaction in the current download. This
may include transactions from business days other than the prior business day. The following table
defines the columns of output that CI creates for the Transaction file:
Column Header
Col
#
Required
Data
Type
Description
1
ACCOUNT_IDENTIFIER
v
CHAR64
Unique alphanumeric string that
identifies this account within the set
of accounts configured within CI.
2
SYMBOL
v
CHAR512
Symbol used to identify this
security. The value may be a
CUSIP, TICKER, SEDOL, ISIN, or a
CI-generated symbol (OTHER).
3
SYMBOL_TYPE
v
CHAR6
Describes the type of the symbol in
the SYMBOL field. Text is one of
the following: CUSIP, TICKER,
SEDOL, ISIN, OTHER.
4
FI_SUPPLIED_CUSIP
CHAR9
Security’s CUSIP as provided by the
Financial Institution.
5
FI_SUPPLIED_TICKER
CHAR32
Security’s Ticker as provided by the
Financial Institution.
ByAllAccounts, Inc.
11
Custodial Integrator User Guide
Financial Institution.
6
FI_SUPPLIED_SEDOL
CHAR7
Security’s SEDOL as provided by
the Financial Institution.
7
FI_SUPPLIED_ISIN
CHAR12
Security’s ISIN as provided by the
Financial Institution.
8
ID
v
NUM BER
Unique numeric ID for this
Transaction. This is a BAA Internal
ID and is unique across all
transactions within the WebPortfolio
repository.
9
TX_TYPE
v
CHAR20
Type of Transaction. Please refer to
the table below for valid values.
10
EXECUTION_DATE
v
DATE
Date on which this Transaction was
executed. Also called Trade Date.
11
SETTLEMENT_DATE
DATE
Date on which this Transaction was
settled.
12
COMMISSIONS_FEES
NUMBER
Commission and/or fees associated
with this Transaction.
13
UNITS
NUMBER
Number of units (of security)
involved in this Transaction.
14
UNIT_PRICE
NUMBER
Per share price of the Security for
purposes of this Transaction.
15
TOTAL_AMOUNT
NUMBER
Total $ value associated with this
Transaction (may be negative).
16
CURRENCY_CODE
CHAR3
ISO 4217 currency code for the
TOTAL_AMOUNT.
17
FI_SUPPLIED_CURRENCY
CHAR64
Currency identifier supplied by the
Financial Institution for
TOTAL_AMOUNT.
18
PRINCIPAL_UNITS
NUMBER
Principal units of the transaction as
reported by the Financial
Institution.
19
PRINCIPAL_TOTAL_AMOUNT
NUMBER
Principal amount of the transaction
as reported by the Financial
Institution.
20
INCOME_UNITS
NUMBER
Income units of the transaction as
reported by the Financial
Institution.
21
INCOME_TOTAL_AMOUNT
NUMBER
Income amount of the transaction
as reported by the Financial
Institution.
22
TOTAL_AMOUNT_BASE
NUMBER
Total amount of the transaction in
the Base currency for the account.
23
UNIT_PRICE_BASE
NUMBER
Unit price of the transaction in the
Base currency for the account.
24
CURRENCY_CODE_BASE
CHAR3
ISO 4217 currency code for the
account’s base currency.
ByAllAccounts, Inc.
12
Custodial Integrator User Guide
25
FI_SUPPLIED_CURRENCY_BASE
CHAR64
Currency identifier supplied by the
Financial Institution for
TOTAL_AMOUNT_BASE.
26
PRINCIPAL_TOTAL_AMOUNT_BASE
NUMBER
The principal portion of the amount
of the transaction converted to the
base currency of the account
27
INCOME_TOTAL_AMOUNT_BASE
NUMBER
The income portion of the amount
of the transaction converted to the
base currency of the account
28
TOTAL_AMOUNT_LOCAL
NUMBER
The amount of the transaction in
the local currency
29
UNIT_PRICE_LOCAL
NUMBER
The share price of the security
involved in the transaction in the
local currency
30
CURRENCY_CODE_LOCAL
CHAR3
ISO 4217 currency code for
TOTAL_AMOUNT_LOCAL and
UNIT_PRICE_LOCAL.
31
FI_SUPPLIED_CURRENCY_LOCAL
CHAR64
The local currency of the
transaction as provided by the
institution.
32
PRINCIPAL_TOTAL_AMOUNT_LOCAL
NUMBER
The principal portion of the amount
of the transaction in the local
currency
33
INCOME_TOTAL_AMOUNT_LOCAL
NUMBER
The income portion of the amount
of the transaction in the local
currency
34
EXCHANGE_RATE_LOCAL_TO_BASE
NUMBER
The local-to-base exchange rate as
provided by the Financial
Institution.
35
EXCHANGE_RATE_LOCAL_TO_USD
NUMBER
The local-to-USD exchange rate as
provided by the Financial
Institution.
36
NAME
CHAR512
Either the name of the Security or a
short description of the Transaction.
37
DESCRIPTION
CHAR2000
Text provided by the Financial
Service that describes the
transaction.
38
FI_SUPPLIED_TX_TYPE
CHAR64
Text provided by the Financial
Institution that describes the type
of activity that this transaction
represents.
39
FI_SUPPLIED_TX_TYPE2
CHAR64
Secondary text provided by the
Financial Institution that describes
the type of activity that this
transaction represents.
40
FI_SUPPLIED_TX_TYPE_CODE
CHAR16
Transaction type code or
abbreviation provided by the
Financial Institution.
ByAllAccounts, Inc.
13
Custodial Integrator User Guide
NUMBER
Total amount of the cash flow for
this transaction relative to the cash
balance of the account.
NUMBER
Normalized units for the
transaction. Whereas UNITS
contains the units value directly
from the Financial Institution and
may have great variations in sign
within a given transaction type,
FLOW_UNITS contains this same
units value but normalized by
transaction type (e.g. Withdrawal
will always have negative
FLOW_UNITS).
DATE
Date when this Transaction object
was created in WebPortfolio.
ORIG_TX_TYPE
CHAR20
Transactions reported as a reversal
or cancellation by the Financial
Institution will have the original
transaction type code for in this
field. Please see the list of
Transaction type codes that follows
this table for valid values.
45
CONTRACTUAL_SETTLEMENT_DATE
DATE
The date by which the transaction
must contractually settle as
provided by the Financial
Institution.
46
POST_DATE
DATE
The date that the transaction
posted as provided by the Financial
Institution.
47
ACCOUNT_NUMBER
CHAR64
Alphanumeric string used to identify
this Account at the Financial Service
where this Account is held.
48
NET_AMOUNT
NUMBER
For Reinvestment transactions on
cash equivalents this is set to the
absolute value of the first non-null
of the following:
PRINCIPAL_TOTAL_AMOUNT_LOCAL
+
INCOME_TOTAL_AMOUNT_LOCAL,
FLOW_AMOUNT, TOTAL_AMOUNT.
41
FLOW_AMOUNT
42
FLOW_UNITS
43
CREATION_DATE
44
v
v
For all other transactions this is set
to the absolute value of the first
non-null of the following:
PRINCIPAL_TOTAL_AMOUNT +
INCOME_TOTAL_AMOUNT,
FLOW_AMOUNT, TOTAL_AMOUNT
49
NUMBER
NET_AMOUNT_LOCAL
ByAllAccounts, Inc.
14
For Reinvestment transactions on
cash equivalents this is set to the
absolute value of the first non-null
of the following:
PRINCIPAL_TOTAL_AMOUNT_LOCAL
Custodial Integrator User Guide
+
INCOME_TOTAL_AMOUNT_LOCAL,
FLOW_AMOUNT, TOTAL_AMOUNT.
For all other transactions this is set
to the first non-null of the
following:
PRINCIPAL_TOTAL_AMOUNT_LOCAL
+
INCOME_TOTAL_AMOUNT_LOCAL,
FLOW_AMOUNT, TOTAL_AMOUNT
50
PRIOR_BUSINESS_DAY_DATE
DATE
The date of the business day prior
to the day that the file was
generated.
51
TX_SUBTYPE
CHAR20
The transaction’s subtype as
determined by CI. Possible subtype
values are in bold followed by the
transaction types to which they
apply in parentheses:
ACCRUED (BUY, SELL),
TAX (DEBIT, CREDIT),
FOREIGNTAX (DEBIT, CREDIT),
MATURITY (SELL)
SHORTTERMGAIN,
LONGTERMGAIN,
UNCLASSIFIEDGAIN,
MIDTERMGAIN
(DIVIDEND, REINVESTMENT),
IN, OUT (ATM, INCOME,
INTEREST, JOURNAL,
POINT_OF_SALE, TRANSFER)
52
SECURITY_TYPE
53
FI_ID
54
TX_COMPOSITE_TYPE
55
CI_SYNTHESIZED
ByAllAccounts, Inc.
v
CHAR20
Transaction’s security type.
Possible values: BOND, CASH,
STOCK, MUTUALFUND, OPTION,
OTHER
CHAR64
Unique internal identifier for the
financial institution that reported
the transaction
v
CHAR128
Customizable transaction type
composite field that is the
concatenation of one or more of the
following transaction fields:
TX_TYPE, TX_SUBTYPE,
SECURITY_TYPE, FI_ID. Default
composition: TX_TYPETX_SUBTYPE-SECURITY_TYPE
v
BOOLEAN
1 indicates the transaction was
created by CI (sweeps). 0 indicates
the transaction was not synthesized
by CI. 0 is the default.
15
Custodial Integrator User Guide
56
REVERSAL
57
BOOLEAN
1 indicates the transaction is a
reversal. 0 indicates the
transaction is not a reversal.
ACCRUED_INCOME
NUMBER
Amount of accrued interest
58
ACCRUED_INCOME_BASE
NUMBER
Amount of accrued interest in the
account’s base currency
59
ACCRUED_INCOME_LOCAL
NUMBER
Amount of accrued interest in the
account’s local currency
60
FI_SUPPLIED_DESCRIPTION
CHAR2000
Transaction description
61
INCOME_TYPE
CHAR20
Type of income for REINVESTMENT
type only, Possible values: INCOME,
INTEREST, DIVIDEND.
62
POSITION_ID
NUMBER
Unique numeric ID for the position
with which this transaction is
associated. This is a BAA Internal
ID and is unique across all positions
within the WebPortfolio repository.
63
PRINCIPAL_OR_INCOME
CHAR1
Defaults to ‘P’. Set to ‘I’ if the
transaction has a non-zero and
non-blank value in the
INCOME_TOTAL_AMOUNT field.
ByAllAccounts, Inc.
v
16
Custodial Integrator User Guide
The following table shows the valid values for the TX_TYPE and ORIG_TX_TYPE columns:
TX_TYPE Value
Description
ATM
ATM debit or credit (depends on signage of amount)
BUY
Buy a Security
CHECK
Check written
CLOSURE
Close a position for an option
CREDIT
Generic credit
DEBIT
Generic debit
DEPOSIT
Deposit
DIRECT_DEBIT
Merchant initiated debit
DIRECT_DEPOSIT
Direct deposit
DIVIDEND
Dividend paid
FEE
Financial Institution fee
INCOME
Investment income is realized as cash into the investment Account
INTEREST
Interest earned or paid (depends on signage of amount)
INVESTMENT_EXPENSE
Miscellaneous investment expense that is associated with a specific
Security
JOURNAL
Journal cash or Securities between Sub-Accounts within the same
investment Account
MARGIN_INTEREST
Margin interest expense
OTHER
Other
PAYMENT
Electronic payment
POINT_OF_SALE
Point of sale debit or credit (depends on signage of amount)
REINVESTMENT
Reinvestment of income
REPEAT_PAYMENT
Repeating payment/standing order
RETURN_OF_CAPITAL
Return of capital
SELL
Sell a Security
SERVICE_CHARGE
Service charge
SPLIT
Stock or Mutual Fund split
TRANSFER
Transfer cash or Holdings in or out (depends on signage of amount)
WITHDRAWAL
Withdraw funds from Account
ByAllAccounts, Inc.
17
Custodial Integrator User Guide
The following table defines the sign used for the FLOW_AMOUNT and FLOW_UNITS field in the
Transaction file. The sign is based on the transaction type. Signs available are:
o
o
o
o
Positive
Negative
Neutral – used only by FLOW_AMOUNT, this is a flow of 0
As is – the sign in the original data from the Financial Institution. This is usually done to
preserve the full meaning of the transaction (e.g. transfer in vs. transfer out are not
distinguished by type alone, but by type plus unit sign).
TX_TYPE Value
FLOW_AMOUNT Sign
FLOW_UNITS Sign
ATM
As is
As is
Buy
Negative
Positive
Check
Negative
Negative
Closure
Neutral
As is
Credit
Positive
Positive
Debit
Negative
Negative
Deposit
Positive
Positive
Direct Deposit
Positive
Positive
Direct Debit
Negative
Negative
Dividend
Positive
Positive
Fee
Negative
Negative
Income
Positive
Positive
Interest
As is
As is
Investment Expense
Negative
Negative
Journal
As is
As is
Margin Interest
As is
As is
Other
Neutral
As is
Payment
Negative
Negative
Point of Sale
Negative
Negative
Reinvestment
Neutral
Positive
Repeat Payment
Negative
Negative
Return of Capital
Positive
Positive
Sell
Positive
Negative
Service Charge
Negative
Negative
Split
Neutral
As is
Transfer
As is
As is
Withdrawal
Negative
Negative
ByAllAccounts, Inc.
18
Custodial Integrator User Guide
Position File
CI generates an entry in the Position file for each position in the accounts listed in the Account
translations configuration section of CI. The units and market value for the positions are as of the
prior business day. CI does not currently provide a way to obtain a Position file for a business day
other than the prior business day. The following table defines the columns of output that CI
creates for the Position file:
Col
#
Column Header
Required
Data Type
Description
1
ACCOUNT_IDENTIFIER
v
CHAR64
Unique account identifier for the
account that contains this
position.
2
SYMBOL
v
CHAR512
Symbol used to identify this
security. May be a CUSIP,
TICKER, SEDOL, ISIN, or OTHER.
3
SYMBOL_TYPE
v
CHAR6
Describes the type of the symbol
in the SYMBOL field. Text is one
of the following: CUSIP, TICKER,
SEDOL, IS IN, OTHER.
4
FI_SUPPLIED_CUSIP
CHAR9
Security’s CUSIP as provided by
the Financial Institution.
5
FI_SUPPLIED_TICKER
CHAR32
Security’s Ticker as provided by
the Financial Institution.
6
FI_SUPPLIED_SEDOL
CHAR7
Security’s SEDOL as provided by
the Financial Institution.
7
FI_SUPPLIED_ISIN
CHAR12
Security’s ISIN as provided by
the Financial Institution.
8
NAME
CHAR128
The holding’s name (typically
identifies the security) as
provided by Financial Service or
entered by the user (offline
accounts).
9
UNITS
NUMBER
Units of the Security held.
10
COST_BASIS
NUMBER
Financial service-supplied or
user-supplied cost basis for the
Holding . Note: This is a total
basis not a per-share basis.
11
MARKET_VALUE
NUMBER
Total market value for this
Holding as retrieved from the
Financial Institution or entered
by the user.
12
UNIT_PRICE
NUMBER
Unit price for the security as
provided by the Financial
Institution.
13
PRICE_DATA_AS_OF
DATE
Date for which the UNIT_PRICE
is valid.
14
CURRENCY_CODE
CHAR3
ISO 4217 currency code for
MARKET_VALUE and UNIT_PRICE
ByAllAccounts, Inc.
19
Custodial Integrator User Guide
MARKET_VALUE and UNIT_PRICE
15
FI_SUPPLIED_CURRENCY
CHAR64
Currency identifier for
MARKET_VALUE and UNIT_PRICE
as provided by the Financial
Institution.
16
ACCRUED_INCOME
NUMBER
Value of the income that has
accrued to the holding but has
not yet been distributed.
17
PRINCIPAL_UNITS
NUMBER
Principal units as reported by the
Financial Institution.
18
PRINCIPAL_COST_BASIS
NUMBER
Principal cost basis as reported
by the Financial Institution.
19
PRINCIPAL_MARKET_VALUE
NUMBER
Principal market value as
reported by the Financial
Institution (e.g. PRINCIPAL CASH
market value).
20
INCOME_UNITS
NUMBER
Income units as reported by the
Financial Institution.
21
INCOME_COST_BASIS
NUMBER
Income cost basis as reported by
the Financial Institution.
22
INCOME_MARKET_VALUE
NUMBER
Income market value as reported
by the Financial Institution (e.g.
INCOME CASH market value).
23
MARKET_VALUE_BASE
NUMBER
The market value of the position
converted to the base currency
of the account.
24
UNIT_PRICE_BASE
NUMBER
The price of the position
converted to the base currency
of the account.
25
CURRENCY_CODE_BASE
CHAR3
ISO 4217 currency code of the
base currency of the account as
determined from the
FI_SUPPLIED_CURRENCY_BASE
field.
26
FI_SUPPLIED_CURRENCY_BASE
CHAR64
The base currency of the account
as provided by the Financial
Institution.
27
ACCRUED_INCOME_BASE
NUMBER
Value of the income in the base
currency that has accrued to the
holding but has not yet been
distributed.
28
PRINCIPAL_MARKET_VALUE_BASE
NUMBER
The principal portion of the
market value of the position in
the base currency of the account.
29
INCOME_MARKET_VALUE_BASE
NUMBER
Income market value as reported
by the Financial Institution (e.g.
INCOME CASH market value).
30
MARKET_VALUE_LOCAL
NUMBER
The market value of the position
in the local currency of the
ByAllAccounts, Inc.
20
Custodial Integrator User Guide
holding.
31
UNIT_PRICE_LOCAL
NUMBER
The price of the position in the
local currency of the holding
32
CURRENCY_CODE_LOCAL
CHAR3
ISO 4217 currency code of the
local currency of the holding as
determined from the
FI_SUPPLIED_CURRENCY_LOCAL
field.
33
FI_SUPPLIED_CURRENCY_LOCAL
CHAR64
The local currency of the holding
as provided by the Financial
Institution.
34
ACCRUED_INCOME_LOCAL
NUMBER
Value of the income in the local
currency that has accrued to the
holding but has not yet been
distributed.
35
PRINCIPAL_MARKET_VALUE_LOCAL
NUMBER
Principal market value as
reported by the Financial
Institution (e.g. PRINCIPAL CASH
market value).
36
INCOME_MARKET_VALUE_LOCAL
NUMBER
Income market value as reported
by the Financial Institution (e.g.
INCOME CASH market value).
37
EXCHANGE_RATE_LOCAL_TO_BASE
NUMBER
The local-to-base exchange rate
as provided by the Financial
Institution.
38
EXCHANGE_RATE_LOCAL_TO_USD
NUMBER
The local-to-USD exchange rate
as provided by the Financial
Institution.
39
COUPON
NUMBER
The interest payment rate of a
debt instrument.
40
MATURITY_DATE
DATE
The date a debt instrument
becomes due and pays in full.
41
ORIGINAL_FACE
NUMBER
The original face or par value for
a security that amortizes or
accretes (e.g., a mortgage). For
such securities the “current face”
is available in the UNITS field.
42
LAST_UPDATED
TIMESTAMP
Date/time on which the data for
this Holding was last updated with
information fro m the Financial
Service. Absent for Holdings
maintained manually by the user.
43
DATA_AS_OF
DATE
The date which the holding data
is ‘as of’ as reported by the
Financial Institution. If the
Financial Institution does not
report a date then this field will
be empty.
44
VALUE_SOURCE
CHAR12
The source of the market value
for this holding. May be one of
ByAllAccounts, Inc.
v
21
Custodial Integrator User Guide
the following strings: FI,
WPAPPROX, USER.
•
FI - the market value was
collected from the Financial
Institution.
• WPAPPROX – WebPortfolio
approximated the market
value using the units reported
by the Financial Institution
and a closing security price
obtained from a third party.
• USER – the user edited the
market value.
Alphanumeric string used to
identify this Account at the
Financial Service where this
Account is held.
45
ACCOUNT_NUMBER
CHAR64
46
PRINCIPAL_OR_INCOME
CHAR1
Defaults to ‘P’. Set to ‘I’ if the
position has a non-zero and nonblank value in the
INCOME_UNITS field.
47
PRIOR_BUSINESS_DAY_DATE
DATE
The date of the business day
prior to the day that the file was
generated.
48
ASSET_CLASS
CHAR64
The asset class of this security,
one of:
Unclassified
Stocks
Bonds
Cash
Real Estate
Other
49
ASSET_SUBCLASS
CHAR128
The asset subclass of this
security, see list below.
50
POSITION_ID
NUMBER
Unique numeric ID for the
Position. This is a BAA Internal
ID and is unique across all
positions within the WebPortfolio
repository.
Asset subclass values:
•
Unclassified
•
Large Cap Growth (US)
•
Large Cap Core (US)
•
Large Cap Value (US)
•
Mid Cap Growth (US)
•
Mid Cap Core (US)
ByAllAccounts, Inc.
22
Custodial Integrator User Guide
•
Mid Cap Value (US)
•
Small Cap Growth (US)
•
Small Cap Core (US)
•
Small Cap Value (US)
•
Large Cap Growth (Emerging Foreign)
•
Large Cap Core (Emerging Foreign)
•
Large Cap Value (Emerging Foreign)
•
Mid Cap Growth (Emerging Foreign)
•
Mid Cap Core (Emerging Foreign)
•
Mid Cap Value (Emerging Foreign)
•
Small Cap Growth (Emerging Foreign)
•
Small Cap Core (Emerging Foreign)
•
Small Cap Value (Emerging Foreign)
•
Large Cap Growth (Developed Foreign)
•
Large Cap Core (Developed Foreign)
•
Large Cap Value (Developed Foreign)
•
Mid Cap Growth (Developed Foreign)
•
Mid Cap Core (Developed Foreign)
•
Mid Cap Value (Developed Foreign)
•
Small Cap Growth (Developed Foreign)
•
Small Cap Core (Developed Foreign)
•
Small Cap Value (Developed Foreign)
•
Unclassified Stocks
•
Invest. Grade Short (US Tax Exempt)
•
Invest. Grade Intermediate (US Tax Exempt)
•
Invest. Grade Long (US Tax Exempt)
•
Medium Grade Short (US Tax Exempt)
•
Medium Grade Intermediate (US Tax Exempt)
•
Medium Grade Long (US Tax Exempt)
•
High Yield Short (US Tax Exe mpt)
•
High Yield Intermediate (US Tax Exempt)
•
High Yield Long (US Tax Exempt)
•
Invest. Grade Short (US Taxable)
•
Invest. Grade Intermediate (US Taxable)
•
Invest. Grade Long (US Taxable)
•
Medium Grade Short (US Taxable)
•
Medium Grade Intermediate (US Taxable)
•
Medium Grade Long (US Taxable)
•
High Yield Short (US Taxable)
•
High Yield Intermediate (US Taxable)
ByAllAccounts, Inc.
23
Custodial Integrator User Guide
•
High Yield Long (US Taxable)
•
Invest. Grade Short (Foreign)
•
Invest. Grade Intermediate (Foreign)
•
Invest. Grade Long (Foreign)
•
Medium Grade Short (Foreign)
•
Medium Grade Intermediate (Foreign)
•
Medium Grade Long (Foreign)
•
High Yield Short (Foreign)
•
High Yield Intermediate (Foreign)
•
High Yield Long (Foreign)
•
Unclassified Bonds
•
Cash
•
Real Estate
•
Hedge Funds
•
Private Equity Investments
•
Options & Futures
•
Precious Metals
•
Natural Resources
•
Other Investments
•
Unclassified Other
ByAllAccounts, Inc.
24
Custodial Integrator User Guide
Security File
Column Header
Col
#
Required
Data
Type
Description
1
SYMBOL
v
CHAR512
Symbol used to identify this security.
May be a CUSIP, TICKER, SEDOL, ISIN,
or a dummy symbol (when no official
symbol can be determined).
2
SYMBOL_TYPE
v
CHAR6
Describes the type of the symbol in the
SYMBOL field. Text is one of the
following: CUSIP, TICKER, SEDOL, ISIN,
OTHER.
3
NAME
v
CHAR128
Name for this Security (e.g., “Ford
Motor Company”)
4
SECTYPE
v
CHAR20
Type of Security, one of:
BOND
CASH
MUTUALFUND
OPTION
OTHER
STOCK
5
TICKER
CHAR32
Ticker symbol for this Security (e.g.,
“F”), if available
6
CUSIP
CHAR9
Committee on Uniform Security
Identification Procedures (CUSIP) for
this Security (e.g., “345370860”), if
available
7
SEDOL
CHAR7
Stock Exchange Daily Official List
number for this security, if available.
SEDOL is a code used by the London
Stock Exchange to identify foreign
stocks, especially those that aren't
actively traded in the U.S. and don't
have a CUSIP.
8
ISIN
CHAR12
9
COUPON
NUMBER
ISIN for this security, if available. A
unique international code that identifies
a securities issue. Each country has a
national numbering agency that assigns
ISIN numbers for securities in that
country.
Coupon rate for debt instruments.
10
MATURITY_DATE
DATE
Maturity date for debt instruments.
11
PRIOR_BUSINESS_DAY_DATE
DATE
The date of the business day prior to the
day that the file was generated.
12
ASSET_CLASS
CHAR64
The asset class of this security, one of:
Unclassified
ByAllAccounts, Inc.
25
Custodial Integrator User Guide
Stocks
Bonds
Cash
Real Estate
Other
13
ASSET_SUBCLASS
ByAllAccounts, Inc.
CHAR128
26
The asset subclass of this security, see
list under Security file description.
Custodial Integrator User Guide
Price File
CI generates an entry in the Price file for each security referenced by a position or transaction in
the current download and for which a price is available for the prior business day. The following
table defines the columns of output that CI creates for the Price file:
Col #
Column Header
Required
Data
Type
Description
1
SYMBOL
v
CHAR512
Symbol used to identify this
security. May be a CUSIP, TICKER,
SEDOL, ISIN, or a dummy symbol
(when no official symbol can be
determined).
2
SYMBOL_TYPE
v
CHAR6
Describes the type of the symbol in
the SYMBOL field. Text is one of
the following: CUSIP, TICKER,
SEDOL, ISIN, OTHER.
3
CLOSE_PRICE
v
NUMBER
The closing price of the security for
the date in PRICE_AS_OF.
4
PRICE_AS_OF
v
DATE
The date for which CLOSE_PRICE is
the closing price of the security.
5
CURRENCY_CODE
CHAR3
ISO 4217 currency code. This will
always be USD or blank in this
release of CI.
6
PRIOR_BUSINESS_DAY_DATE
DATE
The date of the business day prior
to the day that the file was
generated.
Additional Data Standardization
CI provides additional standardization of the data, in particular, transaction data, to make it more
uniform across custodians. This standardization is done via several mechanisms and the most
common transactions that require this additional standardization are these:
•
Reinvestments
Custodians report reinvestment activity in a variety of ways, sometimes with multiple
transactions and other times with just a single transaction. This behavior can even vary by
account within a single custodian. CI provides standardization to a single Reinvestment
transaction in these cases when the “Combine dividend with other transactions that
represent a reinvestment…” option is enabled (Setup- >Configuration- >Advanced>Transactions tab->General Options).
•
Exchanges
Often reported as “Transfers” the activity of exchanging one mutual fund for another in a
retirement account is more correctly modeled as a trade (Buy, Sell) rather than an asset
transfer. CI converts exchanges that are reported by the custodian using “Transfer” type
code or language to a Buy or Sell as needed.
•
Retirement account Contributions and Distributions
Many retirement plan web sites do not report on cash activity explicitly. The account
holder’s contributions to the account are often reported as a single “Contribution”
ByAllAccounts, Inc.
27
Custodial Integrator User Guide
transaction with a related mutual fund (or other security). The transaction actually
represents a deposit of cash to the account and a subsequent purchase of the security.
Similarly compact reporting is made on the withdrawal side, where a “Distribution”
transaction with a related mutual fund actually represents a sell of the fund and a
withdrawal of cash. CI converts these transactions to appropriately account for cash by
generating a Cash Deposit and a Buy for Contributions and a Sell and a Cash Withdrawal for
Distributions.
•
Retirement account Fees
Similar to the previous item, Fees may be reported as a Fee against a mutual fund (or other
security). The Fee transaction describes the selling of the fund and then a withdrawal of
cash to pay the named fee. CI converts these transactions to appropriately account for
cash by converting them to a Sell and a Fee.
CI provides options to control how certain types of transactions are output:
•
Bond purchase/sale - You can opt to have CI identify and combine Bond purchase
transaction pairs where one transaction is the bond purchase and the other is the purchase
of the associated accrued interest. The same option controls combining Sell and Sell
Accrued transactions for Bonds. Since some custodians report a single transaction that
includes the accrued income while other custodians report two transactions, this option lets
you produce a consistent single transaction for these cases regardless of which way the
custodian reports the activity.
•
Reinvestments - You can opt to have CI identify and combine two transactions (Dividend
and Buy, Dividend and Reinvestment) into a single Reinvestment transaction. Some
custodians report reinvestment of dividends using multiple transactions while others use
just a single transaction. This option lets you consistently produce a single transaction for a
reinvestment regardless of custodian behavior.
Data Customization
CI provides support for the following data customizations:
•
Position Filters
Position filters are used to omit from CI’s output any positions you do not want delivered to
your target system. When a position is omitted, all associated transactions are also
omitted. Position filters are implemented for you by ByAllAccounts and are encoded in an
XML file that is specific to you. Filtered positions are not included in the
BAAREC_yyyymmdd.csv file but they are written to another file named
BAAEXCLUDEDREC_yyyymmdd.csv file. Similarly, filtered transactions are not included in
the BAATRX_yyyymmdd.csv but are written to the BAAEXCLUDEDTRX_yyyymmdd.csv file.
•
Transaction Filters
Transaction filters are used to omit from CI’s output any transactions you do not want
delivered to your target system. Transaction filters are implemented for you by
ByAllAccounts and are encoded in an XML file that is specific to you. Filtered transactions
are not included in the BAATRX_yyyymmdd.csv but are written to the
BAAEXCLUDEDTRX_yyyymmdd.csv file.
•
Custom Transaction Translation
CI provides a default set of transaction translations for standardization purposes. You can
override these translations to produce different transaction output using the custom
transaction translation mechanism. Custom transaction translations are implemented for
you by ByAllAccounts and are encoded in an XML file named CiTxTransCommonCust.xml.
•
Automatic generation of sweep transactions
ByAllAccounts, Inc.
28
Custodial Integrator User Guide
CI can create “sweep” transactions for accounts where the custodian does not explicitly
report those transactions. “Sweep” transactions are those which move the cash that is
generated by another transaction into or out of the “sweep” holding, typically a money
market. You control whether CI creates these sweep transactions by enabling the “Create
sweep transactions using this holding” option in the Account Translation.
CI enables you to specify the symbol, symbol type, and security type of the sweep symbol
for each account for which you want sweep transactions to be created. The following
reserved symbols are not allowed for use as the sweep symbol: CASH BALANCE, MONEY
MARKET BALANCE, or MARGIN BALANCE. If you have an account that reports the money
market position as simply “MONEY MARKET” then WebPortfolio will assign the ticker “MONEY
MARKET BALANCE”. In this case, use CI to define the translation for MONEY MARKET at the
account level in the Add/Edit Account Translation dialog. You should then use the new
symbol for money market in the Sweep configuration.
For each download of transactions, CI will determine which transactions from the custodian
require a sweep transaction. Original transactions with a positive (and non-zero)
FLOW_AMOUNT will cause a Buy of the sweep security while transactions with a negative
FLOW_AMOUNT will cause a Sell of the sweep security. Transactions with a zero
FLOW_AMOUNT will not cause any sweep transactions. The following table shows which
final (after all additional normalization and translation steps have occurred) CI transactions
will cause sweep transactions to be generated if sweeps are configured for the associated
account:
ByAllAccounts, Inc.
29
Custodial Integrator User Guide
TX_TYPE
Sweep
Transaction
Type
ATM
IN
BUY
OUT
SELL
BUY
SELL
CHECK
SELL
CLOSURE
No sweep
CREDIT
BUY
DEBIT
SELL
DEPOSIT
BUY
DIRECT_DEBIT
SELL
DIRECT_DEPOSIT
BUY
DIVIDEND
BUY
FEE
SELL
INCOME
BUY
INTEREST
BUY
INVESTMENT_
EXPENSE
SELL
JOURNAL (CASH)
IN
BUY
OUT
SELL
MARGIN_INTEREST
SELL
OTHER
No sweep
PAYMENT
SELL
POINT_OF_SALE
SELL
REINVESTMENT
No seep
REPEAT_PAYMENT
SELL
RETURN_OF_
CAPITAL
BUY
SELL
BUY
SERVICE_CHARGE
SELL
SPLIT
No sweep
TRANSFER (CASH)
IN
BUY
OUT
SELL
WITHDRAWAL
ByAllAccounts, Inc.
SELL
30
Custodial Integrator User Guide
Sweep transactions use the TOTAL_AMOUNT of the originating transaction as well as its
currency. The sweep will only be created if the original transaction’s local currency matches its
base currency.
ByAllAccounts, Inc.
31
Custodial Integrator User Guide
Customized Transaction Translation
CI provides for additional customization of data translation via the CI initialization file, CI.ini. The
following table lists the CI parameters that affect customization. Please refer to the Custodial
Integrator Installation Guide for more information on setting a parameter in the initialization file.
Parameter
label
Parameter
value(s)
Default
Description
Example
overwritepos
useMV
useMV
overwriteposzerounits
zerounits
use1
Used to handle positions where
neither quantity nor price is
reported by the financial
institution. If not specified or
useMV is specified then the
quantity is set to the market
value of the position and the
price is set to $1. If use1 is
specified then set the quantity to
1 and the price equal to the
market value. If none is
specified then do not report any
quantity for the position in either
the reconciliation file or the
position file.
none
=useMV
Security Identification
Several of the output files (transaction, position, price) require a security reference. Due to the
variations in source data from Financial Institutions and the limitations of the WebPortfolio security
master, a valid symbol may not always be available from WebPortfolio for CI to use in its output.
Please note that you can define security translations in Custodial Integrator. The following lists the
default security identification behavior of CI in the various situations that can occur:
1. Security Found in WebPortfolio Security Master:
a. The position (or transaction) as presented by the Financial Institution has a Ticker
and/or a CUSIP and the symbol identifies a security in the WebPortfolio security
master. CI will use the Ticker from the WebPortfolio security master in this case as
the security symbol for the Position and all transactions against this position. If no
Ticker is available in the WebPortfolio security master then CI will use the CUSIP
from the WebPortfolio Security Master.
b. The position (or transaction) as presented by the Financial Institution has no symbol
information but WebPortfolio is able to identify it as a security in the WebPortfolio
security master through evaluation of descriptive information for the position. CI will
use the Ticker from the WebPortfolio security master in this case as the security
symbol for the position and all transactions against this position. If no Ticker is
available in the WebPortfolio security master then CI will use the CUSIP from the
WebPortfolio Security Master.
2. Symbol info provided by institution but not found in WebPortfolio Security Master:
The position (or transaction) as presented by the Financial Institution has a Ticker
and/or a CUSIP but WebPortfolio is unable to identify a corresponding security in the
WebPortfolio security master using this symbol information. CI will use the symbol
ByAllAccounts, Inc.
32
Custodial Integrator User Guide
information from the Financial Institution (Ticker if present, otherwise CUSIP) to identify
the security in the output files. CI has no way to ascertain if the Financial Institution is
providing a ‘dummy’ or otherwise invalid symbol. CI will use the security type ‘OTHER’
in this case.
3. No Symbol Information:
The position (or transaction) as presented by the Financial Institution has no symbol
information and BAA is not able to identify it as a security in the BAA security master.
CI has no symbol information for the security in this case. CI will use the ‘name’ of the
position (when present) to identify the security for a position. The name is the
descriptive name for the position presented by the Financial Institutions. If no name is
present for a position or transaction then CI will use the symbol string specified in the CI
Configuration field named: String used when none of these symbols is available.
This defaults to 999999.
ByAllAccounts, Inc.
33
Custodial Integrator User Guide
USING CUSTODIAL INTEGRATOR
Starting Custodial Integrator
Invoke the Custodial Integrator application through the Custodial Integrator icon that has been
installed on your Windows desktop. Double-clicking the icon causes CI to start up and display its
identifying splash screen:
This will be followed by a display of the main view. To exit the application, use the File->Exit menu
item.
CI Configuration
The first time you use CI you will establish the configuration for the accounts that you wish to
process on an ongoing basis. You will subsequently need to change your CI configuration only if
your output directory changes, your import/export preferences change, or you want to add or
remove WebPortfolio accounts from the data translation process. Such changes will typically not
occur at the same frequency that you have established for using CI to download data.
This section provides an explanation of the features in the CI Configuration section of the
application. You will need to be familiar with these features to do your initial CI configuration as
well as to monitor and maintain it.
Your system administrator has performed some minimal configuration of CI as part of the
installation process. We will now review in detail the capabilities of the CI configuration section of
the application and update your configuration to prepare for your first download. To begin:
1. Run the CI application using the CI icon on your Windows desktop. The main view
displays.
2. Click Setup… to bring up the CI Configuration tabbed dialog.
The Configuration tab of the CI Configuration dialog will now appear as shown in Figure 4 below. It
contains the following sections of configuration information:
-
CI Access: login and password to access data from the WebPortfolio service
-
Output Configuration: configuration settings for the files to be generated
-
Import/Export Defaults: default settings for the data translation process, including which
files to generate.
ByAllAccounts, Inc.
34
Custodial Integrator User Guide
Figure 4 - CI Configuration
CI Access
This section contains login and password that are used to access your firm’s data from the
WebPortfolio service. This login and password is often the same as what you use to log in to
WebPortfolio Advisor.
CI Login – the login assigned to you for data access by ByAllAccounts
CI Password – the password assigned to you for data access by ByAllAccounts
Enter the CI login and password you received from ByAllAccounts in the CI Access section then
verify that the login and password have been entered correctly by choosing the Test Access button
in the CI Access section. Once the test is complete you should see the message “Access was
successfully tested.” at the bottom of the CI Access section. If you do not see this message,
please contact your system administrator or ByAllAccounts Technical Support to resolve the issue
before continuing with the configuration.
This section also includes the CI Working Folder, a folder where CI can write temporary files. Set
this to a folder specifically created for CI’s use and make sure that the folder grants read and write
access to those Windows users who will run CI.
ByAllAccounts, Inc.
35
Custodial Integrator User Guide
Output Configuration
This section contains your preferences for formatting and delivery of output files.
This section enables you to configure the following:
File output folder - designates the folder to receive output files created by CI.
Browse… button to navigate to the desired directory.
Click the
Security symbol preference order – the four types of security symbols that are gathered by
WebPortfolio are listed in order. Use the up and down arrow to reorder the symbol types to reflect
your preference. If multiple types of security symbols are present for a security (e.g. a CUSIP and
a Ticker), CI uses your preference to determine which symbol to use for security identification.
String used when none of these symbols is available – if no symbol is available from
the Financial Institution and you do not define a security translation to provide a symbol, CI
will generate a unique symbol for the security using the string you provide here as a symbol
prefix. Symbols generated in this way will have a symbol type of OTHER.
Column delimiter – the character used to separate columns of data in the output. If you do
not opt to enclose column values in double quotes (see next option) then CI will strip the
column delimiter from the column values. For example, if you use a comma (“,”) to delimit but
do not use double-quotes to enclose values then CI will strip all commas from your string
values prior to writing them to the delimited file.
Enclose column values in double quotes (“) – enclose column header strings and column
values in double-quotes.
The Advanced button produces the following sub-dialog:
ByAllAccounts, Inc.
36
Custodial Integrator User Guide
The Output Options dialog provides a tab of customization options for each of the five output
files. All tabs enable you to control which columns are included in the output file and to control the
order of those columns in the output file. To prevent a column from being included, select the
column, uncheck the Include box at the bottom of the dialog, and click Apply.
The Transactions tab is shown as follows:
The Transactions tab, Columns sub-tab also allows you to specify a blank or zero (“0”) value to
be used for NUMBER type fields (other than the ID) in the case where the field has no value (from
the custodian). It allows you to have the absolute value function applied to each of the following
fields via a custom option: COMMISSIONS_FEES, TOTAL_AMOUNT, UNIT_PRICE,
PRINCIPAL_TOTAL_AMOUNT, INCOME_TOTAL_AMOUNT, TOTAL_AMOUNT_BASE,
UNIT_PRICE_BASE, PRINCIPAL_TOTAL_AMOUNT_BASE, INCOME_TOTAL_AMOUNT_BASE,
TOTAL_AMOUNT_LOCAL, UNIT_PRICE_LOCAL, PRINCIPAL_TOTAL_AMOUNT_LOCAL,
INCOME_TOTAL_AMOUNT_LOCAL.
You can also specify default values for some fields when no value is provided in the source data:
o
CURRENCY_CODE, CURRENCY_CODE_LOCAL – Choose a specific currency code to use.
o
SETTLEMENT_DATE – default to Prior Business Day
o
EXCHANGE_RATE_LOCAL_TO_BASE, EXCHANGE_RATE_LOCAL_TO_USD – use 0 or 1
ByAllAccounts, Inc.
37
Custodial Integrator User Guide
The Transactions tab, General Options sub-tab is shown here:
The Transactions tab, General Options sub-tab provides these global options:
•
‘Back out unsettled transactions’.
This option, off by default, will cause CI to perform special handling of transactions
whose settlement date is later than the prior business day. These transactions will be
reversed in the associated position (e.g. a Buy that is unsettled would cause a reduction
in shares of the corresponding position) and the transactions will not be included in the
transaction output file. For positions that are adjusted and subsequently have non-zero
units, the positions’ market value, principal market value, and income market value will
be recalculated by using the percentage change in units as a factor. The same
calculation will be performed for the base and local market values, principal market
values, and income market values. They will be written to a special additional file named
BAAUNSETTLEDTX_yyyymmdd.csv.
•
Combine the buy or sell of bonds with their matching accrued transactions
This option, selected by default, causes the combining of a Buy and Buy Accrued or a
Sell and Sell Accrued transaction into a single transaction. These transaction pairs occur
for bond purchases and sales. The resulting transaction contains the accrued interest
purchase in the ACCRUED_INCOME. Disable the option to prevent CI from combining
these transactions.
•
Combine dividend with other transactions that represent a single reinvestment into a
reinvestment transaction
This option, selected by default, causes the combining of a pair of transactions that
represent a dividend reinvestment:
o
Dividend and a Buy
o
Dividend and a Reinvestment
The resulting transaction has a TX_TYPE of Reinvestment. Disable the option to prevent
CI from combining these transaction pairs.
ByAllAccounts, Inc.
38
Custodial Integrator User Guide
The Positions tab, Columns sub-tab is shown here:
The Positions tab also allows you to specify a blank or zero (“0”) value to be used for NUMBER
type fields (other than the ID) in the case where the field has no value (from the custodian). It
allows you to provide a default currency code value for CURRENCY_CODE,
FI_SUPPLIED_CURRENCY_CODE, and CURRENCY_CODE_LOCAL columns. You can choose from a
list of standard currency codes for this setting. Another option you have is to override the UNITS
value with the value of the PRINCIPAL_UNITS field. Finally, the PRINCIPAL_OR_INCOME field can
have a default set to blank, ‘P’, or ‘I’.
ByAllAccounts, Inc.
39
Custodial Integrator User Guide
The Positions tab, General Options sub-tab is shown here:
The Positions tab, General Options sub-tab provides these global options:
•
Create separate income positions
For any position that has a non-zero value for INCOME_UNITS as well as a value for
either UNITS or PRINCIPAL_UNITS, this option specifies that CI should create a new
position in the position file to represent the Income portion of the position. The new
position will have units, cost basis, and market value all set to the value from the
INCOME_UNITS field. The PRINCIPAL_OR_INCOME field will be set to ‘I’ and all of its
PRINCIPAL fields will be set to the default (blank or zero). All remaining fields in the
income position will be set from the original position. The original position will have its
INCOME_UNITS field set to the default (blank or zero).
•
Combine positions with duplicate account, security id, and principal/income designation
Where there exist multiple positions of the same security within an account and those
positions have the same principal/income designation, CI will combine those positions
into a single position for output. The combined position UNITS, COST BASIS, and
MARKET_VALUE fields will contain the sum of the corresponding fields from the original
positions.
•
Remove positions that have no units
After other position manipulations are performed (backing out unsettled transactions,
creating separate income positions, combining positions), CI will exclude from the
output any position that has zero in the units field.
ByAllAccounts, Inc.
40
Custodial Integrator User Guide
The Securities tab is shown as follows:
The Securities tab allows you to specify a blank or zero (“0”) value to be used for NUMBER type
fields in the case where the field has no value (from the custodian).
ByAllAccounts, Inc.
41
Custodial Integrator User Guide
The Prices tab is shown as follows:
The Prices tab allows you to specify a blank or zero (“0”) value to be used for NUMBER type fields
in the case where the field has no value (from the custodian). It also allows you to choose a
default currency code for the CURRENCY_CODE column.
Import/Export Defaults
This section of the configuration defines the data translation settings you will use on a daily basis.
Most of these settings can be overridden in the main view for a particular import/export session.
Include accounts for export – this option controls the creation of an account file.
Include transactions for export – this option controls the creation of a transaction file.
Include positions for export – this option controls the creation of a position file.
Include securities for export – this option controls the creation of a securities file.
Include security prices for export – this option controls the creation of a security prices file.
ByAllAccounts, Inc.
42
Custodial Integrator User Guide
The Advanced button produces the following sub-dialog with additional options:
CI uses the prior day closing price from our third party pricing provider. If a price is
not available for the security from this provider then CI uses the price provided by the
financial institution. One exception to this is that you can configure CI to always use the
financial institution’s price for bonds instead of first using the third party provider price
(when available).
When no price is available from the third party or from the financial institution then the
settings in the ‘Missing Prices’ section specify what should be done. If the ‘Calculate the
price…’ option is set then CI will calculate the price using the holding’s market value and
units. CI may not always be able to calc ulate a price in these situations.
The second Missing Prices option, ‘Use the price from an earlier business day…’ enables
you to use a price from an earlier business day (normally considered a ‘stale’ price) –
this can be a useful option when there are holdings that do not price every day. Please
note that if the ‘Calculate price…’ option is checked then CI will first attempt calculation
of the price and failing this will use an available earlier business day price if the second
Missing Prices option is checked. Prices from a day earlier than prior business day are
only used for accounts that are updating successfully from the custodian. If you check
the ‘Use the price from an earlier business day…’ option we strongly recommend that
you click the ‘View Prices’ button in the main view on each download and review prices
in that list that have a date earlier than the prior business day.
Select for import only up-to-date and offline accounts that have not yet been
exported today – WebPortfolio begins data collection at 1 AM and attempts to update all
accounts in WP by approximately 8 AM. Depending on what time you begin the download
process, data may not be available for all your accounts, resulting in a need to perform the data
translation process multiple times throughout the day. Selecting this option as a default will
automatically select any accounts that are ready for download on the Account Status Tab.
Accounts that are considered ready for download are those that (1) have updated in today’s
WebPortfolio data gathering cycle, and (2) have not yet been processed through CI today. For
more information see the Account Status section on page 50.
Review the settings in this section of the configuration, make any desired changes, and then press
Save. The next step is to configure Accounts for translation.
ByAllAccounts, Inc.
43
Custodial Integrator User Guide
Account Translation
The Account Translation tab enables you to configure which WebPortfolio accounts you want to
download for CI. It also provides functions for maintaining accounts within Custodial Integrator.
Figure 5 - Account Translation
The Account Translation tab is divided into two major sections. The upper section includes a single
table (Account translations) that shows the WebPortfolio accounts that have been configured for
delivery to output files. The lower section includes a table of accounts present in WebPortfolio for
your firm that have not yet been selected for translation (Untranslated WebPortfolio accounts).
These accounts will not be included when output files are generated.
The Account translations table includes the following columns:
•
Account ID - this is the account identifier that you want used to identify the account in the
output files. For example, each transaction will include the value of the account id to
identify its account of origin. The account file, position file, and transaction file all include
the account identifier. CI will automatically assign a value to this field using the Account
Number you entered into WebPortfolio for this account. You will need to enter a different
value if you have already configured the account in your target system and the account
number differs from that in WebPortfolio. You will also need to enter a different value for
this account number if the WebPortfolio account number is not unique within the set of
accounts you will be downloading through Custodial Integrator. You can edit this field.
•
WP Account – this is the WebPortfolio Account name (nickname) for reference.
ByAllAccounts, Inc.
44
Custodial Integrator User Guide
•
WP Account # - this is the WebPortfolio Account number.
•
Institution – this is the name of the Financial Institution Service where the account is held.
•
Last Updated - this is the date when WebPortfolio last gathered data for the account from
the Financial Institution
•
Last Exported – this is the date when Custodial Integrator last exported this account to
delimited files
•
Tx date – this is the date that will be used to determine which transactions should be
downloaded in the next cycle. For CI installations that are configured to be trade-date
based (the default), the following behavior applies:
o
After the first download for an account is Accepted (see Data Download process) this
column will display the word ‘latest’ for the account instead of a date. This indicates
that CI will download all transactions posted to the account since the last download.
If an institution posts transactions late to its web site then you could receive
transactions with trade date more than one business day in the past. You can edit
this field.
For CI installations that are configured to be settlement-date based, the following behavior
applies:
o
•
This column will always show a date that controls which transactions will be retrieved
in the next download. CI will retrieve all transactions with settlement date on or
after the date shown here. You could receive transactions that will settle in the
future, and you will also receive these transactions in your transaction file each day
until the settlement date is reached. this account. CI updates this field after the
download is complete in preparation for the next download. You can edit this field.
Validation – this is the validation status of the account’s Financial Institution and will be
one of the following:
o
validated – the account’s Financial Institution has been validated for your use with
CI. ByAllAccounts recommends that you only translate accounts in CI that have this
status.
o
in process – validation is in progress but has not yet been completed
o
invalid – the account’s Financial Institution could not be validated for your use with
CI due to insufficient data from the institution
o
N/A – you have opted to not use accounts at this Financial Institution with Custodial
Integrator
•
MM Symbol – this is the money market symbol to be used for positions and transactions
for this account that refer to a general ‘money market’ instead of to a specific money
market fund. You can edit this field.
•
Sweep Symbol – this is the symbol to be used for sweep transactions synthesized by
Custodial Integrator. You can edit this field.
Adding Account Translations
CI will only download WebPortfolio accounts for which you define an account translation. To add a
WebPortfolio account to the translation:
1. Select the account in the Untranslated WebPortfolio accounts table and click the Add
button.
2. The Add Translations dialog will appear:
ByAllAccounts, Inc.
45
Custodial Integrator User Guide
Figure 6 – Add account translation
3. CI will automatically enter the account number from WebPortfolio into the Account
identifier field. You may change the number if you wish to use an account identifier for
this account that differs from its custodial account number. What you enter here will be
used in the Account ID field for all output files that include an account identifier.
4. Enter a date into the Earliest transaction trade date field. This date will be used to
control which transactions are included in the first download for this account. Transactions
with trade date on or after the date you enter will be included.
5. The Enable for import option is checked by default and you will normally want to leave
this selected. If for some reason you want to add the translation but do not want the
account included in the output files then uncheck this option.
6. Enter a money market symbol (optional). This option is used for accounts at financial
institutions that report the money market position as simply ‘MONEY MARKET’ or ‘MONEY
MARKET BALANCE’ or they report transactions against ‘MONEY MARKET’ instead of against a
specifically named money market fund. Enter here the account-specific money market
symbol that you would like used for such positions and transactions in the account when
data is generated for output files. You may still have to create additional security
translations for money market related items in this account. This field is not required.
7. Enter information for a sweep security for this account but only if you want CI to synthesize
sweep transactions. When you enable the ‘Create sweep transactions using this holding’
option, CI will create sweep Buy and Sell transactions as needed by the other transactions
reported for the account. For example, a cash Dividend transaction would cause CI to
synthesize a sweep Buy in the amount of the cash Dividend transaction.
8. Click Add
ByAllAccounts, Inc.
46
Custodial Integrator User Guide
Once you add an account to the Account translation table it is no longer listed in the
Untranslated WebPortfolio accounts table.
You can add multiple accounts to the Account Translation table at once if the following criteria are
met:
1. Each account can be configured with the default account number (from WebPortfolio)
2. Each account will use the same setting for money market symbol.
3. Each account will use the same setting for earliest transaction trade date option.
4. All accounts will be either Enabled or Disabled for Import.
To add multiple accounts at once, select them all from the Untranslated table using Shift-Click or
Ctrl-Click then press Add. The Add Translations dialog appears:
Figure 7 - Add multiple account translations
You may then modify the Earliest transaction trade date, Money Market symbol and symbol
type, Sweep security information, and Enable for import options and click Add to add
translations for all accounts.
Add some account translations now by following the steps listed above. The rema inder of this
section describes other capabilities of the Account translation tab.
The Account translation table contains accounts that are configured for CI to process.
WebPortfolio accounts are created and maintained in the WebPortfolio application and are selected
for the CI in this table. These items can be modified or removed in WebPortfolio and this can result
in CI’s reference to the items becoming invalid. If CI detects an invalid account reference in the
Account translation then CI will display that entry in blue text. You must resolve the reference
error before you can proceed with the download. Typically the reference will be in error because
ByAllAccounts, Inc.
47
Custodial Integrator User Guide
the account has been removed from WebPortfolio. If this is the case then you should also remove
the entry in the Account translation table for this account by selecting the account in the table and
pressing the Delete button.
Editing Account Translations
The Account Translation tab provides for editing existing account translations. You can edit a
single account translation or you can edit multiple account translations. To edit a single account
translation, select that translation in the Account translations table (top table) and click the Edit…
button. The Edit Translations dialog appears as in the following picture:
Figure 8 - Edit Account translation
The account translation to be edited is shown in the table at the top. The fields that can be edited
are shown in the bottom portion of the dialog. To edit the translation, simply edit the information
in the fields shown at the bottom and click Ok.
To edit multiple account translations, select all of the translations in the Account translations table
using your mouse and extended select (Ctrl-click to add a single row to the selection, Shift-click to
add a range of rows to the selection) and click Edit… The Edit Translations dialog appears as in
the following picture:
ByAllAccounts, Inc.
48
Custodial Integrator User Guide
Figure 9 - Edit multiple account translations
The account translations to be edited are shown in the table at the top. The fields that can be
edited are shown in the section below. The values you enter in the lower section will be applied to
all of the translations shown in the upper section. If you make a mistake you may terminate the
operation without affecting your account translations by selecting the Cancel button.
To edit the translations:
1. Enter new values into the fields at the bottom of the dialog. The apply button will become
enabled as soon as there is a new value to apply.
2. Apply your change to the translations by clicking Apply.
3. Repeat this process to obtain the desired changes.
4. Click Ok to save the changes or Cancel to exit without making the changes. Note that
even if you have hit the Apply button you can still Cancel without affecting your
translations.
Disabling Account Translations
You can disable the delivery of accounts to output files without deleting the account translation.
This might be your preferred method of handling accounts that are not updating due to client
login/password problems and for which you must wait for a lengthy period before the account will
update successfully. To disable the inclusion of a translated account in delimited file output, do the
following:
1. Select the account(s) in the Account translation table.
2. Click the Edit… button.
3. Check the Disable check-box under Enable for import section:
ByAllAccounts, Inc.
49
Custodial Integrator User Guide
4. Click Apply
5. Click Ok
The disabled accounts are now shown in Red in the Account translation table. You can re-enable
the accounts by completing the process above but checking the Enable check box instead.
Deleting Account Translations
You may remove account translations when you no longer want those accounts delivered to output
files. Deleting an account translation in Custodial Integrator does not affect that account’s status
in WebPortfolio. If you want to eliminate data gathering for an account you must remove it from
WebPortfolio. To delete account translations in CI, select them from the Account translations
table and then click the Delete button. CI will ask you to confirm the deletion. If you confirm, the
accounts will be removed from the Account translations table and appear in the Untranslated
WebPortfolio Accounts table.
Account Update Status
The Account Translation tab enables you to review the account update status, that is, when
WebPortfolio last updated the account from the Financial Institution and if that update was
successful. It will also indicate accounts that do not contain data as of prior close by showing those
accounts in bold. You should review the detailed status of all accounts in bold and take corrective
action prior to finalizing your Import. If your account translation tab shows accounts in bold:
•
Click the Update… button
•
The Account Update Status dialog will appear as follows:
ByAllAccounts, Inc.
50
Custodial Integrator User Guide
Figure 10 - Account update status
This dialog shows all translated accounts in the upper table (WebPortfolio accounts) and it
operates similar to the WebPortfolio Account Update status dialog. All accounts that are out of
date are selected. The update status for the selected accounts is shown in the Update status
details section in the lower half of the dialog. You can review the status by scrolling through the
Update status details. You may also select an individual account to see the update status for just
that account.
If an account is showing a login failure then you must use WebPortfolio to update the account
login, password, etc. and test those credentials. For most other account update errors, a manual
update may correct the situation. To manually update accounts from within this dialog, select the
accounts to be updated in the upper table and click the Update button. CI will initiate the Update
process for the selected accounts and will provide a ‘working’ indicator in the lower portion of the
dialog:
You will not be able to perform any operations in CI until the Update completes.
completes, the Update dialog will show the latest status of the accounts.
Other Functions in Account Translation
Account translation provides these additional functions:
ByAllAccounts, Inc.
51
Once the Update
Custodial Integrator User Guide
•
The Refresh all button will refresh CI’s WebPortfolio account listing from the WebPortfolio
service and display it in the dialog.
•
The Print… button will print the account translations table.
•
The Select All button will select all of the account translations in preparation for a
subsequent action (e.g. Edit).
Security Translations
Creating Security Translations
The Security Translation tab contains translations of WebPortfolio securities to securities in your
target system (the system into which you are sending the data from CI). WebPortfolio security
identification originates with information from the Financial Institution. The Financial Institution
may provide a valid CUSIP, ticker, an invalid symbol, and/or a name. WebPortfolio attempts to
map this information to securities in the WebPortfolio security master. In the event that
WebPortfolio is unable to do this mapping then it will provide the original symbol and name
information presented by the Financial Institution.
There are several instances that may require you to create security translations in CI:
1. Your system uses a security identifier other than the one used in WebPortfolio.
2. Your system uses a different security type than the one used in WebPortfolio (e.g.
classifying a money market fund as ‘Cash and Money Fund’ vs. ‘Mutual Fund’).
3. The Financial Institution provides a symbol but WebPortfolio cannot locate the security in its
security master and does not know what the type of the security is (e.g. Cash, Mutual
Fund). If no translation is created, CI will use the security type OTHER for this security and
this may have undesirable consequences.
4. No security identifier (symbol) is available from WebPortfolio. CI will use the security name
unless a security translation is provided.
ByAllAccounts, Inc.
52
Custodial Integrator User Guide
Figure 11 - Security Translation
The Security Translation tab consists of two main sections. The upper section contains a single
table that shows security translations that have been defined and is used for viewing and
maintaining those translations.
ByAllAccounts, Inc.
53
Custodial Integrator User Guide
The lower section contains two tabbed areas for creating security translations:
Custom (shown above) this tab enables you to create security translations ad-hoc. This is the
tab you will use to create translations for cases 1 through 3 above.
Untranslated (shown below) – this tab shows WebPortfolio securities in the current download
for which no security symbol (Ticker or CUSIP) is available in WebPortfolio. If you do not define
a security translation for these securities then the ‘WP Name’ will be used. In the rare situation
where no name is available, the symbol column in the output file will contain only the unique
symbol string (defaults to 999999).
Figure 12 - Untranslated securities
You will create Security Translations for securities without symbols as part of your download
process.
Defining Security Translations Ad-Hoc
You may need to establish security translations even if the Financial Institution provides a symbol.
You can use the Custom tab in the Security Translation dialog to create a security translation.
Begin by going to the Security Translation tab in the CI Configuration dialog and Click Custom to
show the Custom sub-tab. The tab will appear as follows:
ByAllAccounts, Inc.
54
Custodial Integrator User Guide
Figure 13 - Security Translation: custom
The Custom WebPortfolio Security section of the dialog provides two ways for you to create a
custom translation: (1) specify the WP Ticker or CUSIP that WebPortfolio shows for this security, or
(2) specify the security name and the Financial Institution where the security is held. The second
option can also be use to create an account-specific security translation. Checking the Accountspecific checkbox will result in the display of an Account # field as shown in this picture:
The predominant use of a Custom security translation is case (1) above, symbol-to-symbol
translations. To make this type of translation, click the ‘WP ticker’ option so that the Custom tab
appears as follows:
ByAllAccounts, Inc.
55
Custodial Integrator User Guide
To create the translation:
1. Enter the symbol that CI is delivering to the output file in the WP Ticker field.
2. Enter a Translated symbol and select the correct Symbol type.
3. Enter a Translated security type
4. Click Add
5. The new Security Translation appears in the Security translations table at the top of the
dialog.
Most translations that fall into case (2) above will be created through the Untranslated Security
tab rather than through the Custom tab.
Resolving Untranslated Securities
During the Import step, CI will identify those securities for which it does not have a symbol. If any
such securities exist, CI will display a Security Translation… button in the Main View under the
Import step. Complete the following steps to define a Security Translation for Untranslated
Securities:
1. Click the Security Translation button. The CI Configuration dialog appears and shows the
Security Translation tab:
ByAllAccounts, Inc.
56
Custodial Integrator User Guide
Figure 14 - Security translatio n: Untranslated
2. The Untranslated WebPortfolio securities table contains the securities in this download
that could not be translated. In the example above, numerous untranslated securities are
shown.
3. Research the security and determine the correct symbol and type to use. Click the
Details… button to view the positions and transactions in the current download that refer
to the untranslated securities listed in this table.
4. Select a security to be translated from the Untranslated WebPortfolio securities table.
5. Enter a Translated symbol, Symbol type, and select a Translated Security Type.
6. Click Add
7. The new Security Translation appears in the Security translations table at the top of the
dialog.
ByAllAccounts, Inc.
57
Custodial Integrator User Guide
Account-specific Security Translations
The Security Translation sub-tabs (Untranslated and Custom) allow you to create account-specific
security translations. Use account-specific security translations for the rare cases where securities
with the same identifying information (WebPortfolio Symbol or WebPortfolio Financial Institution +
Security Name) that are held in different accounts (but usually at the same Financial Institutions)
are actually different securities. For example, you may have two annuity accounts at one Financial
Institution that both hold a security named ‘Stock Index’. The ‘Stock Index’ security held in one
account is not the same as the ‘Stock Index’ security held in the second account and typically the
securities will have different prices.
If you create account-specific security translations for a security you will subsequently be required
to establish a new account-specific translation for each account that contains the security. In
addition, you will not be able to establish a global security translation for that security. Therefore,
for a given security you are either using a global translation or multiple (one or more) accountspecific translations.
You will create security translations that are not account-specific most of the time. You must take
an additional action to establish an account-specific translation. This action is to check the
checkbox labeled ‘Account specific’ in any one of the Security Translation sub-tabs (Untranslated,
Custom). Once you check this, the Security Translation dialog will change to present an account
number for those Untranslated securities for which you may establish an account-specific
translation. The Custom sub-tab will change to allow you to enter an account number along with
WP Name and Institution as part of the security translation. Any account-specific translations you
have defined are shown in the upper part of the Security Translation tab and will display the
account number associated with the translations. Remember that while the ‘Account-specific’
checkbox is checked that all of the security translations you create will be account-specific. For
this reason, you should always establish all of your global translations first and then establish
account-specific translations.
Editing Security Translations
The Security Translation tab provides an Edit button to enable you to edit existing security
translations. To edit a security translation, select one row in the Security Translations table and
click the Edit button:
Figure 15 - Edit Security Translation
ByAllAccounts, Inc.
58
Custodial Integrator User Guide
The Edit Security Translation dialog allows you to change the right-hand side, or “output” side, of
the translation. As shown in the picture above, the security name “American International” at the
institution “John Hancock – Annuities (Annuity)” is mapped to ticker AIFJH with a type of MUTUAL
FUND. Change the values in the Translated security section to map this security name to a
different target symbol and then click Ok to save that change.
The Edit Security Translation dialog shows the key fields for the translation and will look different
for symbol-to-symbol mappings and for account-specific mappings. A symbol-to-symbol mapping
will appear as in the following picture:
Figure 16 - Symbol to Symbol Translation
An account-specific mapping will look like this, note that the Account number for the John Hancock
Annuities account that is mapped is shown:
Figure 17 - Account-specific mapping
If you need to change portions of the security translation that are not editable then you should
delete the security translation and create it again.
Data Download
You invoke CI daily (or less frequently) to download data from WebPortfolio and deliver it to files.
Each data download session will consist of the following steps:
ByAllAccounts, Inc.
59
Custodial Integrator User Guide
1) Review account status to determine if any accounts are failing to update or do not
contain prior day data. Resolve account update issues.
2) Import: download data from WebPortfolio
a. Resolve account translation errors: Review erroneous account translations and
determine why the account reference is no longer valid then delete or re-create the
problem account translations in the Account Translation configuration tab.
b. Resolve stale account data issues by performing a manual update.
c. Resolve untranslated securities (securities with no symbol information available
from WebPortfolio): Create a security translation in the Security Translation
configuration tab
3) Export: format and deliver the data to the output folder
4) Review the output files
5) Accept the Exported data. For trade-date based installations, this step tells CI that you
are satisfied with the downloaded data and in the future do not want to re-download
that same information. This step is relevant primarily for transaction download. For
settlement-date based installations, this step tells CI to revise the ‘tx date’ for accounts
forward to the current business day. If you fail to complete the Accept step you will
receive the same transactions plus any new transactions the next time you download.
Import
The CI main view will initially appear as in the following picture:
ByAllAccounts, Inc.
60
Custodial Integrator User Guide
Figure 18 - Main View
The main view shows the four main steps in the data translation process. Each step has several
parts: the step number, the step name, any status information for that step, and buttons to initiate
action related to that step. Various indicators are used to draw attention to steps. The green
check mark is used to indicate a completed step. The right arrow is used to indicate the current
step. Finally, a red X is used to indicate errors have occurred in a step and must be corrected
before the step can be completed.
As in the picture above, your main view should show that Setup is complete. Note that in the
example above, the WebPortfolio Account Status is “Not all updated”. Additionally, the Stale
Accounts… button is shown to indicate that not all translated accounts contain data as of prior
close. Accounts that have not updated today are those that you have configured for download but
have not been updated in WebPortfolio’s most recent data gathering cycle. This can occur if the
Financial Institution is unavailable or has changed in such a way that WebPortfolio is unable to
gather data, or the accounts have been scheduled for update but the update has not yet
completed. The latter case can occur for institutions that are scheduled at a relatively late time
(such as 7 A.M. Eastern or later) where the update might not be complete at the time you attempt
to do your download. Stale accounts can occur even if an account updated today but the Financial
Institution had not yet posted prior close information. CI will also detect stale accounts after
Import is performed and it has a chance to examine the status of positions in the accounts.
The Import step is the next to be performed, and this is indicated with an arrow to the right of the
step number. The import options are shown, and you may change them for any given Import.
First, you should resolve the accounts that failed to update. You can examine the account update
status in CI by clicking the Setup… button, then going to the Account Translation tab and clicking
the Update button. Once you have resolved the failed account updates, you may proceed by
clicking Import.
CI now performs the following steps that make up the Import:
1. Validate Account translations
CI checks for the existence of each WebPortfolio account referenced by an Account
translation. If the account is not found in WebPortfolio then the Account translation is
considered to be in error. If this error occurs CI will show the following dialog:
Click OK and the Import step in the main view will show the error as follows:
ByAllAccounts, Inc.
61
Custodial Integrator User Guide
The red X and ‘Error validating account translation table’ appears accompanied by the
Account Translation… button.
The Instructions window at the bottom of the main view will provide information on the
error:
To resolve this error, click Account Translation. The Account translations that are
erroneous will be shown in blue. Conf irm that the account has been removed from
WebPortfolio and, if so, delete the account translation and redo the Import.
2. Download data from WebPortfolio
CI uses your CI credentials to contact the WebPortfolio service and download the
requested data into the CI data store. In the event of a network connectivity error CI would
show a message similar to the following:
The Import step and instruction window change in appearance to show the error.
Wait for Internet connectivity to be re-established and try the Import again.
3. Check for stale account data (positions and prices)
CI checks the available “as of” date on positions within accounts to determine if data in
the account is stale. CI assesses an account as stale if more than 50% of its positions
have an “as of” date that is earlier than prior business day. If a financial institution does
not provide an “as of” date for the position then CI uses the position’s last updated date
(when WP last updated the position from the FI) to make this determinatio n.
If CI detects stale accounts it displays a Stale Accounts… button in the Import section,
adds the text ‘Some data is not up-to-date’ in the Import summary, and includes “Click
Stale Accounts… to view the last update date for the accounts” in the Instructions
window as shown here:
ByAllAccounts, Inc.
62
Custodial Integrator User Guide
To see which accounts are stale, click the Stale Accounts… button. Accounts shown in
the Account translation table in bold are stale. This may be true even if the account has
a last updated date of today (indicating that the data from the custodian was stale when
WebPortfolio gathered it today). Within the Account Translation tab, use the Update
button to perform a manual update of stale accounts. The accounts may remain stale
even after this update if there is a proble m with the data posted by the custodian. You
may still opt to proceed with the export and the stale account will be included as is.
Stale accounts will almost always be accompanied by stale prices and this is called out
using the View Data Issues button. You may also have accounts that updated
successfully and are not stale but that contain holdings that do not price daily and so
some of the prices are stale. Press this button to see more detail on how many holdings
in each account are stale and which prices are stale and will not be included in the price
file.
ByAllAccounts, Inc.
63
Custodial Integrator User Guide
The top table shows those accounts where at least 50% of the holdings do not contain
prior day business values. The lower table shows securities for which there are no prior
day business prices and for which, using your CI Configuration for prices, CI was unable
to determine a price for these securities. No entry will be included in the price file for
the securities listed in the lower table in this view. In the above example, the ICMA 401
account has four holdings that do not contain prior day business values. Also for this
account, there are three securities for which a prior day price could not be determined.
You can print the information in this view using the Print… button. You can also save the
information to a .CSV file using the File… button.
4. Preview prices to be delivered to files
If CI has any prices to be delivered, the ‘View Prices’ button will appear in the main view
after the Import button is pressed:
ByAllAccounts, Inc.
64
Custodial Integrator User Guide
Press the View Prices button to see a preview of your price data:
The Security Prices view shown above lists the information to be written to the price file
(symbol and price) as well as supporting information, including the source of the price and
the date of the price. The Source column identifies where the price came from and it can
contain one of the following values:
•
Third party – the price comes from ByAllAccounts third party pricing provider. As
of February 2008 this provider is Interactive Data Corporation (IDC).
•
Institution – the price comes from the financial institution where the security is
held.
•
Calculated – no price was available from the third party pricing provider or from
the institution so one was calculated. You configured in the CI Advanced Prices
section that prices should be calculated if they were not available from one of
these sources.
•
Override – one or more other advanced price configuration settings is in effect
(from CI.ini file as well as from CI Advanced Prices section) and these settings
caused the price to be set as it is shown. This source value will be less common
than the three listed above.
If you opted to utilize prices from a business day that is earlier than the prior business day
(when no better price is available) then these prices will be shown in this view but the ‘Price
as of Date’ value will identify when the price is ‘as of’. Prices that are not as of prior
business day are also shown in bold.
You can print the information in this view using the Print… button. You can also save the
information to a .CSV file using the File… button.
5. Translate data
Applies the Security Translations you have established previously to convert
WebPortfolio security symbols. CI reports a warning during the Import process if one or
more WebPortfolio securities do not have a symbol and also have no Security Translation.
These securities are referred to as untranslated securities. When untranslated securities are
encountered, the Import step will change in appearance as shown in this picture:
ByAllAccounts, Inc.
65
Custodial Integrator User Guide
The Import is considered a success (indicated by the enabling of the Export button and
the disabling of the Import button) and you can proceed to the Export step even if
untranslated securities or stale accounts exist. If you want to resolve the untranslated
securities prior to Export click on the Security Translation button. This will bring up the
Configuration dialog positioned to the Security Translation tab.
The Instructions window at the bottom of the main view will provide information on the
erro r:
Untranslated Securities exist when a WebPortfolio security does not include a Ticker or CUSIP.
This can occur when the Financial Institution does not provide a security identifier and
WebPortfolio is unable to determine a corresponding security through name evaluation. CI will
have only a name and Financial Institution for the security (e.g. BLUE CHIP GROWTH held at My
401(k)). You can resolve this untranslated security by creating a CI Security Translation.
Export
The Export step is enabled once the Import step completes without error. Stale accounts and
untranslated securities are considered to be only warnings and will not prevent you from Exporting.
The main view will appear as in the following picture:
ByAllAccounts, Inc.
66
Custodial Integrator User Guide
Figure 19 - Export
To perform the Export step, simply click Export in the main view. The Export is now performed
and the main view changes as follows:
ByAllAccounts, Inc.
67
Custodial Integrator User Guide
Figure 20 - Export complete
The Export step takes data from the CI database and produces files in delimited file format
according to the type of data: Account, Transaction, Position, Security, and Price. CI only
generates files for the types of data you selected in the Import step.
Each file is then delivered
into your specified Output folder. The Instruction window lists the folder and filename for the files
that were written (see Figure above). The Instructions window will also notify you of any errors
that occurred during the Export.
Accept Exported Data
This step allows you to confirm to CI that you have completed processing the data that CI just
delivered to the output files. The main purpose of the Accept step is to update CI’s internal
transaction ‘counter’ so that the next time CI is run it will only download transactions that have not
yet been downloaded.
Click Accept to perform this step. CI confirms that the accept was completed:
ByAllAccounts, Inc.
68
Custodial Integrator User Guide
The main view transitions back to its initial state and CI is now ready to perform another data
translation cycle.
If you attempt to exit CI without doing the Accept, CI will warn you with the following:
To complete the Accept, press Yes. To exit without Accepting, press No. To return to CI without
Accepting or Exiting, press Cancel.
Additional Translation Information
This section documents additional considerations for CI data translation activities.
Financial Institution Support
The WebPortfolio service provides support for over 2,600 financial institutions as of June 2007.
Data from these services is used primarily for the WebPortfolio Application by Registered
Investment Advisors for client reporting and investment management. BAA has recently been
enhancing support for select financial institutions to enable the data to be used for import into an
accounting or reconciliation system. Financial institutions with this enhanced support are termed
validated. Financial institutions are validated on a per-customer basis. Your FI validation status is
visible to you from within the Account Translation tab in CI. BAA automatically queues for
validation any accounts you enter in WebPortfolio at an institution that had not been previously
validated for you. CI will show such institutions with a validation status of ‘in process’. When BAA
completes the validation, you will receive an ema il notifying you of this event and the accounts will
now appear in CI with their new validation status, typically validated indicating they are acceptable
for use with CI.
Data Retrieval Scheduling
WebPortfolio gathers data from Financial Institutions each day starting at 1 A.M. Eastern. Some
Financial Institutions may be scheduled for gathering at a later time for data availability reasons.
In general, data from the Financial Institution for day n is available on day n+1 after 8 A.M.
Eastern. If WebPortfolio encounters errors gathering data for an account then CI will show that the
account was not updated. Check the Setup section in the CI main view and the Account Status tab
of the CI Configuration dialog for more information.
ByAllAccounts, Inc.
69
Custodial Integrator User Guide
APPENDIX A - ADMINISTERING CUSTODIAL INTEGRATOR
The following is a list of important administrative information regarding your Custodial Integrator
installation:
1. Internet connectivity
CI retrieves data from WebPortfolio over the Internet. It is important to have a reliable
Internet connection on the computer where you will run CI. You must also properly
configure your proxy login and password in runCI.bat or ensure that your firewall will not
prevent CI from contacting the WebPortfolio service over https. Changes to your Internet
proxy access or firewall configuration could require configuration changes in CI. Please
refer to the Custodial Integrator Installation Guide for more information on this topic.
2. Disk space
CI requires free disk space on the drive where it is installed for temporary file storage. For
the typical CI installation, the daily disk space requirements will be very small. We
recommend that you maintain at least 50 MB of free space on the CI drive to provide
sufficient space for these files with a healthy safety margin.
3. CI logs
CI maintains a log of activities and errors in the \log folder (ex:
C:\Program Files\Custodial Integrator\log). If CI appears to be malfunctioning, please
check the most recent log file for errors. CI automatically removes all logs that are older
than 14 calendar days.
4. Database backup
CI’s database is used primarily for temporary storage. There is some data that is persistent
and that should be protected from loss so that it will not need to be recreated. This data
consists of Account translations, Security translations, and some of CI’s internal state
information. Please refer to your SQL Server documentation for information on how to
back up and restore a SQL Server database.
ByAllAccounts, Inc.
70
Custodial Integrator User Guide
APPENDIX B – MULTIPLE CI U SERS
There are two main ways that multiple users within a single firm use Custodial Integrator:
-
Job Sharing - All users will process the same set of accounts. This includes the case where a
single individual has primary responsibility for the accounts and one or more individuals have
backup responsibility in the event that the primary user is unavailable to process the accounts.
-
Independent - Each user will process a distinct set of accounts.
The following sections describe how to install and configure CI for each of these usage types.
Job Sharing Configuration
Multiple users have access to the Custodial Integrator application but will never use the application
simultaneously. Install Custodial Integrator on each user machine.
Independent User Configuration
Multiple users will process separate sets of accounts and there is no overlap of account
responsibility. This usage type requires a separate and complete Custodial Integration installation
for each user where each installation has its own CI database. Complete a standard CI installation
for each user but select an alternate database name during each install.
ByAllAccounts, Inc.
71
Source Exif Data:
File Type : PDF File Type Extension : pdf MIME Type : application/pdf PDF Version : 1.2 Linearized : No Page Count : 74 Creator : CI_User_Guide.doc (Read-Only) - Microsoft Word Create Date : 2010:07:26 14:14:06 Title : CI_User_Guide.doc Author : edickau Producer : Acrobat PDFWriter 5.0 for Windows NTEXIF Metadata provided by EXIF.tools