Skip to main content

File format for importing TWR

FA Platform calculates TWR in Analytics+ dynamically based on transactions. When migrating data from other systems, you can import precalculated TWR and benchmark data. This feature allows you to:

  • Migrate historical TWR data without migrating full transaction history – for example, if your transaction history has some gaps or the data quality is not reliable.

  • Ensure that historical TWR values in FA Platform are the same as in the source system.

  • Improve the speed and performance of the FA Platform. Calculating TWR dynamically might take considerable resources. When you import historical TWR data, these calusulations are frozen up to the specified date.

You can choose one of the options to import TWR data:

Option

Aggregation in Analytics+

Support for TWR gross calculation

Option 1 (recommended). Import market value and cashflows. The system calculates TWR using the imported values. Figures available in Analytics+: Market value, TWR, Daily profit and loss, Share of total, cashflows.

Full grouping support in Analytics+. You can analyse multiple portfolios by aggregating the data.

Does not calculate gross TWR.

Option 2. Importing TWR values at the given level (portfolio, security, position, type, group, sector, class, contact and so on). Figures available in Analytics+: Market value, TWR, TWR gross, Daily profit and loss, share of total.

Doesn’t support aggregation. You can only analyse the portfolio to which TWR values are imported.

Supports gross TWR calculation.

Import TWR data

To upload the TWR history and benchmark return in the system, follow the steps:

  1. Define the period for which imported TWR values should be used. You can use the following options:

    • Define the period on the system level by specifying the end date. This period is is applied to all portfolios. Go to PreferenceAdministrationPerformance calculation and choose the date in the Use imported TWR up to field.

    • (Optional) Define the portfolio-specific period. Open the Portfolio window and choose the date in the Imported TWR date field.

    Note that the TWR data imported for any dates outside the period you set doesn't have any effect and isn't used in Analytics.

  2. If you import TWR values on portfolio level, create a zero dummy position in the portfolio and import zero values for it first, to initiate Analytics calculation.

    1. Create a dummy security of the Reference instruments (INDEX) type.

    2. Create an Add transaction that adds the dummy security position in the portfolio: Amount = 1, Unit price = 0, Status = Accepted.

    3. Import zero TWR values for the added position. Create an import file with dummy TWR data

      twr_import_dummy.png
      • pd.ednDate – Use the same dates you are going to use in the TWR import file.

      • pd.twr, pd.marketValueEnd – Specify as "0.00".

      • pd.identifier – Portfolio ID.

      • pd.groupingCode – Security code of the dummy security you created.

      • pd.grouping – "SECURITY".

      Leave the rest of the fields in the import file empty.

  3. Prepare the TWR import file (CSV or XLSX) and upload it in FA Back via the Import menu. You can include the TWR and benchmark data in the same file. The file format for benchmark import is the same as for TWR (with some differences in the required fields and in the field values). To learn more about what benchmark use and aggregation logic, see Measures of benchmarks.

Note

The CSV file delimiter is a semicolon by default, but it can be changed to another character: see Preference - Importing for details.

FA Format for importing TWR

TWR is imported in FA in a CSV or XLSX file, with the file columns described below. Define the column headers in the first row – you can specify them in any order. After the first header row, each row in the file should contain TWR data.

Download the CSV and XLSX file templates here:

FAFormat_TWR.csv

FAFormat_TWR.xlsx

#

Code

Option 1. Import market value and cashflows

Option 2. Importing TWR values

Name

Description

1

pd.endDate

Required

Required

End date

The end date of the period for which the TWR values are valid. The format is YYYY-MM-dd.

2

pd.twr

Not used

Required

TWR

TWR over the period. Use a dot as a decimal separator, for example: 0.00995.

3

pd.twrFx

Not used

Optional

TWR (fx)

TWR (fx) over the period. Use a dot as a decimal separator, for example: 0.00995. The default value is 0.

4

pd.twrGross

Not used

Optional

TWR Gross

TWR Gross over the period. Use a dot as a decimal separator, for example: 0.00995. The default value is 0.

5

pd.marketValueEnd

Required

Required

Market value

Market value (end of period). Use a dot as a decimal separator, for example: 0.00995.

6

pd.marketValueStart

Required

Optional

Market value start

Market value (beginning of period). Use a dot as a decimal separator, for example: 0.00995.

7

pd.cashflow

Required

Optional

Net cashflow

Net cashflow over the period. Use a dot as a decimal separator, for example: 0.00995. The default value is 0.

8

pd.identifier

Required

Required

Portfolio ID

Portfolio ID of the portfolio analysed (see the description of the pd.scope field).

9

pd.scope

Optional

Optional

Scope

The scope of analysis. Currently, only one option is available for the TWR import:

  • PORTFOLIO – Import TWR values for a certain portfolio.

The default value is "PORTFOLIO".

10

pd.groupingCode

Required

Required

Code

Identifier of the item used for grouping the data (the grouping is defined in the pd.grouping field). For a portfolio, specify portfolio ID. For a security or security type, specify the code.

11

pd.grouping

Required

Required

Grouping

Grouping used for the analysis. For example, you can upload the TWR data grouped by portfolio, security, or security type. For the full list of grouping options, see Analytics+ grouping options in FA Developer guide.

FA Format for importing benchmark return

Benchmark values can be imported in the same file with the TWR data. The file columns are similar, with some differences in the required fields and in the field values.

#

Code

Required

Name

Description

1

pd.endDate

Yes

End date

The end date of the period for which the benchmark return values are valid. The format is YYYY-MM-dd.

5

pd.marketValueEnd

Yes

Market value

Benchmark value (end of period). Use a dot as a decimal separator, for example: 0.00995.

6

pd.marketValueStart

Yes

Market value Start

Benchmark value (beginning of the period). Use a dot as a decimal separator, for example: 0.00995.

8

pd.identifier

Yes

Portfolio ID

Portfolio ID of the portfolio analysed (see the description of the pd.scope field).

9

pd.scope

Yes

Scope

The scope of analysis. When importing benchmark values, enter "BENCHMARK".

10

pd.groupingCode

Yes

Code

Identifier of the item used for grouping the data (the grouping is defined in the pd.grouping field). For a portfolio, specify portfolio ID. For a security or security type, specify the code.

11

pd.grouping

Yes

Grouping

Grouping used for the analysis. For example, you can upload the data grouped by portfolio, security, or security type. For the full list of grouping options, see Analytics+ grouping options in FA Developer guide.

12

pd.twrBm

Yes

TWR Bm

Benchmark TWR. Use a dot as a decimal separator, for example: 0.00995.