BRKIMPORT - Broker Import/Reconcile
Jump to
What is BRKIMPORT
Major brokers can provide an automated recap of your trade positions in the form of a B2B transfer.
BRKIMPORT can be used to:-
-collect import data from a broker’s I.T. system, usually ftp or http or WebService. Can be new business or full open position.
-secure a broker’s imported data into a TOPEN type database for ITAS reporting
-secure new business from a .csv data source (fixed format) for mass entry to TERM, where the trades need to be confirmed in TERM/Confirm process tts.csv
-secure new business from a .txt or .csv data source (variable format, S01 controlled) for mass entry to TERM, where the trades need to be confirmed in TERM/Confirm process standard
-secure new business from a Webservice for mass entry to TERM, where the trades need to be confirmed in TERM/Confirm process WebService
There are various useful processes in TERM/Confirm, especially the Consolidate request – see ?help on Confirm grid for more details
-produce a XML export file for any list of client account F&O trades
-produce a STANDARD export file for any list of client account F&O trades - variable format, S01 controlled. See Standard export.
-action a reconciliation between the company's broker position and the imported broker position
-reconcile the company accounts with the company's version of their broker positions.
-reconcile accounting version of settlements and TERM/SETT contents, this will recognise where adjustments have been actioned manually in accounting only and not in trading
The external reconciliation reports the exception Account/instrument/prompt month with a full drill-down to the detail trades from both the ITAS source and the Broker import.
To use BRKIMPORT. The report tool bar item provides the route to the reconciliation procedures.
The external reconciliation will need entry of a broker code for which you have already collected their import. The import data will need to be recognised as the correct source by the date and time of its collection. This requires manual control to ensure that you are checking consistent sources of information, the cutoff date will default to yesterday so that todays' trades are ignored from the reconciliation.
The internal company reconciliations have several selections to restrict viewing, there are drill-downs to the detail .
The export procedure will produce a XML format file that can be emailed etc to the client. The export procedure will use the table filters to locate/dispatch the required information.
The R&N import uses ftp to access the relevant R&N website with secure username and password that are maintained in a text file in the directory itas\ran\ran.dat
The import procedure will expect the broker transmission to be placed in a ITS directory that will be setup by the user. The user also supplies the Broker code, type of import structure and the import completes with creation of the new table that can be used in BRKIMPORT for reconciliation and by TOPEN for standard reports. IMPORTNT need to specify in TCCM the codes used by the broker that is supplying the import data, i.e. the code they use for the instrument. If the BRKIMPORT fails to import with a message to the effect that 723 rows were read but 723 were NOT imported then check the TCCM data for the relevant expected instruments.
If you require a B2B link from a front-end trading system, Hivedome can develop the interface.
LTI, LIFFE Trades Import
The activation of this process (controlled by RPP) is the setup of the path to the common directory where the TRS/Liffe trades are placed. This is S01/View/F&O/B2B page. Also can setup control parameters for when service should poll directory i.e. start/end time of day and frequency to poll.
The data content of LTI record is:
ClearingSlipId = 17,10. This will be secured as TERM x External Serial No
Type = 31, 1. AA = "@", "Y", "N"
HalfTradeStatus = 33, 2. If code = UA or CA then trade is a Give-Up else RT or DE
CliRef = 35, 8.
Trader = 52, 3. CMP initials
Floor Broker = 55, 3. Translate to ITAS x CLI account by locating record using C2C reference; then check that the CLI is nominated as a Broker F&O Trading account
BS = 58, 1. B or S
ExType = 59, 1. P or C or blank
Cmy = 61, 3. Locate ITAS x TCCM using the Broker Interface code(s) from TCCM/This company setup
Prompt = 68, 6. YYYYMM
Series = 76, 5. Format of decimal place sis controlled by TCCM/3rd party/Futures settle Dp
Price = 81, 5. Format of decimal place sis controlled by TCCM/3rd party/Futures settle Dp
Lots = 86, 6
AccountCode (Seg) = 93, 1 "S", "Y", "N"
Broker = 97, 3. Ignore and use S01 setup for the Clearing Broker (ctrl20_ltibroker)
Time = 115, 6
Memo = 139, 12
UserSpecData = 151, 14. Translate to ITAS x CLI account by add ‘.T’ and check CLI is nominated as a Client F&O Trading account
ActionFlag = 217, 1
The rejection/failures will be shown in the log
The TTS files are .csv format for New Business where there are specific columns and there are very explicit rules for translating their content into acceptable ITAS content. There is a feature that allows consolidation of import trades into ITAS new business; trades with the same values for Trdr, Cli Ref, B/S, Prompt, Opt Series, Opt Type, Price, Broker, Brk A/c Ref, Give-Up, CMY, Code, (optional Physicals and Date) will be created as one trade with the sum of the lots of the constituent trades as the lots value and the latest Time for the constituent trades as the Time value. TERM is then needed to confirm the full import of the ITAS data into the positions. The TTS imported data can be viewed as Instruction trades in TENQ, prior to TERM/confirm import.
tts.csv format
1: Heading ‘TRDR’. Trader Initials, must be valid CMP user MANDATORY
Col 2: Heading CLI REF’ Client account. Can be the code without the .T. Else numeric content with explicit rules. The rules are only applied when the Operating company is setup as a Brokerage/Commission business MANDATORY
Col 3: Heading ‘B/S‘ Buy/Sell, B or S MANDATORY
Col 4: Heading ‘LOTS’ Number of lots MANDATORY
Col 5: Heading ‘PROMPT‘ Prompt period e.g. JAN-08 or Reuters format e.g. F14 = JAN-2014. F=Jan, G=Feb, H=Mar, J=Apr, K=May, M=May, N=Jul, Q=Aug, U=Sep, V=Oct, X=Nov, Z=Dec. MANDATORY
Col 6: Heading ‘OPT SERIES‘ Option Series, numeric MANDATORY
Col 7: Heading ‘OPT TYPE‘ Option type , P or C MANDATORY
Col 8: Heading ‘PRICE’ Trade Price MANDATORY
Col 9: Heading ‘BROKER’ Broker, CLI code setup accordingly without the .T. There are special rules applied when the Operating company is setup as a Brokerage/Commission business i.e. recognizes ABN and MAN codes and applies Segregated checking on Client/House account MANDATORY
Col 10: Heading ‘BRK A/C REF’ Broker Ref, freeform max 120 characters MANDATORY
Col 11: Heading ‘GIVE-UP’ Giveup trade, N or Y Optional, default = N
Col 12: Heading CMY’ ‘Commodity Code, can be the same code as used in TCCM or can be either of the Company/Broker translation codes MANDATORY
Col 13: Heading ‘TIME’ Time of trade; hhmm format MANDATORY
Col 14: Heading ‘CODE’ Code, valid TERMCODES/trade, max 3 characters
Col 15: Heading ‘DATE’ Trade date; dd-mmm-yy format. Optional, will use ITAS date if not present
Col 16: Heading ‘PHYSICAL’ Hedge TRADE reference, full 10 characters needed e.g. P00001.000
Col 17: Heading ‘EXTREF’ External Serial Ref, max 10 characters freeformat
Col 18: Heading ‘FLOOR BROKER’ Floor Broker, CLI code setup accordingly without the .T
Col 19: Heading ‘DEPT’ Department code, Optional – valid DEPT code
The content is determined in S01/View/Data Import-Export/fut02.
The list of available fields for entry needs to be ticked in S01 and this process will confirm content before proceeding.
The first row, first cell needs to have name fut02.
The 2nd row will have the technical database fields names e.g. fut02_trinit, fut02_client, in their respective columns.
The data content must be setup as ITAS would expect data entry i.e. ITAS full account references, codes and formats (date = dd-mmm-yy).
The Prompt can be entered in Reuters format i.e. F14=Jan14. F=Jan, G=Feb, H=Mar, J=Apr, K=May, M=May, N=Jul, Q=Aug, U=Sep, V=Oct, X=Nov, Z=Dec.
There are some ITAS rules applied by the process to ensure completeness of ITAS record e.g. commissions will be created if none specified on import scheme.
It is possible to have a single import file with references to different ITAS entities (companies) whereby the TERM records will be created in their respective company tables. This needs a column in the file named as ‘comp’ with the row content being the ITAS company code.
Internal trades can be entered; the file will need their external serial reference entered (fut02_externalserial) and the internal code (fut02_internal) = ‘1’ on the 1st record and ‘2’ on the 2nd Record i.e. other side of the internal. There will be consistency checking for the 2 records which must be linked via broker = ‘INTERNAL.T’, commodity code, prompt month, trade date, price and lots must be the same so that ITAS can match/create a true Internal trade.
There is a feature that allows consolidation of import trades into ITAS new business; trades with the same values for Trade Date, Account, Instrument, Prompt Month, Option Series, Option Type, Price, Broker Account, Code(s), Physical Ref etc will be created as one trade with the sum of the lots of the constituent trades as the LOT value on the consolidated record. All the columns imported, except Lots and Trade Time, are applied for the consolidation grouping.
TERM is then used to confirm the full import of the ITAS data into the positions. The imported data can be viewed as Instruction trades in TENQ, prior to TERM/confirm import.
Standard Webservice
This process is similar to import by .csv except it uses Itaswindowsservice and IGI. It achieves the same results i.e. imports F&O records into TERM ready to be confirmed as executed business.
The IGI will be used to setup the import content whereby the XML , example below needs to correspond. The dispatch process will need to send notify messages as below example
<notify><service name='IGI' profile='FUT02IMP' process='DATA' company='us'>
<data>
<fut02>
<fut02_client>100.T</fut02_client><fut02_trdat>28-FEB-12</fut02_trdat><fut02_currlots>98</fut02_currlots><fut02_bs>S</fut02_bs>
<fut02_comod>NY11</fut02_comod><fut02_prompt>20130532</fut02_prompt><fut02_price>13.21</fut02_price><fut02_broker>JPM.T</fut02_broker>
</fut02>
<fut02>
<fut02_client>234.T</fut02_client><fut02_trdat>28-FEB-12</fut02_trdat><fut02_currlots>10</fut02_currlots><fut02_bs>B</fut02_bs>
<fut02_comod>L5</fut02_comod><fut02_prompt>20130532</fut02_prompt><fut02_price>500.5</fut02_price><fut02_broker>MAN.T</fut02_broker>
</fut02>
</data>
</service></notify>
There can be 1 or many data rows in the message. There will be one message returned with OK or validation failure messages.
eMIDAS (UK) Formats
Switches
| Switch | Meaning |
| /FORMAT=EMIDAS | EMIDAS Import |
| /STYLE= | 0 = Open Position, 1 = New Business |
| /IMPORT= | Filename |
Open position format:
1: Heading ‘CCOUNT CODE’. Client account code without the .T.
Col 2: Heading INSTRUMENT CODE’ Must be either a valid TCCM code or match the brokers commodity code from the “This company” tab in TCCM
Col 3: Heading ‘DELIVERY DTE ‘ Prompt
Col 4: Heading ‘TRDE TYPE’ FT = Future , TP = Put Option , TC = Call Option
Col 5: Heading ‘CRETED DTE ‘ Trade Date
Col 6: Heading ‘SOURCE SERIL ‘ External Serial Number
Col 7: Heading ‘STRIKE PRICE’ Option Strike Price
Col 8: Heading ‘PREMIUM RTE’ Option Premium
Col 9: Heading ‘CONTRCT PRICE’ Futures Price
Col 10: Heading ‘VLUE DTE’ Option Declaration Date
Col 11: Heading ‘QUNTITY’ Number of Lots
Col 12: Heading ‘COMMISSION’ Commission
New Business format:
1: Heading ‘TRDE TYPE’ 1 = Future , 3 = Option
2: Heading ‘CRETED DTE ‘ Trade Date
3: Heading ‘SOURCE SERIL ‘ External Serial Number
4: Heading ‘CCOUNT CODE’. Client account code without the .T.
Col 5: Heading INSTRUMENT CODE’ Must be either a valid TCCM code or match the brokers commodity code from the “This company” tab in TCCM
Col 6: Heading ‘QUNTITY’ Number of Lots
Col 7: Heading ‘CONTRCT PRICE’ Futures Price
Col 8: Heading ‘STRIKE PRICE’ Option Strike Price
Col 9: Heading ‘DELIVERY DTE ‘ Prompt
Col 10: Heading ‘COMMISSION UPFRONT’ – Not used
Col 11: Heading ‘PREMIUM RTE’ Option Premium
Col 13: Heading ’UNDERLYING TYPE’ - Not used
Col 12: Heading ‘VLUE DTE’ Option Declaration Date
eMidasUS
Open Position content, where records are secured as representing the broker code entered during Import process.
Col 1: Heading ‘Trade date’ format is YYYMMDD
Col 2: Heading ‘Buy/Sell’ ‘1’ = ‘B’, ‘2’ = ‘S’
Col 3: Heading ‘Quantity’ number of lots
Col 4: Heading ‘Symbol’ TCCM code that needs translation applying either the Lead or Secondary broker code as maintained in TCCM/This Company page.
Col 5: Heading ‘Contract Year Month’ format is YYYYMM
Col 6: Heading ‘Call Put’ , ‘C’ or ‘P’ or space (Future)
Col 7: Heading ‘Strike Price’ , relevant for Options only
Col 8: Heading ‘Price’, Price or Premium
Examples @ 3Q08 are:-
"CSC COCOA-METR" = NYCO
"CSC SUGAR 11" = NY11
"CSC COFFEE C" = NYCF
"NYC COTTON" = NYCT
"CBT BEAN OIL" = SBO
UBIX
The format is the same as eMidasUS with regards the number of columns and the name of the columns. The difference is that the import data is already coded using ITAS conventions and data source.
Open Position content, where records are secured as representing the broker code entered during Import process.
1: Heading ‘Trade date’ format is DD/MM/YY or DDMMMYY
Col 2: Heading ‘Buy/Sell’ ‘B’, ‘S’
Col 3: Heading ‘Quantity’ number of lots
Col 4: Heading ‘Symbol’ TCCM code.
5: Heading ‘Contract Year Month’ format is MMMYY
Col 6: Heading ‘Call Put’ , ‘C’ or ‘P’ or space (Future)
Col 7: Heading ‘Strike Price’ , relevant for Options only
ABN
The format (.csv) is a number of columns, some of which are mandatory for ITAS to complete an import. The file will provide a heading line with the name of the columns. The import data is coded using ITAS conventions and data source.
Open Position content, where records are secured as representing the broker code entered during Import process.
Col : Heading ‘Maturity Date’ format is MMMYY
Col : Heading ‘Trade Date’ format is DD/MM/YY or DDMMMYY
Col : Heading ‘Buy/Sell’ ‘B’, ‘S’
Col : Heading ‘Quantity’ number of lots
Col : Heading ‘Futures Code’ either a TCCM code OR one of the broker interface commodity codes specified on TCCM/This Company page.
Col : Heading ‘Put/Call’ , ‘C’ or ‘P’ or space (Future)
Col : Heading ‘Strike Price’ , relevant for Options only
Col : Heading ‘Trade Price’, Price or Premium
Validation occurs on B/S, C/P/ and Futures Code whereby failure will ignore the row from import and report a message
WhenTech
Open Position content, where records are secured as representing the broker code entered during Import process.
Col 1: Heading ‘Symbol’ TCCM code that needs translation applying the Span/Ticker code as maintained in TCCM, in conjunction with Exchange = ‘OTC’
Col 2: Heading ‘Contract Year Month’ format is ANN i.e. the Reuters Period format
Col 3: Heading ‘StrikePriceRaw’ , relevant for Options only
Col 4: Heading ‘CurrCallPos’ number of Call lots , signed negative if a SELL
Col 5: Heading ‘CurrPutPos’ number of Put lots , signed negative if a SELL
Col 6: Heading ‘CurrFutPos’ number of Futures lots , signed negative if a SELL
Col 7: Heading ‘OTC’ ‘Y’ indicates symbol/commodity code is to be translated to OTC variation of TCCM code
One row of input csv can create 1, 2 or 3 F&O x ITAS records.
There are no prices being saved so TOPEN on this data will only show Lot positions and the BRKIMPORT/Reconcile process will process explicitly for Dept ‘OPT’ trades in ITAS
STANDARD Export, can be either .txt (tab delimited) or .csv format. Can be either New Business or Open Position structure.
The content is determined in S01/View/Data Import-Export/fut02.
The list of available fields for entry needs to be ticked in S01 and this process will confirm content before proceeding.
The first row with the first cell will contain the name fut02. This enables this file to be imported by BRKIMPORT/Standard
The 2nd row will have the technical database fields names e.g. fut02_trinit, fut02_client, in their respective columns.
The data content is extracted in ITAS content i.e. ITAS full account references, codes and formats (date = YYYYMMDD).
Any internal trades selected will be split from their ITAS partner and sent as standard trade with a broker = ‘INTERNAL.T’
When producing an Open Position file, the Broker Account will be output with the same broker code entered as the name of the output file.
The House/client selection of accounts will be exported as a recap position for the target company, so the target/receiving import company will need same accounts setup in their CLI.
BRKEXPORT (LDC), (mode 5 of T59)
Export required for futures and options trades. The exported file will be sent to the exchange. The export will export all open position trades entered within a selected period. The period will usually be the prior day. If a trade is amended after the export file has been sent a reversing trade will be created.
File Format
The file is fixed format text file.
| LD Field Name | ITAS Field Name | Position | Format | Description |
| AILDC | Fut02_client | 1 – 5 | | Client A/c |
| DTCOMC | Tcy20_sfainstrcode_fut, Tcy20_sfainstrcode_opt, | 6 -7 | | Commodity Code |
| DTACCT | Fut02_strategy | 8-10 | Numeric | Strategy Code |
| DTBRKR | Fut02_broker | 11 – 13 | Alpha numeric | Broker |
| DTDATE | Fut02_trdate | 14 – 19 | YYMMDD | Date of trade |
| DTENDT | Fut02_setdate | 20-25 | YYMMDD | Date keyed |
| DTTIME | Fut02_settime New field | 26-31 | HHMMSS | Time keyed |
| DTID | F or O | 32-32 | F/O | F = Future O = Option |
| DTCTRM | Fut02_prompt | 33-34 | MM | Contract Month |
| DTCTRY | Fut02_prompt | 35-36 | YY | Contract Year |
| DTSTRK | Fut02_exprice | 37-41 | Decimal | Strike Price |
| DTTYPE | Fut02_extype | 42-42 | C / P/ F | Option Type |
| DTPRIC | Fut02_price | 43-48 | Decimal | Transaction Price |
| DTQNTY | Fut02_currlots | 49-54 | Numeric | Quantity Traded |
| DTEDAT | Fut02_declndate | 55-60 | YYMMDD | Expiry Date |
| DTCTR# | Fut02_serialno | 61-66 | Numeric | Contract Number |
| DTBORS | Fut02_bs | 67-67 | B / S | Buy or Sell |
| DTNOTE | Fut02_code | 68-69 | 2 character alphanumeric | Trade Code |
BRKEXPORT (UBS), (mode 2 of T59)
The design of this export process is to enable any number of exports to be actioned in any day BUT only transfer the outstanding items. The only CLI accounts that will be processed are those that have been setup with their Produce Client documentation = required (CLI/View/Trading details). There can be any number of informal exports but the completion of the FORMAL process secures a cutoff date (ITAS) and time (database) to ensure that the next request commences at the first available audit record (_futdoc). The target file is created in the directory selected by the user with the user also entering the name. It is the responsibility of the operations staff to transfer the file to it’s physical destination.
The file is a simple text type with every field delimited by a comma; this means that individual field lengths are not restricted.
| UBS name | format | Rules/value | ITAS name |
| Version | numeric | 1 | |
| Account code | char | Clearing broker | ? |
| Serial No | Char | | fut02_serialno |
| Product | Char | | fut02_commod |
| Trade type | Char | Fut=FT, Opt call = TC, Opt Put=TP | fut02_extype |
| Prompt date | YYYYMMDD | Last day of month | fut02_prompt |
| Strike price | 9999999V99 | e.g. 5.5 | fut02_exprice |
| Trade price | 9999999V99 | | fut02_price |
| Contracts | 9999999 | Buy=+, Sell= - | fut02_currlots |
| Exec Broker | char | | ? |
| Source account 1 | | Empty | |
| EFP | | Empty | |
| Cabinet | | Empty | |
| OpenClose | | Empty | |
| Comments | | Empty | |
| Clear broker | | Empty | |
| Trade date | YYYYMMDD | | fut02_trdat |
| Transaction type | | Original export process: backdated new biz = NCP new biz =NEW Cancellation Export: previous trade date = CXP todays delete = CXL | Futdoc_ |
| Market code | | Empty | |
| Market price | | Empty | |
| Customer field 1 | | Empty | |
| Customer field 2 | | Empty | |