General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Solver for Excel

Discussion in 'Automated Trading' started by EqtTrdr, Jun 16, 2006.

1. ### 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

2. ### 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.

3. ### EqtTrdr

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.

4. ### kalzayani

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)

5. ### 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.

6. ### EqtTrdr

appreciate all the help...

thanks again!

7. ### jimrockford

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.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: