Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Creating estimation window using high frequency data

    Hi guys.

    I am doing an event-study using high-frequency financial data and I would appreciate if you could help me with the below.

    Click image for larger version Name: untitled.png Views: 1 Size: 9.2 KB ID: 1442065


    As you can see I have an event which occurs on 04 Jan 2006 at 10:00, which I have defined as being evday=0 on stata.

    I need to define an estimation window which spans over prior 10 days for example but only 30 minutes prior to the time the event occurred, that is at 10:00. It would look like the above, i.e. my event window would then be an aggregation of days -1, -10, covering only times from 09:29 to 09.59.

    Below is a snapshot of my data. I've only included 2 days of data from 09:35 to 10:59 each day not to make it too heavy for a post on the forum.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
clear
input str4 underlying_symbol double dt float return_sp500
"^SPX" 1451899860000              .
"^SPX" 1451899920000    .0005744325
"^SPX" 1451899980000   .00027911077
"^SPX" 1451900040000   -.0003828115
"^SPX" 1.4519001e+12    .0000558926
"^SPX" 1451900160000   -.0003430974
"^SPX" 1451900220000  7.9871415e-06
"^SPX" 1451900280000  -.00013569267
"^SPX" 1451900340000   -.0003511525
"^SPX" 1.4519004e+12    .0002713648
"^SPX" 1451900460000  -.00001597779
"^SPX" 1451900520000   .00013570566
"^SPX" 1451900580000   .00015954993
"^SPX" 1451900640000  .000015973073
"^SPX" 1.4519007e+12    .0004707015
"^SPX" 1451900760000  -.00022334624
"^SPX" 1451900820000  -.00055858394
"^SPX" 1451900880000  -.00017557766
"^SPX" 1451900940000  .000071813236
"^SPX"  1.451901e+12   .00016767405
"^SPX" 1451901060000     .000335069
"^SPX" 1451901120000  -.00025528512
"^SPX" 1451901180000   -.0003272616
"^SPX" 1451901240000   -.0005828862
"^SPX" 1.4519013e+12  -.00021569553
"^SPX" 1451901360000     .000135839
"^SPX" 1451901420000   .00004787568
"^SPX" 1451901480000   .00014380828
"^SPX" 1451901540000    .0003752666
"^SPX" 1.4519016e+12   .00013574358
"^SPX" 1451901660000   -.0003273556
"^SPX" 1451901720000    .0006864562
"^SPX" 1451901780000    .0003909086
"^SPX" 1451901840000   -.0007899605
"^SPX" 1.4519019e+12   -.0005988766
"^SPX" 1451901960000   -.0007350496
"^SPX" 1451902020000   .00043947555
"^SPX" 1451902080000  -.00008787966
"^SPX" 1451902140000     -.00089533
"^SPX" 1.4519022e+12   -.0004879629
"^SPX" 1451902260000     .000416009
"^SPX" 1451902320000   -.0004560565
"^SPX" 1451902380000  -.00048020715
"^SPX" 1451902440000    .0002961631
"^SPX" 1.4519025e+12   .00011205206
"^SPX" 1451902560000   -.0000880189
"^SPX" 1451902620000  .000071998285
"^SPX" 1451902680000 -.000071998285
"^SPX" 1451902740000 -.000016022033
"^SPX" 1.4519028e+12    .0003840673
"^SPX" 1451902860000  .000072069444
"^SPX" 1451902920000 -.000032030224
"^SPX" 1451902980000   -.0006402337
"^SPX" 1451903040000   .00013611083
"^SPX" 1.4519031e+12   -.0007045958
"^SPX" 1451903160000   .00002405182
"^SPX" 1451903220000   -.0003525246
"^SPX" 1451903280000  -.00009614685
"^SPX" 1451903340000  -.00056904997
"^SPX" 1.4519034e+12   .00015227412
"^SPX" 1451903460000    .0003366627
"^SPX" 1451903520000  -.00024849916
"^SPX" 1451903580000  -.00018435974
"^SPX" 1451903640000  -.00013623667
"^SPX" 1.4519037e+12  -.00012832608
"^SPX" 1451903760000  -.00012834255
"^SPX" 1451903820000  -.00008821447
"^SPX" 1451903880000   -.0004332548
"^SPX" 1451903940000              0
"^SPX"  1.451904e+12   -.0003691589
"^SPX" 1451904060000    .0003130297
"^SPX" 1451904120000    .0005615423
"^SPX" 1451904180000    .0000802785
"^SPX" 1451904240000    .0002726051
"^SPX" 1.4519043e+12  .000032100845
"^SPX" 1451904360000 -.000072228344
"^SPX" 1451904420000   .00008015553
"^SPX" 1451904480000   .00016841335
"^SPX" 1451904540000    .0003206017
"^SPX" 1.4519046e+12   .00005604828
"^SPX" 1451904660000  -.00004812503
"^SPX" 1451904720000   .00013625044
"^SPX" 1451904780000  -.00006406376
"^SPX" 1451904840000   .00008812328
"^SPX" 1.4519049e+12  -.00017625434
"^SPX" 1451904960000    .0003845504
"^SPX" 1451905020000  .000016036023
"^SPX" 1451905080000    -.000160274
"^SPX" 1451905140000  -.00017624305
"^SPX" 1451986260000  -.00007100496
"^SPX" 1451986320000   .00003155838
"^SPX" 1451986380000   -.0003625965
"^SPX" 1451986440000   -.0004337873
"^SPX" 1.4519865e+12   .00048893585
"^SPX" 1451986560000   .00014983857
"^SPX" 1451986620000  -.00002367242
"^SPX" 1451986680000   -.0003469711
"^SPX" 1451986740000   .00021291306
"^SPX" 1.4519868e+12    .0003941353
"^SPX" 1451986860000  .000023666267
end
format %tc dt
    Any insights on how I can work around this? Any tips on the commands I need to use would be much appreciated.

    Thank you for any forthcoming replies.

    P.S

    My apologies if the picture is inappropriate for a post on the forum. I was not really sure how to explain what I'm trying to do other than putting it in graphical form!
    Tags: None
  • #2
    Well, in order to do this there needs to be an observation corresponding to the actual event, and there needs to be a variable identifying the observation corresponding to the event. Your example has neither, so I have modified your example data to include those. (See the last observation and the variable event.)

    Code:
    clear
input str4 underlying_symbol double dt float return_sp500 byte event
"^SPX" 1451899860000              . 0
"^SPX" 1451899920000    .0005744325 0
"^SPX" 1451899980000   .00027911077 0
"^SPX" 1451900040000   -.0003828115 0
"^SPX" 1.4519001e+12    .0000558926 0
"^SPX" 1451900160000   -.0003430974 0
"^SPX" 1451900220000  7.9871415e-06 0
"^SPX" 1451900280000  -.00013569267 0
"^SPX" 1451900340000   -.0003511525 0
"^SPX" 1.4519004e+12    .0002713648 0
"^SPX" 1451900460000  -.00001597779 0
"^SPX" 1451900520000   .00013570566 0
"^SPX" 1451900580000   .00015954993 0
"^SPX" 1451900640000  .000015973073 0
"^SPX" 1.4519007e+12    .0004707015 0
"^SPX" 1451900760000  -.00022334624 0
"^SPX" 1451900820000  -.00055858394 0
"^SPX" 1451900880000  -.00017557766 0
"^SPX" 1451900940000  .000071813236 0
"^SPX"  1.451901e+12   .00016767405 0
"^SPX" 1451901060000     .000335069 0
"^SPX" 1451901120000  -.00025528512 0
"^SPX" 1451901180000   -.0003272616 0
"^SPX" 1451901240000   -.0005828862 0
"^SPX" 1.4519013e+12  -.00021569553 0
"^SPX" 1451901360000     .000135839 0
"^SPX" 1451901420000   .00004787568 0
"^SPX" 1451901480000   .00014380828 0
"^SPX" 1451901540000    .0003752666 0
"^SPX" 1.4519016e+12   .00013574358 0
"^SPX" 1451901660000   -.0003273556 0
"^SPX" 1451901720000    .0006864562 0
"^SPX" 1451901780000    .0003909086 0
"^SPX" 1451901840000   -.0007899605 0
"^SPX" 1.4519019e+12   -.0005988766 0
"^SPX" 1451901960000   -.0007350496 0
"^SPX" 1451902020000   .00043947555 0
"^SPX" 1451902080000  -.00008787966 0
"^SPX" 1451902140000     -.00089533 0
"^SPX" 1.4519022e+12   -.0004879629 0
"^SPX" 1451902260000     .000416009 0
"^SPX" 1451902320000   -.0004560565 0
"^SPX" 1451902380000  -.00048020715 0
"^SPX" 1451902440000    .0002961631 0
"^SPX" 1.4519025e+12   .00011205206 0
"^SPX" 1451902560000   -.0000880189 0
"^SPX" 1451902620000  .000071998285 0
"^SPX" 1451902680000 -.000071998285 0
"^SPX" 1451902740000 -.000016022033 0
"^SPX" 1.4519028e+12    .0003840673 0
"^SPX" 1451902860000  .000072069444 0
"^SPX" 1451902920000 -.000032030224 0
"^SPX" 1451902980000   -.0006402337 0
"^SPX" 1451903040000   .00013611083 0
"^SPX" 1.4519031e+12   -.0007045958 0
"^SPX" 1451903160000   .00002405182 0
"^SPX" 1451903220000   -.0003525246 0
"^SPX" 1451903280000  -.00009614685 0
"^SPX" 1451903340000  -.00056904997 0
"^SPX" 1.4519034e+12   .00015227412 0
"^SPX" 1451903460000    .0003366627 0
"^SPX" 1451903520000  -.00024849916 0
"^SPX" 1451903580000  -.00018435974 0
"^SPX" 1451903640000  -.00013623667 0
"^SPX" 1.4519037e+12  -.00012832608 0
"^SPX" 1451903760000  -.00012834255 0
"^SPX" 1451903820000  -.00008821447 0
"^SPX" 1451903880000   -.0004332548 0
"^SPX" 1451903940000              0 0
"^SPX"  1.451904e+12   -.0003691589 0
"^SPX" 1451904060000    .0003130297 0
"^SPX" 1451904120000    .0005615423 0
"^SPX" 1451904180000    .0000802785 0
"^SPX" 1451904240000    .0002726051 0
"^SPX" 1.4519043e+12  .000032100845 0
"^SPX" 1451904360000 -.000072228344 0
"^SPX" 1451904420000   .00008015553 0
"^SPX" 1451904480000   .00016841335 0
"^SPX" 1451904540000    .0003206017 0
"^SPX" 1.4519046e+12   .00005604828 0
"^SPX" 1451904660000  -.00004812503 0
"^SPX" 1451904720000   .00013625044 0
"^SPX" 1451904780000  -.00006406376 0
"^SPX" 1451904840000   .00008812328 0
"^SPX" 1.4519049e+12  -.00017625434 0
"^SPX" 1451904960000    .0003845504 0
"^SPX" 1451905020000  .000016036023 0
"^SPX" 1451905080000    -.000160274 0
"^SPX" 1451905140000  -.00017624305 0
"^SPX" 1451986260000  -.00007100496 0
"^SPX" 1451986320000   .00003155838 0
"^SPX" 1451986380000   -.0003625965 0
"^SPX" 1451986440000   -.0004337873 0
"^SPX" 1.4519865e+12   .00048893585 0
"^SPX" 1451986560000   .00014983857 0
"^SPX" 1451986620000  -.00002367242 0
"^SPX" 1451986680000   -.0003469711 0
"^SPX" 1451986740000   .00021291306 0
"^SPX" 1.4519868e+12    .0003941353 0
"^SPX" 1451986860000  .000023666267 0
"^SPX" 1451988000000  .000023666267 1
end
format %tc dt

//    CREATE SEPARATE DATE AND TIME VARIABLES
gen date = dofc(dt)
format date %td
gen time = dt - cofd(date)
format time %tcHH:MM:SS

//    EXTRACT THE EVENT DATE AND TIME FOR EACH UNDERLYING SYMBOL
by underlying_symbol (dt), sort: egen event_date = max(cond(event, date, .))
format event_date %td
by underlying_symbol (dt): egen event_time = max(cond(event, time, .))
format event_time %tcHH:MM:SS

//    DEFINE WINDOW
gen byte event_window = inrange(date, event_date-10, event_date-1) ///
    & inrange(time, event_time-msofminutes(31), event_time-msofminutes(1))

    Comment

    • #3
      Thanks Clyde for replying. Highly appreciate it.

      The example I provided is not a good one at all! My apologies for that.

      Hopefully the one below should be able to show my problem more explicitly.


      ----------------------- copy starting from the next line -----------------------
      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input double dt float(date time event_date event_time return_sp500 evday evt_window est_window)
1.5155838e+12 17541 4.14e+07     .        .   -.0009265866 . . .
1515583860000 17541 41460000     .        .   -.0003637691 . . .
1515583920000 17541 41520000     .        .   -.0005709089 . . .
1515583980000 17541 41580000     .        .     .000349692 . . .
1515584040000 17541 41640000     .        .   -.0004210568 . . .
1.5155841e+12 17541 4.17e+07     .        .    .0007135926 . . .
1515584160000 17541 41760000     .        .  -.00029253575 . . .
1515584220000 17541 41820000     .        .  -.00012125455 . . 1
1515584280000 17541 41880000     .        .   -.0008138834 . . 1
1515584340000 17541 41940000     .        .  -.00005710739 . . 1
1.5155844e+12 17541 4.20e+07     .        .   .00027844397 . . 1
1515584460000 17541 42060000     .        .    -.001329017 . . 1
1515584520000 17541 42120000     .        .    .0004075192 . . 1
1515584580000 17541 42180000     .        .    .0003286867 . . 1
1515584640000 17541 42240000     .        .    .0000785792 . . 1
1.5155847e+12 17541 4.23e+07     .        .   -.0002072322 . . 1
1515584760000 17541 42360000     .        . -.000021371083 . . 1
1515584820000 17541 42420000     .        .  .000021371083 . . 1
1515584880000 17541 42480000     .        .  -.00020003376 . . 1
1515584940000 17541 42540000     .        .    .0004929896 . . 1
 1.515585e+12 17541 4.26e+07     .        .    .0010995411 . . 1
1515585060000 17541 42660000     .        .    .0006277544 . . 1
1515585120000 17541 42720000     .        .   .00007843071 . . 1
1515585180000 17541 42780000     .        .  .000014275295 . . 1
1515585240000 17541 42840000     .        .     .000613036 . . 1
1.5155853e+12 17541 4.29e+07     .        .   -.0004633318 . . 1
1515585360000 17541 42960000     .        .  -.00004273305 . . 1
1515585420000 17541 43020000     .        .    -.000627629 . . 1
1515585480000 17541 43080000     .        .    .0003352372 . . 1
1515585540000 17541 43140000     .        .   .00002855527 . . 1
1.5155856e+12 17541 4.32e+07     .        .    .0004135247 . . 1
1515585660000 17541 43260000     .        .   -.0003493654 . . 1
1515585720000 17541 43320000     .        .  .000028552626 . . 1
1515585780000 17541 43380000     .        .  -.00004282924 . . 1
1515585840000 17541 43440000     .        .  -.00007129909 . . 1
1.5155859e+12 17541 4.35e+07     .        .   .00021388203 . . 1
1515585960000 17541 43560000     .        .  -.00016399824 . . 1
1515586020000 17541 43620000     .        .  -.00027103905 . . .
1515586080000 17541 43680000     .        .  -.00006417902 . . .
1515586140000 17541 43740000     .        .   -.0003567606 . . .
1.5155862e+12 17541 4.38e+07     .        .    .0013692987 . . .
1515586260000 17541 43860000     .        .  -.00008552082 . . .
1515586320000 17541 43920000     .        .  -.00010693306 . . .
1515586380000 17541 43980000     .        .   -.0000356759 . . .
1515586440000 17541 44040000     .        .   .00057727116 . . .
1.5155865e+12 17541 4.41e+07     .        .    .0007904668 . . .
1515669360000 17542 40560000     .        .    .0004108589 . . .
1515669420000 17542 40620000     .        .   -.0008360779 . . .
1515669480000 17542 40680000     .        .    .0006873546 . . .
1515669540000 17542 40740000     .        .   -.0004605774 . . .
1.5156696e+12 17542 4.08e+07     .        .   -.0010281337 . . .
1515669660000 17542 40860000     .        .     .000730397 . . .
1515669720000 17542 40920000     .        .   .00001419231 . . .
1515669780000 17542 40980000     .        .   -.0001701479 . . .
1515669840000 17542 41040000     .        .    .0003828354 . . .
1.5156699e+12 17542 4.11e+07     .        .   -.0008154322 . . .
1515669960000 17542 41160000     .        .    .0003617078 . . .
1515670020000 17542 41220000     .        . -.000021293674 . . .
1515670080000 17542 41280000     .        .   .00053878094 . . .
1515670140000 17542 41340000     .        .  -.00017719626 . . .
1.5156702e+12 17542 4.14e+07     .        .    .0004181086 . . .
1515670260000 17542 41460000     .        .    .0010340093 . . .
1515670320000 17542 41520000     .        .   .00055188104 . . .
1515670380000 17542 41580000     .        .    .0009828531 . . .
1515670440000 17542 41640000     .        .    .0012218888 . . .
1.5156705e+12 17542 4.17e+07     .        .   -.0008261548 . . .
1515670560000 17542 41760000     .        .  -.00006355875 . . .
1515670620000 17542 41820000     .        .    .0003178396 . . .
1515670680000 17542 41880000     .        .   .00055076316 . . .
1515670740000 17542 41940000     .        .   -.0008898186 . . .
1.5156708e+12 17542 4.20e+07     .        .   -.0013716223 . . .
1515670860000 17542 42060000     .        .    .0003961539 . . .
1515670920000 17542 42120000     .        .   -.0006437866 . . .
1515670980000 17542 42180000     .        .    -.000162761 . . .
1515671040000 17542 42240000     .        .  -.00010618904 . . .
1.5156711e+12 17542 4.23e+07     .        .  .000035426405 . . .
1515671160000 17542 42360000     .        .   -.0002265778 . . .
1515671220000 17542 42420000     .        .   .00024074804 . . .
1515671280000 17542 42480000     .        .   -.0004106719 . . .
1515671340000 17542 42540000     .        .   .00051676924 . . .
1.5156714e+12 17542 4.26e+07     .        .   .00021941653 . . .
1515671460000 17542 42660000     .        .     .000954765 . . .
1515671520000 17542 42720000     .        .  -.00004944686 . . .
1515671580000 17542 42780000     .        .    -.000318227 . . .
1515671640000 17542 42840000     .        .  -.00055876054 . . .
1.5156717e+12 17542 4.29e+07     .        .    .0001627126 . . .
1515671760000 17542 42960000     .        .   .00025463875 . . .
1515671820000 17542 43020000     .        .   -.0006791808 . . .
1515671880000 17542 43080000     .        .   -.0001840386 . . .
1515671940000 17542 43140000     .        .    .0004953583 . . .
 1.515672e+12 17542 4.32e+07     .        .  -.00012031823 . . .
1515672060000 17542 43260000     .        .    -.000346784 . . .
1515672120000 17542 43320000     .        .   -.0011190126 . . .
1515672180000 17542 43380000     .        .    -.000382773 . . .
1515672240000 17542 43440000     .        .    .0007228542 . . .
1.5156723e+12 17542 4.35e+07     .        .    .0001629074 . . .
1515672360000 17542 43560000     .        .   -.0004605154 . . .
1515672420000 17542 43620000 17542 43620000  -.00028341968 0 1 .
1515672480000 17542 43680000     .        .    .0006943913 . . .
1515672540000 17542 43740000     .        .   .00031157545 . . .
end
format %tc dt
format %td date
format %tcHH:MM:SS time
format %td event_date
format %tcHH:MM:SS event_time
      ------------------ copy up to and including the previous line ------------------


      So as you can see I have already defined my event day (evday==0) and event window (evt_window==1).

      So my goal to create an estimation window which spans over the past 10 days (-10, -1) and consists only of 30 minutes prior to the event time for each of these days.

      I've manually created the estimation window (est_window column in italic and red) to show you what I want to achieve. As you can see the estimation window is -1 days and the time is 30 minutes prior to the event time (i.e. 12.07 on event day).

      Any help would be much appreciated.

      Regards
      Parvesh

      Comment

      • #4
        I've been thinking about it hard.

        My guess would be:

        1. Create a day count so that I could say pick days -10,-1.
        2. Then create an if statement that restricts the estimation window to take only the prior 30mins before the event time in each of the prior ten days.

        Any insights would be more than welcomed.

        Comment

        • #5
          Code:
          * Example generated by -dataex-. To install: ssc install dataex
clear
input double dt float(date time event_date event_time return_sp500 evday evt_window est_window)
1.5155838e+12 17541 4.14e+07     .        .   -.0009265866 . . .
1515583860000 17541 41460000     .        .   -.0003637691 . . .
1515583920000 17541 41520000     .        .   -.0005709089 . . .
1515583980000 17541 41580000     .        .     .000349692 . . .
1515584040000 17541 41640000     .        .   -.0004210568 . . .
1.5155841e+12 17541 4.17e+07     .        .    .0007135926 . . .
1515584160000 17541 41760000     .        .  -.00029253575 . . .
1515584220000 17541 41820000     .        .  -.00012125455 . . 1
1515584280000 17541 41880000     .        .   -.0008138834 . . 1
1515584340000 17541 41940000     .        .  -.00005710739 . . 1
1.5155844e+12 17541 4.20e+07     .        .   .00027844397 . . 1
1515584460000 17541 42060000     .        .    -.001329017 . . 1
1515584520000 17541 42120000     .        .    .0004075192 . . 1
1515584580000 17541 42180000     .        .    .0003286867 . . 1
1515584640000 17541 42240000     .        .    .0000785792 . . 1
1.5155847e+12 17541 4.23e+07     .        .   -.0002072322 . . 1
1515584760000 17541 42360000     .        . -.000021371083 . . 1
1515584820000 17541 42420000     .        .  .000021371083 . . 1
1515584880000 17541 42480000     .        .  -.00020003376 . . 1
1515584940000 17541 42540000     .        .    .0004929896 . . 1
 1.515585e+12 17541 4.26e+07     .        .    .0010995411 . . 1
1515585060000 17541 42660000     .        .    .0006277544 . . 1
1515585120000 17541 42720000     .        .   .00007843071 . . 1
1515585180000 17541 42780000     .        .  .000014275295 . . 1
1515585240000 17541 42840000     .        .     .000613036 . . 1
1.5155853e+12 17541 4.29e+07     .        .   -.0004633318 . . 1
1515585360000 17541 42960000     .        .  -.00004273305 . . 1
1515585420000 17541 43020000     .        .    -.000627629 . . 1
1515585480000 17541 43080000     .        .    .0003352372 . . 1
1515585540000 17541 43140000     .        .   .00002855527 . . 1
1.5155856e+12 17541 4.32e+07     .        .    .0004135247 . . 1
1515585660000 17541 43260000     .        .   -.0003493654 . . 1
1515585720000 17541 43320000     .        .  .000028552626 . . 1
1515585780000 17541 43380000     .        .  -.00004282924 . . 1
1515585840000 17541 43440000     .        .  -.00007129909 . . 1
1.5155859e+12 17541 4.35e+07     .        .   .00021388203 . . 1
1515585960000 17541 43560000     .        .  -.00016399824 . . 1
1515586020000 17541 43620000     .        .  -.00027103905 . . .
1515586080000 17541 43680000     .        .  -.00006417902 . . .
1515586140000 17541 43740000     .        .   -.0003567606 . . .
1.5155862e+12 17541 4.38e+07     .        .    .0013692987 . . .
1515586260000 17541 43860000     .        .  -.00008552082 . . .
1515586320000 17541 43920000     .        .  -.00010693306 . . .
1515586380000 17541 43980000     .        .   -.0000356759 . . .
1515586440000 17541 44040000     .        .   .00057727116 . . .
1.5155865e+12 17541 4.41e+07     .        .    .0007904668 . . .
1515669360000 17542 40560000     .        .    .0004108589 . . .
1515669420000 17542 40620000     .        .   -.0008360779 . . .
1515669480000 17542 40680000     .        .    .0006873546 . . .
1515669540000 17542 40740000     .        .   -.0004605774 . . .
1.5156696e+12 17542 4.08e+07     .        .   -.0010281337 . . .
1515669660000 17542 40860000     .        .     .000730397 . . .
1515669720000 17542 40920000     .        .   .00001419231 . . .
1515669780000 17542 40980000     .        .   -.0001701479 . . .
1515669840000 17542 41040000     .        .    .0003828354 . . .
1.5156699e+12 17542 4.11e+07     .        .   -.0008154322 . . .
1515669960000 17542 41160000     .        .    .0003617078 . . .
1515670020000 17542 41220000     .        . -.000021293674 . . .
1515670080000 17542 41280000     .        .   .00053878094 . . .
1515670140000 17542 41340000     .        .  -.00017719626 . . .
1.5156702e+12 17542 4.14e+07     .        .    .0004181086 . . .
1515670260000 17542 41460000     .        .    .0010340093 . . .
1515670320000 17542 41520000     .        .   .00055188104 . . .
1515670380000 17542 41580000     .        .    .0009828531 . . .
1515670440000 17542 41640000     .        .    .0012218888 . . .
1.5156705e+12 17542 4.17e+07     .        .   -.0008261548 . . .
1515670560000 17542 41760000     .        .  -.00006355875 . . .
1515670620000 17542 41820000     .        .    .0003178396 . . .
1515670680000 17542 41880000     .        .   .00055076316 . . .
1515670740000 17542 41940000     .        .   -.0008898186 . . .
1.5156708e+12 17542 4.20e+07     .        .   -.0013716223 . . .
1515670860000 17542 42060000     .        .    .0003961539 . . .
1515670920000 17542 42120000     .        .   -.0006437866 . . .
1515670980000 17542 42180000     .        .    -.000162761 . . .
1515671040000 17542 42240000     .        .  -.00010618904 . . .
1.5156711e+12 17542 4.23e+07     .        .  .000035426405 . . .
1515671160000 17542 42360000     .        .   -.0002265778 . . .
1515671220000 17542 42420000     .        .   .00024074804 . . .
1515671280000 17542 42480000     .        .   -.0004106719 . . .
1515671340000 17542 42540000     .        .   .00051676924 . . .
1.5156714e+12 17542 4.26e+07     .        .   .00021941653 . . .
1515671460000 17542 42660000     .        .     .000954765 . . .
1515671520000 17542 42720000     .        .  -.00004944686 . . .
1515671580000 17542 42780000     .        .    -.000318227 . . .
1515671640000 17542 42840000     .        .  -.00055876054 . . .
1.5156717e+12 17542 4.29e+07     .        .    .0001627126 . . .
1515671760000 17542 42960000     .        .   .00025463875 . . .
1515671820000 17542 43020000     .        .   -.0006791808 . . .
1515671880000 17542 43080000     .        .   -.0001840386 . . .
1515671940000 17542 43140000     .        .    .0004953583 . . .
 1.515672e+12 17542 4.32e+07     .        .  -.00012031823 . . .
1515672060000 17542 43260000     .        .    -.000346784 . . .
1515672120000 17542 43320000     .        .   -.0011190126 . . .
1515672180000 17542 43380000     .        .    -.000382773 . . .
1515672240000 17542 43440000     .        .    .0007228542 . . .
1.5156723e+12 17542 4.35e+07     .        .    .0001629074 . . .
1515672360000 17542 43560000     .        .   -.0004605154 . . .
1515672420000 17542 43620000 17542 43620000  -.00028341968 0 1 .
1515672480000 17542 43680000     .        .    .0006943913 . . .
1515672540000 17542 43740000     .        .   .00031157545 . . .
end
format %tc dt
format %td date
format %tcHH:MM:SS time
format %td event_date
format %tcHH:MM:SS event_time

gen underlying_symbol = "^SPX"

//    SPREAD EVENT DATE AND TIME TO ALL OBSERVATIONS FOR THE SYMBOL
by underlying_symbol (event_date), sort: replace event_date = event_date[1]
by underlying_symbol (event_time), sort: replace event_time = event_time[1]


//    DEFINE WINDOW
gen byte estimation_window = inrange(date, event_date-10, event_date-1) ///
    & inrange(time, event_time-msofminutes(30), event_time-msofminutes(1))
    
//    VERIFY ESTIMATION WINDOW IS WHAT WAS DESIRED
mvencode est_window, mv(0)
assert est_window == estimation_window
          In your new example you have eliminated the underlying_symbol variable. But I assume you still want to do this by symbol, so I have re-created that variable so that the code will take it into account.

          I also see that you have already calculated separate date and time values for the event observation--but I need those to be in every observation, so I have replaced the missing values.

          The estimation window variable is then calculated. The code is a light edit of what I proposed yesterday in that it begins 30, not 31 minutes before the event time.

          The last two lines of code just verify that my calculation matches your hand-calculated result. Note that I calculate this variable as 1/0, not 1/missing. This is better Stata practice, and the way Stata deals with both missing values and logical expressions you are likely to get into trouble eventually using 1/missing coding. Change it if you want, but do so at your own risk. I have left your evday == 0 variable alone, even though it similarly offends, because I don't need to use it in my code. But, again, you are likely to get into trouble with this coding at some point. It would be better to have evday = 1 where you have 0, and 0 where you have missing values.

          Comment

          • #6
            Thanks Clyde.

            As far as the symbol is concerned I have only one symbol so I'm not defining by symbol. And thanks, the code works fine if there's only one event date.

            In my case I have 64 events and I need to define the estimation window for each of them.

            My code looks as follows so far:

            Code:
            forvalues i = 1/73 {
preserve
keep if obs == `i'
joinby nnn using marketdata
sort dt
drop time_id
g time_id = _n
gen time_cnt = time_id
gen target_time = time_cnt if dt==event_dt
egen max_target_time = max(target_time)
gen evtime = time_cnt-max_target_time
drop time_cnt target_time max_target_time
sort evtime
gen evt_window=1 if evtime>=0 & evtime<=15

gen est_window???

drop if evt_window==. & est_window==.
reg return_sp500 if est_window==1, robust
gen rmse = e(rmse)
predict phat
gen ar = return_sp500 - phat if evt_window==1
drop phat
drop if evt_window==.
drop est_window
***************************************************
* Calculate CAR & test statistics
egen car = sum(ar)
gen tstat = car/(rmse*sqrt(_N))
* DO EVENT analysis, generate CAR in 1/1
keep in 1/1
append using 15mins_evtwindow
save 15mins_evtwindow, replace
restore
}
            Would be grateful if you could provide a hint.

            Thanks

            Comment

            • #7
              OK. So your example data doesn't illustrate this problem. I have created a new example data set that does, and written new code that works correctly with my example. It is a very long -dataex-

              N.B. In my example data, evday is coded as 1 for the observations corresponding to the events, and 0 elsewhere; the code relies on this and will not work with 1/missing coding.

              Also my example omits variable ret_sp500 which plays no role in this aspect of the problem. This should not affect your ability to adapt my code to your data.

              Note that at the end of this code, the variables event_dt, event_date, and event_time are defined only for those observations that are part of the estimation window: they serve to identify which observation the are in-window for.

              I have not thought about how this code would behave if an event happened to be within the estimation window of another event. If this problem arises in your data, the code will give you an error message at the end and stop there. You will then need to sort through the results to see if they make sense or not.

              Code:
              * Example generated by -dataex-. To install: ssc install dataex
clear
input double dt float(date time evday)
1.5155838e+12 17541 4.14e+07 0
1515583860000 17541 41460000 0
1515583920000 17541 41520000 0
1515583980000 17541 41580000 0
1515584040000 17541 41640000 0
1.5155841e+12 17541 4.17e+07 0
1515584160000 17541 41760000 0
1515584220000 17541 41820000 0
1515584280000 17541 41880000 0
1515584340000 17541 41940000 0
1.5155844e+12 17541 4.20e+07 0
1515584460000 17541 42060000 0
1515584520000 17541 42120000 0
1515584580000 17541 42180000 0
1515584640000 17541 42240000 0
1.5155847e+12 17541 4.23e+07 0
1515584760000 17541 42360000 0
1515584820000 17541 42420000 0
1515584880000 17541 42480000 0
1515584940000 17541 42540000 0
 1.515585e+12 17541 4.26e+07 0
1515585060000 17541 42660000 0
1515585120000 17541 42720000 0
1515585180000 17541 42780000 0
1515585240000 17541 42840000 0
1.5155853e+12 17541 4.29e+07 0
1515585360000 17541 42960000 0
1515585420000 17541 43020000 0
1515585480000 17541 43080000 0
1515585540000 17541 43140000 0
1.5155856e+12 17541 4.32e+07 0
1515585660000 17541 43260000 0
1515585720000 17541 43320000 0
1515585780000 17541 43380000 0
1515585840000 17541 43440000 0
1.5155859e+12 17541 4.35e+07 0
1515585960000 17541 43560000 0
1515586020000 17541 43620000 0
1515586080000 17541 43680000 0
1515586140000 17541 43740000 0
1.5155862e+12 17541 4.38e+07 0
1515586260000 17541 43860000 0
1515586320000 17541 43920000 0
1515586380000 17541 43980000 0
1515586440000 17541 44040000 0
1.5155865e+12 17541 4.41e+07 0
1515669360000 17542 40560000 0
1515669420000 17542 40620000 0
1515669480000 17542 40680000 0
1515669540000 17542 40740000 0
1.5156696e+12 17542 4.08e+07 0
1515669660000 17542 40860000 0
1515669720000 17542 40920000 0
1515669780000 17542 40980000 0
1515669840000 17542 41040000 0
1.5156699e+12 17542 4.11e+07 0
1515669960000 17542 41160000 0
1515670020000 17542 41220000 0
1515670080000 17542 41280000 0
1515670140000 17542 41340000 0
1.5156702e+12 17542 4.14e+07 0
1515670260000 17542 41460000 0
1515670320000 17542 41520000 0
1515670380000 17542 41580000 0
1515670440000 17542 41640000 0
1.5156705e+12 17542 4.17e+07 0
1515670560000 17542 41760000 0
1515670620000 17542 41820000 0
1515670680000 17542 41880000 0
1515670740000 17542 41940000 0
1.5156708e+12 17542 4.20e+07 0
1515670860000 17542 42060000 0
1515670920000 17542 42120000 0
1515670980000 17542 42180000 0
1515671040000 17542 42240000 1
1.5156711e+12 17542 4.23e+07 0
1515671160000 17542 42360000 0
1515671220000 17542 42420000 0
1515671280000 17542 42480000 0
1515671340000 17542 42540000 0
1.5156714e+12 17542 4.26e+07 0
1515671460000 17542 42660000 0
1515671520000 17542 42720000 0
1515671580000 17542 42780000 0
1515671640000 17542 42840000 0
1.5156717e+12 17542 4.29e+07 0
1515671760000 17542 42960000 0
1515671820000 17542 43020000 0
1515671880000 17542 43080000 0
1515671940000 17542 43140000 0
 1.515672e+12 17542 4.32e+07 0
1515672060000 17542 43260000 0
1515672120000 17542 43320000 0
1515672180000 17542 43380000 0
1515672240000 17542 43440000 0
1.5156723e+12 17542 4.35e+07 0
1515672360000 17542 43560000 0
1515672420000 17542 43620000 0
1515672480000 17542 43680000 0
1515672540000 17542 43740000 0
1.5181758e+12 17571 4.14e+07 0
1518175860000 17571 41460000 0
1518175920000 17571 41520000 0
1518175980000 17571 41580000 0
1518176040000 17571 41640000 0
1.5181761e+12 17571 4.17e+07 0
1518176160000 17571 41760000 0
1518176220000 17571 41820000 0
1518176280000 17571 41880000 0
1518176340000 17571 41940000 0
1.5181764e+12 17571 4.20e+07 0
1518176460000 17571 42060000 0
1518176520000 17571 42120000 0
1518176580000 17571 42180000 0
1518176640000 17571 42240000 0
1.5181767e+12 17571 4.23e+07 0
1518176760000 17571 42360000 0
1518176820000 17571 42420000 0
1518176880000 17571 42480000 0
1518176940000 17571 42540000 0
 1.518177e+12 17571 4.26e+07 0
1518177060000 17571 42660000 0
1518177120000 17571 42720000 0
1518177180000 17571 42780000 0
1518177240000 17571 42840000 0
1.5181773e+12 17571 4.29e+07 0
1518177360000 17571 42960000 0
1518177420000 17571 43020000 0
1518177480000 17571 43080000 0
1518177540000 17571 43140000 0
1.5181776e+12 17571 4.32e+07 0
1518177660000 17571 43260000 0
1518177720000 17571 43320000 0
1518177780000 17571 43380000 0
1518177840000 17571 43440000 0
1.5181779e+12 17571 4.35e+07 0
1518177960000 17571 43560000 0
1518178020000 17571 43620000 0
1518178080000 17571 43680000 0
1518178140000 17571 43740000 0
1.5181782e+12 17571 4.38e+07 0
1518178260000 17571 43860000 0
1518178320000 17571 43920000 0
1518178380000 17571 43980000 0
1518178440000 17571 44040000 0
1.5181785e+12 17571 4.41e+07 0
1518261360000 17572 40560000 0
1518261420000 17572 40620000 0
1518261480000 17572 40680000 0
1518261540000 17572 40740000 0
1.5182616e+12 17572 4.08e+07 0
1518261660000 17572 40860000 0
1518261720000 17572 40920000 0
1518261780000 17572 40980000 0
1518261840000 17572 41040000 0
1.5182619e+12 17572 4.11e+07 0
1518261960000 17572 41160000 0
1518262020000 17572 41220000 0
1518262080000 17572 41280000 0
1518262140000 17572 41340000 0
1.5182622e+12 17572 4.14e+07 0
1518262260000 17572 41460000 0
1518262320000 17572 41520000 0
1518262380000 17572 41580000 0
1518262440000 17572 41640000 0
1.5182625e+12 17572 4.17e+07 0
1518262560000 17572 41760000 0
1518262620000 17572 41820000 0
1518262680000 17572 41880000 0
1518262740000 17572 41940000 0
1.5182628e+12 17572 4.20e+07 0
1518262860000 17572 42060000 0
1518262920000 17572 42120000 0
1518262980000 17572 42180000 0
1518263040000 17572 42240000 1
1.5182631e+12 17572 4.23e+07 0
1518263160000 17572 42360000 0
1518263220000 17572 42420000 0
1518263280000 17572 42480000 0
1518263340000 17572 42540000 0
1.5182634e+12 17572 4.26e+07 0
1518263460000 17572 42660000 0
1518263520000 17572 42720000 0
1518263580000 17572 42780000 0
1518263640000 17572 42840000 0
1.5182637e+12 17572 4.29e+07 0
1518263760000 17572 42960000 0
1518263820000 17572 43020000 0
1518263880000 17572 43080000 0
1518263940000 17572 43140000 0
 1.518264e+12 17572 4.32e+07 0
1518264060000 17572 43260000 0
1518264120000 17572 43320000 0
1518264180000 17572 43380000 0
1518264240000 17572 43440000 0
1.5182643e+12 17572 4.35e+07 0
1518264360000 17572 43560000 0
1518264420000 17572 43620000 0
1518264480000 17572 43680000 0
1518264540000 17572 43740000 0
1.5207678e+12 17601 4.14e+07 0
1520767860000 17601 41460000 0
1520767920000 17601 41520000 0
1520767980000 17601 41580000 0
1520768040000 17601 41640000 0
1.5207681e+12 17601 4.17e+07 0
1520768160000 17601 41760000 0
1520768220000 17601 41820000 0
1520768280000 17601 41880000 0
1520768340000 17601 41940000 0
1.5207684e+12 17601 4.20e+07 0
1520768460000 17601 42060000 0
1520768520000 17601 42120000 0
1520768580000 17601 42180000 0
1520768640000 17601 42240000 0
1.5207687e+12 17601 4.23e+07 0
1520768760000 17601 42360000 0
1520768820000 17601 42420000 0
1520768880000 17601 42480000 0
1520768940000 17601 42540000 0
 1.520769e+12 17601 4.26e+07 0
1520769060000 17601 42660000 0
1520769120000 17601 42720000 0
1520769180000 17601 42780000 0
1520769240000 17601 42840000 0
1.5207693e+12 17601 4.29e+07 0
1520769360000 17601 42960000 0
1520769420000 17601 43020000 0
1520769480000 17601 43080000 0
1520769540000 17601 43140000 0
1.5207696e+12 17601 4.32e+07 0
1520769660000 17601 43260000 0
1520769720000 17601 43320000 0
1520769780000 17601 43380000 0
1520769840000 17601 43440000 0
1.5207699e+12 17601 4.35e+07 0
1520769960000 17601 43560000 0
1520770020000 17601 43620000 0
1520770080000 17601 43680000 0
1520770140000 17601 43740000 0
1.5207702e+12 17601 4.38e+07 0
1520770260000 17601 43860000 0
1520770320000 17601 43920000 0
1520770380000 17601 43980000 0
1520770440000 17601 44040000 0
1.5207705e+12 17601 4.41e+07 0
1520853360000 17602 40560000 0
1520853420000 17602 40620000 0
1520853480000 17602 40680000 0
1520853540000 17602 40740000 0
1.5208536e+12 17602 4.08e+07 0
1520853660000 17602 40860000 0
1520853720000 17602 40920000 0
1520853780000 17602 40980000 0
1520853840000 17602 41040000 0
1.5208539e+12 17602 4.11e+07 0
1520853960000 17602 41160000 0
1520854020000 17602 41220000 0
1520854080000 17602 41280000 0
1520854140000 17602 41340000 0
1.5208542e+12 17602 4.14e+07 0
1520854260000 17602 41460000 0
1520854320000 17602 41520000 0
1520854380000 17602 41580000 0
1520854440000 17602 41640000 0
1.5208545e+12 17602 4.17e+07 0
1520854560000 17602 41760000 0
1520854620000 17602 41820000 0
1520854680000 17602 41880000 0
1520854740000 17602 41940000 0
1.5208548e+12 17602 4.20e+07 0
1520854860000 17602 42060000 0
1520854920000 17602 42120000 0
1520854980000 17602 42180000 0
1520855040000 17602 42240000 1
1.5208551e+12 17602 4.23e+07 0
1520855160000 17602 42360000 0
1520855220000 17602 42420000 0
1520855280000 17602 42480000 0
1520855340000 17602 42540000 0
1.5208554e+12 17602 4.26e+07 0
1520855460000 17602 42660000 0
1520855520000 17602 42720000 0
1520855580000 17602 42780000 0
1520855640000 17602 42840000 0
1.5208557e+12 17602 4.29e+07 0
1520855760000 17602 42960000 0
1520855820000 17602 43020000 0
1520855880000 17602 43080000 0
1520855940000 17602 43140000 0
 1.520856e+12 17602 4.32e+07 0
1520856060000 17602 43260000 0
1520856120000 17602 43320000 0
1520856180000 17602 43380000 0
1520856240000 17602 43440000 0
1.5208563e+12 17602 4.35e+07 0
1520856360000 17602 43560000 0
1520856420000 17602 43620000 0
1520856480000 17602 43680000 0
1520856540000 17602 43740000 0
end
format %tc dt
format %td date
format %tcHH:MM:SS time

tempfile copy
save `copy'

keep if evday

rangejoin time `=-msofminutes(30)' `=-msofminutes(1)' using `copy'
keep if inrange(date_U, date - 10, date - 1)

rename (dt date time) event_=
drop evday
rename *_U *

merge 1:1 dt date time using `copy', assert(match using)
gen byte estimation_window = (_merge == 3)
drop _merge
sort dt

assert !evday if estimation_window
              Note: -rangejoin- is written by Robert Picard and is available from SSC.

              Comment

              • #8
                Here's a solution that is quite similar to Clyde's. I'm thinking it's not a bad idea to explore efficient ways to tackle this type of problem because each event is independent from the others and trying to handle all events at once may generate very large datasets.

                First, I start by generating a demonstration dataset with 64 events, spread out randomly in 2017. I also create a demonstration dataset of high-frequency financial data, also spread out randomly in 2017. I then use listsome (from SSC) to list a few random observations from each dataset.
                Code:
                clear all
set seed 3123
set obs 64
gen double event = runiformint(mdyhms(1,1,2017,0,0,0),mdyhms(1,1,2018,0,0,0))
format %tc event
sort event
gen event_id = _n
save "my_events.dta", replace
listsome, max(5) random

clear all
set obs 100000
gen double dt = runiformint(mdyhms(1,1,2017,0,0,0),mdyhms(1,1,2018,0,0,0))
format %tc dt
sort dt
gen return_sp500 = runiform()
save "my_marketdata.dta", replace
listsome, max(10) random
                and the listsome (from SSC) output for the events followed by the financial data:
                Code:
                . listsome, max(5) random

     +-------------------------------+
     |              event   event_id |
     |-------------------------------|
  4. | 04feb2017 20:28:08          4 |
 31. | 21may2017 19:26:15         31 |
 33. | 04jun2017 08:30:47         33 |
 42. | 10jul2017 18:35:47         42 |
 44. | 05aug2017 06:56:45         44 |
     +-------------------------------+

. 

. listsome, max(10) random

        +-------------------------------+
        |                 dt   retu~500 |
        |-------------------------------|
  9998. | 06feb2017 05:47:59   .3713164 |
 11999. | 13feb2017 11:47:34   .4210292 |
 16278. | 28feb2017 21:06:43   .9407082 |
 19509. | 12mar2017 20:34:32    .070147 |
 24016. | 29mar2017 02:51:20   .5785351 |
        |-------------------------------|
 24937. | 01apr2017 09:37:16   .1794766 |
 36472. | 13may2017 17:17:54   .5175447 |
 43928. | 09jun2017 18:12:31    .499375 |
 63345. | 19aug2017 20:51:35   .8131101 |
 83836. | 02nov2017 23:44:54   .0197808 |
        +-------------------------------+

.
                With datasets that we can play with, I use runby (from SSC) to process each event. In this case, there's only one observation per event so when the all_events program is called, the data in memory will contain a single observation with the date and time of the event and the event identifier. Since the problem requires searching for trades in the past 10 days within a 30 minute window that ends with the time of the event, I make 10 copies of the event observation, one for each of the 10 days in the overall target window and then calculate exact date and time window bounds for each of these days. A little reading of help datetime will probably be necessary to make sense of the code. I then follow Clyde's model and use rangejoin (from SSC) to find, for each of these 10 event windows, all trades within the desired bounds. At that point, the data in memory will contain all observations in the overall event window. I copied the desired estimation commands from #6; I don't do statistics so it's up to you get that part right. You will most likely want to reduce the data in memory to a single observation before exiting the all_events program. I commented the last line to skip that step to show what's going on.
                Code:
                clear all
use "my_events.dta"

* comment the following line when ready to process all events at once
keep in 1/2
list

program all_events
    expand 10
    gen double high = event - dhms(_n-1,0,0,0)
    gen double low = high - msofminutes(30)
    format %tc low high
    
    rangejoin dt low high using "my_marketdata.dta"
    
    reg return_sp500, robust
    gen rmse = e(rmse)
    predict phat
    gen ar = return_sp500 - phat
    egen car = sum(ar)
    gen tstat = car/(rmse*sqrt(_N))
    // keep in 1
end
runby all_events, by(event_id) status
                Here's a listing of the matching trades for the first 4 days (starting on the date of the event and going backwards in time) of the event window for the first event.
                Code:
                . list event low high dt return_sp500 in 1/29, sepby(high) noobs

  +----------------------------------------------------------------------------------------------+
  |              event                  low                 high                   dt   retu~500 |
  |----------------------------------------------------------------------------------------------|
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:28:58   .0663034 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:30:47   .6891089 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:37:49   .1541257 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:39:57   .0541458 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:51:06   .7804216 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:51:41   .0740838 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:53:07   .0855999 |
  | 15jan2017 11:58:28   15jan2017 11:28:28   15jan2017 11:58:28   15jan2017 11:55:24   .6165955 |
  |----------------------------------------------------------------------------------------------|
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:30:17   .3858864 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:37:45   .5157887 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:39:43   .9529741 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:43:55   .7284381 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:44:36   .3395688 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:51:18   .8591657 |
  | 15jan2017 11:58:28   14jan2017 11:28:28   14jan2017 11:58:28   14jan2017 11:55:15   .0813529 |
  |----------------------------------------------------------------------------------------------|
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:30:59   .8111063 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:33:09   .6940593 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:34:52   .4282138 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:39:25   .1364102 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:39:30   .9434168 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:44:46   .7852429 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:48:29   .8806806 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:57:56   .8374264 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:58:15    .291414 |
  | 15jan2017 11:58:28   13jan2017 11:28:28   13jan2017 11:58:28   13jan2017 11:58:27   .1253142 |
  |----------------------------------------------------------------------------------------------|
  | 15jan2017 11:58:28   12jan2017 11:28:28   12jan2017 11:58:28   12jan2017 11:29:06   .4130898 |
  | 15jan2017 11:58:28   12jan2017 11:28:28   12jan2017 11:58:28   12jan2017 11:34:38   .4248691 |
  | 15jan2017 11:58:28   12jan2017 11:28:28   12jan2017 11:58:28   12jan2017 11:44:08   .4997032 |
  | 15jan2017 11:58:28   12jan2017 11:28:28   12jan2017 11:58:28   12jan2017 11:52:32   .4671655 |
  +----------------------------------------------------------------------------------------------+

.
                • 1 like

                Comment

                • #9
                  Thank you very Clyde and Richard.

                  I've got an understanding on how to around doing what I require thanks to you guys.

                  I need to manipulate the code a bit though as:

                  For every event in my sample, I need to:

                  1. Define the estimation window by say calling it est_window=1 if the conditions are met ((in this case 10 past days & 30 minutes prior to event time meaning 300 observations in the estimation window) so I can calculate the normal return.
                  2. #6 simply means that I am defining an event window which spans from the event time to 15 minutes after the event time.

                  The object of analysis is then to look at abnormal returns - i.e. AR (calculated in the event window) - mean return over past 10 days (which will be given by the regression from the estimation window).

                  Your insights have been very helpful thus far.

                  Thank you.

                  P.S This is how my data looks like:

                  This is my event sample:
                  Code:
                  * Example generated by -dataex-. To install: ssc install dataex
clear
input double event_dt float(date time obs)
1491147720000 17258 56520000  1
1502366940000 17388 43740000  2
1503485940000 17401 39540000  3
1505041260000 17419 39660000  4
1509535860000 17471 41460000  5
1513074660000 17512 37860000  6
1515672420000 17542 43620000  7
1.5330396e+12 17743 4.44e+07  8
1537192980000 17791 50580000  9
1537282320000 17792 53520000 10
1.5374466e+12 17794 4.50e+07 11
1538915640000 17811 45240000 12
1538998620000 17812 41820000 13
1.5396012e+12 17819 3.96e+07 14
1.5397797e+12 17821 4.53e+07 15
1541934240000 17846 39840000 16
1.5439239e+12 17869 4.23e+07 17
1.5462621e+12 17896 4.77e+07 18
1.5477234e+12 17913 4.02e+07 19
1.5492924e+12 17931 5.40e+07 20
1.5499008e+12 17938 5.76e+07 21
1.5505863e+12 17946 5.19e+07 22
1551355320000 17955 43320000 23
1551611040000 17958 39840000 24
1552573860000 17969 52260000 25
1553429280000 17979 43680000 26
1.5546492e+12 17993 5.40e+07 27
1558538940000 18038 55740000 28
1.5601779e+12 18057 5.31e+07 29
1560862080000 18065 46080000 30
1561549320000 18073 42120000 31
1567092420000 18137 55620000 32
1.5685635e+12 18154 5.79e+07 33
 1.568907e+12 18158 5.58e+07 34
1575984360000 18240 48360000 35
end
format %tc event_dt
format %td date
format %tcHH:MM:SS time
                  And this is part of the financial data
                  Code:
                  * Example generated by -dataex-. To install: ssc install dataex
clear
input str4 underlying_symbol double dt float(date time return_sp500)
"^SPX" 1451899860000 16804 34260000              .
"^SPX" 1451899920000 16804 34320000    .0005744325
"^SPX" 1451899980000 16804 34380000   .00027911077
"^SPX" 1451900040000 16804 34440000   -.0003828115
"^SPX" 1.4519001e+12 16804 3.45e+07    .0000558926
"^SPX" 1451900160000 16804 34560000   -.0003430974
"^SPX" 1451900220000 16804 34620000  7.9871415e-06
"^SPX" 1451900280000 16804 34680000  -.00013569267
"^SPX" 1451900340000 16804 34740000   -.0003511525
"^SPX" 1.4519004e+12 16804 3.48e+07    .0002713648
"^SPX" 1451900460000 16804 34860000  -.00001597779
"^SPX" 1451900520000 16804 34920000   .00013570566
"^SPX" 1451900580000 16804 34980000   .00015954993
"^SPX" 1451900640000 16804 35040000  .000015973073
"^SPX" 1.4519007e+12 16804 3.51e+07    .0004707015
"^SPX" 1451900760000 16804 35160000  -.00022334624
"^SPX" 1451900820000 16804 35220000  -.00055858394
"^SPX" 1451900880000 16804 35280000  -.00017557766
"^SPX" 1451900940000 16804 35340000  .000071813236
"^SPX"  1.451901e+12 16804 3.54e+07   .00016767405
"^SPX" 1451901060000 16804 35460000     .000335069
"^SPX" 1451901120000 16804 35520000  -.00025528512
"^SPX" 1451901180000 16804 35580000   -.0003272616
"^SPX" 1451901240000 16804 35640000   -.0005828862
"^SPX" 1.4519013e+12 16804 3.57e+07  -.00021569553
"^SPX" 1451901360000 16804 35760000     .000135839
"^SPX" 1451901420000 16804 35820000   .00004787568
"^SPX" 1451901480000 16804 35880000   .00014380828
"^SPX" 1451901540000 16804 35940000    .0003752666
"^SPX" 1.4519016e+12 16804 3.60e+07   .00013574358
"^SPX" 1451901660000 16804 36060000   -.0003273556
"^SPX" 1451901720000 16804 36120000    .0006864562
"^SPX" 1451901780000 16804 36180000    .0003909086
"^SPX" 1451901840000 16804 36240000   -.0007899605
"^SPX" 1.4519019e+12 16804 3.63e+07   -.0005988766
"^SPX" 1451901960000 16804 36360000   -.0007350496
"^SPX" 1451902020000 16804 36420000   .00043947555
"^SPX" 1451902080000 16804 36480000  -.00008787966
"^SPX" 1451902140000 16804 36540000     -.00089533
"^SPX" 1.4519022e+12 16804 3.66e+07   -.0004879629
"^SPX" 1451902260000 16804 36660000     .000416009
"^SPX" 1451902320000 16804 36720000   -.0004560565
"^SPX" 1451902380000 16804 36780000  -.00048020715
"^SPX" 1451902440000 16804 36840000    .0002961631
"^SPX" 1.4519025e+12 16804 3.69e+07   .00011205206
"^SPX" 1451902560000 16804 36960000   -.0000880189
"^SPX" 1451902620000 16804 37020000  .000071998285
"^SPX" 1451902680000 16804 37080000 -.000071998285
"^SPX" 1451902740000 16804 37140000 -.000016022033
"^SPX" 1.4519028e+12 16804 3.72e+07    .0003840673
"^SPX" 1451902860000 16804 37260000  .000072069444
"^SPX" 1451902920000 16804 37320000 -.000032030224
"^SPX" 1451902980000 16804 37380000   -.0006402337
"^SPX" 1451903040000 16804 37440000   .00013611083
"^SPX" 1.4519031e+12 16804 3.75e+07   -.0007045958
"^SPX" 1451903160000 16804 37560000   .00002405182
"^SPX" 1451903220000 16804 37620000   -.0003525246
"^SPX" 1451903280000 16804 37680000  -.00009614685
"^SPX" 1451903340000 16804 37740000  -.00056904997
"^SPX" 1.4519034e+12 16804 3.78e+07   .00015227412
"^SPX" 1451903460000 16804 37860000    .0003366627
"^SPX" 1451903520000 16804 37920000  -.00024849916
"^SPX" 1451903580000 16804 37980000  -.00018435974
"^SPX" 1451903640000 16804 38040000  -.00013623667
"^SPX" 1.4519037e+12 16804 3.81e+07  -.00012832608
"^SPX" 1451903760000 16804 38160000  -.00012834255
"^SPX" 1451903820000 16804 38220000  -.00008821447
"^SPX" 1451903880000 16804 38280000   -.0004332548
"^SPX" 1451903940000 16804 38340000              0
"^SPX"  1.451904e+12 16804 3.84e+07   -.0003691589
"^SPX" 1451904060000 16804 38460000    .0003130297
"^SPX" 1451904120000 16804 38520000    .0005615423
"^SPX" 1451904180000 16804 38580000    .0000802785
"^SPX" 1451904240000 16804 38640000    .0002726051
"^SPX" 1.4519043e+12 16804 3.87e+07  .000032100845
"^SPX" 1451904360000 16804 38760000 -.000072228344
"^SPX" 1451904420000 16804 38820000   .00008015553
"^SPX" 1451904480000 16804 38880000   .00016841335
"^SPX" 1451904540000 16804 38940000    .0003206017
"^SPX" 1.4519046e+12 16804 3.90e+07   .00005604828
"^SPX" 1451904660000 16804 39060000  -.00004812503
"^SPX" 1451904720000 16804 39120000   .00013625044
"^SPX" 1451904780000 16804 39180000  -.00006406376
"^SPX" 1451904840000 16804 39240000   .00008812328
"^SPX" 1.4519049e+12 16804 3.93e+07  -.00017625434
"^SPX" 1451904960000 16804 39360000    .0003845504
"^SPX" 1451905020000 16804 39420000  .000016036023
"^SPX" 1451905080000 16804 39480000    -.000160274
"^SPX" 1451905140000 16804 39540000  -.00017624305
"^SPX" 1.4519052e+12 16804 3.96e+07   .00015218506
"^SPX" 1451905260000 16804 39660000    .0001041493
"^SPX" 1451905320000 16804 39720000  -.00008009132
"^SPX" 1451905380000 16804 39780000    .0002724212
"^SPX" 1451905440000 16804 39840000   7.919248e-06
"^SPX" 1.4519055e+12 16804 3.99e+07   -.0002322262
"^SPX" 1451905560000 16804 39960000   -.0002484195
"^SPX" 1451905620000 16804 40020000 -.000031986052
"^SPX" 1451905680000 16804 40080000              0
"^SPX" 1451905740000 16804 40140000  -.00009615994
"^SPX" 1.4519058e+12 16804 4.02e+07    .0005046633
end
format %tc dt
format %td date
format %tcHH:MM:SS time
                  And this how it should look at once the codes are implemented.
                  Code:
                  * Example generated by -dataex-. To install: ssc install dataex
clear
input double dt float(return evtime evt_window est_window)
1502021280000   -.0012641384 -1621 . 1
1502105940000   .00027302813 -1245 . 1
 1.502106e+12   6.830996e-06 -1244 . 1
1502106060000   .00017742295 -1243 . 1
1502106120000    .0007570672 -1242 . 1
1502106180000  -.00002721551 -1241 . 1
1502106240000   .00017043846 -1240 . 1
1.5021063e+12    .0004429376 -1239 . 1
1502106360000   .00007494054 -1238 . 1
1502106420000   .00049732643 -1237 . 1
1502106480000   .00032000075 -1236 . 1
1502106540000    .0005512416 -1235 . 1
1.5021066e+12   -.0002381556 -1234 . 1
1502106660000   .00004087343 -1233 . 1
1502106720000   -.0002927138 -1232 . 1
1502106780000  -.00010894832 -1231 . 1
1502106840000    .0005104837 -1230 . 1
1.5021069e+12    .0005986387 -1229 . 1
1502106960000  -.00009514042 -1228 . 1
1502107020000   .00014279026 -1227 . 1
1502107080000   .00023116077 -1226 . 1
1502107140000    -.000761746 -1225 . 1
1.5021072e+12    .0004694038 -1224 . 1
1502107260000    .0003127582 -1223 . 1
1502107320000 -.000034026798 -1222 . 1
1502107380000  -.00021074298 -1221 . 1
1502107440000  -.00010883347 -1220 . 1
1.5021075e+12    .0008090359 -1219 . 1
1502107560000    .0005977465 -1218 . 1
1502107620000    .0004753381 -1217 . 1
1502107680000  -.00023080295 -1216 . 1
1502192340000    .0006970888  -840 . 1
1.5021924e+12  -.00012057902  -839 . 1
1502192460000   .00026134524  -838 . 1
1502192520000 -.000026826663  -837 . 1
1502192580000  -.00026135225  -836 . 1
1502192640000   .00004025024  -835 . 1
1.5021927e+12   .00013399219  -834 . 1
1502192760000    -.000469133  -833 . 1
1502192820000  -.00019445937  -832 . 1
1502192880000   .00052289193  -831 . 1
1502192940000   .00003345905  -830 . 1
 1.502193e+12  -.00018088854  -829 . 1
1502193060000   -.0000938524  -828 . 1
1502193120000  -.00002012991  -827 . 1
1502193180000   .00012731894  -826 . 1
1502193240000   -.0003083427  -825 . 1
1.5021933e+12   -.0005231234  -824 . 1
1502193360000    .0001744049  -823 . 1
1502193420000   -.0003957709  -822 . 1
1502193480000   -.0003892087  -821 . 1
1502193540000    .0005435068  -820 . 1
1.5021936e+12   -.0001341495  -819 . 1
1502193660000    -.000516783  -818 . 1
1502193720000   -.0000335985  -817 . 1
1502193780000  -.00011407943  -816 . 1
1502193840000     .000161117  -815 . 1
1.5021939e+12   .00002679568  -814 . 1
1502193960000   -.0001745536  -813 . 1
1502194020000  .000033601882  -812 . 1
1502194080000 -.000013440618  -811 . 1
1502278740000   .00027023564  -435 . 1
1.5022788e+12   -.0005675215  -434 . 1
1502278860000   -.0006152267  -433 . 1
1502278920000     .000324537  -432 . 1
1502278980000    .0004528716  -431 . 1
1502279040000  -.00006755883  -430 . 1
1.5022791e+12   -.0004055314  -429 . 1
1502279160000   -.0003516211  -428 . 1
1502279220000   -.0006359558  -427 . 1
1502279280000    .0008590762  -426 . 1
1502279340000    -.000473375  -425 . 1
1.5022794e+12   -.0008865915  -424 . 1
1502279460000   -.0011991232  -423 . 1
1502279520000    .0005963507  -422 . 1
1502279580000    .0004470504  -421 . 1
1502279640000  -.00009481719  -420 . 1
1.5022797e+12    .0004874647  -419 . 1
1502279760000    -.000839698  -418 . 1
1502279820000    .0003184209  -417 . 1
1502279880000    .0002707971  -416 . 1
1502279940000    .0001083479  -415 . 1
  1.50228e+12    .0011164317  -414 . 1
1502280060000  .000027075555  -413 . 1
1502280120000   .00033805254  -412 . 1
1502280180000   -.0004936644  -411 . 1
1502280240000  -.00016232448  -410 . 1
1.5022803e+12   .00004062487  -409 . 1
1502280360000  -.00048042045  -408 . 1
1502280420000   -.0009478077  -407 . 1
1502280480000  -.00052851095  -406 . 1
1502366940000   -.0011296502     0 1 .
 1.502367e+12   .00008269651     1 1 .
1502367060000   -.0007514182     2 1 .
1502367120000     -.00086935     3 1 .
1502367180000  -.00020713166     4 1 .
1502367240000     .000841995     5 1 .
1.5023673e+12    -.000158657     6 1 .
1502367360000  -.00013804533     7 1 .
1502367420000    .0009517801     8 1 .
end
format %tc dt
                  Above has been manually generated (kind of confirming if my results are ok!).

                  And based on the above I can run #6.

                  But again thank you very much for the help. Seems I need some more reading and tiny bit of extra hard work and I'll eventually get there!

                  Comment

                  • #10
                    It's Robert and not Richard... and full disclosure, I'm not in finance so I can offer no insight as to the logic and methodology of what you are doing.

                    Seems like you did not quite grasp what my solution offered so I'll try again and expand it to include an "event window". I'll also make it more realistic by creating a demonstration dataset of high frequency data, with an observation per minute for each weekday in 2007 during market business hours (9:30am to 4pm). More realistic data raises the issue of business days. I'll assume that you want to look back for 10 trading days. In order to get there, you'll need to get familiar with Stata's business calendars.
                    Code:
                    clear
set seed 3213
set obs 365
gen d = mdy(1,1,2007) + _n - 1
format %td d
drop if inlist(dow(d), 0, 6)    // Sunday and Saturday
expand 390
bysort d: gen double dt = dhms(d, 9, 30, 0) + _n * msofminutes(1)
format %tc dt
sort dt
gen return_sp500 = runiform()

bcal create my_bcal, from(d) gen(bdate) replace
save "my_marketdata.dta", replace

gen d2 = d
gen bdate2 = bdate
listsome, max(10) random
                    and here's the listsome (from SSC) output:
                    Code:
                    . listsome, max(10) random

        +------------------------------------------------------------------------+
        |         d                   dt   retu~500       bdate      d2   bdate2 |
        |------------------------------------------------------------------------|
  9994. | 05feb2007   05feb2007 13:34:00   .5880489   05feb2007   17202       25 |
 22070. | 20mar2007   20mar2007 13:20:00    .751517   20mar2007   17245       56 |
 25294. | 30mar2007   30mar2007 15:04:00   .1888532   30mar2007   17255       64 |
 27194. | 06apr2007   06apr2007 14:14:00   .4529727   06apr2007   17262       69 |
 30115. | 18apr2007   18apr2007 10:55:00   .5728872   18apr2007   17274       77 |
        |------------------------------------------------------------------------|
 31433. | 23apr2007   23apr2007 13:23:00   .0161447   23apr2007   17279       80 |
 81518. | 19oct2007   19oct2007 09:38:00   .7806123   19oct2007   17458      209 |
 91278. | 23nov2007   23nov2007 09:48:00   .7125727   23nov2007   17493      234 |
 94635. | 05dec2007   05dec2007 13:45:00   .0509238   05dec2007   17505      242 |
 97097. | 13dec2007   13dec2007 15:47:00   .9238008   13dec2007   17513      248 |
        +------------------------------------------------------------------------+

.
                    I added copies of d and bdate to make it easy to see the underlying numerical values that Stata uses to store the same date, d2 for the calendar date and bdate2 for the business calendar date.

                    The following takes the 6 events in 2007 you posted in #9 and puts together the set of observations that fall within both the estimation and event window. Stata's business calendar dates are daily dates and do not include a time component so there's some extra code required to correctly target the proper time interval on the desired business dates.

                    Code:
                    clear all

* Example generated by -dataex-. To install: ssc install dataex
clear
input double event_dt float obs
1491147720000 1
1502366940000 2
1503485940000 3
1505041260000 4
1509535860000 5
1513074660000 6
end
format %tc event_dt

* business calendar date of the event
gen b_event =  bofd("my_bcal", dofc(event_dt))
format b_event %tbmy_bcal

* extract the time of the event
gen double time = event_dt - cofd(dofc(event_dt))
format time %tcHH:MM:SS

list


program all_events
    
    // generate observations over 10 business days that end on b_event
    expand 10
    gen bdays = b_event - _n + 1
    gen cdays = dofb(bdays, "my_bcal")
    format %td cdays

    gen double high = cofd(cdays) + time
    gen double low = high - msofminutes(30)
    
    // add 15 minutes to the 30 min window of the event day to pick up event window obs
    replace high = high + msofminutes(15) if high == event_dt
    format %tcDayDDmonCCYY_HH:MM:SS.sss low high
    
    rangejoin dt low high using "my_marketdata.dta"
    
    gen est_window = dt < event_dt
    gen evt_window = dt >= event_dt
end
runby all_events, by(obs) status

format dt %tcDayDDmonCCYY_HH:MM:SS.sss
list event_dt dt return_sp500 est_window evt_window if obs == 1, sepby(high)
                    Because of space constraints, the listing is truncated to the past 7 business days
                    Code:
                    . list event_dt dt return_sp500 est_window evt_window if obs == 1, sepby(high)

      +---------------------------------------------------------------------------------+
      |           event_dt                          dt   retu~500   est_wi~w   evt_wi~w |
      |---------------------------------------------------------------------------------|
   1. | 02apr2007 15:42:00   Mon02apr2007 15:12:00.000   .4332957          1          0 |
   2. | 02apr2007 15:42:00   Mon02apr2007 15:13:00.000   .2863628          1          0 |
   3. | 02apr2007 15:42:00   Mon02apr2007 15:14:00.000   .0169784          1          0 |
   4. | 02apr2007 15:42:00   Mon02apr2007 15:15:00.000   .2147206          1          0 |
   5. | 02apr2007 15:42:00   Mon02apr2007 15:16:00.000   .4609037          1          0 |
   6. | 02apr2007 15:42:00   Mon02apr2007 15:17:00.000    .779937          1          0 |
   7. | 02apr2007 15:42:00   Mon02apr2007 15:18:00.000   .1181169          1          0 |
   8. | 02apr2007 15:42:00   Mon02apr2007 15:19:00.000   .0801838          1          0 |
   9. | 02apr2007 15:42:00   Mon02apr2007 15:20:00.000   .7031575          1          0 |
  10. | 02apr2007 15:42:00   Mon02apr2007 15:21:00.000   .2617211          1          0 |
  11. | 02apr2007 15:42:00   Mon02apr2007 15:22:00.000   .7653649          1          0 |
  12. | 02apr2007 15:42:00   Mon02apr2007 15:23:00.000   .4866744          1          0 |
  13. | 02apr2007 15:42:00   Mon02apr2007 15:24:00.000   .7519349          1          0 |
  14. | 02apr2007 15:42:00   Mon02apr2007 15:25:00.000   .9965692          1          0 |
  15. | 02apr2007 15:42:00   Mon02apr2007 15:26:00.000   .5020336          1          0 |
  16. | 02apr2007 15:42:00   Mon02apr2007 15:27:00.000   .8013653          1          0 |
  17. | 02apr2007 15:42:00   Mon02apr2007 15:28:00.000   .0741498          1          0 |
  18. | 02apr2007 15:42:00   Mon02apr2007 15:29:00.000   .7153957          1          0 |
  19. | 02apr2007 15:42:00   Mon02apr2007 15:30:00.000   .9920664          1          0 |
  20. | 02apr2007 15:42:00   Mon02apr2007 15:31:00.000   .5204668          1          0 |
  21. | 02apr2007 15:42:00   Mon02apr2007 15:32:00.000   .3878596          1          0 |
  22. | 02apr2007 15:42:00   Mon02apr2007 15:33:00.000   .9941716          1          0 |
  23. | 02apr2007 15:42:00   Mon02apr2007 15:34:00.000   .0953395          1          0 |
  24. | 02apr2007 15:42:00   Mon02apr2007 15:35:00.000   .8181406          1          0 |
  25. | 02apr2007 15:42:00   Mon02apr2007 15:36:00.000   .3934459          1          0 |
  26. | 02apr2007 15:42:00   Mon02apr2007 15:37:00.000    .413705          1          0 |
  27. | 02apr2007 15:42:00   Mon02apr2007 15:38:00.000   .4701866          1          0 |
  28. | 02apr2007 15:42:00   Mon02apr2007 15:39:00.000   .1536132          1          0 |
  29. | 02apr2007 15:42:00   Mon02apr2007 15:40:00.000    .938244          1          0 |
  30. | 02apr2007 15:42:00   Mon02apr2007 15:41:00.000   .1070662          1          0 |
  31. | 02apr2007 15:42:00   Mon02apr2007 15:42:00.000   .6786414          0          1 |
  32. | 02apr2007 15:42:00   Mon02apr2007 15:43:00.000   .3799079          0          1 |
  33. | 02apr2007 15:42:00   Mon02apr2007 15:44:00.000   .0967615          0          1 |
  34. | 02apr2007 15:42:00   Mon02apr2007 15:45:00.000    .132361          0          1 |
  35. | 02apr2007 15:42:00   Mon02apr2007 15:46:00.000   .6512914          0          1 |
  36. | 02apr2007 15:42:00   Mon02apr2007 15:47:00.000   .9660878          0          1 |
  37. | 02apr2007 15:42:00   Mon02apr2007 15:48:00.000   .4622979          0          1 |
  38. | 02apr2007 15:42:00   Mon02apr2007 15:49:00.000   .3655838          0          1 |
  39. | 02apr2007 15:42:00   Mon02apr2007 15:50:00.000   .2335329          0          1 |
  40. | 02apr2007 15:42:00   Mon02apr2007 15:51:00.000   .5086564          0          1 |
  41. | 02apr2007 15:42:00   Mon02apr2007 15:52:00.000   .9557813          0          1 |
  42. | 02apr2007 15:42:00   Mon02apr2007 15:53:00.000   .4614463          0          1 |
  43. | 02apr2007 15:42:00   Mon02apr2007 15:54:00.000    .219903          0          1 |
  44. | 02apr2007 15:42:00   Mon02apr2007 15:55:00.000   .5497924          0          1 |
  45. | 02apr2007 15:42:00   Mon02apr2007 15:56:00.000   .4359198          0          1 |
  46. | 02apr2007 15:42:00   Mon02apr2007 15:57:00.000   .5518639          0          1 |
      |---------------------------------------------------------------------------------|
  47. | 02apr2007 15:42:00   Fri30mar2007 15:12:00.000   .0675282          1          0 |
  48. | 02apr2007 15:42:00   Fri30mar2007 15:13:00.000   .8588795          1          0 |
  49. | 02apr2007 15:42:00   Fri30mar2007 15:14:00.000   .4189462          1          0 |
  50. | 02apr2007 15:42:00   Fri30mar2007 15:15:00.000   .9746378          1          0 |
  51. | 02apr2007 15:42:00   Fri30mar2007 15:16:00.000   .1768937          1          0 |
  52. | 02apr2007 15:42:00   Fri30mar2007 15:17:00.000   .3915992          1          0 |
  53. | 02apr2007 15:42:00   Fri30mar2007 15:18:00.000    .083911          1          0 |
  54. | 02apr2007 15:42:00   Fri30mar2007 15:19:00.000   .4795009          1          0 |
  55. | 02apr2007 15:42:00   Fri30mar2007 15:20:00.000   .8899888          1          0 |
  56. | 02apr2007 15:42:00   Fri30mar2007 15:21:00.000   .2554915          1          0 |
  57. | 02apr2007 15:42:00   Fri30mar2007 15:22:00.000   .5082894          1          0 |
  58. | 02apr2007 15:42:00   Fri30mar2007 15:23:00.000   .9733915          1          0 |
  59. | 02apr2007 15:42:00   Fri30mar2007 15:24:00.000   .4625504          1          0 |
  60. | 02apr2007 15:42:00   Fri30mar2007 15:25:00.000   .4786708          1          0 |
  61. | 02apr2007 15:42:00   Fri30mar2007 15:26:00.000   .4981335          1          0 |
  62. | 02apr2007 15:42:00   Fri30mar2007 15:27:00.000   .5651129          1          0 |
  63. | 02apr2007 15:42:00   Fri30mar2007 15:28:00.000    .321823          1          0 |
  64. | 02apr2007 15:42:00   Fri30mar2007 15:29:00.000   .9179403          1          0 |
  65. | 02apr2007 15:42:00   Fri30mar2007 15:30:00.000   .0224005          1          0 |
  66. | 02apr2007 15:42:00   Fri30mar2007 15:31:00.000   .8147572          1          0 |
  67. | 02apr2007 15:42:00   Fri30mar2007 15:32:00.000   .9607509          1          0 |
  68. | 02apr2007 15:42:00   Fri30mar2007 15:33:00.000   .6002659          1          0 |
  69. | 02apr2007 15:42:00   Fri30mar2007 15:34:00.000   .7037848          1          0 |
  70. | 02apr2007 15:42:00   Fri30mar2007 15:35:00.000   .0261053          1          0 |
  71. | 02apr2007 15:42:00   Fri30mar2007 15:36:00.000   .6422778          1          0 |
  72. | 02apr2007 15:42:00   Fri30mar2007 15:37:00.000   .0469407          1          0 |
  73. | 02apr2007 15:42:00   Fri30mar2007 15:38:00.000   .9186565          1          0 |
  74. | 02apr2007 15:42:00   Fri30mar2007 15:39:00.000   .5419573          1          0 |
  75. | 02apr2007 15:42:00   Fri30mar2007 15:40:00.000   .2286165          1          0 |
  76. | 02apr2007 15:42:00   Fri30mar2007 15:41:00.000   .5105467          1          0 |
  77. | 02apr2007 15:42:00   Fri30mar2007 15:42:00.000   .3558726          1          0 |
      |---------------------------------------------------------------------------------|
  78. | 02apr2007 15:42:00   Thu29mar2007 15:12:00.000   .5175249          1          0 |
  79. | 02apr2007 15:42:00   Thu29mar2007 15:13:00.000   .5768422          1          0 |
  80. | 02apr2007 15:42:00   Thu29mar2007 15:14:00.000   .3144721          1          0 |
  81. | 02apr2007 15:42:00   Thu29mar2007 15:15:00.000   .7948087          1          0 |
  82. | 02apr2007 15:42:00   Thu29mar2007 15:16:00.000   .3959469          1          0 |
  83. | 02apr2007 15:42:00   Thu29mar2007 15:17:00.000   .2717375          1          0 |
  84. | 02apr2007 15:42:00   Thu29mar2007 15:18:00.000   .3227955          1          0 |
  85. | 02apr2007 15:42:00   Thu29mar2007 15:19:00.000   .9124011          1          0 |
  86. | 02apr2007 15:42:00   Thu29mar2007 15:20:00.000   .3376218          1          0 |
  87. | 02apr2007 15:42:00   Thu29mar2007 15:21:00.000   .8801539          1          0 |
  88. | 02apr2007 15:42:00   Thu29mar2007 15:22:00.000   .4301346          1          0 |
  89. | 02apr2007 15:42:00   Thu29mar2007 15:23:00.000   .1315556          1          0 |
  90. | 02apr2007 15:42:00   Thu29mar2007 15:24:00.000   .0245389          1          0 |
  91. | 02apr2007 15:42:00   Thu29mar2007 15:25:00.000    .850767          1          0 |
  92. | 02apr2007 15:42:00   Thu29mar2007 15:26:00.000   .3958901          1          0 |
  93. | 02apr2007 15:42:00   Thu29mar2007 15:27:00.000   .1567455          1          0 |
  94. | 02apr2007 15:42:00   Thu29mar2007 15:28:00.000   .0845687          1          0 |
  95. | 02apr2007 15:42:00   Thu29mar2007 15:29:00.000   .5168117          1          0 |
  96. | 02apr2007 15:42:00   Thu29mar2007 15:30:00.000   .7555894          1          0 |
  97. | 02apr2007 15:42:00   Thu29mar2007 15:31:00.000   .3527876          1          0 |
  98. | 02apr2007 15:42:00   Thu29mar2007 15:32:00.000   .5656487          1          0 |
  99. | 02apr2007 15:42:00   Thu29mar2007 15:33:00.000   .2400037          1          0 |
 100. | 02apr2007 15:42:00   Thu29mar2007 15:34:00.000   .3205103          1          0 |
 101. | 02apr2007 15:42:00   Thu29mar2007 15:35:00.000   .6836029          1          0 |
 102. | 02apr2007 15:42:00   Thu29mar2007 15:36:00.000   .9071024          1          0 |
 103. | 02apr2007 15:42:00   Thu29mar2007 15:37:00.000   .9122951          1          0 |
 104. | 02apr2007 15:42:00   Thu29mar2007 15:38:00.000   .2106421          1          0 |
 105. | 02apr2007 15:42:00   Thu29mar2007 15:39:00.000   .0939973          1          0 |
 106. | 02apr2007 15:42:00   Thu29mar2007 15:40:00.000   .3313866          1          0 |
 107. | 02apr2007 15:42:00   Thu29mar2007 15:41:00.000   .5474067          1          0 |
 108. | 02apr2007 15:42:00   Thu29mar2007 15:42:00.000   .5392137          1          0 |
      |---------------------------------------------------------------------------------|
 109. | 02apr2007 15:42:00   Wed28mar2007 15:12:00.000   .4469242          1          0 |
 110. | 02apr2007 15:42:00   Wed28mar2007 15:13:00.000   .9076935          1          0 |
 111. | 02apr2007 15:42:00   Wed28mar2007 15:14:00.000   .0954431          1          0 |
 112. | 02apr2007 15:42:00   Wed28mar2007 15:15:00.000   .0321961          1          0 |
 113. | 02apr2007 15:42:00   Wed28mar2007 15:16:00.000   .6920508          1          0 |
 114. | 02apr2007 15:42:00   Wed28mar2007 15:17:00.000   .7131269          1          0 |
 115. | 02apr2007 15:42:00   Wed28mar2007 15:18:00.000   .2521129          1          0 |
 116. | 02apr2007 15:42:00   Wed28mar2007 15:19:00.000   .4334252          1          0 |
 117. | 02apr2007 15:42:00   Wed28mar2007 15:20:00.000   .2874857          1          0 |
 118. | 02apr2007 15:42:00   Wed28mar2007 15:21:00.000    .241889          1          0 |
 119. | 02apr2007 15:42:00   Wed28mar2007 15:22:00.000   .2683178          1          0 |
 120. | 02apr2007 15:42:00   Wed28mar2007 15:23:00.000   .1423557          1          0 |
 121. | 02apr2007 15:42:00   Wed28mar2007 15:24:00.000   .5091652          1          0 |
 122. | 02apr2007 15:42:00   Wed28mar2007 15:25:00.000   .7423305          1          0 |
 123. | 02apr2007 15:42:00   Wed28mar2007 15:26:00.000   .6606768          1          0 |
 124. | 02apr2007 15:42:00   Wed28mar2007 15:27:00.000   .7560758          1          0 |
 125. | 02apr2007 15:42:00   Wed28mar2007 15:28:00.000     .74305          1          0 |
 126. | 02apr2007 15:42:00   Wed28mar2007 15:29:00.000   .2005536          1          0 |
 127. | 02apr2007 15:42:00   Wed28mar2007 15:30:00.000   .4268954          1          0 |
 128. | 02apr2007 15:42:00   Wed28mar2007 15:31:00.000   .8789054          1          0 |
 129. | 02apr2007 15:42:00   Wed28mar2007 15:32:00.000   .3246821          1          0 |
 130. | 02apr2007 15:42:00   Wed28mar2007 15:33:00.000   .7500755          1          0 |
 131. | 02apr2007 15:42:00   Wed28mar2007 15:34:00.000    .054805          1          0 |
 132. | 02apr2007 15:42:00   Wed28mar2007 15:35:00.000   .9684947          1          0 |
 133. | 02apr2007 15:42:00   Wed28mar2007 15:36:00.000   .0623225          1          0 |
 134. | 02apr2007 15:42:00   Wed28mar2007 15:37:00.000    .876208          1          0 |
 135. | 02apr2007 15:42:00   Wed28mar2007 15:38:00.000   .2665892          1          0 |
 136. | 02apr2007 15:42:00   Wed28mar2007 15:39:00.000   .8304271          1          0 |
 137. | 02apr2007 15:42:00   Wed28mar2007 15:40:00.000   .7883704          1          0 |
 138. | 02apr2007 15:42:00   Wed28mar2007 15:41:00.000   .4200024          1          0 |
 139. | 02apr2007 15:42:00   Wed28mar2007 15:42:00.000   .1581607          1          0 |
      |---------------------------------------------------------------------------------|
 140. | 02apr2007 15:42:00   Tue27mar2007 15:12:00.000    .389244          1          0 |
 141. | 02apr2007 15:42:00   Tue27mar2007 15:13:00.000   .1241376          1          0 |
 142. | 02apr2007 15:42:00   Tue27mar2007 15:14:00.000   .4663013          1          0 |
 143. | 02apr2007 15:42:00   Tue27mar2007 15:15:00.000   .9052405          1          0 |
 144. | 02apr2007 15:42:00   Tue27mar2007 15:16:00.000   .3817019          1          0 |
 145. | 02apr2007 15:42:00   Tue27mar2007 15:17:00.000   .9866372          1          0 |
 146. | 02apr2007 15:42:00   Tue27mar2007 15:18:00.000   .3407746          1          0 |
 147. | 02apr2007 15:42:00   Tue27mar2007 15:19:00.000   .3895895          1          0 |
 148. | 02apr2007 15:42:00   Tue27mar2007 15:20:00.000   .3065744          1          0 |
 149. | 02apr2007 15:42:00   Tue27mar2007 15:21:00.000   .7030567          1          0 |
 150. | 02apr2007 15:42:00   Tue27mar2007 15:22:00.000   .8251919          1          0 |
 151. | 02apr2007 15:42:00   Tue27mar2007 15:23:00.000   .0486013          1          0 |
 152. | 02apr2007 15:42:00   Tue27mar2007 15:24:00.000   .0492725          1          0 |
 153. | 02apr2007 15:42:00   Tue27mar2007 15:25:00.000    .082111          1          0 |
 154. | 02apr2007 15:42:00   Tue27mar2007 15:26:00.000   .4665299          1          0 |
 155. | 02apr2007 15:42:00   Tue27mar2007 15:27:00.000    .957064          1          0 |
 156. | 02apr2007 15:42:00   Tue27mar2007 15:28:00.000    .564221          1          0 |
 157. | 02apr2007 15:42:00   Tue27mar2007 15:29:00.000    .154488          1          0 |
 158. | 02apr2007 15:42:00   Tue27mar2007 15:30:00.000    .642902          1          0 |
 159. | 02apr2007 15:42:00   Tue27mar2007 15:31:00.000   .2011774          1          0 |
 160. | 02apr2007 15:42:00   Tue27mar2007 15:32:00.000   .2645048          1          0 |
 161. | 02apr2007 15:42:00   Tue27mar2007 15:33:00.000   .3406003          1          0 |
 162. | 02apr2007 15:42:00   Tue27mar2007 15:34:00.000   .8967028          1          0 |
 163. | 02apr2007 15:42:00   Tue27mar2007 15:35:00.000   .7896674          1          0 |
 164. | 02apr2007 15:42:00   Tue27mar2007 15:36:00.000   .3340228          1          0 |
 165. | 02apr2007 15:42:00   Tue27mar2007 15:37:00.000   .6377007          1          0 |
 166. | 02apr2007 15:42:00   Tue27mar2007 15:38:00.000   .3028997          1          0 |
 167. | 02apr2007 15:42:00   Tue27mar2007 15:39:00.000   .6299675          1          0 |
 168. | 02apr2007 15:42:00   Tue27mar2007 15:40:00.000   .1422045          1          0 |
 169. | 02apr2007 15:42:00   Tue27mar2007 15:41:00.000   .2288398          1          0 |
 170. | 02apr2007 15:42:00   Tue27mar2007 15:42:00.000   .4508187          1          0 |
      |---------------------------------------------------------------------------------|
 171. | 02apr2007 15:42:00   Mon26mar2007 15:12:00.000   .8943223          1          0 |
 172. | 02apr2007 15:42:00   Mon26mar2007 15:13:00.000   .0641244          1          0 |
 173. | 02apr2007 15:42:00   Mon26mar2007 15:14:00.000   .6051881          1          0 |
 174. | 02apr2007 15:42:00   Mon26mar2007 15:15:00.000   .9089748          1          0 |
 175. | 02apr2007 15:42:00   Mon26mar2007 15:16:00.000   .5808921          1          0 |
 176. | 02apr2007 15:42:00   Mon26mar2007 15:17:00.000   .2836961          1          0 |
 177. | 02apr2007 15:42:00   Mon26mar2007 15:18:00.000   .0095001          1          0 |
 178. | 02apr2007 15:42:00   Mon26mar2007 15:19:00.000   .4633252          1          0 |
 179. | 02apr2007 15:42:00   Mon26mar2007 15:20:00.000   .8376557          1          0 |
 180. | 02apr2007 15:42:00   Mon26mar2007 15:21:00.000    .854659          1          0 |
 181. | 02apr2007 15:42:00   Mon26mar2007 15:22:00.000   .3980419          1          0 |
 182. | 02apr2007 15:42:00   Mon26mar2007 15:23:00.000   .5419332          1          0 |
 183. | 02apr2007 15:42:00   Mon26mar2007 15:24:00.000   .5328223          1          0 |
 184. | 02apr2007 15:42:00   Mon26mar2007 15:25:00.000   .8739595          1          0 |
 185. | 02apr2007 15:42:00   Mon26mar2007 15:26:00.000   .2691363          1          0 |
 186. | 02apr2007 15:42:00   Mon26mar2007 15:27:00.000   .2565786          1          0 |
 187. | 02apr2007 15:42:00   Mon26mar2007 15:28:00.000   .2261589          1          0 |
 188. | 02apr2007 15:42:00   Mon26mar2007 15:29:00.000   .2380836          1          0 |
 189. | 02apr2007 15:42:00   Mon26mar2007 15:30:00.000   .9850891          1          0 |
 190. | 02apr2007 15:42:00   Mon26mar2007 15:31:00.000   .7931126          1          0 |
 191. | 02apr2007 15:42:00   Mon26mar2007 15:32:00.000    .965271          1          0 |
 192. | 02apr2007 15:42:00   Mon26mar2007 15:33:00.000   .8004449          1          0 |
 193. | 02apr2007 15:42:00   Mon26mar2007 15:34:00.000   .2967338          1          0 |
 194. | 02apr2007 15:42:00   Mon26mar2007 15:35:00.000   .7850129          1          0 |
 195. | 02apr2007 15:42:00   Mon26mar2007 15:36:00.000   .6245311          1          0 |
 196. | 02apr2007 15:42:00   Mon26mar2007 15:37:00.000   .8337967          1          0 |
 197. | 02apr2007 15:42:00   Mon26mar2007 15:38:00.000    .872963          1          0 |
 198. | 02apr2007 15:42:00   Mon26mar2007 15:39:00.000   .3782791          1          0 |
 199. | 02apr2007 15:42:00   Mon26mar2007 15:40:00.000   .6470609          1          0 |
 200. | 02apr2007 15:42:00   Mon26mar2007 15:41:00.000   .3265411          1          0 |
 201. | 02apr2007 15:42:00   Mon26mar2007 15:42:00.000   .6107325          1          0 |
      |---------------------------------------------------------------------------------|
 202. | 02apr2007 15:42:00   Fri23mar2007 15:12:00.000   .3087666          1          0 |
 203. | 02apr2007 15:42:00   Fri23mar2007 15:13:00.000   .2990933          1          0 |
 204. | 02apr2007 15:42:00   Fri23mar2007 15:14:00.000    .047203          1          0 |
 205. | 02apr2007 15:42:00   Fri23mar2007 15:15:00.000   .8949759          1          0 |
 206. | 02apr2007 15:42:00   Fri23mar2007 15:16:00.000    .659057          1          0 |
 207. | 02apr2007 15:42:00   Fri23mar2007 15:17:00.000   .3476671          1          0 |
 208. | 02apr2007 15:42:00   Fri23mar2007 15:18:00.000   .3471882          1          0 |
 209. | 02apr2007 15:42:00   Fri23mar2007 15:19:00.000   .3612553          1          0 |
 210. | 02apr2007 15:42:00   Fri23mar2007 15:20:00.000   .3561181          1          0 |
 211. | 02apr2007 15:42:00   Fri23mar2007 15:21:00.000   .9450113          1          0 |
 212. | 02apr2007 15:42:00   Fri23mar2007 15:22:00.000   .2233463          1          0 |
 213. | 02apr2007 15:42:00   Fri23mar2007 15:23:00.000   .9979317          1          0 |
 214. | 02apr2007 15:42:00   Fri23mar2007 15:24:00.000   .4313433          1          0 |
 215. | 02apr2007 15:42:00   Fri23mar2007 15:25:00.000   .6824942          1          0 |
 216. | 02apr2007 15:42:00   Fri23mar2007 15:26:00.000   .8989304          1          0 |
 217. | 02apr2007 15:42:00   Fri23mar2007 15:27:00.000   .6599994          1          0 |
 218. | 02apr2007 15:42:00   Fri23mar2007 15:28:00.000   .7056679          1          0 |
 219. | 02apr2007 15:42:00   Fri23mar2007 15:29:00.000   .3101844          1          0 |
 220. | 02apr2007 15:42:00   Fri23mar2007 15:30:00.000   .6318666          1          0 |
 221. | 02apr2007 15:42:00   Fri23mar2007 15:31:00.000   .9663602          1          0 |
 222. | 02apr2007 15:42:00   Fri23mar2007 15:32:00.000   .4799707          1          0 |
 223. | 02apr2007 15:42:00   Fri23mar2007 15:33:00.000    .692157          1          0 |
 224. | 02apr2007 15:42:00   Fri23mar2007 15:34:00.000   .6886501          1          0 |
 225. | 02apr2007 15:42:00   Fri23mar2007 15:35:00.000   .8452044          1          0 |
 226. | 02apr2007 15:42:00   Fri23mar2007 15:36:00.000   .1700923          1          0 |
 227. | 02apr2007 15:42:00   Fri23mar2007 15:37:00.000   .8372194          1          0 |
 228. | 02apr2007 15:42:00   Fri23mar2007 15:38:00.000   .0079373          1          0 |
 229. | 02apr2007 15:42:00   Fri23mar2007 15:39:00.000   .7851374          1          0 |
 230. | 02apr2007 15:42:00   Fri23mar2007 15:40:00.000    .815998          1          0 |
 231. | 02apr2007 15:42:00   Fri23mar2007 15:41:00.000     .82082          1          0 |
 232. | 02apr2007 15:42:00   Fri23mar2007 15:42:00.000   .1021684          1          0 |
      +---------------------------------------------------------------------------------+

                    Comment

                    • #11
                      It just dawned on me that it would be more efficient to extract the observations within the desired window for each event in one pass. I think this is equivalent to the code in my last post.

                      Code:
                      * Example generated by -dataex-. To install: ssc install dataex
clear
input double event_dt float obs
1491147720000 1
1502366940000 2
1503485940000 3
1505041260000 4
1509535860000 5
1513074660000 6
end
format %tc event_dt

* business calendar date of the event
gen b_event =  bofd("my_bcal", dofc(event_dt))
format b_event %tbmy_bcal

* extract the time of the event
gen double time = event_dt - cofd(dofc(event_dt))
format time %tcHH:MM:SS

* generate 10 observations per event, to target days in the window
expand 10
bysort obs: gen day_count = _n - 1
bysort obs: gen bdays = b_event - day_count
gen cdays = dofb(bdays, "my_bcal")
format %td cdays

* the interval bounds to use to select trading data by calendar date and time
gen double high = cofd(cdays) + time
gen double low = high - msofminutes(30)
// add 15 minutes to the 30 min window of the event day to pick up event window obs
replace high = high + msofminutes(15) if high == event_dt
format %tcDayDDmonCCYY_HH:MM:SS.sss low high

list obs event_dt day_count low high, sepby(obs)

* get the trading data
rangejoin dt low high using "my_marketdata.dta"

* indicator for estimation and event window
gen est_window = dt < event_dt
gen evt_window = dt >= event_dt

format dt %tcDayDDmonCCYY_HH:MM:SS.sss
list event_dt dt return_sp500 est_window evt_window if obs == 1, sepby(high)
                      and here's the listing of all interval bounds used for each event (keep in mind that the high bound on the day of the event overshoots by 15 min to capture observations in the "event" window):
                      Code:
                      . list obs event_dt day_count low high, sepby(obs)

     +---------------------------------------------------------------------------------------------+
     | obs             event_dt   day_co~t                         low                        high |
     |---------------------------------------------------------------------------------------------|
  1. |   1   02apr2007 15:42:00          0   Mon02apr2007 15:12:00.000   Mon02apr2007 15:57:00.000 |
  2. |   1   02apr2007 15:42:00          1   Fri30mar2007 15:12:00.000   Fri30mar2007 15:42:00.000 |
  3. |   1   02apr2007 15:42:00          2   Thu29mar2007 15:12:00.000   Thu29mar2007 15:42:00.000 |
  4. |   1   02apr2007 15:42:00          3   Wed28mar2007 15:12:00.000   Wed28mar2007 15:42:00.000 |
  5. |   1   02apr2007 15:42:00          4   Tue27mar2007 15:12:00.000   Tue27mar2007 15:42:00.000 |
  6. |   1   02apr2007 15:42:00          5   Mon26mar2007 15:12:00.000   Mon26mar2007 15:42:00.000 |
  7. |   1   02apr2007 15:42:00          6   Fri23mar2007 15:12:00.000   Fri23mar2007 15:42:00.000 |
  8. |   1   02apr2007 15:42:00          7   Thu22mar2007 15:12:00.000   Thu22mar2007 15:42:00.000 |
  9. |   1   02apr2007 15:42:00          8   Wed21mar2007 15:12:00.000   Wed21mar2007 15:42:00.000 |
 10. |   1   02apr2007 15:42:00          9   Tue20mar2007 15:12:00.000   Tue20mar2007 15:42:00.000 |
     |---------------------------------------------------------------------------------------------|
 11. |   2   10aug2007 12:09:00          0   Fri10aug2007 11:39:00.000   Fri10aug2007 12:24:00.000 |
 12. |   2   10aug2007 12:09:00          1   Thu09aug2007 11:39:00.000   Thu09aug2007 12:09:00.000 |
 13. |   2   10aug2007 12:09:00          2   Wed08aug2007 11:39:00.000   Wed08aug2007 12:09:00.000 |
 14. |   2   10aug2007 12:09:00          3   Tue07aug2007 11:39:00.000   Tue07aug2007 12:09:00.000 |
 15. |   2   10aug2007 12:09:00          4   Mon06aug2007 11:39:00.000   Mon06aug2007 12:09:00.000 |
 16. |   2   10aug2007 12:09:00          5   Fri03aug2007 11:39:00.000   Fri03aug2007 12:09:00.000 |
 17. |   2   10aug2007 12:09:00          6   Thu02aug2007 11:39:00.000   Thu02aug2007 12:09:00.000 |
 18. |   2   10aug2007 12:09:00          7   Wed01aug2007 11:39:00.000   Wed01aug2007 12:09:00.000 |
 19. |   2   10aug2007 12:09:00          8   Tue31jul2007 11:39:00.000   Tue31jul2007 12:09:00.000 |
 20. |   2   10aug2007 12:09:00          9   Mon30jul2007 11:39:00.000   Mon30jul2007 12:09:00.000 |
     |---------------------------------------------------------------------------------------------|
 21. |   3   23aug2007 10:59:00          0   Thu23aug2007 10:29:00.000   Thu23aug2007 11:14:00.000 |
 22. |   3   23aug2007 10:59:00          1   Wed22aug2007 10:29:00.000   Wed22aug2007 10:59:00.000 |
 23. |   3   23aug2007 10:59:00          2   Tue21aug2007 10:29:00.000   Tue21aug2007 10:59:00.000 |
 24. |   3   23aug2007 10:59:00          3   Mon20aug2007 10:29:00.000   Mon20aug2007 10:59:00.000 |
 25. |   3   23aug2007 10:59:00          4   Fri17aug2007 10:29:00.000   Fri17aug2007 10:59:00.000 |
 26. |   3   23aug2007 10:59:00          5   Thu16aug2007 10:29:00.000   Thu16aug2007 10:59:00.000 |
 27. |   3   23aug2007 10:59:00          6   Wed15aug2007 10:29:00.000   Wed15aug2007 10:59:00.000 |
 28. |   3   23aug2007 10:59:00          7   Tue14aug2007 10:29:00.000   Tue14aug2007 10:59:00.000 |
 29. |   3   23aug2007 10:59:00          8   Mon13aug2007 10:29:00.000   Mon13aug2007 10:59:00.000 |
 30. |   3   23aug2007 10:59:00          9   Fri10aug2007 10:29:00.000   Fri10aug2007 10:59:00.000 |
     |---------------------------------------------------------------------------------------------|
 31. |   4   10sep2007 11:01:00          0   Mon10sep2007 10:31:00.000   Mon10sep2007 11:16:00.000 |
 32. |   4   10sep2007 11:01:00          1   Fri07sep2007 10:31:00.000   Fri07sep2007 11:01:00.000 |
 33. |   4   10sep2007 11:01:00          2   Thu06sep2007 10:31:00.000   Thu06sep2007 11:01:00.000 |
 34. |   4   10sep2007 11:01:00          3   Wed05sep2007 10:31:00.000   Wed05sep2007 11:01:00.000 |
 35. |   4   10sep2007 11:01:00          4   Tue04sep2007 10:31:00.000   Tue04sep2007 11:01:00.000 |
 36. |   4   10sep2007 11:01:00          5   Mon03sep2007 10:31:00.000   Mon03sep2007 11:01:00.000 |
 37. |   4   10sep2007 11:01:00          6   Fri31aug2007 10:31:00.000   Fri31aug2007 11:01:00.000 |
 38. |   4   10sep2007 11:01:00          7   Thu30aug2007 10:31:00.000   Thu30aug2007 11:01:00.000 |
 39. |   4   10sep2007 11:01:00          8   Wed29aug2007 10:31:00.000   Wed29aug2007 11:01:00.000 |
 40. |   4   10sep2007 11:01:00          9   Tue28aug2007 10:31:00.000   Tue28aug2007 11:01:00.000 |
     |---------------------------------------------------------------------------------------------|
 41. |   5   01nov2007 11:31:00          0   Thu01nov2007 11:01:00.000   Thu01nov2007 11:46:00.000 |
 42. |   5   01nov2007 11:31:00          1   Wed31oct2007 11:01:00.000   Wed31oct2007 11:31:00.000 |
 43. |   5   01nov2007 11:31:00          2   Tue30oct2007 11:01:00.000   Tue30oct2007 11:31:00.000 |
 44. |   5   01nov2007 11:31:00          3   Mon29oct2007 11:01:00.000   Mon29oct2007 11:31:00.000 |
 45. |   5   01nov2007 11:31:00          4   Fri26oct2007 11:01:00.000   Fri26oct2007 11:31:00.000 |
 46. |   5   01nov2007 11:31:00          5   Thu25oct2007 11:01:00.000   Thu25oct2007 11:31:00.000 |
 47. |   5   01nov2007 11:31:00          6   Wed24oct2007 11:01:00.000   Wed24oct2007 11:31:00.000 |
 48. |   5   01nov2007 11:31:00          7   Tue23oct2007 11:01:00.000   Tue23oct2007 11:31:00.000 |
 49. |   5   01nov2007 11:31:00          8   Mon22oct2007 11:01:00.000   Mon22oct2007 11:31:00.000 |
 50. |   5   01nov2007 11:31:00          9   Fri19oct2007 11:01:00.000   Fri19oct2007 11:31:00.000 |
     |---------------------------------------------------------------------------------------------|
 51. |   6   12dec2007 10:31:00          0   Wed12dec2007 10:01:00.000   Wed12dec2007 10:46:00.000 |
 52. |   6   12dec2007 10:31:00          1   Tue11dec2007 10:01:00.000   Tue11dec2007 10:31:00.000 |
 53. |   6   12dec2007 10:31:00          2   Mon10dec2007 10:01:00.000   Mon10dec2007 10:31:00.000 |
 54. |   6   12dec2007 10:31:00          3   Fri07dec2007 10:01:00.000   Fri07dec2007 10:31:00.000 |
 55. |   6   12dec2007 10:31:00          4   Thu06dec2007 10:01:00.000   Thu06dec2007 10:31:00.000 |
 56. |   6   12dec2007 10:31:00          5   Wed05dec2007 10:01:00.000   Wed05dec2007 10:31:00.000 |
 57. |   6   12dec2007 10:31:00          6   Tue04dec2007 10:01:00.000   Tue04dec2007 10:31:00.000 |
 58. |   6   12dec2007 10:31:00          7   Mon03dec2007 10:01:00.000   Mon03dec2007 10:31:00.000 |
 59. |   6   12dec2007 10:31:00          8   Fri30nov2007 10:01:00.000   Fri30nov2007 10:31:00.000 |
 60. |   6   12dec2007 10:31:00          9   Thu29nov2007 10:01:00.000   Thu29nov2007 10:31:00.000 |
     +---------------------------------------------------------------------------------------------+

.
                      Last edited by Robert Picard; 02 May 2018, 11:11.

                      Comment

                      • #12
                        Hi Robert

                        I am so so so embarrassed by the typo! Let me make it right...THANK YOU ROBERT!

                        Yeah this is exactly what my data should look like. Now I can proceed with my statistical analysis.

                        I've printed out the explanations...stuck it on my desk and will analyse every single bit of it to make sure I understand the whole process!

                        Thanks again Robert & Clyde for all the help you've provided.

                        Regards
                        Parvesh

                        Comment

                        Previous Next
                        Working...
                        X