Single_Line_Transactions

Introduction

Following customer feedback we have added a table that has the entire transaction in a single row (unlike Flash Blotter which split the transaction into two rows for speed of calculations purposes). For the avoidance of doubt, this table is derived from the flash_blotter table (and any changes to this table have zero effect on flash_blotter)

The fields in this table are as follows

Field Name	Data Type	Description
blotter_id	integer	Blotter ID (unique for each transaction)
ticketref	text	Ticket Reference (Unique for each transaction. Either blotter_id or ticketref can be used as unique transactions identifiers)
traded_on	date	Trade Date Same as from flash_blotter, except data type is date (instead of timestamp)
settled_on	date	Settlement Date
ccy_account_code	text	Currency Account e.g. canopy_demo-cssg01-usd-01
contract_currency	text	Transaction Currency e.g. USD
ticker	text	Ticker for trade types Transfer In/Transfer Out/Buy/Sell it will be the Canopy Ticker e.g 'IBM_US' for transactions not moving a security e.g. Dividends, Misc Expenses etc. it will be 'Cash'
isin	text	ISIN for trade types Transfer In/Transfer Out/Buy/Sell it will be the ISIN of the security (only if available and blank otherwise) It will be blank for all other Trade Types (e.g. for Dividends / Misc Expense etc.). Also please look at field Related_Security_ISIN it is arranged like this so that calculating holdings as of a certain date is a single SQL query
security_name	text
contract_type	text	EquityContract, BondContract, FundContract etc. This is just an indication of the internal table in which security details are stored. This is NOT the user defined asset class.
trade_type	text	23 trade types in Canopy at the moment
nominal_amount	numeric
trade_price	numeric
effective_price	numeric	This is settlement_amount_ccy / nominal_amount (i.e. translates brokerage, accrued interest and other costs etc into an all-in price)
gross_amount_ccy	numeric	nominal_amount x trade_price
accrued_interest	numeric
withholding_tax	numeric
brokerage	numeric
other_expenses	numeric
settlement_amount_ccy	numeric
quantity	numeric	Here for backward compatability. please use nominal_amount
number_of_contracts	numeric
effective_amount_ccy	numeric	For dividend in kind transactions captures the equivalent amount in currency for reference purposes
custodian_ref	text
ticket_created_at	timestamp
ticket_updated_at	timestamp
price_updated_at	timestamp
custodian_code	text
custodian_name	text
parent_username	text
child_username	text
parent_display_name	text
child_display_name	text
base_ccy	text
contract_id	integer
contract_currency_id	integer
counterparty_id	integer
parent_user_id	integer
user_id	integer
user_account_id	integer
bank_narration	text
strategy_tag_id	integer
transaction_id	integer
transaction_type_id	integer
security_id	integer
related_security_id	integer
related_security_ticker	text	For distribution transactions contains the ticker of security on which this distribution has been paid
related_security_name	text	For distribution transactions contains the name of security on which this distribution has been paid
related_security_isin	text	For distribution transactions contains the isin of security on which this distribution has been paid
user_asset_class	text
user_geography	text
user_industry	text
canopy_asset_class	text
bb_composite_rating	text
em_dm	text
canopy_geography	text
partner_asset_class	text
partner_geography	text
day_1_notional_trade_ccy	numeric
day_1_mtm_trade_ccy	numeric
day_1_price_close	numeric
day_1_price_clean	numeric
day_1_fxrate_trade_ccy	numeric
day_1_mtm_usd	numeric
day_1_notional_usd	numeric
single_line_transactions_created_at	timestamp