File formats

From Flametree Technologies
Jump to: navigation, search

Contents

Introduction

This chapter describes the formats for the various data files used by FIA.

All files used by FIA are comma or tab-delimited ASCII, without leading or trailing rows. Therefore, no data field (such as a security name) can include a comma or a tab. Standard editing tools such as Excel or Notepad can be used to edit and update files.

In most cases, the only files required by FIA are a security file and a portfolio file.

The security file contains details about the securities held in your portfolios, while the portfolio file contains information about the holdings and returns of these securities. If you are modeling a hierarchical portfolio with several levels of security grouping, the structure of the portfolio is also supplied in the portfolio file.

Regularly updated data for sovereign zero coupon yield curves for most currencies is available to all users as part of the subscription package. However, should you wish to use your own data, we also describe the formats for these files.

Depending on the complexity of your requirements, you may need to refer to other chapters for more detail. For instance, Chapter 6 describes the particular requirements for each security type, while Chapter 8 and Chapter 9 show how to set up nested portfolios and synthetic securities.

Numerous sample data files are supplied on our website to help you set up your own files, and we suggest you refer to these while reading this chapter.

Setting up the security file

The security definition file records quantities such as identifier, name, type, currency, maturity, coupon and credit rating for the securities in your portfolio.

Each line in the security definition file sets up an individual security. In some cases, a security definition can run over multiple lines if its characteristics change over time. In this case, the security ID will be the same on each row, but the security’s effective date will be different on each row.

A security only need be set up once in the security file, even if it used in many portfolios, while unused security definitions are ignored. Therefore, you can use a single security definition file for all your portfolios and benchmarks and add to it over time as the number of securities you trade increases.

Non-security-specific columns

The first seven columns in the security file have the same format for all securities, as follows:

Field Description Example
Security ID Unique identifier for security CGL_10_150206

DG0000151510

CASH

Security name Name of security CGL 10% 15th Feb 06
Custom classification bucket(s) Name of bucket and name of value. Values must be assigned using an '=' sign. Any number of buckets can be set up, but 'bucket=value' assignments must be separated by a vertical bar character. COUNTRY=USD ¦ SECTOR=INDUSTRIAL
Effective date Date from which security definition is active 1/1/2005

[Blank]

Security type Name of security type BOND
Currency Currency in which security is denominated AUD

USD

Residual sector Name of sector to which residual return will be written for the current security Equity return

Country spread Duration

Security ID

A security ID can be made up of any combination of characters, including spaces and punctuation marks. The only restriction on a security ID is that it must be between 3 and 256 characters in length, and cannot include a tab or a comma character, since these are used as column delimiters in the security definition file. Suitable security IDs include CUSIP or SEDOL codes, an internal code, or a descriptive name.

Security name

A security name is a user-friendly identifier for the security. Both the security name and the security ID will be shown on reports that refer to individual securities. The name must be between 3 and 256 characters in length and cannot include a tab or a comma character.

Custom sector

The custom sector field supplies an extremely flexible way to add as much additional information as you require to the current security's definition. Sector fields are of the form

HEADER = VALUE

where HEADER is the name of the class of variables to be assigned, and VALUE is the value of that class for the given security. HEADER and VALUE must always be linked with an '=' sign, but spaces are ignored.

For instance, suppose you wanted to record each security's country, and to have this appear on FIA's reports. (FIA currently records security currencies and yield curves, but not specific countries.) Simply fill this field with the entry

Country = (country name)

where (country name) is the country you want to associate with the current security. The header Country will then appear on reports that list individual securities, and the assigned value of 'Country' will be shown under that heading.

If a country has been set up for some securities but not for others, the value Undefined will be written to those securities that have not have a value assigned.

The field is also available for use by other parts of the program. For instance, if you want to use a custom classification in the FIA's drill-down reporting, use the header value in PartitionList. The attribution and exposure results can then be decomposed by the given category. (For more information, see 'Configuring FIA'). Custome classification schemes can also be used for asset allocation calculations (see Asset allocation).

The number of categories that can be assigned to a given security is unlimited. To assign multiple categories, write a string of the form

HEADER1 = VALUE1 ¦ HEADER2 = VALUE2 ¦ HEADER3 = VALUE3

where the assignments are separated by a ¦ character. For instance, to declare that a particular security was issued in the US, that its sector is INDUSTRIAL, and its home state is Utah, write the following string to the classification field:

COUNTRY = US ¦ SECTOR = INDUSTRIAL ¦ STATE = Utah

Effective date

The effective date is the date at which the security’s settings became active.

Most of the securities you hold will not have any structural changes over time. In this case, the ‘effective date’ can either be left blank, or set to some date in the far past.

However, some securities do have properties that may change over time. For instance:

In each case there will be a change in some structural value of the security. For the above examples, the changed entries will be credit rating, CPI factor, and pricing type, respectively. FIA allows the user to handle such occurrences by entering a new definition for the security that reflects the required changes, using the same security ID but a different effective date. More than one change in the security’s parameters can be made at the same date.

For instance, consider a bond that was downgraded from a AAA rating to a AA rating on the 15th September 2008. This is represented by two records in the security definition file:

Column 2 Column 3 Column 5
... MEGACORP_2020 ... AAA ...
... MEGACORP_2020 15-Sep-2008 ... AA ...


The first row shows the bond initially had a AAA rating. The effective date of this setting is left blank, so in the absence of any other entries it will apply to all dates.

The second row shows that the bond's rating changed to AA on the 15th of September 2008. This rating will be used for all analysis on and after that date.

Any number of security updates can be made to a given security, but the program checks that there are no duplicate security ID/effective date combinations.

Entries in this column must conform to the relevant date format string in the project’s configuration file (see Chapter 4, Setting up the configuration file).

Security type

Security type is a string identifier that identifies the type of security. Permissible values include CASH, BILL, BOND, MBS and many others.

The security type is not case-sensitive. Please refer to chapter 6 for a full list and more information about how to set up each type of security. The list will grow in future releases.

As described above, a security can have more than one type over time. For instance, a convertible bond that changed its type from BOND to EQUITY on 1st July 2009 would have its first three columns as follows:

Column 1 Column 2 Column 3
CONVERTIBLE_2015 BOND ...
CONVERTIBLE_2015 01-Jul-2009 EQUITY ...

Currency

Currency is a string identifier that denotes the currency in which the security is issued.

FIA uses the standard three-letter ISO 4217 format for currency codes. A table of commonly used currencies is shown below.

Currency ISO 4217 code
US dollar USD
Euro EUR
Japanese yen JPY
British pound GBP
Australian dollar AUD
New Zealand dollar NZD
Swiss franc CHF

The full list is available at http://en.wikipedia.org/wiki/ISO_4217.

Residual field

The residual field allows the user to direct residuals to named buckets, rather than just to a generic residual category.

Security-specific data requirements

The data requirements for each type of security vary widely according to type. In addition to the entries mentioned above, security-specific fields for some common types are as follows:

For a full description of the data requirements for any security type, refer to chapter 6.

Setting up the returns file

The returns definition files supply information about your portfolio and benchmark and how quantities such as security holdings, weights and returns change over time.

Any number of rows and columns may be entered into the file, but unused data will be ignored. All rows in the returns file must conform to the following format:

Column Field Type Example Required?
Column 1 Date Date 02-Sep-2010 Yes
Column 2 Portfolio String STF1 Yes
Column 3 Security_ID String MEGACORP 15082015 Yes
Column 4 Market weight Real 1200 Yes
Column 5 Base currency return Real -0.0043 Yes
Column 6 Local currency return Real -0.0043 Yes
Column 7 Yield to maturity Real 0.0544 Only for perturbational securities
Column 8 Modified duration Real 4.33 Only for perturbational securities
Column 9 Convexity Real 23.99 Only for perturbational securities (may be omitted)

Each row in the file shows that:

Dates

Entries in the Date column must conform to the relevant date format string in the project’s configuration file (see Chapter 4, Setting up the configuration file).

Data may be supplied at any frequency you wish, including daily, weekly or monthly. We recommend daily data for most accurate results. If a few days are missing, FIA will use the most recent data available up to that point for attribution.

Portfolio

This field supplies the name of the portfolio that holds the named securities. The portfolio name is used in reporting. A returns file can contain data on any number of portfolios. However, only one portfolio can be set up as a root portfolio.

Like a security name, a portfolio name must be between 3 and 256 characters in length, and cannot include a tab or a comma character, since these are used as column delimiters in the returns file. In addition, a portfolio name cannot be the same as that of an existing security.

FIA is designed to process two such files at the same time, usually referred to as the portfolio and the benchmark. The names of the files may be different to the name of the root portfolio within the file.

Security ID

The Security field supplies an identifier code for the current security. This must match a security ID that has been set up in the security definition file, and an error is flagged if no matching definition is found.

Note that you can use the name of another portfolio in the security field in order to set up a nested portfolio structure.

The portfolio name need not match that of a security.

Market weight

For securities, this field is used to supply the market exposure of the current security. As long as the same scheme is used for all securities, market weight can either be supplied in dollars, as an asset allocation between 0 and 1, or in some other form (note that negative weights are permissible).

Some securities, particularly futures, can have a zero market weight but a non-zero effective exposure. The effect this has on performance calculations is handled automatically by FIA.

Base currency return and local currency return

These fields specify the return of the security over the previous period in its own currency and in the currency in which the overall return of the portfolio is measured. For instance, if your data is supplied at a weekly frequency, then the security returns should be the base return and local return made over the previous week. These figures should include returns made from all sources, including accrued interest.

By supplying the weights, base and local currency returns of each security, the user ensures that the overall returns shown in FIA’s reports are identical to the overall returns calculated by the source performance data system.

Yield

This optional field allows the user to supply a YTM (yield to maturity) for the current security. This is useful in ensuring that FIA’s attribution calculations are as accurate as possible. If no yield is supplied, FIA will calculate a YTM from the appropriate yield curves for the current security, but this may differ from the true YTM due to security-specific effects. The main impact of not supplying a yield will be seen in the time return data and in (possibly) larger residuals at the security level.

Modified duration

This optional field allows the user to supply a modified duration, or interest-rate sensitivity measure, for the current security. This can be useful for highly complex securities that may be complex to define and model. In such cases, it may be easier to set up the security as a pass-through security, to supply an external modified duration and to use that in the attribution analysis.

Other column definitions may be added in future.

Nested portfolios and carve-outs

Portfolio nesting is an extremely versatile feature that is central to FIA’s capabilities.

Instead of supplying the name of a security in column 3, you can supply the name of another portfolio that has been set up in the same file. The market weight is then interpreted as the fraction of that portfolio you hold. This is a very simple but powerful way to model hierarchical portfolio holdings.

For instance, suppose you manage both a managed fund called STATFUND1, and a unit trust called TRUST1. If the fund holds 50% of the unit trust, then all you need to do is to set up both funds in the same file under their own names, and to add a single line at each date the holding is active:

[Date] [STATFUND1] [TRUST1] [0.5]

For subportfolios, this field supplies the fraction of the subportfolio that is held in the current portfolio.

Note that the market weight of a subportfolio can be more than 1. For instance, if we have set up a swap INTSWAP as a subportfolio with market value $1, then a $100,000 holding in that swap is defined by using the subportfolio INTSWAP as a security with a market weight of 100,000.

The fractional holding of a subportfolio can also vary over time. For instance, if the holding in TRUST1 increases from 50% to 70% at a given date, then that new holding should be used at the appropriate date.

Portfolios can be nested to any depth and at any level of complexity.

The nested portfolio capability allows carve-outs, easily customized benchmarks, separation of strategic groupings of bonds, and synthetic securities. The topic is covered in more detail in Chapter 8 and Chapter 9.

Setting up the yield curve file

FIA supplies daily sovereign zero coupon (or spot rate) yield curve data for the major markets, automatically updated every 24 hours, so the following section is provided for reference. You need only provide yield curves if (i) you are modeling securities for which we do not provide a sovereign curve; (ii) you need to use a particular set of rates; (iii) you are running credit analysis (FIA can handle multiple credit curves for the same market).

The yield curve file supplies information about zero coupon curves for various ratings over time. Any number of rows may be entered into the file, but there must always be exactly five columns as shown below.

All rows in the yield curve file must conform to the following format:

Column Field Type Example Required?
Column 1 Curve name String AUD_CURVE Yes
Column 2 Credit rating String AAA Yes
Column 3 Maturity Real 0.25 Yes
Column 4 Date Date 31/12/2000 Yes
Column 5 Zero coupon rate Real 4.554 Yes

Each row in the file contains the zero coupon rate for a given maturity, credit rate, date and curve.

Date

Entries in the Date column must conform to the relevant date format string in the project’s configuration file (see Chapter 4, Setting up the configuration file).

Yield curve data can be supplied at any frequency you wish, including daily, weekly or monthly. We recommend daily data for most accurate results. If a few days are missing, FIA will use the most recent data available up to that point for attribution.

Currency

This field contains a three-character currency code. Currency codes follow the global ISO 4217 standard.

Maturity

This field holds the position of the current yield along the yield curve from the current date. For instance, a value of 5 refers to the 5-year point on the zero coupon curve.

Zero coupon rate

This field contains the appropriate zero coupon rate for the supplied currency, credit rating, date and maturity.

Personal tools
Namespaces
Variants
Actions
Manual
File formats
Local API
API
Architecture
Additional information
Company
Downloads
Toolbox