Asset Allocation Problem

I have an investment portfolio that need to be rebalanced every time there is a subscription/redemption. Goal is to distribute investment amongst stocks to get as close as possible to the weightings these stocks have in the Index, without violating any of the following 3 rules:

1. Holding in any one stock can not exceed Index Weight or 10% which ever is higher? New holdings in any stock can not exceed individual stock's "Trading Limit"
2. Each trade has to be for 10,000

Seemingly it is a simple problem, but I have gotten myself into a series of recursive loops and cant seem to work my self out of it. Can you please shine some light on the best way to solve the problem.

[john]

Before I have a look at this solver problem, have you tried using the Excel solver and looking here:
http://www.solver.com/suppstdsolver.htm
My first thought is that the solver might be able to do this, but its going to struggle on speed and what happens if it doesn't exactly match the target. Solver seems to want to match a target rather than get quite close to it.

If this is important for you I would consider something like
http://www.lindo.com/cgi/frameset.cg....html;wbf.html
(I used Lindo in Business School 15 years back and it was good then) which is an industrial-strength solver. Its $500.

I keep looking at this but I'm struggling a bit with lack of knowledge of your situation.

Hi John,
I have tried the solver and just now gave Lindo a shot, the problem is two fold, #1 the standard solvers do not allow for the number of constraints that I have, and #2 They solve for "a" result. In my case I have X number of shares and a Finite amount has to be distributed over all of them.
I believe that the problem can be solved by a bit of programming, but in my old age I am getting rusty on the code, if I was to explain the code it will be something like this:

Target Answer:
Assign a market weighted $amount ($S$1) to each stock in the list , subject to constraints.

Constraints:
*Holding in any one stock can not exceed "NCB Index" Weight or 10% which ever is higher?
* New holdings in any stock can not exceed individual stock's "Trading Limit"
Solution:
*Multiply S1 to NCB Index ($H7...H37) and assign $ amount to each (first part of first constraint met)
*Make sure each of the $ amount when divided by share price ($I7) does not exceed "trading limit" (U7)
*If any stock exceeds the Trading limit, reduce the amount for that share until 2nd constraint is satisfied, keep all excess cash in Temp
*Take the cash from Temp and distribute it over the stocks that do not violate constraint #1
*Is it possible to write a macro to accomplish this? For now I will ignore the 10K rule to make the process easier.

[john]

Well I had a go at something.

I ignored the constraint of max holding because it didn't seem to work with my figures.

But I set up a solve with the other constraints and it came up with a (rather obvious) answer.

You need Excel's solver addin installed.

Is this the right kind of approach?

John

Thanks a million for putting me on the right path.

I think I have found 99% of the solution!! I was able to introduce a check for Maximum Holding limit. I did it
by converting the Max hold to a % of Weight of the portfolio, once done, I
compare the Index Weight to Max Holding Weight, and take the LOWER of the
two.

This also means that for certain Investment, we will not be able to invest
100% of the money, hence a place holder for Cash*. Plus I also added a
condition that the weight can not be -ve.

I have also changed the goal so that the solver tries to minimize the cash
balance.

Now only if I can add the cash trade limit, but that has to be done with
existing holdings etc...

In the meanwhile Is there a way to automate the Solver, i.e let it
dynamically apply the set of rules across a range of cells? and move the
cells around? I noticed that the solver does not like to work with Relative
Names etc.. Is there a way to have multiple Solver cases setup in the same
sheet? and can I export the models?

[john]

You can automate the solver from VBA, can't remember how, you can always go record macro and set a minin solver from scratch.

I am not surprised if solver doesn't like relative references. It seemed to like the names I gave it.
I think the only way to have multiple solvers would be via VBA. I have an idea to put solver in a function like I did with Goalseek(), so that it calcs when you calc the worksheet, but its just a different way of automating what you would otherwise do in VBA. I think there's a limit on constraints in each solver, by the way, watch out, it may be 100 variables and/or constraints.

If you save the workbook, the solve will save with it, and of course any VBA.

I don't think you can just save the solve bit, although you could save just the VBA as an addin.

I think the biggest failing and problem might be the lack of support for relative references.

Once again thanks a million, I was spinning my wheels and going in all
different directions. In fact I was even able to come up with a function
based solution, but that involved use of multiple circular references
(actually that was the thing that made it work).

Only problem with that solution was that it was worse than the problem and I
could not read it my self!! Needless to say the solver based solution is a
lot more diagnosable, if a bit restrained. I will work with VBA to overcome
some of the shortcomings.