Generic Execution format

If you are looking to post one or two trades into PropReports, you can follow instructions on how to manually post a trade. To load a large number of executions (fills) into PropReports, a comma-separated values (CSV) file can be used. Please follow RFC 418 guidelines when creating CSV files. Notably, if you will have field values with commas, the field value must be in quotes.

Sample Data

Download template as: trade_template.csv. If creating by hand, you can use a text editor or Excel (must save as csv before uploading). For PropReports to recognize the file format, the column heading names must be exact. Columns after the Misc column are optional and can be in any order.

Date/Time,Account,B/S,Qty,Symbol,Price,Route,Exec Bkr,Contra,Liq,Comm,Ecn,SEC,ORF,TAF,NFA,CAT,NSCC,Acc,Clr,Misc,Order Id,Fill Id,Status
06/17/2010 16:00:00,1234567,S,800,RIG,49.44,INET,MFTP,PFSI,A,1,2,3,4,5,6,7,8,9,10,11,ABC-123,12,

Fields

Name	Mandatory?	Format	Sample	Available in Version	Description
Date/Time	Yes	MM/DD/YYYY HH:MM:SS	`06/17/2010 16:00:00`	All	Date and time of the trade in Eastern time. If time is unknown, specify as `00:00:00`
Account	Yes	CHAR(50)	`ABC1234567`	All	Name of the account (PropReports Account Id). Anything after the first 20 characters will be ignored.
B/S	Yes	CHAR(1)	`B`	All	Must be one of: `B` = Buy, `S` = Sell, `T` = Short, `C` = Cover. See side and intention codes for more information.
Qty	Yes	DECIMAL(18, 8) UNSIGNED	`5000`	All	Number of shares or contracts traded. Number must always be positive.
Symbol	Yes	CHAR(48)	`IBM`	All	Ticker symbol of the instrument being traded. Please follow the symbol naming convention. To specify a ticker in a different format you can name this column "Sterling Symbol," "Reuters Symbol," "Nasdaq IP Symbol," "Bloomberg Symbol" or "Wedbush Symbol".
Price	Yes	DECIMAL(18,8)	`1.234`	All	Price of the trade.
Route	No	CHAR(10)	`ARCA`	All	Name of the route, ECN or exchange. Will be converted to uppercase.
Exec Bkr	No	CHAR(10)	`PFSI`	All	MPID / identifier of the executing broker. Will be converted to uppercase.
Contra	No	CHAR(10)	`PFSI`	All	MPID / identifier of the counterparty. Will be converted to uppercase.
Liq	No	CHAR(8)	`A`	All	Liquidity flag. This value is case-sensitive.
Comm	No	DECIMAL(10, 4)	`1.234`	All	Commission. For all fees a positive number is a debit and negative is a credit.
Ecn	No	DECIMAL(14, 8)	`1.234`	All	Exchange/ECN fee
SEC	No	DECIMAL(8, 4)	`1.234`	All	SEC (Section 31) transaction fee
ORF	No	DECIMAL(8,4)	`1.234`	All	Options Regulatory Fee
TAF	No	DECIMAL(10, 6)	`1.234`	All	Trade activity fee
NFA	No	DECIMAL(8,4)	`1.234`	All	NFA Fee
CAT	No	DECIMAL(10, 4)	`1.234`	1.14.1+	CAT (Consolidated Audit Model) fee
NSCC	No	DECIMAL(8, 4)	`1.234`	All	National Securities Clearing Corporation fee
Acc	No	DECIMAL(8, 4)	`1.234`	All	Access fee
Clr	No	DECIMAL(10, 4)	`1.234`	All	Other clearing fee
Misc	No	DECIMAL(10, 4)	`1.234`	All	Miscellaneous fee
Columns below are optional and can be in any order.
Order Id	No	CHAR(25)	`ABC-123`	All	Ticket or Order Id. There can be more than one execution (fill) with the same Order Id. Order Ids are not case sensitive.
Fill Id	No	CHAR(25)	`ABC-12345`	All	An execution-specific identifier. Should be unique for each fill for the trade date but this not enforced. Fill Ids are not case sensitive.
Status	No	CHAR(7)	`CXLD`	All	For regular trades, leave this field blank. Specify `MF` (manual fee) to process execution as a regular trade but retain fees as given (do not apply any plans). Specify `NC` to indicate non-clearable fills – if reconciliation vs the clearing firm is enabled, only clearable executions will be compared. `MF` and `NC` codes can be specified together (e.g. `MF NC`) (version 1.5.95.41 or greater). `ASSIGN`, `DELIST`, `DIVIDEND`, `EXERCISE`, `EXPIRE`, `JOURNAL`, `M/A`, `RENAME`, `SPINOFF`, `SPLIT` - to adjust positions or post corporate actions. See execution status values for more information. Note that for these executions any fees are imported as specified and no plans are applied (version 1.5.95.41 or greater). `CXLD` - a canceled or busted execution. Tells PropReports to find and cancel an existing fill. These fills will remain visible with a "CXLD" indicator and will not count towards P/L, positions or any other totals. `CORR` - Cancel / correct. PropReports will attempt to cancel an existing trade and post a new trade that has some or all of the following fields corrected: price, quantity, route or liquidity. Corrected values must be specified in columns `New Price`, `New Qty`, `New Route` and `New Liq`, respectively. REPL - Replace. Same as `CORR` except the original execution is deleted and is not viewable (version 1.9.63 or greater). Please refer to the section on specifying cancels and corrections below for an explanation of how PropReports locates an execution to correct.
Display Name	No	CHAR(50)	`John`	All	The account name you and the traders will see in reports (see Account ID & Names). If an account already exists, the display name will only be updated if it has not been already set.
Clearing Id	No	CHAR(50)	`ABC-123`	All	The Clearing Id is the Account Id at your clearing firm (see Account ID & Names ).
Multiplier	No	DECIMAL(15,4)	`1`	All	The multiplier (can be called "contract size" for options and "value multiplier" for futures) to use when computing profit and loss. If the value is empty or column is omitted, PropReports will default to 1 for equities and futures, and 100 for options.
ISIN	No	CHAR(12)	`US0378331005`	1.16.1+	International Securities Identification Number (ISIN) associated with the security. Invalid values will be ignored.
CUSIP	No	CHAR(9)	037833100	1.16.1+	Committee on Uniform Security Identification Procedures (CUSIP) code associated with the security. Invalid values will be ignored.
SEDOL	No	CHAR(7)	`0540528`	All	Stock Exchange Daily Official List (SEDOL) identifier associated with the security. Invalid values will be ignored.
FIGI	No	CHAR(12)	`BBG000BLNNV0`	1.16.1+	Financial Instrument Global Identifier (FIGI) associated with the security. Invalid values are ignored.
New Price	No	DECIMAL(18,8)	`1.234`	All	If status field is set to CORR, specifies the corrected price.
New Qty	No	DECIMAL(18, 8) UNSIGNED	`5000`	All	If status field is set to CORR, specifies the corrected quantity.
New Route	No	CHAR(10)	`ARCA`	All	If status field is set to CORR, specifies the corrected route, ECN or exchange.
New Liq	No	CHAR(8)	`A`	All	If status field is set to CORR, specifies the corrected liquidity flag.
New Comm	No	DECIMAL(10, 4)	1.234	1.16.53+	If status field is set to CORR, specifies the corrected commission.
Currency	No	CHAR(3)	`USD`	All	Three-character ISO 4217 code for the currency in which the security is traded. If blank, PropReports will attempt to guess the currency based on the symbol.
Source	No	CHAR(32)	`Sterling`	All	Source of the data being uploaded (see list of source names). If the column is not present, the source defaults to Generic.
Entry Date	No	MM/DD/YYYY	`06/30/2012`	All	Date the trade has been entered (useful when uploading As-Of trades). Blank value will set the entry date to be the same as the trade date.
Int Route	No	CHAR(10)	`EDGA`	All	Name of the internal route. Will be converted to uppercase.
Int Liq	No	CHAR(8)	`A`	All	Liquidity flag sent by the internal route. This value is case-sensitive.
Int Contra	No	CHAR(10)	PFSI	All	MPID / identifier of the counterparty sent by the internal route. Will be converted to uppercase.
Capacity	No	CHAR(1)	A	All	Trading capacity of the order. A = Agency, C = Agency Cross, P = Principal, R = Riskless Principal. Omit if unknown.

Specifying Symbols

Please specify the ticker following the PropReports symbol naming convention. We acknowledge that at times this may not be possible (if, for example, the file is coming from another system). In those cases, PropReports tries to figure out the symbology used and converts the symbol when necessary. You can explicitly specify an alternate symbology in use by changing the symbol column name. The following are supported:

Field Name	Description	Sample Symbols
Bloomberg Symbol	Symbols include an exchange or country code in addition to the ticker.	YHOO US, ITTw US, AU/ LN, C/WS/B US, DL NA
Nasdaq IP Symbol	Symbology utilizing the Nasdaq Integrated Platform suffixes.	BRK.A, C+, BAC-A
Sterling Symbol	Symbols follow the same naming convention as the Sterling platform.	CpM, F/WS, FBPr, GGPw

Specifying Cancels and Corrections

If the status column is set to CXLD (cancel), CORR (cancel and correct) or REPL (replace), PropReports attempts to locate the specified execution. Therefore, the entry must closely match the originally-posted fill. The following fields are used to determine which fill is affected: Date/Time, Account, B/S, Qty, Symbol, Price, Order Id, and Fill Id. The fee fields are ignored.

Here's an example of a cancel of a 185 share ADBE buy trade in account ACCOUNT1:

Date/Time,Account,B/S,Qty,Symbol,Price,Route,Contra,Liq,Comm,Ecn,SEC,ORF,TAF,NFA,CAT,NSCC,Acc,Clr,Misc,Order Id,Status
01/20/2012 10:12:34,ACCOUNT1,B,185,ADBE,30.5,ISLD,,R,0,0,0,0,0,0,0,0,0,0,0,15527618,CXLD

Here's an example of correcting fill quantity from 185 to 100. The original fill would be marked canceled and a new one with the corrected quantity posted in its place.

Date/Time,Account,B/S,Qty,Symbol,Price,Route,Contra,Liq,Comm,Ecn,SEC,ORF,TAF,NFA,CAT,NSCC,Acc,Clr,Misc,Order Id,Status,New Qty
01/20/2012 10:12:34,ACCOUNT1,B,185,ADBE,30.5,ISLD,,R,0,0,0,0,0,0,0,0,0,0,0,15527618,CORR,100

Note: If the fill to modify cannot be found, no action is performed and an error will is shown in the Upload Log. In those cases, you will need to locate and modify the trade manually. Partial cancels are not supported at this time.

Note: If the upload specifying corrections is deleted, the modifications are rolled back - canceled or deleted executions are re-instated.

An alternate method to correct fills is to post entries with status "JOURNAL" reversing or partially reversing the original execution.

Enabling the Upload

Before uploading for the first time, you must to make sure the Generic data source is enabled

Step 1: Navigate to the Upload / Sources page.

Step 2: If the Generic source is in the Disabled Sources list, click Enable next to it to update the configuration.

Uploading the File

To load the file into PropReports, please follow these instructions.