+ Reply to Thread
Results 1 to 1 of 1

Position Lot tracker - find quantities in excess and relieve

  1. #1
    Forum Contributor
    Join Date
    11-01-2012
    Location
    NJ
    MS-Off Ver
    Excel 365
    Posts
    109

    Position Lot tracker - find quantities in excess and relieve

    Hi,

    I am trying to build a tax lot tracker for transaction report. I have gotten to a point where I am not sure how to get the code to do what I want. Attached is a file where I have starting data and where what the ending data should look like.

    The concept is for each "Buy" trade we have a lot number. When "Sell" trades occur we need to assign the lot number to the appropiate Buy trades. "Buy" trades plus "sell" trades can't go past zero. If a sell trade is greater than the remaining Buy trade shares, the excess will relieve the next Buy trade in the list. When relieving Buy trades with sell trades they should go in numberical order - buy - tax lot 1, buy tax lot 2, etc.

    I started to write some criteria down"

    Criteria
    For each Cell in Column "U" where
    Column "S" is "SLL" AND Column "T" is "order" Then
    sumif the cells ABOVE in Column M - "Par Value" with criteria - Column "D" - "Portfolio ID" & Column "I" - "Security Name" and is same as row currently checking
    if most current "Buy" that has not been fully relieved lot plus "Par Value" of current row is greater than zero then Column "U" should be Buy Lot number
    if most current "Buy" that has not been fully relieved lot plus "Par Value" of current row is less than zero then Column "U" should either be split into two row so that
    current buy lot is relived and remainder is put towards next "Buy" Lot
    ***When rows are split the Column "M" - "Par Value" and Column "N" - "Funded Amount" need to be recalced.
    ***"Par Value will be the difference for relieving previous lot so if you add the "Buy" and all "SLL" the net amount will be zero
    ***"Funded Amount" will be "Par Value" * "Price" /100

    Any help would certainly be appreciated on something like this

    Thank you!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Race Position Tracker - Calc Ahead and Behind Relative to Own Position
    By JLI127 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2021, 09:39 PM
  2. Find Text in Range of Cells and Sum All Quantities for Matched Cell
    By NardoBro in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2017, 04:35 PM
  3. [SOLVED] Looking for help on a Loop to find and update stock sheet quantities
    By kuduck in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-15-2015, 05:21 PM
  4. Help build a Leave tracker and Effort Time Estimation Tracker
    By cherias in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-29-2015, 02:10 PM
  5. System idle time tracker / Break Tracker
    By reetika05 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-10-2014, 07:26 AM
  6. Replies: 1
    Last Post: 07-27-2014, 10:57 AM
  7. Excel 2007 : Global number tracker based on position.
    By theelous3 in forum Excel General
    Replies: 10
    Last Post: 03-23-2012, 06:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1