 FICO Xpress Optimization Examples Repository
 FICO Optimization Community FICO Xpress Optimization Home   Folio - Embedding examples from 'Getting started'

Description
• runfolio.xlsm: launching a Mosel model from Excel via VBA marcos

Source Files

Data Files

folioexcel.mos

```(!******************************************************
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

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), "%")

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

```   