You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Solver for Excel
- Thread starter EqtTrdr
- Start date
Quote from Batman28:
why would u need to use the solver to do this? how are u modelling it?
u can also use Matlab to solve which is much faster than excels solver.
thanks.. will look into it..
My first time trying to optimize for a data set. I am an extreme novice and I figured Excel's Solver would be sufficient and at no cost.
to use solver...
1) Formulat
a) Decision Variable (Something u are in control of)
b) Make an objective function the depends on those Decsion Variable
c) Constariants ...Limits that those variables have to have Minimum Maximum..=...etc
2) Run the solver to solve for those Decision Variables
Example:
Say Ur Decison Variables are 3 of them
Call them Appl msft and amgn
the decision Variables in this case is the amount of ur assets Into those Stocks in Percent
appl= % of totoal into appl...etc
total returns or average= Return of aapl*(AAPL)+Ret of MSFT*(MSFT)...etc
(...Decision Variables)
that thing is ur objective funtion...I think u want to maximize
now for the contraints:
1) Total of assets=100%=1
=AAPL+MSFT+AMGN=1
say u want less then 40% in any
then..
AAPL<.4
MSFT <.4
amgn<.4
say also U want AAPL+MSFT < .6
put that there...etc
off course
D Variables in this case have to >0
...etc
Hope that helps
Maximum Variables EXCEL can do is 200 I think
I developed ONES that 500,000 Variables
USing "What's Best" Software
Good Luck with the area...
Called Operations Research... (Not Easy Stuff)
1) Formulat
a) Decision Variable (Something u are in control of)
b) Make an objective function the depends on those Decsion Variable
c) Constariants ...Limits that those variables have to have Minimum Maximum..=...etc
2) Run the solver to solve for those Decision Variables
Example:
Say Ur Decison Variables are 3 of them
Call them Appl msft and amgn
the decision Variables in this case is the amount of ur assets Into those Stocks in Percent
appl= % of totoal into appl...etc
total returns or average= Return of aapl*(AAPL)+Ret of MSFT*(MSFT)...etc
(...Decision Variables)
that thing is ur objective funtion...I think u want to maximize
now for the contraints:
1) Total of assets=100%=1
=AAPL+MSFT+AMGN=1
say u want less then 40% in any
then..
AAPL<.4
MSFT <.4
amgn<.4
say also U want AAPL+MSFT < .6
put that there...etc
off course
D Variables in this case have to >0
...etc
Hope that helps
Maximum Variables EXCEL can do is 200 I think
I developed ONES that 500,000 Variables
USing "What's Best" Software
Good Luck with the area...
Called Operations Research... (Not Easy Stuff)
Quote from EqtTrdr:
I can't seem to figure out how to use this program...
Exactly what data do I need to input?
I have 100 different trades with Max DD and Max gain of each trade.
I am trying to optimize for stop and target?
Any suggestions?
thanks
You have bigger problems than trying to use the Solver. Your data are insufficient for purposes of your desired optimization. You only have the max DD and max gain of each trade. This is not sufficient to optimize for stop and target. You need the entire history of price action as a function of time, in order to optimize for stop and target. You are also more likely than not to be disappointed, if you expect the optimal stop and target, measured during a particular time period, to remain the same over future time periods.
Quote from jimrockford:
You have bigger problems than trying to use the Solver. Your data are insufficient for purposes of your desired optimization. You only have the max DD and max gain of each trade. This is not sufficient to optimize for stop and target. You need the entire history of price action as a function of time, in order to optimize for stop and target. You are also more likely than not to be disappointed, if you expect the optimal stop and target, measured during a particular time period, to remain the same over future time periods.
I have received a PM asking me where more background info can be obtained regarding my posting into this thread.
My answer is nowhere. This kind of information is not available in published works. Nor is it taught at university. You need to figure it out for yourself.
You can, however, learn some important things about backtesting, by reading through the postings of EliteTrader member acrary. You can also google, and otherwise research the term "non-stationarity", both with and without the hyphen, in order to learn more about the phenomenon I mentioned, which is that most optimizations performed upon one time period of market data cannot safely be extrapolated to other future time periods.
P.S. Please publicly post any questions or responses, rather than sending them via PM, unless you have a specific reason for using PM.