File format for importing TWR
You can import precalculated TWR and benchmark values to use in Analytics+. Imported values speed up the usage of Analytics+ – otherwise, the system calculates performance data dynamically based on portfolio positions, which might take considerable resources and slow down the process. To avoid increased loading times, you can import precalculated TWR for the history data.
To upload the TWR history and benchmark return in the system, follow the steps:
Define the date before which Analytics+ should use imported TWR values:
Add the system-level date is defined in preferences. This date is applied to all portfolios. Go to Preference → Administration → Performance calculation and choose the date in the Use imported TWR up to field.
(Optional) Add the portfolio-specific date. Open the Portfolio window and choose the date in the Imported TWR date field.
Prepare the import file (CSV and XLSX) and upload it in FA Back via the Import menu. The file format is the same for TWR and benchmark import (with some differences in the required fields and in the field values). You can include the TWR and benchmark data in the same file. 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.
Download the CSV and XLSX file templates here:
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.
Depending on what data you have, you can import:
TWR
Market value at the beginning of the period. The system will calculate TWR based on the market value at the beginning and at the end of the period.
# | Code | Required | Name | Description |
|---|---|---|---|---|
1 | pd.endDate | Yes | End date | The end date of the period for which the TWR values are valid. The format is YYYY-MM-dd. |
2 | pd.twr | No | TWR | TWR over the period. Use a dot as a decimal separator, for example: 0.00995. You can skip the TWR value and import market value at the beginning of the period instead (pd.marketValueStart). The system will calculate TWR based on the market value at the beginning and at the end of the period. |
3 | pd.twrFx | No | 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 | No | 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 | Yes | Market value | Market value (end of period). Use a dot as a decimal separator, for example: 0.00995. |
6 | pd.marketValueStart | No | Market value start | Market value (beginning of period). Use a dot as a decimal separator, for example: 0.00995. This value can be used to calculate the TWR value based on the market value at the beginning and at the end of the period. In this case, you can skip the pd.twr field. |
7 | pd.cashflow | No | 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 | Yes | Portfolio ID | Portfolio ID of the portfolio analysed (see the description of the pd.scope field). |
9 | pd.scope | No | Scope | The scope of analysis. Currently, only one option is available for the TWR import:
The default value is "PORTFOLIO". |
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. |
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. |