QUANTRIX 2018

## Solver Equivalent

**Moderators:** Ben G, Moderators

12 posts
• Page

**1**of**1**### Solver Equivalent

Hi All,

I'm trying to calculate the weights of the minimum variance portfolio with more than 2 stocks. In Excel, it can be done using the solver but I know Quantrix presently does not have a solver yet. Any suggestions or advice on how I go about doing it in Quantrix is much appreciated.

Thanks,

YC

I'm trying to calculate the weights of the minimum variance portfolio with more than 2 stocks. In Excel, it can be done using the solver but I know Quantrix presently does not have a solver yet. Any suggestions or advice on how I go about doing it in Quantrix is much appreciated.

Thanks,

YC

- yc3
- Quantrix Forum Member
**Posts:**4**Joined:**Wed Jul 06, 2011 8:34 am

Hi,

I'm afraid I am not familiar with portfolio theory. Is it possible to describe what you are trying to do in explicit mathematical terms, and perhaps provide a sample of how it would be done in Excel? If I can see a concrete example of the sort of calculation you are hoping to accomplish, I can better advise on whether Quantrix can do it and how.

Regards,

Ben

I'm afraid I am not familiar with portfolio theory. Is it possible to describe what you are trying to do in explicit mathematical terms, and perhaps provide a sample of how it would be done in Excel? If I can see a concrete example of the sort of calculation you are hoping to accomplish, I can better advise on whether Quantrix can do it and how.

Regards,

Ben

- Ben G
- Quantrix Forum Member
**Posts:**110**Joined:**Fri Dec 03, 2010 10:31 am

Hi Ben,

In explicit mathematical terms, I am trying to minimum the function f = c1*(w1^2) + c2*(w2^2) + c3*(w3^2) + c4*w1*w2 + c5*w1*w3 + c6*w2*w3 with constraints w1+w2+w3=0 and c7*w1+c8*w2+c9*w3=c10. c1 … c10 are all known constants and i'm trying to find the unknowns w1,w2 and w3 that minimizes function f.

Much thanks,

YC

In explicit mathematical terms, I am trying to minimum the function f = c1*(w1^2) + c2*(w2^2) + c3*(w3^2) + c4*w1*w2 + c5*w1*w3 + c6*w2*w3 with constraints w1+w2+w3=0 and c7*w1+c8*w2+c9*w3=c10. c1 … c10 are all known constants and i'm trying to find the unknowns w1,w2 and w3 that minimizes function f.

Much thanks,

YC

- yc3
- Quantrix Forum Member
**Posts:**4**Joined:**Wed Jul 06, 2011 8:34 am

For further clarification/simplication, I just want to solve a quadratic optimization problem with an objective function and constraints given by values in a table. The solution should return the optimal values of optimization variables. In Excel, I would use Solver to minimize the objective function to solve for the optimal values.

Perhaps from another perspective, is there a general way of passing data in and out of quantrix dynamically, for eg. in Matlab, there are external interfaces that allows you to interact with external C, C++, Fortran, Java

programs <http://www.mathworks.com/help/techdoc/matlab_external/f44062.html>

Thanks,

YC

Perhaps from another perspective, is there a general way of passing data in and out of quantrix dynamically, for eg. in Matlab, there are external interfaces that allows you to interact with external C, C++, Fortran, Java

programs <http://www.mathworks.com/help/techdoc/matlab_external/f44062.html>

Thanks,

YC

- yc3
- Quantrix Forum Member
**Posts:**4**Joined:**Wed Jul 06, 2011 8:34 am

Googling for portfolio optimization java library I found this:

http://ojalgo.org/blapp.html

Haven't tested it yet, but seems promising.

Maybe it could be used in Quantrix via scripting

http://ojalgo.org/blapp.html

Haven't tested it yet, but seems promising.

Maybe it could be used in Quantrix via scripting

Luca

- lerzegov
- Quantrix Forum Member
**Posts:**101**Joined:**Mon Jul 11, 2005 12:38 pm**Location:**Trento, Italy

### lp_solve

Another options is lp_solve. You can find the instructions for using it with Java (Groovy) here:

http://lpsolve.sourceforge.net/5.5/Java/README.html

But:

While I could call and use lp_solve in Eclipse with Java I couldn't use it from within Quantrix beacause it isn't allowed to call the necessary *.jar files.

So I would like to repeat an old request from my side to the Quantrix developers: Make it possible to use external Java libraries (*.jar files) from within Quantrix without to many restrictions.

This topic has been discussed in this post already:

http://www.quantrix.com/forums/showthread.php?t=710&highlight=lp_solve

...including this reply from Ben:

"I can relate to your situation, but unfortunately our hands are tied on this one. Allowing the execution of arbitrary libraries from the system folder would be a major security liability, so that isn't something we're able to change. Sorry about that."

I personally don't like the term "arbitrary libraries" because lp_solve (and other libraries I have tried to use within Quantrix) are widely known and well developed. I therefore suggest that Quantrix installs a process where a user can request a security key for a particular library which will allow to use that library within the Quantrix scripting environment.

Many thanks for consideration.

Dominik

http://lpsolve.sourceforge.net/5.5/Java/README.html

But:

While I could call and use lp_solve in Eclipse with Java I couldn't use it from within Quantrix beacause it isn't allowed to call the necessary *.jar files.

So I would like to repeat an old request from my side to the Quantrix developers: Make it possible to use external Java libraries (*.jar files) from within Quantrix without to many restrictions.

This topic has been discussed in this post already:

http://www.quantrix.com/forums/showthread.php?t=710&highlight=lp_solve

...including this reply from Ben:

"I can relate to your situation, but unfortunately our hands are tied on this one. Allowing the execution of arbitrary libraries from the system folder would be a major security liability, so that isn't something we're able to change. Sorry about that."

I personally don't like the term "arbitrary libraries" because lp_solve (and other libraries I have tried to use within Quantrix) are widely known and well developed. I therefore suggest that Quantrix installs a process where a user can request a security key for a particular library which will allow to use that library within the Quantrix scripting environment.

Many thanks for consideration.

Dominik

- dom
- Quantrix Forum Member
**Posts:**155**Joined:**Tue Dec 20, 2005 2:41 am**Location:**Zurich, Switzerland

Hi All,

Some comments on this topic.

Quantrix does not currently have a solver like the one in Excel. There are a few options in terms of simulating one.

The first (and likely the easiest) option is to locate a Java library that can solve these optimization problems, such as those that Luca and Dominik have mentioned, and then invoke it using scripting. There is a specific reason why lp_solve is not practicable in this situation, which I have explained in a separate note below. However, it seems likely that there are other effective offerings out there.

An additional option is to use Quantrix scripting to write a scripted function that can find the minimum for you. This is more of a DIY approach and requires some understanding of the math. I have attached a model containing a scripted function that serves as a proof of concept. The function was written specifically from yc3's example and is not very generalized, but is demonstrates the basic premise. Below is a brief explanation of how we solved it. If anyone is interested in putting this sort of technique to use and needs further clarification of how the script works, please reply and I will try to give a more in-depth discussion.

Basically, by algebraic manipulation of the constraint equations (w1 + w2 + w3 = 0 and c7 * w1 + c8 * w2 + c9 * w3 = c10), you can choose one w (we chose w3) and solve for the other two in terms of it. You can then re-write the function as a quadratic in a single variable. The minimum of this function can be found by taking its derivative and solving for f ' = 0. Once you have the minimum value of your chosen w, you can substitute into your previous equations to obtain the minimum values for the others.

Note Regarding lp_solve:

Dominik, I would like to take a moment to clarify precisely why lp_solve is problematic. lp_solve, as near as I can tell, is actually written in C. When I tested it before, what I discovered was that the JAR file that I installed to use with Quantrix seemed to merely be a wrapper around a bunch of native DLL files that lp_solve installs into the system folder (e.g. C:\Windows\system32). When you call the Java code, it calls out to the DLLs to perform the actual number crunching. So to accommodate lp_solve, Quantrix wouldn't merely need to allow the loading of a particular JAR file; it would need some way of authenticating and running native DLL files from the system folder. This could get really dicey because DLLs are much less secure than Java code and most malware takes the form of a DLL that gets planted in system32. So that sort of access needs to be handled very carefully, and thus the case of lp_solve is more subtle than just dealing with JARs. I've only looked briefly at lp_solve and I'm open to being corrected here. But that is my understanding.

Hope this helps.

-Ben

Some comments on this topic.

Quantrix does not currently have a solver like the one in Excel. There are a few options in terms of simulating one.

The first (and likely the easiest) option is to locate a Java library that can solve these optimization problems, such as those that Luca and Dominik have mentioned, and then invoke it using scripting. There is a specific reason why lp_solve is not practicable in this situation, which I have explained in a separate note below. However, it seems likely that there are other effective offerings out there.

An additional option is to use Quantrix scripting to write a scripted function that can find the minimum for you. This is more of a DIY approach and requires some understanding of the math. I have attached a model containing a scripted function that serves as a proof of concept. The function was written specifically from yc3's example and is not very generalized, but is demonstrates the basic premise. Below is a brief explanation of how we solved it. If anyone is interested in putting this sort of technique to use and needs further clarification of how the script works, please reply and I will try to give a more in-depth discussion.

Basically, by algebraic manipulation of the constraint equations (w1 + w2 + w3 = 0 and c7 * w1 + c8 * w2 + c9 * w3 = c10), you can choose one w (we chose w3) and solve for the other two in terms of it. You can then re-write the function as a quadratic in a single variable. The minimum of this function can be found by taking its derivative and solving for f ' = 0. Once you have the minimum value of your chosen w, you can substitute into your previous equations to obtain the minimum values for the others.

Note Regarding lp_solve:

Dominik, I would like to take a moment to clarify precisely why lp_solve is problematic. lp_solve, as near as I can tell, is actually written in C. When I tested it before, what I discovered was that the JAR file that I installed to use with Quantrix seemed to merely be a wrapper around a bunch of native DLL files that lp_solve installs into the system folder (e.g. C:\Windows\system32). When you call the Java code, it calls out to the DLLs to perform the actual number crunching. So to accommodate lp_solve, Quantrix wouldn't merely need to allow the loading of a particular JAR file; it would need some way of authenticating and running native DLL files from the system folder. This could get really dicey because DLLs are much less secure than Java code and most malware takes the form of a DLL that gets planted in system32. So that sort of access needs to be handled very carefully, and thus the case of lp_solve is more subtle than just dealing with JARs. I've only looked briefly at lp_solve and I'm open to being corrected here. But that is my understanding.

Hope this helps.

-Ben

You do not have the required permissions to view the files attached to this post.

- Ben G
- Quantrix Forum Member
**Posts:**110**Joined:**Fri Dec 03, 2010 10:31 am

Ben,

Many thanks for your interesting and helpful post; I appreciate your effort very much.

Regarding the security issues caused by lp_solve (and other libraries):

I fully agree to give the security the first priority and I understand the issues that can be caused when a library calls external dll files. It is better to have a maybe "not so open" system but then we can be sure to be in a save mode. The security issues caused by VBA in Excel should be a warning example. I apologize for my harsh comments in the previous post in this thread.

Seeing that the Quantrix scripting functionality is improving gives me enough confidence that we can now implement customized solver and optimization requirements.

Dominik

Many thanks for your interesting and helpful post; I appreciate your effort very much.

Regarding the security issues caused by lp_solve (and other libraries):

I fully agree to give the security the first priority and I understand the issues that can be caused when a library calls external dll files. It is better to have a maybe "not so open" system but then we can be sure to be in a save mode. The security issues caused by VBA in Excel should be a warning example. I apologize for my harsh comments in the previous post in this thread.

Seeing that the Quantrix scripting functionality is improving gives me enough confidence that we can now implement customized solver and optimization requirements.

Dominik

- dom
- Quantrix Forum Member
**Posts:**155**Joined:**Tue Dec 20, 2005 2:41 am**Location:**Zurich, Switzerland

Hi Dominik,

No worries - I didn't find your comment harsh at all. It's always useful to question why something is done the way it is, as there isn't always a sufficient reason and perhaps it can be done better. Your feedback has been very valuable to the development of scripting and will surely continue to be. I just wanted to give your post the thorough response that I felt it deserved.

-Ben

No worries - I didn't find your comment harsh at all. It's always useful to question why something is done the way it is, as there isn't always a sufficient reason and perhaps it can be done better. Your feedback has been very valuable to the development of scripting and will surely continue to be. I just wanted to give your post the thorough response that I felt it deserved.

-Ben

- Ben G
- Quantrix Forum Member
**Posts:**110**Joined:**Fri Dec 03, 2010 10:31 am

### Preliminary test with ojAlgo matrix

Hi all. I have done some very simple tests with ojAlgo (http://ojalgo.org), a pure Java library implementing portfolio optimization. All the stuff needed is in a single jar that is enclosed here together with an example model. It must be copied to the /ScriptingLibs folder of the Quantrix installation. ojAlgo is very rich in functionalities, the API is rather complex and somewhat redundant, I extracted the classes for doing a very basic constrained optimization case, setting a target value for portfolio variance and solving for the weights. I use scripting functions for accessing ojAlgo. In order to save on computation time, I get all the portfolio results in a "blob" string stored in a cell, than parse such string in order to extract portfolio expected return, variance and asset weights.

If someone is interested, I may add something to the very limited notes in the model.

If someone is interested, I may add something to the very limited notes in the model.

You do not have the required permissions to view the files attached to this post.

Luca

- lerzegov
- Quantrix Forum Member
**Posts:**101**Joined:**Mon Jul 11, 2005 12:38 pm**Location:**Trento, Italy

### Re: Solver Equivalent

I'm quite late to the party! Just wanted to say that with the release of 18.1.0; Quantrix now has its own built in Solver functionality covering Linear, Smooth Non-Linear and Genetic (Evolutionary) algorithms.

Kind Regards,

James

Kind Regards,

James

James Kipling

Product Manager, Quantrix

Official Quantrix support is provided through the IDBS Help Desk via support@quantrix.com

Product Manager, Quantrix

Official Quantrix support is provided through the IDBS Help Desk via support@quantrix.com

- James K
- Quantrix Forum Member
**Posts:**29**Joined:**Thu Dec 12, 2013 8:20 am

12 posts
• Page

**1**of**1**### Who is online

Users browsing this forum: No registered users and 1 guest