solver related some financial knowledge may be needed

[Richard Payman]

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.

[Bill Martin -- (Remove NOSPAM from address)]

Richard Payman wrote:
> 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]

Richard Payman wrote:
> 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]

Richard Payman wrote:
> 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