Hi,
I have a spreadsheet with a large number of trades, where these match off by the total sales matching the total purchases for an individual client I show the P&L in column X (Cells X9 and X14). Where trades have not matched off I show the total open position in column Y.
The problem I’m having is due to the number of variables such as:
· There can be any number of trades to make sales match one or more purchases.
· Two clients can be trading in the same stock. (rows 2:9 , 15:18 and 21:22)
· One client can be trading in a number of stocks. (rows 2,14)
· Purchases or sales may have no other side trades (rows 19:20)
· Purchases are debit entries and sales are credit entries in column Y.
Trades are sorted by account number, Security description, Buy Sell (Z to A) and then advice number.
I have tried sub totalling but too many variables.
A sample of the spreadsheet is attached. Once I have the correct formulas I can add them to my VBA macro to automate as loaded or would it be easier to calculate directly in VBA?
Any help as always is very much appreciated.
My computers details are:
Intel® Core™ i7-5820K Processor, MSI X99A Motherboard, 16GB (4x4GB) Quad Channel DDR4 2133 MHz Memory, Windows 10 Home 64 bit, Microsoft Office Home and Business 2013
Reply With Quote