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!
Bookmarks