R
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
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