Vision Portfolio Management Application Feeds

Overview

A standardized interface for loading security, portfolio, and related investment data into Vision is provided as part of the Portfolio Management Application Layer. DataFeed classes have been defined to translate data from sources external to Vision into Vision objects. All feeds correspond to regular, tabular structures that are loaded into Vision from external files and/or updated directly from other sources such as a spreadsheet range or a relational database.

DataFeed Formats

A separate DataFeed is defined for each format of external data you wish to load into your Vision database. A data feed consists of one or more records that are separated by carriage-return/line-feed. Each record contains one or more fields, normally delimited by a tab character. In addition:

  • The first record is a header containing tab-delimited field identifiers which correspond to the data items to update.
  • The header record can contain any number of identifiers in any order. If an identifier is invalid, the data in this column will be ignored.
  • The field identifiers Entity, EntityId, and Id can be used interchangeably to identify a column containing an entity identifier. The field identifiers Currency and CurrencyId can be used interchangeably to identify a column containing a currency identifier. Field identifiers are not case sensitive.
  • Numeric values can be supplied with any number of decimal places.
  • Dates can be supplied in a number of default formats:
      YY (12/31 of supplied year)
      YYMM (end of month in supplied year)
      YYYYMMDD
      MM/DD/YY
      MM/DD/YYYY
      MM-DD-YY
      MM-DD-YYYY
    The initial date can be indicated as 10101 or Default. Additional date formats are available with Configuration Files.
  • Values that are not available should be supplied as blanks or the string NA.
  • Records that are blank or start with a # as the first non-blank character are ignored.

DataFeeds have been subdivided into a number of categories that represent different ways to map external data into Vision. These include:

--- DataFeed Categories ---
Master Feeds create new entity instances and refresh key properties in entity instances.
EntityExtender Feeds update sets of data used to supplement an existing entity, potentially over time, such as pricing data for Security.
Transaction Feeds create, cross reference, and generate summary statistics for classes associated with two or more entities such as Holding.
Alias Feeds establish multiple aliases for existing Entity instances.
XRef Feeds establish alternative identifiers for existing Entity instances.
Membership Feeds update and cross reference one-to-many relationships between two Entity instances such as Company and Industry over time.
RangeGroup Feeds define and update numeric ranges and categorize Entity instances into the appropriate range group.
Support Feeds define access names and rules for data items.
Schema Feeds define new core classes, properties, and data feed classes.

Pre-defined Feed Summary

A number of data feeds have been pre-defined to support the Portfolio Management Application. These feeds are summarized below and are described in detail in individual fact sheets.

Master Feeds

These feeds are used to create new entity instances and refresh key properties in entity instances:

Entity Extender Feeds

These feeds are used to update sets of data used to supplement an existing entity and usually populate DataRecord classes such as PriceRecord and DivRecord.

  • AnalystEst updates earnings estimate data supplied by an analyst for Company instances.
  • DivFeed updates dividend data for Security instances.
  • EconFeed updates economic data for Country instances.
  • ExchangeRateFeed updates exchange rate data for Currency instances.
  • FundamentalA updates annual fundamental data for Company instances.
  • FundamentalM updates monthly fundamental data for Company instances.
  • FundamentalQ updates quarterly fundamental data for Company instances.
  • IndexAccountBuilder updates data associated with rule-based creation of IndexAccount holdings.
  • PriceFeed updates pricing data for Security instances.
  • SplitsFeed updates stock split data for Security instances.

Transaction Feeds

These feeds are used to create, cross reference, and generate summary statistics for classes associated with two or more entities such as Holding.

  • HoldingsFeed creates Holding instances and cross references the holdings over time by Portfolio and Security.

Alias Feeds

These feeds are used to establish multiple aliases for specific entity instances.

XRef Feeds

These feeds are used to load alternative identifiers.

  • SecurityXRef is used to load alternative identifiers for Security instances.

Membership Feeds

These feeds are used to update and cross reference one-to-many relationships such as company-to-industry over time.

RangeGroup Feeds

These feeds are used to define and update numeric ranges and categorize entities into the appropriate range group.

  • McapGroups assigns Security instances to a group based on market capitalization.
  • PbGroups assigns Security instances to a group based on price-to-book ratios.
  • PeGroups assigns Security instances to a group based on price-to-earnings ratios.

Support Feeds

These feeds are used are used to name data item access paths and define rules for inclusion in various applications. the appropriate range group.

  • DataAccessPathFeed defines links to navigate between classes.
  • DataItemFeed names and defines access rules for data items.

Schema Feeds

These feeds are used to define new core classes, data feed classes, and properties.

  • ClassSetup is used to create new Entity, DataRecord, and LinkRecord subclasses.
  • PropertySetup is used to define properties that can be uploaded for any of the core classes.
  • MasterFeedSetup is used to define new MasterFeed subclasses.
  • EntityExtenderFeedSetup is used to define new EntityExtenderFeed subclasses.
  • TransactionFeedSetup is used to define new TransactionFeed subclasses.
  • MembershipFeedSetup is used to define new MembershipFeed subclasses.
  • AliasFeedSetup is used to define new AliasFeed subclasses.
  • RangeGroupFeedSetup is used to define new RangeGroupFeed subclasses.

Starter Feeds

To successfully use the supplied Headstart Applications, your initial feeds should include security, portfolio, and company masters, holdings data, and supplemental data such as industry and sector masters. The recommended starter feeds are described in the document, Portfolio Management Application Startup. These feeds provide enough information to use most of the starter portfolio reports without modification.

Reject Files

A reject file is generated when a data feed record is not processed for any reason. The reject file is created in the working directory with the same file name as the data feed appended by .rejects. The first line of this file is the field order list used to process the feed followed by the records that were not processed by the feed. Comments at the end of the record indicate a rejection reason where possible. Once the reject reason has been resolved the reject file can be used as the data feed. The configuration file option rejectFileName can be used to customize the location and name of the reject file.

Temporary Changes via Excel

Vision data feeds provide a potent means for an analyst that wants to use the Vision browser applications on dynamically generated information. For example, an analyst may want to create a composite account on the fly that can be used as a benchmark for comparison with an existing portfolio. You can use the CompositeAccountMaster feed to define CompositeAccount instances and the CompositeAccountMembers feed to update the composition of CompositeAccounts over time. You can then run the Account Comparison or the Characteristics application from the browser to compare these newly created composites with other portfolios.

Your Vision installation comes with a custom menu named VUpload. If you have installed it, this choice will appear in the menu bar at the top of your Excel spreadsheet. If you do not see this choice, you will need to install the Add-In.

The VUpload component allows you to temporarily make private modifications to the database using the available data feed formats. You can use VUpload to modify existing data, create new instances, and define new structures including new data feed formats. Several data feeds are also available for controlling display aspects of the applications you view through your browser.

When you exit Excel, your changes to the database are not saved. You can save your changes as data feed files which can be saved permanently by the Vision Administrator using the tools described below. The Excel Add-Ins are described in detail in the document Vision Analyst Tools.

Permanent Changes via VAdmin

Your Vision database is designed to store portfolio, security, holding, and related information over time. Although you can load this data temporarily on an as needed basis using the techniques described above, you will most likely want to permanently commit data to the database on a regular basis so that the information can be shared by many users. The Vision Administrator Module provides a number of tools for coordinating updates to your Vision database and performing various database administration functions.

The Vision Administrator Module is described in detail in the document Vision Application Administration.

Configuration Files

A configuration file is a standard text file which contains directions that override the default settings for loading a data feed file. The configuration file must have the same name, through the first '.', as the file it accompanies. For example,

    Feed File name Configuration File name
    Holdings.txt Holdings.cfg
    Holdings.19981105.txt    Holdings.cfg
    Holdings19990315 Holdings19990315.cfg
Note that the naming convention used in the second example allows for one configuration file to be shared with multiple holdings files.

Configuration files contain one or more lines, where each line contains two fields: a configuration option and a value. The option and value are separated by either the tab character or vertical bar. The data item fields themselves are separated by a comma. For example, the default delimiter character for data feed files is either the tab or vertical bar. You can override this in the configuration file using: 

    delimiter	,

If this entry exists in your configuration file for the data feed, the data feed loader will assume that the fields in your feed are separated by commas.

Configuration files can also be used with loadFromFile and bulkLoadFromFile.

The current configuration file options are described below:

Option Description Default
fieldOrderList Sets the order of the field headings. First line of file
headerLineNumber Indicates row number containing header. 1
currency Sets currency value. none
rejectFileName Sets name and location of reject file. File name appended with .rejects in working directory
delimiter Separates data columns. tab or vertical bar
naTest Format of NA values in the feed, multiple values can be supplied. NA
naPatternTest Uses regular expressions to determine NA value. none
skipTop Skips rows at the top of the feed file. 0
skipBottom Skips rows at end of feed file. 0
skipChars Eliminates specific characters from data being uploaded. none
maxRecords Limits the number of rows that the feed uploads. All
autoExpire Adds a 'default' value to a time series after last 'real' value, based on a supplied expiration period. off
dateFormat Indicates the format of the digits supplied in the 'date' field. Variety of default formats as described in DataFeed Formats.
asofDate Sets value for any date-sensitive data. Date provided in data records or if none, current date.
asofDateLineNumber Indicates the line in the data file that contains the as of date. Date provided in data records or if none, current date.
adjustmentDate Sets the default value of the adjustment date. Date provided in data records or if none, current date.

The following situations show where a configuration file is necessary and provide examples of how it is used.

    fieldOrderList

      If a feed has an incorrect headerline, the option fieldOrderList should be used to change the headers. More specifically, if a feed contains the header symbol for the data item that Vision recognizes as id, you can use the fieldOrderList to specify the correct item names. Following is an example CompanyMaster feed and configuration file:

        Sample File: 

        symbol | name          | country
GM     | GeneralMotors | US
        Sample Configuration: 

        fieldOrderList | id,name,country

      fieldOrderList can also be used to eliminate columns of data from your feed. For example, if a file contains five columns of data and you only wish to use the first, second, and fourth columns, you could set up your configuration file to include only the items you want:

        Sample File: 

        id | name | ticker | country | currency

        Sample Configuration: 
        fieldOrderList | id, name, , country

    headerLineNumber

      Another use of the configuration file is to provide headings to files that do not have them. In addition to fieldOrderList, you need to set headerLineNumber to 0 so that the top line of the instances is included (by default the first line is assumed to be a header).

        Sample File: 

        GM  | GeneralMotors               | US
IBM | International Business Mach | US

        Sample Configuration: 
        fieldOrderList   | id,name,country
headerLineNumber | 0

    currency

      The configuration file option currency will set the currency value for all records in a feed that have no value or NA for currency.

        Sample File: 

        id   | name                      | currencyId
MRK  | Merck & Co Inc            | NA
GM   | GeneralMotors             | 
MHP  | McGraw-Hill Companies Inc | CAD

        Sample Configuration: 

        currency | USD
      This example will set USD as the currency for MRK and GM but CAD will remain the currency for MHP. In addition, currency can be used to set the currency value for all records in a feed where a currency field does not exist.

    naPatternTest

      This configuration file option will convert data to NAs based on the exsitence of the pattern in the data. The pattern can include the special pattern matching symbols supported as "regular expression". For example:

        Sample File: 

        id | currencyId | sharesOut | latestMarketCapUS
45920010 | US | 9999 | 7595900
00036110 | US | 2400 | 4499000
        Sample Configuration: 

        naPatternTest | 9[59]9
      This example will load NA for sharesOut and latestMarketCapUS for security 45920010 but will load all of other values. WARNING: Practice extreme caution when using Regular Expressions - due to their nature it is easy for a regular expression to include more than intended as seen in the above example where it might not have been the intent for security 45920010's latestMarketCapUS value to be loaded as NA.

    skipTop

      Another use of the configuration file is for a multiline header. For example, if a date has been included as a seperate header line, you need to include skipTop set to 2, so that the two headers are not processed as actual data:

        Sample File: 

        98/06/30
symbol | name          | currency
GM     | GeneralMotors | USD

        Sample Configuration: 

        fieldOrderList | id,name,currency
skipTop        | 2

    skipChars

      If a file has data that has quotation marks surrounding specific values, uploads will be not be processed properly. To prevent this, you can use the option skipChars.

        Sample File: 

        "id" | "name"           | "currency"
"GM" | "General Motors" | "US"

        Sample Configuration: 
        skipChars | "

    autoExpire

      The autoExpire option provides the capability to add a default value to a time series as of a certain offset for feeds associated with a DataRecord. Since by default Vision returns the last available value for a time series, this option is useful if you prefer to retrieve NA's rather than old data. For example, assume all companies in your database have funDataM data as of 5/31/99 and your June 30 file has data for active companies, but does not include data for companies that no longer exist. In these cases, you will want to expire your FundMData after 1 month. The autoExpire option can be used in the configuration file to specify an expiration period such as 1 monthEnds. This indicates that any company whose most recent 'real' data point is from more than 1 monthEnds ago should get a 'default' data point added as of the month-end following its last real date:

        Sample File: 

        EntityId | date | currencyId | pe 
abc | 06/30/99 | USD | 7.39
mno | 06/30/99 | USD | 4.56

        Sample Configuration: 

        autoExpire | 1 monthEnds | DateOffset

      In the above example, FundMData values for all companies other than abc and mno whose most recent "real" data is 5/30/99 or earlier, will be updated with the default value NA for 6/30/99.

      The second column in the configuration file indicates the expiration period and the third column indicates that the second column should be treated as a DateOffset. You can use any DateOffset as a value. The most likely values are: 

        1 monthEnds
3 monthEnds
12 monthEnds
1 businessDays

      Note that 3 monthEnds is the same as 1 quarterEnds when you are dealing with calendar data. When you are dealing with fiscal data, 3 monthEnds puts you at the end of the next fiscal quarter whereas 1 quarterEnds puts you at the end of the next calendar quarter

    dateFormat

      If a file contains dates in any of the following alternative formats: MMYY, MMYYYY, MMDDYY, MMDDYYYY, or YYYYMM, you will need to use the dateFormat option. Note: this only applies to the field named 'date'. For example:

        Sample File: 

        memberID | groupID | date
0036110  | 560     | 121994
        Sample Configuration: 
        dateFormat | MMYYYY
      The format YYYYMMXX will convert the supplied date in YYYYMMDD format to end of month format. For example, if you use this configuration file option with a feed with date values of 20000805 and 20000825, both will load as 20000831.

    asofDate

      If a file does not include a date, you can use the asofDate option. For example:

        Sample File: 
        memberID | groupID
0036110 560
        Sample Configuration: 

        asofDate | 19990430

    asofDateLineNumber

      Alternatively, you can include the date in the feed file and use the asofDateLineNumber option. For example:

        Sample File: 

        19990430
memberID | groupID
0036110  | 560
        Sample Configuration: 

        asofDateLineNumber | 1
HeaderLineNumber   | 2

    adjustmentDate

      If the data in your feed file has been adjusted to reflect splits but adjustmentDate is not included as part of the data record, use this option to set the adjustment date. For example:

        Sample File: 
        entityId | date | currencyId | close | volume 
abc | 09/30/99 | USD | 37.39 | .59
mno | 09/30/99 | USD | 44.56 | .38
        Sample Configuration: 

        adjustmentDate | 19991231