Help needed with pro-rata share allocation for sale of stock for clients
First time visiting this forum so I'm hoping someone can assist. I'm building a macro in Excel to handle the following scenario, and while my code is good, the math to arrive at the answer is wrong...which is where (hopefully) some of you come in.
I have situations where multiple client equity orders are sold together as one large aggregated order (the parent order). Each client often has a different percentage of the aggregate order, and sometimes the aggregate order is not completely filled. In any event, I first calculate the % and total shares of the sold order that belong to each client (i.e. the child order). That part is easy.
The most important part of the allocation process is to ensure that all executions (all at different prices) that make up the parent order are distributed to the client child orders in a fashion that results in all child order average prices matching the average price of the parent order. Again, my Excel macro has no problem distributing all of the shares so that each client and price point are allocated the appropriate amount, but the average prices end up being different for almost everyone.
A little background on the current logic in my macro... After the percentages for each child order are determined, shares are allocated pro-rata from each price in the parent order to the child orders. Due to the calculations done at this point (e.g. 200 shares sold @ 25.00, with 22.125% going to child order #1, or 44.25 shares), all share amounts need to be rounded as I can't give someone fractional shares. Originally I was just rounding (in this case to 44 as the calculated value is less than 44.5), but my more recent version forces every amount to be rounded down (this eliminated the instances where certain execution price levels or child orders were overallocated). Now everything is underallocated and I have a pool of shares at various prices to be allocated to fill in the gaps.
I'm sure there is a mathematical way to determine where to allocate these remaining shares, but I don't know how. Can anyone offer any help?
Attached is a sample of my sheet (sans code) so you can get a sense of what needs to happen.
Thanks in advance!