Help needed with pro-rata share allocation for sale of stock for clients

1 Attachment(s)

Hi all,

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!
Best,
Mike

Are u buying or selling here?

Quote:

I have situations where multiple client equity orders are sold

vs

Quote:

I have a pool of shares at various prices to be allocated to fill in the gaps.

Re the spreadsheet, I for one wont open any such attachments so perhaps its best you post a few lines here.

If you are buying then I understand the issue: you have orders to say fill as follows

A: 100
B: 200
C: 150

And you fill this as follows

C: 99 at 101
D: 98 at 103
E: XXX at 104

I put in XXX as you may have to buy a lot larger than whats required to fill the 450 less (99+98) Maybe thats not allowed so you may end up buying less and not filling

The average cost price can be found say 103.95
and you then charge the customers this price

What am I missing?

Carlow52,

Thanks for the response. Understand your concerns about the excel attachment so I'll do my best to explain here. FWIW I have no scripts in the attached file, so if you did try to open and got prompted to enable any macros then something fishy is going on.

On to your questions:

1) We are selling in all cases, though I don't think it would matter if we were buying, as all I'm looking to do is allocate a block of shares at specific prices to a group of clients so that everyone gets the same price and their deserved percentage of the total executed order.

2) Here's my attempt at recreating the sheet:

Code:

Client Name: Black Blue White Totals Order Qty: 18,008 12,540 5,800 36,348 % of Total Order: 49.5433% 34.4998% 15.9569% 100% Shares to be Allocated: 793 552 255 1,600 Quantity Price 300 25.89 148 103 47 298 300 25.895 148 103 47 298 100 26.365 49 34 15 98 200 26.38 99 68 31 198 200 26.39 99 68 31 198 500 26.4 247 172 79 498 ------------------------------------------------------------------------------- Total Sold 1,600 790 548 250 1,588 Average Price 26.2038 $26.2039 $26.2033 $26.2034 $26.2036

Does that make it any clearer? The 'Shares to be Allocated' row totals need to match the 'Total Sold' row for each client (which my macro does), and the 'Quantity' column on the left needs to match the 'Totals' column on the right (which my macro can also do). Problem is that all of the shares are assigned, but in the wrong places, so the Average Prices for each client don't match the Average Price for the lot ($26.2038)

Thanks for the time on this... please let me know if you need more info.

-Mike

I think its a precision/rounding/truncation issue which is common in this space

To me it seems if you leave out the sub allocation of the smaller volumes of shares then it will work

You know Black needs 793 from the 1600 but the 148/148/49 etc compilation is unnecessary as I believe the allocation needs to be done at the 1600 level and not the sub levels of 300 etc.
will look at more later

Quote:

Originally Posted by Carlow52

I think its a precision/rounding/truncation issue which is common in this space

To me it seems if you leave out the sub allocation of the smaller volumes of shares then it will work

You know Black needs 793 from the 1600 but the 148/148/49 etc compilation is unnecessary as I beleive the allocation needs to be done at one price, the 26.2038 and not the sub prices

Unfortunately I don't have that option as the detailed fills need to be reported/kept on file due to SEC regulations. The correct allocation can be done (usually I do it via trial and error on my excel sheet) but there are times when I get hung up and can spend an hour trying to find the right combination. I know there's a systematic solution to this rooted in math, but I don't know what it is. I'm thinking it has something to do with determining the delta between the average price we're trying to hit and the individual price that we're allocating shares to at the moment. Depending on how many shares a client needs (to get from 790 to 793, for instance) and how much their current average price needs to change, this should point to the "right" combination of shares/prices to be allocated to land on the number...

I work with the SEC on other stuff and I doubt if they need the theoretical sub allocations as they dont happen. God knows they collect enough stuff of questionable value:)

Unless Interval math will help you Interval arithmetic - Wikipedia, the free encyclopedia I cant do anymore

Quote:

Originally Posted by Carlow52

I work with the SEC on other stuff and I doubt if they need the theoretical sub allocations as they dont happen. God knows they collect enough stuff of questionable value:)

Unless Interval math will help you Interval arithmetic - Wikipedia, the free encyclopedia I cant do anymore

I agree that it seems a bit excessive, but with all due respect I've worked in the industry for a good amount of time and know what the rules and regs surrounding these order executions say, the multiple interpretations of said rules and regs by various securities lawyers, and the perception and expectations of the clients/companies responsible for reporting said information. If it was as easy as telling the clients "Hey, I don't think you need this..." then I'd have many hours of my life back that was otherwise wasted on trying to figure this stuff out!

Will take a look at the interval math stuff though... that may be a push in the right direction... Thanks!