Results 1 to 7 of 7

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

  1. #1
    Newbie
    Joined
    Apr 2011
    Posts
    4

    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.

    Thanks in advance!
    Best,
    Mike
    Attached Files Attached Files
    Follow Math Help Forum on Facebook and Google+

  2. #2
    Newbie
    Joined
    Apr 2011
    Posts
    21
    Are u buying or selling here?
    I have situations where multiple client equity orders are sold
    vs
    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?
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Apr 2011
    Posts
    4
    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
    Follow Math Help Forum on Facebook and Google+

  4. #4
    Newbie
    Joined
    Apr 2011
    Posts
    21
    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
    Follow Math Help Forum on Facebook and Google+

  5. #5
    Newbie
    Joined
    Apr 2011
    Posts
    4
    Quote Originally Posted by Carlow52 View Post
    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...
    Follow Math Help Forum on Facebook and Google+

  6. #6
    Newbie
    Joined
    Apr 2011
    Posts
    21
    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
    Follow Math Help Forum on Facebook and Google+

  7. #7
    Newbie
    Joined
    Apr 2011
    Posts
    4
    Quote Originally Posted by Carlow52 View Post
    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!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 0
    Last Post: December 5th 2010, 04:54 PM
  2. Rate of return on stock stock index
    Posted in the Business Math Forum
    Replies: 0
    Last Post: July 14th 2010, 10:45 AM
  3. Maximum sale
    Posted in the Calculus Forum
    Replies: 1
    Last Post: June 5th 2009, 06:57 PM
  4. Share Stock
    Posted in the Algebra Forum
    Replies: 0
    Last Post: December 11th 2008, 09:23 PM
  5. sale by percent
    Posted in the Algebra Forum
    Replies: 3
    Last Post: October 21st 2008, 06:58 PM

Search Tags


/mathhelpforum @mathhelpforum