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 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 calculations are frozen up to the specified date.

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

Option	Figures available in Analytics+	Aggregation in Analytics+	Implementation
Option 1 (recommended) Import market value and cashflows. The system calculates TWR using the imported values.	Market value, TWR, TWR Gross, Daily profit and loss, Share of total, cashflows, Daily costs.	Full grouping support. Analyze multiple portfolios by aggregating data.	Requires more data and setup.
Option 2. Import pre-calculated TWR values at the given level (portfolio, security, position, type, group, sector, class, contact and so on).	Market value, TWR, TWR gross, Daily profit and loss, Share of total, Daily costs.	No aggregation support. You can only analyze the portfolio to which TWR values are imported.	Simpler and requires less input data.

Option

Figures available in Analytics+

Aggregation in Analytics+

Implementation

Option 1 (recommended)

Import market value and cashflows. The system calculates TWR using the imported values.

Market value, TWR, TWR Gross, Daily profit and loss, Share of total, cashflows, Daily costs.

Full grouping support. Analyze multiple portfolios by aggregating data.

Requires more data and setup.

Option 2.

Import pre-calculated TWR values at the given level (portfolio, security, position, type, group, sector, class, contact and so on).

Market value, TWR, TWR gross, Daily profit and loss, Share of total, Daily costs.

No aggregation support. You can only analyze the portfolio to which TWR values are imported.

Simpler and requires less input data.

Import TWR data

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

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 Preference → Administration → Performance 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.
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
  - 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 columns in the import file empty.
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 columnss and in the column 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.
(Optional) Check the imported values with the SQL query: Query to check the imported data.

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_Option_1.csv

FAFormat_TWR_Option_1.xlsx

FAFormat_TWR_Option_2.csv

FAFormat_TWR_Option_2.xlsx

Code	Option 1. Import market value and cashflows	Option 2. Importing TWR values	Name	Description
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.
pd.twr	Not used	Required	TWR	TWR over the period. Use a dot as a decimal separator, for example: 0.00995.
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.
pd.twrGross	Not used	Optional	TWR Gross	TWR Gross over the period. Use a dot as a decimal separator, for example: 0.00995. If no TWR Gross or costs (pd.cost) are imported, the TWR Gross is set to the TWR.
pd.marketValueEnd	Required	Required	Market value	Market value (end of period). Use a dot as a decimal separator, for example: 0.00995.
pd.marketValueStart	Required	Optional	Market value start	Market value (beginning of period). Use a dot as a decimal separator, for example: 0.00995.
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.
pd.identifier	Required	Required	Portfolio ID	Portfolio ID of the portfolio analysed (see the description of the pd.scope column).
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".
pd.groupingCode	Required	Required	Code	Identifier of the item used for grouping the data (the grouping is defined in the pd.grouping column). For a portfolio, specify portfolio ID. For a security or security type, specify the code.
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.
pd.delete	Optional	Optional	Delete	A column used to delete a row. TRUE – Delete the row. To delete a row with the TWR data, you need to specify the following fields (the system uses them to identify which row to delete): Specify pd.id column with the. Other fields can be left empty. Specify pd.endDate, pd.identifier, pd.scope, pd.groupingCode, pd.grouping, pd. currency. Other fields can be left empty. FALSE (default) – Import the row.
pd.cost	Optional	Optional	Daily costs	Daily costs that include costs, other cost and implicit costs (i.e. costs embedded in security price as ex-post costs). The figure is available in the Analytics+ view as "Daily costs". It is used to calculate the TWR Gross: With Option 1 (importing market value and cashflows), you have to import the daily costs figures to have TWR Gross calculated. With Option 2 (importing TWR values), we recommend importing the TWR Gross figures directly in the pd.twrGross column. Alternatively, you can import pd.costs to have TWR Gross calculated based on daily costs. If no TWR Gross or costs are imported, the TWR Gross is set to the TWR.
pd.id	Optional	Optional	Internal UUID	Internal UUID of the data row. Needed for deleting TWR values. To get the UUID, use the SQL query: Query to check the imported data.

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.

Query to check the imported data

You can check the data tha you imported by running the following SQL query in the Queries view in FA Back:

SELECT LOWER(CONCAT_WS('-',
        SUBSTR(HEX(id), 1, 8),
        SUBSTR(HEX(id), 9, 4),
        SUBSTR(HEX(id), 13, 4),
        SUBSTR(HEX(id), 17, 4),
        SUBSTR(HEX(id), 21)
    )) as 'pd.id',
    pd.end_date as 'pd.endDate',
    pd.twr as 'pd.twr',
    pd.twr_fx as 'pd.twrFx',
    pd.twr_gross as 'pd.twrGross',
    pd.market_value_end as 'pd.marketValueEnd',
    pd.market_value_start as 'pd.marketValueStart',
    pd.cashflow as 'pd.cashflow',
    pd.currency as 'pd.currency',
    pd.identifier as 'pd.identifier',
    pd.scope as 'pd.scope',
    pd.grouping_type as 'pd.grouping',
    pd.grouping_code as 'pd.groupingCode',
    pd.cost as 'pd.cost',
    "false" as 'pd.delete'
FROM pm2_performance_data pd
WHERE pd.identifier = $P(portfolioId)

In this section:

File format for importing TWR

Import TWR data

Note

FA Format for importing TWR

FA Format for importing benchmark return

Query to check the imported data

Search results