Sunday, May 24, 2009

Using TradeStation Data in Excel



Above is the TradeStation Window that I like to down load data into Excel. This will give you Date, Time, Open, High, Low, Close, Volume, Open Interest, Today's Close - Yesterday's Close Difference with the Standard Deviations, High - Open Difference and Open - Low Difference.



You can get the above window by clicking in the Tool Bar "View" and then "Data Window" and then clicking the "Show All" icon. Then save the window as a text file on your hard drive.





In Excel, you can down load the text file into the spreadsheet by clicking in the Tool Bar, "Data", "Get External Data" and "Import Text File". You then have to go through 3 Steps (see the 3 windows above) to down load the data.



Above is the result spreadsheet.
The TCYC column is the data "Today's Close - Yesterday's Close" from the TradeStation Window. Friday we had a situation where this difference kept decreasing for three days straight with the fourth day (Friday) being an increase in this difference but still below zero. So to find similar situations in the last three years and determine what the next day statistically looks like, I wrote the conditional statement that is shown in the above window in Column R.
Basically, the Conditional Statement reads, If(Condition is True, Do This, Else Leave Cell Blank). So if the TCYC Column decreases in value for three days, and the value on the fourth day increases but is still less than zero, type in the TCYC value for day Five else leave blank.




I'm still using a version of Excel that is older than the 2007 version. The 2007 version ignores blank cells, but older versions do not. So I have to accumulate the results without the blank cells before I can calculate the Average, Median and so on.
The results above show that the above situation has occurred 9 times in the past 3 years. 67% of those times, the next day closed even or higher than the previous day on average of 2.25 points.
Actually, the average for bullish days is 8.5 points and -7.25 points for bearish days.
Charles

No comments:

Post a Comment