One thing that’s awesome about this journey is learning new things in excel.
my data set was printing dates as year.day.month, ex 1971.02.05. Unfortunately excel can’t read that as a day, so i had to reformat it into 19710205, and then use =date(left(),mid(),right()). I then changed it into the day of the week using =TEXT(A1, “ddd”) so that I could look at full weeks of data. After using a few filters and some crude programming on my part, I got a list!
Pivot table from the list:
The expected % should be about 17%, or 100/6. Note that the total of 747 is weeks of data.
If I were to draw this onto a chart it would look something like..
Here’s what I’m taking from it. First, it looks like Sundays are uneventful, as expected. Second, the % of a weekly extreme being hit is highest on Friday, and then Monday. Mondays+Fridays make up close to 50%(!) of the weekly extreme frequency. If we start with the Friday statistic, this is suggesting that the week moves in a trend, or a weekly wave. Cool. Close to 30% of the time, the extreme isn’t hit until then, meaning if it’s been moving up for the past few days, there’s a good shot it will continue to do so until sometime on Friday. Next, Mondays seem to be a good time for a trend reversal, either from the week before it or simply from Sunday (This data is from Oanda servers btw, so Sunday data is just from Asian open until mid night NY, or 17:00-00:00 EST). Since this is calculating both highs and lows, it’s open to the possibility that in a “bull weekly wave” the low is made on Monday followed by the high on Friday and vice versa for bear weekly waves? Hmm.. Possibly more statistics coming out of this one later.