starting point is the font office table
everyday a new file is uploaded into the table and new records are added. Records can either be a new record or a record that is cancelling a previous one
3.1.2 Enter/Cancel/Amend using the State field
New records are added with state = I (Insert).
A previous record is cancelled by providing a new record with state = C (cancelled) and the same trade identifier as the record being cancelled.
A previous record is amended using a cancel/replace approach. First, the previous record is cancelled, and then a new (replacement) record is written with state = I (insert).
Note: Records with a state A are not provided by this stream although A is still a valid value on the end of day extracts.
3.1.3 Enter/Cancel/Amend using the BO_State field
New records are added with state = I (Insert).
A previous record is cancelled by providing a new record with state = C (cancelled) and the same trade identifier as the record being cancelled. Where a record has been provided with BO_State cancelled, no further records with the same trade identifier will be provided.
A previous record is amended using the reversal/amend approach. First, the previous record is reversed with state = R (reversal), and then a new (amended) record is written with state = A (amend).
we only need to work with records that have Back_Office_Report_Type <> "T"
what we need to do is to calculate the p&l of an order. an order is generally 2 records that have the same order_id, one Buy and one Sell. sometimes the same order ide can have multiple Buy or multiple Sell
the p&l is calculated in the following way
(Mark + Commision – Brokerage)* Dealt_To_Settlement_Rate
Where Mark = GrossConsideration of the Sell - GrossConsideration of the Buy
order has a client side and a market side. For the client side an attached record is in the allocation table linked by the Booking_Trade_Id
once the p&l of each order has been calculated (please bear in mind that a cancellation can occur on the next day file if there is a cancel replace) then we need to allocate this p&l to the different representative and send an email (directly from VBA not from outlook) to them for the day and a cumulative amount for the month. The allocation is based on the Bookid.
For exemple for book OUB 50% of pl is for representatie1, 20% for representative2 and 30% for representative3
I have been working on very similar project for citi bank.
I am doing this PnL stiff in sybase database but I know the logic of playing with amendment/cancel corrections etc.
Kindly allow me to work for you for this project
$55 USD in 3 days
0.0 (1 review)
0.0
0.0
2 freelancers are bidding on average $53 USD for this job
Sir,
I am well versed in this kind of jobs and can do your project as per requirement.
I have done lots of these kind of projects and will deliver flawless work
Looking for further discussions in this regard.
with thanks and regards