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:
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.
Create a dummy security of the Reference instruments (INDEX) type.
Create an Add transaction that adds the dummy security position in the portfolio: Amount = 1, Unit price = 0, Status = Accepted.
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 fields 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 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:
# | 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:
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. |