PDA

View Full Version : [SOLVED] Summarising trading positions



pcarmour
01-05-2016, 03:36 PM
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

Bob Phillips
01-05-2016, 04:07 PM
Is this what you want to get the Profit/Loss figures?


=IF(AND(I2<>I3,SUMIFS($H$2:$H$24,$I$2:$I$24,I2,$F$2:$F$24,F2,$G$2:$G$24,"SELL")-SUMIFS($H$2:$H$24,$I$2:$I$24,I2,$F$2:$F$24,F2,$G$2:$G$24,"Buy")=0),
SUMIFS($W$2:$W$24,$I$2:$I$24,I2,$F$2:$F$24,F2,$G$2:$G$24,"SELL")-SUMIFS($W$2:$W$24,$I$2:$I$24,I2,$F$2:$F$24,F2,$G$2:$G$24,"BUY"),"")

pcarmour
01-06-2016, 12:48 AM
Hi XLD,
Absolutely Brilliant! This is exactly what I was looking for as it gives the right answer when extended to over 100 rows.
Thank You.

pcarmour
01-06-2016, 08:56 AM
Hi XLD,
Your code is working really well thank you again.
If you have the time and are happy to continue with my post, the second part which is 'where trades have not matched off I show the open position in column Y' would be great to get my project completed.

Thank you.

pcarmour
01-08-2016, 10:25 AM
Hi Just to say that I have solved the second part myself with the following rather long winded code, although it did mean sorting the data differently.Thank you again to XLD for your assistance.


=IF(OR(X9<>"",B9<>B10,I9<>I8),IF(I9=I10,IF(ISERROR(IF(OR(AND(B9=B10,I9=I10,H9<>H10,I9<>I11,I10<>I8),AND(B8=B9,I8=I9,H8<>H9,I8<>I10,I9<>I7)),IF(G9="BUY",-W9,W9),"")),"",IF(OR(AND(B9=B10,I9=I10,H9<>H10,I9<>I11,I10<>I8),AND(B8=B9,I8=I9,H8<>H9,I8<>I10,I9<>I7)),IF(G9="BUY",-W9,W9),"")),IF(X9<>"","",IF(G9="BUY",-W9,W9))),IF(ISERROR(IF(OR(AND(B9=B10,I9=I10,H9<>H10,I9<>I11,I10<>I8),AND(B8=B9,I8=I9,H8<>H9,I8<>I10,I9<>I7)),IF(G9="BUY",-W9,W9),"")),"",IF(OR(AND(B9=B10,I9=I10,H9<>H10,I9<>I11,I10<>I8),AND(B8=B9,I8=I9,H8<>H9,I8<>I10,I9<>I7)),IF(G9="BUY",-W9,W9),"")))

Bob Phillips
01-09-2016, 08:10 AM
The order was causing problems when I tried to solve that one. What sort did you apply, maybe we can do a better formula?

pcarmour
01-09-2016, 11:10 AM
Hi XLD,
Thank you for getting back.
I have just sorted by Columns B,I & G (Client Code, Stock Description and B/S) Descending,Descending & Ascending).
All seems to be working on the samples that I have attached, but still testing.15137