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

• Apr 13th 2011, 05:55 AM
PSUlion01
Help needed with pro-rata share allocation for sale of stock for clients
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.

Best,
Mike
• Apr 13th 2011, 08:00 AM
Carlow52
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?
• Apr 13th 2011, 08:41 AM
PSUlion01
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
• Apr 13th 2011, 09:41 AM
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 believe the allocation needs to be done at the 1600 level and not the sub levels of 300 etc.
will look at more later
• Apr 13th 2011, 09:49 AM
PSUlion01
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...
• Apr 13th 2011, 10:14 AM
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
• Apr 13th 2011, 10:40 AM
PSUlion01
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!