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

   file folioodbc.mos
   ``````````````````

   Modeling a small LP problem 
   to perform portfolio optimization.
   -- Parameters, data input through ODBC, 
      result output through ODBC --
   
   IMPORTANT:
   If this example is run more than once,
   you need to delete the solution data from the
   previous run in the database.

   The mysql database 'folio' is not provided.
   You can create the database contents by
   executing model 'genfoliodb.mos'.

  (c) 2008 Fair Isaac Corporation
      author: S.Heipcke, Mar. 2006, rev. Aug. 2023
*******************************************************!)

model "Portfolio optimization with LP (ODBC)"
 uses "mmxprs", "mmodbc"

 parameters
!  DATAFILE = "folio.mdb"             ! Access database with problem data
!  DATAFILE = "DSN=mysql;DB=folio"    ! MySQL database with problem data
  DATAFILE = "folio.sqlite"          ! SQLite database with problem data
  
  DBDATA = "folio"                   ! Database table with problem data
  DBSOL = "foliosol"                 ! Table 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 database
 initializations from "mmodbc.odbc:" + 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 database
 declarations
  Solfrac: array(SHARES) of real      ! Solution values
 end-declarations

 forall(s in SHARES) Solfrac(s):= getsol(frac(s))*100

(! Cleaning up previous results: uncomment if the example is run more than once
 SQLconnect(DATAFILE)
 if not getparam("SQLsuccess"): setioerr("Database connection failed")
 SQLexecute("delete from " + DBSOL) 
 SQLdisconnect
!) 
 
! Data output to database
 initializations to "mmodbc.odbc:" + DATAFILE
   Solfrac as DBSOL
 end-initializations

end-model