Excel Workbook: Day of Month Seasonality
Back to a topic from earlier this month: day of month seasonality.
Using a simple walk-forward test to minimize hindsight bias, I showed that trading the days of the month that have been strong historically, has led to stronger returns in the future since 1950 (read posts one and two).
A number of readers had trouble understanding how to run this type of analysis, so I’ve put together an Excel workbook to demonstrate.
Note that the workbook is about 4 MB. It’s in XLSX format (Office 2007) because an XLS file is just too big for downloading. And I’ve only included data back to 1980 (meaning the walk-forward begins in 2000) because, again, the file would be too big otherwise.
Once you download the file, you can populate the required data and copy all formulas to your new cells.
Cells colored grey designate information required by the user: date (column A), S&P 500 closing prices (B), the day of the month (C) and the total number of days in the month (D), and when the 20-year lookback begins for that date in terms of date (F) and row number (G).
I use columns F and G, rather than just taking the previous 5040 trading days (which would roughly equal 20 years), to compensate for potential earlier data when the market was open 6 days a week.
Columns AY through BR show days that qualified as the best half of days in the walk-forward. And columns BV and BW show hypothetical (frictionless) portfolios that only trade either qualifying or non-qualifying days.
Note that the S&P 500 data provided is not dividend adjusted.
. . . . .
P.S. In a previous post I showed how October would have broken down into quartiles based on day of month seasonality (using a more sophisticated approach than the one in the Excel workbook, but one that’s similar in spirit).
That breakdown has been very accurate so far this month with 5 of 6 qualifying days closing up, versus 2 of 6 non-qualifying days.
I should mention that those kind of numbers are definitely not par for the course, so temper expectations. As I’ve shown previously, this seasonality bias does not by itself justify a trade – only potentially biasing the trade.
Happy Trading,
ms
. . . . .
To stay up to date with what’s happening at the MarketSci Blog, we recommend subscribing to our RSS Feed or Email Feed.
Filed under: Time-based, Trading Strategies | 7 Comments
Thanks for sharing.
day of month?
i suppose you think women can’t trade, too.
what a sexist article!
Hmmm…humor that escapes my socially awkward nerd mind, or actual critique from someone who obviously didn’t bother reading the post?
Thanks for the hard work in putting this together. Much appreciated
I’d go with the first. Haha. I like “her” (?) title too: Mr. Rachel.
cant find anything remotely related to sex or sexism here – what a disappointment:)