I get trade report from one of my broker as below in text file. I am trying to parse it to do some analysis. Problem is each record has multiple rows, including one aggregate row (marked with * for BUY or SELL and below that).
TRADE SETTL AT BUY SELL CONTRACT DESCRIPTION EX TRADE PRICE CC DEBIT(DR)/CREDIT
------- ------- -- -------------- -------------- ------------------------------ -- ----------- -- --------------------
11/26/2 F1 1 JAN 13 SOYBEAN MEAL 01 424.70 US
ELECTRONIC TRADE
F1 1* COMMISSION US 1.20DR
F1 EXCHANGE & CLEARING FEE US .81DR
F1 NFA FEE US .02DR
F1 TOTAL COMMISSION & FEES US 2.03DR
11/28/2 F1 1 DEC 12 SWISS FRANC 16 107.490 US
ELECTRONIC TRADE
F1 1* COMMISSION US 1.20DR
F1 EXCHANGE & CLEARING FEE US .54DR
F1 NFA FEE US .02DR
F1 TOTAL COMMISSION & FEES US 1.76DR
11/29/2 F1 2 MAR 13 NEW COCOA 06 24.61 US
ELECTRONIC TRADE
F1 2* COMMISSION US 2.40DR
F1 EXCHANGE & CLEARING FEE US 4.00DR
F1 NFA FEE US .04DR
F1 TOTAL COMMISSION & FEES US 6.44DR
12/03/2 F1 1 DEC 12 IMM EURO FX 16 1.30000 US
ELECTRONIC TRADE
F1 1* COMMISSION US 1.20DR
F1 EXCHANGE & CLEARING FEE US .54DR
F1 NFA FEE US .02DR
F1 TOTAL COMMISSION & FEES US 1.76DR
12/07/2 F1 3 DEC 12 US $ INDEX 13 80.245 US
ELECTRONIC TRADE
12/07/2 F1 3 DEC 12 US $ INDEX 13 80.250 US
ELECTRONIC TRADE
F1 3* 3* COMMISSION US 7.20DR
F1 EXCHANGE & CLEARING FEE US 8.10DR
F1 NFA FEE US .12DR
F1 TOTAL COMMISSION & FEES US 15.42DR
At the moment I am only interested in aggregated info i.e. CONTRACT DESCRIPTION
, BUY
and SELL
quantities with * in it and fields below i.e COMMISSION
, EXCHANGE AND CLEARING FEES
, NFA FEE
and TOTAL COMMISSION AND FEES
values as specified in last column DEBIT(DR)/CREDIT
?
Any pointers how can I go about doing this?
I tried using read.fwf
but it doesn't work for me because multiline format is not same for each record.
Ultimately, if nothing works, I will have to write line by line parser, which I am trying to avoid at the moment to see if I it can be done in more elegant manner.