Folio - Embedding examples from 'Getting started'
Description
- runfolio.xlsm: launching a Mosel model from Excel via VBA marcos
Source Files
By clicking on a file name, a preview is opened at the bottom of this page. 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
! 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
|