Portfolio Optimization in Excel using Solver Function

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.

Portfolio Optimization in Excel using Solver Function

Postby JonoJono1 » Thu Jul 11, 2013 9:14 pm

I have searched the board for and cannot find how to run a portfolio optimizer using Simba's backtester. I found some stuff on the web that while precise, still confused me.

I would like to optimize a portfolio. I have added in the Excel function SOLVER, which I understand is the right function to use; I am just having trouble with putting in the correct inputs/constraints/target.

Can someone, anyone, walk us through how to use SOLVER (or any other similar approach) to optimize a portfolio using the backtester?
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby 1210sda » Thu Jul 11, 2013 10:01 pm

jonojono1,
thanks for asking this. I too would like to learn how solver works for MVO.

1210
User avatar
1210sda
 
Posts: 777
Joined: Wed Feb 28, 2007 9:31 am

Re: Portfolio Optimization in Excel using Solver Function

Postby serbeer » Thu Jul 11, 2013 10:25 pm

Funny, but that very same issue, or should I say desire to discuss results of such optimization, prompted me to post for the first time on this board, years ago:
viewtopic.php?t=10313

As you can see from the above thread, people question the value of such optimization, and after spending years on this board I do too.
As in "Past performance..." and you should know the rest by now.

Hopefully that thread is of some help despite not addressing your request directly.
User avatar
serbeer
 
Posts: 877
Joined: Fri Dec 28, 2007 3:09 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby JonoJono1 » Thu Jul 11, 2013 11:17 pm

1210SDA: Thanks for sharing that. That means at least 2 of us would benefit by the answer. I am sure there are more among us that would also benefit.

Serbeer:

I appreciate you linking me to that thread. While I do not claim to fully understand the limitations of backtesting, I am aware that there is strong academic and empirical evidence that wisely caution against it. But it would still help me to know how to construct optimizations using SOLVER because I want to take those optimizations, run them against large swaths of data, find relationships across that data with other portfolios and then make some assumptions as starting points for a new hypothesis.

I read the link you posted, and from it I gather that you may be someone who knows how to walk us through how to optimize using SOLVER in Simba's backtester. If this is true, would you walk us through it?
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby serbeer » Fri Jul 12, 2013 12:45 pm

JonoJono1 wrote:I read the link you posted, and from it I gather that you may be someone who knows how to walk us through how to optimize using SOLVER in Simba's backtester. If this is true, would you walk us through it?

No, not true. I used 3rd party software (as stated my original post), not Solver. Sorry, but cannot help with Solver, never used it.
User avatar
serbeer
 
Posts: 877
Joined: Fri Dec 28, 2007 3:09 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby magician » Sat Jul 13, 2013 12:54 am

I used to teach an online course in asset allocation at UC Irvine; the objective was to teach the students how to create efficient portfolios in Excel using Solver.

Shoot me a PM if you like.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1559
Joined: Mon May 02, 2011 2:08 am
Location: Yorba Linda, CA

Re: Portfolio Optimization in Excel using Solver Function

Postby JonoJono1 » Sat Jul 13, 2013 3:52 pm

Magician: Wow! Thanks for the invite. PM sent.
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby magician » Mon Jul 15, 2013 4:20 pm

1210sda wrote:jonojono1,
thanks for asking this. I too would like to learn how solver works for MVO.

1210

I sent you a PM.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1559
Joined: Mon May 02, 2011 2:08 am
Location: Yorba Linda, CA

Re: Portfolio Optimization in Excel using Solver Function

Postby magician » Mon Jul 15, 2013 4:20 pm

JonoJono1 wrote:Magician: Wow! Thanks for the invite. PM sent.

Apparently your PM is disabled; I sent you an e-mail.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1559
Joined: Mon May 02, 2011 2:08 am
Location: Yorba Linda, CA

Re: Portfolio Optimization in Excel using Solver Function

Postby JonoJono1 » Mon Jul 15, 2013 9:57 pm

Magician,

What you sent to me by email was great! Very kind of you to offer it up.

I am now a Solver practitioner.

My PM is disabled (I think) because I am still a noob. When LadyGeek determines I am worthy, she will upgrade me, I hope. :)
JonoJono1
 
Posts: 25
Joined: Sun Jul 07, 2013 11:31 pm

Re: Portfolio Optimization in Excel using Solver Function

Postby magician » Tue Jul 16, 2013 1:47 am

JonoJono1 wrote:Magician,

What you sent to me by email was great! Very kind of you to offer it up.

My pleasure.

JonoJono1 wrote:I am now a Solver practitioner.

With therapy, you can return to normal. But it takes years.
Simplify the complicated side; don't complify the simplicated side.
User avatar
magician
 
Posts: 1559
Joined: Mon May 02, 2011 2:08 am
Location: Yorba Linda, CA

Re: Portfolio Optimization in Excel using Solver Function

Postby 1210sda » Wed Jul 17, 2013 4:07 pm

Magician,

Thanks for the Excel Solver link. It is a really neat program. Prior to this I was clueless as to how to use Solver.

I would recommend that any Boglehead's who want to optimize their portfolio's using Solver contact you with a PM.

Thanks again.

1210
User avatar
1210sda
 
Posts: 777
Joined: Wed Feb 28, 2007 9:31 am


Return to Investing - Theory, News & General

Who is online

Users browsing this forum: ClevrChico, crg11, Majestic-12 [Bot], obgyn65, Peculiar_Investor, willift, zaboomafoozarg and 43 guests