Accounting the Time Line of trades...
I have been looking at strategy tests and taking them trade for trade walking back thru the charts to see when and where the orders were generated.
In accounting for backtest results I want to be able to copy and paste the strategy test results into excel and then run some macro that will look down the timeline for the orders and figure out how long each order was opened.
The first hurdle I see to doing this is the fact that the strategy tester produces the timeline in this format.
2006.04.16 21:39
yyyy.mm.dd.hh:mm
Well that's all fine and good for the strategy tester but that format doesn't correspond to ANYTHING I can find in excel custom or otherwise.
So...
the first hurdle is to convert the time data into an Excel recongizable format...I can find this format in the special formats
m/d/yyyy h:mm
I don't know if that would handle the double diget hours I guess it would...
this format is in the date formats...
4/16/06 21:39
this one would work i believe...
a friend of mine tells me that Excel can subtract two date/times from each other and return the difference but it has to be in a format it can recognize.
ok never mind I figured it out...
To convert a cell like "2006.02.20" into date in Excel use the following function
suppose the cell u want to convert is in B2
=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))
Look at the image attached
Eli
To convert a cell like "2006.02.20" into date in Excel use the following function
suppose the cell u want to convert is in B2
=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))
Look at the image attached
Elithat's about what I did but you did it all in one cell...too cool 
now for the bonus...
once I have these dates I have to determine the starting time/date and ending time/date of each position and then subtract the start from the end so it gives me the duration of the trade. i have one column picking out the endings and one column picking out the startings...but the subtracting the start from the end doesn't always look like it's working. 
To convert a cell like "2006.02.20" into date in Excel use the following function
suppose the cell u want to convert is in B2
=DATE(LEFT(B2,4),MID(B2,6,2),RIGHT(B2,2))
Look at the image attached
Elithat's totally clean! 
- Free trading apps
- Over 8,000 signals for copying
- Economic news for exploring financial markets
You agree to website policy and terms of use
I have been looking at strategy tests and taking them trade for trade walking back thru the charts to see when and where the orders were generated.
In accounting for backtest results I want to be able to copy and paste the strategy test results into excel and then run some macro that will look down the timeline for the orders and figure out how long each order was opened.
The first hurdle I see to doing this is the fact that the strategy tester produces the timeline in this format.
2006.04.16 21:39
yyyy.mm.dd.hh:mm
Well that's all fine and good for the strategy tester but that format doesn't correspond to ANYTHING I can find in excel custom or otherwise.
So...
the first hurdle is to convert the time data into an Excel recongizable format...I can find this format in the special formats
m/d/yyyy h:mm
I don't know if that would handle the double diget hours I guess it would...
this format is in the date formats...
4/16/06 21:39
this one would work i believe...
a friend of mine tells me that Excel can subtract two date/times from each other and return the difference but it has to be in a format it can recognize.
ok never mind I figured it out...