|
PureBytes Links
Trading Reference Links
|
Acetrader:
> My problem now is how do I populate the extra row
> of synthetic data with future date and time stamp. I can manually insert
> the date and time in Excel but it's too tedious. Can anyone help?
This is pretty easy. You'll have to create some formula columns and then
paste-by-value the result.
Type the number 37000 into a cell. Change the cell format to Custom,
using the format yyyymmdd H:mm:ss. You'll notice this number corresponds
to midnight, 20010419 (19 April 2001). If you add the value =TIME(0,0,1)
to it, you get one second after midnight, 19 April 2001.
So to get 5-minute bars, for example, you would start with some number in
(say) cell A1 like =DATE(2000,1,3)+TIME(8,35,0) which will give you 8:35
in the morning for 3 January 2000. Then in cell A2 you would put
=A1+TIME(0,5,0) to get a value for 5 minutes later. In this way you can
generate a list of times.
If you need the dates and times in separate columns, just start with the
date in one column and the time in the other. Add 5 minutes to each time,
and have an IF() test in the date and time columns to increment the date
and re-set the time when the time reaches the closing time for trading.
You can also include in the IF() a test for weekends using the =WEEKDAY()
function.
Once you have generated your list, you can copy and paste by value into
your simulated data. Remember to format the dates and times like you
want, or you'll just get numbers. Once the dates and times are formatted
properly, Save As a .csv file and the file will be saved with the proper
formats for dates and times, comma-delimited.
-Alex
|