I have a table of stock trades, purchases and sales, ordered by date. Each row is a trade. If the transaction is a purchase the shares are positive values, if a sale the shares are negative. Col A below has a sequence number for purchases that have shares that have not yet been sold. Col E has the number of shares which have not yet been sold. When shares are sold they are assigned to a specific purchase and column E is reduced by the number of shares sold. The oldest row with shares remaining has a 1 in col A, the second oldest has a 2. Col A and Col E only have values for purchase transactions, not for sales. (earlier description here incorrectly stated that Col A has no values for purchases) There are hundreds of entries for the past 5 years. I wish to produce a simple table that lets me know the price paid and shares remaining starting with the shares with the lowest basis (Price Pd).
So far I have a secondary table where i collect The date, the Price and the Remaining Shares (Col E) of rows with a value in Col A. I then have added a column which gets the price of the lowest basis, next lowest, etc. based on the Small Function using col A as the k value. From the 2nd table I can determine the price of shares with lowest basis. What i cannot figure out is how to also get the Date the shares with that basis were purchased and how many shares remain. A further complication is that some basis prices may be duplicates. I am open to restructuring either table if there is a better structure. Open to any suggestions on how to proceed. I have seen formulas that use the aggragate, index and match functions as well as various array functions but understanding how to modify examples to meet my requirements is so far beyond my capabilities. If a solution is rather complex I would appreciate an attempt to describe the elements of the formulas used, especially where compound elements are applied. Thanks in advance for any assistance offered.
Apologies for the confusion in displaying the tables. Please advise how to display properly. What i typed at first was not at all what showed in the post. Spaces added to space the tables were ignored. I have tried to model other tables presented here but realize it is still less than sufficient.
Stock Trades Table
A----Date--------Shares---Price---E
--1----2/22/23-------10 ------$5-----5
-------2/24/23------, -3 ------$6
-------2/26/23------, -2 ------$7
-------3/2/23----------5 ------$6-----0
-------3/7/23-------, -5 ------$7
--2----3/8/23----------8 ------$3-----4
--3----3/9/23----------7 ------$5-----2
-------3/11/23------, -9 ------$8
Secondary Table - Collecting Rows with values in Col A above and reporting lowest basis. Col F = price of remaining shares with the lowest basis, Col G = the date of purchase of those shares for which i am seeking a formula, as well as Col H which would be the number of shares remaining.
--A--Date---Shares---Price---E-------F------G------H
--1--2/22/23---10------$5-----5------$3---3/8/23----4
--2--3/8/23-----8------$3-----4------$5---2/22/23---5
--3--3/9/23-----7------$5-----2------$5----3/9/23---2
I've tried using the Cell function applied to both the Small formula and Vlookup formula to at least get the cell location of the result but it seems they are not supported in that way.
I apologize for what is likely a poor description of my problem. Not sure where it is the least clear or how I might make it more clear.