# [R] Porting optimisation setup from Excel Solver to R

Brian D Ripley ripley at stats.ox.ac.uk
Fri Dec 10 13:42:46 CET 2004

```On Fri, 10 Dec 2004, Puneet Singh wrote:

> Hi all,
>
> I am currently optimising a small portfolio I have
> created as a part of my research project in Excel. I
> am unable to find the appropriate package to port this
> into R. My problem set up is as follows
>
> Minimise ABS(Sum(Xi-Xi')+10*Sum(XiMi)/Mavg)
>
> Subject to:
> 0 <= Xi <= 0.05
> ABS(Sum(Xi)) = 0.2

But Sum(Xi) >= 0, so the ABS is unneeded.
So I presume the minimization is over (Xi), but you have not said.

> where
> Mi - Market Cap of Stock i
> Xi - Initial weight of Stock i
> Xi' - New weight of Stock i
> Mavg = Average weighted market cap of portfolio.

I suspect you have the meanings of Xi and Xi' reversed here.

> My portfolio has a long as well as a short side,
> therefore the ABS. The minimisation function is
> basically a penalty on the change from initial weight
> and the distance from the Average market cap.

That's quite a long way from the formula you gave us.

> My problem is that I need to optimise a vector X with
> the weights instead of one weight at a time... i.e. I
> need to be able to change all the stocks in the
> portfolio simultaneously. I am able to do this EASILY
> application/formulation in R.

R is not a spreadsheet nor a general-purpose optimizer: Excel will find it
very hard to do most of the things R can do.  So why not use Excel?

It's actually easy to solve this problem theoretically, especially once
you notice that you are minimizing

ABS(0.2 + 10*Sum(XiMi)/Mavg - Sum(Xi'))

and the last term is a constant.  But I suspect you have made several
mistakes in specifying it, and will leave it to you to sort this out.

It is likely that you can set up your intended problem as a linear
program, and although R is not the obvious program to solve an LP, it does
have at least three packages that can do so.

--
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

```