Skip to main content

Data structure

The data in FA is structured into multiple database tables. Use SHOW TABLES to see all tables in the database, and DESCRIBE <table name> (for example, "DESCRIBE pm2_transaction") to view properties of the table.

General structure

The general structure is as follows:

Table name

Part of

Description

ACT_EVT_LOG

Activiti process engine

Not in use

ACT_GE_BYTEARRAY

Activiti process engine

Used to store task or process-related variables which need to be serialized (e.g. document attachments).

ACT_GE_PROPERTY

Activiti process engine

ACT_HI_ACTINST

Activiti process engine

Not in use

ACT_HI_ATTACHMENT

Activiti process engine

Not in use

ACT_HI_COMMENT

Activiti process engine

Not in use

ACT_HI_DETAIL

Activiti process engine

Not in use

ACT_HI_IDENTITYLINK

Activiti process engine

Not in use

ACT_HI_PROCINST

Activiti process engine

Not in use

ACT_HI_TASKINST

Activiti process engine

Not in use

ACT_HI_VARINST

Activiti process engine

Not in use

ACT_ID_GROUP

Activiti process engine

ACT_ID_INFO

Activiti process engine

ACT_ID_MEMBERSHIP

Activiti process engine

ACT_ID_USER

Activiti process engine

ACT_PROCDEF_INFO

Activiti process engine

ACT_RE_DEPLOYMENT

Activiti process engine

ACT_RE_MODEL

Activiti process engine

ACT_RE_PROCDEF

Activiti process engine

ACT_RU_EVENT_SUBSCR

Activiti process engine

ACT_RU_EXECUTION

Activiti process engine

Information about ongoing processes

ACT_RU_IDENTITYLINK

Activiti process engine

ACT_RU_JOB

Activiti process engine

ACT_RU_TASK

Activiti process engine

Information about ongoing process tasks and standalone (ongoing or completed) tasks

ACT_RU_VARIABLE

Activiti process engine

Variables of ongoing processes or standalone tasks

DATABASECHANGELOG

DATABASECHANGELOGLOCK

MODESHAPE_REPOSITORY

QRTZ_BLOB_TRIGGERS

Quartz scheduling

QRTZ_CALENDARS

Quartz scheduling

QRTZ_CRON_TRIGGERS

Quartz scheduling

QRTZ_FIRED_TRIGGERS

Quartz scheduling

QRTZ_JOB_DETAILS

Quartz scheduling

QRTZ_LOCKS

Quartz scheduling

QRTZ_PAUSED_TRIGGER_GRPS

Quartz scheduling

QRTZ_SCHEDULER_STATE

Quartz scheduling

QRTZ_SIMPLE_TRIGGERS

Quartz scheduling

QRTZ_SIMPROP_TRIGGERS

Quartz scheduling

QRTZ_TRIGGERS

Quartz scheduling

audit_action

FA/Audit info

Not in use

audit_performer

FA/Audit info

Not in use

audit_performer_attribute

FA/Audit info

Not in use

audit_performer_attributes

FA/Audit info

Not in use

audit_target

FA/Audit info

Not in use

audit_target_attribute

FA/Audit info

Not in use

audit_target_attributes

FA/Audit info

Not in use

authentication_token

API tokens

Tokens listed in the Administration > API > Key management

finp_prof_attr

FA/Deprecated profile tables

Deprecated storage tables for profile data. Has been replaced by pm2_profile2 and pm2_profile2_attribute

finp_prof_item

FA/Deprecated profile tables

finp_profile

FA/Deprecated profile tables

pm2_account

FA/Portfolio account info

Account information: bank accounts linked to the portfolio/transaction.

pm2_account_aud

FA/Portfolio account info

Version history

pm2_address

FA/Contact address info

Contact address information

pm2_address_aud

FA/Contact address info

Version history

pm2_analytics_column_definition

FA/Analytics+ custom columns

Preference > Content and translations > Analytics+ columns

pm2_archivedata

Archive data

This table contains miscellaneous archived data in JSON-format. Currently used for storing NAV-calculation details in Fund Management.

pm2_asset

FA/Asset classes and types

Preference > Securities > Asset classes and -types

pm2_assetm

FA/Asset classes and types

Preference > Securities > Asset classes and -types

pm2_assettype

FA/Asset classes and types

Preference > Securities > Asset classes and -types

pm2_assettype_covs

FA/Asset classes and types

Preference > Securities > Asset classes and -types; Covariances of asset types, not used by core product

pm2_benchmark

FA/Portfolio strategies etc.

This table (together with pm2_benchmark_position) contains portfolios' benchmarks, investment plans and strategies, as well as static model portfolios

pm2_benchmark_position

FA/Portfolio strategies etc.

This table (together with pm2_benchmark) contains portfolios' benchmarks, investment plans and strategies, as well as static model portfolios

pm2_class_type

FA/Security class 1 definitions

Preference > Securities > Class 1

pm2_class_type2

FA/Security class 2 definitions

Preference > Securities > Class 2

pm2_class_type3

FA/Security class 3 definitions

Preference > Securities > Class 3

pm2_class_type4

FA/Security class 4 definitions

Preference > Securities > Class 4

pm2_class_type5

FA/Security class 5 definitions

Preference > Securities > Class 5

pm2_classification

FA/Contact class 1 definitions

Preference > Contacts > Classification

pm2_classification2

FA/Contact class 2 definitions

Preference > Contacts > Classification 2

pm2_classification3

FA/Contact class 3 definitions

Preference > Contacts > Classification 3

pm2_contact

FA/Contact data

Most of the information related to contacts (customers, asset managers, custodies, etc.) in FA

pm2_contact_aud

FA/Contact data

Version history

pm2_contact_exchange

FA/Contact data

Contact settlement place tab

pm2_contact_exchange_aud

FA/Contact data

Version history

pm2_contact_reportings

FA/Contact data

pm2_contact_reportings_aud

FA/Contact data

Version history

pm2_contact_representatives

FA/Contact data

Contact Representatives tab

pm2_contact_representatives_aud

FA/Contact data

Version history

pm2_contact_sub_type

FA/Configurations (Contact)

Preference > Contacts > Contact types (sub types)

pm2_contact_type

FA/Configurations (Contact)

Preference > Contacts > Contact types

pm2_contact_type_sub_types

FA/Configurations (Contact)

Links contact sub types to types

pm2_contract

FA/Contracts

pm2_corporate_action_run

FA/Corporate actions

Details about an executed corporate action run

pm2_corporate_actions

FA/Corporate actions

Corporate action definition

pm2_corporate_actions_ex_sec

FA/Corporate actions

pm2_corporate_actions_tax

FA/Corporate actions

pm2_cost_formula

FA/Configurations (Portfolio)

Preference > Fee calculation > Cost formulas. Includes the actual code for the cost formula!

pm2_cost_type

FA/Configurations (Portfolio)

Preference > Fee calculation > Portfolio cost types

pm2_country

FA/Configurations (General)

Preference > General > Countries

pm2_coupon

FA/Security data

Floating rate note coupon table

pm2_coupon_aud

FA/Security data

Version history

pm2_customer_portfolio_search

FA/Technical

pm2_customer_portfolio_search_view

FA/Technical

pm2_date_rule

FA/Technical

pm2_default_tax_config

FA/Configurations (General)

Preference > Taxation > Default tax rates

pm2_dynamic_group

FA/Portfolio groups

Not used since FASalkku 1.9.13

pm2_ex_sec

pm2_exchange

FA/Configurations (Securities)

Preference > Securities > Market place

pm2_exchange_aud

FA/Configurations (Securities)

Version history

pm2_ext_contact_id

FA/Contact data

Contact IDs tab

pm2_ext_contact_id_aud

FA/Contact data

Version history

pm2_ext_portfolio_id

FA/Portfolio data

Portfolio IDs tab

pm2_ext_portfolio_id_aud

FA/Portfolio data

Version history

pm2_fee

FA/Portfolio data

Portfolio fees

pm2_fee_aud

FA/Portfolio data

Version history

pm2_group

FA/Asset classes and types

Highest level of asset classes and types

pm2_identity

FA/Configurations (Contact)

Preference > Contacts > Identity

pm2_juridical

FA/Configurations (Contact)

Preference > Contacts > Juridical form

pm2_key_figure_definition

FA/Key figures

Preferences > Content and translations > Key figures

pm2_language

FA/Configurations (General)

Preference > General > Languages

pm2_marketdata_observation

FA/Security market prices

All security prices are stored in this table.

pm2_marketdata_observation_aud

FA/Security market prices

Version history

pm2_pf_act_item

FA/Portfolio values and performance

Specific account's value and return on a specific date, linked to pm2_pf_report. Each account in the portfolio has a row in this table for each day. It contains e.g. the balance of the account. (pm2_pf_act_item.pr_id => pm2_pf_report.id, pm2_pf_act_item.pf_id => pm2_portfolio.id, pm2_pf_act_item.account => pm2_account.id)

pm2_pf_act_sub_item

FA/Portfolio values and performance

Purchase lots for accounts, linked to pm2_pf_act_item

pm2_pf_rep_item

FA/Portfolio values and performance

Specific position's value and return on a specific date, linked to pm2_pf_report. Each position in the portfolio has a row in this table for each day. It contains e.g. that positions value and performance info (pm2_pf_rep_item.pf_id => pm2_portfolio.id, pm2_pf_rep_item.pr_id => pm2_pf_report.id, pm2_pf_rep_item.security => pm2_security.id)

pm2_pf_rep_sub_item

FA/Portfolio values and performance

Purchase lots for positions, linked to pm2_pf_rep_item

pm2_pf_report

FA/Portfolio values and performance

Portfolio's value and return on a specific date. Each portfolio has one row in this table for each day. It contains e.g. portfolio value on that day and also performance info. (pm2_pf_report.pf_id => pm2_portfolio.id)

pm2_pf_type

FA/Configurations (Portfolio)

Preference > Portfolios > Portfolio types

pm2_pg_p

FA/Portfolio groups

Which portfolios belong to which portfolio groups (both static AND dynamic groups)

pm2_portfolio

FA/Portfolio data

Most data related to portfolios in FA. Primary contact of the portfolio (pm2_portfolio.pri_con_id => pm2_contact.id) is the main owner of the portfolio.

pm2_portfolio_am

FA/Portfolio data

Portfolio's asset managers tab

pm2_portfolio_aud

FA/Portfolio data

Version history

pm2_portfolio_contacts

FA/Portfolio data

Portfolio's contacts tab

pm2_portfolio_cost_formulas

FA/Portfolio data

Portfolio > Fees > Cost formulas

pm2_portfolio_portfolios

FA/Portfolio data

Portfolio > Contacts > Subportfolios

pm2_portfolio_report_contacts

FA/Portfolio data

Portfolio > Reporting

pm2_portfolio_reportings

FA/Portfolio data

Portfolio > Reporting

pm2_portfolio_reportings_aud

FA/Portfolio data

Version history

pm2_portfoliogroup

FA/Portfolio groups

Portfolio group properties

pm2_posting

FA/Bookkeeping

All information about generated bookkeeping postings in the system. 1 row = 1 posting.

pm2_posting_aud

FA/Bookkeeping

Version history

pm2_prf_def

FA/Profile data

Profile definitions (including UI code)

pm2_profile2

FA/Profile data

Profile object, linked to another object in FA (contact, portfolio or security), to which lots of profile attributes (below) are linked

pm2_profile2_attribute

FA/Profile data

Individual pieces of profile data, including an identifier (key, unique per profile), type, and one or more values.

pm2_readerformatentity

FA/Importing

Preference > Importing > Reader format configuration, old not-so-commonly-used feature for defining file readers via FA UI

pm2_rebalance

FA/Rebalancing

Rebalance view content (i.e. history of what was rebalanced, when, how)

pm2_redemption

FA/Security data

Security > Extra Info > Redemption (table)

pm2_report

Not used

pm2_rounding_rule

Not used

pm2_rule

FA/Bookkeeping

Bookkeeping rule definitions

pm2_rule_aud

FA/Bookkeeping

Version history

pm2_ruleset

FA/Bookkeeping

Preference > Bookkeeping > Posting rule

pm2_ruleset_account

FA/Bookkeeping

pm2_ruleset_account_set

FA/Bookkeeping

pm2_se_type

FA/Configurations (Security)

Preference > Securities > Security types

pm2_sec_type_sub_type

FA/Configurations (Security)

Preference > Securities > Security types; Linkage between security types and security sub types

pm2_sec_type_tr_type

FA/Configurations (Security)

Preference > Securities > Security types; Linkage between available transaction types per security type

pm2_security

FA/Security data

Most security information is stored in this table (pm2_transaction.security_id => pm2_security.id)

pm2_security_allocation

FA/Security data

Security > Allocation tab

pm2_security_allocation_aud

FA/Security data

Version history

pm2_security_aud

FA/Security data

Version history

pm2_security_ex_cost

FA/Security data

Security > Cost Info > Ex-ante / Ex-post costs

pm2_security_ex_cost_aud

FA/Security data

Version history

pm2_security_sub_type

FA/Configurations (Security)

Preference > Securities > Security types; Security subtypes

pm2_settlement

FA/Configurations (Security)

Preference > Securities > Settlement place; Security settlement places (dropdown list content)

pm2_subscription_type

Not used

pm2_task_type

FA/Configurations (Tasks)

Preference > General > Task type

pm2_tax

Tax information related to corporate actions.

pm2_tax_rule

FA/Corporate actions

Preference > Taxation > Default tax rates = Tax rule definitions (used only in corporate actions)

pm2_tax_type

FA/Configurations (Transactions)

Preference > Taxation > Tax types

pm2_template

FA/Configurations (General)

Preference > Reporting > Email, e-mail templates

pm2_tr_type

FA/Configurations (Transactions)

Preference > Transactions > Transaction types

pm2_tr_type_aud

FA/Configurations (Transactions)

Version history

pm2_transaction

FA/Transactions and trade orders

Most transaction and trade order information is stored in this table: Transactions (status <> 'ORDER') / trade orders (status = 'ORDER') linked to the portfolio (pm2_transaction.pf_id => pm2_portfolio.id).

pm2_transaction_aud

FA/Transactions and trade orders

Version history

pm2_transaction_cost_type

FA/Transactions and trade orders

Preference > Transactions > Transaction cost types

pm2_transaction_vo

FA/Transactions and trade orders

Helper table which is only used to efficiently display data in certain places (namely transactions/trade orders view), dynamically generated based on data in other tables.

pm2_user

FA/Users

Most information about users in FA (including both FA Front and FA Back users)

pm2_user_aud

FA/Users

Version history

pm2_user_login

FA/Users

Currently logged in users/sessions.

pm2_user_pref

FA/Technical

Lots of miscellaneous information, e.g. process configurations, information about installed extensions etc. etc.

pm2_user_roles

FA/Users

Which users have which roles (= rights) in the system.

pm2_user_roles_aud

FA/Users

Version history

pm2_view

FA/Configurations (General)

Definitions of saved searches (securities, contacts, portfolios, transactions, trade orders, overview, customers, dashboard...)

pm2_widget

FA/Widgets

All widgets installed into the system (including code)

revinfo

FA/Audit info

revision_info

FA/Audit info

Links audit tables to specific users/sessions. Combining an _aud table with this table tells you which user did the change, when, from which IP address.

security_redemption_aud

Version history for pm2_redemption

Portfolio values and reporting

The structure of the report tables / entities is as follows:

pm2_pf_report (PortfolioReport)

Field

Attribute

Type

Null

Key

Description

id

id

bigint(20)

NO

PRI

account_balance

accountBalance

double

NO

Sum of account balances in portfolio currency

account_balance_pv

accountBalancePv

double

NO

Sum of account balances (purchase values if non portfolio currency accounts) in portfolio currency

accrued_interest_start

accruedInterestStart

double

NO

Sum of accrued interests in the beginning of the day

accured_interest

accuredInterest

double

NO

Sum of accrued interests

benchmark_indexed_value

benchmarkIndexedValue

double

NO

Portfolio's benchmarks index value (starting from 100, adjusted daily based on benchmark price change)

benchmark_indexed_value_change

benchmarkIndexedValueStart

double

NO

Logarithmic value change of benchmark's indexed value

benchmark_indexed_value_start

benchmarkIndexedValueStart

double

NO

Benchmark's indexed value in the beginning of the day

commitment

commitment

double

NO

Sum of remaining commitments

commitment_total

commitmentTotal

double

NO

Sum of total commitments

d_account_balance

dAccountBalance

double

NO

Daily change in the account balance

d_account_balance_pv

dAccountBalancePy

double

NO

Daily change in the accound balance (purchase value)

d_account_profit

dAccountProfit

double

NO

Daily change in profit from accounts

d_accrued_interest

dAccruedInterest

double

NO

Daily change in accrued interest

d_commitment

dCommitment

double

NO

Daily change in remaining commitment

d_commitment_total

dCommitmentTotal

double

NO

Daily change in total commitment

d_cost

dCost

double

NO

Sum of daily changes from account and position costs

d_ex_post3rd_cost_cat1

sExPost3rdCostCat1

double

NO

Sum of daily changes in ex-post costs from positions and accounts

d_ex_post3rd_cost_cat10

dExPost3rdCostCat10

double

NO

d_ex_post3rd_cost_cat2

dExPost3rdCostCat2

double

NO

d_ex_post3rd_cost_cat3

dExPost3rdCostCat3

double

NO

d_ex_post3rd_cost_cat4

dExPost3rdCostCat4

double

NO

d_ex_post3rd_cost_cat5

dExPost3rdCostCat5

double

NO

d_ex_post3rd_cost_cat6

dExPost3rdCostCat6

double

NO

d_ex_post3rd_cost_cat7

dExPost3rdCostCat7

double

NO

d_ex_post3rd_cost_cat8

dExPost3rdCostCat8

double

NO

d_ex_post3rd_cost_cat9

dExPost3rdCostCat9

double

NO

d_ex_post_pf_cost_cat1

dExPostPfCostCat1

double

NO

d_ex_post_pf_cost_cat10

dExPostPfCostCat10

double

NO

d_ex_post_pf_cost_cat2

dExPostPfCostCat2

double

NO

d_ex_post_pf_cost_cat3

dExPostPfCostCat3

double

NO

d_ex_post_pf_cost_cat4

dExPostPfCostCat4

double

NO

d_ex_post_pf_cost_cat5

dExPostPfCostCat5

double

NO

d_ex_post_pf_cost_cat6

dExPostPfCostCat6

double

NO

d_ex_post_pf_cost_cat7

dExPostPfCostCat7

double

NO

d_ex_post_pf_cost_cat8

dExPostPfCostCat8

double

NO

d_ex_post_pf_cost_cat9

dExPostPfCostCat9

double

NO

d_ex_post_sec_cost_cat1

dExPostSecCostCat1

double

NO

d_ex_post_sec_cost_cat10

dExPostSecCostCat10

double

NO

d_ex_post_sec_cost_cat2

dExPostSecCostCat2

double

NO

d_ex_post_sec_cost_cat3

dExPostSecCostCat3

double

NO

d_ex_post_sec_cost_cat4

dExPostSecCostCat4

double

NO

d_ex_post_sec_cost_cat5

dExPostSecCostCat5

double

NO

d_ex_post_sec_cost_cat6

dExPostSecCostCat6

double

NO

d_ex_post_sec_cost_cat7

dExPostSecCostCat7

double

NO

d_ex_post_sec_cost_cat8

dExPostSecCostCat8

double

NO

d_ex_post_sec_cost_cat9

dExPostSecCostCat9

double

NO

d_market_trade_amount

dMarketTradeAmount

double

NO

Daily change in portfolio's total market trade amount

d_other_cost

dOtherCost

double

NO

Daily change in total other costs

d_paid_accrued_interest

dPaidAccruedInterest

double

NO

Daily change in paid accrued interest

d_position_profit

dPositionProfit

double

NO

Daily change in total profit from positions

d_profit

dProfit

double

NO

Daily change in total profit from positions and accounts

d_purchase_trade_amount

dPurchaseTradeAmount

double

NO

Daily change in total purchase trade amount from positions and accounts

d_rec_trade_amount

dRecTradeAmount

double

NO

Daily change in total record trade amount from positions and accounts

d_rec_value_change

dRecValueChange

double

NO

Daily change in record value change from positions and accounts

d_sold_profit

dSoldProfit

double

NO

Daily change in total sold profit from positions and accounts

d_sold_trade_amount

dSoldTradeAmount

double

NO

Daily change in total sold trade amount from positions and accounts

d_value_change

dValueChange

double

NO

Daily change in total change in market from positions and accounts

indexed_value

indexedValue

double

NO

Indexed value (starting from 100, change with portfolio's TWR)

indexed_value_change

indexedValueChange

double

NO

Daily logarithmic change in indexed value

indexed_value_start

indexedValueStart

double

NO

Indexed value in the beginning of the day

intraday_profit

intradayProfit

double

NO

Not used

market_value

marketValue

double

NO

Position market value + account balance

market_value_start

marketValueStart

double

NO

Market value in the beginning of the day

neg_net_cashflow

negativeNetCashflow

double

NO

Sum of all negative net cashflows from positions and accounts from portfolio's point of view

net_cashflow

netCashflow

double

NO

Sum of all net cashflows from positions and accounts from portfolio's point of view

position_market_value

positionMarketValue

double

NO

Total market value of positions that are included in the total

position_purchase_value

positionPurchaseValue

double

NO

Total purchase value of positions that are included in the total

position_rec_purchase_value

positionRecPurchaseValue

double

NO

Total record purchase value of positions that are included in the total

pos_net_cashflow

positiveNetCashflow

double

NO

Sum of all positive net cashflows from positions and accounts from portfolio's point of view

purchase_value

purchaseValue

double

NO

position purchase value + account balance (purchase value)

rec_purchase_value

recPurchaseValue

double

NO

position record purchase value + account balance (purchase value)

rec_value_change_absolute

recValueChangeAbsolute

double

NO

market value - record purchase value

rec_value_change_relative

recValueChangeRelative

double

NO

Record value change absolute / record purchase value

report_date

reportDate

datetime(6)

YES

Date of the portfolio report (market date)

value_change_absolute

valueChangeAbsolute

double

NO

market value - purchase value

value_change_relative

valueChangeRelative

double

NO

market value absolute / purchase value

version

version

int(11)

YES

pf_id

pfId

bigint(20)

YES

MUL

ID of the linked portfolio

pm2_pf_rep_item (PortfolioReportItem)

Field

Attribute

Type

Null

Key

Description

id

id

bigint(20)

NO

PRI

accrued_cashflow

accruedCashflow

double

NO

Accrued sum of all cashflows in the position in the portfolio currency

accrued_cost

accruedCost

double

NO

Accrued sum of all costs in the position in the portfolio currency

accrued_cost_sec

accruedCostSec

double

NO

Accrued sum of all costs in the position in the security currency

accrued_interest

accruedInterest

double

NO

Accrued interest in portfolio currency on the open position on the market date. If accrued interest is based on the transaction date, then the paid accrued interest is considered part of accrued interest between transaction date and settlement date.

accrued_interest_sec

accruedInterestSec

double

NO

Accrued interest in security currency

accrued_interest_start

accruedInterestStart

double

NO

Accrued interest in the beginning of the day

accrued_profit

accruedProfit

double

NO

Sum of accrued profit from purchase lots related to this position

amount

amount

double

NO

Amount of the position.

cashflow

cashflow

double

NO

Cashflow related to the position on the day.

commitment

commitment

double

NO

Remaining commitment in the portfolio currency on the position

commitment_sec

commitmentSec

double

NO

Remaining commitment in the security currency on the position

commitment_total

commitmentTotal

double

NO

Total commitment in the portfolio currency on the position

commitment_total_sec

commitmentTotalSec

double

NO

Total commitment in the security currency on the position

cost

cost

double

NO

Sum of costs from related purchase lots. If the security type is a future contract with costs charged later, then the figure is revaluated into portfolio currency using that day's market fx rate

cost_sec

costSec

double

NO

Sum of costs from related purchase lots. In security currency.

d_accrued_interest

dAccruedInterest

double

NO

Daily change in the accrued interest in portfolio currency

d_accrued_interest_sec

dAccruedInterestSec

double

NO

Daily change in the accrued interest in the security currency

d_amount

dAmount

double

NO

Daily change in the amount

d_commitment

dCommitment

double

NO

Daily change in the remaining commitment in portfolio currency

d_commitment_sec

dCommitmentSec

double

NO

Daily change in the remaining commitment in security currency

d_commitment_total

dCommitmentTotal

double

NO

Daily change in the total commitment in the portfolio currency

d_commitment_total_sec

dCommitmentTotalSec

double

NO

Daily change in the total commitment in the security currency

d_cost

dCost

double

NO

Daily change in the cost in portfolio currency

d_cost_sec

dCostSec

double

NO

Daily change in the cost in security currency

d_ex_post3rd_cost_cat1

dExPost3RdCostCat1

double

NO

Daily change in ex-post costs

d_ex_post3rd_cost_cat10

dExPost3RdCostCat10

double

NO

d_ex_post3rd_cost_cat2

dExPost3RdCostCat2

double

NO

d_ex_post3rd_cost_cat3

dExPost3RdCostCat3

double

NO

d_ex_post3rd_cost_cat4

dExPost3RdCostCat4

double

NO

d_ex_post3rd_cost_cat5

dExPost3RdCostCat5

double

NO

d_ex_post3rd_cost_cat6

dExPost3RdCostCat6

double

NO

d_ex_post3rd_cost_cat7

dExPost3RdCostCat7

double

NO

d_ex_post3rd_cost_cat8

dExPost3RdCostCat8

double

NO

d_ex_post3rd_cost_cat9

dExPost3RdCostCat9

double

NO

d_ex_post_pf_cost_cat1

dExPostPfCostCat1

double

NO

d_ex_post_pf_cost_cat10

dExPostPfCostCat10

double

NO

d_ex_post_pf_cost_cat2

dExPostPfCostCat2

double

NO

d_ex_post_pf_cost_cat3

dExPostPfCostCat3

double

NO

d_ex_post_pf_cost_cat4

dExPostPfCostCat4

double

NO

d_ex_post_pf_cost_cat5

dExPostPfCostCat5

double

NO

d_ex_post_pf_cost_cat6

dExPostPfCostCat6

double

NO

d_ex_post_pf_cost_cat7

dExPostPfCostCat7

double

NO

d_ex_post_pf_cost_cat8

dExPostPfCostCat8

double

NO

d_ex_post_pf_cost_cat9

dExPostPfCostCat9

double

NO

d_ex_post_sec_cost_cat1

dExPostSecCostCat1

double

NO

d_ex_post_sec_cost_cat10

dExPostSecCostCat10

double

NO

d_ex_post_sec_cost_cat2

dExPostSecCostCat2

double

NO

d_ex_post_sec_cost_cat3

dExPostSecCostCat3

double

NO

d_ex_post_sec_cost_cat4

dExPostSecCostCat4

double

NO

d_ex_post_sec_cost_cat5

dExPostSecCostCat5

double

NO

d_ex_post_sec_cost_cat6

dExPostSecCostCat6

double

NO

d_ex_post_sec_cost_cat7

dExPostSecCostCat7

double

NO

d_ex_post_sec_cost_cat8

dExPostSecCostCat8

double

NO

d_ex_post_sec_cost_cat9

dExPostSecCostCat9

double

NO

d_ex_post_security_cost_cat1

dExPostSecurityCostCat1

double

NO

d_ex_post_security_cost_cat10

dExPostSecurityCostCat10

double

NO

d_ex_post_security_cost_cat2

dExPostSecurityCostCat2

double

NO

d_ex_post_security_cost_cat3

dExPostSecurityCostCat3

double

NO

d_ex_post_security_cost_cat4

dExPostSecurityCostCat4

double

NO

d_ex_post_security_cost_cat5

dExPostSecurityCostCat5

double

NO

d_ex_post_security_cost_cat6

dExPostSecurityCostCat6

double

NO

d_ex_post_security_cost_cat7

dExPostSecurityCostCat7

double

NO

d_ex_post_security_cost_cat8

dExPostSecurityCostCat8

double

NO

d_ex_post_security_cost_cat9

dExPostSecurityCostCat9

double

NO

d_market_trade_amount

dMarketTradeAmount

double

NO

Daily change in the market trade amount

d_other_cost

dOtherCost

double

NO

Daily change in other costs

d_other_cost_sec

dOtherCostSec

double

NO

Daily change in others costs in security currency

d_paid_accrued_interest

dPaidAccruedInterest

double

NO

Daily change in paid accrued interest in portfolio currency

d_paid_accrued_interest_sec

dPaidAccruedInterestSec

double

NO

Daily change in paid accrued interest in security currency

d_profit

dProfit

double

NO

Daily change in profit in portfolio currency

d_profit_sec

dProfitSec

double

NO

Daily change in profit in security currency

d_purchase_trade_amount

dPurchaseTradeAmount

double

NO

Daily change in purchase trade amount

d_rec_trade_amount

dRecTradeAmount

double

NO

Daily change in record trade amount

d_rec_value_change

dRecValueChange

double

NO

Daily change in record value change

d_record_sold_profit

dRecordSoldProfit

double

NO

Daily change in record sold profit

d_sold_profit

dSoldProfit

double

NO

Daily change in sold profit

d_sold_profit_sec

dSoldProfitSec

double

NO

Daily change in sold profit in security currency

d_sold_trade_amount

dSoldTradeAmount

double

NO

Daily change in sold trade amount

d_trade_amount

dTradeAmount

double

NO

Daily change in trade amount

d_value_change

dValueChange

double

NO

Daily change in value change

d_value_change_sec

dValueChangeSec

double

NO

Daily change in value change in security currency

days

days

bigint(20)

NO

Number of days the position has been open

duration

duration

double

NO

Used internally in the logic. Not to be confused with bond's duration.

ex_post3rd_cost_cat1

exPost3RdCostCat1

double

NO

Sum of ex-post costs from purchase lots - both open and close.

ex_post3rd_cost_cat10

exPost3RdCostCat10

double

NO

ex_post3rd_cost_cat2

exPost3RdCostCat2

double

NO

ex_post3rd_cost_cat3

exPost3RdCostCat3

double

NO

ex_post3rd_cost_cat4

exPost3RdCostCat4

double

NO

ex_post3rd_cost_cat5

exPost3RdCostCat5

double

NO

ex_post3rd_cost_cat6

exPost3RdCostCat6

double

NO

ex_post3rd_cost_cat7

exPost3RdCostCat7

double

NO

ex_post3rd_cost_cat8

exPost3RdCostCat8

double

NO

ex_post3rd_cost_cat9

exPost3RdCostCat9

double

NO

ex_post_pf_cost_cat1

exPostPfCostCat1

double

NO

ex_post_pf_cost_cat10

exPostPfCostCat10

double

NO

ex_post_pf_cost_cat2

exPostPfCostCat2

double

NO

ex_post_pf_cost_cat3

exPostPfCostCat3

double

NO

ex_post_pf_cost_cat4

exPostPfCostCat4

double

NO

ex_post_pf_cost_cat5

exPostPfCostCat5

double

NO

ex_post_pf_cost_cat6

exPostPfCostCat6

double

NO

ex_post_pf_cost_cat7

exPostPfCostCat7

double

NO

ex_post_pf_cost_cat8

exPostPfCostCat8

double

NO

ex_post_pf_cost_cat9

exPostPfCostCat9

double

NO

ex_post_sec_cost_cat1

exPostSecCostCat1

double

NO

ex_post_sec_cost_cat10

exPostSecCostCat10

double

NO

ex_post_sec_cost_cat2

exPostSecCostCat2

double

NO

ex_post_sec_cost_cat3

exPostSecCostCat3

double

NO

ex_post_sec_cost_cat4

exPostSecCostCat4

double

NO

ex_post_sec_cost_cat5

exPostSecCostCat5

double

NO

ex_post_sec_cost_cat6

exPostSecCostCat6

double

NO

ex_post_sec_cost_cat7

exPostSecCostCat7

double

NO

ex_post_sec_cost_cat8

exPostSecCostCat8

double

NO

ex_post_sec_cost_cat9

exPostSecCostCat9

double

NO

ex_post_security_cost_cat1

exPostSecurityCostCat1

double

NO

ex_post_security_cost_cat10

exPostSecurityCostCat10

double

NO

ex_post_security_cost_cat2

exPostSecurityCostCat2

double

NO

ex_post_security_cost_cat3

exPostSecurityCostCat3

double

NO

ex_post_security_cost_cat4

exPostSecurityCostCat4

double

NO

ex_post_security_cost_cat5

exPostSecurityCostCat5

double

NO

ex_post_security_cost_cat6

exPostSecurityCostCat6

double

NO

ex_post_security_cost_cat7

exPostSecurityCostCat7

double

NO

ex_post_security_cost_cat8

exPostSecurityCostCat8

double

NO

ex_post_security_cost_cat9

exPostSecurityCostCat9

double

NO

format_decimal

formatDecimal

int(11)

NO

Decimal format for the amount field (how many decimals)

future_contract

futureContract

int(11)

NO

0 = position is not based on futures type of security, 1 = position is on a futures type of security (ie. security type has attribute futureContract = 1)

fx_rate

fxRate

double

NO

Market fx rate. If market fx rate is not available, then weighted average fx from purchase lots. If the security type is a future contract, the fx rate is the same as market fx rate (see trade_amount)

include_in_total

includeInTotal

int(11)

NO

1 = true, 0 = false. Depends on the linked security's type settings

indexed_value

indexedValue

double

NO

starting from 100, adjusted daily based on the TWR. NOTE! This figure is not 100% the same as in analytics+ so do not use it for TWR.

indexed_value_change

indexedValueChange

double

NO

Logarithmic change in the indexed value

indexed_value_start

indexedValueStart

double

NO

Indexed value in the beginning of the day

pri_key

key

varchar(255)

YES

Primarily security code, if security missing then account number. However, if security type has setting to separate short and long positions, the security code has ending "-NEG" when position is negative.

market_date

marketDate

datetime(6)

YES

Market date

market_fx_rate

marketFxRate

double

NO

Market fx rate (as used in calculation)

market_fx_rate_start

marketFxRateStart

double

YES

Market fx rate in the beginning of the day

market_fx_rate_view

marketFxRateView

double

NO

Maket fx rate in the view mode (ie. more readable format of the cross)

market_trade_amount

marketTradeAmount

double

NO

Market value of the position excluding accrued interest

market_trade_amount_start

marketTradeAmountStart

double

NO

Market trade amount in the beginning of the day

market_unit_price

marketUnitPrice

double

NO

Market unit price (as used in calculation)

market_unit_price_date

marketUnitPriceDate

datetime(6)

YES

Date of the market unit price

market_unit_price_view

marketUnitPriceView

double

NO

Market unit price (as defined to be seen with multipliers and inversions)

neg_net_cashflow

negNetCashflow

double

NO

Sum of negative net cashlows on the day from the positions point of view (ie. all net cashflows in or out of the position such as buing and selling even if the tr type has no netcashflow effect)

neg_pf_net_cashflow

negPfNetCashflow

double

NO

Sum of negative net cashlows on the day from the portfolio's point of view (ie. if transaction type has net cashflow effect or if transaction has cashflow effect but no account)

net_cashflow

netCashflow

double

NO

Sum of positive and negative net cashflows

other_cost

otherCost

double

NO

Sum of other costs from purchase lots. If the security type is a future contract with costs charged later, then the figure is revaluated into portfolio currency using that day's market fx rate

paid_accrued_interest

paidAccruedInterest

double

NO

Accrued paid accrued interest in the position

pos_net_cashflow

posNetCashflow

double

NO

Sum of positive net cashlows on the day from the positions point of view

pos_pf_net_cashflow

posPfNetCashflow

double

NO

Sum of positive net cashlows on the day from the portfolio's point of view

profit

profit

double

NO

Sum of profit from purchase lots

purchase_amount

purchaseAmount

double

NO

Sum of purchase amounts from open (+ closed on that day) purchase lots

purchase_fx_rate

purchaseFxRate

double

NO

Weighed average purchase fx rate from open (+ closed on that day) purchase lots

purchase_trade_amount

purchaseTradeAmount

double

NO

Sum of purchase trade amounts from open (+ closed on that day) purchase lots

purchase_unit_price

purchaseUnitPrice

double

NO

Weighted average purchase unit price from open (+ closed on that day) purchase lots

purchase_unit_price_view

purchaseUnitPriceView

double

NO

View version of the purchase unit price taking into account multipliers etc.

rec_value_change_absolute

recValueChangeAbsolute

double

NO

Difference between market trade amount and record trade amount

rec_value_change_relative

recValueChangeRelative

double

NO

Record value change / record trade amount

record_fx_rate

recordFxRate

double

NO

Weighed average record fx rates from open purchase lots

record_sold_profit

recordSoldProfit

double

NO

Sum of record sold profit from open (+ closed on that day) purchase lots

rec_trade_amount

recTradeAmount

double

YES

Sum of record trade amount from open purchase lots

rec_unit_price

recUnitPrice

double

YES

Weighted average record unit price from open purchase lots

record_unit_price_view

recordUnitPriceView

double

NO

View version of the record unit price

share_of_parent

shareOfParent

double

NO

Not used. Always 1.

share_of_portfolio

shareOfPortfolio

double

NO

Share of the portfolio base on market trade amounts (excludes accrued interest)

share_of_portfolio_start

shareOfPortfolioStart

double

NO

Share of the portfolio in the beginning of the date

sold_amount

soldAmount

double

NO

Sum of sold amount from open (+ closed on that day) purchase lots

sold_fx_rate

soldFxRate

double

NO

Weighed average sold fx rate from open purchase lots

sold_profit

soldProfit

double

NO

Sum of sold profit from open (+ closed on that day) purchase lots

sold_profit_sec

soldProfitSec

double

NO

Sold profit in security currency

sold_trade_amount

soldTradeAmount

double

NO

Sum of sold trade amount from open (+ closed on that day) purchase lots

sold_unit_price

soldUnitPrice

double

NO

Weighted average sold unit price from open (+ closed on that day) purchase lots

trade_amount

tradeAmount

double

NO

Sum of open trade amounts from open purchase lots. If the security type is a future contract, then the figure is revaluated into portfolio currency using that day's market fx rate. If the costs are marked to be charged later in the security type, then the figure does not include costs.

unit_price

unitPrice

double

NO

Trade amount / amount

unit_price_view

unitPriceView

double

NO

View version of the unit price

value_change_absolute

valueChangeAbsolute

double

NO

Sum of value change absolute from open purchase lots

value_change_absolute_sec

valueChangeAbsoluteSec

double

NO

Sum of value change absolute from open purchase lots in security currency

value_change_relative

valueChangeRelative

double

NO

Value change absolute / trade amount

version

version

int(11)

YES

yield_to_maturity

yieldToMaturity

double

NO

Not used.

account

account

bigint(20)

YES

MUL

ID of the linked account from purchase lots

currency

currency

bigint(20)

YES

MUL

ID of the linked reporting currency

pf_id

pfId

bigint(20)

YES

MUL

ID of the linked portfolio

pr_id

prId

bigint(20)

YES

MUL

ID of the parent portfolio report

security

security

bigint(20)

YES

MUL

ID of the linked security

pm2_pf_rep_sub_item (PortfolioReportSubItem)

Purhase lots in portfolio currency. Data gathered from transactions.

Field

Attribute

Type

Null

Key

Description

id

id

bigint(20)

NO

PRI

accrued_cost

accruedCost

double

NO

Sum of transaction cost1 and cost2 related to this purchase lot

accrued_cost_sec

accruedCostSec

double

NO

Sum of transaction cost1 and cost2 related to this purchase lot. In security currency

accrued_interest

accruedInterest

double

NO

When purchase lot is opened, a separate purchase lot with only the paid ( - )/received ( + ) accrued interest is created. When the purchase lot is closed, the transactions received ( + ) /paid ( - ) accrued interest is stored to the closed purchase lot.

accrued_profit

accruedProfit

double

NO

Sum of trade amount after tax, when transaction has a positive profit (either [profitEffect > 0 && cashflowEffect == 0] or [profitEffect > 0 && cashflowEffect > 0] or [profitEffect < 0 && cashflowEffect < 0])

amount

amount

double

NO

Open amount in the purchase lot

close_ex_post3rd_cost_cat1

closeExPost3RdCostCat1

double

NO

Ex-post costs from a transaction that closes the purchase lot

close_ex_post3rd_cost_cat10

closeExPost3RdCostCat10

double

NO

close_ex_post3rd_cost_cat2

closeExPost3RdCostCat2

double

NO

close_ex_post3rd_cost_cat3

closeExPost3RdCostCat3

double

NO

close_ex_post3rd_cost_cat4

closeExPost3RdCostCat4

double

NO

close_ex_post3rd_cost_cat5

closeExPost3RdCostCat5

double

NO

close_ex_post3rd_cost_cat6

closeExPost3RdCostCat6

double

NO

close_ex_post3rd_cost_cat7

closeExPost3RdCostCat7

double

NO

close_ex_post3rd_cost_cat8

closeExPost3RdCostCat8

double

NO

close_ex_post3rd_cost_cat9

closeExPost3RdCostCat9

double

NO

close_ex_post_pf_cost_cat1

closeExPostPfCostCat1

double

NO

close_ex_post_pf_cost_cat10

closeExPostPfCostCat10

double

NO

close_ex_post_pf_cost_cat2

closeExPostPfCostCat2

double

NO

close_ex_post_pf_cost_cat3

closeExPostPfCostCat3

double

NO

close_ex_post_pf_cost_cat4

closeExPostPfCostCat4

double

NO

close_ex_post_pf_cost_cat5

closeExPostPfCostCat5

double

NO

close_ex_post_pf_cost_cat6

closeExPostPfCostCat6

double

NO

close_ex_post_pf_cost_cat7

closeExPostPfCostCat7

double

NO

close_ex_post_pf_cost_cat8

closeExPostPfCostCat8

double

NO

close_ex_post_pf_cost_cat9

closeExPostPfCostCat9

double

NO

close_ex_post_sec_cost_cat1

closeExPostSecCostCat1

double

NO

close_ex_post_sec_cost_cat10

closeExPostSecCostCat10

double

NO

close_ex_post_sec_cost_cat2

closeExPostSecCostCat2

double

NO

close_ex_post_sec_cost_cat3

closeExPostSecCostCat3

double

NO

close_ex_post_sec_cost_cat4

closeExPostSecCostCat4

double

NO

close_ex_post_sec_cost_cat5

closeExPostSecCostCat5

double

NO

close_ex_post_sec_cost_cat6

closeExPostSecCostCat6

double

NO

close_ex_post_sec_cost_cat7

closeExPostSecCostCat7

double

NO

close_ex_post_sec_cost_cat8

closeExPostSecCostCat8

double

NO

close_ex_post_sec_cost_cat9

closeExPostSecCostCat9

double

NO

closing_date

closingDate

datetime(6)

YES

Date, when the purchase lot was closed. Null if it is still open.

commitment

commitment

double

NO

Same as remaining commitment. When transaction has commitment effect, a new purchase lot is created with commitment. If transaction has amountEffect, then commitment = tr's amount. Otherwise, its commitment = tr's price (amount x unit price)

commitment_total

commitmentTotal

double

NO

The total commitment is set, when transaction has commitment effect and no cashflow nor amount effect. Then commitmentTotal is set to be transaction's trade amount (either positive or negative depending on the commitment effect add/reduce)

cost

cost

double

NO

Total cost of the opening transaction. Zero, if the purchase lot is closed.

cost_sec

cost

double

NO

Total cost of the opening transaction in security currency. Zero, if the purchase lot is closed.

ded_pur_trade_amount

dedPurTradeAmount

double

YES

The total trade amount deducted from the purchase lot due to transaction having purchase unit price effect that deduct purchase trade amount.

description

description

longtext

YES

Text that describes how the purchase lot was created. Included transaction type code, book transaction date and information about ratios and removed securities, if related to the purchase lot.

duration

duration

double

NO

Used internally to track the lifetime of a purchase lot when remove security effect is applied with a ratio.

format_decimal

formatDecimal

int(11)

NO

Number of decimal to be applied in the amount field. Comes from transaction and its security and stored into purchase lot for efficiency reasons.

indexed_value

indexedValue

double

NO

Not used

indexed_value_change

indexedValueChange

double

NO

Not used

indexed_value_start

indexedValueStart

double

NO

Not used

pri_key

key

varchar(255)

YES

Primarily security code, if security missing then account number. However, if security type has setting to separate short and long positions, the security code has ending "-NEG" when position is negative => separates long and short purchase lots.

lot_number

lotNumber

int(11)

NO

Running number (1,2,3..) of a purchase lot in a position. First purchase lot creating the position has a lot number one etc.

market_date

marketDate

datetime(6)

YES

Not used

market_fx_rate

marketFxRate

double

NO

Not used

market_trade_amount

marketTradeAmount

double

NO

Not used

market_trade_amount_start

marketTradeAmountStart

double

NO

Not used

market_unit_price

marketUnitPrice

double

NO

Not used

open_amount

openAmount

double

NO

Amount of an open purchase lot. Same as 'amount'

open_ex_post3rd_cost_cat1

openExPost3RdCostCat1

double

NO

Ex-post costs from a transaction that opens the purchase lot

open_ex_post3rd_cost_cat10

openExPost3RdCostCat10

double

NO

open_ex_post3rd_cost_cat2

openExPost3RdCostCat2

double

NO

open_ex_post3rd_cost_cat3

openExPost3RdCostCat3

double

NO

open_ex_post3rd_cost_cat4

openExPost3RdCostCat4

double

NO

open_ex_post3rd_cost_cat5

openExPost3RdCostCat5

double

NO

open_ex_post3rd_cost_cat6

openExPost3RdCostCat6

double

NO

open_ex_post3rd_cost_cat7

openExPost3RdCostCat7

double

NO

open_ex_post3rd_cost_cat8

openExPost3RdCostCat8

double

NO

open_ex_post3rd_cost_cat9

openExPost3RdCostCat9

double

NO

open_ex_post_pf_cost_cat1

openExPostPfCostCat1

double

NO

open_ex_post_pf_cost_cat10

openExPostPfCostCat10

double

NO

open_ex_post_pf_cost_cat2

openExPostPfCostCat2

double

NO

open_ex_post_pf_cost_cat3

openExPostPfCostCat3

double

NO

open_ex_post_pf_cost_cat4

openExPostPfCostCat4

double

NO

open_ex_post_pf_cost_cat5

openExPostPfCostCat5

double

NO

open_ex_post_pf_cost_cat6

openExPostPfCostCat6

double

NO

open_ex_post_pf_cost_cat7

openExPostPfCostCat7

double

NO

open_ex_post_pf_cost_cat8

openExPostPfCostCat8

double

NO

open_ex_post_pf_cost_cat9

openExPostPfCostCat9

double

NO

open_ex_post_sec_cost_cat1

openExPostSecCostCat1

double

NO

open_ex_post_sec_cost_cat10

openExPostSecCostCat10

double

NO

open_ex_post_sec_cost_cat2

openExPostSecCostCat2

double

NO

open_ex_post_sec_cost_cat3

openExPostSecCostCat3

double

NO

open_ex_post_sec_cost_cat4

openExPostSecCostCat4

double

NO

open_ex_post_sec_cost_cat5

openExPostSecCostCat5

double

NO

open_ex_post_sec_cost_cat6

openExPostSecCostCat6

double

NO

open_ex_post_sec_cost_cat7

openExPostSecCostCat7

double

NO

open_ex_post_sec_cost_cat8

openExPostSecCostCat8

double

NO

open_ex_post_sec_cost_cat9

openExPostSecCostCat9

double

NO

open_trade_amount

openTradeAmount

double

NO

Trade amount of an open purchase lot. Zero, if the purchase lot is closed.

opening_date

openingDate

datetime(6)

YES

Date when the purchase lot was opened based on the transaction date.

opening_settlement_date

openingSettlementDate

datetime(6)

YES

Settlement date of the transaction that opened the purchase lot.

other_cost

otherCost

double

NO

Sum of trade amount after tax, when transaction has a negative profit (either [profitEffect < 0 && cashflowEffect == 0] or [profitEffect > 0 && cashflowEffect < 0] or [profitEffect < 0 && cashflowEffect > 0])

purchase_accrued_interest

purchaseAccruedInterest

double

NO

Accrued interest of a purchase transaction related to the purchase lot.

purchase_amount

purchaseAmount

double

NO

Amount of the purchase transaction related to the purchase lot.

purchase_cost

purchaseCost

double

NO

Total cost of the purchase transaction related to the purchase lot.

purchase_cost_sec

purchaseCostSec

double

NO

Total cost in security currency of the purchase transaction related to the purchase lot.

purchase_date

purchaseDate

datetime(6)

YES

Transaction date of the purchase transaction related to the purchase lot.

purchase_fx_rate

purchaseFxRate

double

NO

Report fx rate of the purchase transaction related to the purchase lot.

purchase_trade_amount

purchaseTradeAmount

double

NO

Trade amount of the purchase transaction related to the purchase lot.

purch_tr_id

purchTrId

varchar(255)

YES

Internal ID of the purchase transaction related to the purchase lot.

purchase_unit_price

purchaseUnitPrice

double

NO

Unit price of the purchase transaction related to the purchase lot.

purchase_unit_price_view

purchaseUnitPriceView

double

NO

Unit price view of the purchase transaction related to the purchase lot.

realized_profit

realizedProfit

int(11)

NO

1, if the transaction had realized profit effect (except indirectly). Otherwise 0. Can be used to fetch purchase lot for tax reporting.

realized_profit_date

realizedProfitDate

datetime(6)

YES

If realized profit was 1, the transaction date when the profit was realized.

record_open_trade_amount

recordOpenTradeAmount

double

NO

Open trade amount, if the transaction booked transaction date <> transaction date (also known as original trade amount).

record_purchase_date

recordPurchaseDate

datetime(6)

YES

Purchase date, if the transaction booked transaction date <> transaction date

record_purchase_fx_rate

recordPurchaseFxRate

double

NO

Purchase fx rate, if the transaction booked transaction date <> transaction date

rec_pur_trade_amount

recPurTradeAmount

double

YES

Purchase trade amount, if the transaction booked transaction date <> transaction date

rec_pur_unit_price

recPurUnitPrice

double

YES

Purchase unit price, if the transaction booked transaction date <> transaction date

record_purchase_unit_price_view

recordPurchaseUnitPriceView

double

NO

Purchase unit price view, if the transaction booked transaction date <> transaction date

record_sold_date

recordSoldDate

datetime(6)

YES

Sold date, if the transaction booked transaction date <> transaction date

record_sold_fx_rate

recordSoldFxRate

double

NO

Sold fx rate, if the transaction booked transaction date <> transaction date

record_sold_profit

recordSoldProfit

double

NO

Sold profit, if the transaction booked transaction date <> transaction date

record_sold_profit_sec

recordSoldProfitSec

double

NO

Sold profit in security currency, if the transaction booked transaction date <> transaction date

rec_sold_trade_amount

recSoldTradeAmount

double

YES

Sold trade amount, if the transaction booked transaction date <> transaction date

rec_sold_unit_price

recSoldUnitPrice

double

YES

Sold unit price, if the transaction booked transaction date <> transaction date

record_sold_unit_price_view

recordSoldUnitPriceView

double

NO

Sold unit price view, if the transaction booked transaction date <> transaction date

share_of_parent

shareOfParent

double

NO

Not used

share_of_portfolio

shareOfPortfolio

double

NO

Not used

sold_accrued_interest

soldAccruedInterest

double

NO

Accrued interest from the sell transaction related to the purchase lot.

sold_amount

soldAmount

double

NO

Amount from the sell transaction related to the purchase lot.

sold_cost

soldCost

double

NO

Total cost from the sell transaction related to the purchase lot.

sold_cost_sec

soldCostSec

double

NO

Total cost in security currency from the sell transaction related to the purchase lot.

sold_date

soldDate

datetime(6)

YES

Transaction date from the sell transaction related to the purchase lot.

sold_fx_rate

soldFxRate

double

NO

Report fx rate from the sell transaction related to the purchase lot.

sold_profit

soldProfit

double

NO

When purchase lot is closed with a transaction that has realized profit effect, the sold profit is set to be the difference between sold trade amount and purchase trade amount

sold_profit_sec

soldProfitSec

double

NO

When purchase lot is closed with a transaction that has realized profit effect, the sold profit is set to be the difference between sold trade amount in security currency and purchase trade amount in security currency

sold_trade_amount

soldTradeAmount

double

NO

Trade amount from the sell transaction related to the purchase lot.

sold_tr_id

soldTrId

varchar(255)

YES

Transaction id of the sell transaction related to the purchase lot.

sold_unit_price

soldUnitPrice

double

NO

Unit price from the sell transaction related to the purchase lot.

sold_unit_price_view

soldUnitPriceView

double

NO

Unit price view from the sell transaction related to the purchase lot.

start_trade_amount

startTradeAmount

double

NO

Trade amount that opened the purchase lot. Not reset when the purchase lot is closed.

tr_id

trId

varchar(255)

YES

ID of the transaction that last affected the purchase lot.

value_change_absolute

valueChangeAbsolute

double

NO

Not used

value_change_relative

valueChangeRelative

double

NO

Not used

version

version

int(11)

YES

Version number (1,2,3...)

yield_to_maturity

yieldToMaturity

double

NO

Not used

account

account

bigint(20)

YES

MUL

ID of the account from the transaction that last affected the purchase lot.

currency

currency

bigint(20)

NO

MUL

ID of the currency security from the transaction account (or security, if no account) that last affected the purchase lot.

pf_id

pfId

bigint(20)

NO

MUL

ID of the portfolio that this purchase belongs to.

security

security

bigint(20)

NO

MUL

ID of the security that this purchase is about.

pm2_pf_act_item (AccountItem)

Field

Attribute

Type

Null

Key

Description

id

id

bigint(20)

NO

PRI

accrued_profit

accruedProfit

double

NO

Sum of all profits aggregated from the account sub items in portfolio currency

balance

balance

double

NO

Account balance valuated in the report currency on the market date

balance_acc_curr

balanceAccCurr

double

NO

Account balance in the account currency

balance_pv

balancePv

double

NO

Account balance in report currency. Equal to balance, if account currency is the same as portfolio currency. Otherwise, its the purchase value of the balance in the portfolio currency.

balance_pv_unit_price

balancePvUnitPrice

double

NO

Account balance purchase value's unit price. Equals to 1 if account and portfolio has the same currency

balance_start

balanceStart

double

NO

Balance in the beginning of the date in portfolio currency

balance_unit_price

balanceUnitPrice

double

NO

Unit price of the balance

balance_unit_price_start

balanceUnitPriceStart

double

YES

Unit price of the balance in the beginning of the day

cashflow

cashflow

double

NO

Total summed up cashflow in the account during the day in portfolio currency

cost

cost

double

NO

Accrued total costs from transactions

d_accrued_profit

dAccruedProfit

double

NO

Daily change in accrued profit

d_balance

dBalance

double

NO

Daily change in balance

d_balance_pv

dBalancePv

double

NO

Daily change in balance (purchase value)

d_cost

dCost

double

NO

Daily change in cost

d_ex_post3rd_cost_cat1

dExPost3RdCostCat1

double

NO

Daily change in ex post costs

d_ex_post3rd_cost_cat10

dExPost3RdCostCat10

double

NO

d_ex_post3rd_cost_cat2

dExPost3RdCostCat2

double

NO

d_ex_post3rd_cost_cat3

dExPost3RdCostCat3

double

NO

d_ex_post3rd_cost_cat4

dExPost3RdCostCat4

double

NO

d_ex_post3rd_cost_cat5

dExPost3RdCostCat5

double

NO

d_ex_post3rd_cost_cat6

dExPost3RdCostCat6

double

NO

d_ex_post3rd_cost_cat7

dExPost3RdCostCat7

double

NO

d_ex_post3rd_cost_cat8

dExPost3RdCostCat8

double

NO

d_ex_post3rd_cost_cat9

dExPost3RdCostCat9

double

NO

d_ex_post_pf_cost_cat1

dExPostPfCostCat1

double

NO

d_ex_post_pf_cost_cat10

dExPostPfCostCat10

double

NO

d_ex_post_pf_cost_cat2

dExPostPfCostCat2

double

NO

d_ex_post_pf_cost_cat3

dExPostPfCostCat3

double

NO

d_ex_post_pf_cost_cat4

dExPostPfCostCat4

double

NO

d_ex_post_pf_cost_cat5

dExPostPfCostCat5

double

NO

d_ex_post_pf_cost_cat6

dExPostPfCostCat6

double

NO

d_ex_post_pf_cost_cat7

dExPostPfCostCat7

double

NO

d_ex_post_pf_cost_cat8

dExPostPfCostCat8

double

NO

d_ex_post_pf_cost_cat9

dExPostPfCostCat9

double

NO

d_ex_post_sec_cost_cat1

dExPostSecCostCat1

double

NO

d_ex_post_sec_cost_cat10

dExPostSecCostCat10

double

NO

d_ex_post_sec_cost_cat2

dExPostSecCostCat2

double

NO

d_ex_post_sec_cost_cat3

dExPostSecCostCat3

double

NO

d_ex_post_sec_cost_cat4

dExPostSecCostCat4

double

NO

d_ex_post_sec_cost_cat5

dExPostSecCostCat5

double

NO

d_ex_post_sec_cost_cat6

dExPostSecCostCat6

double

NO

d_ex_post_sec_cost_cat7

dExPostSecCostCat7

double

NO

d_ex_post_sec_cost_cat8

dExPostSecCostCat8

double

NO

d_ex_post_sec_cost_cat9

dExPostSecCostCat9

double

NO

d_other_cost

dOtherCost

double

NO

Daily change in other costs

indexed_value

indexedValue

double

NO

Indexed value - starting from 100 and changed daily based on daily TWR

indexed_value_change

indexedValueChange

double

NO

Logarithmic daily relative change in the indexed value

indexed_value_start

indexedValueStart

double

NO

Indexed value in the start of the day

pri_key

priKey

varchar(255)

YES

1) Account's number 2) Empty, if transaction is in another portfolio 3) NO-ACCOUNT if no account linked

market_date

marketDate

datetime(6)

YES

Date of the observation

neg_cashflow

negCashflow

double

NO

Sum of all negative cashflows during the day

neg_net_cashflow

negNetCashflow

double

NO

Sum of all negative net cashflows during the day

net_cashflow

netCashflow

double

NO

Sum of all net cashflows from related account sub items during the day

other_cost

otherCost

double

NO

Sum of all other costs from related account sub items during the day

pos_cashflow

posCashflow

double

NO

Sum of all positive cashflows during the day

pos_net_cashflow

posNetCashflow

double

NO

Sum of all positive net cashflows during the day

share_of_portfolio

shareOfPortfolio

double

NO

Account balance share of the total portfolio market value

share_of_portfolio_start

shareOfPortfolioStart

double

NO

Account balance share of the total portfolio market value in the beginning of the day

version

version

int(11)

YES

account

account

bigint(20)

YES

MUL

ID of the linked account

curr_id

currId

bigint(20)

YES

MUL

ID of the linked currency

pf_id

pfId

bigint(20)

YES

MUL

ID of the linked portfolio

pr_id

prId

bigint(20)

YES

MUL

ID of the linked portfolio report

pm2_pf_act_sub_item (AccountSubItem)

Field

Attribute

Type

Null

Key

Description

id

id

bigint(20)

NO

PRI

cashflow

cashflow

double

NO

Cashflow of a transaction (+/-) in the portfolio's reporting currency rounded to number of decimals defined in the transaction's account currency.

cashflow_acc_curr

cashflowAccCurr

double

NO

Cashflow of a transaction (+/-) in the transaction's account currency rounded to number of decimals defined in the transaction's account currency.

cost

cost

double

NO

Total cost of the transaction in the portfolio currency

cost_acc_curr

costAccCurr

double

NO

Total cost of the transaction in the account currency

ex_post3rd_cost_cat1

exPost3RdCostCat1

double

NO

Transaction's ex-post costs if the transaction type does not have amount effect

ex_post3rd_cost_cat10

exPost3RdCostCat10

double

NO

ex_post3rd_cost_cat2

exPost3RdCostCat2

double

NO

ex_post3rd_cost_cat3

exPost3RdCostCat3

double

NO

ex_post3rd_cost_cat4

exPost3RdCostCat4

double

NO

ex_post3rd_cost_cat5

exPost3RdCostCat5

double

NO

ex_post3rd_cost_cat6

exPost3RdCostCat6

double

NO

ex_post3rd_cost_cat7

exPost3RdCostCat7

double

NO

ex_post3rd_cost_cat8

exPost3RdCostCat8

double

NO

ex_post3rd_cost_cat9

exPost3RdCostCat9

double

NO

ex_post_pf_cost_cat1

exPostPfCostCat1

double

NO

ex_post_pf_cost_cat10

exPostPfCostCat10

double

NO

ex_post_pf_cost_cat2

exPostPfCostCat2

double

NO

ex_post_pf_cost_cat3

exPostPfCostCat3

double

NO

ex_post_pf_cost_cat4

exPostPfCostCat4

double

NO

ex_post_pf_cost_cat5

exPostPfCostCat5

double

NO

ex_post_pf_cost_cat6

exPostPfCostCat6

double

NO

ex_post_pf_cost_cat7

exPostPfCostCat7

double

NO

ex_post_pf_cost_cat8

exPostPfCostCat8

double

NO

ex_post_pf_cost_cat9

exPostPfCostCat9

double

NO

ex_post_sec_cost_cat1

exPostSecCostCat1

double

NO

ex_post_sec_cost_cat10

exPostSecCostCat10

double

NO

ex_post_sec_cost_cat2

exPostSecCostCat2

double

NO

ex_post_sec_cost_cat3

exPostSecCostCat3

double

NO

ex_post_sec_cost_cat4

exPostSecCostCat4

double

NO

ex_post_sec_cost_cat5

exPostSecCostCat5

double

NO

ex_post_sec_cost_cat6

exPostSecCostCat6

double

NO

ex_post_sec_cost_cat7

exPostSecCostCat7

double

NO

ex_post_sec_cost_cat8

exPostSecCostCat8

double

NO

ex_post_sec_cost_cat9

exPostSecCostCat9

double

NO

ext_info

extInfo

longtext

YES

Transaction's ext info

fx_rate

fxRate

double

NO

Transaction's book fx rate

fx_rate_acc_curr

fxRateAccCurr

double

NO

Transaction's account fx rate

int_info

intInfo

longtext

YES

Transaction's int info

item_code

itemCode

varchar(255)

YES

If transaction is linked to a security => security code, else if linked to an account => account number

item_name

itemName

varchar(255)

YES

If transaction is linked to a security => security name, else if linked to an account => account name

pri_key

priKey

varchar(255)

YES

Account item key (see account item)

net_cashflow

netCashflow

double

NO

Net cashflow in portfolio currency from the transaction, or cashflow, if the transaction was linked to a second portfolio.

net_cashflow_acc_curr

netCashflowAccCurr

double

NO

Net cashflow in account currency from the transaction, or cashflow, if the transaction was linked to a second portfolio.

other_cost

otherCost

double

NO

If transaction type has negative profit effect and no security linked to it, its trade amount excl. tax is recorded as other cost (positive number)

profit

profit

double

NO

If transaction type has positive profit effect and no security linked to it, its trade amount excl. tax is recorded as profit (positive number) in portfolio currency

profit_acc_curr

profitAccCurr

double

NO

If transaction type has positive profit effect and no security linked to it, its trade amount excl. tax is recorded as profit (positive number) in account currency

settlement_date

settlementDate

datetime(6)

YES

Transaction's settlement date

transaction_date

transactionDate

datetime(6)

YES

Transaction's transaction date

transaction_id

transactionId

varchar(255)

YES

Transaction's ID

version

version

int(11)

YES

ac_id

acId

bigint(20)

YES

MUL

Account ID of the linked account

curr_id

currId

bigint(20)

YES

MUL

Security ID of the linked currency (primarily security's currency, alternative account's currency)

pf_id

pfId

bigint(20)

YES

MUL

Portfolio ID of the linked portfolio

In this section: