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 |
| All | Date and time of the trade in Eastern time. If time is unknown, specify as |
Account | Yes | CHAR(50) |
| All | Name of the account (PropReports Account Id). Anything after the first 20 characters will be ignored. |
B/S | Yes | CHAR(1) |
| All | Must be one of: |
Qty | Yes | DECIMAL(18, 8) UNSIGNED |
| All | Number of shares or contracts traded. Number must always be positive. |
Symbol | Yes | CHAR(48) |
| 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) |
| All | Price of the trade. |
Route | No | CHAR(10) |
| 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) |
| All | MPID / identifier of the counterparty. Will be converted to uppercase. |
Liq | No | CHAR(8) |
| All | Liquidity flag. This value is case-sensitive. |
Comm | No | DECIMAL(10, 4) |
| All | Commission. For all fees a positive number is a debit and negative is a credit. |
Ecn | No | DECIMAL(14, 8) |
| All | Exchange/ECN fee |
SEC | No | DECIMAL(8, 4) |
| All | SEC (Section 31) transaction fee |
| ORF | No | DECIMAL(8,4) | 1.234 | All | Options Regulatory Fee |
TAF | No | DECIMAL(10, 6) |
| 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) |
| All | National Securities Clearing Corporation fee |
| Acc | No | DECIMAL(8, 4) | 1.234 | All | Access fee |
Clr | No | DECIMAL(10, 4) |
| All | Other clearing fee |
Misc | No | DECIMAL(10, 4) |
| All | Miscellaneous fee |
| Columns below are optional and can be in any order. | |||||
Order Id | No | CHAR(25) |
| 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) |
| 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) |
| All | For regular trades, leave this field blank. Specify
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 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) |
| 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. |
Currency | No | CHAR(3) |
| 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.