defining perfect sequence pairs and average non-perfect pairs of data

[Rodrigo Saurin]

Dear all,

In the data structure below I have a perfect sequences Buy (B) Sell (S). Sometimes Buy or Sell appears many times, situation in which I want to average price between the first, second, and third etc observations e.g. symbol ABIL side B in 15jan2019 at 8:48 and 9:02 and AEMD side S 27nov2018 at 8:03 and 8:07. I am having a hard time developing the code for this. Any clue is appreciated.

Thank you,

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 side str5 symbol int qty float(price date) double dt float(mygroup new_var odd)
"B" "ABIL"  100      8.13 21404 50169000  3 17 1
"S" "ABIL"  100       7.9 21404 50273000  3 17 1
"B" "ABIL"  100      8.21 21409 37352000  3 17 1
"S" "ABIL"  100      7.91 21409 37384000  3 17 1
"B" "ABIL"   50      7.38 21509 52749000  3 17 1
"S" "ABIL"   50      7.09 21509 53050000  3 17 1
"B" "ABIL"   50      7.23 21509 53129000  3 17 1
"S" "ABIL"   50      6.93 21509 54075000  3 17 1
"B" "ABIL"   50      2.45 21564 31703000  3 17 1
"B" "ABIL"   40      2.37 21564 32540000  3 17 1
"S" "ABIL"   90      2.34 21564 32551000  3 17 1
"B" "ABIL"   90      2.35 21564 32724000  3 17 1
"S" "ABIL"   90      2.36 21564 32998000  3 17 1
"B" "ABIL"   90    2.4091 21564 34531000  3 17 1
"S" "ABIL"   90    2.4313 21564 34616000  3 17 1
"B" "ABIL"   90       2.3 21564 35005000  3 17 1
"S" "ABIL"   90    2.2513 21564 35465000  3 17 1
"B" "AEMD"  100      1.91 21515 28779000  9  5 1
"S" "AEMD"   50      2.22 21515 29018000  9  5 1
"S" "AEMD"   50      2.07 21515 29247000  9  5 1
"B" "AEMD"  100      2.11 21515 29473000  9  5 1
"S" "AEMD"  100    2.0432 21515 29556000  9  5 1
"B" "AMRN"  100     18.66 21468 36012000 17 19 1
"S" "AMRN"  100    18.563 21468 36168000 17 19 1
"B" "AMRN"  100     18.53 21468 38188000 17 19 1
"S" "AMRN"  100   18.3801 21468 38415000 17 19 1
"B" "AMRN"  100     19.14 21468 46844000 17 19 1
"S" "AMRN"  100    19.013 21468 47011000 17 19 1
"B" "AMRN"  200     19.25 21468 47196000 17 19 1
"S" "AMRN"  200     19.28 21468 47765000 17 19 1
"B" "AMRN"  100     22.01 21482 53067000 17 19 1
"S" "AMRN"  100   21.9122 21482 53266000 17 19 1
"B" "AMRN"  100     18.04 21503 35340000 17 19 1
"S" "AMRN"  100      17.8 21503 35422000 17 19 1
"B" "AMRN"  100     17.82 21503 37821000 17 19 1
"S" "AMRN"   50     17.93 21503 37894000 17 19 1
"S" "AMRN"   50     17.82 21503 38155000 17 19 1
"B" "AMRN"  100     17.99 21503 38409000 17 19 1
"S" "AMRN"  100  18.02016 21503 38499000 17 19 1
"B" "AMRN"  100        18 21503 38641000 17 19 1
"S" "AMRN"  100    17.955 21503 38973000 17 19 1
"B" "APHQF"  50     15.19 21467 52392000 20  3 1
"S" "APHQF"  25      15.6 21467 52686000 20  3 1
"S" "APHQF"  25     15.45 21467 52985000 20  3 1
"B" "CGC"    30      37.4 21411 30350000 38 15 1
"B" "CGC"    30     35.83 21411 30827000 38 15 1
"S" "CGC"    60     35.34 21411 31725000 38 15 1
"B" "CGC"    60     32.28 21411 35038000 38 15 1
"S" "CGC"    60   31.8101 21411 35134000 38 15 1
"B" "CGC"    50     32.08 21411 35332000 38 15 1
"S" "CGC"    50   32.5701 21411 35642000 38 15 1
"B" "CGC"    50     32.95 21411 35769000 38 15 1
"S" "CGC"    50   32.5101 21411 35895000 38 15 1
"B" "CGC"    50     31.22 21411 36556000 38 15 1
"S" "CGC"    50    30.946 21411 36649000 38 15 1
"B" "CGC"    70      30.4 21411 39377000 38 15 1
"S" "CGC"    70   30.2901 21411 39400000 38 15 1
"B" "CGC"    70     30.55 21411 39584000 38 15 1
"S" "CGC"    70   30.4329 21411 39668000 38 15 1
"B" "CRON"  100      6.76 21336 57196000 45 39 1
"S" "CRON"  100      6.83 21336 58039000 45 39 1
"B" "CRON"   50      7.14 21416 35831000 45 39 1
"S" "CRON"   50      7.12 21416 37236000 45 39 1
"B" "CRON"   50      8.04 21417 29721000 45 39 1
"S" "CRON"   50      7.95 21417 29915000 45 39 1
"B" "CRON"   50      8.43 21417 34289000 45 39 1
"S" "CRON"   50    8.0701 21417 34398000 45 39 1
"B" "CRON"   50      8.63 21417 34902000 45 39 1
"S" "CRON"   50       8.4 21417 35269000 45 39 1
"B" "CRON"  100      9.62 21467 53333000 45 39 1
"S" "CRON"   50      9.76 21467 53582000 45 39 1
"S" "CRON"   25        10 21467 53990000 45 39 1
"S" "CRON"   25    9.8401 21467 54362000 45 39 1
"B" "CRON"  100     10.54 21472 39281000 45 39 1
"S" "CRON"  100   10.4301 21472 39434000 45 39 1
"B" "CRON"  200     11.36 21472 50101000 45 39 1
"S" "CRON"  200   11.3116 21472 50326000 45 39 1
"B" "CRON"  200 11.469025 21472 50898000 45 39 1
"S" "CRON"  200   11.3708 21472 51201000 45 39 1
"B" "CRON"  100     11.31 21472 52928000 45 39 1
"S" "CRON"  100     11.18 21472 54075000 45 39 1
"B" "CRON"  200     11.25 21472 54172000 45 39 1
"S" "CRON"  200   11.2711 21472 54635000 45 39 1
"B" "CRON"  200    11.345 21472 55140000 45 39 1
"S" "CRON"  100     11.46 21472 55375000 45 39 1
"S" "CRON"   50     11.56 21472 55591000 45 39 1
"S" "CRON"   50     11.45 21472 55666000 45 39 1
"B" "CRON"  200      11.5 21472 55906000 45 39 1
"S" "CRON"  200   11.5001 21472 56258000 45 39 1
"B" "CRON"  200      11.6 21472 56530000 45 39 1
"S" "CRON"  100     11.67 21472 56862000 45 39 1
"S" "CRON"  100   11.7001 21472 57553000 45 39 1
"B" "CRON"  200     10.55 21474 35804000 45 39 1
"S" "CRON"  200      10.4 21474 35995000 45 39 1
"B" "CRON"   50       9.7 21495 53747000 45 39 1
"S" "CRON"   50    9.5701 21495 53801000 45 39 1
"B" "CRON"   50     15.53 21573 44346000 45 39 1
"S" "CRON"   50     15.54 21573 44384000 45 39 1
"B" "CZR"   100      9.64 21474 39021000 48  7 1
"B" "CZR"   100    9.4999 21474 39074000 48  7 1
end
format %td date
format %tc dt

[Carlo Lazzaro]

Rodrigo:
you may want to try:

bysort symbol: egen mean_price=mean(price)

[Rodrigo Saurin]

Dear Carlo Lazzaro,

This is what I tried, but if I do that then I will be averaging my buy and sells and that is not what I want. Each Buy (B) Sell (S) pair is an operation. Sometimes, the buyer scales in Buying many times as in ABIL or scales out selling chunks from a previous buy as in AEMD. I want to average just the occasions in which I scale in or out. The dataset is sorted in a way that Buys and Sells are perfect sequences, except when scaling in or out happens.

Thank you,

[Carlo Lazzaro]

Rodrigo:

bysort symbol side: egen mean_price_2=mean(price)

[Rodrigo Saurin]

I also tried this command, but as the first attempt it disregards that each pair B and S is a different operation from the others. It ends up averaging everything. I need to keep the individual nature of each operation Buy-Sell accounting for the fact that sometimes the same operation has more than one buy, more than one sell (scaling in and out) or both.

At any case, thank you for your time.

[Carlo Lazzaro]

Rodrigo:
sorry both my attempts were unhelpful.
I do hope that some interested listers who is familiar with your research field will chime in and reply as you expect.