(!******************************************************
   Mosel Example Problems
   ======================

   file folioexcel.mos
   ```````````````````
   Modeling a small LP problem 
   to perform portfolio optimization.
   -- Parameters, data input from Excel, 
      result output to Excel --
   
  (c) 2008 Fair Isaac Corporation
      author: S.Heipcke, Feb. 2007
*******************************************************!)

model "Portfolio optimization with LP (Excel)"
 uses "mmxprs", "mmsheet"

 parameters
  DATAFILE = "folio.xls"             ! Spreadsheet with problem data
  DBDATA = "foliodata"               ! Spreadsheet range with problem data
  DBSOL = "folioresult"              ! Range for solution data
  
  MAXRISK = 1/3                      ! Max. investment into high-risk values
  MAXVAL = 0.3                       ! Max. investment per share
  MINAM = 0.5                        ! Min. investment into N.-American values
 end-parameters

 declarations
  SHARES: set of string              ! Set of shares
  RET: array(SHARES) of real         ! Estimated return in investment
  RISK: array(SHARES) of boolean     ! List of high-risk values among shares
  NA: array(SHARES) of boolean       ! List of shares issued in N.-America
 end-declarations

! Data input from spreadsheet
 initializations from "mmsheet.excel:" + DATAFILE
   [RET,RISK,NA] as DBDATA
 end-initializations

 declarations
  frac: array(SHARES) of mpvar       ! Fraction of capital used per share
 end-declarations

! Objective: total return
 Return:= sum(s in SHARES) RET(s)*frac(s) 

! Limit the percentage of high-risk values
 sum(s in SHARES | RISK(s)) frac(s) <= MAXRISK

! Minimum amount of North-American values
 sum(s in SHARES | NA(s)) frac(s) >= MINAM

! Spend all the capital
 sum(s in SHARES) frac(s) = 1
 
! Upper bounds on the investment per share
 forall(s in SHARES) frac(s) <= MAXVAL

! Solve the problem
 maximize(Return)

! Solution printing
 writeln("Total return: ", getobjval)
 forall(s in SHARES) 
  writeln(strfmt(s,-12), ": \t", strfmt(getsol(frac(s))*100,5,2), "%")

! Solution output to spreadsheet
 declarations
  Solfrac: array(SHARES) of real      ! Solution values
 end-declarations

 forall(s in SHARES) Solfrac(s):= getsol(frac(s))*100
 
 initializations to "mmsheet.excel:" + DATAFILE
   Solfrac as "grow;"+DBSOL
 end-initializations

end-model