Consulting

Results 1 to 7 of 7

Thread: Summarising trading positions

  1. #1
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location

    Summarising trading positions

    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
    Attached Files Attached Files
    Regards, Peter.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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"),"")
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location
    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.
    Regards, Peter.

  4. #4
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location
    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.
    Regards, Peter.

  5. #5
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location
    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),"")))
    Regards, Peter.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    The order was causing problems when I tried to solve that one. What sort did you apply, maybe we can do a better formula?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Contributor
    Joined
    Nov 2012
    Location
    Billericay, Essex
    Posts
    145
    Location
    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.Test 2.xlsm
    Regards, Peter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •