solver related some financial knowledge may be needed

[Richard Payman]

I am trying to design a back testing spreadsheet to analyse where
should place opening, limit and stop order positions for a sprea
betting market. I have a spreadsheet that has four columns o
information: (i) open (ii) high (iii) low (iv) close (this is minut
data, i.e. each row looks at the open, high, low and close for tha
minute). The spreadsheet is then broken up into two areas a long an
short position. For each position the spreadsheet calculates (i
whether the position is open, (ii) whether the stop or limit has bee
triggered. The spreadsheet then calculates how much has been made o
each position on a daily basis. Excel knows whether a position is ope
or a stop/limit has been hit because for each position it looks a
specific criteria I have chosen, e.g. in cell AA1 I have written th
number 15 i.e. I want the position to open when the market moves 1
points up or down (depending on the position). In total I have 6 cell
which contain this information, e.g. a open, stop and limit for each o
my two positions (long/short). Essentially I want excel to calculat
what the optimum levels (maximum profit) is based on moving thes
positions around. I have tried to use solver but I have too much dat
in my spreadsheet, e.g. 5 min data for every weekday for a year (60,00
cells in total). If anyone thinks they can help, please contact me an
I will provide whatever extra information is needed. I must point ou
that I have no vba knowledge so basic explanations please

 

[Guest]

I am trying to design a back testing spreadsheet to analyse where I
should place opening, limit and stop order positions for a spread
betting market. I have a spreadsheet that has four columns of
information: (i) open (ii) high (iii) low (iv) close (this is minute
data, i.e. each row looks at the open, high, low and close for that
minute). The spreadsheet is then broken up into two areas a long and
short position. For each position the spreadsheet calculates (i)
whether the position is open, (ii) whether the stop or limit has been
triggered. The spreadsheet then calculates how much has been made on
each position on a daily basis. Excel knows whether a position is open
or a stop/limit has been hit because for each position it looks at
specific criteria I have chosen, e.g. in cell AA1 I have written the
number 15 i.e. I want the position to open when the market moves 15
points up or down (depending on the position). In total I have 6 cells
which contain this information, e.g. a open, stop and limit for each of
my two positions (long/short). Essentially I want excel to calculate
what the optimum levels (maximum profit) is based on moving these
positions around. I have tried to use solver but I have too much data
in my spreadsheet, e.g. 5 min data for every weekday for a year (60,000
cells in total). If anyone thinks they can help, please contact me and
I will provide whatever extra information is needed. I must point out
that I have no vba knowledge so basic explanations please.

In my experience with that kind of financial problem (thousands of non-linear
equations involved) you won't get Solver to work very well. Typically I use one
of two other more brute force approaches:

For a simple single input variable problem like you're describing I'll just
create a column of X values to test (Say 1 to 100 in steps of 0.1 or whatever is
relevant to your problem). And then you can create a simple macro to take the
first value from the table, plug it into your control cell, then take your
answer cell and plug it back into the second column of the new table. Then
increment all the way down through the table automatically. Effectively just
exhaustively create a table of XY values for the function you're evaluating.
Then you can easily plot it. This not only finds an optimal solution, but it
also shows you visually that some solutions are too twitchy to be of real use in
trading, but others have more room for error and still be workable.

In a more complex situation, with a dozen inputs for example, I typically resort
to a Monte Carlo solution. Write a macro that keeps randomly changing your
input values within limits and keeps track of which combination produced the
best result. The random values will all be incremental variations from the set
of inputs that's produced the best result thus far. This can sometimes run
hours to reach an optimum solution, but it works.

Good luck...

Bill

 

[Richard Payman]

Thanks for the information. Any chance of providing me with an example
of a montecarlo macro. Many Thanks

 

[Bill Martin]

Thanks for the information. Any chance of providing me with an example
of a montecarlo macro. Many Thanks

I've sent you a personal note to the userid shown on your posting. If you don't
get it, post back to here again or write to me at the address shown on this
post. Mail to me at this address goes through a challenge/response filter
though to get rid of mountainous junk mail so it's more cumbersome to reach me
that way.

Bill

 

[Bill Martin]

Thanks for the information. Any chance of providing me with an example
of a montecarlo macro. Many Thanks

I've tried to send you a personal note to the userid shown on your posting. The
web rejects it as an undeliverable address. You'll have to write to me at my
address shown on this posting, and wade through the challenge/response filter
that keeps mountains of junk mail away from my system.

Bill